As iSeries development groups expand their use of SQL to create database objects, they may encounter new challenges when they attempt to move or copy objects from one schema (i.e., library) or system to another using either CL commands or save/restore operations. In this article, I provide some insight into moving and copying SQL tables, and I offer a few tips that will help you manage such operations more smoothly.

Journaled Tables

When you use the Create Schema statement to create an SQL schema, what you get is an OS/400 library with a journal, a journal receiver, and a set of SQL views that implement the SQL catalog of database objects created in the new schema.

When you use the Create Table statement to create a new SQL table in a schema, you get a single-member physical file, which is automatically journaled to the journal in the same schema by default.

Journaling active SQL tables, as well as physical files created from DDS, is generally a good idea because journaling supports effective database recovery and is necessary for transaction isolation (commitment control) and various types of database constraints (e.g., some forms of foreign key constraints).

Here's one tip: When you create foreign key constraints, you should be sure all SQL tables (and DDS physical files) that are related by one or more foreign keys are journaled to the same journal. This ensures that you can use the journal for recovery so your database maintains referential integrity among the related tables.

When you apply journal entries to recover any table or file in a set of tables and files related by foreign key constraints, you should apply journal entries for all the related tables and files. You should also apply (or remove) journal entries to a transaction boundary, such as by specifying CmtBdy(*Yes) on the Apply Journaled Changes (ApyJrnChg) command.

Using the MovObj command to move a journaled SQL table to a different schema on the same system does not switch the journal. And, when you use CrtDupObj to create a duplicate of a journaled table in either the same or a different schema, the new table is not automatically journaled. Instead of CrtDupObj, consider using the following form of the Create Table statement, available as of V5R2:

Create Table -
    As select-statement
    With Data

This statement will automatically journal the new table. You can also use journal-related CL commands to manage journaling for SQL tables. For example, you can use StrJrnPf and EndJrnPf to start and end journaling for a table.

If you want all new tables and physical files in a library (including ones created by a CrtDupObj command) to be journaled automatically, you can create a data area named QDFTJRN in the library and specify the journal's library name in positions 1-10, the journal name in positions 11-20, and *FILE in positions 21-24.

Restoring Journaled Tables

When you restore a table (or file), the resultant journaling status depends on several factors. If another instance of the table (i.e., a table with an identical name and file level identifier) exists on the target system and is replaced by the saved table, then the restored table retains the journaling status of the table that's replaced. If the replaced table was journaled, the restored table will also use the same journal as the replaced table — which may be different from the journal previously used by the saved table.

If an instance of the table doesn't exist on the target system, the restored table is journaled only if it was being journaled when it was saved and the journal it was using exists on (or has been restored to) the target system.

The final cases are subtle, but important, and cover the situation where an instance of the table exists on the target system, and the saved table is restored to a library other than the library that contains the existing table. If the existing table is not being journaled to the same journal that was used by the saved table (or the saved table wasn't being journaled), the restored table's journaling is handled the same as when no instance of the table exists on the target system (as described in the prior paragraph).

But if the existing table is still being journaled to the same journal used by the saved table, the restored table is journaled to the same (now shared) journal. However, you can't apply any journal entries before the restore operation to the restored table (the restore operation changes an internal journal identifier in the restored table so journal entries for the two tables can be distinguished).

Constraints

SQL tables (and DDS physical files) can have four kinds of constraints: primary key, unique, foreign key, and check constraints. DB2 uses keyed access paths associated with the underlying physical file to implement primary key and unique constraints for SQL tables. A foreign key constraint uses a keyed access path on the dependent table's physical file.

Using the MovObj command to move an SQL table to a different schema retains the table's constraints. If the table has a foreign key constraint, the "parent" table is unchanged.

When you use CrtDupObj to create a duplicate of a table in the same schema, the table retains its constraints unchanged. If you also duplicate the table's data and the original table has a constraint with the "check pending" status (e.g., some row in the table violates a constraint that's enabled), the check pending status remains for the new table as well.

Until you correct the constraint violations, no operations are allowed on a table with check pending status for a check constraint. For a foreign key constraint with check pending status, only read and insert operations are allowed on the parent table, and no operations are allowed on the dependent table.

