How to add additional columns for a table in db2 where default 4k tablespace is not enough

Jephe Wu - http://linuxtechres.blogspot.com

Environment: RHEL4 32bit with "DB2 v8.1.2.88", "s050422", "MI00117", and FixPak "9". We need to add an additional column for a table, but we encountered error message below:

The row length of the table exceeded a limit of "4005" bytes. (Table space "xxxxx".)

Objective: to create a 8k tablespace and migrate this table over to new tablespace


Steps:

1. backup the table jephe by using the following commands:(assuming database and schema name are wu, talbe name is jephe)

# more backup.sql
export to "jephe.txt" of del messages jephe.msg select * from  wu.jephe;
CONNECT RESET;

db2 connect to wu user wu
db2 -tvf backup.sql -l backup.sql.log -s

2. use db2look to extract the whole database ddl statements and grep the necessary table creation statements
db2look -d wu -e -z wu -o db2look.sql
vi db2look.sql to search string JEPHE to copy out the table create and alter statements for 'JEPHE' as well as alter statements for other tables which has foreign keys on table 'JEPHE'

e.g.
# more createtable.sql
CREATE TABLE "WU   "."JEPHE"  (
                  "LIFE_INSURANCE_OID" CHAR(30) NOT NULL ,
                  balabala
                  balabala
                  ....
                  "CONTACT_NUMBER" VARCHAR(200) )
                 IN "TB_JEPHE" ;


ALTER TABLE "WU    "."JEPHE"
        ADD PRIMARY KEY
                ("LIFE_INSURANCE_OID");


ALTER TABLE "WU    "."JEPHE"
        ADD CONSTRAINT "SQL050615160106790" FOREIGN KEY
                ("BENEFIT_FILE_OID")
        REFERENCES "WU    "."BINARY_FILE_REPOSITORY"
                ("BINARY_FILE_OID")
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
        ENFORCED
        ENABLE QUERY OPTIMIZATION;

# following is for another table 'TABLE1' which has foreign key for WU.JEPHE
ALTER TABLE "WU    "."TABLE1"
        ADD CONSTRAINT "SQL050615160109840" FOREIGN KEY
                ("JEPHE_OID")
        REFERENCES "WU   "."JEPHE"
                ("JEPHE_OID")
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
        ENFORCED
        ENABLE QUERY OPTIMIZATION;
       
Note: once you drop table 'WU.JEPHE', the foreign key for WU.TABLE1 will also be gone. After recreating table WU.JEPHE in 8k tablespace, you have to create this foreign key again.

You also need to extract view creation statements which depends on the table 'WU.JEPHE'. Because you need to create view again after dropping and creating again WU.JEPHE in 8k tablespace.

# more view.sql
create view emp_benefits_life_ins_v as select balabala.

3. create a 8k buiffer pool tablespace and a 8k system temporary tablespace for 'order by' operation on new table wu.jephe, specify container path.
If you don't create a 8k temporary tablespace, you will encouter the following error message when doing 'order by' operation

db2 "select * from wu.jephe order by status"
SQL1585N  A system temporary table space with sufficient page size does not
exist.  SQLSTATE=54048

creation statement is as follows for system temporary tablespace:

CONNECT TO WU;
CREATE  SYSTEM TEMPORARY  TABLESPACE TEMPSPACE2 PAGESIZE 8 K  MANAGED BY SYSTEM  USING ('/db2/db2inst1/db/WU/tb_temp2' ) EXTENTSIZE 16 OVERHEAD 10.67 PREFETCHSIZE 16 TRANSFERRATE 0.04 BUFFERPOOL  IBM8KBP ;
COMMENT ON TABLESPACE TEMPSPACE2 IS '8k temporary tablespace';
CONNECT RESET;

4 drop table
login as db2inst1
db2 connect to wu
db2 "drop table wu.jephe"

5 recreate table in 8k tablespace
db2 connect to wu
db2 set schema = jephe
modify createtable.sql to change
CREATE TABLE "WU   "."JEPHE"  (
                  "LIFE_INSURANCE_OID" CHAR(30) NOT NULL ,
                  balabala
                  balabala
                  ....
                  "CONTACT_NUMBER" VARCHAR(200) )
                 IN "TB_JEPHE" ;
               
to

CREATE TABLE "WU   "."JEPHE"  (
                  "LIFE_INSURANCE_OID" CHAR(30) NOT NULL ,
                  balabala
                  balabala
                  ....
                  "CONTACT_NUMBER" VARCHAR(200) )
                 IN "TB_JEPHE2";
db2 -tvf createtable.sql -l createtable.sql.log -s

6. restore data into table wu.jephe again from backup

#more restore.sql
import from "jephe.txt" of del messages "jephe.impmsg" insert into wu.jephe;
connect reset;

db2 connect to wu
db2 set schema = jephe
db2 -tvf restore.sql -l restore.sql.log -s

import from "jephe.txt" of del messages "jephe.impmsg" insert into wu.jephe

Number of rows read         = 77
Number of rows skipped      = 0
Number of rows inserted     = 77
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 77

7. run command to add additional comumns for new table wu.jephe
# more command.sql
alter table wu.jephe add column contact_detail varchar(250);

db2 connect to wu
db2 set schema = jephe
db2 -tvf command.sql -l command.sql.log -s

8. grant permission for table WU.JEPHE and related views from db2 control center GUI