|
|
|
| |
 |
 |
The EDITORIAL by Philippe MAGNE |
 |
|
| |
Why
should the SQL language be
adopted as a database manipulation
standard? This question will
no doubt bring a knowing smile
to the faces of Unix or Windows
developers. We are a part of
the larger iSeries community,
backed by a gigantic existing
legacy developed over the years. “Classical” database
access methods still exist,
even in some new developments.
So the question is legitimate
and we will attempt to find
an answer.
SQL
is a single standard
Just like TCP/IP
is the single standard in networking,
SQL stands alone. SQL is a worldwide
standard, recognized by all industry
operators without exception. With
new tools peripheral to applications
in Business Intelligence or Enterprise
Application Integration (EAI), the
language’s influence reaches everywhere
in information systems.
SQL
is a language we already have in
common
When developers from different
cultures work together, they must
have common languages if they are
to understand each other.
|
|
While
it is improbable that
developers will work
in just one programming
language, thanks to SQL
they can share the same
database access language. Just
like IBM used Eclipse to
bring together its RPG/Cobol
and Java environment developments,
SQL is another technical
tool that “old school” and “modern” users
can have in common. SQL
is more effective than other
forms of access. This is
a fact, and it makes sense.
Since IBM has begun work
on bringing all the latest
and best-performing technologies
into this standard within
the iSeries platform, this
is the logical result. The
SQL language is perfectly
adapted to handling very
large volumes of data. -
just where “classical” access
modes fall short.
SQL
is an easily maintainable language
SQL
produces concise, easily adaptable
code. Even the most complex
access can be written in a
few lines. The code is easy
to understand. At a time when
information systems must evolve
ever more quickly, there is
a real need for such flexibility
in the code.
So why not adopt the SQL standard today for all new developments? Because it
requires changes in habit for
some developers? This may be
true, but young developers
already know SQL when they
get out of school.
Because
it creates new development
constraints? This is where
ARCAD’s solutions can help
you, by maximum absorption
of these limitations and by
making the developer’s work
at least as easy as it was
before.
Our next newsletter
will focus on Service
Oriented Architectures (SOA).
We will take a look at how
the two
technological building blocks
(namely SQL and ILE) discussed
in our recent newsletters are
a perfect fit for SOA migration
strategies. In the meanwhile,
enjoy this edition’s
detailed technical discussion
provided by our iSeries product
chief, Gilbert Fillatre.
Best regards,
Philippe MAGNE,
CEO
|
|
|
|
|
| |
|
|
DB2/UDB
Databases on iSeries and SQL Language,
by Gilbert FILLATRE |
|
|
|
Since multi-platform developments have
become widespread, more and more applications
use DB2/UDB databases on the iSeries (for
all or part of each application).
The iSeries
implements databases in an original way.
There is only one real
database for the OS/400, and a collection
is simply a selective view of this database
(by schema=library).
In this way, the OS can reconcile the notion of the DDS physical file (PF) and
logical file (LF) and make them both available in the database, at the same time
as all the tables, views, etc
The SQL meta-language for describing DB2/UDB databases
can be:
- Integrated entirely into STRSQL,
- Recorded in source members and executed with RUNSQLSTM,
- Built using iSeries Navigator,
- Retrieved/executed from database modeling tools.
This allows definition of “files” by data types, tables, index, and
views.
To
set the maximum number of management rules at the database level, also define:
- Constraints on keys, verification, and referential constraints between tables,
- Triggers on actions to add, modify, delete (and even read) records in tables
(or at the field level),
- Stored procedures and user functions (operations that
can be called from SQL),
- …
One quirk of the iSeries is that it allows “system” triggers. These are processed
by non-SQL programs (RPG, COBOL, etc.). Stored procedures and functions can also
serve as a calling protocol for native programs (or ILE procedures).
Access to
data in SQL encapsulated within native programs (RPG, COBOL, and others) has
become an alternative to using language-specific commands to access the files
(traditional PF-LF files or tables and views).
This allows use of a single method
to access the files, shared by all developers (whether they use AS/400
or newer
technologies).
SQL is a standard language. Nonetheless, it has specific characteristics
for
each database type. These must not be used if you wish to ensure complete portability
toward other platforms.
|
|
|
|
| |
|
|
Applications
with SQL and DB2/UDB Database Maintenance,
by Gilbert FILLATRE |
|
|
|
For
applications that include embedded
SQL and/or databases in DB2/UDB, ARCAD-Skipper
allows:
Cross references
- For file/field usage made in SQL from the SQLRPG(LE) and SQLCBL(LE),
and also from IFS sources (Java, VB, Delphi, etc.), when they access
databases on the iSeries via JDBC, ODBC, and others
- From “system” triggers to called 3GL programs
- From stored procedures or “system” functions to called
3GL programs
- Between tables that have referential constraints
- Between SQL sources of tables, stored procedures, and functions
to used files or called stored procedures/functions (**).
Apply clear methodology
to database maintenance in
a version
In general, without tools, it is easier to manage the very first delivery than
to deliver separate modifications later.
With
ARCAD, we resolve this issue by managing components (source and object) of type:
- TABLE (with fields, keys, constraints,
and triggers)
- INDEX,
- VIEW,
- SQLUDT (data types)
- SQLPRC (stored procedures) (*),
- SQLUDF (user functions) (*),
- SQLSEQ (sequences=counters) (**),
Each source allows creation/re-creation
of the object through compilation. All
these sources can be recovered from the
objects in an existing database.
In a version, you can choose to modify
only some DB2 components, or perform major
modifications to a database.
These modifications can be performed on
the source or directly on the objects (whether
controlled by ARCAD or not).
In both cases, ARCAD will then detect
the delta for additions/modifications/deletions
and incorporate them into the version (by
comparing the reference objects to possibly
modified ones).
Overall control of the version ensures
source/object coherence.
To transport these modified components
during transfers to test, production or
distribution to sites/customers, you can
choose to:
- Transport source and then recompile it,
- Directly transport all necessary objects.
At
the time of installation:
- Existing data is preserved,
- Constraints to a delivered table are
also maintained.
For compilations, an
examination of sources automatically
determines the correct compilation order, in cases of dependence between tables
or between views.
The ARCAD rollback system handles
all these object types.
Substitution triggers managed by ARCAD
permit easy maintenance and particularly
testing (in a version) of 3GL programs
when they are called from system triggers
placed on tables. There is no need to modify
the table.
(*)
With a “virtual” object
if stored procedure or “system” function
(**) Available in the next version of ARCAD
(V 8.05.A).
|
|
|
|
|
| |
|
|
ARCAD
NEWS : UPCOMING EVENTS |
|
|
|
IBM/ARCAD
Software seminar on Eclipse/WDSc & ARCAD
plug-ins, 18th May am.
How
to modernize your applications without
having to modernize your internal
organization? This is a critical
issue, especially when you are also
introducing new staff into your team.
Since V5R2 of OS/400, IBM has already
addressed this objective by providing
a
new graphical development environment – WDSc – as the official
replacement for the classic SEU/SDA/RLU and PDM.
We invite you to meet with George Voutsinas and David Muir,
both managers in the System i Application Tools Development in Toronto Lab, who
will give you a complete overview of the features of this environment, and illustrate
its practical advantages. You will also see how a vendor like ARCAD Software
has
been able to build on the stength of WDSc and integrate further structuring
features such as:
 |
Multi-platform
cross references, |
|
Mass
recompilation systems, |
|
Simplified
management of ILE, DB2 UDB and
SQL environments, |
|
Automation
and security of transfers to
production. |
In
addition to greater simplicity and productivity,
this working environment is a powerful
convergence factor for different IT cultures.
This seminar will be held on Thursday
18th May 2006 at 9am Eastern time.
| |
|
Venue:
Toronto Lab
8200 Warden Ave.
Markham,
Ont
Canada L6G 1C7
Tel: 905 413-3523 (security) |
|
|
The
IBM Toronto Lab is located southwest of Highway 7 and Warden Ave.,
just north of the 407ETR in Markham, Ontario.
Important: Visitors must first report to security through the visitor entrance
at 8200 Warden.
Target
audience:
This seminar is aimed at both IT heads and development staff keen to keep
abreast of the future working environment for development on System i. |
| |
To
subscribe to this session
please: |
 |
Don't
hesitate to ask for more information from
our sales staff at sales@arcadsoftware.com.
|
|
|
| |
|
|
|
Don't
hesitate to ask for more information
from our sales department by e-mail to
: 
