Performance Tuning for Content Manager

190 Performance Tuning for Content Manager

Example 8-5 Output from db2look

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

-- DDL Statements for TABLESPACES --

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

CREATE REGULAR TABLESPACE ICMLFQ32 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP

PAGESIZE 32768 MANAGED BY SYSTEM

USING ('/lsdb/db2inst1/db2inst1/NODE0000/SQL00001/ICMSXITEMS0010013U')

EXTENTSIZE 32

PREFETCHSIZE AUTOMATIC

BUFFERPOOL ICMLSMAINBP32

OVERHEAD 12.670000

TRANSFERRATE 0.180000

DROPPED TABLE RECOVERY ON;

8.4.2 Exporting table data

The conversion from SMS to DMS involves dropping the SMS table spaces.

Before this is done, you must save your existing table data using the export

command. Example 8-6 shows an example of the export command.

Example 8-6 Export table data command

EXPORT TO /lsdb/sms/ exportICMUT01016001.ixf of ixf messages

/lsdb/sms/exp_msgICMUT01016001.msg

SELECT * FROM ICMADMIN.ICMUT01016001;

8.4.3 Creating new logical volumes

Create new logical volumes and file systems on separate hard disks if possible

for each table space. This allows more parallelism in disk I/O and enables the

filemon and iostat commands to clearly display which file systems have the

highest disk I/O.

Example 8-7 shows an example of the commands that could be necessary.

These commands can be in a script that loops through an input file that defines

the new file systems for the new DMS tables.

Example 8-7 AIX logical volume commands

#

# Library Server

#

# ls db 400GB

Chapter 8. Tuning DB2 for Content Manager 191

while read sz fs lv vg pv logv

do

#

# Setup VGroups (using a partition size of 32MB -s )

#

# /usr/sbin/mkvg -y$vg -s'32' $pv

# /usr/sbin/lsvg -L -p $vg

#

# Setup LVs

#

# for jfs2, use: /usr/sbin/mklv -v'n' -w'n' -y$lv -t'jfs2' -e'x' $vg $sz $pv

/usr/sbin/mklv -v'n' -w'n' -y$lv -t'jfs' -e'x' $vg $sz $pv

#

# Setup filesystems

#

# /usr/sbin/crfs -v jfs2 -d${lv} -m'/'${fs} -A yes -p'rw' -t no -a size=$sz -a

agblksize='4096' -a logname='INLINE'

/usr/sbin/crfs -v jfs -d${lv} -m'/'${fs} -A yes -p'rw' -t no -a bf=true -a

size=$sz -a frag='4096' -a nbpi='32768' -a ag='16' -a logname=$logv

done<ls.input

/usr/sbin/mount -a

/usr/bin/df -k

Example 8-8 shows a sample input file that could be used as input to the script.

Example 8-8 Sample input file

## more ls.input

5G lsdbfast1 lsdblv1 sysfvg hdisk5 /dev/loglsdbfast1

5G lsdbfast2 lsdblv2 sysfvg hdisk4 /dev/loglsdbfast2

5G lsdbfast3 lsdblv3 sysfvg hdisk3 /dev/loglsdbfast3

5G lsdbfast4 lsdblv4 sysfvg hdisk2 /dev/loglsdbfast4

5G lsdbfast5 lsdblv5 sysfvg hdisk1 /dev/loglsdbfast5

5G lsdbfast6 lsdblv6 sysfvg hdisk5 /dev/loglsdbfast6

5G lsdbfast7 lsdblv7 sysfvg hdisk4 /dev/loglsdbfast7

5G lsdbfast8 lsdblv8 sysfvg hdisk3 /dev/loglsdbfast8

5G lsdbfast9 lsdblv9 sysfvg hdisk2 /dev/loglsdbfast9

5G lsdbfast10 lsdblv10 sysfvg hdisk1 /dev/loglsdbfast10

5G lsdbfast11 lsdblv11 sysfvg hdisk5 /dev/loglsdbfast11

5G lsdbfast1i lsdblv1i sysfvg hdisk4 /dev/loglsdbfast1i

5G lsdbfast2i lsdblv2i sysfvg hdisk3 /dev/loglsdbfast2i

192 Performance Tuning for Content Manager

5G lsdbfast3i lsdblv3i sysfvg hdisk2 /dev/loglsdbfast3i

5G lsdbfast4i lsdblv4i sysfvg hdisk1 /dev/loglsdbfast4i

5G lsdbfast5i lsdblv5i sysfvg hdisk5 /dev/loglsdbfast5i

5G lsdbfast6i lsdblv6i sysfvg hdisk4 /dev/loglsdbfast6i

5G lsdbfast7i lsdblv7i sysfvg hdisk3 /dev/loglsdbfast7i

5G lsdbfast8i lsdblv8i sysfvg hdisk2 /dev/loglsdbfast8i

5G lsdbfast9i lsdblv9i sysfvg hdisk1 /dev/loglsdbfast9i

5G lsdbfast10i lsdblv10i sysfvg hdisk5 /dev/loglsdbfast10i

5G lsdbfast11i lsdblv11i sysfvg hdisk4 /dev/loglsdbfast11i

In Example 8-8 on page 191, one logical volume manager will be created for

each file system. This reduces the bottleneck that would have occurred if all file

systems were under one logical volume manager.

Example 8-9 shows results similar to the look of the newly created file system. In

our example, the “i” is used to indicate table spaces for indexes of the

corresponding tables.

Example 8-9 File system output

Filesystem 1024-blocks Free %Used Iused %Iused Mounted on

/dev/hd4 63176704 47458360 25% 50016 1% /

/dev/hd2 32505856 29354860 10% 63111 1% /usr

/dev/hd9var 4456448 4423500 1% 456 1% /var

/dev/hd3 26214400 18412260 30% 180 1% /tmp

/dev/hd1 7340032 7267456 1% 493 1% /home

/proc - - - - - /proc

/dev/hd10opt 27262976 25581188 7% 54239 1% /opt

/dev/fslv01 393216 390992 1% 45 1% /root

/dev/usaflv 98304000 84807100 14% 24707 1% /lsdb

/dev/lsdblv1 5242880 5221944 1% 17 1% /lsdbfast1

/dev/lsdblv2 5242880 5221944 1% 17 1% /lsdbfast2

/dev/lsdblv3 5242880 5221944 1% 17 1% /lsdbfast3

/dev/lsdblv4 5242880 5221944 1% 17 1% /lsdbfast4

/dev/lsdblv5 5242880 5221944 1% 17 1% /lsdbfast5

/dev/lsdblv6 5242880 5221944 1% 17 1% /lsdbfast7

/dev/lsdblv7 5242880 5221944 1% 17 1% /lsdbfast8

/dev/lsdblv8 5242880 5221944 1% 17 1% /lsdbfast9

/dev/lsdblv10 5242880 5221944 1% 17 1% /lsdbfast10

/dev/lsdblv11 5242880 5221944 1% 17 1% /lsdbfast11

/dev/lsdblv1i 5242880 5221944 1% 17 1% /lsdbfast1i

/dev/lsdblv2i 5242880 5221944 1% 17 1% /lsdbfast2i

/dev/lsdblv3i 5242880 5221944 1% 17 1% /lsdbfast3i

/dev/lsdblv4i 5242880 5221944 1% 17 1% /lsdbfast4i

/dev/lsdblv5i 5242880 5221944 1% 17 1% /lsdbfast5i