How to determine if your PE DB2 database has the correct tablespace pagesize and/or code page for upgrading to Process Engine 5.0

Content

Overview

During upgrade to Process Engine 5.0, DB2 for Linux, UNIX and Windows (LUW), and zOS database string data types will be changed from VARCHAR to VARGRAPHIC. This will be done to support UNICODE character based strings. The VARGRAPHIC data types require twice as much storage space as does the VARCHAR data type BYTE based strings. In addition to the increased page size requirements Process Engine 5.0 also requires that the database be defined with a UTF-8 character set.

In DB2, the table space pagesize determines the maximum number of bytes that can be stored in a row of a table. The minimum DB2 table space pagesize for Process Engine 5.0 is 32 KB. Process Engine 3.5 and 4.X supported DB2 table space page sizes of 8 – 32 KB. Sites upgrading to Process Engine 5.0 might find that their defined table space size is no longer adequate to store the Process Engine 5.0 character based UNICODE strings.

This techdoc was written to assist existing Process Engine 4.x on DB2 for LUW and zOS databases - that are planning to upgrade to Process Engine 5.0 - on how to gather information on their DB2 table space pagesize, current max row size and character set. This document will also help in determining a course of action based on the information that is gathered.

Process

Summary:

Table space pagesize – How to determine if your current DB2 database table space pagesize will be able to handle the increase in storage space that is needed to upgrade to Process Engine 5.0 and support the increased row length.

DB2 database code page - How to determine if your current DB2 database code page is supported for Process Engine 5.0.

Table space pagesize:

This section is divided into DB2 LUW and DB2 z/OS and within those versions of DB2 there are steps to show you how to:

1. Find your current defined table space pagesize.
2. Calculate the row size your current data will need in a Process Engine 5.0 DB2 database. An example is provided for DB2 LUW only at the end of the document.
3. Determine if you need to increase your table space pagesize.

DB2 LUW

1. Find your current defined table space pagesize.

The current table space pagesize can be obtained from

db2=> list tablespaces show detail
From the table space listing, for each Process Engine table space, find the page size:
Tablespace ID = 12
Name = PE_DATA
Type = Database managed space
…
Page size (bytes) = 32768

2. Calculate the row size your current data will need in a Process Engine 5.0 DB2 database.

a. Get a list of Process Engine tables by running the following command.

DB2 => select tabname from syscat.tables where tabschema = 'F_SW' and type = 'T'
and (tabname LIKE 'VWQUEUE%' OR tabname LIKE 'VWROSTER%' OR tabname LIKE 'VWLOG%')
where F_SW is the database runtime user name.

b. For each Process Engine table run the following 7 SQL statements:
select SUM(LENGTH + 1) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'Process Engine TABLE NAME' and typename = 'SMALLINT'
select SUM(LENGTH + 1) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'Process Engine TABLE NAME' and typename = 'INTEGER'
select SUM(LENGTH + 1) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'Process Engine TABLE NAME' and (typename = 'BIGINT' OR typename = 'DOUBLE')
select SUM(LENGTH +2) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'Process Engine TABLE NAME' and typename = 'DECIMAL'
select SUM(LENGTH*2 + 5) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'Process Engine TABLE NAME' and typename = 'VARCHAR'
select SUM(LENGTH + 1) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'Process Engine TABLE NAME' and typename = 'CHARACTER'
select SUM(317) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'Process Engine TABLE NAME' and typename = 'BLOB'


c. Sum the returned seven values to get a current maximum defined row size for each Process Engine table. The sum value will be in bytes. Convert the number of bytes to K by dividing the sum by 1024 , then round up to the next KB.

3. Determine if you need to increase your table space pagesize for DB2 LUW.

Using the table with the largest calculated max row size, answer the following questions to determine if the table space pagesize in your Process Engine database will have to be increased before upgrading to Process Engine 5.0.

