DB2 REORG command is not able to start

0

I am trying to reorg a table in DB2 as the number of alters on a table has exceeded more than 3 times.

DB2 CALL SYSPROC.ADMIN_CMD('REORG TABLE WASADMIN.EXCHANGERATES');

It is not giving any response and looks like it is hanged . I checked the status by running the command .

db2pd -db UB602 -reorgs

but there is no active Reorg process .

Database Member 0 -- Database UB602 -- Active -- Up 0 days 02:14:45 -- Date 2018-11-26-19.34.15.256000

Table Reorg Information:
Address            TbspaceID TableID PartID MasterTbs MasterTab TableName          Type    IndexID    TempSpaceID

Table Reorg Stats:
Address            TableName          Start               End                 PhaseStart          MaxPhase   Phase      CurCount   MaxCount   Status  Completion

What could have gone wrong?

Edit:

I checked the logs but nothing is printed. but when I am pressing ctrl+c to terminate . I get some message in the logs .

2018-11-26-20.19.53.459000+330 I1F1191              LEVEL: Event
PID     : 5176                 TID : 4928           PROC : db2syscs.exe
INSTANCE: DB2_01               NODE : 000           DB   : UB602
APPHDL  : 0-1186               APPID: *LOCAL.DB2_01.181126124159
AUTHID  : DB2ADMIN             HOSTNAME: BLR2DEVLP0434
EDUID   : 4928                 EDUNAME: db2agent (UB602) 0
FUNCTION: DB2 UDB, RAS/PD component, pdLogInternal, probe:120
START   : New Diagnostic Log file
DATA #1 : Build Level, 232 bytes
Instance "DB2_01" uses "64" bits and DB2 code release "SQL11010"
with level identifier "0201010F".
Informational tokens are "DB2 v11.1.0.1527", "s1606081100", "DYN1606081100WIN64", Fix Pack "0".
DATA #2 : System Info, 1760 bytes
System: WIN32_NT BLR2DEVLP0434  10.0 AMD64 Family 6, model 78, stepping 3
CPU: total:4 online:4 Cores per socket:2 Threading degree per core:2 SIMD:Y
CPU binding: not in use
Physical Memory(MB): total:16049 free:6448 available:6448
Virtual  Memory(MB): total:28849 free:19248
Swap     Memory(MB): total:12800 free:12800
Information in this record is only valid at the time when this file was
created (see this record's time stamp)

2018-11-26-20.19.53.469000+330 I1195F968            LEVEL: Info
PID     : 5176                 TID : 4928           PROC : db2syscs.exe
INSTANCE: DB2_01               NODE : 000           DB   : UB602
APPHDL  : 0-1186               APPID: *LOCAL.DB2_01.181126124159
AUTHID  : DB2ADMIN             HOSTNAME: BLR2DEVLP0434
EDUID   : 4928                 EDUNAME: db2agent (UB602) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:1700
MESSAGE : Severe TERMINATE err at nest lvl 1, in SQL from rtn SYSPROC.ADMIN_CMD
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -952   sqlerrml: 0
 sqlerrmc: 
 sqlerrp : SQLRL04B
 sqlerrd : (1) 0x80100003      (2) 0x00000003      (3) 0x00000000
           (4) 0x00000000      (5) 0x00000000      (6) 0x00000000
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)    
           (7)      (8)      (9)      (10)        (11)     
 sqlstate: 57014
db2
db2-luw
asked on Stack Overflow Nov 26, 2018 by xpioneer • edited Nov 27, 2018 by mao

1 Answer

1

To perform an offline reorg, you have to ensure that nothing is using the table before the reorg command starts to run.

If an offline reorg is not possible, check if an inplace reorg can work instead.

If there are other reasons preventing the action, consult the db2diag.log file for more details. In your question it shows only sqlcode -952 which tells us that you cancelled the job with the control-C.

You can check if there are any row-locks or table-locks on the target table by using one of many different methods. You can use dsmtop or db2top, or dsm, or a command line (db2 get snapshot for locks and then parsing the output for your table name), or by using the MON_GET_LOCKS table function or equivalents.

The documentation states "

Be sure to complete all database operations and release all locks before you invoke REORG TABLE. This step can be done by issuing a COMMIT after you close all cursors opened WITH HOLD, or by issuing a ROLLBACK. After you reorganize a table, use RUNSTATS to update the table statistics, and REBIND to rebind the packages that use this table. The reorganize utility implicitly closes all the cursors. "

answered on Stack Overflow Nov 26, 2018 by mao

User contributions licensed under CC BY-SA 3.0