DSXchange

We have done extensive search on this forum and have done all the set up accordingly. We are trying to set up DataStage 7.5A to connect to DB2 mainframe using DB2Connect V8 on the DataStage server. We have setup the dsenv file to include the following lines:

DB2DIR=/usr/opt/db2_08_01; export DB2DIR
DB2INSTANCE=cae1adm; export DB2INSTANCE
INSTHOME=/home/cae1adm; export INSTHOME
THREADS_FLAG=native; export THREADS_FLAG
LIBPATH=$LIBPATH:$DB2DIR/lib:$INSTHOME/sqllib/lib
PATH=$PATH:$INSTHOME/sqllib/bin:$INSTHOME/sqllib/adm:$INSTHOME/sqllib/misc

We can connect to the DB2 database using the db2 command and were able to select, insert, update, delete to a table.

We could also view the data from the DB2 API stage in Designer but the job failed to run with the following error:

APT_CombinedOperatorController,0: Fatal Error: Fatal: [IBM][CLI Driver][DB2] SQL0440N No authorized routine named "SQLTABLES" of type "" having compatible arguments was found. SQLSTATE=42884
SQLTables: Error retrieving system catalog information for requested tables(s).

Any help would be greatly appreciated.

Thanks

SQLTables is a function used by the API to retrieve a list of available tables in a database. Looks like, perhaps, its name is being translated to upper case by the (?) database server or the (?) calling mechanism. Or maybe there is a system function that you don't have privilege to execute. Check with your DBA.

This seems to be a DB2 version issue. Looks like u are using DB2 version8 as the client. What is the DB2 server version?
We had seen similar messages when the DB2 client version was 8 and the DB2 server version was still 7.
U need to talk to ur DB2 DBAs to get this resolved.

bmadhav wrote:This seems to be a DB2 version issue. Looks like u are using DB2 version8 as the client. What is the DB2 server version?
We had seen similar messages when the DB2 client version was 8 and the DB2 server version was still 7.
U need to talk to ur DB2 DBAs to get this resolved.

Thanks for your reply. The DB2 database version was 7.X on a mainframe. I will forward the information to our DBA team. They mentioned that the Stored Procedure option was not installed on the mainframe. Do you think this is the reason why the SQLTABLES object was not available?

It could be a stored procedure issue as well, as DS is retrieving the metadata with stored procedure calls.

But normally you then would also not be able to view data or import DB2 metadata.

Klaus

We cannot use the DSDB2 plug-in to import table definitions from the DB2 database either. We got the "Unable to obtain list of available tables from database "XXX"" message. I certainly hope once the SQLTABLES issue is resolved, it would also solve my table definition import issue.

Thanks

Well, that really sounds like your DB2 mainframe admins shall install the stored procedures option


Klaus

Klaus Schaefer wrote:Well, that really sounds like your DB2 mainframe admins shall install the stored procedures option


Klaus

After installing the stored procedure option and adjusted the privileges, the issues have been resolved. We can now import table defs as well as using the DB2/UDB API stage.

Thanks