IBM DB2 UDB Blog Center

First log archive method                 (LOGARCHMETH1) = OFF
Options for logarchmeth1                  (LOGARCHOPT1) =

  Database Connection Information

Database server        = DB2/LINUXX8664 9.7.4
SQL authorization ID   = INST97
Local database alias   = SAMPLE

First log archive method                 (LOGARCHMETH1) = DISK:/instance/inst97/archive/
Options for logarchmeth1                  (LOGARCHOPT1) =

$ db2 backup db sample to ~/archive compress

Backup successful. The timestamp for this backup image is : 20120423104901

$ db2 drop db sample

DB20000I  The DROP DATABASE command completed successfully.

$ db2 restore db sample from ~/archive

DB20000I  The RESTORE DATABASE command completed successfully.

$ db2 connect to sample

SQL1117N  A connection to or activation of database "SAMPLE" cannot be made
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

$ db2 restore db sample from ~/archive

                                Rollforward Status

Input database alias                   = sample
Number of nodes have returned status   = 1

Node number                            = 0
Rollforward status                     = DB  pending
Next log file to be read               = S0000001.LOG
Log files processed                    =  -
Last committed transaction             = 2012-04-23-02.09.42.000000 UTC

                                 Rollforward Status

Input database alias                   = sample
Number of nodes have returned status   = 1

Node number                            = 0
Rollforward status                     = not pending
Next log file to be read               =
Log files processed                    =  -
Last committed transaction             = 2012-04-23-02.09.42.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.

                                Rollforward Status

Input database alias                   = sample
Number of nodes have returned status   = 1

Node number                            = 0
Rollforward status                     = not pending
Next log file to be read               =
Log files processed                    =  -
Last committed transaction             = 2012-04-23-02.09.42.000000 UTC

$ db2 rollforward db sample stop

SQL1276N  Database "SAMPLE" cannot be brought out of rollforward pending state
until roll-forward has passed a point in time greater than or equal to
"2012-04-23-02.20.40.000000 UTC", because node "0" contains information later
than the specified time

$ db2 rollforward db sample query status

                                Rollforward Status

Input database alias                   = sample
Number of nodes have returned status   = 1

Node number                            = 0
Rollforward status                     = DB  working
Next log file to be read               = S0000000.LOG
Log files processed                    =  -
Last committed transaction             = 2012-04-23-02.20.40.000000 UTC

$ db2 connect to sample

SQL1117N  A connection to or activation of database "SAMPLE" cannot be made
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

항목

MTK

DMT

Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:06:46 -- Date 03/12/2012 13:17:07

Dynamic Cache:
Current Memory Used           912084
Total Heap Size               1271398
Cache Overflow Flag           0
Number of References          184
Number of Statement Inserts   26
Number of Statement Deletes   8
Number of Variation Inserts   18
Number of Statements          18

Dynamic SQL Statements:
Address            AnchID StmtUID    NumEnv     NumVar     NumRef     NumExe     Text
0x00002B8015F1FD60 111    1          1          1          1          1          SELECT POLICY FROM
    SYSTOOLS.POLICY WHERE MED='DB2TableMaintenanceMED' AND DECISION='TableRunstatsDecision' AND NAME='TableRunstatsPolicy'
0x00002B8015E4F440 164    1          1          1          3          3          SELECT COLNAME, TYPENAME FROM
     SYSCAT.COLUMNS WHERE TABNAME='POLICY' AND TABSCHEMA='SYSTOOLS'
0x00002B8015F2CB80 167    1          1          1          1          1          SELECT IBM.TID, IBM.FID FROM
    SYSIBM.SYSTABLES AS IBM, SYSTOOLS.HMON_ATM_INFO AS ATM WHERE ATM.STATS_FLAG <> 'Y' AND IBM.TYPE IN ( 'S', 'T' ) AND
    ATM.CREATE_TIME = IBM.CTIME AND ATM.SCHEMA = IBM.CREATOR AND ATM.NAME = IBM.NAME ORDER BY IBM.FID, IBM.TID WITH UR
0x00002B8015E423C0 185    1          1          1          1          1          select * from emp
0x00002B8015F279A0 332    1          1          1          1          1          UPDATE SYSTOOLS.HMON_ATM_INFO
    AS ATM SET STATS_FLAG = 'N', REORG_FLAG = 'N' WHERE (ATM.SCHEMA, ATM.NAME) IN   (SELECT IBM.CREATOR,           IBM.NAME
    FROM SYSIBM.SYSTABLES AS IBM    WHERE IBM.TYPE = 'N' )