If you have a table space pagesize of 32KB and your max row size is under 30KB
- You do not need to do anything. Your pagesize in combination with the max row size is fine.
If you have a table space pagesize of 32KB and your max row size is over 30KB
- You need to analyze the need for the number and amount of exposed fields in your Process Engine tables. To determine the need for exposed fields – check with the site Application Programmer and the site Process Engine Administrator. Changing the size of exposed fields could affect how the Process Engine application runs.
If you have a table space pagesize of 16KB and your max row size is under 14KB
- You can stay at 16KB for the upgrade, however we recommend that you increase the page size of your table spaces to 32KB as soon as you can.
If you have a table space pagesize of under 16KB you must increase the page size of your table spaces to 32KB before upgrading. See Planning and preparing for IBM FileNet P8: http://publib.boulder.ibm.com/infocenter/p8docs/v5r0m0/index.jsp?topic=/com.ibm.p8toc.doc/planning.htm

DB2 zOS

1. Find your current defined table space pagesize.

With DB2 zOS, Process Engine uses DB2 Storage Management Subsystem (SMS). With SMS each table has its own table space. The current table space pagesize for each table can be obtained from

db2 => select name, bpool, pgsize, ntables from sysibm.systablespace where name like 'VWQU%' or name like 'VWLO%' or name like 'VWROS%'

2. Calculate the row size your current data will need in a Process Engine 5.0 DB2 database.

a. Get a list of Process Engine table names by running the following command.

db2 => select name from sysibm.systables where creator = 'FSW1' and type = 'T' and (name like 'VWQUEUE%' or name like 'VWROSTER%' or name like 'VWLOG%')
where FSW1 is the database user name.

