Connecting to IBM DB2 for i5/OS to execute a Transformation Map
When the Transformation map runs, it will use JDBC to establish the connection to the IBM DB2 for i5/OS 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 DB2 for i5/OS 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 DB2 for i5/OS 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=trueThe 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 |
Further considerations
- When your transformation map uses database components that connect to IBM DB2 for i5/OS 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 i5/OS user authorized to perform the database transactions.
- Depending which driver version you are using, the IBM JDBC Driver is implemented in the file jt400.jar or jt400Native.jar. If it is not already present, you may need to copy the file to the /jar directory of your JSM instance and restart the Java Service Manager before using the driver with Transformation Maps in LANSA Composer. The jt400.jar is supplied with the IBM i operating system can usually and can usually be found in an IFS folder such as //QIBM/ProdData/OS400/jt400/lib
- Refer to relevant IBM reference materials for more information on the JDBC drivers available for IBM DB2 for i5/OS and the keywords and values that can be specified in the JDBC connection string for the native IBM JDBC driver. Following are some useful WEB references. Note that some of these references relate to specific versions of i5/OS and associated software. You should refer to the equivalent pages for the software versions you are using.
IBM Toolbox for Java JDBC properties
IBM i - IBM Toolbox for Java: JDBC FAQ
ConnectionStrings.com - Forgot that connection string? Get it here!