0x00002B8015EF7400 340    1          1          1          2          2          SELECT TABNAME FROM
    SYSCAT.TABLES  WHERE TABNAME='HMON_ATM_INFO' AND  TABSCHEMA='SYSTOOLS'
0x00002B8015E8CFE0 450    1          1          1          3          3          SELECT TRIGNAME FROM
    SYSCAT.TRIGGERS WHERE TABNAME='POLICY' AND TABSCHEMA='SYSTOOLS'
0x00002B8015F01E80 462    1          1          1          156        156        SELECT CREATE_TIME FROM
    SYSTOOLS.HMON_ATM_INFO WHERE SCHEMA = ? AND NAME = ? FOR UPDATE
0x00002B8015E469A0 513    1          1          1          1          1          select * from staff
0x00002B8015E4B640 580    1          1          1          3          3          CALL
    SYSPROC.SYSINSTALLOBJECTS('POLICY','V','','')
0x00002B8015F1B4E0 639    1          1          1          1          1          DELETE FROM
    SYSTOOLS.HMON_ATM_INFO AS ATM WHERE NOT EXISTS ( SELECT * FROM SYSIBM.SYSTABLES AS IBM WHERE ATM.NAME = IBM.NAME AND
    ATM.SCHEMA = IBM.CREATOR AND ATM.CREATE_TIME = IBM.CTIME ) WITH UR
0x00002B8015F23D80 734    1          1          1          1          1          UPDATE SYSTOOLS.HMON_ATM_INFO
    AS ATM SET ATM.STATS_FLAG = 'Y'
0x00002B8015E91DC0 735    1          1          1          2          2          CALL SYSINSTALLOBJECTS(
    'DB2AC', 'V', NULL, NULL )
0x00002B8015F016E0 766    1          1          1          1          1          SELECT CREATOR, NAME, CTIME
    FROM SYSIBM.SYSTABLES WHERE TYPE='T' OR TYPE='S' OR TYPE='N' WITH UR
0x00002B8015E91160 809    1          1          1          3          3          SET CURRENT LOCK TIMEOUT 5
0x00002B8015EFF380 876    1          1          1          1          1          UPDATE SYSTOOLS.HMON_ATM_INFO
    SET STATS_LOCK = 'N', REORG_LOCK = 'N'
0x00002B8015EFB680 886    1          1          1          2          2          SELECT TABNAME FROM
    SYSCAT.TABLES  WHERE TABNAME='HMON_COLLECTION' AND  TABSCHEMA='SYSTOOLS'
0x00002B8015F2EF00 1014   1          1          1          1          1          SELECT POLICY FROM
    SYSTOOLS.POLICY WHERE MED='DB2CommonMED' AND DECISION='NOP' AND NAME='CommonPolicy'

Dynamic SQL Environments:
Address            AnchID StmtUID    EnvID      Iso QOpt Blk
0x00002B8015F1FF20 111    1          1          CS  5    B
0x00002B8015E4F5C0 164    1          1          CS  5    B
0x00002B8015F2CDA0 167    1          1          CS  5    B
0x00002B8015E42500 185    1          1          CS  5    B
0x00002B8015F27BA0 332    1          1          CS  5    B
0x00002B8015EF7580 340    1          1          CS  5    B
0x00002B8015E8D160 450    1          1          CS  5    B
0x00002B8015F174A0 462    1          1          CS  5    B
0x00002B8015E46AE0 513    1          1          CS  5    B
0x00002B8015E4B7A0 580    1          1          CS  5    B
0x00002B8015F1B6C0 639    1          1          CS  5    B
0x00002B8015F23EE0 734    1          1          CS  5    B
0x00002B8015EF3CA0 735    1          1          CS  5    B
0x00002B8015F01860 766    1          1          CS  5    B
0x00002B8015E912A0 809    1          1          CS  5    B
0x00002B8015EFF4E0 876    1          1          CS  5    B
0x00002B8015EFB800 886    1          1          CS  5    B
0x00002B8015F2F080 1014   1          1          CS  5    B

Dynamic SQL Variations:
Address            AnchID StmtUID    EnvID      VarID      NumRef     Typ Lockname                   Val Insert Time                Sect Size

!db2 "connect to sample" ;

!db2 "quiesce db immediate force connections" ;

!db2 "terminate" ;

!db2 "backup db sample to 'C:\'" ;

!db2 "connect to sample" ;

!db2 "unquiesce db" ;

!db2 "terminate" ;

CREATE TABLE "INST97 "."T1" (

"C1" TIMESTAMP(0) ,

"C2" TIMESTAMP )

