Accessing IBM DB2 Databases

To use the DB2/UDB enterprise stage, you must have valid accounts and appropriate privileges on the databases to which the stage connects. If you are using IBM® DB2® 8.1 Enterprise Server Edition (ESE), it is recommended that you install DPF in order to leverage the InfoSphere DataStage's parallel capabilities.

Listed below are the required IBM DB2 privileges:

• SELECT privilege on any tables from which to read data.
• INSERT privilege on any existing tables to be updated.
• TABLE CREATE privilege to create any new tables.
• INSERT and TABLE CREATE privileges on any existing tables to be replaced.
• DBADM privilege on any database written by using the LOAD method.

You can grant this privilege in several ways in IBM DB2. One is to start IBM DB2, connect to a database, and grant DBADM privilege to a user, as shown below:

db2> CONNECT TO db_namedb2> GRANT DBADM ON DATABASE TO USER user_name

where db_name is the name of the IBM DB2 database and user_name is your InfoSphere DataStage login user name. If you specify the message file property in conjunction with LOAD method, the database instance must have read or write privilege on that file. The location of the log file for the LOAD operation messages is exactly as defined in the APT_CONFIG_FILE.

Your PATH should include $DB2_HOME/bin, for example, /opt/IBMdb2/V8.1/bin. The LIBPATH should include $DB2_HOME/lib before any other lib statements, for example, /opt/IBMdb2/V8.1/lib.

The following IBM DB2 environment variables set the runtime characteristics of your system:

• DB2INSTANCE specifies the user name of the owner of the IBM DB2 instance. IBM DB2 uses DB2INSTANCE to determine the location of db2nodes.cfg. For example, if you set DB2INSTANCE to "Mary", the location of db2nodes.cfg is ~Mary/sqllib/db2nodes.cfg.
• DB2DBDFT specifies the name of the IBM DB2 database that you want to access from your DB2/UDB enterprise stage.

There are two other methods of specifying the IBM DB2 database:

1. The override database property of the DB2/UDB enterprise stage Input or Output link.
2. The APT_DBNAME environment variable (this takes precedence over DB2DBDFT).

You should normally use the input property Row Commit Interval to specify the number of records to insert into a table between commits (see the Row Commit Interval section under Options category). Previously the environment variable APT_RDBMS_COMMIT_ROWS was used for this, and this is still available for backwards compatibility. You can set this environment variable to any value between 1 and (2³¹ - 1) to specify the number of records. The default value is 2000. If you set APT_RDBMS_COMMIT_ROWS to 0, a negative number, or an invalid value, a warning is issued and each partition commits only once after the last insertion.

If you set APT_RDBMS_COMMIT_ROWS to a small value, you force IBM DB2 to perform frequent commits. Therefore, if your program terminates unexpectedly, your data set can still contain partial results that you can use. However, the high frequency of commits might affect performance of DB2/UDB enterprise stage. If you set a large value for APT_RDBMS_COMMIT_ROWS, DB2 must log a correspondingly large amount of rollback information. This, too, might slow your application.