pajama

DB2에서 오라클 호환모드를 사용할 때, PL/SQL에서 오라클 방식으로 VARCHAR ARRAY ( VARRAY )  타입의 데이터를 오라클 스타일로 초기화할 때 발생하는 오류에 대한 해결방법입니다. DB2에서 VARRAY 데이터 타입을 오라클 스타일로 초기화하려고 하면 함수로 인식해 SQL0440N 오류가 발생합니다.

Problem(Abstract)

A PL/SQL array constructor statement with the syntax as seen below, fails in DB2 with error SQL0440N 

For e.g.
v_List va1.t_Strings := va1.t_Strings('Harry', 'Ron', 'Hermione');
SQL0440N No authorized routine named routine-name of type routine-type having compatible arguments was found

Symptom

Cause

Diagnosing the problem

Resolving the problem

http://www-01.ibm.com/support/docview.wss?uid=swg21608791

Question

How to move DB2 instance and databases from one filesystem to another

Cause

This is just to show all the overall steps needed to move the instance and databases. This is one of the ways.

Answer

First, need to take a full off-line db2 backup of the databases.

Then preserve all the instance level and database level configurations.

Specially, the dbm config, database config, registry settings.

db2cfexp could be used to preserve the instance level configurations,

http://www-01.ibm.com/support/knowledgecenter/api/content/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002442.html

Then, drop the database and instance from current location.

Recreate instance in new location.

Update all the configurations as preserved earlier.

db2cfimp could be used to import back the instance level configurations,
http://www-01.ibm.com/support/knowledgecenter/api/content/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002441.html

Then perform a redirect restore of the databases under new instance if the new container paths needs to be defined. Though the underline filesystem is changed, if the exact same paths as the source location are present in the destination location then regular restore should work fine.

Just to reiterate that the database level configurations will be propagated as part of backup/restore process and need not have to be taken care separately unless some new paths have to be redefined in the destination locations, example active log directory, archive path etc.

http://www-01.ibm.com/support/docview.wss?uid=swg21572030&myns=swgimgmt&mynp=OCSSEPGG&mync=E

Problem(Abstract)

Creation of a new instance, by using 'db2icrt' command fails with,

DBI1295E The instance list could not be updated.

Symptom

Diagnosing the problem

http://www-01.ibm.com/support/docview.wss?uid=swg21640590

시스템

AIX 7100-02-02-1316 , "DB2 v10.5.0.3", "s140203"

AIX 7100-02-05-1415 , "DB2 v10.5.0.4", "special_32990

환경변수

(locale 관련)

LANG=ko_KR.UTF-8

LC_COLLATE="ko_KR.UTF-8"

LC_CTYPE="ko_KR.UTF-8"

LC_MONETARY="ko_KR.UTF-8"

LC_NUMERIC="ko_KR.UTF-8"

LC_TIME="ko_KR.UTF-8"

LC_MESSAGES="ko_KR.UTF-8"

LC_ALL=

TERM=xterm

위와 같은 환경에서 db2top을 실행했을 때 화면이 어그러지는 현상이 발생합니다.

환경변수 LANG을 en_US으로, TERM을 xterm, dtterm 등으로 설정했을 때 정상적으로 표시되었습니다.

IBM APAR에는 9.5버전에 대한 이슈만 나와있는데 10.5에서도 마찬가지인 것으로 보입니다.

http://www-01.ibm.com/support/docview.wss?uid=swg1IC73092

http://db2commerce.com/2014/07/01/scrambled-output-from-db2top/

http://www-01.ibm.com/support/docview.wss?uid=swg21458782

아래 내용은 DB2 10.5 환경의 UTF-8 코드셋 데이터베이스를 기준으로 합니다.

(eucKR 코드셋에서도 비슷하게 작동할 것으로 보입니다만 테스트가 필요합니다)

DB2에서 멀티바이트 문자에 LENGTH 함수를 사용하면 바이트 값을 리턴합니다.

UTF-8 인코딩에서 한글은 일반적으로 3바이트로 표현됩니다. (가 : ea b0 80)

DB2의 LENGTH 함수에서는 3을 리턴합니다.

