By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,242 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

WITH TYPEINTS

P: n/a
In a DB2 server setup on Sun, the clients are using Developer 2000 as
frontend on the ODBC connection.

A particular report is taking too long to execute. The snapshot shows
the following. The query seems to be generated by DB2, as none of the
reports have this particular query. Is there any way by which I can
speeden up / avoid the statement? Am I missing something here? BTW, the
DB2 level V8FP4.

Thanks in advance.

Number of executions = 1465
Number of compilations = 0
Worst preparation time (ms) = 138
Best preparation time (ms) = 138
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 0
Internal rows updated = 0
Rows written = 0
Statement sorts = 20
Buffer pool data logical reads = 0
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 0
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 75.858788
Total user cpu time (sec.ms) = 0.050000
Total system cpu time (sec.ms) = 0.040000
Statement text = WITH TYPEINTS ( TYPEINT, COLTYPE
) AS ( VALUES ( SMALLINT(1 ), CHAR( 'INTEGER', 8) ), ( SMALLINT(2
), CHAR( 'SMALLINT', 8) ), ( SMALLINT(3 ), CHAR( 'BIGINT', 8) ), (
SMALLINT(4 ), CHAR( 'REAL', 8) ), ( SMALLINT(5 ), CHAR( 'DOUBLE',
8) ), ( SMALLINT(6 ), CHAR( 'CHAR', 8) ), ( SMALLINT(7 ), CHAR(
'VARCHAR', 8) ), ( SMALLINT(8 ), CHAR( 'LONGVAR', 8) ), (
SMALLINT(9 ), CHAR( 'DECIMAL', 8) ), ( SMALLINT(10), CHAR( 'GRAPHIC',
8) ), ( SMALLINT(11), CHAR( 'VARGRAPH', 8) ), ( SMALLINT(12), CHAR(
'LONGVARG', 8) ), ( SMALLINT(13), CHAR( 'BLOB', 8) ), (
SMALLINT(14), CHAR( 'CLOB', 8) ), ( SMALLINT(15), CHAR( 'DBCLOB',
8) ), ( SMALLINT(16), CHAR( 'DATE', 8) ), ( SMALLINT(17), CHAR(
'TIME', 8) ), ( SMALLINT(18), CHAR( 'TIMESTMP', 8) ), (
SMALLINT(19), CHAR( 'DATALINK', 8) ), ( SMALLINT(20), CHAR( 'STRUCT',
8) ), ( SMALLINT(21), CHAR( 'DISTINCT', 8) ), ( SMALLINT(22), CHAR(
'REF', 8) ) ), SYSIBM.SQLCOLS ( TABLE_CAT, TABLE_SCHEM,
TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE,
BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE, REMARKS,
COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH,
ORDINAL_POSITION, IS_NULLABLE, JDBC_DATA_TYPE, SCOPE_CATLOG,
SCOPE_SCHEMA, SCOPE_TABLE, SOURCE_DATA_TYPE, DBNAME, PSEUDO_COLUMN
) AS ( SELECT CAST( NULL AS VARCHAR(128) ), RTRIM(T.CREATOR), T.NAME,
C.NAME, SMALLINT( CASE WHEN I.TYPEINT=1 THEN 4 WHEN I.TYPEINT=2
THEN 5 WHEN I.TYPEINT=3 THEN -5 WHEN I.TYPEINT=4 THEN 7 WHEN
I.TYPEINT=5 THEN 8 WHEN I.TYPEINT=6 AND C.CODEPAGE <0 then 1 WHEN
I.TYPEINT=6 AND C.CODEPAGE = 0 then -2 WHEN I.TYPEINT=7 AND
C.CODEPAGE <0 THEN 12 WHEN I.TYPEINT=7 AND C.CODEPAGE = 0 THEN -3
WHEN I.TYPEINT=8 AND C.CODEPAGE <0 THEN -1 WHEN I.TYPEINT=8 AND
C.CODEPAGE = 0 THEN -4 WHEN I.TYPEINT=9 THEN 3 WHEN I.TYPEINT=10
THEN -95 WHEN I.TYPEINT=11 THEN -96 WHEN I.TYPEINT=12 THEN -97
WHEN I.TYPEINT=13 THEN -98 WHEN I.TYPEINT=14 THEN -99 WHEN
I.TYPEINT=15 THEN -350 WHEN I.TYPEINT=16 THEN 91 WHEN I.TYPEINT=17
THEN 92 WHEN I.TYPEINT=18 THEN 93 WHEN I.TYPEINT=19 THEN -400
WHEN I.TYPEINT=20 THEN 17 WHEN I.TYPEINT=21 THEN 17 ELSE 0 END),
CAST( CASE WHEN I.TYPEINT=1 THEN 'INTEGER' WHEN I.TYPEINT=2 THEN
'SMALLINT' WHEN I.TYPEINT=3 THEN 'BIGINT' WHEN I.TYPEINT=4 THEN
'REAL' WHEN I.TYPEINT=5 THEN 'DOUBLE' WHEN I.TYPEINT=6 AND
C.CODEPAGE <0 THEN 'CHAR' WHEN I.TYPEINT=6 AND C.CODEPAGE = 0 THEN
'CHAR () FOR BIT DATA' WHEN I.TYPEINT=7 AND C.CODEPAGE <0 THEN
'VARCHAR' WHEN I.TYPEINT=7 AND C.CODEPAGE = 0 THEN 'VARCHAR () FOR
BIT DATA' WHEN I.TYPEINT=8 AND C.CODEPAGE <0 THEN 'LONG VARCHAR'
WHEN I.TYPEINT=8 AND C.CODEPAGE = 0 THEN 'LONG VARCHAR FOR BIT DATA'
WHEN I.TYPEINT=9 THEN 'DECIMAL' WHEN I.TYPEINT=10 THEN 'GRAPHIC'
WHEN I.TYPEINT=11 THEN 'VARGRAPHIC' WHEN I.TYPEINT=12 THEN 'LONG
VARGRAPHIC' WHEN I.TYPEINT=13 THEN 'BLOB' WHEN I.TYPEINT=14 THEN
'CLOB' WHEN I.TYPEINT=15 THEN 'DBCLOB' WHEN I.TYPEINT=16 THEN
'DATE' WHEN I.TYPEINT=17 THEN 'TIME' WHEN I.TYPEINT=18 THEN
'TIMESTAMP' WHEN I.TYPEINT=19 THEN 'DATALINK' WHEN I.TYPEINT=20
THEN '"' || RTRIM(D.SCHEMA) || '"."' || D.NAME || '"' WHEN
I.TYPEINT=21 THEN '"' || RTRIM(D.SCHEMA) || '"."' || D.NAME || '"'
ELSE '' END AS VARCHAR(261) ), CASE WHEN I.TYPEINT=1 THEN 10 WHEN
I.TYPEINT=2 THEN 5 WHEN I.TYPEINT=3 THEN 19 WHEN I.TYPEINT=4 THEN
24 WHEN I.TYPEINT=5 THEN 53 WHEN I.TYPEINT=6 THEN C.LENGTH WHEN
I.TYPEINT=7 THEN C.LENGTH WHEN I.TYPEINT=8 THEN C.LENGTH WHEN
I.TYPEINT=9 THEN C.LENGTH WHEN I.TYPEINT=10 THEN C.LENGTH WHEN
I.TYPEINT=11 THEN C.LENGTH WHEN I.TYPEINT=12 THEN C.LENGTH WHEN
I.TYPEINT=13 THEN C.LONGLENGTH WHEN I.TYPEINT=14 THEN C.LONGLENGTH
WHEN I.TYPEINT=15 THEN C.LONGLENGTH WHEN I.TYPEINT=16 THEN 10 WHEN
I.TYPEINT=17 THEN 8 WHEN I.TYPEINT=18 THEN 26 WHEN I.TYPEINT=20
THEN D.LENGTH WHEN I.TYPEINT=21 THEN D.LENGTH ELSE C.LENGTH END,
CASE WHEN I.TYPEINT=1 THEN 4 WHEN I.TYPEINT=2 THEN 2 WHEN
I.TYPEINT=3 THEN 8 WHEN I.TYPEINT=4 THEN 4 WHEN I.TYPEINT=5 THEN 8
WHEN I.TYPEINT=6 THEN C.LENGTH WHEN I.TYPEINT=7 THEN C.LENGTH WHEN
I.TYPEINT=8 THEN C.LENGTH WHEN I.TYPEINT=9 THEN C.LENGTH + 2 WHEN
I.TYPEINT=10 THEN C.LENGTH * 2 WHEN I.TYPEINT=11 THEN C.LENGTH * 2
WHEN I.TYPEINT=12 THEN C.LENGTH * 2 WHEN I.TYPEINT=13 THEN
C.LONGLENGTH WHEN I.TYPEINT=14 THEN C.LONGLENGTH WHEN I.TYPEINT=15
THEN C.LONGLENGTH * 2 WHEN I.TYPEINT=16 THEN 6 WHEN I.TYPEINT=17
THEN 6 WHEN I.TYPEINT=18 THEN 16 WHEN I.TYPEINT=19 THEN C.LENGTH
WHEN I.TYPEINT=20 AND D.SOURCETYPE NOT IN
('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH WHEN I.TYPEINT=20 AND D.SOURCETYPE IN
('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH * 2 WHEN I.TYPEINT=21 AND D.SOURCETYPE NOT
IN ('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH WHEN I.TYPEINT=21 AND D.SOURCETYPE IN
('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH * 2 ELSE C.LENGTH END, SMALLINT( CASE WHEN I.TYPEINT=1
THEN 0 WHEN I.TYPEINT=2 THEN 0 WHEN I.TYPEINT=3 THEN 0 WHEN
I.TYPEINT=4 THEN NULL WHEN I.TYPEINT=5 THEN NULL WHEN I.TYPEINT=6
THEN NULL WHEN I.TYPEINT=7 THEN NULL WHEN I.TYPEINT=8 THEN NULL
WHEN I.TYPEINT=9 THEN C.SCALE WHEN I.TYPEINT=10 THEN NULL WHEN
I.TYPEINT=11 THEN NULL WHEN I.TYPEINT=12 THEN NULL WHEN
I.TYPEINT=13 THEN NULL WHEN I.TYPEINT=14 THEN NULL WHEN
I.TYPEINT=15 THEN NULL WHEN I.TYPEINT=16 THEN NULL WHEN
I.TYPEINT=17 THEN 0 WHEN I.TYPEINT=18 THEN 6 WHEN I.TYPEINT=19 THEN
NULL WHEN I.TYPEINT=20 AND D.SOURCETYPE='DECIMAL' THEN
D.SCALE WHEN I.TYPEINT=20 AND D.SOURCETYPE IN
('INTEGER','SMALLINT','BIGINT','TIME') THEN 0 WHEN
I.TYPEINT=20 AND D.SOURCETYPE='TIMESTAMP' THEN 6 WHEN
I.TYPEINT=21 AND D.SOURCETYPE='DECIMAL' THEN D.SCALE
WHEN I.TYPEINT=21 AND D.SOURCETYPE IN
('INTEGER','SMALLINT','BIGINT','TIME') THEN 0 WHEN
I.TYPEINT=21 AND D.SOURCETYPE='TIMESTAMP' THEN 6 ELSE
NULL END ), SMALLINT(CASE WHEN I.TYPEINT=1 THEN 10 WHEN
I.TYPEINT=2 THEN 10 WHEN I.TYPEINT=3 THEN 10 WHEN I.TYPEINT=4 THEN
2 WHEN I.TYPEINT=5 THEN 2 WHEN I.TYPEINT=6 THEN NULL WHEN
I.TYPEINT=7 THEN NULL WHEN I.TYPEINT=8 THEN NULL WHEN I.TYPEINT=9
THEN 10 WHEN I.TYPEINT=10 THEN NULL WHEN I.TYPEINT=11 THEN NULL
WHEN I.TYPEINT=12 THEN NULL WHEN I.TYPEINT=13 THEN NULL WHEN
I.TYPEINT=14 THEN NULL WHEN I.TYPEINT=15 THEN NULL WHEN
I.TYPEINT=16 THEN NULL WHEN I.TYPEINT=17 THEN NULL WHEN
I.TYPEINT=18 THEN NULL WHEN I.TYPEINT=19 THEN NULL WHEN
I.TYPEINT=20 AND D.SOURCETYPE IN
('DECIMAL','INTEGER','SMALLINT','BIGINT') THEN 10 WHEN
I.TYPEINT=20 AND D.SOURCETYPE IN ('REAL','FLOAT','DOUBLE')
THEN 2 WHEN I.TYPEINT=21 AND D.SOURCETYPE IN
('DECIMAL','INTEGER','SMALLINT','BIGINT') THEN 10 WHEN
I.TYPEINT=21 AND D.SOURCETYPE IN ('REAL','FLOAT','DOUBLE')
THEN 2 ELSE NULL END), SMALLINT(CASE WHEN C.NULLS='Y' THEN 1
ELSE 0 END), C.REMARKS, C.DEFAULT, SMALLINT(CASE WHEN I.TYPEINT=1
THEN 4 WHEN I.TYPEINT=2 THEN 5 WHEN I.TYPEINT=3 THEN -5 WHEN
I.TYPEINT=4 THEN 7 WHEN I.TYPEINT=5 THEN 8 WHEN I.TYPEINT=6 AND
C.CODEPAGE <0 then 1 WHEN I.TYPEINT=6 AND C.CODEPAGE = 0 then -2
WHEN I.TYPEINT=7 AND C.CODEPAGE <0 THEN 12 WHEN I.TYPEINT=7 AND
C.CODEPAGE = 0 THEN -3 WHEN I.TYPEINT=8 AND C.CODEPAGE <0 THEN -1
WHEN I.TYPEINT=8 AND C.CODEPAGE = 0 THEN -4 WHEN I.TYPEINT=9 THEN 3
WHEN I.TYPEINT=10 THEN -95 WHEN I.TYPEINT=11 THEN -96 WHEN
I.TYPEINT=12 THEN -97 WHEN I.TYPEINT=13 THEN -98 WHEN I.TYPEINT=14
THEN -99 WHEN I.TYPEINT=15 THEN -350 WHEN I.TYPEINT=16 THEN 9
WHEN I.TYPEINT=17 THEN 9 WHEN I.TYPEINT=18 THEN 9 WHEN I.TYPEINT=19
THEN -400 WHEN I.TYPEINT=20 THEN 17 WHEN I.TYPEINT=21 THEN 17
ELSE 0 END), SMALLINT(CASE WHEN I.TYPEINT=1 THEN NULL WHEN
I.TYPEINT=2 THEN NULL WHEN I.TYPEINT=3 THEN NULL WHEN I.TYPEINT=4
THEN NULL WHEN I.TYPEINT=5 THEN NULL WHEN I.TYPEINT=6 THEN NULL
WHEN I.TYPEINT=7 THEN NULL WHEN I.TYPEINT=8 THEN NULL WHEN
I.TYPEINT=9 THEN NULL WHEN I.TYPEINT=10 THEN NULL WHEN I.TYPEINT=11
THEN NULL WHEN I.TYPEINT=12 THEN NULL WHEN I.TYPEINT=13 THEN NULL
WHEN I.TYPEINT=14 THEN NULL WHEN I.TYPEINT=15 THEN NULL WHEN
I.TYPEINT=16 THEN 1 WHEN I.TYPEINT=17 THEN 2 WHEN I.TYPEINT=18 THEN
3 WHEN I.TYPEINT=19 THEN NULL WHEN I.TYPEINT=20 AND
D.SOURCETYPE='DATE' THEN 1 WHEN I.TYPEINT=20 AND
D.SOURCETYPE='TIME' THEN 2 WHEN I.TYPEINT=20 AND
D.SOURCETYPE='TIMESTAMP' THEN 3 WHEN I.TYPEINT=21 AND
D.SOURCETYPE='DATE' THEN 1 WHEN I.TYPEINT=21 AND
D.SOURCETYPE='TIME' THEN 2 WHEN I.TYPEINT=21 AND
D.SOURCETYPE='TIMESTAMP' THEN 3 ELSE NULL END), CASE WHEN
I.TYPEINT=1 THEN NULL WHEN I.TYPEINT=2 THEN NULL WHEN I.TYPEINT=3 THEN
NULL WHEN I.TYPEINT=4 THEN NULL WHEN I.TYPEINT=5 THEN NULL WHEN
I.TYPEINT=6 THEN C.LENGTH WHEN I.TYPEINT=7 THEN C.LENGTH WHEN
I.TYPEINT=8 THEN C.LENGTH WHEN I.TYPEINT=9 THEN NULL WHEN I.TYPEINT=10
THEN C.LENGTH * 2 WHEN I.TYPEINT=11 THEN C.LENGTH * 2 WHEN I.TYPEINT=12
THEN C.LENGTH * 2 WHEN I.TYPEINT=13 THEN C.LONGLENGTH WHEN I.TYPEINT=14
THEN C.LONGLENGTH WHEN I.TYPEINT=15 THEN C.LONGLENGTH * 2 WHEN
I.TYPEINT=16 THEN NULL WHEN I.TYPEINT=17 THEN NULL WHEN I.TYPEINT=18
THEN NULL WHEN I.TYPEINT=19 THEN NULL WHEN I.TYPEINT=20 AND
D.SOURCETYPE IN ('CHARACTER','VARCHAR','LONG
VARCHAR','BLOB','CLOB') THEN D.LENGTH WHEN I.TYPEINT=20 AND
D.SOURCETYPE IN ('GRAPHIC','VARGRAPHIC','LONG
VARGRAPHIC','DBCLOB') THEN D.LENGTH * 2 WHEN I.TYPEINT=21 AND
D.SOURCETYPE IN ('CHARACTER','VARCHAR','LONG
VARCHAR','BLOB','CLOB') THEN D.LENGTH WHEN I.TYPEINT=21
AND D.SOURCETYPE IN ('GRAPHIC','VARGRAPHIC','LONG
VARGRAPHIC','DBCLOB') THEN D.LENGTH * 2 ELSE NULL END, C.COLNO
+ 1, CASE WHEN C.NULLS='Y' THEN 'YES' ELSE 'NO' END, SMALLINT( CASE
WHEN I.TYPEINT=1 THEN 4 WHEN I.TYPEINT=2 THEN 5 WHEN I.TYPEINT=3
THEN -5 WHEN I.TYPEINT=4 THEN 7 WHEN I.TYPEINT=5 THEN 8 WHEN
I.TYPEINT=6 AND C.CODEPAGE <0 then 1 WHEN I.TYPEINT=6 AND
C.CODEPAGE = 0 then -2 WHEN I.TYPEINT=7 AND C.CODEPAGE <0 THEN 12
WHEN I.TYPEINT=7 AND C.CODEPAGE = 0 THEN -3 WHEN I.TYPEINT=8 AND
C.CODEPAGE <0 THEN -1 WHEN I.TYPEINT=8 AND C.CODEPAGE = 0 THEN -4
WHEN I.TYPEINT=9 THEN 3 WHEN I.TYPEINT=10 THEN 1 WHEN I.TYPEINT=11
THEN 12 WHEN I.TYPEINT=12 THEN -1 WHEN I.TYPEINT=13 THEN 2004
WHEN I.TYPEINT=14 THEN 2005 WHEN I.TYPEINT=15 THEN 2005 WHEN
I.TYPEINT=16 THEN 91 WHEN I.TYPEINT=17 THEN 92 WHEN I.TYPEINT=18
THEN 93 WHEN I.TYPEINT=19 THEN 70 WHEN I.TYPEINT=20 THEN 2002
WHEN I.TYPEINT=21 THEN 2001 ELSE 0 END), CAST( NULL AS VARCHAR(128)
), CAST( NULL AS VARCHAR(128) ), CAST( NULL AS VARCHAR(128) ),
SMALLINT( CASE WHEN D.SOURCETYPE IS NULL THEN NULL WHEN
D.SOURCETYPE='INTEGER' THEN 4 WHEN D.SOURCETYPE='SMALLINT' THEN 5
WHEN D.SOURCETYPE='BIGINT' THEN -5 WHEN D.SOURCETYPE='FLOAT' AND
C.LENGTH=4 THEN 7 WHEN D.SOURCETYPE='FLOAT' AND C.LENGTH=8 THEN 8
WHEN D.SOURCETYPE='REAL' THEN 7 WHEN D.SOURCETYPE='DOUBLE' THEN 8
WHEN D.SOURCETYPE='CHARACTER' AND C.CODEPAGE <0 then 1 WHEN
D.SOURCETYPE='CHARACTER' AND C.CODEPAGE = 0 then -2 WHEN
D.SOURCETYPE='VARCHAR' AND C.CODEPAGE <0 THEN 12 WHEN
D.SOURCETYPE='VARCHAR' AND C.CODEPAGE = 0 THEN -3 WHEN
D.SOURCETYPE='LONG VARCHAR' AND C.CODEPAGE <0 THEN -1 WHEN
D.SOURCETYPE='LONG VARCHAR' AND C.CODEPAGE = 0 THEN -4 WHEN
D.SOURCETYPE='DECIMAL' THEN 3 WHEN D.SOURCETYPE='GRAPHIC' THEN -95
WHEN D.SOURCETYPE='VARGRAPHIC' THEN -96 WHEN D.SOURCETYPE='LONG
VARGRAPHIC' THEN -97 WHEN D.SOURCETYPE='BLOB' THEN -98 WHEN
D.SOURCETYPE='CLOB' THEN -99 WHEN D.SOURCETYPE='DBCLOB' THEN -350
WHEN D.SOURCETYPE='DATE' THEN 91 WHEN D.SOURCETYPE='TIME' THEN 92
WHEN D.SOURCETYPE='TIMESTAMP' THEN 93 WHEN D.SOURCETYPE='DATALINK'
THEN -400 ELSE 0 END), CAST( NULL AS VARCHAR(8) ), SMALLINT(CASE
WHEN C.IDENTITY = 'Y' THEN 2 ELSE 1 END ) FROM SYSIBM.SYSCOLUMNS
C, SYSIBM.SYSDATATYPES D, SYSIBM.SYSTABLES T, TYPEINTS I WHERE
T.NAME LIKE 'VT_CO_M_SUBJECT' ESCAPE '\' AND ( (C.TBCREATOR =
T.CREATOR AND C.TBNAME = T.NAME ) OR (C.TBCREATOR =
T.BASE_SCHEMA AND C.TBNAME = T.BASE_NAME ) ) AND C.COLTYPE <'REF'
AND I.TYPEINT <22 AND C.TYPENAME = D.NAME AND C.TYPESCHEMA =
D.SCHEMA AND C.COLTYPE = I.COLTYPE UNION ALL SELECT CAST( NULL AS
VARCHAR(128) ), RTRIM(T.CREATOR), T.NAME, C.NAME, SMALLINT(20),
CAST( '"' || RTRIM(D.SCHEMA) || '"."' || D.NAME || '"' AS
VARCHAR(261) ), CASE WHEN D.SOURCETYPE = 'REAL' THEN 24 WHEN
D.SOURCETYPE = 'DOUBLE' THEN 53 ELSE D.LENGTH END, CASE
WHEN D.SOURCETYPE NOT IN ('GRAPHIC','VARGRAPHIC','LONG
VARGRAPHIC','DBCLOB') THEN D.LENGTH WHEN D.SOURCETYPE
IN ('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB')
THEN D.LENGTH * 2 ELSE NULL END, SMALLINT( CASE WHEN
D.SOURCETYPE='DECIMAL' THEN D.SCALE WHEN D.SOURCETYPE IN
('INTEGER','SMALLINT','BIGINT','TIME') THEN 0 WHEN
D.SOURCETYPE='TIMESTAMP' THEN 6 ELSE NULL END ),
SMALLINT( CASE WHEN D.SOURCETYPE IN
('DECIMAL','INTEGER','SMALLINT','BIGINT') THEN 10 WHEN
D.SOURCETYPE IN ('REAL','FLOAT','DOUBLE') THEN 2 ELSE NULL
END), SMALLINT(CASE WHEN C.NULLS='Y' THEN 1 ELSE 0 END),
C.REMARKS, C.DEFAULT, SMALLINT( 20 ), SMALLINT( CASE WHEN
D.SOURCETYPE='DATE' THEN 1 WHEN D.SOURCETYPE='TIME' THEN 2 WHEN
D.SOURCETYPE='TIMESTAMP' THEN 3 ELSE NULL END), CASE WHEN
D.SOURCETYPE IN ('CHARACTER','VARCHAR','LONG
VARCHAR','BLOB','CLOB') THEN D.LENGTH WHEN D.SOURCETYPE IN
('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH * 2 ELSE NULL END, C.COLNO + 1, CASE WHEN C.NULLS='Y' THEN
'YES' ELSE 'NO' END, SMALLINT( 2006 ), CAST( NULL AS VARCHAR(128) ),
CAST( NULL AS VARCHAR(128) ), CAST( NULL AS VARCHAR(128) ), SMALLINT(
CASE WHEN D.SOURCETYPE IS NULL THEN NULL WHEN
D.SOURCETYPE='INTEGER' THEN 4 WHEN D.SOURCETYPE='SMALLINT' THEN 5
WHEN D.SOURCETYPE='BIGINT' THEN -5 WHEN D.SOURCETYPE='FLOAT' AND
C.LENGTH=4 THEN 7 WHEN D.SOURCETYPE='FLOAT' AND C.LENGTH=8 THEN 8
WHEN D.SOURCETYPE='REAL' THEN 7 WHEN D.SOURCETYPE='DOUBLE' THEN 8
WHEN D.SOURCETYPE='CHARACTER' AND C.CODEPAGE <0 then 1 WHEN
D.SOURCETYPE='CHARACTER' AND C.CODEPAGE = 0 then -2 WHEN
D.SOURCETYPE='VARCHAR' AND C.CODEPAGE <0 THEN 12 WHEN
D.SOURCETYPE='VARCHAR' AND C.CODEPAGE = 0 THEN -3 WHEN
D.SOURCETYPE='LONG VARCHAR' AND C.CODEPAGE <0 THEN -1 WHEN
D.SOURCETYPE='LONG VARCHAR' AND C.CODEPAGE = 0 THEN -4 WHEN
D.SOURCETYPE='DECIMAL' THEN 3 WHEN D.SOURCETYPE='GRAPHIC' THEN -95
WHEN D.SOURCETYPE='VARGRAPHIC' THEN -96 WHEN D.SOURCETYPE='LONG
VARGRAPHIC' THEN -97 WHEN D.SOURCETYPE='BLOB' THEN -98 WHEN
D.SOURCETYPE='CLOB' THEN -99 WHEN D.SOURCETYPE='DBCLOB' THEN -350
WHEN D.SOURCETYPE='DATE' THEN 91 WHEN D.SOURCETYPE='TIME' THEN 92
WHEN D.SOURCETYPE='TIMESTAMP' THEN 93 WHEN D.SOURCETYPE='DATALINK'
THEN -400 ELSE 0 END ), CAST( NULL AS VARCHAR(8) ), SMALLINT(1) FROM
SYSIBM.SYSCOLUMNS C, SYSIBM.SYSDATATYPES D,
SYSIBM.SYSCOLPROPERTIES P, SYSIBM.SYSTABLES T, TYPEINTS I WHERE
T.NAME LIKE 'VT_CO_M_SUBJECT' ESCAPE '\' AND ( (C.TBCREATOR =
T.CREATOR AND C.TBNAME = T.NAME ) OR (C.TBCREATOR = T.BASE_SCHEMA
AND C.TBNAME = T.BASE_NAME ) ) AND C.COLTYPE = 'REF' AND I.TYPEINT
= 22 AND P.COLNAME = C.NAME AND P.TABSCHEMA = C.TBCREATOR AND
P.TABNAME = C.TBNAME AND P.TARGET_TYPENAME = D.NAME AND
P.TARGET_TYPESCHEMA = D.SCHEMA AND C.COLTYPE = I.COLTYPE ) SELECT
TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, SMALLINT( CASE
DATA_TYPE WHEN 70 THEN -400 WHEN 91 THEN 9 WHEN 92 THEN 10 WHEN
93 THEN 11 WHEN -95 then -8 WHEN -96 then -9 WHEN -97 then -10
ELSE DATA_TYPE END) as DATA_TYPE, TYPE_NAME, COLUMN_SIZE,
BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE, REMARKS,
COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH,
ORDINAL_POSITION, IS_NULLABLE FROM SYSIBM.SQLCOLS WHERE TABLE_NAME
LIKE 'VT_CO_M_SUBJECT' ESCAPE '\' ORDER BY 1,2,3,17

Sep 27 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

bb********@gmail.com wrote:
In a DB2 server setup on Sun, the clients are using Developer 2000 as
frontend on the ODBC connection.

A particular report is taking too long to execute. The snapshot shows
the following. The query seems to be generated by DB2, as none of the
reports have this particular query. Is there any way by which I can
speeden up / avoid the statement? Am I missing something here? BTW, the
DB2 level V8FP4.

Thanks in advance.

Number of executions = 1465
Number of compilations = 0
Worst preparation time (ms) = 138
Best preparation time (ms) = 138
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 0
Internal rows updated = 0
Rows written = 0
Statement sorts = 20
Buffer pool data logical reads = 0
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 0
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 75.858788
Total user cpu time (sec.ms) = 0.050000
Total system cpu time (sec.ms) = 0.040000
Statement text = WITH TYPEINTS ( TYPEINT, COLTYPE
) AS ( VALUES ( SMALLINT(1 ), CHAR( 'INTEGER', 8) ), ( SMALLINT(2
), CHAR( 'SMALLINT', 8) ), ( SMALLINT(3 ), CHAR( 'BIGINT', 8) ), (
SMALLINT(4 ), CHAR( 'REAL', 8) ), ( SMALLINT(5 ), CHAR( 'DOUBLE',
8) ), ( SMALLINT(6 ), CHAR( 'CHAR', 8) ), ( SMALLINT(7 ), CHAR(
'VARCHAR', 8) ), ( SMALLINT(8 ), CHAR( 'LONGVAR', 8) ), (
SMALLINT(9 ), CHAR( 'DECIMAL', 8) ), ( SMALLINT(10), CHAR( 'GRAPHIC',
8) ), ( SMALLINT(11), CHAR( 'VARGRAPH', 8) ), ( SMALLINT(12), CHAR(
'LONGVARG', 8) ), ( SMALLINT(13), CHAR( 'BLOB', 8) ), (
SMALLINT(14), CHAR( 'CLOB', 8) ), ( SMALLINT(15), CHAR( 'DBCLOB',
8) ), ( SMALLINT(16), CHAR( 'DATE', 8) ), ( SMALLINT(17), CHAR(
'TIME', 8) ), ( SMALLINT(18), CHAR( 'TIMESTMP', 8) ), (
SMALLINT(19), CHAR( 'DATALINK', 8) ), ( SMALLINT(20), CHAR( 'STRUCT',
8) ), ( SMALLINT(21), CHAR( 'DISTINCT', 8) ), ( SMALLINT(22), CHAR(
'REF', 8) ) ), SYSIBM.SQLCOLS ( TABLE_CAT, TABLE_SCHEM,
TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE,
BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE, REMARKS,
COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH,
ORDINAL_POSITION, IS_NULLABLE, JDBC_DATA_TYPE, SCOPE_CATLOG,
SCOPE_SCHEMA, SCOPE_TABLE, SOURCE_DATA_TYPE, DBNAME, PSEUDO_COLUMN
) AS ( SELECT CAST( NULL AS VARCHAR(128) ), RTRIM(T.CREATOR), T.NAME,
C.NAME, SMALLINT( CASE WHEN I.TYPEINT=1 THEN 4 WHEN I.TYPEINT=2
THEN 5 WHEN I.TYPEINT=3 THEN -5 WHEN I.TYPEINT=4 THEN 7 WHEN
I.TYPEINT=5 THEN 8 WHEN I.TYPEINT=6 AND C.CODEPAGE <0 then 1 WHEN
I.TYPEINT=6 AND C.CODEPAGE = 0 then -2 WHEN I.TYPEINT=7 AND
C.CODEPAGE <0 THEN 12 WHEN I.TYPEINT=7 AND C.CODEPAGE = 0 THEN -3
WHEN I.TYPEINT=8 AND C.CODEPAGE <0 THEN -1 WHEN I.TYPEINT=8 AND
C.CODEPAGE = 0 THEN -4 WHEN I.TYPEINT=9 THEN 3 WHEN I.TYPEINT=10
THEN -95 WHEN I.TYPEINT=11 THEN -96 WHEN I.TYPEINT=12 THEN -97
WHEN I.TYPEINT=13 THEN -98 WHEN I.TYPEINT=14 THEN -99 WHEN
I.TYPEINT=15 THEN -350 WHEN I.TYPEINT=16 THEN 91 WHEN I.TYPEINT=17
THEN 92 WHEN I.TYPEINT=18 THEN 93 WHEN I.TYPEINT=19 THEN -400
WHEN I.TYPEINT=20 THEN 17 WHEN I.TYPEINT=21 THEN 17 ELSE 0 END),
CAST( CASE WHEN I.TYPEINT=1 THEN 'INTEGER' WHEN I.TYPEINT=2 THEN
'SMALLINT' WHEN I.TYPEINT=3 THEN 'BIGINT' WHEN I.TYPEINT=4 THEN
'REAL' WHEN I.TYPEINT=5 THEN 'DOUBLE' WHEN I.TYPEINT=6 AND
C.CODEPAGE <0 THEN 'CHAR' WHEN I.TYPEINT=6 AND C.CODEPAGE = 0 THEN
'CHAR () FOR BIT DATA' WHEN I.TYPEINT=7 AND C.CODEPAGE <0 THEN
'VARCHAR' WHEN I.TYPEINT=7 AND C.CODEPAGE = 0 THEN 'VARCHAR () FOR
BIT DATA' WHEN I.TYPEINT=8 AND C.CODEPAGE <0 THEN 'LONG VARCHAR'
WHEN I.TYPEINT=8 AND C.CODEPAGE = 0 THEN 'LONG VARCHAR FOR BIT DATA'
WHEN I.TYPEINT=9 THEN 'DECIMAL' WHEN I.TYPEINT=10 THEN 'GRAPHIC'
WHEN I.TYPEINT=11 THEN 'VARGRAPHIC' WHEN I.TYPEINT=12 THEN 'LONG
VARGRAPHIC' WHEN I.TYPEINT=13 THEN 'BLOB' WHEN I.TYPEINT=14 THEN
'CLOB' WHEN I.TYPEINT=15 THEN 'DBCLOB' WHEN I.TYPEINT=16 THEN
'DATE' WHEN I.TYPEINT=17 THEN 'TIME' WHEN I.TYPEINT=18 THEN
'TIMESTAMP' WHEN I.TYPEINT=19 THEN 'DATALINK' WHEN I.TYPEINT=20
THEN '"' || RTRIM(D.SCHEMA) || '"."' || D.NAME || '"' WHEN
I.TYPEINT=21 THEN '"' || RTRIM(D.SCHEMA) || '"."' || D.NAME || '"'
ELSE '' END AS VARCHAR(261) ), CASE WHEN I.TYPEINT=1 THEN 10 WHEN
I.TYPEINT=2 THEN 5 WHEN I.TYPEINT=3 THEN 19 WHEN I.TYPEINT=4 THEN
24 WHEN I.TYPEINT=5 THEN 53 WHEN I.TYPEINT=6 THEN C.LENGTH WHEN
I.TYPEINT=7 THEN C.LENGTH WHEN I.TYPEINT=8 THEN C.LENGTH WHEN
I.TYPEINT=9 THEN C.LENGTH WHEN I.TYPEINT=10 THEN C.LENGTH WHEN
I.TYPEINT=11 THEN C.LENGTH WHEN I.TYPEINT=12 THEN C.LENGTH WHEN
I.TYPEINT=13 THEN C.LONGLENGTH WHEN I.TYPEINT=14 THEN C.LONGLENGTH
WHEN I.TYPEINT=15 THEN C.LONGLENGTH WHEN I.TYPEINT=16 THEN 10 WHEN
I.TYPEINT=17 THEN 8 WHEN I.TYPEINT=18 THEN 26 WHEN I.TYPEINT=20
THEN D.LENGTH WHEN I.TYPEINT=21 THEN D.LENGTH ELSE C.LENGTH END,
CASE WHEN I.TYPEINT=1 THEN 4 WHEN I.TYPEINT=2 THEN 2 WHEN
I.TYPEINT=3 THEN 8 WHEN I.TYPEINT=4 THEN 4 WHEN I.TYPEINT=5 THEN 8
WHEN I.TYPEINT=6 THEN C.LENGTH WHEN I.TYPEINT=7 THEN C.LENGTH WHEN
I.TYPEINT=8 THEN C.LENGTH WHEN I.TYPEINT=9 THEN C.LENGTH + 2 WHEN
I.TYPEINT=10 THEN C.LENGTH * 2 WHEN I.TYPEINT=11 THEN C.LENGTH * 2
WHEN I.TYPEINT=12 THEN C.LENGTH * 2 WHEN I.TYPEINT=13 THEN
C.LONGLENGTH WHEN I.TYPEINT=14 THEN C.LONGLENGTH WHEN I.TYPEINT=15
THEN C.LONGLENGTH * 2 WHEN I.TYPEINT=16 THEN 6 WHEN I.TYPEINT=17
THEN 6 WHEN I.TYPEINT=18 THEN 16 WHEN I.TYPEINT=19 THEN C.LENGTH
WHEN I.TYPEINT=20 AND D.SOURCETYPE NOT IN
('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH WHEN I.TYPEINT=20 AND D.SOURCETYPE IN
('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH * 2 WHEN I.TYPEINT=21 AND D.SOURCETYPE NOT
IN ('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH WHEN I.TYPEINT=21 AND D.SOURCETYPE IN
('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH * 2 ELSE C.LENGTH END, SMALLINT( CASE WHEN I.TYPEINT=1
THEN 0 WHEN I.TYPEINT=2 THEN 0 WHEN I.TYPEINT=3 THEN 0 WHEN
I.TYPEINT=4 THEN NULL WHEN I.TYPEINT=5 THEN NULL WHEN I.TYPEINT=6
THEN NULL WHEN I.TYPEINT=7 THEN NULL WHEN I.TYPEINT=8 THEN NULL
WHEN I.TYPEINT=9 THEN C.SCALE WHEN I.TYPEINT=10 THEN NULL WHEN
I.TYPEINT=11 THEN NULL WHEN I.TYPEINT=12 THEN NULL WHEN
I.TYPEINT=13 THEN NULL WHEN I.TYPEINT=14 THEN NULL WHEN
I.TYPEINT=15 THEN NULL WHEN I.TYPEINT=16 THEN NULL WHEN
I.TYPEINT=17 THEN 0 WHEN I.TYPEINT=18 THEN 6 WHEN I.TYPEINT=19 THEN
NULL WHEN I.TYPEINT=20 AND D.SOURCETYPE='DECIMAL' THEN
D.SCALE WHEN I.TYPEINT=20 AND D.SOURCETYPE IN
('INTEGER','SMALLINT','BIGINT','TIME') THEN 0 WHEN
I.TYPEINT=20 AND D.SOURCETYPE='TIMESTAMP' THEN 6 WHEN
I.TYPEINT=21 AND D.SOURCETYPE='DECIMAL' THEN D.SCALE
WHEN I.TYPEINT=21 AND D.SOURCETYPE IN
('INTEGER','SMALLINT','BIGINT','TIME') THEN 0 WHEN
I.TYPEINT=21 AND D.SOURCETYPE='TIMESTAMP' THEN 6 ELSE
NULL END ), SMALLINT(CASE WHEN I.TYPEINT=1 THEN 10 WHEN
I.TYPEINT=2 THEN 10 WHEN I.TYPEINT=3 THEN 10 WHEN I.TYPEINT=4 THEN
2 WHEN I.TYPEINT=5 THEN 2 WHEN I.TYPEINT=6 THEN NULL WHEN
I.TYPEINT=7 THEN NULL WHEN I.TYPEINT=8 THEN NULL WHEN I.TYPEINT=9
THEN 10 WHEN I.TYPEINT=10 THEN NULL WHEN I.TYPEINT=11 THEN NULL
WHEN I.TYPEINT=12 THEN NULL WHEN I.TYPEINT=13 THEN NULL WHEN
I.TYPEINT=14 THEN NULL WHEN I.TYPEINT=15 THEN NULL WHEN
I.TYPEINT=16 THEN NULL WHEN I.TYPEINT=17 THEN NULL WHEN
I.TYPEINT=18 THEN NULL WHEN I.TYPEINT=19 THEN NULL WHEN
I.TYPEINT=20 AND D.SOURCETYPE IN
('DECIMAL','INTEGER','SMALLINT','BIGINT') THEN 10 WHEN
I.TYPEINT=20 AND D.SOURCETYPE IN ('REAL','FLOAT','DOUBLE')
THEN 2 WHEN I.TYPEINT=21 AND D.SOURCETYPE IN
('DECIMAL','INTEGER','SMALLINT','BIGINT') THEN 10 WHEN
I.TYPEINT=21 AND D.SOURCETYPE IN ('REAL','FLOAT','DOUBLE')
THEN 2 ELSE NULL END), SMALLINT(CASE WHEN C.NULLS='Y' THEN 1
ELSE 0 END), C.REMARKS, C.DEFAULT, SMALLINT(CASE WHEN I.TYPEINT=1
THEN 4 WHEN I.TYPEINT=2 THEN 5 WHEN I.TYPEINT=3 THEN -5 WHEN
I.TYPEINT=4 THEN 7 WHEN I.TYPEINT=5 THEN 8 WHEN I.TYPEINT=6 AND
C.CODEPAGE <0 then 1 WHEN I.TYPEINT=6 AND C.CODEPAGE = 0 then -2
WHEN I.TYPEINT=7 AND C.CODEPAGE <0 THEN 12 WHEN I.TYPEINT=7 AND
C.CODEPAGE = 0 THEN -3 WHEN I.TYPEINT=8 AND C.CODEPAGE <0 THEN -1
WHEN I.TYPEINT=8 AND C.CODEPAGE = 0 THEN -4 WHEN I.TYPEINT=9 THEN 3
WHEN I.TYPEINT=10 THEN -95 WHEN I.TYPEINT=11 THEN -96 WHEN
I.TYPEINT=12 THEN -97 WHEN I.TYPEINT=13 THEN -98 WHEN I.TYPEINT=14
THEN -99 WHEN I.TYPEINT=15 THEN -350 WHEN I.TYPEINT=16 THEN 9
WHEN I.TYPEINT=17 THEN 9 WHEN I.TYPEINT=18 THEN 9 WHEN I.TYPEINT=19
THEN -400 WHEN I.TYPEINT=20 THEN 17 WHEN I.TYPEINT=21 THEN 17
ELSE 0 END), SMALLINT(CASE WHEN I.TYPEINT=1 THEN NULL WHEN
I.TYPEINT=2 THEN NULL WHEN I.TYPEINT=3 THEN NULL WHEN I.TYPEINT=4
THEN NULL WHEN I.TYPEINT=5 THEN NULL WHEN I.TYPEINT=6 THEN NULL
WHEN I.TYPEINT=7 THEN NULL WHEN I.TYPEINT=8 THEN NULL WHEN
I.TYPEINT=9 THEN NULL WHEN I.TYPEINT=10 THEN NULL WHEN I.TYPEINT=11
THEN NULL WHEN I.TYPEINT=12 THEN NULL WHEN I.TYPEINT=13 THEN NULL
WHEN I.TYPEINT=14 THEN NULL WHEN I.TYPEINT=15 THEN NULL WHEN
I.TYPEINT=16 THEN 1 WHEN I.TYPEINT=17 THEN 2 WHEN I.TYPEINT=18 THEN
3 WHEN I.TYPEINT=19 THEN NULL WHEN I.TYPEINT=20 AND
D.SOURCETYPE='DATE' THEN 1 WHEN I.TYPEINT=20 AND
D.SOURCETYPE='TIME' THEN 2 WHEN I.TYPEINT=20 AND
D.SOURCETYPE='TIMESTAMP' THEN 3 WHEN I.TYPEINT=21 AND
D.SOURCETYPE='DATE' THEN 1 WHEN I.TYPEINT=21 AND
D.SOURCETYPE='TIME' THEN 2 WHEN I.TYPEINT=21 AND
D.SOURCETYPE='TIMESTAMP' THEN 3 ELSE NULL END), CASE WHEN
I.TYPEINT=1 THEN NULL WHEN I.TYPEINT=2 THEN NULL WHEN I.TYPEINT=3 THEN
NULL WHEN I.TYPEINT=4 THEN NULL WHEN I.TYPEINT=5 THEN NULL WHEN
I.TYPEINT=6 THEN C.LENGTH WHEN I.TYPEINT=7 THEN C.LENGTH WHEN
I.TYPEINT=8 THEN C.LENGTH WHEN I.TYPEINT=9 THEN NULL WHEN I.TYPEINT=10
THEN C.LENGTH * 2 WHEN I.TYPEINT=11 THEN C.LENGTH * 2 WHEN I.TYPEINT=12
THEN C.LENGTH * 2 WHEN I.TYPEINT=13 THEN C.LONGLENGTH WHEN I.TYPEINT=14
THEN C.LONGLENGTH WHEN I.TYPEINT=15 THEN C.LONGLENGTH * 2 WHEN
I.TYPEINT=16 THEN NULL WHEN I.TYPEINT=17 THEN NULL WHEN I.TYPEINT=18
THEN NULL WHEN I.TYPEINT=19 THEN NULL WHEN I.TYPEINT=20 AND
D.SOURCETYPE IN ('CHARACTER','VARCHAR','LONG
VARCHAR','BLOB','CLOB') THEN D.LENGTH WHEN I.TYPEINT=20 AND
D.SOURCETYPE IN ('GRAPHIC','VARGRAPHIC','LONG
VARGRAPHIC','DBCLOB') THEN D.LENGTH * 2 WHEN I.TYPEINT=21 AND
D.SOURCETYPE IN ('CHARACTER','VARCHAR','LONG
VARCHAR','BLOB','CLOB') THEN D.LENGTH WHEN I.TYPEINT=21
AND D.SOURCETYPE IN ('GRAPHIC','VARGRAPHIC','LONG
VARGRAPHIC','DBCLOB') THEN D.LENGTH * 2 ELSE NULL END, C.COLNO
+ 1, CASE WHEN C.NULLS='Y' THEN 'YES' ELSE 'NO' END, SMALLINT( CASE
WHEN I.TYPEINT=1 THEN 4 WHEN I.TYPEINT=2 THEN 5 WHEN I.TYPEINT=3
THEN -5 WHEN I.TYPEINT=4 THEN 7 WHEN I.TYPEINT=5 THEN 8 WHEN
I.TYPEINT=6 AND C.CODEPAGE <0 then 1 WHEN I.TYPEINT=6 AND
C.CODEPAGE = 0 then -2 WHEN I.TYPEINT=7 AND C.CODEPAGE <0 THEN 12
WHEN I.TYPEINT=7 AND C.CODEPAGE = 0 THEN -3 WHEN I.TYPEINT=8 AND
C.CODEPAGE <0 THEN -1 WHEN I.TYPEINT=8 AND C.CODEPAGE = 0 THEN -4
WHEN I.TYPEINT=9 THEN 3 WHEN I.TYPEINT=10 THEN 1 WHEN I.TYPEINT=11
THEN 12 WHEN I.TYPEINT=12 THEN -1 WHEN I.TYPEINT=13 THEN 2004
WHEN I.TYPEINT=14 THEN 2005 WHEN I.TYPEINT=15 THEN 2005 WHEN
I.TYPEINT=16 THEN 91 WHEN I.TYPEINT=17 THEN 92 WHEN I.TYPEINT=18
THEN 93 WHEN I.TYPEINT=19 THEN 70 WHEN I.TYPEINT=20 THEN 2002
WHEN I.TYPEINT=21 THEN 2001 ELSE 0 END), CAST( NULL AS VARCHAR(128)
), CAST( NULL AS VARCHAR(128) ), CAST( NULL AS VARCHAR(128) ),
SMALLINT( CASE WHEN D.SOURCETYPE IS NULL THEN NULL WHEN
D.SOURCETYPE='INTEGER' THEN 4 WHEN D.SOURCETYPE='SMALLINT' THEN 5
WHEN D.SOURCETYPE='BIGINT' THEN -5 WHEN D.SOURCETYPE='FLOAT' AND
C.LENGTH=4 THEN 7 WHEN D.SOURCETYPE='FLOAT' AND C.LENGTH=8 THEN 8
WHEN D.SOURCETYPE='REAL' THEN 7 WHEN D.SOURCETYPE='DOUBLE' THEN 8
WHEN D.SOURCETYPE='CHARACTER' AND C.CODEPAGE <0 then 1 WHEN
D.SOURCETYPE='CHARACTER' AND C.CODEPAGE = 0 then -2 WHEN
D.SOURCETYPE='VARCHAR' AND C.CODEPAGE <0 THEN 12 WHEN
D.SOURCETYPE='VARCHAR' AND C.CODEPAGE = 0 THEN -3 WHEN
D.SOURCETYPE='LONG VARCHAR' AND C.CODEPAGE <0 THEN -1 WHEN
D.SOURCETYPE='LONG VARCHAR' AND C.CODEPAGE = 0 THEN -4 WHEN
D.SOURCETYPE='DECIMAL' THEN 3 WHEN D.SOURCETYPE='GRAPHIC' THEN -95
WHEN D.SOURCETYPE='VARGRAPHIC' THEN -96 WHEN D.SOURCETYPE='LONG
VARGRAPHIC' THEN -97 WHEN D.SOURCETYPE='BLOB' THEN -98 WHEN
D.SOURCETYPE='CLOB' THEN -99 WHEN D.SOURCETYPE='DBCLOB' THEN -350
WHEN D.SOURCETYPE='DATE' THEN 91 WHEN D.SOURCETYPE='TIME' THEN 92
WHEN D.SOURCETYPE='TIMESTAMP' THEN 93 WHEN D.SOURCETYPE='DATALINK'
THEN -400 ELSE 0 END), CAST( NULL AS VARCHAR(8) ), SMALLINT(CASE
WHEN C.IDENTITY = 'Y' THEN 2 ELSE 1 END ) FROM SYSIBM.SYSCOLUMNS
C, SYSIBM.SYSDATATYPES D, SYSIBM.SYSTABLES T, TYPEINTS I WHERE
T.NAME LIKE 'VT_CO_M_SUBJECT' ESCAPE '\' AND ( (C.TBCREATOR =
T.CREATOR AND C.TBNAME = T.NAME ) OR (C.TBCREATOR =
T.BASE_SCHEMA AND C.TBNAME = T.BASE_NAME ) ) AND C.COLTYPE <'REF'
AND I.TYPEINT <22 AND C.TYPENAME = D.NAME AND C.TYPESCHEMA =
D.SCHEMA AND C.COLTYPE = I.COLTYPE UNION ALL SELECT CAST( NULL AS
VARCHAR(128) ), RTRIM(T.CREATOR), T.NAME, C.NAME, SMALLINT(20),
CAST( '"' || RTRIM(D.SCHEMA) || '"."' || D.NAME || '"' AS
VARCHAR(261) ), CASE WHEN D.SOURCETYPE = 'REAL' THEN 24 WHEN
D.SOURCETYPE = 'DOUBLE' THEN 53 ELSE D.LENGTH END, CASE
WHEN D.SOURCETYPE NOT IN ('GRAPHIC','VARGRAPHIC','LONG
VARGRAPHIC','DBCLOB') THEN D.LENGTH WHEN D.SOURCETYPE
IN ('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB')
THEN D.LENGTH * 2 ELSE NULL END, SMALLINT( CASE WHEN
D.SOURCETYPE='DECIMAL' THEN D.SCALE WHEN D.SOURCETYPE IN
('INTEGER','SMALLINT','BIGINT','TIME') THEN 0 WHEN
D.SOURCETYPE='TIMESTAMP' THEN 6 ELSE NULL END ),
SMALLINT( CASE WHEN D.SOURCETYPE IN
('DECIMAL','INTEGER','SMALLINT','BIGINT') THEN 10 WHEN
D.SOURCETYPE IN ('REAL','FLOAT','DOUBLE') THEN 2 ELSE NULL
END), SMALLINT(CASE WHEN C.NULLS='Y' THEN 1 ELSE 0 END),
C.REMARKS, C.DEFAULT, SMALLINT( 20 ), SMALLINT( CASE WHEN
D.SOURCETYPE='DATE' THEN 1 WHEN D.SOURCETYPE='TIME' THEN 2 WHEN
D.SOURCETYPE='TIMESTAMP' THEN 3 ELSE NULL END), CASE WHEN
D.SOURCETYPE IN ('CHARACTER','VARCHAR','LONG
VARCHAR','BLOB','CLOB') THEN D.LENGTH WHEN D.SOURCETYPE IN
('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH * 2 ELSE NULL END, C.COLNO + 1, CASE WHEN C.NULLS='Y' THEN
'YES' ELSE 'NO' END, SMALLINT( 2006 ), CAST( NULL AS VARCHAR(128) ),
CAST( NULL AS VARCHAR(128) ), CAST( NULL AS VARCHAR(128) ), SMALLINT(
CASE WHEN D.SOURCETYPE IS NULL THEN NULL WHEN
D.SOURCETYPE='INTEGER' THEN 4 WHEN D.SOURCETYPE='SMALLINT' THEN 5
WHEN D.SOURCETYPE='BIGINT' THEN -5 WHEN D.SOURCETYPE='FLOAT' AND
C.LENGTH=4 THEN 7 WHEN D.SOURCETYPE='FLOAT' AND C.LENGTH=8 THEN 8
WHEN D.SOURCETYPE='REAL' THEN 7 WHEN D.SOURCETYPE='DOUBLE' THEN 8
WHEN D.SOURCETYPE='CHARACTER' AND C.CODEPAGE <0 then 1 WHEN
D.SOURCETYPE='CHARACTER' AND C.CODEPAGE = 0 then -2 WHEN
D.SOURCETYPE='VARCHAR' AND C.CODEPAGE <0 THEN 12 WHEN
D.SOURCETYPE='VARCHAR' AND C.CODEPAGE = 0 THEN -3 WHEN
D.SOURCETYPE='LONG VARCHAR' AND C.CODEPAGE <0 THEN -1 WHEN
D.SOURCETYPE='LONG VARCHAR' AND C.CODEPAGE = 0 THEN -4 WHEN
D.SOURCETYPE='DECIMAL' THEN 3 WHEN D.SOURCETYPE='GRAPHIC' THEN -95
WHEN D.SOURCETYPE='VARGRAPHIC' THEN -96 WHEN D.SOURCETYPE='LONG
VARGRAPHIC' THEN -97 WHEN D.SOURCETYPE='BLOB' THEN -98 WHEN
D.SOURCETYPE='CLOB' THEN -99 WHEN D.SOURCETYPE='DBCLOB' THEN -350
WHEN D.SOURCETYPE='DATE' THEN 91 WHEN D.SOURCETYPE='TIME' THEN 92
WHEN D.SOURCETYPE='TIMESTAMP' THEN 93 WHEN D.SOURCETYPE='DATALINK'
THEN -400 ELSE 0 END ), CAST( NULL AS VARCHAR(8) ), SMALLINT(1) FROM
SYSIBM.SYSCOLUMNS C, SYSIBM.SYSDATATYPES D,
SYSIBM.SYSCOLPROPERTIES P, SYSIBM.SYSTABLES T, TYPEINTS I WHERE
T.NAME LIKE 'VT_CO_M_SUBJECT' ESCAPE '\' AND ( (C.TBCREATOR =
T.CREATOR AND C.TBNAME = T.NAME ) OR (C.TBCREATOR = T.BASE_SCHEMA
AND C.TBNAME = T.BASE_NAME ) ) AND C.COLTYPE = 'REF' AND I.TYPEINT
= 22 AND P.COLNAME = C.NAME AND P.TABSCHEMA = C.TBCREATOR AND
P.TABNAME = C.TBNAME AND P.TARGET_TYPENAME = D.NAME AND
P.TARGET_TYPESCHEMA = D.SCHEMA AND C.COLTYPE = I.COLTYPE ) SELECT
TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, SMALLINT( CASE
DATA_TYPE WHEN 70 THEN -400 WHEN 91 THEN 9 WHEN 92 THEN 10 WHEN
93 THEN 11 WHEN -95 then -8 WHEN -96 then -9 WHEN -97 then -10
ELSE DATA_TYPE END) as DATA_TYPE, TYPE_NAME, COLUMN_SIZE,
BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE, REMARKS,
COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH,
ORDINAL_POSITION, IS_NULLABLE FROM SYSIBM.SQLCOLS WHERE TABLE_NAME
LIKE 'VT_CO_M_SUBJECT' ESCAPE '\' ORDER BY 1,2,3,17
my advice is to run statitics and reorganise the indexes on the table
involved.

since these tables are system table situated in catalog tablespace
running db2empfa tool would be helpful.
(never tried and i'm not sure is there anything to do with performance
by using this tool)

Sep 27 '06 #2

P: n/a

situ wrote:
bb********@gmail.com wrote:
In a DB2 server setup on Sun, the clients are using Developer 2000 as
frontend on the ODBC connection.

A particular report is taking too long to execute. The snapshot shows
the following. The query seems to be generated by DB2, as none of the
reports have this particular query. Is there any way by which I can
speeden up / avoid the statement? Am I missing something here? BTW, the
DB2 level V8FP4.

Thanks in advance.

Number of executions = 1465
Number of compilations = 0
Worst preparation time (ms) = 138
Best preparation time (ms) = 138
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 0
Internal rows updated = 0
Rows written = 0
Statement sorts = 20
Buffer pool data logical reads = 0
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 0
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 75.858788
Total user cpu time (sec.ms) = 0.050000
Total system cpu time (sec.ms) = 0.040000
Statement text = WITH TYPEINTS ( TYPEINT, COLTYPE
) AS ( VALUES ( SMALLINT(1 ), CHAR( 'INTEGER', 8) ), ( SMALLINT(2
), CHAR( 'SMALLINT', 8) ), ( SMALLINT(3 ), CHAR( 'BIGINT', 8) ), (
SMALLINT(4 ), CHAR( 'REAL', 8) ), ( SMALLINT(5 ), CHAR( 'DOUBLE',
8) ), ( SMALLINT(6 ), CHAR( 'CHAR', 8) ), ( SMALLINT(7 ), CHAR(
'VARCHAR', 8) ), ( SMALLINT(8 ), CHAR( 'LONGVAR', 8) ), (
SMALLINT(9 ), CHAR( 'DECIMAL', 8) ), ( SMALLINT(10), CHAR( 'GRAPHIC',
8) ), ( SMALLINT(11), CHAR( 'VARGRAPH', 8) ), ( SMALLINT(12), CHAR(
'LONGVARG', 8) ), ( SMALLINT(13), CHAR( 'BLOB', 8) ), (
SMALLINT(14), CHAR( 'CLOB', 8) ), ( SMALLINT(15), CHAR( 'DBCLOB',
8) ), ( SMALLINT(16), CHAR( 'DATE', 8) ), ( SMALLINT(17), CHAR(
'TIME', 8) ), ( SMALLINT(18), CHAR( 'TIMESTMP', 8) ), (
SMALLINT(19), CHAR( 'DATALINK', 8) ), ( SMALLINT(20), CHAR( 'STRUCT',
8) ), ( SMALLINT(21), CHAR( 'DISTINCT', 8) ), ( SMALLINT(22), CHAR(
'REF', 8) ) ), SYSIBM.SQLCOLS ( TABLE_CAT, TABLE_SCHEM,
TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE,
BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE, REMARKS,
COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH,
ORDINAL_POSITION, IS_NULLABLE, JDBC_DATA_TYPE, SCOPE_CATLOG,
SCOPE_SCHEMA, SCOPE_TABLE, SOURCE_DATA_TYPE, DBNAME, PSEUDO_COLUMN
) AS ( SELECT CAST( NULL AS VARCHAR(128) ), RTRIM(T.CREATOR), T.NAME,
C.NAME, SMALLINT( CASE WHEN I.TYPEINT=1 THEN 4 WHEN I.TYPEINT=2
THEN 5 WHEN I.TYPEINT=3 THEN -5 WHEN I.TYPEINT=4 THEN 7 WHEN
I.TYPEINT=5 THEN 8 WHEN I.TYPEINT=6 AND C.CODEPAGE <0 then 1 WHEN
I.TYPEINT=6 AND C.CODEPAGE = 0 then -2 WHEN I.TYPEINT=7 AND
C.CODEPAGE <0 THEN 12 WHEN I.TYPEINT=7 AND C.CODEPAGE = 0 THEN -3
WHEN I.TYPEINT=8 AND C.CODEPAGE <0 THEN -1 WHEN I.TYPEINT=8 AND
C.CODEPAGE = 0 THEN -4 WHEN I.TYPEINT=9 THEN 3 WHEN I.TYPEINT=10
THEN -95 WHEN I.TYPEINT=11 THEN -96 WHEN I.TYPEINT=12 THEN -97
WHEN I.TYPEINT=13 THEN -98 WHEN I.TYPEINT=14 THEN -99 WHEN
I.TYPEINT=15 THEN -350 WHEN I.TYPEINT=16 THEN 91 WHEN I.TYPEINT=17
THEN 92 WHEN I.TYPEINT=18 THEN 93 WHEN I.TYPEINT=19 THEN -400
WHEN I.TYPEINT=20 THEN 17 WHEN I.TYPEINT=21 THEN 17 ELSE 0 END),
CAST( CASE WHEN I.TYPEINT=1 THEN 'INTEGER' WHEN I.TYPEINT=2 THEN
'SMALLINT' WHEN I.TYPEINT=3 THEN 'BIGINT' WHEN I.TYPEINT=4 THEN
'REAL' WHEN I.TYPEINT=5 THEN 'DOUBLE' WHEN I.TYPEINT=6 AND
C.CODEPAGE <0 THEN 'CHAR' WHEN I.TYPEINT=6 AND C.CODEPAGE = 0 THEN
'CHAR () FOR BIT DATA' WHEN I.TYPEINT=7 AND C.CODEPAGE <0 THEN
'VARCHAR' WHEN I.TYPEINT=7 AND C.CODEPAGE = 0 THEN 'VARCHAR () FOR
BIT DATA' WHEN I.TYPEINT=8 AND C.CODEPAGE <0 THEN 'LONG VARCHAR'
WHEN I.TYPEINT=8 AND C.CODEPAGE = 0 THEN 'LONG VARCHAR FOR BIT DATA'
WHEN I.TYPEINT=9 THEN 'DECIMAL' WHEN I.TYPEINT=10 THEN 'GRAPHIC'
WHEN I.TYPEINT=11 THEN 'VARGRAPHIC' WHEN I.TYPEINT=12 THEN 'LONG
VARGRAPHIC' WHEN I.TYPEINT=13 THEN 'BLOB' WHEN I.TYPEINT=14 THEN
'CLOB' WHEN I.TYPEINT=15 THEN 'DBCLOB' WHEN I.TYPEINT=16 THEN
'DATE' WHEN I.TYPEINT=17 THEN 'TIME' WHEN I.TYPEINT=18 THEN
'TIMESTAMP' WHEN I.TYPEINT=19 THEN 'DATALINK' WHEN I.TYPEINT=20
THEN '"' || RTRIM(D.SCHEMA) || '"."' || D.NAME || '"' WHEN
I.TYPEINT=21 THEN '"' || RTRIM(D.SCHEMA) || '"."' || D.NAME || '"'
ELSE '' END AS VARCHAR(261) ), CASE WHEN I.TYPEINT=1 THEN 10 WHEN
I.TYPEINT=2 THEN 5 WHEN I.TYPEINT=3 THEN 19 WHEN I.TYPEINT=4 THEN
24 WHEN I.TYPEINT=5 THEN 53 WHEN I.TYPEINT=6 THEN C.LENGTH WHEN
I.TYPEINT=7 THEN C.LENGTH WHEN I.TYPEINT=8 THEN C.LENGTH WHEN
I.TYPEINT=9 THEN C.LENGTH WHEN I.TYPEINT=10 THEN C.LENGTH WHEN
I.TYPEINT=11 THEN C.LENGTH WHEN I.TYPEINT=12 THEN C.LENGTH WHEN
I.TYPEINT=13 THEN C.LONGLENGTH WHEN I.TYPEINT=14 THEN C.LONGLENGTH
WHEN I.TYPEINT=15 THEN C.LONGLENGTH WHEN I.TYPEINT=16 THEN 10 WHEN
I.TYPEINT=17 THEN 8 WHEN I.TYPEINT=18 THEN 26 WHEN I.TYPEINT=20
THEN D.LENGTH WHEN I.TYPEINT=21 THEN D.LENGTH ELSE C.LENGTH END,
CASE WHEN I.TYPEINT=1 THEN 4 WHEN I.TYPEINT=2 THEN 2 WHEN
I.TYPEINT=3 THEN 8 WHEN I.TYPEINT=4 THEN 4 WHEN I.TYPEINT=5 THEN 8
WHEN I.TYPEINT=6 THEN C.LENGTH WHEN I.TYPEINT=7 THEN C.LENGTH WHEN
I.TYPEINT=8 THEN C.LENGTH WHEN I.TYPEINT=9 THEN C.LENGTH + 2 WHEN
I.TYPEINT=10 THEN C.LENGTH * 2 WHEN I.TYPEINT=11 THEN C.LENGTH * 2
WHEN I.TYPEINT=12 THEN C.LENGTH * 2 WHEN I.TYPEINT=13 THEN
C.LONGLENGTH WHEN I.TYPEINT=14 THEN C.LONGLENGTH WHEN I.TYPEINT=15
THEN C.LONGLENGTH * 2 WHEN I.TYPEINT=16 THEN 6 WHEN I.TYPEINT=17
THEN 6 WHEN I.TYPEINT=18 THEN 16 WHEN I.TYPEINT=19 THEN C.LENGTH
WHEN I.TYPEINT=20 AND D.SOURCETYPE NOT IN
('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH WHEN I.TYPEINT=20 AND D.SOURCETYPE IN
('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH * 2 WHEN I.TYPEINT=21 AND D.SOURCETYPE NOT
IN ('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH WHEN I.TYPEINT=21 AND D.SOURCETYPE IN
('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH * 2 ELSE C.LENGTH END, SMALLINT( CASE WHEN I.TYPEINT=1
THEN 0 WHEN I.TYPEINT=2 THEN 0 WHEN I.TYPEINT=3 THEN 0 WHEN
I.TYPEINT=4 THEN NULL WHEN I.TYPEINT=5 THEN NULL WHEN I.TYPEINT=6
THEN NULL WHEN I.TYPEINT=7 THEN NULL WHEN I.TYPEINT=8 THEN NULL
WHEN I.TYPEINT=9 THEN C.SCALE WHEN I.TYPEINT=10 THEN NULL WHEN
I.TYPEINT=11 THEN NULL WHEN I.TYPEINT=12 THEN NULL WHEN
I.TYPEINT=13 THEN NULL WHEN I.TYPEINT=14 THEN NULL WHEN
I.TYPEINT=15 THEN NULL WHEN I.TYPEINT=16 THEN NULL WHEN
I.TYPEINT=17 THEN 0 WHEN I.TYPEINT=18 THEN 6 WHEN I.TYPEINT=19 THEN
NULL WHEN I.TYPEINT=20 AND D.SOURCETYPE='DECIMAL' THEN
D.SCALE WHEN I.TYPEINT=20 AND D.SOURCETYPE IN
('INTEGER','SMALLINT','BIGINT','TIME') THEN 0 WHEN
I.TYPEINT=20 AND D.SOURCETYPE='TIMESTAMP' THEN 6 WHEN
I.TYPEINT=21 AND D.SOURCETYPE='DECIMAL' THEN D.SCALE
WHEN I.TYPEINT=21 AND D.SOURCETYPE IN
('INTEGER','SMALLINT','BIGINT','TIME') THEN 0 WHEN
I.TYPEINT=21 AND D.SOURCETYPE='TIMESTAMP' THEN 6 ELSE
NULL END ), SMALLINT(CASE WHEN I.TYPEINT=1 THEN 10 WHEN
I.TYPEINT=2 THEN 10 WHEN I.TYPEINT=3 THEN 10 WHEN I.TYPEINT=4 THEN
2 WHEN I.TYPEINT=5 THEN 2 WHEN I.TYPEINT=6 THEN NULL WHEN
I.TYPEINT=7 THEN NULL WHEN I.TYPEINT=8 THEN NULL WHEN I.TYPEINT=9
THEN 10 WHEN I.TYPEINT=10 THEN NULL WHEN I.TYPEINT=11 THEN NULL
WHEN I.TYPEINT=12 THEN NULL WHEN I.TYPEINT=13 THEN NULL WHEN
I.TYPEINT=14 THEN NULL WHEN I.TYPEINT=15 THEN NULL WHEN
I.TYPEINT=16 THEN NULL WHEN I.TYPEINT=17 THEN NULL WHEN
I.TYPEINT=18 THEN NULL WHEN I.TYPEINT=19 THEN NULL WHEN
I.TYPEINT=20 AND D.SOURCETYPE IN
('DECIMAL','INTEGER','SMALLINT','BIGINT') THEN 10 WHEN
I.TYPEINT=20 AND D.SOURCETYPE IN ('REAL','FLOAT','DOUBLE')
THEN 2 WHEN I.TYPEINT=21 AND D.SOURCETYPE IN
('DECIMAL','INTEGER','SMALLINT','BIGINT') THEN 10 WHEN
I.TYPEINT=21 AND D.SOURCETYPE IN ('REAL','FLOAT','DOUBLE')
THEN 2 ELSE NULL END), SMALLINT(CASE WHEN C.NULLS='Y' THEN 1
ELSE 0 END), C.REMARKS, C.DEFAULT, SMALLINT(CASE WHEN I.TYPEINT=1
THEN 4 WHEN I.TYPEINT=2 THEN 5 WHEN I.TYPEINT=3 THEN -5 WHEN
I.TYPEINT=4 THEN 7 WHEN I.TYPEINT=5 THEN 8 WHEN I.TYPEINT=6 AND
C.CODEPAGE <0 then 1 WHEN I.TYPEINT=6 AND C.CODEPAGE = 0 then -2
WHEN I.TYPEINT=7 AND C.CODEPAGE <0 THEN 12 WHEN I.TYPEINT=7 AND
C.CODEPAGE = 0 THEN -3 WHEN I.TYPEINT=8 AND C.CODEPAGE <0 THEN -1
WHEN I.TYPEINT=8 AND C.CODEPAGE = 0 THEN -4 WHEN I.TYPEINT=9 THEN 3
WHEN I.TYPEINT=10 THEN -95 WHEN I.TYPEINT=11 THEN -96 WHEN
I.TYPEINT=12 THEN -97 WHEN I.TYPEINT=13 THEN -98 WHEN I.TYPEINT=14
THEN -99 WHEN I.TYPEINT=15 THEN -350 WHEN I.TYPEINT=16 THEN 9
WHEN I.TYPEINT=17 THEN 9 WHEN I.TYPEINT=18 THEN 9 WHEN I.TYPEINT=19
THEN -400 WHEN I.TYPEINT=20 THEN 17 WHEN I.TYPEINT=21 THEN 17
ELSE 0 END), SMALLINT(CASE WHEN I.TYPEINT=1 THEN NULL WHEN
I.TYPEINT=2 THEN NULL WHEN I.TYPEINT=3 THEN NULL WHEN I.TYPEINT=4
THEN NULL WHEN I.TYPEINT=5 THEN NULL WHEN I.TYPEINT=6 THEN NULL
WHEN I.TYPEINT=7 THEN NULL WHEN I.TYPEINT=8 THEN NULL WHEN
I.TYPEINT=9 THEN NULL WHEN I.TYPEINT=10 THEN NULL WHEN I.TYPEINT=11
THEN NULL WHEN I.TYPEINT=12 THEN NULL WHEN I.TYPEINT=13 THEN NULL
WHEN I.TYPEINT=14 THEN NULL WHEN I.TYPEINT=15 THEN NULL WHEN
I.TYPEINT=16 THEN 1 WHEN I.TYPEINT=17 THEN 2 WHEN I.TYPEINT=18 THEN
3 WHEN I.TYPEINT=19 THEN NULL WHEN I.TYPEINT=20 AND
D.SOURCETYPE='DATE' THEN 1 WHEN I.TYPEINT=20 AND
D.SOURCETYPE='TIME' THEN 2 WHEN I.TYPEINT=20 AND
D.SOURCETYPE='TIMESTAMP' THEN 3 WHEN I.TYPEINT=21 AND
D.SOURCETYPE='DATE' THEN 1 WHEN I.TYPEINT=21 AND
D.SOURCETYPE='TIME' THEN 2 WHEN I.TYPEINT=21 AND
D.SOURCETYPE='TIMESTAMP' THEN 3 ELSE NULL END), CASE WHEN
I.TYPEINT=1 THEN NULL WHEN I.TYPEINT=2 THEN NULL WHEN I.TYPEINT=3 THEN
NULL WHEN I.TYPEINT=4 THEN NULL WHEN I.TYPEINT=5 THEN NULL WHEN
I.TYPEINT=6 THEN C.LENGTH WHEN I.TYPEINT=7 THEN C.LENGTH WHEN
I.TYPEINT=8 THEN C.LENGTH WHEN I.TYPEINT=9 THEN NULL WHEN I.TYPEINT=10
THEN C.LENGTH * 2 WHEN I.TYPEINT=11 THEN C.LENGTH * 2 WHEN I.TYPEINT=12
THEN C.LENGTH * 2 WHEN I.TYPEINT=13 THEN C.LONGLENGTH WHEN I.TYPEINT=14
THEN C.LONGLENGTH WHEN I.TYPEINT=15 THEN C.LONGLENGTH * 2 WHEN
I.TYPEINT=16 THEN NULL WHEN I.TYPEINT=17 THEN NULL WHEN I.TYPEINT=18
THEN NULL WHEN I.TYPEINT=19 THEN NULL WHEN I.TYPEINT=20 AND
D.SOURCETYPE IN ('CHARACTER','VARCHAR','LONG
VARCHAR','BLOB','CLOB') THEN D.LENGTH WHEN I.TYPEINT=20 AND
D.SOURCETYPE IN ('GRAPHIC','VARGRAPHIC','LONG
VARGRAPHIC','DBCLOB') THEN D.LENGTH * 2 WHEN I.TYPEINT=21 AND
D.SOURCETYPE IN ('CHARACTER','VARCHAR','LONG
VARCHAR','BLOB','CLOB') THEN D.LENGTH WHEN I.TYPEINT=21
AND D.SOURCETYPE IN ('GRAPHIC','VARGRAPHIC','LONG
VARGRAPHIC','DBCLOB') THEN D.LENGTH * 2 ELSE NULL END, C.COLNO
+ 1, CASE WHEN C.NULLS='Y' THEN 'YES' ELSE 'NO' END, SMALLINT( CASE
WHEN I.TYPEINT=1 THEN 4 WHEN I.TYPEINT=2 THEN 5 WHEN I.TYPEINT=3
THEN -5 WHEN I.TYPEINT=4 THEN 7 WHEN I.TYPEINT=5 THEN 8 WHEN
I.TYPEINT=6 AND C.CODEPAGE <0 then 1 WHEN I.TYPEINT=6 AND
C.CODEPAGE = 0 then -2 WHEN I.TYPEINT=7 AND C.CODEPAGE <0 THEN 12
WHEN I.TYPEINT=7 AND C.CODEPAGE = 0 THEN -3 WHEN I.TYPEINT=8 AND
C.CODEPAGE <0 THEN -1 WHEN I.TYPEINT=8 AND C.CODEPAGE = 0 THEN -4
WHEN I.TYPEINT=9 THEN 3 WHEN I.TYPEINT=10 THEN 1 WHEN I.TYPEINT=11
THEN 12 WHEN I.TYPEINT=12 THEN -1 WHEN I.TYPEINT=13 THEN 2004
WHEN I.TYPEINT=14 THEN 2005 WHEN I.TYPEINT=15 THEN 2005 WHEN
I.TYPEINT=16 THEN 91 WHEN I.TYPEINT=17 THEN 92 WHEN I.TYPEINT=18
THEN 93 WHEN I.TYPEINT=19 THEN 70 WHEN I.TYPEINT=20 THEN 2002
WHEN I.TYPEINT=21 THEN 2001 ELSE 0 END), CAST( NULL AS VARCHAR(128)
), CAST( NULL AS VARCHAR(128) ), CAST( NULL AS VARCHAR(128) ),
SMALLINT( CASE WHEN D.SOURCETYPE IS NULL THEN NULL WHEN
D.SOURCETYPE='INTEGER' THEN 4 WHEN D.SOURCETYPE='SMALLINT' THEN 5
WHEN D.SOURCETYPE='BIGINT' THEN -5 WHEN D.SOURCETYPE='FLOAT' AND
C.LENGTH=4 THEN 7 WHEN D.SOURCETYPE='FLOAT' AND C.LENGTH=8 THEN 8
WHEN D.SOURCETYPE='REAL' THEN 7 WHEN D.SOURCETYPE='DOUBLE' THEN 8
WHEN D.SOURCETYPE='CHARACTER' AND C.CODEPAGE <0 then 1 WHEN
D.SOURCETYPE='CHARACTER' AND C.CODEPAGE = 0 then -2 WHEN
D.SOURCETYPE='VARCHAR' AND C.CODEPAGE <0 THEN 12 WHEN
D.SOURCETYPE='VARCHAR' AND C.CODEPAGE = 0 THEN -3 WHEN
D.SOURCETYPE='LONG VARCHAR' AND C.CODEPAGE <0 THEN -1 WHEN
D.SOURCETYPE='LONG VARCHAR' AND C.CODEPAGE = 0 THEN -4 WHEN
D.SOURCETYPE='DECIMAL' THEN 3 WHEN D.SOURCETYPE='GRAPHIC' THEN -95
WHEN D.SOURCETYPE='VARGRAPHIC' THEN -96 WHEN D.SOURCETYPE='LONG
VARGRAPHIC' THEN -97 WHEN D.SOURCETYPE='BLOB' THEN -98 WHEN
D.SOURCETYPE='CLOB' THEN -99 WHEN D.SOURCETYPE='DBCLOB' THEN -350
WHEN D.SOURCETYPE='DATE' THEN 91 WHEN D.SOURCETYPE='TIME' THEN 92
WHEN D.SOURCETYPE='TIMESTAMP' THEN 93 WHEN D.SOURCETYPE='DATALINK'
THEN -400 ELSE 0 END), CAST( NULL AS VARCHAR(8) ), SMALLINT(CASE
WHEN C.IDENTITY = 'Y' THEN 2 ELSE 1 END ) FROM SYSIBM.SYSCOLUMNS
C, SYSIBM.SYSDATATYPES D, SYSIBM.SYSTABLES T, TYPEINTS I WHERE
T.NAME LIKE 'VT_CO_M_SUBJECT' ESCAPE '\' AND ( (C.TBCREATOR =
T.CREATOR AND C.TBNAME = T.NAME ) OR (C.TBCREATOR =
T.BASE_SCHEMA AND C.TBNAME = T.BASE_NAME ) ) AND C.COLTYPE <'REF'
AND I.TYPEINT <22 AND C.TYPENAME = D.NAME AND C.TYPESCHEMA =
D.SCHEMA AND C.COLTYPE = I.COLTYPE UNION ALL SELECT CAST( NULL AS
VARCHAR(128) ), RTRIM(T.CREATOR), T.NAME, C.NAME, SMALLINT(20),
CAST( '"' || RTRIM(D.SCHEMA) || '"."' || D.NAME || '"' AS
VARCHAR(261) ), CASE WHEN D.SOURCETYPE = 'REAL' THEN 24 WHEN
D.SOURCETYPE = 'DOUBLE' THEN 53 ELSE D.LENGTH END, CASE
WHEN D.SOURCETYPE NOT IN ('GRAPHIC','VARGRAPHIC','LONG
VARGRAPHIC','DBCLOB') THEN D.LENGTH WHEN D.SOURCETYPE
IN ('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB')
THEN D.LENGTH * 2 ELSE NULL END, SMALLINT( CASE WHEN
D.SOURCETYPE='DECIMAL' THEN D.SCALE WHEN D.SOURCETYPE IN
('INTEGER','SMALLINT','BIGINT','TIME') THEN 0 WHEN
D.SOURCETYPE='TIMESTAMP' THEN 6 ELSE NULL END ),
SMALLINT( CASE WHEN D.SOURCETYPE IN
('DECIMAL','INTEGER','SMALLINT','BIGINT') THEN 10 WHEN
D.SOURCETYPE IN ('REAL','FLOAT','DOUBLE') THEN 2 ELSE NULL
END), SMALLINT(CASE WHEN C.NULLS='Y' THEN 1 ELSE 0 END),
C.REMARKS, C.DEFAULT, SMALLINT( 20 ), SMALLINT( CASE WHEN
D.SOURCETYPE='DATE' THEN 1 WHEN D.SOURCETYPE='TIME' THEN 2 WHEN
D.SOURCETYPE='TIMESTAMP' THEN 3 ELSE NULL END), CASE WHEN
D.SOURCETYPE IN ('CHARACTER','VARCHAR','LONG
VARCHAR','BLOB','CLOB') THEN D.LENGTH WHEN D.SOURCETYPE IN
('GRAPHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN
D.LENGTH * 2 ELSE NULL END, C.COLNO + 1, CASE WHEN C.NULLS='Y' THEN
'YES' ELSE 'NO' END, SMALLINT( 2006 ), CAST( NULL AS VARCHAR(128) ),
CAST( NULL AS VARCHAR(128) ), CAST( NULL AS VARCHAR(128) ), SMALLINT(
CASE WHEN D.SOURCETYPE IS NULL THEN NULL WHEN
D.SOURCETYPE='INTEGER' THEN 4 WHEN D.SOURCETYPE='SMALLINT' THEN 5
WHEN D.SOURCETYPE='BIGINT' THEN -5 WHEN D.SOURCETYPE='FLOAT' AND
C.LENGTH=4 THEN 7 WHEN D.SOURCETYPE='FLOAT' AND C.LENGTH=8 THEN 8
WHEN D.SOURCETYPE='REAL' THEN 7 WHEN D.SOURCETYPE='DOUBLE' THEN 8
WHEN D.SOURCETYPE='CHARACTER' AND C.CODEPAGE <0 then 1 WHEN
D.SOURCETYPE='CHARACTER' AND C.CODEPAGE = 0 then -2 WHEN
D.SOURCETYPE='VARCHAR' AND C.CODEPAGE <0 THEN 12 WHEN
D.SOURCETYPE='VARCHAR' AND C.CODEPAGE = 0 THEN -3 WHEN
D.SOURCETYPE='LONG VARCHAR' AND C.CODEPAGE <0 THEN -1 WHEN
D.SOURCETYPE='LONG VARCHAR' AND C.CODEPAGE = 0 THEN -4 WHEN
D.SOURCETYPE='DECIMAL' THEN 3 WHEN D.SOURCETYPE='GRAPHIC' THEN -95
WHEN D.SOURCETYPE='VARGRAPHIC' THEN -96 WHEN D.SOURCETYPE='LONG
VARGRAPHIC' THEN -97 WHEN D.SOURCETYPE='BLOB' THEN -98 WHEN
D.SOURCETYPE='CLOB' THEN -99 WHEN D.SOURCETYPE='DBCLOB' THEN -350
WHEN D.SOURCETYPE='DATE' THEN 91 WHEN D.SOURCETYPE='TIME' THEN 92
WHEN D.SOURCETYPE='TIMESTAMP' THEN 93 WHEN D.SOURCETYPE='DATALINK'
THEN -400 ELSE 0 END ), CAST( NULL AS VARCHAR(8) ), SMALLINT(1) FROM
SYSIBM.SYSCOLUMNS C, SYSIBM.SYSDATATYPES D,
SYSIBM.SYSCOLPROPERTIES P, SYSIBM.SYSTABLES T, TYPEINTS I WHERE
T.NAME LIKE 'VT_CO_M_SUBJECT' ESCAPE '\' AND ( (C.TBCREATOR =
T.CREATOR AND C.TBNAME = T.NAME ) OR (C.TBCREATOR = T.BASE_SCHEMA
AND C.TBNAME = T.BASE_NAME ) ) AND C.COLTYPE = 'REF' AND I.TYPEINT
= 22 AND P.COLNAME = C.NAME AND P.TABSCHEMA = C.TBCREATOR AND
P.TABNAME = C.TBNAME AND P.TARGET_TYPENAME = D.NAME AND
P.TARGET_TYPESCHEMA = D.SCHEMA AND C.COLTYPE = I.COLTYPE ) SELECT
TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, SMALLINT( CASE
DATA_TYPE WHEN 70 THEN -400 WHEN 91 THEN 9 WHEN 92 THEN 10 WHEN
93 THEN 11 WHEN -95 then -8 WHEN -96 then -9 WHEN -97 then -10
ELSE DATA_TYPE END) as DATA_TYPE, TYPE_NAME, COLUMN_SIZE,
BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE, REMARKS,
COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH,
ORDINAL_POSITION, IS_NULLABLE FROM SYSIBM.SQLCOLS WHERE TABLE_NAME
LIKE 'VT_CO_M_SUBJECT' ESCAPE '\' ORDER BY 1,2,3,17

my advice is to run statitics and reorganise the indexes on the table
involved.

since these tables are system table situated in catalog tablespace
running db2empfa tool would be helpful.
(never tried and i'm not sure is there anything to do with performance
by using this tool)
Thanks for the reply. runstats is done and an index reorg is also done.

By default, the multipage_alloc is enabled. I think db2empfa would not
be required.

Sep 27 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.