Prepare the DB2 Tables on the AS400

Prepare the DB2 Tables on the AS/400

The goal of this solution is to copy and transform data from DB2 tables on the AS/400 system to similar data tables in SQL Server on Windows 2000. Many DB2 and AS/400 administrators may be reluctant to allow access to the DB2 database containing critical business data from other applications. There may be concerns about the impact of this external data access on interactive use and response times for these core business applications. There may be concerns about reliability and data integrity if external applications are allowed access to this critical business data.

In order to mitigate these concerns in the proposed solution, the critical DB2 database and tables will not be accessed directly using the OLE DB Provider for DB2 and DTS. The information from DB2 that needs to be accessed will first be copied to new tables in a temporary database in DB2. The OLE DB Provider for DB2 and DTS will only access these temporary or staging tables, not the core business data tables. DB2 can be configured to generate these temporary tables automatically using triggers when changes are made to the core data tables. These triggers are special constraints placed on a database that can cause other events in DB2 to occur. In our scenario, triggers will be created and attached to each core DB2 table that is to be made available to the OLE DB Provider for DB2 and DTS. When the trigger event is activated by an insertion, deletion, or update of rows in a specific table, the change will be propagated to the temporary tables.

IBM DB2 on the AS/400 supports two types of triggers:

• SQL Triggers - triggers configured as SQL statements on a database by the database administrator.
• System Triggers - triggers configured as external programs written in RPG, COBOL, or ILE C that are attached to a physical or logical file that trigger on file system changes. Since DB2 tables are implemented as part of the file system on OS/400, system triggers can be used to implement the same effects as SQL triggers.

The proposed solution uses SQL triggers, primarily because they are simpler to describe, create, and use. SQL triggers are a feature added in IBM DB2 Universal Database Version 6.0 and later. On the AS/400, these features were added with the release of DB2 Universal Database for OS/400 included as part of the OS/400 operating system in Version 5 Release 1.

Prior to OS/400 V5R1, SQL triggers were not supported but system triggers can still be used. The IBM documentation provided with DB2 provides details and source code examples of system triggers written in several languages.

An SQL trigger is a constraint configured on a database that can cause other DB2 operations to occur. SQL triggers provide a mechanism to actively monitor a database or group of tables for an occurrence when an insert, update, or delete operation is to be performed. The definition of the SQL trigger is stored in DB2 as part of the metadata associated with a database. SQL triggers on DB2 are created using the SQL CREATE TRIGGER statement applied to a data table. Any number of separate SQL triggers can be applied to a data table. The statements specified in the SQL trigger are executed each time an insert, update, or delete operation is performed on the table.

SQL Triggers must have a unique name. In DB2 Universal Database, SQL triggers have a two-part name similar to a table that includes the database name (SAMPLE.cust_trig4 in the SAMPLE database, for example). When creating triggers, the following properties must be specified:

• Trigger name - a unique name within a schema
• Triggering event - the type of operation that should invoke the trigger (an insert, update, or delete operation)
• Activation time - when the trigger is to be executed (either before or after the event occurs to the database)
• Granularity - whether the trigger should be activated only once based on an SQL statement causing the event (statement trigger) or for each row that is changed (row trigger)
• Transition variables - whether access to information on the specific changes to a database or row are needed in the form of variables to the trigger.

The body of the trigger can consist of one or more SQL statements. SQL triggers can also call DB2 stored procedures or DB2 user-defined functions to perform additional processing when the trigger is executed.

For the proposed solution, after triggers are used to activate for each row that is changed in a table. Three separate triggers are created for each data table in DB2 to be moved and transformed using DTS. The purpose of the triggers is to maintain a parallel set of data tables (temporary or staging tables) that are accessed using DTS.

The following triggers are needed:

• Insert trigger that inserts a new row of data to the corresponding temporary table.
• Update trigger that makes updates to an existing row of data in the corresponding temporary table
• Delete trigger that deletes an existing row of data in the corresponding temporary table

SQL triggers can be created using various tools provided with IBM DB2 for configuration and management. SQL triggers are saved in a Triggers folder under the database to which they are attached.

Recent versions of IBM DB2 include a SAMPLE database that will be used as the basis for developing an example SQL trigger. The SAMPLE database consists of several data tables including SALES, EMPLOYEE, etc. The SALES table has four columns: SALES_DATE, SALES_PERSON, REGION, and SALES. The SQL trigger defined below (sales_ins_trig in the DB2 SAMPLE database) is designed to trigger on inserts to the SALES table and insert a new row into a parallel TMPSALES data table.

CREATE TRIGGER sales_ins_trig      AFTER INSERT on sales     REFERENCING NEW as newrow     FOR EACH ROW MODE DB2SQL     INSERT INTO tmpsales(sales_date, sales_person, region, sales)     VALUES(newrow.sales_date, newrow.sales_person, newrow.region, newrow.sales)  

The name of the SQL trigger is specified as sales_ins_trig after the CREATE TRIGGER keywords. The fully qualified name of the trigger, SAMPLE.sales_ins_trig, includes the database name. DB2 Universal restricts database names to 8 characters and other name elements (the trigger name, for example) to 18 characters. These name-length restrictions are also dependent on the version of DB2 that is to be used.

The "AFTER INSERT" statement identifies sales_ins_trig as an after trigger where the triggering event occurs after an INSERT occurring on the SALES data table. The REFERENCING NEW syntax indicates that newrow is a transition variable that will be used by the trigger. The "FOR EACH ROW MODE DB2SQL" statement indicates the granularity of the trigger, in this case a trigger will activate for each row that is inserted. Finally the INSERT INTO and VALUES SQL statements indicate the action to be taken when this trigger occurs. In our example, the values of the fields (columns) in the new row being inserted in the SALES data table are also inserted into a new row that is inserted into the parallel TMPSALES data table.

SQL triggers for update and delete operations for the SALES data table also need to be created that will propagate changes to the TMPSALES data table. Similar SQL triggers would need to be created for each data table that is to be replicated in a temporary table.

SQL triggers are executed under the authentication of the user that created the trigger, not the user that may have executed the SQL statement that triggered the event. Consequently, DB2 administrators or other users with the appropriate authority must create the SQL triggers to maintain a set of temporary data tables proposed in this scenario. Administrative users or the owner of the TMPSALES database have the necessary authority to insert records.

Previous  Next