DB2 Errors: SQL0204N name is and undefined name

This is a new format for me – Focusing on a single error message I’ve seen recently and probably frequently. It’s a short entry for this one. I’m not sure of the value of this. I can get most of this from the info center. But I think about when I search on things in other areas and want something in plain English … let me know what you think and if I should continue posts like this.

What this error looks like

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "DB2INST1.CATGRPDESC" is an undefined name.  SQLSTATE=42704

Does this error mean the command didn’t work

Yes – note the “N” on the end.

Common Causes

  1. A query or command tried to access an object that did not exist.
  2. The query or command did not specify the proper schema for the object.

Unlike Oracle and ORA-00942, as of DB2 9.7, this error is not returned if you don’t have permissions on an object. If you don’t have permissions on an object, you will get SQL0551N, SQL1060N or a similar very clear error message.

There are a number of other possible causes for this error, especially when dealing with Security Labels. See the Info Center link at the end of this post for more details.

Common Solutions

  1. A query or command tried to access an object that did not exist.
    • Usually this is simply a typo and you need to use the right table or object name
  2. The query or command did not specify the proper schema for the object.
    • Some of the engineers I work with refer to this as the “alias error”. They call it that, because for certain ids, we create aliases to the application’s core tables so that those ids don’t have to specify a schema name. If we miss one (most often on creation of a new object), the developers can get this message. Yes, we educate developers on using fully qualified object names, and that is sure the preference, but the WebSphere Commerce application does not use fully qualified names, so all documentation eliminates that as well, and developers get used to working without it

Info Center Link for This Error

http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00204n.html

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 544

9 Comments

  1. About 4 years late but this also occurs sometimes if the table is empty of values. Don’t know all the specs as I’ve just discovered it but for anyone looking, this may help.

      • I’m only a developer so I won’t be the best source of information. Working on a legacy program where there were simultaneous queries issued in one command and encountered this issue when one of those tables were empty. Didn’t explore it very deep so I can’t provide any more information at this time.

  2. Using python-sql:

    %sql select * from “schemaname”.tablename;

    It looks like the schema name needs to be in between double quotes.

    It worked for me.

    • Yep, I’m sure this is a common cause of this error in Python. If not in quotes, db2 assumes the schema name is all lower case, and also can’t handle spaces. With the quotes, db2 uses the schema exactly as provided, so my guess is that your schema name had upper or mixed case or a space in it.

  3. I have a question regarding this DB2 error code.
    I am using entity framework.
    Schema name is in upper case: TEST
    Table name Pascal case: MyTestTable
    Define Schema Name and Table Name in Model: [Table(“MyTestTable”, Schema = “TEST”)]
    When debugging I am receiving this error: “IBM.Data.Db2.DB2Exception (0x80004005): ERROR [42704] [IBM][DB2/NT64] SQL0204N “TEST.MYTESTTABLE” is an undefined name.”
    Note table name is in upper case, DB2 does not recognized the upper case table name.
    Can someone assist?

    • Db2 will generally assume upper case unless you quote the table name. You may need to surround the table name in single or double quotes if you’ve truly managed to create it using Pascal case. Alternately if this is new, you can create the table in all upper case, and then refer to it using any mixed case you like without quotes.

      Db2 has case sensitivity in odd ways that you may not expect if you’re used to some other RDBMSes

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.