10.5버전부터 LENGTH2, LENGTH4 와 같은 스칼라 함수를 지원합니다.

$ db2 "values length('가')"

1

-----------

          3

  1 record(s) selected.

$ db2 "values length2('가')"

1

-----------

          1

  1 record(s) selected.

$ db2 "values length4('가')"

1

-----------

          1

참조. 오라클 작동방식 (11.2 버전 기준)

오라클의 경우 LENGTH 함수 실행결과 글자길이값을 보여줍니다. 오라클에서도 LENGTH2, LENGTH4 함수가 있는데 역시 DB2와 동일하게 작동합니다. 원래는 ORACLE함수이나 DB2에서도 호환성 측면에서 추가된 것으로 보입니다. 유니코드 캐릭터셋의 DB에서는 LENGTH4 함수가 LENGTH와 동일한 결과값을 보여줍니다. (기타 캐릭터셋에서는 확인이 필요합니다)

# export LANG=KO_KR.UTF-8

# export NLS_LANG=NLS_LANG=AMERICAN_AMERICA.AL32UTF8

# sqlplus scott/tiger

SQL> create table utftest (a varchar(100));

Table created.

SQL> insert into utftest values ('한?글');

1 row created.

SQL> select * from utftest;

A

--------------------------------------------------------------------------------

한?글

SQL> select length(a) from utftest;

 LENGTH(A)

----------

         3

SQL> select length2(a) from utftest;

LENGTH2(A)

----------

         4

SQL>  select length4(a) from utftest;

LENGTH4(A)

----------

         3

SQL> insert into utftest values ('a한?글b');

1 row created.

SQL> select * from utftest;

A

--------------------------------------------------------------------------------

한?글

a한?글b

SQL> select length(a) from utftest;

 LENGTH(A)

----------

         3

         5

SQL> select length2(a) from utftest;

LENGTH2(A)

----------

         4

         6

SQL> select length4(a) from utftest;

LENGTH4(A)

----------

         3

         5

참조

http://www.utf8-chartable.de/unicode-utf8-table.pl

http://www.ibm.com/developerworks/data/library/techarticle/dm-0705nair/

Problem(Abstract)

You have multiple tables in your database that have changed extensively and you are experiencing a performance degradation. Reorg and runstats are recommended on these tables. You can create reorg and runstats scripts to make DB2® performance tuning easy.

Resolving the problem

http://www-01.ibm.com/support/docview.wss?uid=swg21444174&myns=swgimgmt&mynp=OCSSEPGG&mync=E

Question

Is it possible to prevent incoming database-connection attempts from application layer/driver from automatically activating the database.

Cause

Answer

http://www-01.ibm.com/support/docview.wss?uid=swg21673436&myns=swgimgmt&mynp=OCSSEPGG&mync=E

Technote (troubleshooting)

Technote (troubleshooting)

http://www-01.ibm.com/support/docview.wss?uid=swg21598094

Error description

• when there are applications in rollback or commit state, db2 get
  snapshot for locks on <db2_name> may receive sql1042c error.
  E.g.
  $ db2 get snapshot for locks on sample
  SQL1042C  An unexpected system error occurred.  SQLSTATE=58004
  Some other utility which relies on the service of monitor
  component could also be affected, e.g db2top. In db2top, using
  'U' option to collect lock information may also cause db2top
  exited with sql1042c error.
  The following records could be found in db2diag.log if turning
  on the database manager configuration parameter DIAGLEVEL to 4:
  2013-03-12-03.43.12.074851-240 I243248E595           LEVEL: Info
  PID     : 19365                TID : 46913030187328  KTID :
  xxxxxx
  PROC    : db2sysc
  INSTANCE: db2inst1             NODE : 000            DB   :
  SAMPLE
  APPHDL  : 0-21                 APPID:
  *LOCAL.db2inst1.130312073953
  AUTHID  : DB2INST1             HOSTNAME: host123
  EDUID   : 41                   EDUNAME: db2agent (instance)
  FUNCTION: DB2 UDB, database monitor, sqm_snap_appl_locks,
  probe:10
  DATA #1 : <preformatted>
  Snapshot cannot obtain lock information for application with app
  handle 13. Application in commit/rollback.
  The following records could be found in db2 trace:
  | | | | sqm___sqm_write_lock_waits entry [eduid 224497 eduname
  db2agent]
  | | | | sqm___sqm_write_lock_waits exit
  | | | sqm_snap_appl_locks data [probe 10]
  | | | sqm_snap_appl_locks exit [rc = 0x000004D2 = 1234]
  | | sqm___sqm_snap_db_locks exit [rc = 0x000004D2 = 1234]
  | | sqm___sqm_free_dbcb_list entry [eduid 224497 eduname
  db2agent]
  | | sqm___sqm_free_dbcb_list exit
  | sqm___sqlmonssagnt exit [rc = 0x000004D2 = 1234]
  | sqlm_buffer_postprocess entry [eduid 224497 eduname db2agent]
  | sqlm_buffer_postprocess data [probe 1]
  | sqlm_buffer_postprocess data [probe 2]
  | sqlm_buffer_postprocess exit
  | sqm___sqlmfree entry [eduid 224497 eduname db2agent]
  | sqm___sqlmfree exit [rc = 0xFFFFFFFF = -1]
    sqm___sqlmonssbackend exit [rc = 0xFFFFFFFF = -1]
  

