Defining the database configuration for IBM i DB2

When the Transformation map (prepared as Java executable file) runs, it will use JDBC to establish the connection to the IBM i DB2 database on your IBM i server. This connection is initiated from the server computer on which the transformation map executes.

LANSA Composer prepares the JDBC connection according to the Database Configuration that you provide as a parameter to the Transformation Map. You must define the Database Configuration with the correct details necessary to establish the particular database connection to be used.

Example Database Configuration

The following sample values assume that you are using native IBM JDBC driver to connect to the IBM i DB2 database on your IBM i server:

Database driver class

Following is the class name used with the native IBM JDBC driver: com.ibm.as400.access.AS400JDBCDriver

Database connection string

Following is a sample JDBC connection string to connect to the IBM i DB2 database running on your IBM i server using the native IBM JDBC driver: jdbc:as400://SYSNAME/LIBNAME;naming=sql;errors=full;date format=iso; translate binary=true The following alternate form permits database tables from more than one library (or schema) to be used.  The JDBC connection property naming=system must specified for this to be effective: jdbc:as400://SYSNAME;naming=system;libraries=LIB1, LIB2,*LIBL;errors=full;date format=iso; translate binary=true

 

NOTE:  The IBM Toolbox for Java comes with two JDBC drivers for the IBM i , which can be used for accessing DB2 data:

• IBM Toolbox for Java JDBC Driver - this is a Type 4 driver, implemented in the file jt400.jar, that makes direct socket connect to the database host server.  Examples and advice in this section assume that you are using this driver.

• An IBM Developer Kit for Java JDBC Driver is also available- this is a Type 2 driver, implemented in the file jt400native.jar, and makes native method calls to the SQL CLI (Client Level Interface).

 

Further considerations

• When your transformation map uses database components that connect to IBM i DB2 database tables, LANSA Composer will remove the library qualifier when you prepare the transformation map.  You must setup your database configuration so that the required files can be found at run-time.  Usually this is done by specifying the library name as part of the URL in the JDBC connection string - in the first example shown, LIBNAME represents the library name.

• An alternate form of the connection string permits database tables from more than one library (or schema) to be used.  For this form to be effective, the library name must be omitted from the URL portion of the connection string (so that the connection has no default schema) and naming=system must be specified.  In this form, the libraries= property of the JDBC connection string specifies the libraries that are to be searched for the tables used in the Transformation Map.  In the second example shown, LIB1, LIB2 and *LIBL represent the library names to be used.

• The Database Configuration should specify the username and password of an IBM i user authorized to perform the database transactions.

Refer also to:

Additional information and resources for IBM i DB2

Additional Considerations for Transformation Maps Using IBM i DB2