DB2 - To reorg a table

 $ db2 "reorg table wcs.staglog inplace  allow write access"

DB20000I  The REORG command completed successfully.

DB21024I  This command is asynchronous and may not be effective immediately.


[stg1ins1@db2stg601 stg1ins1 C0000001]



The command comes back immediately but check on the status by doing this:
$ db2pd -d wcstg1 -reorg | grep STAGLOG
0x00007FD78572C608 3         42      n/a    n/a       n/a       STAGLOG            Online  0          3
0x00007FD78572C608 STAGLOG            05/22/2022 08:13:46 n/a                 n/a                 n/a        n/a        723060     9689919    Started 0
[stg1ins1@db2stg601 stg1ins1 LOGSTREAM0000]

OR this command if one REORG is in progress:
$ db2pd -d wcstg1 -reorg

Database Member 0 -- Database WCSTG1 -- Active -- Up 26 days 21:03:59 -- Date 2022-05-22-09.01.26.542512

Table Reorg Information:
Address            TbspaceID TableID PartID MasterTbs MasterTab TableName          Type    IndexID    TempSpaceID
0x00007FD78572C608 3         42      n/a    n/a       n/a       STAGLOG            Online  0          3

Table Reorg Stats:
Address            TableName          Start               End                 PhaseStart          MaxPhase   Phase      CurCount   MaxCount   Status  Completion
0x00007FD78572C608 STAGLOG            05/22/2022 08:13:46 n/a                 n/a                 n/a        n/a        798259     9689919    Truncat 0



For the above, it will go from STARTED to TRUNCATE to DONE statuses
The numbers represent the number of pages processed and total number of pages to be processed.


$ db2pd -d wcstg1 -reorg

Database Member 0 -- Database WCSTG1 -- Active -- Up 26 days 22:12:20 -- Date 2022-05-22-10.09.47.697985

Table Reorg Information:
Address            TbspaceID TableID PartID MasterTbs MasterTab TableName          Type    IndexID    TempSpaceID
0x00007FD78572C608 3         42      n/a    n/a       n/a       STAGLOG            Online  0          3

Table Reorg Stats:
Address            TableName          Start               End                 PhaseStart          MaxPhase   Phase      CurCount   MaxCount   Status  Completion
0x00007FD78572C608 STAGLOG            05/22/2022 08:13:46 05/22/2022 10:03:03 n/a                 n/a        n/a        0          0          Done    0




Plus keep pruning archive logs every few mins
$ db2 get db config | grep -i log
$ db2 prune logfile prior to  S0003690.LOG
$ df -h

When reorg is complete
RUNSTATS:
db2 runstats on table wcs.STAGLOG with distribution and detailed indexes all

REDUCE TABLESPACE
db2 "alter tablespace tab8k reduce max"

Use 'dsmtop' to see the size of the table
1. go to Options and mark an X (space bar) next to Show Expensive Views and SAVE
2. View> Storage > Table Utilization 
3. Use 'd' to scroll down

Use  'db2pd' to see the tablespace reduce
$ db2pd -d wcstg1 -extentmovement -repeat 15


REORG Indexes:
$ db2 REORG INDEXES ALL FOR TABLE WCS.STAGLOG ALLOW WRITE ACCESS REBUILD


To see status of index reorg:
$  db2 list history reorg all for db wcstg1


Comments

Post a Comment

Popular posts from this blog

Oracle: To clean up WRI$_ADV_OBJECTS

 set linesize 200; select segment_name, segment_type, bytes from dba_segments where tablespace_name='SYSAUX' order by bytes asc; COL OCCUPANT_NAME FORMAT A30 SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC; COL SEGMENT_NAME FORMAT A30 COL OWNER FORMAT A10 COL TABLESPACE_NAME FORMAT A10 COL SEGMENT_TYPE FORMAT A15 SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10; 1.  automatic clean-up default 31 days           SELECT dbms_stats.get_stats_history_availability from Dual; 2. modify retention policy        exec  dbms_stats.alter_stats_history_retention (14); 3. a manual cleaning  exec dbms_stats.purge_stats (sysdate-14);   ALTER TABLE WRI$_ADV_OBJECTS MOVE; ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD; ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 R...
Read more