error in running a job with DB2 UDB Stage

caterin_huang · Post by **caterin_huang** » Wed Feb 10, 2010 8:09 pm

hi all,
refering on my previous thread at viewtopic.php?t=132049&postdays=0&postorder=asc&start=0

I still get an error message in creating an ETL Job in Datastage 8.1 for DB2 UDP Stage.

When I compile the job, it has no error. But when I try to run the job, I got an error. And when I investigate the Datastage Director, it says:

Code: Select all

DB2_UDB_Enterprise_12: Error Idx = 1;
DB2Driver Embedded SQL message: ;
sqlcode = -1390;
sqlstate =

Code: Select all

DB2_UDB_Enterprise_12: Failed to connect to the database

for your information, the job I created is a very simple job which extracting a DB2 table to a sequential text file. and also, when I tried to view data from the DB2 table, it got an error like this:

Code: Select all

Error executing View Data command:##E IIS-DSEE-TNDB-00534 02:11:41(006) <DB2_UDB_Enterprise_12> Error Idx = 1;

and the server command output is:

Code: Select all

##E IIS-DSEE-TNDB-00534 02:11:41(006) <DB2_UDB_Enterprise_12> Error Idx = 1;
DB2Driver Embedded SQL message: ;
sqlcode = -1390;
sqlstate =      
##E IIS-DSEE-TNDB-00000 02:11:41(007) <DB2_UDB_Enterprise_12> Failed to connect to the database
##E IIS-DSEE-TOPK-00007 02:11:41(011) <_PEEK_IDENT_> Input dataset does not have field: "PRODUCT_LINE_CODE".
##E IIS-DSEE-TOPK-00007 02:11:41(012) <_PEEK_IDENT_> Input dataset does not have field: "PRODUCT_TYPE_CODE".
##E IIS-DSEE-TOPK-00007 02:11:41(013) <_PEEK_IDENT_> Input dataset does not have field: "PRODUCT_CODE".
##E IIS-DSEE-TFSR-00019 02:11:41(015) <main_program> Could not check all operators because of previous error(s)
##E IIS-DSEE-TCOS-00029 02:11:41(022) <main_program> Creation of a step finished with status = FAILED.

and the setting I use in the stage is can be seen in this image:

I wish that all of the information is clear enough for anybody willing to help. if it's not enough, I will provide further.

thanks very much in advance,
Caterin[/url]

ray.wurlod · Post by **ray.wurlod** » Wed Feb 10, 2010 8:50 pm

The main error is that DataStage was unable to connect. Is db2inst3 your default database connection? (What values do DB2INSTANCE, APT_DBNAME and APT_DB2INSTANCE_HOME environment variables have?)

The error message suggests that the Columns tab contains column names other than those in the STAGING.PRODUCT_HIERARCHY table. However, if DataStage was unable to connect to the database, this may not be a genuine error - simply that DataStage was unable to verify the metadata.

chulett · Post by **chulett** » Wed Feb 10, 2010 9:28 pm

Perhaps, with a little extrapolation, this can help.

chulett · Post by **chulett** » Wed Feb 10, 2010 9:33 pm

I'd also be curious if this is the first time ever you're attempting to use the stage or you've used it in the past and the issue is just with this connection? For the former, I would wonder if all of the 'configuration procedures' have been followed that the documentation mentions must be done before the stage can be used for the first time.

See the Connectivity Guide for IBM DB2 Databases pdf for specifics, if you haven't already.

caterin_huang · Post by **caterin_huang** » Wed Feb 10, 2010 10:38 pm

ray.wurlod wrote:The main error is that DataStage was unable to connect. Is db2inst3 your default database connection? (What values do DB2INSTANCE, APT_DBNAME and APT_DB2INSTANCE_HOME environment variables have?)

...

hi,
the value for

APT_DB2INSTANCE_HOME is: /home/db2inst3/
APT_DBNAME is: STAGING

but I can't locate the DB2INSTANCE environment variable. where is it located in Datastage?

Thanks very much,
Caterin

caterin_huang · Post by **caterin_huang** » Wed Feb 10, 2010 10:40 pm

chulett wrote:I'd also be curious if this is the first time ever you're attempting to use the stage or you've used it in the past and the issue is just with this connection? For the former, I would wonder if all of the 'configuration procedures' have been followed that the documentation mentions must be done before the stage can be used for the first time.

See the Connectivity Guide for IBM DB2 Databases pdf for specifics, if you haven't already.

yes, this is my first time using the stage. Where can I get the documentation? I just search it in google, and there is no free information about it. would anyone kindly to share the pdf?

Thanks in advance,
Caterin

ray.wurlod · Post by **ray.wurlod** » Thu Feb 11, 2010 4:46 am

DB2INSTANCE is not part of DataStage unless you make it so (for example by including it into the dsenv script).

chulett · Post by **chulett** » Thu Feb 11, 2010 7:25 am

All of the documentation installs on every client pc.

caterin_huang · Post by **caterin_huang** » Mon Feb 15, 2010 3:38 am

Hi,
actually until now,I havent got the problem solved. But i'm curious about Should I install db2 client at the datastage server to make me enable to making a job using DB2 stage?

ray.wurlod · Post by **ray.wurlod** » Mon Feb 15, 2010 1:40 pm

Yes, but there's still more that you must do, including bind. There is a manual on DB2 connectivity - reading it will benefit your understanding.

caterin_huang · Post by **caterin_huang** » Mon Feb 15, 2010 9:23 pm

Hi,
I have followed all the step on the DB2 Connectivity guide. But I'm still facing the same error. For your information, my DB2 server is located at the same local with the Datastage server. But should I install a DB2 Client there? And isn't it when we install a DB2 server at a machine, the client will be automatically installed? I can connect to the DB2 using the datastage user. But still, I'm facing the same error.. I'm so stucked of these. Is there another suggestion?

Thanks very much,
Caterin

caterin_huang · Post by **caterin_huang** » Mon Feb 15, 2010 9:47 pm

Hi,
Does it need a DB2 database plug in to use the DB2 API and DB2 Enterprise stage in datastage 8.1?
If it is, where can I find it? Is it included when we buy the IBM Datastage 8.1 Enterprise Edition Product?

Thanks very much
Caterin

chulett · Post by **chulett** » Tue Feb 16, 2010 6:31 am

Contact your official support provider, get some one-on-one hands on help. Or just have them do it for you and explain / document what all the steps were so someone there can do it the next time this comes up.

caterin_huang · Post by **caterin_huang** » Tue Feb 16, 2010 9:22 am

chulett wrote:Contact your official support provider, get some one-on-one hands on help. Or just have them do it for you and explain / document what all the steps were so someone there can do it the next time this comes up.

unfortunately, the official support in my country is not really quick and give a good response. that's why we search the answer to people at forums. Who knows if they could help us better. Thanks anyway for the suggestion.

Hmm, but do you mind if letting me know do I need to install extra db2 database plug-in before I use a DB2 API stage?

Thanks very much,
Caterin

chulett · Post by **chulett** » Tue Feb 16, 2010 12:48 pm

If I had any hands-on DB2 experience, I would be more than happy to help. However, I do not so can only point you to the documentation and hope that others who have actually done this join in the conversation.

I have no idea what you mean by "install extra db2 database plug-in". All you should need is the DB2 client software and the configuration steps the documentation lists.