As System i specialists, we are very aware that traditional databases still exist, even in some new developments. This is partly because adopting SQL requires changes in developers’ habits, and also because it generates new development constraints.

SQL adoption has become an obligatory rite of passage in changing development techniques. With the rise of multi-platform developments and the arrival of young developers in these companies, the choice is already made.

ARCAD’s solutions will help you to absorb a maximum of these new development restrictions and make the developer’s job easier than it was before.

Why adopt the SQL language as the standard for database manipulation?

SQL is a single standard
Just as TCP/IP is the single standard for networks, SQL has no rival. It is a worldwide standard, recognized by all operators in the field, without exception. With new tools on the periphery of applications, in Business Intelligence and Enterprise Application Integration (EAI), SQL use has become even more entrenched in the IS world.

SQL is a language for everyone
At a time when developers of widely different backgrounds work together, there is a need for common languages so everyone can understand each other. While it’s unlikely that developers will someday use only one programming language, with SQL they can at least share the same database access language.

Just as IBM has succeeded in creating a convergence between RPG/COBOL and Java development environments (with Eclipse), SQL is another technical building block that old school and new developers can have in common. SQL performs better than traditional modes of access. This is a fact, and it’s easy to understand why. From the time IBM started bringing together all the most recent, highest-performing technologies into the System i platform, this was the projected end result. When used properly, SQL language is perfectly adapted to handling very large data volumes, where previous access modes fail.

SQL is easy to maintain
SQL produces concise, easily adaptable code. Even the most complex accesses can be written in just 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 this kind of flexibility in the code.

DB2/UDB Databases on the
System i and SQL language ...

The System i relies on an original concept of databases: there is only one real database for the OS/400. Collection corresponds only to a selective view of the database (with plan=library). The OS could then reconcile the notion of a DDS physical file (PF) and logical file (LF) and make them available in the database, along with all the tables, views, etc.

The SQL meta language for DB2/UDB database description can be:

• Entered entirely in STRSQL
• Saved into source code members and executed with RUNSQLSTM
• Developed with help from the System i Navigator
• Recovered/executed from database modeling tools such as Rational Software Architect, PowerAMC and ERWin
• Used in other ways determined by the user

SQL allows definition of files by data type, tables, indices, or views. In order to place the maximum management rules at the database level, we define:

• Key and verification constraints, plus referential constraints between tables
• Triggers on addition, modification, deletion (and even reading) actions for table records (or at the field level)
• Stored procedures and user functions (processes can be called from SQL)
• And more

One special System i characteristic is that it allows system triggers to delegate their processing of non-SQL programs (RPG, COBOL, etc.). Further, functions and stored procedures can also serve as a calling protocol for native programs (or ILE procedures).

Access to data in SQL that is encapsulated inside native programs like RPG and COBOL has become an alternative to using specific orders for each language to get file access (such as for classic PF-LF files or tables and views). In particular, this allows use of a single method to access files--and all developers share this single method, whether they use AS/400 or newer technologies.

SQL is a standard language. However, it does have specific functions for each database type. Avoid using these in order to maintain total portability to other platforms.

Applications with SQL and DB2/UDB Database Maintenance ...

For applications with embedded SQL and/or DB2/UDB databases, ARCAD-Skipper can:

Provide cross-references:

• For use of files (and their fields) via SQL from SQLRPG(LE), SQLCBL(LE), and from IFS sources (Java, VB, Delphi) when they access databases on an iSeries using JDBC, ODBC, and other programs.
• From system-type triggers to called 3GL programs
• From stored procedures and system functions to called L3G3GL programs
• Between tables with referential constraints
• Between SQL source code for tables, stored procedures and functions to used files and stored procedures/called functions.

Provide methodology to maintain a database in a version
Without a tool, it is easier to deliver a complete database the first time than it is to deliver additions/modifications/deletions of tables, views, procedures, and the like into an existing databaseHowever ARCAD the tools to:

To maintain your database, ARCAD manages components (source + object) of the following types:

• TABLE (with associated fields, keys, constraints, and triggers),
• INDEX,
• VIEW,
• SQLUDT (data types),
• SQLPRC (stored procedures) (*),
• SQLUDF (user functions) (*),
• SQLSEQ (sequences=counters),
  

Each source uses compilation to create/recreate an object. All source code can be recovered from the objects in an existing database. In a version, you may choose to modify just a few DB2 components, or to perform broader modifications on a database. These modifications can be performed on the source code or directly on the objects. (whether under ARCAD control or not).

In both cases, ARCAD detects the change in additions/modifications/deletions and incorporates them into the version (by comparing reference objects to modified objects). Overall check of the version ensures source code/object coherence.

To transport modified components during transfer to test, production or distribution on sites/clients, you may choose to:

• Transport the source code and then recompile
• Directly transport all necessary objects.

During installation:

• Existing data is preserved
• Constraints on a delivered table are maintained

For compilations, a study of the source code automatically determines the correct compilation order in the case of dependency between tables or between views. The ARCAD Rollback system handles all these object types.

ARCAD-managed substitution triggers let you facilitate 3GL program maintenance and tests (in a version) when they are called from the system triggers placed on the tables--with no need to modify the table.

(*) with a virtual object if this is a stored procedure or a system function.

ADVANTAGES...

With ARCAD-Skipper, you will be able to:

• Ensure an easy transition to adoption of this de facto query language standard
• Increase your development productivity, by automating the logistics of your SQL code’s modification)
• Secure your database upgrades
• Automate transfers to production for your SQL components, and all other component types.