IBM Support

What can I do if I want to change the pagesize of DB2 tablespaces

Technical Blog Post


Abstract

What can I do if I want to change the pagesize of DB2 tablespaces

Body

There may be occasions that you want to change the tablespaces from the current pagesize

to a different one, for instance from 4KB to 8KB. Please note tablespaces pagesize cannot be changed directly.

However, you can use the following steps to move the tables to the new tablespaces with the pagesize you want

and then drop the original tablespaces once you confirm the new tablespaces work(the following is an example

to move to 8KB tablespaces):

1.Create a bufferpool with 8KB pagesize if you don't have one already

db2 CREATE BUFFERPOOL bp-name SIZE xxx PAGESIZE 8K

2.Create the tablespaces needed(data,index,lob) with 8KB pagesize       
db2 CREATE TABLESPACE TBS1 PAGESIZE 8K                                  
db2 CREATE TABLESPACE TBS1_IDX PAGESIZE 8K                              
and create lob tablespace if needed                                     
3.Use the following as an example to move a table to the target         
tablespaces(specify its data,index,lob tablespace name)                 
                                                                        
CALL SYSPROC.ADMIN_MOVE_TABLE(                                          
'USER1',                                                                
'T1',                                                                   
'TBS1',                                                                 
'TBS2_IDX',                                                             
'TBS3_LOB',                                                             
'',                                                                     
'',                                                                     
'',                                                                     
'',                                                                     
'',                                                                     
'MOVE');                                                                
                                                                        
You can put the above in a file yourfile and run "db2 -tvf yourfile"(Change the tablename, tablespaces name accordingly).         

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm13286365