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
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. "
User contributions licensed under CC BY-SA 3.0