Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

IBM Db2 net driver SQL error. Not finding table name?

Tags:

sql

db2

from this query:

select * from table

I get the following error messages:

Error: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=webuser.table, DRIVER=4.8.87
SQLState:  42704
ErrorCode: -204
Error: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-204;42704;webuser.table, DRIVER=4.8.87
SQLState:  56098
ErrorCode: -727

Any suggestions for how to investigate the problem is much appreciated.

like image 396
cmck23 Avatar asked Aug 24 '11 23:08

cmck23


People also ask

What does SQLCODE =- 206 mean?

The name of the object that could not be resolved. Possible reasons for this error include: The specified name is not a column of any of the source or target tables or views of the statement.

How do I fix a Db2 error?

Fixes. If you suspect that the problem is with Db2 for z/OS, ask your system programmer to help you search for any available fixes. For Db2 for z/OS, a fix is called a PTF (program temporary fix) . Potentially, someone else has already experienced the same problem and a PTF is available or will be available soon.

How do I fix error 805 in Db2?

Corrective action: Correct the collection ID of the entry in the PKLIST option and use the REBIND subcommand to rebind the application plan that is identified by plan-name . The location-name in the package list was not correct when the application plan that is identified by plan-name was bound.

Why can't DB2 find a table name under the Webuser schema?

At first glance, it seems that DB2 is not finding that table name under the webuser schema, or the schema of the current connected user does not match the schema for the table. Try: If that fails, then it probably does not exist under the webuser schema, so try to find out what schema it does exist under.

How to check for SQL error codes in DB2?

You can often find information for sql error codes in the IBM DB2 infocenter or on the DB2 UDB command line like so: db2 ? SQL0100 db2 ? SQLnnnnn where nnnnn is the error code.

Does DB2 only work with names?

Apparently at the package level, DB2 only works with the IDs and not the names. Thanks for contributing an answer to Stack Overflow! Please be sure to answer the question.

Where do I find the JDBC driver in DB2?

By default, the JDBC 1.X driver is on the class path. On Windows systems, look for the inuse file in the java12 directory in your DB2 installation root. If the file missing, you are using the JDBC 1.x driver. On operating systems such as AIX® or Linux®, check the class path for your data source.


2 Answers

At first glance, it seems that DB2 is not finding that table name under the webuser schema, or the schema of the current connected user does not match the schema for the table. Try:

select * from webuser.table

If that fails, then it probably does not exist under the webuser schema, so try to find out what schema it does exist under.

You can often find information for sql error codes in the IBM DB2 infocenter or on the DB2 UDB command line like so:

db2 ? SQL0100

You can look up any error this way:

db2 ? SQLnnnnn 

where nnnnn is the error code.

You can also often find information on sql error code from db2 by searching on google without including the - since google removes the term with a dash in front from the search results. So, search for sql code 204 and not sql code -204 .

You can usually find a list of all the tables under sysibm.tables or syscat.tables depending on what type of system (z/OS or UDB) you are running on, and also depending on if the db2 administrator has restricted access to those catalog tables.

If you are running against a UDB instance, try:

select tabschema, tabname from syscat.tables

I'm assuming of course that you are not expecting the table to be named table. I would think that is a restricted word under db2 or the SQL-92 standard.

If you happen to be running a stored procedure that runs that query, then you may not be seeing the true error from the query. Stored procedures often return a different error code than a query inside the stored procedure would, unless they are programmed to catch and return the sqlcode and sqlstate from the query itself.

Additionally, I see that you are getting a sqlcode of -727, with a message code of 2. The documentation for that says:

An error occurred during implicit system action type action-type . Information returned for the error includes SQLCODE sqlcode , SQLSTATE sqlstate and message tokens token-list . 

where the action-type is 2: implicit prepare of a cached dynamic SQL statement

This error information is less straightforward and more tuned for a Database Administrator. It may end up being useful but I would try the other suggestions first. It may mean that certain packages are not bound to the database (like the ones for your net driver) or that certain permissions in the database are not correct or a fixpack was not applied correctly and there are missing database procedures. However, it could also be caused by the -204 and just be a subsequent error.

like image 148
mwolfetech Avatar answered Sep 23 '22 09:09

mwolfetech


SQLCODE=-727, SQLSTATE=56098 may happen if there is a typo in one of the column names in the select statement.

like image 20
mp31415 Avatar answered Sep 24 '22 09:09

mp31415