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

Strange query

P: n/a
I was getting snapshot since the database was responding very
slow...here is the query that was in a snapshot:

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) ), ( SMA
LLINT(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), CH
AR( '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, TABL
E_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE,
BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PR
EC_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 t
hen 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 10 WHEN
I.TYPEINT=18 THEN 11 WHEN I.TYPEINT=19 THEN -400 WHEN
I.TYPEINT=20 THEN -450 WHEN I.TYPEINT=2
1 THEN -450 WHEN I.TYPEINT=22 THEN 20 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.TYPEIN
T=6 AND C.CODEPAGE = 0 THEN 'CHAR () FOR BIT DATA' WHEN I.TYPEINT=7
AND C.CODEPAGE <0 THEN 'VARC
HAR' WHEN I.TYPEINT=7 AND C.CODEPAGE = 0 THEN 'VARCHAR () FOR BIT
DATA' WHEN I.TYPEINT=8 AND C.C
ODEPAGE <0 THEN 'LONG VARCHAR' WHEN I.TYPEINT=8 AND C.CODEPAGE = 0
THEN 'LONG VARCHAR FOR BIT DA
TA' WHEN I.TYPEINT=9 THEN 'DECIMAL' WHEN I.TYPEINT=10 THEN
'GRAPHIC' WHEN I.TYPEINT=11 THEN 'V
ARGRAPHIC' WHEN I.TYPEINT=12 THEN 'LONG VARGRAPHIC' WHEN
I.TYPEINT=13 THEN 'BLOB' WHEN I.TYPEI
NT=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.TY
PEINT=20 THEN '"' || RTRIM(D.SCHEMA) || '"."' || D.NAME || '"' WHEN
I.TYPEINT=21 THEN '"' || RTRIM
(D.SCHEMA) || '"."' || D.NAME || '"' WHEN I.TYPEINT=22 THEN
'REFERENCE' ELSE '' END AS VARCHAR(2
61) ), 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=1
3 THEN C.LONGLENGTH WHEN I.TYPEINT=14 THEN C.LONGLENGTH WHEN
I.TYPEINT=15 THEN C.LONGLENGTH WH
EN I.TYPEINT=16 THEN 10 WHEN I.TYPEINT=17 THEN 8 WHEN I.TYPEINT=18
THEN 26 WHEN I.TYPEINT=19 T
HEN C.LENGTH WHEN I.TYPEINT=20 THEN D.LENGTH WHEN I.TYPEINT=21
THEN D.LENGTH WHEN I.TYPEINT=22
THEN D.LENGTH ELSE C.LENGTH END, CASE WHEN I.TYPEINT=1 THEN 4
WHEN I.TYPEINT=2 THEN 2 WHE
N I.TYPEINT=3 THEN 8 WHEN I.TYPEINT=4 THEN 4 WHEN I.TYPEINT=5 THEN
8 WHEN I.TYPEINT=6 THEN C.L
ENGTH WHEN I.TYPEINT=7 THEN C.LENGTH WHEN I.TYPEINT=8 THEN
C.LENGTH WHEN I.TYPEINT=9 THEN C.LE
NGTH+2 WHEN I.TYPEINT=10 THEN C.LENGTH*2 WHEN I.TYPEINT=11 THEN
C.LENGTH*2 WHEN I.TYPEINT=12 T
HEN 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.TYPEIN
T=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 WHEN I.TYPEINT=22
AND D.SOURCETYPE NOT IN ('GRAPHIC','VARGRAPHIC','LONG
VARGRAPHIC','DBCLOB') THEN D
..LENGTH WHEN I.TYPEINT=22 AND D.SOURCETYPE IN
('GRAPHIC','VARGRAPHIC','LONG VARGRA
PHIC','DBCLOB') THEN D.LENGTH*2 ELSE NULL 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 T
HEN NULL WHEN I.TYPEINT=6 THEN NULL WHEN I.TYPEINT=7 THEN NULL
WHEN I.TYPEINT=8 THEN NULL WH
EN I.TYPEINT=9 THEN C.SCALE WHEN I.TYPEINT=10 THEN NULL WHEN
I.TYPEINT=11 THEN NULL WHEN I.TYP
EINT=12 THEN NULL WHEN I.TYPEINT=13 THEN NULL WHEN I.TYPEINT=14
THEN NULL WHEN I.TYPEINT=15 TH
EN 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 WH
EN I.TYPEINT=20 AND D.SOURCETYPE IN
('INTEGER','SMALLINT','BIGINT','TIME') THEN 0 WH
EN I.TYPEINT=20 AND D.SOURCETYPE='TIMESTAMP' THEN 6
WHEN I.TYPEINT=21 AND D.S
OURCETYPE='DECIMAL' THEN D.SCALE WHEN I.TYPEINT=21 AND
D.SOURCETYPE IN ('INTEGER','S
MALLINT','BIGINT','TIME') THEN 0 WHEN I.TYPEINT=21 AND
D.SOURCETYPE='TIMESTAMP'
THEN 6 WHEN I.TYPEINT=22 AND D.SOURCETYPE='DECIMAL'
THEN D.SCALE WHEN I.TYPEINT
=22 AND D.SOURCETYPE IN
('INTEGER','SMALLINT','BIGINT','TIME') THEN 0 WHEN I.TYPEINT
=22 AND D.SOURCETYPE='TIMESTAMP' THEN 6 ELSE NULL
END ), SMALLINT(CASE WHEN I.TY
PEINT=1 THEN 10 WHEN I.TYPEINT=2 THEN 10 WHEN I.TYPEINT=3 THEN
10 WHEN I.TYPEINT=4 THEN 2 WH
EN I.TYPEINT=5 THEN 2 WHEN I.TYPEINT=6 THEN NULL WHEN I.TYPEINT=7
THEN NULL WHEN I.TYPEINT=8 T
HEN NULL WHEN I.TYPEINT=9 THEN 10 WHEN I.TYPEINT=10 THEN NULL
WHEN I.TYPEINT=11 THEN NULL WH
EN I.TYPEINT=12 THEN NULL WHEN I.TYPEINT=13 THEN NULL WHEN
I.TYPEINT=14 THEN NULL WHEN I.TYPEI
NT=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','INTEG
ER','SMALLINT','BIGINT') THEN 10 WHEN I.TYPEINT=20 AND
D.SOURCETYPE IN ('REAL','FLOA
T','DOUBLE') THEN 2 WHEN I.TYPEINT=21 AND D.SOURCETYPE
IN ('DECIMAL','INTEGER','SMAL
LINT','BIGINT') THEN 10 WHEN I.TYPEINT=21 AND
D.SOURCETYPE IN ('REAL','FLOAT','DOUBL
E') THEN 2 WHEN I.TYPEINT=22 AND D.SOURCETYPE IN
('DECIMAL','INTEGER','SMALLINT','BI
GINT') THEN 10 WHEN I.TYPEINT=22 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.DEFA
ULT, 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 WH
EN I.TYPEINT=6 AND C.CODEPAGE = 0 then -2 WHEN I.TYPEINT=7 AND
C.CODEPAGE <0 THEN 12 WHEN I.T
YPEINT=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.TYPE
INT=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.TYPE
INT=18 THEN 9 WHEN I.TYPEINT=19 THEN -400 WHEN I.TYPEINT=20 THEN
-450 WHEN I.TYPEINT=21 THEN -
450 WHEN I.TYPEINT=22 THEN 20 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 THE
N 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 NU
LL 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 A
ND 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 WHEN I.TYPEINT=22 A
ND D.SOURCETYPE='DATE' THEN 1 WHEN I.TYPEINT=22 AND
D.SOURCETYPE='TIME' THEN 2 WHEN I.TYP
EINT=22 AND D.SOURCETYPE='TIMESTAMP' THEN 3 WHEN
I.TYPEINT=999 THEN 0 ELSE NULL END), C
ASE 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 WHE
N I.TYPEINT=14 THEN C.LONGLENGTH WHEN I.TYPEINT=15 THEN C.LONGLENGTH*2
WHEN I.TYPEINT=16 THEN NULL W
HEN 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.LENG
TH 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 ('GR
APHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN D.LENGTH*2
WHEN I.TYPEINT=22 AND D.
SOURCETYPE IN ('CHARACTER','VARCHAR','LONG
VARCHAR','BLOB','CLOB') THEN D.LENGTH WHEN I.TY
PEINT=22 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.TYPEI
NT=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.COD
EPAGE = 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.TY
PEINT=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 WHEN I.
TYPEINT=22 THEN 2006 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='REAL' THEN 7 WHEN D.SOURCETYPE='DOUBLE' THEN 8 WHEN
D.SOURCETYPE='CHARACTER' AND D.C
ODEPAGE <0 THEN 1 WHEN D.SOURCETYPE='CHARACTER' AND D.CODEPAGE = 0
THEN -2 WHEN D.SOURCETYPE='
VARCHAR' AND D.CODEPAGE <0 THEN 12 WHEN D.SOURCETYPE='VARCHAR' AND
D.CODEPAGE = 0 THEN -3 WHEN
D.SOURCETYPE='LONG VARCHAR' AND D.CODEPAGE <0 THEN -1 WHEN
D.SOURCETYPE='LONG VARCHAR' AND D.CO
DEPAGE = 0 THEN -4 WHEN D.SOURCETYPE='DECIMAL' THEN 3 WHEN
D.SOURCETYPE='GRAPHIC' THEN -95 WHE
N D.SOURCETYPE='VARGRAPHIC' THEN -96 WHEN D.SOURCETYPE='LONG
VARGRAPHIC' THEN -97 WHEN D.SOURCET
YPE='BLOB' THEN -98 WHEN D.SOURCETYPE='CLOB' THEN -99 WHEN
D.SOURCETYPE='DBCLOB' THEN -350 WHE
N D.SOURCETYPE='DATE' THEN 9 WHEN D.SOURCETYPE='TIME' THEN 10 WHEN
D.SOURCETYPE='TIMESTAMP' THEN
11 WHEN D.SOURCETYPE='DATALINK' THEN -400 ELSE 0 END),
CAST( NULL AS VARCHAR(8) ), SMALLIN
T(CASE WHEN C.IDENTITY = 'Y' THEN 2 ELSE 1 END ) FROM
SYSIBM.SYSCOLUMNS C, SYSIBM.SYSDATATYP
ES D, SYSIBM.SYSTABLES T, TYPEINTS I WHERE RTRIM(T.CREATOR) LIKE
'TB_SCH2S' ESCAPE '\' AND T.N
AME LIKE 'CAL' 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.TYPENAME = D.NAME AND C.TYPESCHEM
A = D.SCHEMA AND C.COLTYPE = I.COLTYPE ) SELECT TABLE_CAT,
TABLE_SCHEM, TABLE_NAME, COLUMN_NAME,
DATA_TYPE, TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS,
NUM_PREC_RADIX, NULLABLE, RE
MARKS, COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH, ORDINAL_POSITION, IS_NULL
ABLE FROM SYSIBM.SQLCOLS WHERE TABLE_SCHEM LIKE 'TB_SCH2S' ESCAPE '\'
AND TABLE_NAME LIKE 'CAL'
ESCAPE '\' ORDER BY 1,2,3,17
Can anybody shed some light on why this query was executed? The
strange thing is that "Client login ID" shows a developer's ID, but he
did NOT log in the database (I talked to the developer and got this
information).

Apr 9 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
an*************@gmail.com wrote:
I was getting snapshot since the database was responding very
slow...here is the query that was in a snapshot:

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) ), ( SMA
LLINT(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), CH
AR( '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, TABL
E_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE,
BUFFER_LENGTH, DECIMAL_DIGITS, NUM_PR
EC_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 t
hen 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 10 WHEN
I.TYPEINT=18 THEN 11 WHEN I.TYPEINT=19 THEN -400 WHEN
I.TYPEINT=20 THEN -450 WHEN I.TYPEINT=2
1 THEN -450 WHEN I.TYPEINT=22 THEN 20 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.TYPEIN
T=6 AND C.CODEPAGE = 0 THEN 'CHAR () FOR BIT DATA' WHEN I.TYPEINT=7
AND C.CODEPAGE <0 THEN 'VARC
HAR' WHEN I.TYPEINT=7 AND C.CODEPAGE = 0 THEN 'VARCHAR () FOR BIT
DATA' WHEN I.TYPEINT=8 AND C.C
ODEPAGE <0 THEN 'LONG VARCHAR' WHEN I.TYPEINT=8 AND C.CODEPAGE = 0
THEN 'LONG VARCHAR FOR BIT DA
TA' WHEN I.TYPEINT=9 THEN 'DECIMAL' WHEN I.TYPEINT=10 THEN
'GRAPHIC' WHEN I.TYPEINT=11 THEN 'V
ARGRAPHIC' WHEN I.TYPEINT=12 THEN 'LONG VARGRAPHIC' WHEN
I.TYPEINT=13 THEN 'BLOB' WHEN I.TYPEI
NT=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.TY
PEINT=20 THEN '"' || RTRIM(D.SCHEMA) || '"."' || D.NAME || '"' WHEN
I.TYPEINT=21 THEN '"' || RTRIM
(D.SCHEMA) || '"."' || D.NAME || '"' WHEN I.TYPEINT=22 THEN
'REFERENCE' ELSE '' END AS VARCHAR(2
61) ), 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=1
3 THEN C.LONGLENGTH WHEN I.TYPEINT=14 THEN C.LONGLENGTH WHEN
I.TYPEINT=15 THEN C.LONGLENGTH WH
EN I.TYPEINT=16 THEN 10 WHEN I.TYPEINT=17 THEN 8 WHEN I.TYPEINT=18
THEN 26 WHEN I.TYPEINT=19 T
HEN C.LENGTH WHEN I.TYPEINT=20 THEN D.LENGTH WHEN I.TYPEINT=21
THEN D.LENGTH WHEN I.TYPEINT=22
THEN D.LENGTH ELSE C.LENGTH END, CASE WHEN I.TYPEINT=1 THEN 4
WHEN I.TYPEINT=2 THEN 2 WHE
N I.TYPEINT=3 THEN 8 WHEN I.TYPEINT=4 THEN 4 WHEN I.TYPEINT=5 THEN
8 WHEN I.TYPEINT=6 THEN C.L
ENGTH WHEN I.TYPEINT=7 THEN C.LENGTH WHEN I.TYPEINT=8 THEN
C.LENGTH WHEN I.TYPEINT=9 THEN C.LE
NGTH+2 WHEN I.TYPEINT=10 THEN C.LENGTH*2 WHEN I.TYPEINT=11 THEN
C.LENGTH*2 WHEN I.TYPEINT=12 T
HEN 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.TYPEIN
T=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 WHEN I.TYPEINT=22
AND D.SOURCETYPE NOT IN ('GRAPHIC','VARGRAPHIC','LONG
VARGRAPHIC','DBCLOB') THEN D
.LENGTH WHEN I.TYPEINT=22 AND D.SOURCETYPE IN
('GRAPHIC','VARGRAPHIC','LONG VARGRA
PHIC','DBCLOB') THEN D.LENGTH*2 ELSE NULL 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 T
HEN NULL WHEN I.TYPEINT=6 THEN NULL WHEN I.TYPEINT=7 THEN NULL
WHEN I.TYPEINT=8 THEN NULL WH
EN I.TYPEINT=9 THEN C.SCALE WHEN I.TYPEINT=10 THEN NULL WHEN
I.TYPEINT=11 THEN NULL WHEN I.TYP
EINT=12 THEN NULL WHEN I.TYPEINT=13 THEN NULL WHEN I.TYPEINT=14
THEN NULL WHEN I.TYPEINT=15 TH
EN 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 WH
EN I.TYPEINT=20 AND D.SOURCETYPE IN
('INTEGER','SMALLINT','BIGINT','TIME') THEN 0 WH
EN I.TYPEINT=20 AND D.SOURCETYPE='TIMESTAMP' THEN 6
WHEN I.TYPEINT=21 AND D.S
OURCETYPE='DECIMAL' THEN D.SCALE WHEN I.TYPEINT=21 AND
D.SOURCETYPE IN ('INTEGER','S
MALLINT','BIGINT','TIME') THEN 0 WHEN I.TYPEINT=21 AND
D.SOURCETYPE='TIMESTAMP'
THEN 6 WHEN I.TYPEINT=22 AND D.SOURCETYPE='DECIMAL'
THEN D.SCALE WHEN I.TYPEINT
=22 AND D.SOURCETYPE IN
('INTEGER','SMALLINT','BIGINT','TIME') THEN 0 WHEN I.TYPEINT
=22 AND D.SOURCETYPE='TIMESTAMP' THEN 6 ELSE NULL
END ), SMALLINT(CASE WHEN I.TY
PEINT=1 THEN 10 WHEN I.TYPEINT=2 THEN 10 WHEN I.TYPEINT=3 THEN
10 WHEN I.TYPEINT=4 THEN 2 WH
EN I.TYPEINT=5 THEN 2 WHEN I.TYPEINT=6 THEN NULL WHEN I.TYPEINT=7
THEN NULL WHEN I.TYPEINT=8 T
HEN NULL WHEN I.TYPEINT=9 THEN 10 WHEN I.TYPEINT=10 THEN NULL
WHEN I.TYPEINT=11 THEN NULL WH
EN I.TYPEINT=12 THEN NULL WHEN I.TYPEINT=13 THEN NULL WHEN
I.TYPEINT=14 THEN NULL WHEN I.TYPEI
NT=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','INTEG
ER','SMALLINT','BIGINT') THEN 10 WHEN I.TYPEINT=20 AND
D.SOURCETYPE IN ('REAL','FLOA
T','DOUBLE') THEN 2 WHEN I.TYPEINT=21 AND D.SOURCETYPE
IN ('DECIMAL','INTEGER','SMAL
LINT','BIGINT') THEN 10 WHEN I.TYPEINT=21 AND
D.SOURCETYPE IN ('REAL','FLOAT','DOUBL
E') THEN 2 WHEN I.TYPEINT=22 AND D.SOURCETYPE IN
('DECIMAL','INTEGER','SMALLINT','BI
GINT') THEN 10 WHEN I.TYPEINT=22 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.DEFA
ULT, 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 WH
EN I.TYPEINT=6 AND C.CODEPAGE = 0 then -2 WHEN I.TYPEINT=7 AND
C.CODEPAGE <0 THEN 12 WHEN I.T
YPEINT=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.TYPE
INT=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.TYPE
INT=18 THEN 9 WHEN I.TYPEINT=19 THEN -400 WHEN I.TYPEINT=20 THEN
-450 WHEN I.TYPEINT=21 THEN -
450 WHEN I.TYPEINT=22 THEN 20 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 THE
N 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 NU
LL 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 A
ND 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 WHEN I.TYPEINT=22 A
ND D.SOURCETYPE='DATE' THEN 1 WHEN I.TYPEINT=22 AND
D.SOURCETYPE='TIME' THEN 2 WHEN I.TYP
EINT=22 AND D.SOURCETYPE='TIMESTAMP' THEN 3 WHEN
I.TYPEINT=999 THEN 0 ELSE NULL END), C
ASE 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 WHE
N I.TYPEINT=14 THEN C.LONGLENGTH WHEN I.TYPEINT=15 THEN C.LONGLENGTH*2
WHEN I.TYPEINT=16 THEN NULL W
HEN 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.LENG
TH 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 ('GR
APHIC','VARGRAPHIC','LONG VARGRAPHIC','DBCLOB') THEN D.LENGTH*2
WHEN I.TYPEINT=22 AND D.
SOURCETYPE IN ('CHARACTER','VARCHAR','LONG
VARCHAR','BLOB','CLOB') THEN D.LENGTH WHEN I.TY
PEINT=22 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.TYPEI
NT=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.COD
EPAGE = 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.TY
PEINT=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 WHEN I.
TYPEINT=22 THEN 2006 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='REAL' THEN 7 WHEN D.SOURCETYPE='DOUBLE' THEN 8 WHEN
D.SOURCETYPE='CHARACTER' AND D.C
ODEPAGE <0 THEN 1 WHEN D.SOURCETYPE='CHARACTER' AND D.CODEPAGE = 0
THEN -2 WHEN D.SOURCETYPE='
VARCHAR' AND D.CODEPAGE <0 THEN 12 WHEN D.SOURCETYPE='VARCHAR' AND
D.CODEPAGE = 0 THEN -3 WHEN
D.SOURCETYPE='LONG VARCHAR' AND D.CODEPAGE <0 THEN -1 WHEN
D.SOURCETYPE='LONG VARCHAR' AND D.CO
DEPAGE = 0 THEN -4 WHEN D.SOURCETYPE='DECIMAL' THEN 3 WHEN
D.SOURCETYPE='GRAPHIC' THEN -95 WHE
N D.SOURCETYPE='VARGRAPHIC' THEN -96 WHEN D.SOURCETYPE='LONG
VARGRAPHIC' THEN -97 WHEN D.SOURCET
YPE='BLOB' THEN -98 WHEN D.SOURCETYPE='CLOB' THEN -99 WHEN
D.SOURCETYPE='DBCLOB' THEN -350 WHE
N D.SOURCETYPE='DATE' THEN 9 WHEN D.SOURCETYPE='TIME' THEN 10 WHEN
D.SOURCETYPE='TIMESTAMP' THEN
11 WHEN D.SOURCETYPE='DATALINK' THEN -400 ELSE 0 END),
CAST( NULL AS VARCHAR(8) ), SMALLIN
T(CASE WHEN C.IDENTITY = 'Y' THEN 2 ELSE 1 END ) FROM
SYSIBM.SYSCOLUMNS C, SYSIBM.SYSDATATYP
ES D, SYSIBM.SYSTABLES T, TYPEINTS I WHERE RTRIM(T.CREATOR) LIKE
'TB_SCH2S' ESCAPE '\' AND T.N
AME LIKE 'CAL' 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.TYPENAME = D.NAME AND C.TYPESCHEM
A = D.SCHEMA AND C.COLTYPE = I.COLTYPE ) SELECT TABLE_CAT,
TABLE_SCHEM, TABLE_NAME, COLUMN_NAME,
DATA_TYPE, TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS,
NUM_PREC_RADIX, NULLABLE, RE
MARKS, COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH, ORDINAL_POSITION, IS_NULL
ABLE FROM SYSIBM.SQLCOLS WHERE TABLE_SCHEM LIKE 'TB_SCH2S' ESCAPE '\'
AND TABLE_NAME LIKE 'CAL'
ESCAPE '\' ORDER BY 1,2,3,17
Can anybody shed some light on why this query was executed? The
strange thing is that "Client login ID" shows a developer's ID, but he
did NOT log in the database (I talked to the developer and got this
information).
Not sure what this is supposed to do, but as it seems purely system related,
I would suggest checking whether you should reorg/runstats the system
catalog tables/indexes.

HTH.

--
Jeroen
Apr 9 '07 #2

P: n/a
"The Boss" <us****@No.Spam.Please.invalidwrote in message
news:46*********************@news.xs4all.nl...
Not sure what this is supposed to do, but as it seems purely system
related, I would suggest checking whether you should reorg/runstats the
system catalog tables/indexes.

HTH.
--
Jeroen
Or it could be have run by a GUI tool such as the Control Center (or a 3rd
party tool).
Apr 9 '07 #3

P: n/a
On Apr 9, 7:03 pm, "Mark A" <nob...@nowhere.comwrote:
"The Boss" <use...@No.Spam.Please.invalidwrote in message

news:46*********************@news.xs4all.nl...
Not sure what this is supposed to do, but as it seems purely system
related, I would suggest checking whether you should reorg/runstats the
system catalog tables/indexes.
HTH.
--
Jeroen

Or it could be have run by a GUI tool such as the Control Center (or a 3rd
party tool).
Thanks all for your help. The problem did not occur today...

Apr 11 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.