Configuring DB2 database connectivity in a parallel environment in DataStage

Configure DataStage to run on all processing nodes that function as DB2 servers.

Steps

To configure InfoSphere DataStage to access IBM DB2 servers, include a node definition for each IBM DB2 server node in the configuration file. Also, complete the following steps.

Running the IBM DB2 configuration script
Use the DB2 configuration script to configure access to IBM DB2 databases.

Procedure
From the Linux or UNIX command line, run the $APT_ORCHHOME/bin/db2setup.sh script once for each IBM DB2 database that users will access. Pass the database name as an argument.

For example, the following command calls db2setup.sh to configure access to the database db2db:

db2setup.sh db2db


Granting user privileges to run IBM DB2 Enterprise stage jobs
You must grant privileges to each user who runs jobs that contain the DB2 Enterprise stage.

Procedure
Run the $APT_ORCHHOME/bin/db2grant.sh script once for each user. Use the following syntax:

db2grant.sh database_name user_name

Accessing a remote IBM DB2 server from IBM DB2 Enterprise stages
You use an DB2 client to connect from an IBM DB2 Enterprise stage to a remote IBM DB2 server.

Configuring the DB2 system
The DB2 server can be configured in many ways. The DB2 administrator typically follows this procedure to configure IBM® DB2 and DataStage to communicate.

Procedure
Install DB2 and make sure it is working correctly.

Note: Ensure that the touch utility exists in the /usr/bin directory or create a symbolic link to the touch command.

Grant the InfoSphere DataStage users SELECT privileges on the DB2 system tables syscat.nodegroupdef, syscat.tablespaces, and syscat.tables.

Make the file db2nodes.cfg readable by the InfoSphere DataStage administrative user.

InfoSphere DataStage runs many processes for each job. This can require the system administrator to modify DB2 resources, configuration parameters, and manager configuration parameters. See the DB2 information center for more information.

The DB2 Enterprise stage requires that users invoking it in load mode have DBADM privilege on the DB2 database written to by the stage. Among the ways to grant this privilege is to start the DB2 system, connect to a database, and grant privileges from the DB2 command prompt:

CONNECT TO database_name
GRANT DBADM ON DATABASE TO USER user_name 


Configuring DB2 users
To access DataStage, parallel jobs use an operating system user ID to connect to the DB2 server. Therefore, user IDs that invoke DB2 read operators must have the SELECT privilege on the tables that they access, and user IDs that invoke DB2 write operators must have INSERT privileges on the tables that they access.

Procedure
Set the DB2INSTANCE environment variable to the user name of the owner of the DB2 instance. DB2INSTANCE determines the location of the db2nodes.cfg file, as follows:

~owner_user_name/sqllib/db2nodes.cfg

The db2nodes.cfg file contains information that is used to translate DB2 node numbers to host names. The information in this file must agree with the host names that are specified in the InfoSphere DataStage parallel configuration file.

For example, if you set DB2INSTANCE to Janet, the db2nodes.cfg file is in the following location:

~Janet/sqllib/db2nodes.cfg.

Optional: Set the DB2DBDFT environment variable to the name of the DB2 database that you want to connect to.

Source the db2profile file and update the LIBPATH environment variable to include the location of the DB2 library that contains your instance of DB2. In the following example, the owner of the DB2 instance is the user Mary, the location of db2profile is ~Mary/sqllib/db2profile, and the location of the DB2 library is ~Mary/sqllib/lib.

For a Korn or Bourne shell, include these statements in the .profile or .kshrc files:

. ~Mary/sqllib/db2profile
export LIBPATH=~Mary/sqllib/lib:${LIBPATH:-/usr/lib}

For a C shell, include these lines in the .cshrc file:

source ~Mary/sqllib/db2profile
if (! $?LIBPATH) setenv LIBPATH /usr/lib
setenv LIBPATH ~Mary/sqllib/lib:$LIBPATH

njoy the simplicity.......
Atul Singh