473,466 Members | 1,393 Online
Bytes | Software Development & Data Engineering Community
Create Post

Home Posts Topics Members FAQ

Strange query

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
3 2749
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
"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
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
Create Post

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
Strange SQL behaviour when using query short-cuts
by: Ethel Aardvark | last post by:
I am running a 9.0.1 database on a W2K server and have come across some strange behaviour with a SQL query. I have a query which runs in a PL/SQL cursor which has several PL/SQL variables used to...
Oracle Database
0
Strange results when optimizing LEFT JOIN in MySQL
by: Soefara | last post by:
Dear Sirs, I am experiencing strange results when trying to optimize a LEFT JOIN on 3 tables using MySQL. Given 3 tables A, B, C such as the following: create table A ( uniqueId int not...
MySQL Database
24
Strange OSQL behavoir.
by: LineVoltageHalogen | last post by:
Greetings All, I was hoping that someone out there has run into this issue before and can shed some light on it for me. I have a stored procedure that essentially does a mini ETL from a source...
Microsoft SQL Server
2
Can't stop the query? Strange DLL error
by: Mal | last post by:
Greetings. I have a perplexing problem....please help. I am having a problem with an insert query. (SQL below) When I run the query via code (executing the SQL string) it crashes Access and...
Microsoft Access / VBA
2
VERY strange database connection problem
by: Buddy Ackerman | last post by:
I have a web app that I have setup on numerous web servers. I've set one up for a new client at their hosting facility and cannot get it to connect to their database. I get a "SQL Server does not...
ASP.NET
3
Strange JOIN query problem
by: phillip.s.powell | last post by:
I have to produce a query to obtain a single record, however, the tables I work with have a very strange relationship: Table: student Fields: id first_name last_name email school_type_id...
MySQL Database
5
union query - strange unwanted grouping
by: BillCo | last post by:
I'm having a problem with a union query, two simple queries joined with a union statement. It's created in code based on parameters. Users were noticing some inconsistant data and when I analysed...
Microsoft Access / VBA
4
Strange IMPORT warning
by: Gregor KovaĨ | last post by:
Hi! When I'm using IMPORT with INSERT_UPDATE I sometimes get SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. I'm not sure why this happens....
DB2 Database
2
Strange Bug
by: BinaryMonk | last post by:
Ok, this makes absolutely no sense to me, so I am hoping that someone here will have run across this issue as well. I have a pretty simple PHP script. which essentially just selects and displays...
PHP
1
Strange text characters in query
by: bbatson | last post by:
Hello, I am receiving strange text characters in a query linked to a back-end table with a memo text field. Below is the strange text that I am receiving in the query: ...
Microsoft Access / VBA
0
marktang
What is ONU?
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
General
0
Oralloy
Problem With Comparison Operator <=> in G++
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
C / C++
0
jinu1996
Maximizing Business Potential: The Nexus of Website Design and Digital Marketing
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
Online Marketing
0
tracyyun
Discussion: How does Zigbee compare with other wireless protocols in smart home applications?
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
General
0
agi2029
AI Job Threat for Devs
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
Career Advice
0
isladogs
Access Europe - Using VBA to create a class based on a table - Wed 1 May
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...
Microsoft Access / VBA
0
Trying to create a lan-to-lan vpn between two differents networks
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...
Networking - Hardware / Configuration
0
Windows Forms - .Net 8.0
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
Visual Basic .NET
0
transfer the data from one system to another through ip address
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
C# / C Sharp

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes
How to Post and Respond on Bytes
How to Promote and Link on Bytes
How to increase your Ranking on Bytes
Become a Recognized Expert on Bytes
Feedback Welcomed! Contact Us