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

Dev/DB 2012. 7. 19. 16:14

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


저작자표시 비영리 동일조건

'Dev > DB' 카테고리의 다른 글

[DB2] order by sum()  (0) 2012.07.20
[DB2] fetch-first-clause  (0) 2012.07.20
[DB2] SQLCODE=-613, SQLSTATE=54008  (0) 2012.07.20
[DB2] Export to relational database fails with SQL error: SQLCODE=-286, SQLSTATE=42727  (0) 2012.07.19
Posted by pwsunf
,

티스토리 하위 카테고리 펼치기

Information 2012. 5. 22. 16:50

티스토리 관리자 - HTML/CSS 편집 - skin.html 의 제일 하단에 아래의 스크립트 추가 :D


</s_t3>
<script type="text/javascript">try{expandTree();}catch(e){}</script>
</body>
</html>  


저작자표시 비영리 동일조건
Posted by pwsunf
,

티스토리툴바