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.
The following is based on DB2 server running on Windows:
- Click "Start - Programs - IBM DB2 - DB2COPY1 (Default) - Command Line Tools - Command Window"
- 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.
- TIP: For advice on how to do this, see separate IBM Technote #1570572.
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
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
Related Information
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21594790