DB2 Query Error SQL0204N Even With The Schema Defined
Question: I'm using pyodbc to access 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
    

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

EDIT: 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.


Answer: I had the same issue and solved it by setting the SCHEMA explicit by query:

    
    
     SET CURRENT SCHEMA foobar
    

