[Help] DB2/UDB Enterprise Stage

HSBCdev · Post by **HSBCdev** » Sun Jan 30, 2005 9:36 pm

I want to setup AIX server to support DB2 connection with DataStage, anyone can give some information on how to setup, what authority should be granted.

Thx a lot!

What I understand have listed below.

1. Run /sysp/DataStage/Ascential/DataStage/PXEngine/bin/db2grant.sh

User dstgadm

What authorities should be granted to run the above script

2. Run /sysp/DataStage/Ascential/DataStage/PXEngine/bin/db2setup.sh

User dstgadm

What authorities should be granted to run the above script

3. Grant syscat.nodegroupdef, syscat.tablespaces, syscat.tables

What level of authorities are required for the above tables to user dstgadm.

4. Grant read authority for db2nodes.cfg

What should be set within db2nodes.cfg

5. DBADM is required to be granted ?

Eric · Post by **Eric** » Mon Jan 31, 2005 6:46 am

What do you mean by "What authorities" ?

(1,2,3,4) All of these only need to be run once by the user who will be running the job (requiring access to DB2)

5 you do not need to give admin writes to the database for the user to run jobs.

HSBCdev · Post by **HSBCdev** » Mon Jan 31, 2005 10:09 pm

Eric, thanks a lot ! :>

I have encountered another problem while using this stage.

1. Write Methods = Delete Rows

fatal error occur :
DB2_UDB_Enterprise_4: Unable to convert adapter spec ostrstream object with value of '' to ustring.

2. Write Methods = Load (Uses fast DB2 loader technology)

fatal error occur:
DB2_UDB_Enterprise_4,0: DB2 Load Binary Error.
DB2_UDB_Enterprise_4,0: SQLCODE = -3508; SQLSTATE= striptnulls fastparse binarynumerics packeddecimal reclen=22 dateformat="YYYY-MM-DD" timeformat="HH.MM.SS" timestampformat="YYYY-MM-DD-HH.MM.SS.UUUUUU" noheader codepage=819
DB2_UDB_Enterprise_4,0: SQL3508N Error in accessing a file or path of type "TEMP_FILE" during load or load query. Reason code: "1". Path: "/data/Scratch/APT_DB2_LOADMSG_db2gcf1u0_REMOTE/DB".
DB2_UDB_Enterprise_4,0: Failed opening fifo /data/Scratch/ordb219820806bba4c1_0 after 90 seconds: No such device or address
DB2_UDB_Enterprise_4,0: Operator's runLocally() failed
DB2_UDB_Enterprise_4,0: Operator terminated abnormally: runLocally did not return APT_StatusOk

Actually this file /data/Scratch/APT_DB2_LOADMSG_db2gcf1u0_REMOTE/DB can not be found, but /data/Scratch/APT_DB2_LOADMSG_db2gcf1u0 found.

I can use Write Methods = Write to load data, but failed in Methods = Load, why ?

Eric · Post by **Eric** » Tue Feb 01, 2005 5:34 am

1) Could this be a NULL value in a column of type ustring (Varchar) where the column is set to Not Nulls?

2) This could be that the DB2 instance Owner does not have permissions to the DataStage directories that are used during the Load.

OR

2) This could be due to User defined SQL being used that has an Error and thus is failing to be parsed by DB2 at runtime.

T42 · Post by **T42** » Tue Feb 01, 2005 2:00 pm

You must have a DB2 node set up on the DB2 server, with a number of configuration issues.

The guide for DB2 configuration for EE is incorrect. Contact Ascential for the updated copy of the DB2 installation guide for EE.

HSBCdev · Post by **HSBCdev** » Wed Feb 02, 2005 3:28 am

Eirc & T42,

thank you very much for your help, the information is so useful to me.

Besides, I can use this stage to connect to Local DB2 now, and what I want to ask is how to setup our server to connect to a remote DB2 ?

For the DB2 installation guide, I have contact Ascential, and waiting for reply, so would you please give more detail information on the setup step (connect to remote DB2) .

Eric · Post by **Eric** » Wed Feb 02, 2005 6:09 am

I don't beleive a remote connection as such is suppported. You must configure your local DB client to talk to the remote DB2 server. (This is a DB2 setup issue rather than a DataStage issue). DataStage then connects to the local client in the same way as you have already configured.

HSBCdev · Post by **HSBCdev** » Wed Feb 02, 2005 8:30 am

Eric, I have got ideas from your reply, many many thanks