Local fix

• using db2pd -locks -db <db_name> to collect lock information
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * when there are applications in rollback or commit state, db2 *
  * get                                                          *
  * snapshot for locks on <db2_name> may receive sql1042c error. *
  *                                                              *
  *                                                              *
  *                                                              *
  * E.g.                                                         *
  *                                                              *
  *                                                              *
  *                                                              *
  * $ db2 get snapshot for locks on sample                       *
  *                                                              *
  * SQL1042C  An unexpected system error occurred.               *
  * SQLSTATE=58004                                               *
  *                                                              *
  *                                                              *
  * Some other utility which relies on the service of monitor    *
  *                                                              *
  * component could also be affected, e.g db2top. In db2top,     *
  * using                                                        *
  * 'U' option to collect lock information may also cause db2top *
  *                                                              *
  * exited with sql1042c error.                                  *
  *                                                              *
  *                                                              *
  *                                                              *
  * The following records could be found in db2 trace:           *
  *                                                              *
  *                                                              *
  *                                                              *
  * | | | | sqm___sqm_write_lock_waits entry [eduid 224497       *
  * eduname                                                      *
  * db2agent]                                                    *
  *                                                              *
  * | | | | sqm___sqm_write_lock_waits exit                      *
  *                                                              *
  * | | | sqm_snap_appl_locks data [probe 10]                    *
  *                                                              *
  * | | | sqm_snap_appl_locks exit [rc = 0x000004D2 = 1234]      *
  *                                                              *
  * | | sqm___sqm_snap_db_locks exit [rc = 0x000004D2 = 1234]    *
  *                                                              *
  * | | sqm___sqm_free_dbcb_list entry [eduid 224497 eduname     *
  *                                                              *
  * db2agent]                                                    *
  *                                                              *
  * | | sqm___sqm_free_dbcb_list exit                            *
  *                                                              *
  * | sqm___sqlmonssagnt exit [rc = 0x000004D2 = 1234]           *
  *                                                              *
  * | sqlm_buffer_postprocess entry [eduid 224497 eduname        *
  * db2agent]                                                    *
  * | sqlm_buffer_postprocess data [probe 1]                     *
  *                                                              *
  * | sqlm_buffer_postprocess data [probe 2]                     *
  *                                                              *
  * | sqlm_buffer_postprocess exit                               *
  *                                                              *
  * | sqm___sqlmfree entry [eduid 224497 eduname db2agent]       *
  *                                                              *
  * | sqm___sqlmfree exit [rc = 0xFFFFFFFF = -1]                 *
  *                                                              *
  * sqm___sqlmonssbackend exit [rc = 0xFFFFFFFF = -1]            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 db2_v97fp6                                    *
  ****************************************************************
  

Problem conclusion

• The fix will be included in DB2 db2_v97fp6
  

Temporary fix

• using db2pd -locks -db <db_name> to collect lock information
  

http://www-01.ibm.com/support/docview.wss?uid=swg1IC78490