Configuring access to DB2 databases

To access DB2 databases from InfoSphere® Information Server, you must configure DB2 environment variables and set the privileges for DB2 users. The DB2 connector connects to your databases by using the DB2 client on the InfoSphere DataStage® nodes.

Before you begin

  • Confirm that your system meets the system requirements for InfoSphere Information Server. Make sure that you are using a supported version of IBM® DB2®. For more information about system requirements, see http://www.ibm.com/software/data/infosphere/info-server/overview/.
  • Install the IBM DB2 client on all InfoSphere DataStage nodes, and make sure that the client is working correctly.
  • Use the DB2 Configuration Assistant to test the DB2 client and server connection. If the DB2 client fails to connect to the DB2 server, jobs that use the DB2 Connector stage also fail.
  • Catalog each database in the DB2 client.
  • InfoSphere DataStage runs many processes for each job. Ensure that DB2 resources, configuration parameters, and manager configuration parameters are configured properly.
  • Make sure that the DB2_PMAP_COMPATIBILITY registry variable is set to ON to indicate that the distribution map size remains 4,096 (4-KB) entries. Though DB2® version 9.7 database for Linux, UNIX, and Windows supports distribution map entries up to 32,768 (32 KB), the DB2 connector supports only 4-KB entries in distribution maps.
  • If you plan to use the DB2 connector with DB2 for z/OS in jobs with sparsely arriving data (such as jobs that use the Change Data Capture stage), ensure that the idle timeout value set in the DB2 IDTHTION subsystem parameter is longer than the longest expected interval of inactivity for the DB2 Connector stages in the job.

Procedure

  1. Grant the InfoSphere DataStage users SELECT privileges on the following tables:
    Table 1. Required SELECT privileges
    DB2 product Tables that require SELECT privileges
    DB2 Database for Linux, UNIX, and Windows
    • SYSCAT.COLUMNS
    • SYSCAT.KEYCOLUSE
    • SYSIBM.SYSDBAUTH
    • SYSCAT.TABLES
    DB2 for z/OS
    Note: Before the data is loaded to data to DB2 for z/OS, make sure the user has the GRANT ALL access on SYSIBM.SYSPRINT:
    • SYSIBM.SYSCOLUMNS
    • SYSIBM.SYSINDEXES
    • SYSIBM.SYSKEYCOLUSE
    • SYSIBM.SYSKEYS
    • SYSIBM.SYSPRINT
    • SYSIBM.SYSTABLESPACE
    • SYSIBM.SYSTABLES
    • SYSIBM.SYSTABLEPART
    • SYSIBM.SYSUSERAUTH
    DB2 Database for Linux, UNIX, and Windows and z/OS
    • SYSIBM.SYSDUMMY1
    • SYSIBM.SYSVIEWS
  2. On DB2 for z/OS, ensure that the DBA runs the DSNTIJSG installation job to install the DSNUTILS stored procedure. The DSNUTILS stored procedure is required to start the bulk loader on DB2 for z/OS. For more information, see http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2z9.doc.inst/src/tpc/db2z_enabledb2supplstprocs.htm
  3. Set the DB2INSTANCE environment variable to the instance in the DB2 client in which you cataloged the target database.
    You must set the DB2INSTANCE environment variable even if the stage accesses the default DB2 instance. The instance that is specified in the DB2INSTANCE environment variable becomes the default instance that is used by the connector. If you want to use a DB2 instance other than the default, then enter the name of that instance in the Instance property of the DB2 connector in the Properties tab. The DB2 client installs the default instances.
    Table 2. Default instance installed by the DB2 client
    Operating System DB2 Instance
    Linux or UNIX db2inst1
    Microsoft Windows DB2
  4. Add the path to the directory that contains the client libraries to the library path environment variable. The default path for client libraries is shown in the table.
    Table 3. Default path for DB2 client libraries
    Operating System DB2 Instance
    Linux or UNIX /opt/IBM/db2/V9/lib64
    Microsoft Windows C:\IBM\SQLLIB\BIN
    Note: The DB2 Client Library File property must contain the file name of the client library as a fully qualified path when the path to the client library is not added to the library path environment variable. Else, just add the file name if the path already exists.
  5. Optional: If the globalization map name for the DB2 connector job does not match the current system locale on the engine tier, then set the DB2CODEPAGE environment variable to a codepage corresponding to the map name. The DB2 code page can also be set by using a DB2 registry variable.
Parent topic: Configuring access to data sources
Related tasks:
Setting the library path environment variable