Accessing DB2 (iSeries) at AS400 from DataStage server 8.1

braj · Post by **braj** » Wed Apr 13, 2011 3:14 pm

Hi

We have a requirement wherein we need to access iSeries (DB2) at As400 end from DataSatge. We have got iSeries Database catalogued on DataStage server.

DS can use DB2 API stage but we have to create this job as Generic job which will have RCP turned on.

We tried with DB2 Connector Stage but it failed with below error:
QLFetch reported: SQLSTATE = HY010: Native Error Code = -99,999: Msg = [IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=HY010 (CC_DB2DBStatement::dbsFetch, file CC_DB2DBStatement.cpp, line 1,510)

I tried to search it but could not find the solution for this.

Any help would really be appreciated.

Thanks
Brajesh.

antonyraj.deva · Post by **antonyraj.deva** » Thu Apr 14, 2011 12:59 am

Check the code page setting of the DB2/400 with your DBA. Couldn't remember the exact code set to be used at the moment.

Also try using the ODBC enterprise stage for the extract. If that also fails then it is good to look into the .odbc.ini file once to see if it is properly configured.

ppgoml · Post by **ppgoml** » Sun Apr 17, 2011 8:38 pm

Hi Brajesh,

I now can confirm that AS400/DB2 is not support by db2 connector at the moment, so you should go with odbc or db2 api stage.

BI-RMA · Post by **BI-RMA** » Tue Dec 13, 2011 11:21 am

I tried to connect to the iSeries from a DB2 Connector Stage in Version 8.5. We catalogued the iSeries on db2inst1 and provided "db2inst1" for property instancename.

We had to provide a View SYSIBMADM.ENV_SYS_INFO on the iSeries, which simply provides the same values as the same view within XMETA.

Afterwards I received warnings concerning a problem to set the LOCK WAIT Mode:

"DB2_Connector_5: Unable to set Lock Wait mode."
There is another message:
"DB2_Connector_5: DB2 reported: SQLSTATE = 42601: Native Error Code = -104: Msg = [IBM][CLI Driver][AS] SQL0104N An unexpected token "LOCK" was found following "". Expected tokens may include: "DEGREE". SQLSTATE=42601", but this just informational.

Nonetheless, the actual data selected from the database looks fine.

I'll try to find out whether it is possible to stop DataStage from trying to set a specific LOCK WAIT Mode on the iSeries.

rameshrr3 · Post by **rameshrr3** » Tue Dec 13, 2011 11:29 am

I've used an ODBC Connector Stage succesfully to connect to Db2/AS400 instances , and no warnings in job logs , no issues ,performance is pretty decent .. Try using that if DB2 connector doesnt work for you right now ..

HTH

BI-RMA · Post by **BI-RMA** » Wed Dec 14, 2011 4:49 am

That is a nice suggestion, but unfortunately the Connectormigrationtool provided with 8.5 will not give You the option to migrate a DB2_UDB_API_16-Stage to the ODBC-Connector Stage but opt for migration to the DB2-Connector-Stage automatically instead. That would mean we would have to change about 1000 jobs manually to do the migration. I do not think we want to do that.

There is probably going to be enough hassle with the fact, that the DB2-API-Stage identified column-names by position in SQL-statements and the connectors need explicit naming.

rameshrr3 · Post by **rameshrr3** » Thu Dec 15, 2011 10:08 am

Connectormigrationtool provided with 8.5 will not give You the option to migrate a DB2_UDB_API_16-Stage to the ODBC-Connector Stage but opt for migration to the DB2-Connector-Stage automatically instead.

Duh.. Im confused , who is the original poster of this thread ? or are you the same individual with a different ID or just hijacked it ? nowhere in the problem statement was the requirement to automate migration stated , .. I do Hope you find a good way out of the situation or find a way to continue using DB2/API stage ..

BI-RMA · Post by **BI-RMA** » Thu Dec 15, 2011 10:45 am

OK. You've got a point there. My problem is currently being scrutinized by the official support provider.

I just mentioned our problems here because others may be facing the same situation. Using the ODBC-connector for new jobs is definitely an option.