DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704 when connecting to new Controller DB

Technote (troubleshooting)


Problem(Abstract)

When attempting to connect to or create a new Controller DB in a DB2 environment, The Controller 'Database Conversion Utility' throws an error that includes "DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=<UserID>.USER_TABLES".

Symptom

When the 'Create DB' button is pressed:


    Creating new empty Database
    Running DB Step: initial
    Running script batch: script/db2_createDB.batch
    Running script: script/db2/sequence_xdbtrickle_seq.sql
    Running script: script/db2/table_xdb00.sql
    ** ERROR: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=DECLARE
    sSql VARCHAR2(4000);
    BEGIN ;BEGIN-OF-STATEMENT;<create_proc>, DRIVER=3.57.82
    ** ERROR: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=<UserID>.USER_TABLES, DRIVER=3.57.82

When the 'Connect' button is pressed:
    ** ERROR: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=<UserID>.USER_TABLES, DRIVER=3.57.82


Cause

The USER_TABLES object does not exist in the DB2 Controller tablespace, because it was not created with the database running in Oracle Compatibility Mode.


"SQLCODE=-204, SQLSTATE=42704" is a DB2 error code indicating 'object does not exist'; in this case, it is indicating that either the USER_TABLES object is not present, or that the <UserID> user does not have access to read this. As USER_TABLES is an Oracle compatibility feature, the "SQLCODE=-204, SQLSTATE=42704" error referencing USER_TABLES often implies that Oracle Compatibility was not switched on when the Controller Tablespace was created.


Environment

Controller with DB2 database.

Diagnosing the problem

Use the command DB2SET to check the DB2 server's settings.


For example, if your DB2 database server is installed on Windows then:

  1. Click "Start - Programs - IBM DB2 - DB2COPY1 (Default) - Command Line Tools - Command Window"
  2. Type "db2set"

On a correctly-configured server, the settings should include the following:
DB2_DEFERRED_PREPARE_SEMANTICS=YES
DB2_COMPATIBILITY_VECTOR=ORA

Resolving the problem

Ensure that DB2 is set to run in Oracle Compatibility Mode before creating any Controller tablespaces.


Modify the DB2 database server to be Oracle compatible. Specifically, reconfigure it so that DB2_COMPATIBILITY_VECTOR=ORA.

  • TIP: This setting may have adverse consequences for other (non-Controller) software, so please check before making the change.

Steps:

Assuming your DB2 database server is hosted on Windows:

  1. Obtain some downtime (nobody using ANY databases hosted on the DB2 server)
  2. Shutdown Controller application server
  3. Logon to DB2 database server as an administrator
  4. Click "Start - Programs - IBM DB2 - DB2COPY1 (Default) - Command Line Tools - Command Window"
  5. Type the following: db2set DB2_COMPATIBILITY_VECTOR=ORA
  6. If necessary, also type: db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES
  7. Type: db2stop
  8. Type: db2start
  9. Restart Controller application server
  10. Re-create the necessary Tablespaces.

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Cognos Controller

Software version:

8.3, 8.4, 8.5, 8.5.1, 10.1, 10.1.1

Operating system(s):

Windows

Reference #:

1613531

Modified date:

2012-10-09

Translate my page

Machine Translation

Content navigation