In my previous post we have discussed about REORG utility and some monitoring utilities for REORGFriendship 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
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