IN "USERSPACE1" ;

테이블 삭제

db2 drop table t1

오라클 호환성 제거

db2set DB2_COMPATIBILITY_VECTOR=

db2stop

db2start

테이블 생성

db2 "create table t1(c1 date, c2 timestamp)"

테이블 DDL문 생성

db2look -d test -e -z "INST97" -t "T1"

결과 확인

CREATE TABLE "INST97 "."T1" (

"C1" TIMESTAMP(0) ,

"C2" TIMESTAMP )

IN "USERSPACE1" ;

db2 "select * from t1"

C1                           C2

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

2011-10-26-16.47.38   2011-10-26-16.47.38.548792

CREATE TABLE "INST97 "."T1" (

"C1" DATE ,

"C2" TIMESTAMP )

IN "IBMDB2SAMPLEREL" ;

C1              C2

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

2011-10-26   2011-10-26-16.57.49.963273

C1          C2          C3_SUM
----------- ----------- ------------
          1           1 A,B,C
          1           2 D
          1           3 E

select a.c1, a.c2, a.c5||','|| b.c3 c3
from t1 b,
(select a.c1, a.c2, a.c3, t1.c3 c4, a.c3 || ',' || t1.c3 c5
from t1,
    (select a.c1, a.c2, b.c3
           from (select c1,c2,count(c3) cnt from t1 group by c1, c2 having count(c3) > 1 ) a,
                (select c1,c2,c3 from t1)b
     where a.c1=b.c1 and a.c2=b.c2 fetch first 1 rows only) a
where a.c1 = t1.c1 and a.c2 = t1.c2 and a.c3 <> t1.c3  fetch first 1 rows only) a
where a.c1=b.c1 and a.c2=b.c2 and b.c3 <> a.c3 and b.c3 <> a.c4

C1          C2          C3
----------- ----------- -----------
          1           2 D
          1           3 E
          1           1 A,B,C

  3 record(s) selected.

Tracking Memory on: 2011/12/26 at 15:35:25

Memory for instance

   Other Memory is of size 30212096 bytes
   FCMBP Heap is of size 15859712 bytes
   Database Monitor Heap is of size 327680 bytes
   Total: 46399488 bytes

select pool_id, sum(pool_cur_size) instance_mem_tsize
from sysibmadm.snapdbm_memory_pool
group by grouping sets(pool_id,())
with ur;

POOL_ID        INSTANCE_MEM_TSIZE 
-------------- --------------------
-                          21561344
FCMBP                        851968
MONITOR                      327680
OTHER                      20381696

Memory for database: SAMPLE

   Backup/Restore/Util Heap is of size 65536 bytes
   Package Cache is of size 786432 bytes
   Other Memory is of size 196608 bytes
   Catalog Cache Heap is of size 393216 bytes
   Buffer Pool Heap (1) is of size 8650752 bytes
   Buffer Pool Heap (System 32k buffer pool) is of size 851968 bytes
   Buffer Pool Heap (System 16k buffer pool) is of size 589824 bytes
   Buffer Pool Heap (System 8k buffer pool) is of size 458752 bytes
   Buffer Pool Heap (System 4k buffer pool) is of size 393216 bytes
   Shared Sort Heap is of size 0 bytes
   Lock Manager Heap is of size 17629184 bytes
   Database Heap is of size 19791872 bytes
   Application Heap (13) is of size 65536 bytes
   Application Heap (12) is of size 65536 bytes
   Application Heap (11) is of size 196608 bytes
   Application Heap (10) is of size 65536 bytes
   Application Heap (9) is of size 65536 bytes
   Application Heap (8) is of size 65536 bytes
   Application Heap (7) is of size 65536 bytes
   Applications Shared Heap is of size 458752 bytes
   Total: 50855936 bytes

select substr(db_name,1,8) dbname,
       pool_id,
       sum(pool_cur_size) db_mem_tsize
from sysibmadm.snapdb_memory_pool
group by grouping sets(db_name, pool_id)
with ur;
EOF

Application Memory for database: SAMPLE

   Applications Shared Heap is of size 524288 bytes
   Total: 524288 bytes

  Memory for application 13

   Application Heap is of size 65536 bytes
   Other Memory is of size 196608 bytes
   Total: 262144 bytes

  Memory for application 12

   ……………

Memory for agent 33

   Other Memory is of size 196608 bytes
   Total: 196608 bytes

Memory for agent 32

   Other Memory is of size 196608 bytes
   Total: 196608 bytes

