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:


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"
    rows = cursor.fetchall()
    for row in rows:
        print 'Row data'
        print row[0]

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:


the script runs successfully and it returns


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


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.

  • An exact duplicate of stackoverflow.com/questions/23834390/…
    – mustaccio
    May 24, 2014 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, 2014 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, 2015 at 17:46

1 Answer 1


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


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.