Additional Considerations for Transformation Maps Using IBM DB2 for i5 OS

LANSA Composer

Additional Considerations for Transformation Maps Using IBM DB2 for i5/OS

The IBM i and its integrated environment including the IBM DB2 for i5/OS database offer some unique capabilities.  Because the mapping tool is designed to support databases on a wide variety of platforms, some of the capabilities specific to the IBM i server present particular considerations for Transformation Maps that use IBM DB2 for i5/OS database components.  The following topics describe some of these considerations:

Libraries and Library Lists

Using SQL SELECT Statements or SQL-WHERE Components in an IBM DB2 for i5/OS Database Components

Library names in the DSN

Transaction Control and IBM DB2 for i5/OS Database Journaling

Note:  The considerations described are based on using Altova MapForce version 2010 release 3.  Earlier (or later) versions of the mapping tool may be subject to different considerations or may differ with respect to the specific features mentioned.

 

Libraries and Library Lists

In a typical i5/OS environment, applications avoid hard-coded reference to specific libraries.  This is a powerful feature that permits the same programs to be run using database tables and other objects in libraries that are determined by the run-time environment.  LANSA Composer supports this feature in connection with Transformation Maps that use IBM DB2 for i5/OS database tables as follows:

1.  Defining a Transformation Map that uses IBM DB2 for i5/OS database tables

When you define a Transformation Map that uses IBM DB2 for i5/OS database tables you select a specific library containing the files whose definitions you wish to use.

It is important to understand that the library from which you select the files is used only for the purpose of defining the transformation map.  It does not mean that the Transformation Map will use the files in that library at execution time.

 

2.  Preparing a Transformation Map that uses IBM DB2 for i5/OS database tables

When you prepare a Transformation Map that uses IBM DB2 for i5/OS database tables, LANSA Composer will remove the library qualifier from the generated Java code.  This means that the Transformation Map can be executed using different instances of the database tables at run-time.  This is the desired behavior in an IBM i server environment.

Note that LANSA Composer removes library qualifiers only for an IBM DB2 for i5/OS database component - not for any other type of database.  It does so by using a temporary copy of the map definition (*.mfd) file in which it has set MapForce's database component setting Strip schema names from table names (if not already set).  The original map definition (*.mfd) file is not changed in this process.

Note:  LANSA Composer will not remove library qualifiers for certain types of map constructs.  For more information, refer to Using SQL SELECT Statements or SQL-WHERE Components in an IBM DB2 for i5/OS Database Components.

 

3.  Executing a Transformation Map that uses IBM DB2 for i5/OS database tables

When you execute a Transformation Map that uses IBM DB2 for i5/OS database tables, LANSA Composer prepares the JDBC connection according to the Database Configuration that you provide as a parameter to the Transformation Map.  The Database Configuration specifies the library (or libraries) to use.

 

Using SQL SELECT Statements or SQL-WHERE Components in an IBM DB2 for i5/OS Database Components

The mapping tool provides two features that allow you to enter SQL that will be executed by the Transformation Map:

  • A database component may include one ore more SQL SELECT statements, the results of which may be used as "virtual" tables for mapping purposes.
  • Another type of component, SQL-WHERE, allows you to filter database data conditionally using an SQL WHERE clause that you enter yourself.

 

When you use these features with an IBM DB2 for i5/OS database component, you must understand that the Strip schema names from table names feature does not strip schema (library) names from the user-defined SQL SELECT statement or SQL-WHERE clause.  This means that when you prepare the Transformation Map, any schema (library) qualifiers present will remain in the generated Java code.

In order to avoid creating Transformation Maps that access or update database tables in a library other than intended, you must avoid using schema (library) qualifiers in your SQL SELECT statement or SQL-WHERE clause when using these MapForce features.

There is a secondary consequence of this.  If you omit or remove the library qualifiers from your SQL SELECT statement or SQL-WHERE clause, you may find that:

  • You can no longer test execute the map in the mapping tool using the Output tab, and/or
  • The Prepare step may fail while generating the Java code.

 

In both cases, this occurs because ODBC cannot find the files because the library or libraries containing the files are not specified in the DSN.  If this occurs, it will be necessary to use an ODBC DSN that specifies an appropriate library list.  Refer to Library names in the DSN for more information.

 

Library names in the DSN

When you define a Transformation Map that uses IBM DB2 for i5/OS database tables, MapForce prompts you to select a specific library containing the files whose definitions you wish to use.

As a result, it is frequently not necessary to specify the library or libraries you wish to use in the ODBC DSN you use for the connection.  This can be convenient because it allows you to reference tables in multiple libraries using a single DSN.

