Friendship between REORG and Temp Tablespace's - Part2

In my previous post we have discussed about REORG utility and some monitoring utilities for REORG

Friendship between REORG and Temp Tablespace's

Normally when you issue a REORG on a table like one below

REORG TABLE DB2INST1.EMPLOYEE

db2 does the reorganization of the table in the same tablespace it is residing on ,which is not a good thing always , especially when your data is growing there is no sense in doing reorg in the same tablespace and disturbing the other physical objects like tables and MQT's performance

That is where system temporary tablespaces save us from the performance degradations while REORG

So how do we achieve this ? , we create separate bufferpools for all different page sizes i.e., 4K, 8K, 16K, 32K

Bufferpools Memory analysis

Name	pagesize	size(MB)
BUFTMP_4	1000	3.90625
BUFTMP_8	1000	7.8125
BUFTMP_16	1000	15.625
BUFTMP_32	1000	31.25
Total		58.59375

So we have created 4 bufferpools where all the 4 take 59MB approximately which is not a big deal.

Below are the scripts given to create bufferpools , just change the path of the container and you are good to go .
Always use a different partition in your SAN storage for the temporary tablespaces other than your regular tablespaces so that they will not affect the transaction performance .

create bufferpool BUFTMP_4 immediate size 1000 automatic pagesize 4k
create bufferpool BUFTMP_8 immediate size 1000 automatic pagesize 8k
create bufferpool BUFTMP_16 immediate size 1000 automatic pagesize 16k
create bufferpool BUFTMP_32 immediate size 1000 automatic pagesize 32k

Now create the system temporary tablespaces as they fall in the respective pagesizes of the bufferpools we have created

Below are the scripts to create the system temporary tablespaces

create system temporary tablespace STMPTBSP_4 pagesize 4K managed by system using ('/home/db2inst1/SYSTMPTBSP_4') bufferpool BUFTMP_4
create system temporary tablespace STMPTBSP_8 pagesize 8K managed by system using ('/home/db2inst1/SYSTMPTBSP_8') bufferpool BUFTMP_8
create system temporary tablespace STMPTBSP_16 pagesize 16K managed by system using ('/home/db2inst1/SYSTMPTBSP_16') bufferpool BUFTMP_16
create system temporary tablespace STMPTBSP_32 pagesize 32K managed by system using ('/home/db2inst1/SYSTMPTBSP_32') bufferpool BUFTMP_32

The important thing now would be to determine the pagesize of the table so that we can use the SYSTEM TEMPORARY TABLESPACE of the same pagesize while doing a REORG

Below is the SQL script which will help to determine the pagesize of the table

select A.TABLEID,A.TBSPACEID,substr(A.TBSPACE,1,40) as TBSPACE,B.PAGESIZE from syscat.tables A , syscat.tablespaces B where A.TBSPACEID=B.TBSPACEID and A.TABNAME='EMPLOYEE'

TABLEID TBSPACEID TBSPACE                                  PAGESIZE
------- --------- ---------------------------------------- -----------
   1044         2 USERSPACE1                                      4096

  1 record(s) selected.

In our example our table EMPLOYEE resides in tablespace USERSPACE1 whose PAGESIZE is 4K , so the suitable tablespace from the set we have created would be STMPTBSP_4

Now our REORG statement should look like one below

REORG TABLE DB2INST1.EMPLOYEE USE STMPTBSP_4

Following these would prevent unanticipated behaviour while performing REORG

Have a Nice Day