IBM FileNet P8, Version 5.2.1            

Workflow system database table row size

Some databases have a maximum byte limit for the row length in a table. Because each exposed field that you create becomes a table column, you can exceed the row size limitation and receive an error when adding more exposed fields to a queue, roster, or log.

Each column data type consumes a certain number of bytes from the row length. Run the following SQL queries and add the byte values returned from each query to get the total bytes that are used for the row. The formulas are approximate, but provide a good indication if the row length is nearing the limit. See your database documentation for the create table statement for more information.

DB2® for Linux, UNIX and Windows

Get a list of workflow system tables by running the following command. In all of the following examples, F_SW is the database user name.
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%')
For each workflow system table, run the following SQL statements:
select SUM(LENGTH + 1) from syscat.columns where TABSCHEMA = 'F_SW' 
and tabname = 'Workflow TABLE NAME' and typename = 'SMALLINT' 

select SUM(LENGTH + 1) from syscat.columns where TABSCHEMA = 'F_SW' 
and tabname = 'Workflow TABLE NAME' and typename = 'INTEGER'

select SUM(LENGTH + 1) from syscat.columns where TABSCHEMA = 'F_SW' 
and tabname = 'Workflow TABLE NAME' 
and (typename = 'BIGINT' OR typename = 'DOUBLE')

select SUM(LENGTH +2) from syscat.columns where TABSCHEMA = 'F_SW' 
and tabname = 'Workflow TABLE NAME' and typename = 'DECIMAL' 

select SUM(LENGTH*2 + 5) from syscat.columns where TABSCHEMA = 'F_SW' 
and tabname = 'Workflow TABLE NAME' and typename = 'VARCHAR' 

select SUM(LENGTH + 1) from syscat.columns where TABSCHEMA = 'F_SW' 
and tabname = 'Workflow TABLE NAME' and typename = 'CHARACTER'

select SUM(317) from syscat.columns where TABSCHEMA = 'F_SW' 
and tabname = 'Workflow TABLE NAME' and typename = 'BLOB' 

Sum the returned values to get a current maximum defined row size for each workflow system table. The sum value is in bytes. Convert the number of bytes to kilobytes by dividing the sum by 1024, and then round up to the next KB.

DB2 for z/OS®

Get a list of workflow system tables by running the following command. In all of the following examples, FSW1 is the database user name.
db2 => select name from sysibm.systables 
where creator = 'FSW1' and type = 'T' 
and (name like 'VWQUEUE%' or name like 'VWROSTER%' or name like 'VWLOG%') 
For each workflow system table, run the following SQL statements:
select SUM(LENGTH + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' 
and TBNAME = 'Workflow TABLE NAME' and COLTYPE = 'SMALLINT' 

select SUM(LENGTH + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' 
and TBNAME = 'Workflow TABLE NAME' and COLTYPE = 'INTEGER'

select SUM(LENGTH + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' 
and TBNAME = 'Workflow TABLE NAME' 
and (COLTYPE = 'BIGINT' OR COLTYPE = 'DOUBLE')

select SUM(LENGTH + 1 + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' 
and TBNAME = 'Workflow TABLE NAME' and COLTYPE = 'DECIMAL' 

select SUM(LENGTH*2 + 2 + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' 
and TBNAME = 'Workflow TABLE NAME' and COLTYPE = 'VARCHAR' 

select SUM(LENGTH + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' 
and TBNAME = 'Workflow TABLE NAME' and COLTYPE = 'CHAR'

select SUM(6 + 1) from sysibm.syscolumns where TBCREATOR = 'FSW1' 
and TBNAME = 'Workflow TABLE NAME' and COLTYPE = 'BLOB' 

Sum the returned values, plus 8, to get a current maximum defined row size for each workflow system table. The sum value is in bytes. Convert the number of bytes to kilobytes by dividing the sum by 1024, and then round up to the next KB.



Last updated: March 2016
bpfad028.htm

© Copyright IBM Corporation 2016.