The Home of C&P Software on the Web

create the new tablespaces:

       
CREATE BUFFERPOOL BP32K PAGESIZE 32k SIZE 100;

CREATE REGULAR TABLESPACE SmallTables1 IN DATABASE PARTITION GROUP IBMCATGROUP PAGESIZE 4096 MANAGED BY SYSTEM
         USING (’/var/db2fs0/SmallTables1′)
         EXTENTSIZE 32
         PREFETCHSIZE AUTOMATIC
         BUFFERPOOL IBMDEFAULTBP
         DROPPED TABLE RECOVERY ON;

CREATE REGULAR TABLESPACE LargeTables1 IN DATABASE PARTITION GROUP IBMCATGROUP PAGESIZE 4096 MANAGED BY SYSTEM
         USING (’/var/db2fs1/LargeTables1′, ‘/var/db2fs2/LargeTables2′, ‘/var/db2fs3/LargeTables3′)
         EXTENTSIZE 32
         PREFETCHSIZE AUTOMATIC
         BUFFERPOOL IBMDEFAULTBP
         DROPPED TABLE RECOVERY ON;

CREATE REGULAR TABLESPACE LargePages1 IN DATABASE PARTITION GROUP IBMCATGROUP PAGESIZE 32K MANAGED BY SYSTEM
         USING (’/var/db2fs0/largePages1′)
         EXTENTSIZE 32
         PREFETCHSIZE AUTOMATIC
         BUFFERPOOL BP32K
         DROPPED TABLE RECOVERY ON;

 

Do a ‘db2look’ on the table, remove in the views and indexes and change the tablespace name and the table name in the CREATE TABLE command

– This CLP file was created using DB2LOOK Version 8.2
– Timestamp: Wed 30 Aug 2006 02:30:09 PM EDT
– Database Name: CO01          
– Database Manager Version: DB2/LINUX Version 8.2.1      
– Database Codepage: 1208
– Database Collating Sequence is: BINARY

CONNECT TO CO01;

————————————————
– DDL Statements for table “DB2INST1″.”TR_TRN_NEW”
————————————————
 
 CREATE TABLE “DB2INST1″.”TR_TRN_NEW”  (
                  “ID_STR_RT” CHAR(5) NOT NULL ,
                  “ID_WS” CHAR(3) NOT NULL ,
                  “DC_DY_BSN” CHAR(10) NOT NULL ,
                  “AI_TRN” INTEGER NOT NULL ,
                  “ID_OPR” CHAR(10) ,
                  “TY_TRN” VARCHAR(20) ,
                  “TS_TM_SRT” TIMESTAMP ,
                  “TS_TRN_BGN” TIMESTAMP ,
                  “TS_TRN_END” TIMESTAMP ,
                  “FL_TRG_TRN” CHAR(1) WITH DEFAULT ‘0′ ,
                  “FL_KY_OFL” CHAR(1) WITH DEFAULT ‘0′ ,
                  “SC_TRN” SMALLINT WITH DEFAULT 2 ,
                  “ID_EM” VARCHAR(10) ,
                  “INF_CT” VARCHAR(14) ,
                  “TY_INF_CT” SMALLINT WITH DEFAULT 0 ,
                  “ID_RPSTY_TND” VARCHAR(10) ,
                  “ID_TLOG_BTCH” INTEGER WITH DEFAULT -1 ,
                  “ID_BTCH_ARCH” VARCHAR(14) WITH DEFAULT ‘-1′ ,
                  “SC_PST_PRCS” SMALLINT WITH DEFAULT 0 ,
                  “TS_CRT_RCRD” TIMESTAMP ,
                  “TS_MDF_RCRD” TIMESTAMP ,
                  “FL_TRE_TRN” CHAR(1) WITH DEFAULT ‘0′ )  
                 IN LARGETABLES1 ;
COMMENT ON TABLE “DB2INST1″.”TR_TRN_NEW” IS ‘Transaction’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”AI_TRN” IS ‘TransactionSequenceNumber’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”DC_DY_BSN” IS ‘BusinessDay’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”FL_KY_OFL” IS ‘TransactionKeyedOfflineFlag’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”FL_TRE_TRN” IS ‘TransactionReentryFlag’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”FL_TRG_TRN” IS ‘TransactionTrainingFlag’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”ID_BTCH_ARCH” IS ‘TransactionArchiveBatchIdentifier’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”ID_EM” IS ‘Sales AssociateID’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”ID_OPR” IS ‘OperatorID’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”ID_RPSTY_TND” IS ‘TenderRepositoryId’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”ID_STR_RT” IS ‘RetailStoreID’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”ID_TLOG_BTCH” IS ‘TransactionTLogBatchIdentifier (Deprecated in 5.1.0)’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”ID_WS” IS ‘WorkstationID’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”INF_CT” IS ‘CustomerInfo’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”SC_PST_PRCS” IS ‘TransactionPostProcessingStatusCode’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”SC_TRN” IS ‘TransactionStatusCode’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”TS_CRT_RCRD” IS ‘RecordCreationTimestamp’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”TS_MDF_RCRD” IS ‘RecordLastModifiedTimestamp’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”TS_TM_SRT” IS ‘WorkstationStartDateTimestamp’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”TS_TRN_BGN” IS ‘TransactionBeginDateTimestamp’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”TS_TRN_END” IS ‘TransactionEndDateTimestamp’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”TY_INF_CT” IS ‘CustomerInfoType’;

COMMENT ON COLUMN “DB2INST1″.”TR_TRN_NEW”.”TY_TRN” IS ‘TransactionTypeCode’;

– DDL Statements for primary key on Table “DB2INST1″.”TR_TRN_NEW”

ALTER TABLE “DB2INST1″.”TR_TRN_NEW”
        ADD PRIMARY KEY
                (”ID_STR_RT”,
                 “ID_WS”,
                 “DC_DY_BSN”,
                 “AI_TRN”);

COMMIT WORK;

CONNECT RESET;

TERMINATE;

Load the new table from the old using a ‘cursor’

declare mycursor cursor for select * from tr_trn;
load from mycursor of cursor insert into tr_trn_new;

Rename the table from current to old and new to current (remember to qualify the “from” table)

db2 rename table db2inst1.tr_trn to tr_trn_old
db2 rename table db2inst1.tr_trn_new to tr_trn

Restore Referencial Integrity to the DB!