DB2 issue with SQLTABLES

cnguyen · Post by **cnguyen** » Wed Jun 21, 2006 2:58 pm

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

ray.wurlod · Post by **ray.wurlod** » Wed Jun 21, 2006 4:37 pm

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.

bmadhav · Post by **bmadhav** » Wed Jun 21, 2006 8:48 pm

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.

cnguyen · Post by **cnguyen** » Thu Jun 22, 2006 9:01 am

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?

Klaus Schaefer · Post by **Klaus Schaefer** » Thu Jun 22, 2006 9:07 am

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

cnguyen · Post by **cnguyen** » Thu Jun 22, 2006 9:14 am

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

Klaus Schaefer · Post by **Klaus Schaefer** » Thu Jun 22, 2006 9:18 am

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

Klaus

cnguyen · Post by **cnguyen** » Thu Jun 22, 2006 11:18 pm

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

DSXchange

DB2 issue with SQLTABLES

DB2 issue with SQLTABLES

Solved