b. For each Process Engine table run the following 7 SQL statements:
select SUM(LENGTH + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' and TBNAME = 'Process Engine TABLE NAME' and COLTYPE = 'SMALLINT'
select SUM(LENGTH + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' and TBNAME = 'Process Engine TABLE NAME' and COLTYPE = 'INTEGER'
select SUM(LENGTH + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' and TBNAME = 'Process Engine TABLE NAME' and (COLTYPE = 'BIGINT' OR COLTYPE = 'DOUBLE')
select SUM(LENGTH + 1 + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' and TBNAME = 'Process Engine TABLE NAME' and COLTYPE = 'DECIMAL'
select SUM(LENGTH*2 + 2 + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' and TBNAME = 'Process Engine TABLE NAME' and COLTYPE = 'VARCHAR'
select SUM(LENGTH + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' and TBNAME = 'Process Engine TABLE NAME' and COLTYPE = 'CHAR'
select SUM(6 + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' and TBNAME = 'Process Engine TABLE NAME' and COLTYPE = 'BLOB'


c. Sum the returned seven values, plus 8, to get a current maximum defined row size for each Process Engine table. The sum value will be in bytes. Convert the number of bytes to KB by dividing the sum by 1024, then round up to the next KB.

3. Determine if you need to increase your table space pagesize for DB2 z/OS.

Using the table with the largest calculated max row size; answer the following questions to determine if the table space pagesize in your Process Engine database will have to be increased before upgrading to Process Engine 5.0.

If you have a table space pagesize of 32KB and your max row size is under 30KB
- You do not need to do anything. Your pagesize in combination with the max row size is fine.
If you have a table space pagesize of 32KB and your max row size is over 30KB
- You need to analyze the need for the number and amount of exposed fields in your Process Engine tables. To determine the need for exposed fields – check with the site Application Programmer and the site Process Engine Administrator. Changing the size of exposed fields could affect how the Process Engine application runs.
If you have a table space pagesize of 16KB and your max row size is under 14KB
- You can stay at 16KB for the upgrade, however we recommend that you increase the page size of your table spaces to 32KB as soon as you can.
If you have a table space pagesize of under 16KB you must increase the page size of your table spaces to 32KB before upgrading. See Planning and preparing for IBM FileNet P8: http://publib.boulder.ibm.com/infocenter/p8docs/v5r0m0/index.jsp?topic=/com.ibm.p8toc.doc/planning.htm

DB2 database code page:

1. Get the DB2 database code page

DB2 LUW

The current database code page can be obtained by running the following command:

db2 => get db cfg show detail

The following is excerpt of the information returned by this command:
Database Configuration for Database
…
Database territory = US
Database code page = 1208
Database code set = UTF-8
…

DB2 zOS

The current database code page can be obtained by running the following SQL statement:

db2 => select name, sbcs_ccsid, dbcs_ccsid, mixed_ccsid from sysibm.sysdatabase where name = 'PEDBASE'

where PEDBASE is the PE database name.

Example:
db2 => select name, sbcs_ccsid, dbcs_ccsid, mixed_ccsid from sysibm.sysdatabase where name = 'PEDBASE'
NAME SBCS_CCSID DBCS_CCSID MIXED_CCSID
------------ ---------- ---------- -----------
PEDBASE 367 1200 1208
The values above: 367, 1200, 1208 – mean that this database is set up for UNICODE and UTF-8 encoding.

2. Determine if you need to change your DB2 database code page:

If you have a UTF-8 code set then you are fine.

If you have any other code set, run the following SQL command:
db2 => create table testTab (col1 vargraphic(10))
If that statement returns an error saying the VARGRAHIC data type is not supported with this code page/character set then you must migrate the Process Engine data to a database with a code set of 1200 and 1208 ( UTF-16 and UTF-8 ). See Planning and preparing for IBM FileNet P8.

Examples:

How to get Table space pagesize for DB2 LUW:

db2=> list tablespaces show detail

From the table space listing, for each Process Engine table space, find the page size:
Tablespace ID = 12
Name = PE_DATA
Type = Database managed space
…
Page size (bytes) = 32768

Get Process Engine table names from the DB2 database:

db2 => select tabname from syscat.tables where tabschema = 'F_SW' and type = 'T' and (tabname like 'VWQUEUE%' OR tabname like 'VWROSTER%' or tabname like 'VWLOG%')

TABNAME
--------------------------------------------------------------------------------------------------------------------------------
VWLOG1_113
VWQUEUE1_100
VWQUEUE1_101
VWQUEUE1_102
VWQUEUE1_103
VWQUEUE1_104
VWQUEUE1_1199
VWROSTER1_112

7 record(s) selected.

Run queries for each table – example using table 'VWQUEUE1_1199':

For this example calculate the sum of the returned seven values to get the maximum defined row size for this Process Engine table.

db2 => select SUM(LENGTH + 1) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'VWQUEUE1_1199' and typename = 'SMALLINT'
1
-----------
6

1 record(s) selected.

db2 => select SUM(LENGTH + 1) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'VWQUEUE1_1199' and typename = 'INTEGER'
1
-----------
75

1 record(s) selected.

db2 => select SUM(LENGTH + 1) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'VWQUEUE1_1199' and (typename = 'BIGINT' OR typename = 'DOUBLE')
1
-----------
9

1 record(s) selected.

db2 => select SUM(LENGTH + 2) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'VWQUEUE1_1199' and typename = 'DECIMAL'
1
-----------
15

1 record(s) selected.

db2 => select SUM(LENGTH*2 + 5) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'VWQUEUE1_1199' and typename = 'VARCHAR'
1
-----------
9814

1 record(s) selected.


db2 => select SUM(LENGTH + 1) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'VWQUEUE1_1199' and typename = 'CHARACTER'
1
-----------
17

1 record(s) selected.

db2 => select SUM(317) from syscat.columns where TABSCHEMA = 'F_SW' and tabname = 'VWQUEUE1_1199' and typename = 'BLOB'
1
-----------
317

1 record(s) selected.

6+75+9+15+9814+17+317= 10253 bytes row size that will be needed in Process Engine
Convert the sum that is in bytes to KB -- 10253/1024 = 10.01KB
Round up to next KB, 11 KB.
Max row size that will be required by Process Engine 5.0 for this table will be 11 KB.

For this example determine if the table space pagesize needs to be changed.

The answer would be NO, the current table space pagesize is 32 KB and the row data will require 11 KB row size in Process Engine 5.0.