DB2 reorg (EXT)

DB2 reorg

Runstats:

using rmadmin

Perform runstats on all user tables (reorgchk plus update parameter equals Runstats)

$ DB2 REORGCHK UPDATE STATISTICS on table userdoing RUNSTATS ....

Reorg:

In the results of the check, all tables or partitioned tables with asterisks and indexes need to be reorg rebuilt.

$ DB2 reorg table Rmadmin. Explain_diagnostic index SYSIBM. SQL120703164841960 use tempspace1db20000i the REORG command completed successfully.$ DB2 REORG table Rmadmin. Explain_diagnostic_data index Rmadmin. Exp_diag_dat_i1 use tempspace1db20000i the REORG command completed successfully.$ DB2 REORG table Rmadmin. Explain_predicate index Rmadmin. Prd_i1 use tempspace1db20000i the REORG command completed successfully.$ DB2 REORG table Rmadmin. Rmstggrpclass index SYSIBM. SQL120321193908820 use tempspace1db20000i the REORG command completed successfully.$ DB2 REORG table Rmadmin.  Rmobjects use tempspace1sql2217n the page size of the system temporary table space used by the REORG utility must match The page size of the table space (s)inchwhich the table data resides (including the LONG or LOB column data). The cause isbased on the following reason codes"1".

12. The reason is related to the selection of the temporal tablespace of a LONG or LOB data table. If the system temporary table is explicitly specified for the REORG utility, the page size of the system temporary tablespace used by the REORG utility must match the page size of the table space where the table data (including long or LOB column data) resides, otherwise the appropriate container must be specified for long data. One of the following violates this restriction: the page size of the table space in which the table data resides differs from the page size of the specified system temporary table space. The table contains a long or LOB column where the data resides in a table space with a different page size than the system temporary table space and the table's rule data, but a table space with the correct page size cannot be found for a LONG or LOB data object. If the REORG utility is not specified with a system temporary tablespace or a long temporary tablespace, the utility finds the system temporary table space internally. There is no system temporary table space in the database that uses the same page size as the table data page, or the system temporary table space is not available at this time. User response if a system temporary tablespace is not present in the database that uses the same page size as the table data page, create a system temporary tablespace that uses a page size that matches the page size of the table data. If the page size of the table data differs from the page size of the LOB or LONG data, ensure that the system temporary table space that uses the page size also exists.

If a system temporary tablespace exists in the database that uses the same page size as the table data page, but the temporary tablespace is not available when the command is issued, re-issue the command when the system temporary tablespace is available.

The currently used temporary tablespace page size and the table's page size do not match, and you need to create a new page size and a system temp table space that conforms to the page size of the table.
To view the pagesize of each table space:

SELECT Tbspace, pagesize from SYSIBM. Systablespaces

View current Bufferpool:

SELECT * from SYSCAT. Bufferpools

Create a new Bufferpool with a page size of 32K

32768 db20000i  The SQL command completed successfully.

Create a new temporary tablespace, using the Bufferpool

" Create system temporary tablespace tempspace3 pagesize 32K managed by system using ('/RMDB11DATA/RMINST11/NODE0000/SQ L00001/tmpspace3 ') bufferpool temppool32"db20000i the  SQL Command completed successfully.

Re-execute reorg:

$ DB2 reorg table Rmadmin. Rmmigrationtasks index SYSIBM. SQL120321193909130 Use   Tempspace3

Monitoring Table reassembly:

Select        1 as Tab_name, 1 as         Tab_schema,       reorg_phase,reorg_max_phase,       1 as Reorg_type,       reorg_status,       reorg_completion,       dbpartitionnum     from  sysibmadm.snaptab_reorg     ORDER BY Dbpartitionnum

Or

DB2 GET SNAPSHOT for TABLES on DBName

DB2 List History reorg all for DBName

DB2PD -db DBName -reorgs index

$ Db2pd-reorgs-db rmdb11database Partition0--Database RMDB11--Active--up3Days +: -: ---DateTen/ -/ -  -: +: theTable Reorg information:address tbspaceid TableID partid mastertbs mastertab TableName Type Ind Exid Tempspaceid0x070000024c0d1528 5         260N/a N/a explain_predicate Offline1          1         0x070000024c0deda8 5         262N/a N/a explain_diagnostic Offline1          1         0X070000024C0E6D28 5         263N/a N/a explain_diagnostic Offline1          1         0x070000024b2c9628 7         5N/a N/a rmmigrationtasks Offline1           -        0x070000024b2a64a8 5          -N/a N/a rmstggrpclass Offline1          1Table Reorg stats:address TableName Start End Phasestart Maxphase Phase curcount MaxCount Status Completion0x070000024c0d1528Explain_predicateTen/ -/ - Ten: A: - Ten/ -/ - Ten: A: - Ten/ -/ - Ten: A: - 4Idxrecreat0          0Done0         0x070000024c0deda8Explain_diagnosticTen/ -/ - Ten:Ten: - Ten/ -/ - Ten:Ten: - Ten/ -/ - Ten:Ten: - 4Idxrecreat0          0Done0         0X070000024C0E6D28Explain_diagnosticTen/ -/ - Ten: A: - Ten/ -/ - Ten: A: - Ten/ -/ - Ten: A: - 4Idxrecreat0          0Done0         0x070000024b2c9628RmmigrationtasksTen/ -/ -  A: $: $N/ATen/ -/ -  -: -: - 4Idxrecreat202794     576060Started0         0x070000024b2a64a8RmstggrpclassTen/ -/ - Ten: -: to Ten/ -/ - Ten: -: to Ten/ -/ - Ten: -: to 4Idxrecreat0          0Done0         

DB2 reorg (EXT)