Performance Tuning for Content Manager

Chapter 8. Tuning DB2 for Content Manager 193

/dev/lsdblv6i 5242880 5221944 1% 17 1% /lsdbfast7i

/dev/lsdblv7i 5242880 5221944 1% 17 1% /lsdbfast8i

/dev/lsdblv8i 5242880 5221944 1% 17 1% /lsdbfast9i

/dev/lsdblv10i 5242880 5221944 1% 17 1% /lsdbfast10i

/dev/lsdblv11i 5242880 5221944 1% 17 1% /lsdbfast11i

8.4.4 Creating the DMS DDL

Using the DDL that was produced (or a copy of it) from the running of db2look,

create the DMS DDL that will be used to rebuild the table space, the table, the

indexes, the views, and any other DB2 objects contained in the table space that

is being converted. You can run all of the create DDLs together in one script or

you can run the create commands separately. This section makes the

assumption that all of the DDLs will be run in one script by the db2 -tvf

command.

Start by creating the DMS DDL for the new table spaces. Example 8-10 shows

an example in which two DMS table spaces are to be created, the first for table

data and the second for the table indexes. Note the MANAGED BY DATABASE, and

that more than one container is created for each table space to achieve

parallelism.

Example 8-10 DMS table space DDL

------------------------------------

-- DDL Statements for TABLESPACES --

------------------------------------

CREATE REGULAR TABLESPACE tsicmut01001a IN DATABASE PARTITION GROUP

ICMLSONENODE PAGESIZE 32768 MANAGED BY DATABASE

USING (FILE '/lsdbfast3/icmut01001a-1' 3000, FILE

'/lsdbfast3/icmut01001a-2' 3000)

EXTENTSIZE 32

PREFETCHSIZE 32

BUFFERPOOL ICMLSMAINBP32

OVERHEAD 12.670000

TRANSFERRATE 0.180000

DROPPED TABLE RECOVERY ON;

CREATE REGULAR TABLESPACE tsicmut01001ai IN DATABASE PARTITION GROUP

ICMLSONENODE PAGESIZE 32768 MANAGED BY DATABASE

USING (FILE '/lsdbfast3i/icmut01001a-1i' 3000, FILE

'/lsdbfast3i/icmut01001a-2i' 3000)

EXTENTSIZE 32

194 Performance Tuning for Content Manager

PREFETCHSIZE 2

BUFFERPOOL ICMLSMAINBP32

DROPPED TABLE RECOVERY ON;

Part of modifying the DDL to add containers to a table space involves calculating

the size that the containers should be. In our example, each container has been

assigned 3000 pages. To determine what size to make the containers:

1. Use reorgchk to determine the table size (tsize) in bytes.

2. Convert the table size into pages by dividing it by the page size.

Assume table ICMUT01016001 is 62,900,000 bytes divided by 32768 (32 K

page size), which is almost 2000 pages.

3. Adjust the number of container pages based on workload and growth

expectations.

When the table spaces have been created, change the table DDL to reference

the new table spaces. This is done in the in and index in options of the create

table command, which is shown in Example 8-11.

Example 8-11 Create table command

CREATE TABLE "ICMADMIN"."ICMUT01016001" (

"COMPCLUSTERID" INTEGER NOT NULL ,

"COMPONENTID" CHAR(18) NOT NULL ,

"ITEMID" CHAR(26) NOT NULL ,

"VERSIONID" SMALLINT NOT NULL ,

"ACLCODE" INTEGER NOT NULL ,

"SEMANTICTYPE" INTEGER NOT NULL ,

"EXPIRATIONDATE" DATE ,

"COMPKEY" VARCHAR(23) FOR BIT DATA NOT NULL ,

"CREATETS" TIMESTAMP NOT NULL ,

"CREATEUSERID" CHAR(32) NOT NULL ,

"LASTCHANGEDTS" TIMESTAMP NOT NULL ,

"LASTCHANGEDUSERID" CHAR(32) NOT NULL ,

"ATTR0000001036" VARCHAR(254) NOT NULL ,

"ATTR0000001037" VARCHAR(254) ,

"ATTR0000001038" VARCHAR(254) ,

"ATTR0000001039" VARCHAR(254) ,

"ATTR0000001040" VARCHAR(254) ,

"ATTR0000001041" VARCHAR(254) ,

"ATTR0000001042" VARCHAR(254) ,

"ATTR0000001043" VARCHAR(254) ,

"ATTR0000001044" VARCHAR(254) ,

"ATTR0000001045" VARCHAR(254) ,

"ATTR0000001046" VARCHAR(254) ,