To correct constraint violations, use the ChgPfCst command to change the constraint state to *Disabled, which allows you to update the table's data. When you're done fixing the constraint violations, change the constraint's state back to *Enabled.

Creating a duplicate of a table in a different schema has one twist: When the original table has a foreign key that references a parent table in the same schema, the new table's foreign key is also changed to reference a parent table (with the same name and key fields) in the same schema as the new table.

For example, suppose you have a parent table P and a dependent table D in schema S1, and you also have a table P in schema S2. When you execute a command such as

CrtDupObj Obj( D )         +
          FromLib( S1 )    +
          ObjType( *File ) +
          ToLib( S2 )

the system tries to add a foreign key constraint to table D in schema S2, and this constraint references table P in schema S2.

If no table named P exists in S2, the constraint is added to the new table D, but the constraint is put in the "defined/enabled" state. When a suitable parent table is created in S2, table D's foreign key constraint will be changed to the "established/enabled" state.

(The SQL command processor won't let you use a Create Table statement to create a table with a foreign key constraint that references a nonexistent parent. However, the CrtDupObj command does not require the parent table to exist at the time you duplicate a dependent table.)

If a parent table (P in this example) does exist in S2 but lacks any of the columns referenced in the newly duplicated table's foreign key, the foreign key constraint is dropped entirely from the new table. If the parent table has the necessary columns but not an appropriate primary key or unique constraint (as DB2 requires for all parent tables), the constraint is put in the defined/ enabled state until a suitable primary key or unique constraint is added to the parent table.

When a foreign key constraint becomes established/ enabled, the system checks to make sure the dependent table's rows all satisfy the foreign key constraint; if any row doesn't, the constraint is flagged with the check pending status. As mentioned earlier, until you correct all constraint violations (or you disable or remove the constraint), only read and insert operations are allowed on the parent table, and no operations are allowed on the dependent table.

CrtDupObj has one other wrinkle: Creating a duplicate table in the same schema generates new, unique names for all the table's constraints. The names have forms such as QSYS_xxx_00001, where xxx is all or part of the original constraint name (or the table name, if the original constraint had a system-generated name), and the last five characters are a sequence number to make the constraint name unique within the schema.

Restoring Tables with Constraints

When you save a table (or file) with constraints, the constraints and their access paths are saved with the table. When you subsequently restore the table by replacing an existing instance of the table, the existing table's constraints are maintained and the saved table's are ignored.

When you restore a table without replacing an existing table, the system attempts to add all the saved constraints to the restored table. In the case of foreign key constraints, the process follows similar rules to those that apply when a duplicate object is created in a different library (as explained in the previous section).

The system maintains a data level attribute for related tables, and when you restore related tables that have identical data levels, the system doesn't need to revalidate foreign keys for the dependent table(s). If a parent table is saved, changes are then made to a dependent table and the dependent table subsequently saved; the saved parent and dependent table(s) may have different data levels, so the system will have to recheck the foreign keys when the tables are restored. If any dependent table row violates a foreign key constraint, the system sets the constraint to the check pending status.

You can avoid potential synchronization problems with restore operations and speed up recovery in some situations by using a single save command to save all tables (and files) that are related by one or more foreign keys. You can then restore the full set, if necessary, with a single restore operation. Alternatively, avoid updates to any of the related tables during the sequence of save commands used to create a backup and to any of the related tables during the sequence of restore commands used during recovery. These precautions will ensure the saved and restored tables' data levels remain in synch.

Triggers

A trigger is a set of conditions and an executable program associated with a table (or physical file). When an I/O operation is attempted on the table and the specified conditions are met, DB2 calls the program. Trigger programs can prevent the I/O operation from being attempted, alter its effect, or perform other tasks.

You create an SQL trigger with the Create Trigger statement, which specifies both the conditions to "fire" the trigger and the implementation code for the trigger program. On the iSeries, the SQL code that implements a trigger program is first converted into ILE C source code with embedded SQL statements. This source code is then precompiled and compiled into a *Pgm object. The program object is flagged internally as an SQL trigger program, which enables the system to handle some housekeeping automatically when you move, rename, or restore the program.

You create an external trigger by compiling an HLL program with appropriate parameters and then executing an AddPfTrg CL command to specify the name of the trigger, the file to which it's attached, the conditions to fire the trigger, and the name of the trigger program object. An external trigger program is not flagged internally as a trigger program, and the system doesn't provide any special handling when you move, rename, or restore it.

Both the Create Trigger statement and the AddPfTrg command resolve the library for unqualified trigger and trigger program names when you add the trigger to the table. This ensures that the trigger program can always be found when any job updates the table, regardless of a particular job's library list or default schema. Both techniques also cause an entry to be placed in the SysTrigger view of the SQL catalog. This entry contains the names and schemas of the trigger, the associated table, and the original trigger program.

When you move, duplicate, or restore a table with a trigger or move or rename the trigger program or objects the trigger program references, you have to be aware of how various object references are affected. These references include the following:

• The table (or file) object maintains fully qualified references to the trigger and the trigger program.
• The SQL catalog trigger entry has fully qualified references to the table and the trigger program.
• The trigger program may reference the associated table and/or other objects. The schema(s) for all unqualified names in an SQL trigger program are resolved when the Create Trigger statement is executed. This ensures that objects the trigger requires can be found (in their original locations, at least) regardless of the current job's search paths.

Unqualified names in an external trigger program, however, may not be resolved until runtime (e.g., by searching the job's library list). In contrast to the normal practice of using unqualified object names in application programs, you should be cautious about using them in external trigger programs. If a job's library list doesn't resolve a trigger program's unqualified object references to the correct objects, the trigger might fail.

When you move a table to a different schema, the system updates any table reference to a trigger in the table's original schema so the reference uses the table's new schema instead. The SQL catalog entries for the trigger are also updated. The table's trigger program references are not changed, but the trigger status is set to *Inoperative, which prevents I/O operations (i.e., inserts, updates, deletes) for which the trigger is defined. As a result, when you move a table, you should generally drop and re-create its SQL triggers and/or remove and re-add its external triggers.

If you rename an SQL trigger program or move it to a different schema, the system automatically updates the table's reference to the trigger program, and the trigger program will still be found when the trigger fires. (The trigger entry in the SQL catalog isn't updated.) No such update occurs when you rename or move an external trigger program, and the program won't be found when needed unless you remove and re-add the trigger program to the table.

When you move an object referenced by an SQL trigger program, it usually won't be found because, as mentioned, SQL trigger programs use only qualified object references. For external trigger programs, a moved object may still be found if the program used an unqualified reference, and the object can still be found through the library list or default collection.

If you save a table (or file) that has a trigger, its references to triggers and trigger programs are saved, but the associated trigger program(s) are not automatically saved.

When you subsequently restore the table by replacing an existing instance of the table, the existing table's triggers are maintained and the saved table's trigger information is ignored.

When you restore a table without replacing an existing table, the system restores the file's trigger and trigger program references and creates trigger entries in the SQL catalog. If the table is restored to a different schema, the system adjusts the trigger and trigger program reference schemas as described for moving a table.

To restore a table with an external trigger, you must make sure the trigger program exists in the schema specified by the table's trigger program reference. With an SQL trigger, if the referenced trigger program doesn't exist at the time of a database I/O operation that fires the trigger, the system attempts to re-create the trigger program from a saved copy of the trigger's SQL implementation code. If the attempt succeeds, the I/O proceeds. You shouldn't depend entirely on this automatic trigger program re-creation for your recovery strategy, however. Generally, you should keep backups of the Create Trigger source and the trigger program object.

Know the Rules

The rules I've just covered for copying, moving, and restoring SQL tables can seem overwhelming. But the two main situations you'll encounter are recovery, in which saved tables are restored to their original libraries, and promotion of new versions of tables and related constraints and triggers from development to production environments.

The rules for recovery are fairly simple and mainly require that you restore related objects from copies that were saved when the objects were in a consistent state.

There are more hurdles to promoting objects, and you generally need some means of running scripts or compiled CL programs that drop and re-create tables and their dependent objects, rather than just moving or duplicating objects with the MovObj and CrtDupObj commands or using save and restore operations. For a flexible promotion approach, you might use either homegrown solutions or one of the iSeries-aware automated build and distribution tools available from a number of software configuration management (SCM) vendors. (For a list of SCM vendors, see "Software Configuration Management Survival Guide," October 2004, article ID 19176 at iSeriesNetwork.com.)

Paul Conte is an iSeries NEWS senior technical editor.