However, when using certain MapForce features with such a DSN you may be affected by one or both of the following:

  • You cannot successfully test execute the map in the mapping tool using the Output tab, and/or
  • The Prepare step may fail while generating the Java code.

 

In particular this occurs:

  • When you use MapForce SQL SELECT statements in which (as recommended) you have omitted the library qualifiers;
  • When you use MapForce SQL-WHERE components in which (as recommended) you have omitted the library qualifiers;
  • When you explicitly select the Strip schema names from table names check-box in the database component settings.

 

To avoid these issues, you are advised to use a DSN that specifies the library or libraries you wish to use.  You can specify such a DSN for the IBM i Access ODBC Driver using the Server page of the IBM i Access for Windows ODBC Setup window in one of the following ways:

1.  To specify a single library, type the library name in the SQL default library box as shown:

 

2.  To specify a list of libraries:

a.  Select System naming convention (*SYS) in the Naming convention drop-down list (but see note below)

b.  Specify the required libraries in the Library List box as shown:

Note:  If you use the system naming convention, you will have to select the option to connect via the ODBC API (instead of Natively) when prompted while adding the database component.  Otherwise you will not be able to see or select your library name in the Add/Remove Tables window.

 

Transaction Control and IBM DB2 for i5/OS Database Journaling

You may use a database component, including an IBM DB2 for i5/OS database component, as the source or target of a mapping.

When used as the source of a mapping, you are using the database for read-only and transaction control considerations do not apply.

When used as the target of a mapping however, the Transformation Map may be performing insert, update and/or delete actions against the affected database tables, according to the Database Table Actions you specify in the mapping tool.  In this case, you may wish to consider transaction control (commitment control) options that allow you to ensure that a transaction is either completed in its entirety or rolled back in the event of an error.

The mapping tool allows you to specify that you wish to use transactions at two levels:

  • In the database Component Settings window, check the Use Transactions checkbox to indicate that transaction control is to be applied to database table actions for all tables used in the database component;
  • In the Database Table Actions window, check the Use Transactions checkbox to indicate that transaction control is to be applied to database table actions for that table.

 

In most cases, specifying the option at the component level is advised.  Unresolved issues have been observed when attempting to specify the option at the database table actions level (at the level of MapForce version 2012 release 2).  Refer to the Altova MapForce documentation for more details concerning these options.

The remainder of this section is concerned with the particular considerations that apply when you use these options in connection with an IBM DB2 for i5/OS database component.

1.  If you do not specify the Use Transactions options in your map, no special considerations apply.  There is no need to have database journaling in effect for the affected IBM i database tables.  (Note that this is a change from the advice that applied to earlier versions of LANSA Composer and the mapping tool).

However, if when you execute the map, you experience an error SQL7008 (<file> in <library> not valid for operation) with reason code 3, it may be because your Database Configuration is not configured appropriately for this case.  In particular, your Database Configuration:

  • Should specify transaction isolation: None
  • Should not contain keywords in the JDBC connection string that will cause the database manager to attempt to use commitment control.  For example, make sure that it does not contain the string 'true autocommit=true'.

 

2.  If you do specify the Use Transactions options in your map, then you will need to:

a.  Ensure that the database itself is configured to support the desired transaction control.  This usually means at least that the database tables must be journalled.  Depending on how your database tables were created, you may have to use IBM i commands such as CRTJRNRCV (Create Journal Receiver), CRTJRN (Create Journal) and STRJRNPF (Start Journal Physical File).  Refer to IBM i documentation for details.

b.  Specify appropriate values in your Database Configuration to support the desired transaction control.  In many cases, the following values will be appropriate:

Database connection string 

jdbc:as400://SYSNAME/LIBNAME; prompt=false;naming=sql;data truncation=false;errors=full;date format=iso;translate binary=true

 
Commit automatically

No

Transaction isolation

Read uncommitted

 

(The values suggested above were successfully tested with IBM i OS 7.1.  The specific values required on your system may vary according to your environment and system and database configuration.  The values required may also vary according to the JDBC driver level and the operating system level.)

 

When using transaction control you are strongly advised to test that the map options you specified along with the database configuration you propose to use do work as expected, both for the case where the transaction completes normally and for the case where an error occurs requiring the transaction to be rolled back.  A transformation map that uses transaction control on an i5/OS database may appear to function correctly in normal circumstances.  It may not become apparent until the necessity arises to invoke the transaction rollback that the database setup (eg: lack of journaling) and/or the database configuration are not appropriate for supporting the desired transaction control.