DB2 SQL Error SQLCODE=-204 SQLSTATE=42704 (How to Fix)

The DB2 SQL error SQLCODE=-204 SQLSTATE=42704 points to an issue where a queried object is undefined in the DB2 subsystem. This error, applicable to different object types, signifies an undefined version of the object, often packages. The following sections contain further details of the error code, its cause, and remedies.

What Is the SQL Code =- 204 in DB2?

The SQLCODE 204 in DB2 is an error code that occurs when the object identified by the name in the query has no corresponding definition in the DB2 subsystem. Any object type can cause this error. The name token returned with the error message will look like:

name-of-procedure VERSION version-id

If you interpret the message for COMMENT or DROP, it indicates that the implicitly or explicitly specified version of the object is undefined. That is when the object is a package. And if there isn’t an explicit version-ID, the null version the statement tried to process is nonexistent.

If the statement runs on an accelerator, the name token might be referring to a table on the accelerator server, not a table on DB2.

What Is DB2 Error Code SQL State 42704?

Consider the following message.

ERROR: com.ibm.DB2.jcc.am.io: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=<UserID>.USER_TABLES, DRIVER=3.57.82

Here, the error occurred because either the USER_TABLES object is absent or the <UserID> does not have the required permission. In this case, the SQLCODE=-204, SQLSTATE=42704 refers to an Oracle Compatibility issue. 

Further interpretation leads to the conclusion that the Oracle Compatibility Mode was off during the creation of the Controller Tablespace or the database itself.

How Do I Fix SQLCODE 204?

The easiest way to fix SQLCODE -204 is to make sure Oracle Compatibility Mode is on before creating any controller tablespaces. Below are the steps to modify the DB2 database server to run in Oracle Compatibility mode.

1. Make sure the DB2 server is not hosting any databases during the fix. Then turn off the Controller application server to cut off connections to the DB2 database.

2. Get administrator access to the DB2 database server.

3. Select Start – Programs – IBM DB2 – DB2COPY1 (Default) – Command Line Tools – Command Window and type in the following.

DB2set DB2_COMPATIBILITY_VECTOR=ORA

4. Next, type the command below.

DB2set DB2_DEFERRED_PREPARE_SEMANTICS=YES

5. Restart the DB2 server by using the DB2stop and DB2start commands.

6. Delete the failed Controller application repo and create a new DB2 database as the repository database. 

7. Check if the issues got solved by starting the Controller application server again.

Conclusion

Before fixing the DB2 SQL error SQLCODE=-204 SQLSTATE=42704, you can check whether the DB2 server has the correct configurations. Use the DB2SET command to check the server settings. A properly configured server should have settings like DB2_DEFERRED_PREPARE_SEMANTICS=YES and DB2_COMPATIBILITY_VECTOR=ORA.