IBM Support

"...SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=<USERNAME>.USER_TABLES, DRIVER=3.59.81" when using "Database Conversion Utility"

Troubleshooting


Problem

Administrator creates a new DB2 database, which is going to be the Data Mart database. Administrator logs onto the Controller application server, and launches "Controller Configuration". Inside "Database Connections" administrator creates a connection to the new Data Mart target database. Administrator clicks green "Play" button to launch "Database Conversion Utility" (DbConv.exe). Administrator clicks "Connect". User receives error.

Symptom

** ERROR: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=FASTNET.USER_TABLES, DRIVER=3.59.81

Cause

There are several possible causes for this error:

  • Scenario #1 - DB2 database server has not been configured to be Oracle-compatible.
  • Scenario #2 - DB2 database client has "Enable operating system security for DB2 objects" enabled.
  • Scenario #3 - DB2 database has not been created correctly.

Environment

Controller Data Mart database hosted on DB2.

Diagnosing The Problem

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

Steps:
The following is based on DB2 server running on Windows:
  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

Scenario #1
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 (to ensure that there are no connections to the DB2 database)

    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 (if this has not already been done in the past), also type:
    db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES

    7. Stop the DB2 server by running the following command:
    db2stop

    8. Start the DB2 server by running the following command:
    db2start

    9. Delete the old (bad) Controller application repository database (the one that failed earlier)
    10. Create a new DB2 database, to be your Controller application repository database
    • TIP: For advice on how to do this, see separate IBM Technote #1570572.

    11. Start the Controller application server
    12. Re-try.

Scenario #2
Either:
(1) Uninstall DB2 client (from the Controller application server), and re-install DB2 client - during the installation wizard do NOT enable "Enable operating system security for DB2 objects"
or (2) simply add the group "Everyone" to the Windows security local group "DB2USERS" on the Controller application server
    • For more information see separate IBM Technote #1504470

Scenario #3
Delete the 'bad' database, and recreate the DB2 database using the correct settings.
  • TIP: For full details, see separate IBM Technote #1570572

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.1.1;10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21594790

Page Feedback