3

I'm using Python to access tables in DB2 10.1.0

I have a login account named foobar and a schema with the same name. I have a table named users under the schema.

When I'm logged in as foobar, I can run the following query successfully from the command line:

select * from users

I have a small Python script that I'm using to connect to the database. The script is:

#!/usr/bin/python

import pyodbc

if  __name__ == "__main__":

    accessString ="DRIVER={DB2};DATABASE=MYDATABASE;SERVER=localhost;UID=foobar; PWD=foobarish1;CURRENTSCHEMA=FOOBAR" 
    print accessString
    cnxn = pyodbc.connect(accessString , autocommit=True)
    cursor = cnxn.cursor()
    query = "SELECT * FROM USERS"
    cursor.execute(query)
    rows = cursor.fetchall()
    for row in rows:
        print 'Row data'
        print row[0]
    cursor.close()
    cnxn.close()

When I run the script, I get the following error:

('42S02', '[42S02] [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "FOOBAR.USERS" is an undefined name. SQLSTATE=42704\n (-204) (SQLExecDirectW)')

This usually means that the schema isn't defined. However, if I change the query in the script to:

VALUES CURRENT SCHEMA

the script runs successfully and it returns

FOOBAR

I've also tried adding the schema directly to the table name, making the query

SELECT * FROM FOOBAR.USERS

and I still get the same error.

Does anyone know how to fix this so I can query the user table? Your assistance and insight is appreciated.

closed as off-topic by mustaccio, RDFozz, Erik Darling, Mr.Brownstone, McNets Nov 29 '17 at 12:42

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, RDFozz, Erik Darling, Mr.Brownstone, McNets
If this question can be reworded to fit the rules in the help center, please edit the question.

  • An exact duplicate of stackoverflow.com/questions/23834390/… – mustaccio May 24 '14 at 19:27
  • @mustaccio Yes - I did it intentionally because folks on DBA section may not be on SO. Also, the problem could be coding or DB setup. – Jesuisme Jun 3 '14 at 10:14
  • I'd confirm that the table exists in the FOOBAR schema. Does select * from foobar.users work from command line? What does db2look -d mydatabase -t users -e output? Is the schema FOOBAR or for example "FOOBAR ", or maybe Foobar? (don't know if those trailing spaces matter, but seen them sometimes). And then of course there is db2 list tables for all command to list all tables (and their schemas)... – Toni Jun 6 '15 at 17:46
0

Maybe there is a problem with upper and lower case. I created a table using the following statement:

CREATE TABLE "FOOBAR"."Users" (col1 INTEGER)

If I try try to run on of the following statements at the CLP I get an error SQL -204

db2 "select * from db2root1.Users"

db2 "select * from db2root1.USERS"

db2 "select * from db2root1.users"

Always the error message SQL0204N "DB2ROOT1.USERS" is an undefined name. SQLSTATE=42704 is returned.

I would suggest to run the following statement and check the spelling of the table name.

select t.tabname from syscat.tables t where lower(t.tabname) like 'user%'

Some tools are creating the tables with a case sensitive table name. This may result in an SQL -204 in some scenarios.

In this case drop and recreate the table without any quotation marks.

Not the answer you're looking for? Browse other questions tagged or ask your own question.