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

Technote (troubleshooting)

Problem(Abstract)

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 server has "Enable operating system security for DB2 objects" enabled.

Environment

Controller Data Mart database hosted on DB2.

Diagnosing the problem

Scenario #1

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

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
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. Test.

Scenario #2
Either:

• Uninstall DB2 server, and re-install DB2 server - during the installation wizard do NOT enable "Enable operating system security for DB2 objects"
• or simply add the group "Everyone" to the local group "DB2USERS" on the DB2 server
  • For more information see separate IBM Technote #1504470

-----

DB2 테스트 하기 어렵네 휴... DB2 Express-C 10.1은 뭔가 이상한데... 9.7로 작업...

해결책은 Everyone을 DB2USERS에 추가

출처 : http://www-01.ibm.com/support/docview.wss?uid=swg21594790