select substr(db_name,1,8) dbname, pool_id, sum(pool_cur_size) app_mem_tsize
from sysibmadm.snapagent_memory_pool
group by grouping sets(db_name,pool_id)
with ur ;

실행 결과

출력 매개변수 값
--------------------------
매개변수 이름  : SNAPSHOTTIMESTAMP
매개변수 값 : 2004-02-29-18.33.34.561000

매개변수 이름  : DATABASESIZE
매개변수 값 : 22302720

매개변수 이름   : DATABASECAPACITY
매개변수 값 : 4684859392

리턴 상태 = 0

Column name                            schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
DEPTNO                                    SYSIBM    CHARACTER                    3          0 No
DEPTNAME                               SYSIBM    VARCHAR                       36          0 No
MGRNO                                    SYSIBM    CHARACTER                    6           0 Yes
ADMRDEPT                               SYSIBM    CHARACTER                    3           0 No
LOCATION                                SYSIBM     CHARACTER                   16          0 Yes

DEPTNO ADMRDEPT
------ --------
A00    A00
B01    A00
C01    A00
D01    A00
D11    D01
D21    D01
E01    A00
E11    E01
E21    E01
F22    E01
G22    E01
H22    E01
I22     E01
J22    E01

  14 record(s) selected..

> with parent (pkey, fkey, level) as
> (select deptno, admrdept, 0 from department
>  where deptno = admrdept
>    union all
>  select a.deptno, a.admrdept, p.level+1
>  from department a, parent p
>  where a.deptno != p.fkey and a.admrdept = p.pkey
> )
> select p.pkey,p.level from parent as p;
> EOF
with parent (pkey, fkey, level) as (select deptno, admrdept, 0 from department where deptno = admrdept union all select a.deptno, a.admrdept, p.level+1 from department a, parent p where a.deptno != p.fkey and a.admrdept = p.pkey ) select p.pkey,p.level from parent as p

PKEY        LEVEL
----          -----------
SQL0347W  The recursive common table expression "INST97.PARENT" may contain an
infinite loop.  SQLSTATE=01605

A00            0
B01            1
C01            1
D01            1
E01            1
D11            2
D21            2
E11            2
E21            2
F22            2
G22            2
H22            2
I22            2
J22            2

  14 record(s) selected with 1 warning messages printed.

with parent (pkey, fkey, level) as
(select deptno, admrdept, 0 from department
where deptno = admrdept
union all
  select a.deptno, a.admrdept, p.level+1
  from department a, parent p
  where a.deptno != p.fkey and a.admrdept = p.pkey
)

select p.pkey,p.level,
       ( with subpar (pkey,fkey, level) as
           ( select d.deptno, d.admrdept, p.level from department d where d.admrdept=p.pkey
              union all
              select a.deptno, a.admrdept, s.level+1 from department a, subpar s where a.deptno !=s.fkey and a.admrdept=s.pkey
            )
         select count(*) from subpar) as subcnt
from parent as p;

실행 결과

SQL0104N  An unexpected token "as" was found following "r (pkey,fkey, level)".

Expected tokens may include:  "JOIN".  SQLSTATE=42601

db2 +p -td"@" -v << EOF
drop function reccnt() @
create function reccnt(p_key varchar(5), p_level integer)
returns integer
return with subpar (pkey,fkey, level) as
            ( select d.deptno, d.admrdept, p_level from department d where d.admrdept=p_key
              union all
              select a.deptno, a.admrdept, s.level+1 from department a, subpar s where a.deptno !=s.fkey and a.admrdept=s.pkey
            )
         select count(*) from subpar
@
EOF

SQL0347W  The recursive common table expression "INST97.SUBPAR" may contain an
infinite loop.  SQLSTATE=01605

db2 +p -tv << EOF
with parent (pkey, fkey, level) as
(select deptno, admrdept, 0 from department
where deptno = admrdept
union all
  select a.deptno, a.admrdept, p.level+1
  from department a, parent p
  where a.deptno != p.fkey and a.admrdept = p.pkey
)
select pkey,level, reccnt(pkey, level) as cnt from parent;
EOF

실행 결과

PKEY        LEVEL       CNT
----         ----------- -----------
SQL0347W  The recursive common table expression "INST97.SUBPAR" may contain an
infinite loop.  SQLSTATE=01605

A00            0          27
B01            1           0
C01            1           0
D01            1           2
E01            1           7
D11            2           0
D21            2           0
E11            2           0
E21            2           0
F22            2           0
G22            2           0
H22            2           0
I22             2           0
J22            2           0

  14 record(s) selected with 1 warning messages printed.