-- Zwischenspeicher der zu untersuchenden Datenbanken 00010000
-- 00020000
DECLARE GLOBAL TEMPORARY TABLE FAMLISTE 00030000
( FAMILY CHAR(20) 00040000
, DBNAME CHAR(08) 00050000
, STORAR CHAR(03) 00060000
, ENSEG CHAR(03) 00070000
, STORAR_N INTEGER 00080000
, ENSEG_N SMALLINT 00090000
, CREATE_FROM DATE 00100000
, CREATE_TO DATE 00110000
) 00120000
; 00130000
-- ============ Input section (full check) ============ 00140000
-- Full check (database, table space etc.) when create_from and 00150000
-- create_to have value NULL 00160000
-- 00170000
-- Partial check on table space level when create_from and 00180000
-- create_to have not NULL value and the values are valid date 00190000
-- 00200000
INSERT INTO SESSION.FAMLISTE ( FAMILY 00210000
, DBNAME 00220000
, CREATE_FROM 00230000
, CREATE_TO 00240000
) 00250000
VALUES ( '?' 00260000
, NULL 00270000
, current date - 1 days 00280000
, '31.12.9999' 00290000
) 00300000
; 00310000
-- 00320000
-- ============ Release notes/ documentation section ============ 00330000
-- 00340000
-- ### INFO Version 2.3, Date: 28.11.2013 00350000
-- ### INFO Library: actual Version only available in DSN.GENERAL.SQL 00360000
-- ### Author: A559315, Uwe Toepperwien (TCG), KIDI 61 00370000
-- 00380000
-- ============ Documentation section ============ 00390000
-- 00400000
-- DDL Checker für partitionierte Datenbanken ELAR. In ELAR ist nur 00410000
-- Table controlled Partition zulässig, folglich werden Non partition 00420000
-- Tables und Index controlled Partition nicht verifiziert. 00430000
-- Grundlage sind die DDL Vereinbarungen vom 10/2010 00440000
-- Nachfolgende DB2 Objekte werden geprüft: 00450000
-- (1) Datenbank 00460000
-- (2) Tablespaces 00470000
-- (3) Tabeles 00480000
-- (4) Indexspaces 00490000
-- (5) Index(e) Primary DPSI/ DPSI 00500000
-- 00510000
-- Ein DDL Check ist möglich durch Angabe der ELAR Familie und/ oder 00520000
-- der Datenbank. 00530000
-- 00540000
-- Ist das Result des DDL Checkers "empty", dann sind alle vereinbarten 00550000
-- DDL Parameter eingehalten. 00560000
-- 00570000
-- Ist das Result des DDL Checkers nicht "empty", dann 00580000
-- (a) werden alle DDL Parameter Abweichungen ausgewiesen, wenn die 00590000
-- Familie/ Datenbank vom Typ Table Controlled Partition ist 00600000
-- (b) der Fehler "DatabaseImplementation" zeigt an, das die Familie/ 00610000
-- Datenbank nicht vom Typ Table Controlled Partition ist. 00620000
-- 00630000
-- Parameterwert fuer maximale Anzahl Partition festlegen 00640000
-- Die maximale Anzahl Partitionen ist für Table- und Indexcontrolled 00650000
-- Partitioning vorzugeben. 00660000
-- 00670000
-- Update: 28.11.2013, TCG 00680000
-- Partial check possible, when create_from and create_to is not null 00690000
-- 00700000
-- Update: 08.07.2013, TCG 00710000
-- Generating final message which describe the state of a cekced 00720000
-- database. 00730000
-- Check pctfree for technical/ business indexes use wrong 00740000
-- catalog table (sysibm.syscolumns instead of syskeys) 00750000
-- 00760000
-- Update: 01.07.2013, TCG (new agreement KIDI63/ ELAR for NDBS) 00770000
-- (see Mail from 01.07.2013 M.Streit) 00780000
-- Tablespace Option Check 00790000
-- (1) Check FREEPAGE, PCTFREE 00800000
-- (2) PARTITION = 0 not allowed for XB databases 00810000
-- Table Option Check 00820000
-- (1) option APPEND changed from NO to YES 00830000
-- (2) no check of EN1ENTERTAG, ET1LOGPROD 00840000
-- (3) Check on table the type of partitioning (classic) 00850000
-- (4) Check if index is index controlled partitioning (antique) 00860000
-- (5) Extend index type check, only D, U and P are allowed 00870000
-- (6) Check that EN1PART is in keylist of unique/ primary index 00880000
-- (7) Check that every table has only one unique/ primary index 00890000
-- (8) Check that cluster index based on correct columns 00900000
-- (9) Check that key columns on correct position 00910000
-- 00920000
-- Update: 17.05.2012, TCG 00930000
-- (1) Find database name for migrated families (migrated families 00940000
-- have a symbolic name like FAMnnmm where nn is multipler 1000 00950000
-- and mm is the family number. Exp. FAM1015 is family 15 that is 00960000
-- family DEVI.IF) 00970000
-- (2) New column COMMENT describes the family 00980000
-- (3) Result shows first families from migration and all other 00990000
-- families. 01000000
-- (4) Correction of text mistakes. 01010000
-- 01020000
-- Update: 10.05.2012, TCG 01030000
-- (1) New Bufferpool assignment (BP2 and BP1) 01040000
-- (2) Check Bufferpool BP8 for LOB 01050000
-- (3) De-activate Partition and Index Columun check 01060000
-- for database with prefix DXB (Elar Metadata) 01070000
-- (4) Result set order changed to family, database, tablespace, 01080000
-- table, indexspace and index. 01090000
-- (5) Defined more and detailed messages. 01100000
-- 01110000
-- ============ Processing section ============ 01120000
-- 01130000
--- Definition of partition limit for table- and indexspace 01140000
DECLARE GLOBAL TEMPORARY TABLE MAXPART 01150000
( PARTYPE CHAR(05) NOT NULL 01160000
, MAXPART INTEGER NOT NULL 01170000
) 01180000
; 01190000
INSERT INTO SESSION.MAXPART ( PARTYPE, MAXPART ) 01200000
VALUES ( 'Table', 200 ) 01210000
; 01220000
INSERT INTO SESSION.MAXPART ( PARTYPE, MAXPART ) 01230000
VALUES ( 'Index', 200 ) 01240000
; 01250000
UPDATE SESSION.FAMLISTE 01260000
SET STORAR = SUBSTR(DBNAME,3,3) 01270000
, ENSEG = SUBSTR(DBNAME,6,3) 01280000
WHERE SUBSTR(DBNAME,1,2) = 'XB' 01290000
AND CREATE_FROM IS NULL 01300000
AND CREATE_TO IS NULL 01310000
; 01320000
UPDATE SESSION.FAMLISTE 01330000
SET STORAR_N = (SELECT XBA510_STORAGE_ARN 01340000
FROM BUA.TXBA510 01350000
WHERE XBA510_STORAGE_ARC = STORAR 01360000
) 01370000
, ENSEG_N = (SELECT XBA510_STORAGE_ARN 01380000
FROM BUA.TXBA510 01390000
WHERE XBA510_STORAGE_ARC = ENSEG 01400000
) 01410000
WHERE CREATE_FROM IS NULL 01420000
AND CREATE_TO IS NULL 01430000
; 01440000
DECLARE GLOBAL TEMPORARY TABLE ELAR 01450000
( FAMILY CHAR(20) 01460000
, STORAR CHAR(08) 01470000
, COMMENT CHAR(20) 01480000
) 01490000
; 01500000
INSERT INTO SESSION.ELAR 01510000
WITH TESLA ( FAMILY 01520000
, STORAR 01530000
, COMMENT 01540000
) 01550000
-- 01560000
-- Search over family name and retrieve database name(s) 01570000
-- 1) Import run time tables 01580000
-- 2) Time range run time table and family configuration 01590000
-- (no data available over(1) 01600000
-- 3) Migration protocol (no data available over (1) and (2) 01610000
-- 4) Family number control table. Last change to find a family, 01620000
-- while all run time and meta data are broken (broken links) 01630000
-- 01640000
AS (SELECT C071.EAOF 01650000
, CAST ( C071.EYPREF || 01660000
C111.ENSTORAR || 01670000
C111.ENSEG AS CHAR(08) 01680000
) 01690000
, ' ' AS COMMENT 01700000
FROM BUA.TXBC111 C111 01710000
FULL OUTER JOIN BUA.TXBC071 C071 01720000
ON C111.ENSTORAR = C071.ENSTORAR 01730000
INNER JOIN SESSION.FAMLISTE FL 01740000
ON FL.FAMILY = C071.EAOF 01750000
WHERE C111.ENSTORAR IS NOT NULL 01760000
AND C111.ENSEG IS NOT NULL 01770000
AND C071.EYPREF IS NOT NULL 01780000
UNION DISTINCT 01790000
SELECT BE051.EAOF 01800000
, FL.DBNAME 01810000
, ' ' AS COMMENT 01820000
FROM BUA.TXBE051 BE051 01830000
INNER JOIN BUA.TXBA201 BA201 01840000
ON BE051.ENLIBENG = BA201.ENSTORAR 01850000
INNER JOIN SESSION.FAMLISTE FL 01860000
ON FL.FAMILY = BE051.EAOF 01870000
UNION DISTINCT 01880000
SELECT T504.XBA501_FAMNAME 01890000
, XBA504_DB 01900000
, '-- old Migration --' AS COMMENT 01910000
FROM BUA.TXBA504 T504 01920000
LEFT JOIN BUA.TXBA511 T511 01930000
ON T504.XBA501_FAMNAME = T511.XBA501_FAMNAME 01940000
AND T504.XBA504_TABLE = T511.XBA504_TABLE 01950000
INNER JOIN SESSION.FAMLISTE FL 01960000
ON FL.FAMILY = T504.XBA501_FAMNAME 01970000
UNION DISTINCT 01980000
SELECT STRIP(BA131.EAOF) 01990000
, SUBSTR(MG103.EANTABNAME,1,8) 02000000
, '-- new Migration --' AS COMMENT 02010000
FROM BUA.TXBMG103 MG103 02020000
INNER JOIN BUA.TXBA131 BA131 02030000
ON MG103.ENOF = BA131.ENOF 02040000
INNER JOIN SESSION.FAMLISTE FL 02050000
ON UCASE(BA131.EAOF) = FL.FAMILY 02060000
-- 02070000
-- Search over Database Name and retrieve family name 02080000
-- In general same processing like search over family, but other 02090000
-- columns are relevant. 02100000
-- 02110000
UNION DISTINCT 02120000
SELECT C071.EAOF 02130000
, FL.DBNAME 02140000
, ' ' AS COMMENT 02150000
FROM BUA.TXBC111 C111 02160000
FULL OUTER JOIN BUA.TXBC071 C071 02170000
ON C111.ENSTORAR = C071.ENSTORAR 02180000
INNER JOIN SESSION.FAMLISTE FL 02190000
ON FL.STORAR = C111.ENSTORAR 02200000
AND FL.ENSEG = C111.ENSEG 02210000
UNION DISTINCT 02220000
SELECT BE051.EAOF AS FAMILY 02230000
, FL.DBNAME 02240000
, ' ' AS COMMENT 02250000
FROM BUA.TXBE051 BE051 02260000
INNER JOIN BUA.TXBA201 BA201 02270000
ON BE051.ENLIBENG = BA201.ENSTORAR 02280000
INNER JOIN SESSION.FAMLISTE FL 02290000
ON FL.STORAR_N = BA201.ENSTORAR 02300000
AND FL.ENSEG_N = BA201.ENSEG 02310000
UNION DISTINCT 02320000
SELECT T504.XBA501_FAMNAME 02330000
, XBA504_DB 02340000
, ' ' AS COMMENT 02350000
FROM BUA.TXBA504 T504 02360000
LEFT JOIN BUA.TXBA511 T511 02370000
ON T504.XBA501_FAMNAME = T511.XBA501_FAMNAME 02380000
AND T504.XBA504_TABLE = T511.XBA504_TABLE 02390000
INNER JOIN SESSION.FAMLISTE FL 02400000
ON FL.DBNAME = T504.XBA504_DB 02410000
UNION DISTINCT 02420000
SELECT UCASE(BA131.EAOF) 02430000
, FL.DBNAME 02440000
, ' ' AS COMMENT 02450000
FROM BUA.TXBA131 BA131 02460000
INNER JOIN BUA.TXBG081 BG181 02470000
ON BA131.ENOF = BG181.ENOF 02480000
INNER JOIN SESSION.FAMLISTE FL 02490000
ON BG181.ENSTORAR = FL.STORAR_N 02500000
UNION DISTINCT 02510000
SELECT UCASE(BA131.EAOF) 02520000
, FL.DBNAME 02530000
, ' ' AS COMMENT 02540000
FROM BUA.TXBA131 BA131 02550000
INNER JOIN BUA.TXBG081X BG181 02560000
ON BA131.ENOF = BG181.ENOF 02570000
INNER JOIN SESSION.FAMLISTE FL 02580000
ON BG181.ENSTORAR = FL.STORAR_N 02590000
UNION DISTINCT 02600000
SELECT 'ELAR Repo. / Base' 02610000
, DBNAME 02620000
, ' ' AS COMMENT 02630000
FROM SESSION.FAMLISTE 02640000
WHERE SUBSTR(DBNAME,1,3) = 'DXB' 02650000
) 02660000
SELECT FAMILY 02670000
, STORAR 02680000
, COMMENT 02690000
FROM TESLA 02700000
WHERE (SELECT CREATE_FROM FROM SESSION.FAMLISTE) IS NULL 02710000
AND (SELECT CREATE_TO FROM SESSION.FAMLISTE) IS NULL 02720000
; 02730000
-- Add database without ELAR meta data into checklist 02740000
-- 02750000
INSERT INTO SESSION.ELAR 02760000
SELECT DBNAME 02770000
, DBNAME 02780000
, ' ' 02790000
FROM SESSION.FAMLISTE 02800000
WHERE DBNAME NOT IN (SELECT STORAR FROM SESSION.ELAR) 02810000
AND DBNAME <> '?' 02820000
AND (SELECT CREATE_FROM FROM SESSION.FAMLISTE) IS NULL 02830000
AND (SELECT CREATE_TO FROM SESSION.FAMLISTE) IS NULL 02840000
; 02850000
INSERT INTO SESSION.ELAR 02860000
SELECT DISTINCT 02870000
'?' 02880000
, DBNAME 02890000
, DBNAME 02900000
FROM SYSIBM.SYSTABLESPACE 02910000
WHERE (SELECT CREATE_FROM FROM SESSION.FAMLISTE ) IS NOT NULL 02920000
AND (SELECT CREATE_TO FROM SESSION.FAMLISTE ) IS NOT NULL 02930000
AND DATE(CREATEDTS) >= (SELECT CREATE_FROM FROM SESSION.FAMLISTE ) 02940000
AND DATE(CREATEDTS) <= (SELECT CREATE_TO FROM SESSION.FAMLISTE ) 02950000
AND DBNAME LIKE 'XB%' 02960000
; 02970000
UPDATE SESSION.FAMLISTE 02980000
SET CREATE_FROM = '01.01.0001' 02990000
, CREATE_TO = '31.12.9999' 03000000
WHERE CREATE_FROM IS NULL 03010000
AND CREATE_TO IS NULL 03020000
; 03030000
DECLARE GLOBAL TEMPORARY TABLE TEMP 03040000
( SNUM CHAR(0013) FOR BIT DATA 03050000
, CRITERIA CHAR(0030) 03060000
, FAMILY CHAR(0030) 03070000
, COMMENT CHAR(0020) 03080000
, OBJECT CHAR(0060) 03090000
, OPTVALUE VARCHAR(1024) 03100000
, CREATEDTS TIMESTAMP(6) 03110000
) 03120000
; 03130000
INSERT INTO SESSION.TEMP 03140000
WITH DBDDL ( FAMILY 03150000
, DATABASE 03160000
, BUFFERPOOL 03170000
, INDEXPOOL 03180000
, STOGROUP 03190000
, CCID 03200000
, DBID 03210000
, COMMENT 03220000
, CREATEDTS 03230000
) 03240000
AS (SELECT DISTINCT 03250000
EL.FAMILY 03260000
, DB.NAME 03270000
, DB.BPOOL 03280000
, DB.INDEXBP 03290000
, DB.STGROUP 03300000
, DB.ENCODING_SCHEME 03310000
, DB.DBID 03320000
, EL.COMMENT 03330000
, DB.CREATEDTS 03340000
FROM SESSION.ELAR EL 03350000
INNER JOIN SYSIBM.SYSDATABASE DB 03360000
ON EL.STORAR = DB.NAME 03370000
INNER JOIN SYSIBM.SYSTABLESPACE TS 03380000
ON DB.DBID = TS.DBID 03390000
WHERE DATE(DB.CREATEDTS) 03400000
>= (SELECT CREATE_FROM FROM SESSION.FAMLISTE) 03410000
AND DATE(DB.CREATEDTS) 03420000
<= (SELECT CREATE_TO FROM SESSION.FAMLISTE) 03430000
) 03440000
, RDB ( FAMILY 03450000
, COMMENT 03460000
, OBJECT 03470000
, OPTVALUE 03480000
, CREATEDTS 03490000
) 03500000
AS (SELECT DISTINCT 03510000
FAMILY 03520000
, COMMENT 03530000
, CAST(DATABASE AS CHAR(08)) 03540000
, 'Bufferpool_DB=' 03550000
|| CASE WHEN BUFFERPOOL = 'BP2' 03560000
THEN 'OK' 03570000
ELSE 'NOK' 03580000
END 03590000
|| ';' 03600000
|| 'Bufferpool_DB_IX=' 03610000
|| CASE WHEN INDEXPOOL = 'BP1' 03620000
THEN 'OK' 03630000
ELSE 'NOK' 03640000
END 03650000
|| ';' 03660000
|| 'StorageGroup=' 03670000
|| CASE STOGROUP 03680000
WHEN 'GSMS1' THEN 'OK' 03690000
-- WHEN 'GSMS2' THEN 'OK' 03700099
WHEN 'GSMS3' THEN 'OK' 03710000
WHEN 'GSMS4' THEN 'OK' 03720000
ELSE CASE WHEN SUBSTR(DATABASE,1,3) = 'DXB' 03730000
THEN CASE WHEN STOGROUP = 'GSMS' 03740000
THEN 'OK' 03750000
ELSE 'NOK' 03760000
END 03770000
ELSE 'NOK' 03780000
END 03790000
END 03800000
|| ';' 03810000
|| 'EncodingScheme=' 03820000
|| CASE WHEN CCID = 'E' 03830000
THEN 'OK' 03840000
ELSE 'NOK' 03850000
END 03860000
, CREATEDTS 03870000
FROM DBDDL 03880000
) 03890000
SELECT GENERATE_UNIQUE() 03900000
, '1Database Option Check' 03910000
, FAMILY 03920000
, COMMENT 03930000
, OBJECT 03940000
, OPTVALUE 03950000
, CREATEDTS 03960000
FROM RDB 03970000
ORDER BY FAMILY 03980000
, OBJECT 03990000
; 04000000
INSERT INTO SESSION.TEMP 04010000
WITH TSDDL ( FAMILY 04020000
, DATABASE 04030000
, BPOOL_T 04040000
, TSNAME 04050000
, PARTITION 04060000
, STOGROUP 04070000
, PRIQTY 04080000
, SECQTY 04090000
, GBPCACHE 04100000
, TRACKMODE 04110000
, LOGGED 04120000
, DSSIZE 04130000
, LOCKSIZE 04140000
, LOCKMAX 04150000
, CLOSE 04160000
, COMPRESS 04170000
, CCID 04180000
, DEFINE 04190000
, MAXROWS 04200000
, TPIXNAME 04210000
, AUXTBNAME 04220000
, FREEPAGE 04230000
, PCTFREE 04240000
, COMMENT 04250000
, CREATEDTS 04260000
) 04270000
AS (SELECT DISTINCT 04280000
EL.FAMILY AS FAMILY 04290000
, DB.NAME AS DATABASE 04300000
, TS.BPOOL AS BPOOL_T 04310000
, TS.NAME AS TSNAME 04320000
, TS.PARTITIONS AS PARTITION 04330000
, TP.STORNAME AS STOGROUP 04340000
, TP.PQTY AS PRIQTY 04350000
, TP.SQTY AS SECQTY 04360000
, TP.GBPCACHE AS GBPCACHE 04370000
, TP.TRACKMOD AS TRACKMODE 04380000
, TS.LOG AS LOGGED 04390000
, TS.DSSIZE AS DSSIZE 04400000
, TS.LOCKRULE AS LOCKSIZE 04410000
, TS.LOCKMAX AS LOCKMAX 04420000
, TS.CLOSERULE AS CLOSE 04430000
, TP.COMPRESS AS COMPRESS 04440000
, TS.ENCODING_SCHEME AS CCID 04450000
, TP.SPACE AS DEFINE 04460000
, TS.MAXROWS AS MAXROWS 04470000
, TP.IXNAME AS TPIXNAME 04480000
, TB.TYPE AS AUXTBNAME 04490000
, TP.FREEPAGE AS FREEPAGE 04500000
, TP.PCTFREE AS PCTFREE 04510000
, EL.COMMENT AS COMMENT 04520000
, TP.CREATEDTS AS CREATEDTS 04530000
FROM SESSION.ELAR EL 04540000
INNER JOIN SYSIBM.SYSDATABASE DB 04550000
ON EL.STORAR = DB.NAME 04560000
INNER JOIN SYSIBM.SYSTABLESPACE TS 04570000
ON DB.DBID = TS.DBID 04580000
INNER JOIN SYSIBM.SYSTABLEPART TP 04590000
ON TS.NAME = TP.TSNAME 04600000
AND TS.DBNAME = TP.DBNAME 04610000
INNER JOIN SYSIBM.SYSTABLES TB 04620000
ON TP.DBNAME = TB.DBNAME 04630000
AND TP.TSNAME = TB.TSNAME 04640000
WHERE DATE(TP.CREATEDTS) 04650000
>= (SELECT CREATE_FROM FROM SESSION.FAMLISTE) 04660000
AND DATE(TP.CREATEDTS) 04670000
<= (SELECT CREATE_TO FROM SESSION.FAMLISTE) 04680000
) 04690000
, RTS ( FAMILY 04700000
, COMMENT 04710000
, OBJECT 04720000
, OPTVALUE 04730000
, CREATEDTS 04740000
) 04750000
AS (SELECT DISTINCT 04760000
FAMILY 04770000
, COMMENT 04780000
, CAST(DATABASE AS CHAR(08)) 04790000
|| ' ' 04800000
|| TSNAME 04810000
|| ' ' 04820000
, CASE WHEN PARTITION > 0 04830000
THEN CASE WHEN LENGTH(STRIP(TPIXNAME)) = 0 04840000
THEN 'InfoTCP=OK' 04850000
ELSE 'InfoICP=OK' 04860000
END 04870000
ELSE 'InfoTSS=OK' 04880000
END 04890000
|| ';' 04900000
|| 'TablePartLimit=' 04910000
|| CASE WHEN (SELECT DISTINCT SUBSTR(DATABASE,1,2) 04920000
FROM TSDDL 04930000
) = 'XB' 04940000
THEN CASE WHEN PARTITION > (SELECT MAXPART 04950000
FROM SESSION.MAXPART 04960000
WHERE PARTYPE = 'Table' 04970000
) 04980000
THEN 'NOK' 04990000
ELSE 'OK' 05000000
END 05010000
ELSE 'OK' 05020000
END 05030000
|| ';' 05040000
|| 'Bufferpool' 05050000
|| CASE BPOOL_T 05060000
WHEN 'BP2' THEN 05070000
CASE WHEN SUBSTR(TSNAME,1,1) 05080000
|| SUBSTR( TSNAME 05090000
, (LENGTH(TSNAME)-1)05100000
, 1 05110000
) 05120000
= 'SH' 05130000
THEN '_SHS=NOK' 05140000
ELSE '=OK' 05150000
END 05160000
WHEN 'BP32K' THEN 05170000
CASE WHEN SUBSTR(TSNAME,1,1) 05180000
|| SUBSTR( TSNAME 05190000
, (LENGTH(TSNAME)-1)05200000
, 1 05210000
) 05220000
= 'SH' 05230000
THEN '=OK' 05240000
ELSE CASE WHEN LENGTH(TSNAME) = 8 05250000
THEN CASE WHEN SUBSTR(TSNAME,8,1)05260000
= 'H' 05270000
THEN '=OK' 05280000
ELSE '_SHS=NOK' 05290000
END 05300000
ELSE '_SHS=NOK' 05310000
END 05320000
END 05330000
WHEN 'BP8' THEN 05340000
CASE WHEN AUXTBNAME = 'X' 05350000
THEN '=OK' 05360000
ELSE '_LOB=NOK' 05370000
END 05380000
ELSE '_TS=NOK' 05390000
END 05400000
|| ';' 05410000
|| 'Groupbufferpool=' 05420000
|| CASE GBPCACHE 05430000
WHEN ' ' THEN 'OK' 05440000
ELSE 'NOK' 05450000
END 05460000
|| ';' 05470000
|| 'Trackmode=' 05480000
|| CASE TRACKMODE 05490000
WHEN 'N' THEN 'OK' 05500000
ELSE 'NOK' 05510000
END 05520000
|| ';' 05530000
|| 'LoggedOption=' 05540000
|| CASE LOGGED 05550000
WHEN 'Y' THEN 'OK' 05560000
ELSE 'NOK' 05570000
END 05580000
|| ';' 05590000
|| 'DSSIZE=' 05600000
|| CASE WHEN PARTITION > 0 05610000
THEN CASE DSSIZE 05620000
WHEN 16777216 THEN 'OK' 05630000
ELSE 'NOK' 05640000
END 05650000
ELSE 'OK' 05660000
END 05670000
|| ';' 05680000
|| 'Locksize=' 05690000
|| CASE LOCKSIZE 05700000
WHEN 'A' THEN 'OK' 05710000
ELSE 'NOK' 05720000
END 05730000
|| ';' 05740000
|| 'LockMax=' 05750000
|| CASE LOCKMAX 05760000
WHEN -1 THEN 'OK' 05770000
ELSE 'NOK' 05780000
END 05790000
|| ';' 05800000
|| 'CloseOption=' 05810000
|| CASE CLOSE 05820000
WHEN 'Y' THEN 'OK' 05830000
ELSE 'NOK' 05840000
END 05850000
|| ';' 05860000
|| 'TblCompressOption=' 05870000
|| CASE COMPRESS 05880000
WHEN 'Y' THEN 'OK' 05890000
ELSE 'NOK' 05900000
END 05910000
|| ';' 05920000
|| 'StorageGroup=' 05930000
|| CASE STOGROUP 05940000
WHEN 'GSMS1' THEN 'OK' 05950000
-- WHEN 'GSMS2' THEN 'OK' 05960099
WHEN 'GSMS3' THEN 'OK' 05970000
WHEN 'GSMS4' THEN 'OK' 05980000
ELSE CASE WHEN SUBSTR(DATABASE,1,3) = 'DXB' 05990000
THEN CASE WHEN STOGROUP = 'GSMS' 06000000
THEN 'OK' 06010000
ELSE 'NOK' 06020000
END 06030000
ELSE 'NOK' 06040000
END 06050000
END 06060000
|| ';' 06070000
|| 'PrimaryQuantity=' 06080000
|| CASE PRIQTY 06090000
WHEN -1 THEN 'OK' 06100000
ELSE 'NOK' 06110000
END 06120000
|| ';' 06130000
|| 'SecondaryQuantity=' 06140000
|| CASE SECQTY 06150000
WHEN -1 THEN 'OK' 06160000
ELSE 'NOK' 06170000
END 06180000
|| ';' 06190000
|| 'EncodingScheme=' 06200000
|| CASE WHEN CCID = 'E' THEN 'OK' 06210000
ELSE 'NOK' 06220000
END 06230000
|| ';' 06240000
|| 'DefineOption=' 06250000
|| CASE DEFINE 06260000
WHEN -1 THEN 'NOK' 06270000
ELSE 'OK' 06280000
END 06290000
|| ';' 06300000
|| 'MaxRowsPerPage=' 06310000
|| CASE MAXROWS 06320000
WHEN 255 THEN 'OK' 06330000
ELSE 'NOK' 06340000
END 06350000
|| ';' 06360000
|| 'TSFreepage=' 06370000
|| CASE FREEPAGE 06380000
WHEN 0 THEN 'OK' 06390000
ELSE 'NOK' 06400000
END 06410000
|| ';' 06420000
|| 'TSPctfree=' 06430000
|| CASE PCTFREE 06440000
WHEN 0 THEN 'OK' 06450000
ELSE 'NOK' 06460000
END 06470000
, CREATEDTS 06480000
FROM TSDDL 06490000
) 06500000
SELECT GENERATE_UNIQUE() 06510000
, '2Tablespace Option Check' 06520000
, FAMILY 06530000
, COMMENT 06540000
, OBJECT 06550000
, OPTVALUE 06560000
, CREATEDTS 06570000
FROM RTS 06580000
ORDER BY FAMILY 06590000
, OBJECT 06600000
; 06610000
INSERT INTO SESSION.TEMP 06620000
WITH TBDDL ( FAMILY 06630000
, DATABASE 06640000
, TSNAME 06650000
, TBNAME 06660000
, AUDIT 06670000
, DATA_CAPTURE 06680000
, RESTRICT_DROP 06690000
, CCID 06700000
, VOLATILE 06710000
, APPEND 06720000
, PART_NAME 06730000
, PART_TYPE 06740000
, PART_SEQ 06750000
, PART_ORDER 06760000
, PARTITION 06770000
, PART_LIMITKEY 06780000
, PART_LOGICALKEY 06790000
, COLNO 06800000
, PARTKEYCOLNUM 06810000
, COMMENT 06820000
, CREATEDTS 06830000
) 06840000
AS (SELECT EL.FAMILY 06850000
, CAST(TB.DBNAME AS CHAR(08)) 06860000
, TB.TSNAME 06870000
, TB.NAME 06880000
, TB.AUDITING 06890000
, TB.DATACAPTURE 06900000
, TB.CLUSTERTYPE 06910000
, TB.ENCODING_SCHEME 06920000
, TB.SPLIT_ROWS 06930000
, TB.APPEND 06940000
, TC.NAME 06950000
, TC.COLTYPE 06960000
, TC.PARTKEY_COLSEQ 06970000
, CAST(CASE WHEN TS.PARTITIONS = 0 06980000
THEN ' ' 06990000
ELSE TC.PARTKEY_ORDERING 07000000
END AS CHAR(01)) 07010000
, CAST(TP.PARTITION AS INTEGER) 07020000
, CAST(CASE WHEN TS.PARTITIONS = 0 07030000
THEN '0' 07040000
ELSE STRIP(TP.LIMITKEY) 07050000
END AS VARCHAR(765)) 07060000
, CAST(CASE WHEN TS.PARTITIONS = 0 07070000
THEN 0 07080000
ELSE TP.LOGICAL_PART 07090000
END AS INTEGER) 07100000
, COLNO 07110000
, TB.PARTKEYCOLNUM 07120000
, EL.COMMENT 07130000
, TP.CREATEDTS 07140000
FROM SESSION.ELAR EL 07150000
INNER JOIN SYSIBM.SYSDATABASE DB 07160000
ON EL.STORAR = DB.NAME 07170000
INNER JOIN SYSIBM.SYSTABLES TB 07180000
ON TB.DBNAME = DB.NAME 07190000
AND TB.TYPE = 'T' 07200000
INNER JOIN SYSIBM.SYSCOLUMNS TC 07210000
ON TB.NAME = TC.TBNAME 07220000
AND TB.CREATOR = TC.TBCREATOR 07230000
INNER JOIN SYSIBM.SYSTABLESPACE TS 07240000
ON TB.TSNAME = TS.NAME 07250000
AND TB.DBID = TS.DBID 07260000
AND TS.DBID = DB.DBID 07270000
INNER JOIN SYSIBM.SYSTABLEPART TP 07280000
ON TS.NAME = TP.TSNAME 07290000
AND TS.DBNAME = TP.DBNAME 07300000
WHERE TC.PARTKEY_COLSEQ > 0 07310000
AND DATE(TP.CREATEDTS) 07320000
>= (SELECT CREATE_FROM FROM SESSION.FAMLISTE) 07330000
AND DATE(TP.CREATEDTS) 07340000
<= (SELECT CREATE_TO FROM SESSION.FAMLISTE) 07350000
ORDER BY TB.DBNAME, TB.TSNAME 07360000
, TC.COLNO , TC.PARTKEY_COLSEQ 07370000
) 07380000
, RTB ( FAMILY 07390000
, COMMENT 07400000
, OBJECT 07410000
, OPTVALUE 07420000
, CREATEDTS 07430000
) 07440000
AS (SELECT DISTINCT 07450000
FAMILY 07460000
, COMMENT 07470000
, CAST(DATABASE AS CHAR(08)) 07480000
|| ' ' 07490000
|| TSNAME 07500000
|| ' ' 07510000
|| TBNAME 07520000
, 'Audit=' 07530000
|| CASE AUDIT 07540000
WHEN ' ' THEN 'OK' 07550000
ELSE 'NOK' 07560000
END 07570000
|| ';' 07580000
|| 'DataCapture=' 07590000
|| CASE DATA_CAPTURE 07600000
WHEN ' ' THEN 'OK' 07610000
ELSE 'NOK' 07620000
END 07630000
|| ';' 07640000
|| 'RestrictOnDrop=' 07650000
|| CASE RESTRICT_DROP 07660000
WHEN 'Y' THEN 'OK' 07670000
ELSE 'NOK' 07680000
END 07690000
|| ';' 07700000
|| 'EncodingScheme=' 07710000
|| CASE CCID 07720000
WHEN 'E' THEN 'OK' 07730000
ELSE 'NOK' 07740000
END 07750000
|| ';' 07760000
|| 'Volatile=' 07770000
|| CASE VOLATILE 07780000
WHEN 'Y' THEN 'OK' 07790000
ELSE 'NOK' 07800000
END 07810000
|| ';' 07820000
|| 'Append=' 07830000
|| CASE APPEND 07840000
WHEN 'Y' THEN 'OK' 07850000
ELSE 'NOK' 07860000
END 07870000
|| ';' 07880000
|| 'PartKeyName=' 07890000
|| CASE WHEN SUBSTR(DATABASE,1,2) = 'XB' 07900000
THEN CASE WHEN PART_SEQ > 0 07910000
THEN CASE PART_NAME 07920000
WHEN 'EN1PART' THEN 'OK' 07930000
ELSE 'NOK' 07940000
END 07950000
ELSE 'NOK' 07960000
END 07970000
ELSE 'OK' 07980000
END 07990000
|| ';' 08000000
|| 'PartKeyType=' 08010000
|| CASE WHEN SUBSTR(DATABASE,1,2) = 'XB' 08020000
THEN CASE WHEN PART_SEQ > 0 08030000
THEN CASE PART_TYPE 08040000
WHEN 'INTEGER' THEN 'OK' 08050000
ELSE 'NOK' 08060000
END 08070000
ELSE 'NOK' 08080000
END 08090000
ELSE 'OK' 08100000
END 08110000
|| ';' 08120000
|| 'PartKeyPosition=' 08130000
|| CASE WHEN SUBSTR(DATABASE,1,2) = 'XB' 08140000
THEN CASE WHEN PART_SEQ > 0 08150000
THEN CASE PART_SEQ 08160000
WHEN 1 THEN 'OK' 08170000
ELSE 'NOK' 08180000
END 08190000
ELSE 'NOK' 08200000
END 08210000
ELSE 'OK' 08220000
END 08230000
|| ';' 08240000
|| 'PartKeyOrder=' 08250000
|| CASE WHEN SUBSTR(DATABASE,1,2) = 'XB' 08260000
THEN CASE WHEN PART_SEQ > 0 08270000
THEN CASE PART_ORDER 08280000
WHEN 'A' THEN 'OK' 08290000
ELSE 'NOK' 08300000
END 08310000
ELSE 'NOK' 08320000
END 08330000
ELSE 'OK' 08340000
END 08350000
|| ';' 08360000
|| 'PartKeyValue(s)=' 08370000
|| CASE 08380000
WHEN SUBSTR(DATABASE,1,2) = 'XB' 08390000
THEN CASE 08400000
WHEN PART_SEQ > 0 08410000
THEN CASE 08420000
WHEN CHAR(PARTITION) = PART_LIMITKEY 08430000
THEN CASE 08440000
WHEN PART_LIMITKEY 08450000
= CHAR(PART_LOGICALKEY) 08460000
THEN 'OK' 08470000
ELSE 'NOK' 08480000
END 08490000
ELSE 'NOK' 08500000
END 08510000
ELSE 'NOK' 08520000
END 08530000
ELSE 'OK' 08540000
END 08550000
|| ';' 08560000
|| 'PartKeyColNum=' 08570000
|| CASE WHEN SUBSTR(DATABASE,1,2) = 'XB' 08580000
THEN CASE WHEN PARTKEYCOLNUM = 1 08590000
THEN 'OK' 08600000
ELSE 'NOK' 08610000
END 08620000
ELSE 'OK' 08630000
END 08640000
AS OPTVALUE 08650000
, CREATEDTS 08660000
FROM TBDDL 08670000
) 08680000
SELECT GENERATE_UNIQUE() 08690000
, '3Table Option Check' 08700000
, FAMILY 08710000
, COMMENT 08720000
, OBJECT 08730000
, OPTVALUE 08740000
, CREATEDTS 08750000
FROM RTB 08760000
ORDER BY FAMILY 08770000
, OBJECT 08780000
; 08790000
INSERT INTO SESSION.TEMP 08800000
WITH XSTMP ( FAMILY 08810000
, DATABASE 08820000
, TBNAME 08830000
, IXNAME 08840000
, IXCREATOR 08850000
, BPOOL_I 08860000
, INDEXTYPE 08870000
, PARTITION 08880000
, STOGROUP 08890000
, PRIQTY 08900000
, SECQTY 08910000
, GBPCACHE 08920000
, PGSIZE 08930000
, FREEPAGE 08940000
, PCTFREE 08950000
, COMPRESS 08960000
, CLOSE 08970000
, COPY 08980000
, DEFINE 08990000
, CLUSTERING 09000000
, TSPARTITION 09010000
, TPIXNAME 09020000
, PIECESIZE 09030000
, TSPGSIZE 09040000
, COMMENT 09050000
, CREATEDTS 09060000
) 09070000
AS (SELECT DISTINCT 09080000
EL.FAMILY 09090000
, DB.NAME 09100000
, IX.TBNAME 09110000
, IX.NAME 09120000
, IX.CREATOR 09130000
, IX.BPOOL 09140000
, IX.INDEXTYPE 09150000
, (SELECT MAX(TMP.PARTITION) 09160000
FROM SYSIBM.SYSINDEXPART TMP 09170000
WHERE TMP.IXNAME = IX.NAME 09180000
AND TMP.IXCREATOR = IX.CREATOR 09190000
) 09200000
, XP.STORNAME 09210000
, XP.PQTY 09220000
, XP.SQTY 09230000
, XP.GBPCACHE 09240000
, IX.PGSIZE 09250000
, XP.FREEPAGE 09260000
, XP.PCTFREE 09270000
, IX.COMPRESS 09280000
, IX.CLOSERULE 09290000
, IX.COPY 09300000
, XP.SPACE 09310000
, IX.CLUSTERING 09320000
, (SELECT TMP.PARTITIONS 09330000
FROM SYSIBM.SYSTABLESPACE TMP 09340000
WHERE TMP.DBID = DB.DBID 09350000
AND TMP.DBID = IX.DBID 09360000
AND TMP.NAME = TB.TSNAME 09370000
) 09380000
, (SELECT TMP.IXNAME 09390000
FROM SYSIBM.SYSTABLEPART TMP 09400000
WHERE TMP.TSNAME = TS.NAME 09410000
AND TMP.DBNAME = TS.DBNAME 09420000
FETCH FIRST 1 ROW ONLY 09430000
) 09440000
, IX.PIECESIZE 09450000
, TS.PGSIZE 09460000
, EL.COMMENT 09470000
, XP.CREATEDTS 09480000
FROM SESSION.ELAR EL 09490000
INNER JOIN SYSIBM.SYSDATABASE DB 09500000
ON EL.STORAR = DB.NAME 09510000
INNER JOIN SYSIBM.SYSINDEXES IX 09520000
ON DB.DBID = IX.DBID 09530000
INNER JOIN SYSIBM.SYSTABLES TB 09540000
ON IX.TBNAME = TB.NAME 09550000
AND IX.DBNAME = TB.DBNAME 09560000
AND IX.DBID = TB.DBID 09570000
AND TB.DBID = DB.DBID 09580000
INNER JOIN SYSIBM.SYSTABLESPACE TS 09590000
ON TB.TSNAME = TS.NAME 09600000
AND TS.DBID = TB.DBID 09610000
AND TS.DBID = IX.DBID 09620000
AND TS.DBID = DB.DBID 09630000
INNER JOIN SYSIBM.SYSINDEXPART XP 09640000
ON IX.NAME = XP.IXNAME 09650000
AND IX.CREATOR = XP.IXCREATOR 09660000
WHERE DATE(XP.CREATEDTS) 09670000
>= (SELECT CREATE_FROM FROM SESSION.FAMLISTE) 09680000
AND DATE(XP.CREATEDTS) 09690000
<= (SELECT CREATE_TO FROM SESSION.FAMLISTE) 09700000
) 09710000
, IXDDL ( FAMILY 09720000
, DATABASE 09730000
, TBNAME 09740000
, IXNAME 09750000
, BPOOL_I 09760000
, INDEXTYPE 09770000
, PARTITION 09780000
, STOGROUP 09790000
, PRIQTY 09800000
, SECQTY 09810000
, GBPCACHE 09820000
, PGSIZE 09830000
, FREEPAGE 09840000
, PCTFREE 09850000
, COMPRESS 09860000
, CLOSE 09870000
, COPY 09880000
, DEFINE 09890000
, CLUSTERING 09900000
, CNT_KEYS 09910000
, CLUSTERKEY 09920000
, TSPARTITION 09930000
, TPIXNAME 09940000
, PIECESIZE 09950000
, TSPGSIZE 09960000
, IXTECHBUSINESS 09970000
, COMMENT 09980000
, CREATEDTS 09990000
) 10000000
AS (SELECT T1.FAMILY 10010000
, T1.DATABASE 10020000
, T1.TBNAME 10030000
, T1.IXNAME 10040000
, T1.BPOOL_I 10050000
, T1.INDEXTYPE 10060000
, T1.PARTITION 10070000
, T1.STOGROUP 10080000
, T1.PRIQTY 10090000
, T1.SECQTY 10100000
, T1.GBPCACHE 10110000
, T1.PGSIZE 10120000
, T1.FREEPAGE 10130000
, T1.PCTFREE 10140000
, T1.COMPRESS 10150000
, T1.CLOSE 10160000
, T1.COPY 10170000
, T1.DEFINE 10180000
, T1.CLUSTERING 10190000
, (SELECT MAX(SK.COLSEQ) 10200000
FROM SYSIBM.SYSKEYS SK 10210000
WHERE T1.IXCREATOR = SK.IXCREATOR 10220000
AND T1.IXNAME = SK.IXNAME 10230000
AND T1.CLUSTERING = 'Y' 10240000
AND SK.COLSEQ = 1 10250000
) AS CNT_KEYS 10260000
, (SELECT SK.COLNAME 10270000
FROM SYSIBM.SYSKEYS SK 10280000
WHERE T1.IXCREATOR = SK.IXCREATOR 10290000
AND T1.IXNAME = SK.IXNAME 10300000
AND T1.CLUSTERING = 'Y' 10310000
AND SK.COLSEQ = 1 10320000
) AS CLUSTERKEY 10330000
, T1.TSPARTITION 10340000
, T1.TPIXNAME 10350000
, T1.PIECESIZE 10360000
, T1.TSPGSIZE 10370000
, (SELECT COUNT(*) 10380000
FROM SYSIBM.SYSKEYS IK 10390000
WHERE IK.IXNAME = T1.IXNAME 10400000
AND ( IK.COLNAME LIKE '%ATRN%' 10410000
OR IK.COLNAME LIKE '%STRN%' 10420000
) 10430000
) AS IXTECHBUSINESS 10440000
, T1.COMMENT 10450000
, T1.CREATEDTS 10460000
FROM XSTMP T1 10470000
) 10480000
, RXS ( FAMILY 10490000
, COMMENT 10500000
, OBJECT 10510000
, OPTVALUE 10520000
, CREATEDTS 10530000
) 10540000
AS (SELECT DISTINCT 10550000
FAMILY 10560000
, COMMENT 10570000
, CAST(DATABASE AS CHAR(08)) 10580000
|| ' ' 10590000
|| IXNAME 10600000
|| ' ' 10610000
|| TBNAME 10620000
, 'NoIndexExists=' 10630000
|| CASE WHEN LENGTH(STRIP(IXNAME)) = 0 10640000
THEN 'NOK' 10650000
ELSE 'OK' 10660000
END 10670000
|| ';' 10680000
|| 'NPI=' 10690000
|| CASE WHEN PARTITION + TSPARTITION = 0 10700000
THEN 'OK' 10710000
ELSE CASE WHEN TSPARTITION > 0 AND PIECESIZE = 0 10720000
THEN 'OK' 10730000
ELSE CASE WHEN PARTITION > 0 AND 10740000
PIECESIZE > 0 10750000
THEN 'OK' 10760000
ELSE 'NOK' 10770000
END 10780000
END 10790000
END 10800000
|| ';' 10810000
|| 'Indextype=' 10820000
|| CASE INDEXTYPE 10830000
WHEN 'D' then case when tspartition > 0 10840000
then 'OK' 10850000
else 'NOK' 10860000
end 10870000
WHEN 'P' then case when tspartition > 0 10880000
then 'OK' 10890000
else 'NOK' 10900000
end 10910000
WHEN ' ' then case when tspartition > 0 10920000
then 'NOK' 10930000
else 'OK' 10940000
end 10950000
WHEN '2' then case when tspartition > 0 10960000
then 'NOK' 10970000
else 'OK' 10980000
end 10990000
ELSE 'NOK' 11000000
END 11010000
|| ';' 11020000
|| 'IndexPartLimit=' 11030000
|| CASE WHEN PARTITION > (SELECT MAXPART 11040000
FROM SESSION.MAXPART 11050000
WHERE PARTYPE = 'Index' 11060000
) 11070000
THEN 'NOK' 11080000
ELSE 'OK' 11090000
END 11100000
|| ';' 11110000
|| 'Bufferpool=' 11120000
|| CASE BPOOL_I 11130000
WHEN 'BP1' THEN 'OK' 11140000
ELSE 'NOK' 11150000
END 11160000
|| ';' 11170000
|| 'GroupBufferPool=' 11180000
|| CASE GBPCACHE 11190000
WHEN ' ' THEN 'OK' 11200000
ELSE 'NOK' 11210000
END 11220000
|| ';' 11230000
|| 'Pagesize=' 11240000
|| CASE WHEN TSPGSIZE = PGSIZE 11250000
THEN 'OK' 11260000
ELSE 'OK' 11270000
END 11280000
|| ';' 11290000
|| 'CloseOption=' 11300000
|| CASE CLOSE 11310000
WHEN 'Y' THEN 'OK' 11320000
ELSE 'NOK' 11330000
END 11340000
|| ';' 11350000
|| 'IdxCompressOption=' 11360000
|| CASE COMPRESS 11370000
WHEN 'N' THEN 'OK' 11380000
ELSE 'NOK' 11390000
END 11400000
|| ';' 11410000
|| 'StorageGroup=' 11420000
|| CASE STOGROUP 11430000
WHEN 'GSMS1' THEN 'OK' 11440000
-- WHEN 'GSMS2' THEN 'OK' 11450099
WHEN 'GSMS3' THEN 'OK' 11460000
WHEN 'GSMS4' THEN 'OK' 11470000
ELSE CASE WHEN SUBSTR(DATABASE,1,3) = 'DXB' 11480000
THEN CASE WHEN STOGROUP = 'GSMS' 11490000
THEN 'OK' 11500000
ELSE 'NOK' 11510000
END 11520000
ELSE 'NOK' 11530000
END 11540000
END 11550000
|| ';' 11560000
|| 'PrimaryQuantity=' 11570000
|| CASE PRIQTY 11580000
WHEN -1 THEN 'OK' 11590000
ELSE 'NOK' 11600000
END 11610000
|| ';' 11620000
|| 'SecondaryQuantity=' 11630000
|| CASE SECQTY 11640000
WHEN -1 THEN 'OK' 11650000
ELSE 'NOK' 11660000
END 11670000
|| ';' 11680000
|| 'Define=' 11690000
|| CASE DEFINE 11700000
WHEN -1 THEN 'NOK' 11710000
ELSE 'OK' 11720000
END 11730000
|| ';' 11740000
|| 'FreePage' 11750000
|| CASE FREEPAGE 11760000
WHEN 0 THEN '=OK' 11770000
ELSE '=NOK' 11780000
END 11790000
|| ';' 11800000
|| 'PctFree' 11810000
|| CASE WHEN SUBSTR(DATABASE,1,3) = 'DXB' 11820000
THEN '_Metadata=OK' 11830000
ELSE 11840000
CASE WHEN IXTECHBUSINESS > 0 11850000
THEN CASE PCTFREE 11860000
WHEN 0 THEN '_Technical=OK' 11870000
ELSE '_Technical=NOK' 11880000
END 11890000
ELSE CASE PCTFREE 11900000
WHEN 10 THEN '_Business=OK' 11910000
ELSE '_Business=NOK' 11920000
END 11930000
END 11940000
END 11950000
|| ';' 11960000
|| 'Copy=' 11970000
|| CASE COPY 11980000
WHEN 'N' THEN 'OK' 11990000
ELSE 'NOK' 12000000
END 12010000
, CREATEDTS 12020000
FROM IXDDL 12030000
) 12040000
SELECT GENERATE_UNIQUE() 12050000
, '4Indexspace Option Check' 12060000
, FAMILY 12070000
, COMMENT 12080000
, OBJECT 12090000
, OPTVALUE 12100000
, CREATEDTS 12110000
FROM RXS 12120000
ORDER BY FAMILY 12130000
, OBJECT 12140000
; 12150000
INSERT INTO SESSION.TEMP 12160000
WITH IXTYPE_FAILURE ( INDEXTYPE 12170000
) 12180000
AS ( SELECT 'C' 12190000
FROM SYSIBM.SYSDUMMY1 12200000
UNION ALL 12210000
SELECT 'N' 12220000
FROM SYSIBM.SYSDUMMY1 12230000
UNION ALL 12240000
SELECT 'R' 12250000
FROM SYSIBM.SYSDUMMY1 12260000
UNION ALL 12270000
SELECT 'G' 12280000
FROM SYSIBM.SYSDUMMY1 12290000
UNION ALL 12300000
SELECT 'X' 12310000
FROM SYSIBM.SYSDUMMY1 12320000
) 12330000
, IXTYPE_UNIQUE ( INDEXTYPE 12340000
) 12350000
AS ( SELECT 'P' 12360000
FROM SYSIBM.SYSDUMMY1 12370000
UNION ALL 12380000
SELECT 'U' 12390000
FROM SYSIBM.SYSDUMMY1 12400000
) 12410000
, IXTYPE ( INDEXTYPE 12420000
) 12430000
AS ( SELECT 'C' 12440000
FROM SYSIBM.SYSDUMMY1 12450000
UNION ALL 12460000
SELECT 'D' 12470000
FROM SYSIBM.SYSDUMMY1 12480000
UNION ALL 12490000
SELECT 'U' 12500000
FROM SYSIBM.SYSDUMMY1 12510000
UNION ALL 12520000
SELECT 'P' 12530000
FROM SYSIBM.SYSDUMMY1 12540000
UNION ALL 12550000
SELECT 'N' 12560000
FROM SYSIBM.SYSDUMMY1 12570000
UNION ALL 12580000
SELECT 'R' 12590000
FROM SYSIBM.SYSDUMMY1 12600000
UNION ALL 12610000
SELECT 'G' 12620000
FROM SYSIBM.SYSDUMMY1 12630000
UNION ALL 12640000
SELECT 'X' 12650000
FROM SYSIBM.SYSDUMMY1 12660000
) 12670000
, IXLST ( FAMILY 12680000
, DATABASE 12690000
, TBNAME 12700000
, IXNAME 12710000
, IXCREATOR 12720000
, ELAR_TYPE 12730000
, ELAR_ORDER 12740000
, UNIQUERULE 12750000
, CLUSTERING 12760000
, MAXCOL 12770000
, EN1PART 12780000
, EA1ATRN 12790000
, EA1STRN 12800000
, EN1STK1 12810000
, EN1STK2 12820000
, EATRNVON 12830000
, EATRNBIS 12840000
, COMMENT 12850000
, CREATEDTS 12860000
) 12870000
AS (SELECT DISTINCT 12880000
EL.FAMILY 12890000
, DB.NAME 12900000
, TB.NAME 12910000
, IFNULL(IX.NAME,'NOK') 12920000
, IX.CREATOR 12930000
, CAST(CASE SUBSTR(TB.NAME,9,2) 12940000
WHEN 'HS' THEN 'SIGNATUR' 12950000
WHEN 'IT' THEN 'BODY ' 12960000
WHEN 'PM' THEN 'MULTIPLE' 12970000
ELSE CASE SUBSTR(TB.NAME,9,5) 12980000
WHEN 'PS001' THEN 'AOP ' 12990000
WHEN 'PS002' THEN 'SEP ' 13000000
WHEN 'PS003' THEN 'SEP_2 ' 13010000
ELSE '??????? ' 13020000
END 13030000
END 13040000
AS CHAR(08) 13050000
) 13060000
, CAST(CASE SUBSTR(TB.NAME,9,2) 13070000
WHEN 'HS' THEN '6' 13080000
WHEN 'IT' THEN '4' 13090000
WHEN 'PM' THEN '5' 13100000
ELSE CASE SUBSTR(TB.NAME,9,5) 13110000
WHEN 'PS001' THEN '1' 13120000
WHEN 'PS002' THEN '2' 13130000
WHEN 'PS003' THEN '3' 13140000
ELSE '7' 13150000
END 13160000
END 13170000
AS CHAR(01) 13180000
) 13190000
, IFNULL(IX.UNIQUERULE,'NOK') AS UNIQUERULE 13200000
, IFNULL(IX.CLUSTERING,'NOK') AS CLUSTERING 13210000
, (SELECT COUNT(*) 13220000
FROM SYSIBM.SYSKEYS SK 13230000
WHERE IX.NAME = SK.IXNAME 13240000
AND IX.CREATOR = SK.IXCREATOR 13250000
) AS MAXCOL 13260000
, IFNULL((SELECT SK.COLSEQ 13270000
FROM SYSIBM.SYSKEYS SK 13280000
WHERE IX.NAME = SK.IXNAME 13290000
AND IX.CREATOR = SK.IXCREATOR 13300000
AND SK.COLNAME = 'EN1PART' 13310000
), -1) AS EN1PART 13320000
, IFNULL((SELECT SK.COLSEQ 13330000
FROM SYSIBM.SYSKEYS SK 13340000
WHERE IX.NAME = SK.IXNAME 13350000
AND IX.CREATOR = SK.IXCREATOR 13360000
AND SK.COLNAME = 'EA1ATRN' 13370000
), -1) AS EA1ATRN 13380000
, IFNULL((SELECT SK.COLSEQ 13390000
FROM SYSIBM.SYSKEYS SK 13400000
WHERE IX.NAME = SK.IXNAME 13410000
AND IX.CREATOR = SK.IXCREATOR 13420000
AND SK.COLNAME = 'EA1STRN' 13430000
), -1) AS EA1STRN 13440000
, IFNULL((SELECT SK.COLSEQ 13450000
FROM SYSIBM.SYSKEYS SK 13460000
WHERE IX.NAME = SK.IXNAME 13470000
AND IX.CREATOR = SK.IXCREATOR 13480000
AND SK.COLNAME = 'EN1STK1' 13490000
), -1) AS EN1STK1 13500000
, IFNULL((SELECT SK.COLSEQ 13510000
FROM SYSIBM.SYSKEYS SK 13520000
WHERE IX.NAME = SK.IXNAME 13530000
AND IX.CREATOR = SK.IXCREATOR 13540000
AND SK.COLNAME = 'EN1STK2' 13550000
), -1) AS EN1STK2 13560000
, IFNULL((SELECT SK.COLSEQ 13570000
FROM SYSIBM.SYSKEYS SK 13580000
WHERE IX.NAME = SK.IXNAME 13590000
AND IX.CREATOR = SK.IXCREATOR 13600000
AND SK.COLNAME = 'EATRNVON' 13610000
), -1) AS EATRNVON 13620000
, IFNULL((SELECT SK.COLSEQ 13630000
FROM SYSIBM.SYSKEYS SK 13640000
WHERE IX.NAME = SK.IXNAME 13650000
AND IX.CREATOR = SK.IXCREATOR 13660000
AND SK.COLNAME = 'EATRNBIS' 13670000
), -1) AS EATRNBIS 13680000
, EL.COMMENT 13690000
, IFNULL(IX.CREATEDTS, '0001-01-01-00.00.00.000000') 13700000
FROM SESSION.ELAR EL 13710000
INNER JOIN SYSIBM.SYSDATABASE DB 13720000
ON EL.STORAR = DB.NAME 13730000
INNER JOIN SYSIBM.SYSTABLES TB 13740000
ON TB.DBNAME = DB.NAME 13750000
AND TB.TYPE = 'T' 13760000
INNER JOIN SYSIBM.SYSTABLESPACE TS 13770000
ON TB.TSNAME = TS.NAME 13780000
AND TB.DBID = TS.DBID 13790000
AND TS.DBID = DB.DBID 13800000
LEFT OUTER JOIN SYSIBM.SYSINDEXES IX 13810000
ON IX.TBNAME = TB.NAME 13820000
LEFT OUTER JOIN IXTYPE TY 13830000
ON IX.INDEXTYPE = TY.INDEXTYPE 13840000
) 13850000
, RXI ( FAMILY 13860000
, COMMENT 13870000
, OBJECT 13880000
, OPTVALUE 13890000
, CREATEDTS 13900000
) 13910000
AS (SELECT FAMILY 13920000
, COMMENT 13930000
, CAST(DATABASE AS CHAR(08)) 13940000
|| ' ' 13950000
|| TBNAME 13960000
|| ' ' 13970000
, 'No' 13980000
|| ' ' 13990000
|| 'Index' 14000000
|| ' ' 14010000
|| 'exists=NOK' 14020000
|| ';' 14030000
, CREATEDTS 14040000
FROM IXLST 14050000
WHERE UNIQUERULE = 'NOK' 14060000
UNION ALL 14070000
SELECT FAMILY 14080000
, COMMENT 14090000
, CAST(DATABASE AS CHAR(08)) 14100000
|| ' ' 14110000
|| IXNAME 14120000
|| ' ' 14130000
|| TBNAME 14140000
|| ' ' 14150000
|| 'IndexType_Failure' 14160000
, 'IndexType_Failure=NOK' 14170000
|| ';' 14180000
, CREATEDTS 14190000
FROM IXLST 14200000
WHERE UNIQUERULE IN (SELECT INDEXTYPE 14210000
FROM IXTYPE_FAILURE 14220000
) 14230000
UNION ALL 14240000
SELECT FAMILY 14250000
, COMMENT 14260000
, CAST(DATABASE AS CHAR(08)) 14270000
|| ' ' 14280000
|| IXNAME 14290000
|| ' ' 14300000
|| TBNAME 14310000
|| ' ' 14320000
|| 'IndexRule=Unique' 14330000
, 'EN1PART_MIS=' 14340000
|| CASE WHEN EN1PART = -1 14350000
THEN 'NOK' 14360000
ELSE 'OK' 14370000
END 14380000
|| ';' 14390000
|| 'EN1PART_WPOS=' 14400000
|| CASE WHEN EN1PART = 2 14410000
THEN 'OK' 14420000
ELSE 'NOK' 14430000
END 14440000
|| ';' 14450000
|| 'EA1ARTN_MIS=' 14460000
|| CASE WHEN EA1ATRN = -1 14470000
THEN 'NOK' 14480000
ELSE 'OK' 14490000
END 14500000
|| ';' 14510000
|| 'EA1ARTN_WPOS=' 14520000
|| CASE WHEN EA1ATRN = 1 14530000
THEN 'OK' 14540000
ELSE 'NOK' 14550000
END 14560000
|| ';' 14570000
|| 'CLUSTER_DEF=' 14580000
|| CASE CLUSTERING 14590000
WHEN 'Y' THEN 'OK' 14600000
ELSE 'NOK' 14610000
END 14620000
|| ';' 14630000
|| 'TooManyCols=' 14640000
|| CASE MAXCOL 14650000
WHEN 2 THEN 'OK' 14660000
ELSE 'NOK' 14670000
END 14680000
|| ';' 14690000
, CREATEDTS 14700000
FROM IXLST 14710000
WHERE ELAR_TYPE = 'AOP' 14720000
AND UNIQUERULE IN (SELECT INDEXTYPE 14730000
FROM IXTYPE_UNIQUE 14740000
) 14750000
AND SUBSTR(DATABASE,1,2) = 'XB' 14760000
UNION ALL 14770000
SELECT FAMILY 14780000
, COMMENT 14790000
, CAST(DATABASE AS CHAR(08)) 14800000
|| ' ' 14810000
|| IXNAME 14820000
|| ' ' 14830000
|| TBNAME 14840000
|| ' ' 14850000
|| 'IndexRule=Unique' 14860000
, 'EN1PART_MIS=' 14870000
|| CASE WHEN EN1PART = -1 14880000
THEN 'NOK' 14890000
ELSE 'OK' 14900000
END 14910000
|| ';' 14920000
|| 'EA1SRTN_MIS=' 14930000
|| CASE WHEN EA1STRN = -1 14940000
THEN 'NOK' 14950000
ELSE 'OK' 14960000
END 14970000
|| ';' 14980000
|| 'EN1PART_WPOS=' 14990000
|| CASE WHEN EN1PART = 2 15000000
THEN 'OK' 15010000
ELSE 'NOK' 15020000
END 15030000
|| ';' 15040000
|| 'EA1SRTN_WPOS=' 15050000
|| CASE WHEN EA1STRN = 1 15060000
THEN 'OK' 15070000
ELSE 'NOK' 15080000
END 15090000
|| ';' 15100000
|| 'CLUSTER_DEF=' 15110000
|| CASE CLUSTERING 15120000
WHEN 'Y' THEN 'OK' 15130000
ELSE 'NOK' 15140000
END 15150000
|| ';' 15160000
|| 'TooManyCols=' 15170000
|| CASE MAXCOL 15180000
WHEN 2 THEN 'OK' 15190000
ELSE 'NOK' 15200000
END 15210000
|| ';' 15220000
, CREATEDTS 15230000
FROM IXLST 15240000
WHERE ELAR_TYPE IN ('SEP', 'SEP_2') 15250000
AND UNIQUERULE IN (SELECT INDEXTYPE 15260000
FROM IXTYPE_UNIQUE 15270000
) 15280000
AND SUBSTR(DATABASE,1,2) = 'XB' 15290000
UNION ALL 15300000
SELECT FAMILY 15310000
, COMMENT 15320000
, CAST(DATABASE AS CHAR(08)) 15330000
|| ' ' 15340000
|| IXNAME 15350000
|| ' ' 15360000
|| TBNAME 15370000
|| ' ' 15380000
|| 'IndexRule=Unique' 15390000
, 'EN1PART_MIS=' 15400000
|| CASE WHEN EN1PART = -1 15410000
THEN 'NOK' 15420000
ELSE 'OK' 15430000
END 15440000
|| ';' 15450000
|| 'EA1STRN_MIS=' 15460000
|| CASE WHEN EA1STRN = -1 15470000
THEN 'NOK' 15480000
ELSE 'OK' 15490000
END 15500000
|| ';' 15510000
|| 'EN1STK1_MIS=' 15520000
|| CASE WHEN EN1STK1 = -1 15530000
THEN 'NOK' 15540000
ELSE 'OK' 15550000
END 15560000
|| ';' 15570000
|| 'EN1STK2_MIS=' 15580000
|| CASE WHEN EN1STK2 = -1 15590000
THEN 'NOK' 15600000
ELSE 'OK' 15610000
END 15620000
|| ';' 15630000
|| 'EN1PART_WPOS=' 15640000
|| CASE WHEN EN1PART = 4 15650000
THEN 'OK' 15660000
ELSE 'NOK' 15670000
END 15680000
|| ';' 15690000
|| 'EA1STRN_WPOS=' 15700000
|| CASE WHEN EA1STRN = 1 15710000
THEN 'OK' 15720000
ELSE 'NOK' 15730000
END 15740000
|| ';' 15750000
|| 'EN1STK1_WPOS=' 15760000
|| CASE WHEN EN1STK1 = 2 15770000
THEN 'OK' 15780000
ELSE 'NOK' 15790000
END 15800000
|| ';' 15810000
|| 'EN1STK2_WPOS=' 15820000
|| CASE WHEN EN1STK2 = 3 15830000
THEN 'OK' 15840000
ELSE 'NOK' 15850000
END 15860000
|| ';' 15870000
|| 'CLUSTER_DEF=' 15880000
|| CASE CLUSTERING 15890000
WHEN 'Y' THEN 'OK' 15900000
ELSE 'NOK' 15910000
END 15920000
|| ';' 15930000
|| 'TooManyCols=' 15940000
|| CASE MAXCOL 15950000
WHEN 4 THEN 'OK' 15960000
ELSE 'NOK' 15970000
END 15980000
|| ';' 15990000
, CREATEDTS 16000000
FROM IXLST 16010000
WHERE ELAR_TYPE = 'BODY' 16020000
AND UNIQUERULE IN (SELECT INDEXTYPE 16030000
FROM IXTYPE_UNIQUE 16040000
) 16050000
AND SUBSTR(DATABASE,1,2) = 'XB' 16060000
UNION ALL 16070000
SELECT FAMILY 16080000
, COMMENT 16090000
, CAST(DATABASE AS CHAR(08)) 16100000
|| ' ' 16110000
|| IXNAME 16120000
|| ' ' 16130000
|| TBNAME 16140000
|| ' ' 16150000
|| 'IndexRule=Unique' 16160000
, 'EN1PART_MIS=' 16170000
|| CASE WHEN EN1PART = -1 16180000
THEN 'NOK' 16190000
ELSE 'OK' 16200000
END 16210000
|| ';' 16220000
|| 'EA1ATRN_MIS=' 16230000
|| CASE WHEN EA1ATRN = -1 16240000
THEN 'NOK' 16250000
ELSE 'OK' 16260000
END 16270000
|| ';' 16280000
|| 'EN1PART_WPOS=' 16290000
|| CASE WHEN EN1PART = 2 16300000
THEN 'OK' 16310000
ELSE 'NOK' 16320000
END 16330000
|| ';' 16340000
|| 'EA1ATRN_MIS=' 16350000
|| CASE WHEN EA1ATRN = 1 16360000
THEN 'OK' 16370000
ELSE 'NOK' 16380000
END 16390000
|| ';' 16400000
|| 'CLUSTER_DEF=' 16410000
|| CASE CLUSTERING 16420000
WHEN 'N' THEN 'OK' 16430000
ELSE 'NOK' 16440000
END 16450000
|| ';' 16460000
|| 'TooManyCols=' 16470000
|| CASE MAXCOL 16480000
WHEN 2 THEN 'OK' 16490000
ELSE 'NOK' 16500000
END 16510000
|| ';' 16520000
, CREATEDTS 16530000
FROM IXLST 16540000
WHERE ELAR_TYPE = 'MULTIPLE' 16550000
AND UNIQUERULE IN (SELECT INDEXTYPE 16560000
FROM IXTYPE_UNIQUE 16570000
) 16580000
AND SUBSTR(DATABASE,1,2) = 'XB' 16590000
UNION ALL 16600000
SELECT FAMILY 16610000
, COMMENT 16620000
, CAST(DATABASE AS CHAR(08)) 16630000
|| ' ' 16640000
|| IXNAME 16650000
|| ' ' 16660000
|| TBNAME 16670000
|| ' ' 16680000
|| 'IndexRule=Unique' 16690000
, 'EN1PART_MIS=' 16700000
|| CASE WHEN EN1PART = -1 16710000
THEN 'NOK' 16720000
ELSE 'OK' 16730000
END 16740000
|| ';' 16750000
|| 'EATRNVON_MIS=' 16760000
|| CASE WHEN EATRNVON = -1 16770000
THEN 'NOK' 16780000
ELSE 'OK' 16790000
END 16800000
|| ';' 16810000
|| 'EATRNBIS_MIS=' 16820000
|| CASE WHEN EATRNBIS = -1 16830000
THEN 'NOK' 16840000
ELSE 'OK' 16850000
END 16860000
|| ';' 16870000
|| 'EN1PART_WPOS=' 16880000
|| CASE WHEN EN1PART = 3 16890000
THEN 'OK' 16900000
ELSE 'NOK' 16910000
END 16920000
|| ';' 16930000
|| 'EATRNVON_WPOS=' 16940000
|| CASE WHEN EATRNVON = 1 16950000
THEN 'OK' 16960000
ELSE 'NOK' 16970000
END 16980000
|| ';' 16990000
|| 'EATRNBIS_MIS=' 17000000
|| CASE WHEN EATRNBIS = 2 17010000
THEN 'OK' 17020000
ELSE 'NOK' 17030000
END 17040000
|| ';' 17050000
|| 'TooManyCols=' 17060000
|| CASE MAXCOL 17070000
WHEN 3 THEN 'OK' 17080000
ELSE 'NOK' 17090000
END 17100000
|| ';' 17110000
|| 'CLUSTER_DEF=' 17120000
|| CASE CLUSTERING 17130000
WHEN 'N' THEN 'OK' 17140000
ELSE 'NOK' 17150000
END 17160000
|| ';' 17170000
, CREATEDTS 17180000
FROM IXLST 17190000
WHERE ELAR_TYPE = 'SIGNATUR' 17200000
AND UNIQUERULE IN (SELECT INDEXTYPE 17210000
FROM IXTYPE_UNIQUE 17220000
) 17230000
AND SUBSTR(DATABASE,1,2) = 'XB' 17240000
UNION ALL 17250000
SELECT FAMILY 17260000
, COMMENT 17270000
, CAST(DATABASE AS CHAR(08)) 17280000
|| ' ' 17290000
|| IXNAME 17300000
|| ' ' 17310000
|| TBNAME 17320000
|| ' ' 17330000
|| 'IndexRule=Duplicate' 17340000
, 'EN1PART_FAIL=' 17350000
|| CASE WHEN EN1PART > -1 17360000
THEN 'NOK' 17370000
ELSE 'OK' 17380000
END 17390000
|| ';' 17400000
|| 'EA1ATRN_MIS=' 17410000
|| CASE WHEN EA1ATRN = -1 17420000
THEN 'NOK' 17430000
ELSE 'OK' 17440000
END 17450000
|| ';' 17460000
|| 'EA1ATRN_WPOS=' 17470000
|| CASE WHEN EA1ATRN = 1 17480000
THEN 'NOK' 17490000
ELSE 'OK' 17500000
END 17510000
|| ';' 17520000
|| 'TooManyCols=' 17530000
|| CASE MAXCOL 17540000
WHEN 1 THEN 'OK' 17550000
ELSE 'NOK' 17560000
END 17570000
|| ';' 17580000
|| 'CLUSTER_DEF=' 17590000
|| CASE CLUSTERING 17600000
WHEN 'Y' THEN 'OK' 17610000
ELSE 'NOK' 17620000
END 17630000
|| ';' 17640000
, CREATEDTS 17650000
FROM IXLST 17660000
WHERE ELAR_TYPE = 'MULTIPLE' 17670000
AND UNIQUERULE IN (SELECT INDEXTYPE 17680000
FROM IXTYPE_UNIQUE 17690000
) 17700000
AND SUBSTR(DATABASE,1,2) = 'XB' 17710000
UNION ALL 17720000
SELECT FAMILY 17730000
, COMMENT 17740000
, CAST(DATABASE AS CHAR(08)) 17750000
|| ' ' 17760000
|| IXNAME 17770000
|| ' ' 17780000
|| TBNAME 17790000
|| ' ' 17800000
|| 'IndexRule=Duplicate' 17810000
, 'EN1PART_FIAL=' 17820000
|| CASE WHEN EN1PART > -1 17830000
THEN 'NOK' 17840000
ELSE 'OK' 17850000
END 17860000
|| ';' 17870000
|| 'EATRNVON_MIS=' 17880000
|| CASE WHEN EATRNVON = -1 17890000
THEN 'NOK' 17900000
ELSE 'OK' 17910000
END 17920000
|| ';' 17930000
|| 'EATRNBIS_MIS=' 17940000
|| CASE WHEN EATRNBIS = -1 17950000
THEN 'NOK' 17960000
ELSE 'OK' 17970000
END 17980000
|| ';' 17990000
|| 'EATRNVON_WPOS=' 18000000
|| CASE WHEN EATRNVON = 1 18010000
THEN 'OK' 18020000
ELSE 'NOK' 18030000
END 18040000
|| ';' 18050000
|| 'EATRNBIS_MIS=' 18060000
|| CASE WHEN EATRNBIS = 2 18070000
THEN 'OK' 18080000
ELSE 'NOK' 18090000
END 18100000
|| ';' 18110000
|| 'TooManyCols=' 18120000
|| CASE MAXCOL 18130000
WHEN 2 THEN 'OK' 18140000
ELSE 'NOK' 18150000
END 18160000
|| ';' 18170000
|| 'CLUSTER_DEF=' 18180000
|| CASE CLUSTERING 18190000
WHEN 'Y' THEN 'OK' 18200000
ELSE 'NOK' 18210000
END 18220000
|| ';' 18230000
, CREATEDTS 18240000
FROM IXLST 18250000
WHERE ELAR_TYPE = 'SIGNATUR' 18260000
AND UNIQUERULE IN (SELECT INDEXTYPE 18270000
FROM IXTYPE_UNIQUE 18280000
) 18290000
AND SUBSTR(DATABASE,1,2) = 'XB' 18300000
UNION ALL 18310000
SELECT FAMILY 18320000
, COMMENT 18330000
, CAST(DATABASE AS CHAR(08)) 18340000
|| ' ' 18350000
|| IXNAME 18360000
|| ' ' 18370000
|| TBNAME 18380000
|| ' ' 18390000
|| 'IndexRule=Unknown' 18400000
, 'Index_Rule=NOK;' 18410000
, CREATEDTS 18420000
FROM IXLST 18430000
WHERE ELAR_TYPE = 'unknown ' 18440000
AND SUBSTR(DATABASE,1,2) = 'XB' 18450000
) 18460000
SELECT GENERATE_UNIQUE() 18470000
, '5Index Option Check' 18480000
, FAMILY 18490000
, COMMENT 18500000
, OBJECT 18510000
, OPTVALUE 18520000
, CREATEDTS 18530000
FROM RXI 18540000
ORDER BY FAMILY 18550000
, OBJECT 18560000
; 18570000
INSERT INTO SESSION.TEMP 18580000
SELECT GENERATE_UNIQUE() 18590000
, '6ELAR meta data mismatch' 18600000
, FAMILY 18610000
, ' ' 18620000
, FAMILY 18630000
, 'NoDatabaseExists=NOK;' 18640000
, '0001-01-01-00.00.00.000000' 18650000
FROM SESSION.FAMLISTE 18660000
WHERE STORAR_N IS NULL 18670000
AND ENSEG_N IS NULL 18680000
AND SUBSTR(DBNAME,1,3) <> 'DXB' 18690000
AND SUBSTR(FAMILY,1,1) <> '?' 18700000
UNION ALL 18710000
SELECT GENERATE_UNIQUE() 18720000
, '7ELAR meta data mismatch' 18730000
, DBNAME 18740000
, ' ' 18750000
, DBNAME 18760000
, 'DatabaseNoFamily=NOK;' 18770000
, '0001-01-01-00.00.00.000000' 18780000
FROM SESSION.FAMLISTE 18790000
WHERE DBNAME NOT IN (SELECT STORAR 18800000
FROM SESSION.ELAR 18810000
) 18820000
AND SUBSTR(DBNAME,1,1) <> '?' 18830000
; 18840000
DECLARE GLOBAL TEMPORARY TABLE PARMVALUE 18850000
( OPTION CHAR(025) 18860000
, VALUE CHAR(120) 18870000
, SUBORD INTEGER 18880000
) 18890000
; 18900000
INSERT INTO SESSION.PARMVALUE 18910000
VALUES ( 'InfoTCP' 18920000
, 'Info: Table Controlled Partition' 18930000
, 0 18940000
) 18950000
; 18960000
INSERT INTO SESSION.PARMVALUE 18970000
VALUES ( 'NPI' 18980000
, 'on partitioned Object (table/Index) ' 18990000
|| 'not allowed' 19000000
, 0 19010000
) 19020000
; 19030000
INSERT INTO SESSION.PARMVALUE 19040000
VALUES ( 'InfoTSS' 19050000
, 'Info: Segmented or Simple Table' 19060000
, 0 19070000
) 19080000
; 19090000
INSERT INTO SESSION.PARMVALUE 19100000
VALUES ( 'InfoICP' 19110000
, 'Info: Index Controlled Partition' 19120000
, 0 19130000
) 19140000
; 19150000
INSERT INTO SESSION.PARMVALUE 19160000
VALUES ( 'InfoNPI' 19170000
, 'Info: Non partitioned Index' 19180000
, 0 19190000
) 19200000
; 19210000
INSERT INTO SESSION.PARMVALUE 19220000
VALUES ( 'StorageGroup' 19230000
, 'GSMS1, GSMS3 or GSMS4 for XB Databases ' 19240099
|| 'GSMS for ELAR Metadata allowed' 19250000
, 3 19260000
) 19270000
; 19280000
INSERT INTO SESSION.PARMVALUE 19290000
VALUES ( 'Bufferpool_DB' 19300000
,'BP2 allowed on Datasbase definition level' 19310000
, 0 19320000
) 19330000
; 19340000
INSERT INTO SESSION.PARMVALUE 19350000
VALUES ( 'Bufferpool_DB_IX' 19360000
,'BP1 allowed on Datasbase definition level' 19370000
, 1 19380000
) 19390000
; 19400000
INSERT INTO SESSION.PARMVALUE 19410000
VALUES ( 'Bufferpool_TS' 19420000
,'Only BP2, BP8 and BP32K allowed for Tablespaces' 19430000
, 2 19440000
) 19450000
; 19460000
INSERT INTO SESSION.PARMVALUE 19470000
VALUES ( 'Bufferpool_SHS' 19480000
,'Only BP32K for this Tablespaces allowed' 19490000
, 2 19500000
) 19510000
; 19520000
INSERT INTO SESSION.PARMVALUE 19530000
VALUES ( 'Bufferpool_LOB' 19540000
,'Only BP8 for LOB Tablespaces allowed' 19550000
, 2 19560000
) 19570000
; 19580000
INSERT INTO SESSION.PARMVALUE 19590000
VALUES ( 'IndexBufferpool', 'BP1' 19600000
, 3 19610000
) 19620000
; 19630000
INSERT INTO SESSION.PARMVALUE 19640000
VALUES ( 'EncodingScheme', 'EBCDIC' 19650000
, 4 19660000
) 19670000
; 19680000
INSERT INTO SESSION.PARMVALUE 19690000
VALUES ( 'Groupbufferpool', 'Changed' 19700000
, 5 19710000
) 19720000
; 19730000
INSERT INTO SESSION.PARMVALUE 19740000
VALUES ( 'LOB Bufferpool', 'BP8' 19750000
, 6 19760000
) 19770000
; 19780000
INSERT INTO SESSION.PARMVALUE 19790000
VALUES ( 'Trackmode', 'No', 10 19800000
) 19810000
; 19820000
INSERT INTO SESSION.PARMVALUE 19830000
VALUES ( 'LoggedOption', 'Yes', 11 19840000
) 19850000
; 19860000
INSERT INTO SESSION.PARMVALUE 19870000
VALUES ( 'DSSIZE' 19880000
, 'DSSIZE must be 16GB. No other values allowed' 19890000
, 12 19900000
) 19910000
; 19920000
INSERT INTO SESSION.PARMVALUE 19930000
VALUES ( 'Locksize', 'Any', 13 19940000
) 19950000
; 19960000
INSERT INTO SESSION.PARMVALUE 19970000
VALUES ( 'LockMax', 'System', 14 19980000
) 19990000
; 20000000
INSERT INTO SESSION.PARMVALUE 20010000
VALUES ( 'CloseOption', 'Yes', 17 20020000
) 20030000
; 20040000
INSERT INTO SESSION.PARMVALUE 20050000
VALUES ( 'TblCompressOption', 'Yes', 27 20060000
) 20070000
; 20080000
INSERT INTO SESSION.PARMVALUE 20090000
VALUES ( 'IdxCompressOption', 'No', 29 20100000
) 20110000
; 20120000
INSERT INTO SESSION.PARMVALUE 20130000
VALUES ( 'PrimaryQuantity', 'Only value -1 allowed' 20140000
, 8 20150000
) 20160000
; 20170000
INSERT INTO SESSION.PARMVALUE 20180000
VALUES ( 'SecondaryQuantity', 'Only value -1 allowed' 20190000
, 9 20200000
) 20210000
; 20220000
INSERT INTO SESSION.PARMVALUE 20230000
VALUES ( 'DefineOption', 'Yes', 23 20240000
) 20250000
; 20260000
INSERT INTO SESSION.PARMVALUE 20270000
VALUES ( 'MaxRowsPerPage', '255', 24 20280000
) 20290000
; 20300000
INSERT INTO SESSION.PARMVALUE 20310000
VALUES ( 'Audit', 'None', 18 20320000
) 20330000
; 20340000
INSERT INTO SESSION.PARMVALUE 20350000
VALUES ( 'DataCapture', 'None', 19 20360000
) 20370000
; 20380000
INSERT INTO SESSION.PARMVALUE 20390000
VALUES ( 'RestrictOnDrop' 20400000
, 'Missing Option With Restrict on Drop' 20410000
, 20 20420000
) 20430000
; 20440000
INSERT INTO SESSION.PARMVALUE 20450000
VALUES ( 'Volatile', 'correct value is Yes', 21 20460000
) 20470000
; 20480000
INSERT INTO SESSION.PARMVALUE 20490000
VALUES ( 'Append', 'Current value is not YES', 22 20500000
) 20510000
; 20520000
INSERT INTO SESSION.PARMVALUE 20530000
VALUES ( 'PartKeyName', 'EN1PART', 30 20540000
) 20550000
; 20560000
INSERT INTO SESSION.PARMVALUE 20570000
VALUES ( 'PartKeyType', 'Integer', 31 20580000
) 20590000
; 20600000
INSERT INTO SESSION.PARMVALUE 20610000
VALUES ( 'PartKeyPosition', 'First Column of Table', 32 20620000
) 20630000
; 20640000
INSERT INTO SESSION.PARMVALUE 20650000
VALUES ( 'EN1PART_MIS' 20660000
, 'EN1PART missing in Index', 33 20670000
) 20680000
; 20690000
INSERT INTO SESSION.PARMVALUE 20700000
VALUES ( 'EA1ARTN_MIS' 20710000
, 'EA1ATRN missing in Index', 33 20720000
) 20730000
; 20740000
INSERT INTO SESSION.PARMVALUE 20750000
VALUES ( 'EA1SRTN_MIS' 20760000
, 'EA1STRN missing in Index', 33 20770000
) 20780000
; 20790000
INSERT INTO SESSION.PARMVALUE 20800000
VALUES ( 'EN1STK1_MIS' 20810000
, 'EN1STK1 missing in Index', 33 20820000
) 20830000
; 20840000
INSERT INTO SESSION.PARMVALUE 20850000
VALUES ( 'EN1STK2_MIS' 20860000
, 'EN1STK2 missing in Index', 33 20870000
) 20880000
; 20890000
INSERT INTO SESSION.PARMVALUE 20900000
VALUES ( 'EATRNVON_MIS' 20910000
, 'EATRNVON missing in Index', 33 20920000
) 20930000
; 20940000
INSERT INTO SESSION.PARMVALUE 20950000
VALUES ( 'EATRNBIS_MIS' 20960000
, 'EATRNBIS missing in Index', 33 20970000
) 20980000
; 20990000
INSERT INTO SESSION.PARMVALUE 21000000
VALUES ( 'EN1PART_WPOS' 21010000
, 'EN1PART wrong position in index', 33 21020000
) 21030000
; 21040000
INSERT INTO SESSION.PARMVALUE 21050000
VALUES ( 'EA1ARTN_WPOS' 21060000
, 'EA1ATRN wrong position in index', 33 21070000
) 21080000
; 21090000
INSERT INTO SESSION.PARMVALUE 21100000
VALUES ( 'EA1SRTN_WPOS' 21110000
, 'EA1STRN wrong position in index', 33 21120000
) 21130000
; 21140000
INSERT INTO SESSION.PARMVALUE 21150000
VALUES ( 'EN1STK1_WPOS' 21160000
, 'EN1STK1 wrong position in index', 33 21170000
) 21180000
; 21190000
INSERT INTO SESSION.PARMVALUE 21200000
VALUES ( 'EN1STK2_WPOS' 21210000
, 'EN1STK2 wrong position in index', 33 21220000
) 21230000
; 21240000
INSERT INTO SESSION.PARMVALUE 21250000
VALUES ( 'EATRNVON_WPOS' 21260000
, 'EATRNVO wrong position in index', 33 21270000
) 21280000
; 21290000
INSERT INTO SESSION.PARMVALUE 21300000
VALUES ( 'EATRNBIS_WPOS' 21310000
, 'EATRNBIS wrong position in index', 33 21320000
) 21330000
; 21340000
INSERT INTO SESSION.PARMVALUE 21350000
VALUES ( 'EN1PART_FAIL' 21360000
, 'EN1PART not allowed for the index', 34 21370000
) 21380000
; 21390000
INSERT INTO SESSION.PARMVALUE 21400000
VALUES ( 'CLUSTER_DEF' 21410000
, 'Cluster definition not allowed, wrong context', 35 21420000
) 21430000
; 21440000
INSERT INTO SESSION.PARMVALUE 21450000
VALUES ( 'TooManyCols' 21460000
, 'Number of key columns not correct. Number depends' 21470000
||'on ELAR table type' 21480000
, 35 21490000
) 21500000
; 21510000
INSERT INTO SESSION.PARMVALUE 21520000
VALUES ( 'TablePartLimit', 'Partition limit exceeded (' || 21530000
(SELECT STRIP(CAST(MAXPART AS 21540000
CHAR(06) 21550000
) 21560000
) 21570000
FROM SESSION.MAXPART 21580000
WHERE PARTYPE = 'Table' 21590000
) || ' Partition allowed)' 21600000
, 39 21610000
) 21620000
; 21630000
INSERT INTO SESSION.PARMVALUE 21640000
VALUES ( 'IndexPartLimit', 'Maximum of ' || 21650000
(SELECT CAST(MAXPART AS CHAR(06)) 21660000
FROM SESSION.MAXPART 21670000
WHERE PARTYPE = 'Index' 21680000
) || ' Partition exceeded ' 21690000
, 40 21700000
) 21710000
; 21720000
INSERT INTO SESSION.PARMVALUE 21730000
VALUES ( 'PartKeyValue(s)' 21740000
, 'only natural Numbers and one value allowed' 21750000
, 34 21760000
) 21770000
; 21780000
INSERT INTO SESSION.PARMVALUE 21790000
VALUES ( 'PartKeyColNum' 21800000
, 'Wrong partitioning, use classic partitioning' 21810000
, 35 21820000
) 21830000
; 21840000
INSERT INTO SESSION.PARMVALUE 21850000
VALUES ( 'Indextype' 21860000
, 'Unique DPSI, when Primary Index all other are DPSI' 21870000
, 28 21880000
) 21890000
; 21900000
INSERT INTO SESSION.PARMVALUE 21910000
VALUES ( 'Pagesize' 21920000
, 'Pagesize Tablespace <> Pagesize Indexspace' 21930000
, 16 21940000
) 21950000
; 21960000
INSERT INTO SESSION.PARMVALUE 21970000
VALUES ( 'FreePage' 21980000
, 'Value must be 0 for technical/business indexes' 21990000
, 7 22000000
) 22010000
; 22020000
INSERT INTO SESSION.PARMVALUE 22030000
VALUES ( 'TSFreepage' 22040000
, 'Value is not 0' 22050000
, 7 22060000
) 22070000
; 22080000
INSERT INTO SESSION.PARMVALUE 22090000
VALUES ( 'PctFree_Business' 22100000
, 'Value must be 10 for business Indexes' 22110000
, 6 22120000
) 22130000
; 22140000
INSERT INTO SESSION.PARMVALUE 22150000
VALUES ( 'PctFree_Technical' 22160000
, 'Value must be 0 for technical indexes' 22170000
, 6 22180000
) 22190000
; 22200000
INSERT INTO SESSION.PARMVALUE 22210000
VALUES ( 'TSPctfree' 22220000
, 'Value is not 0' 22230000
, 6 22240000
) 22250000
; 22260000
INSERT INTO SESSION.PARMVALUE 22270000
VALUES ( 'Copy', 'No', 26 22280000
) 22290000
; 22300000
INSERT INTO SESSION.PARMVALUE 22310000
VALUES ( 'No Index exists' 22320000
, 'verify and implement an Index. Access path for all ' 22330000
|| 'queries are Tablespace Scans| (Proof Monitor)' 22340000
, 50 22350000
) 22360000
; 22370000
INSERT INTO SESSION.PARMVALUE 22380000
VALUES ( 'IndexType_Failure' 22390000
, 'Only index type DPSI (D), Unique (U) and ' 22400000
|| 'Primary (P) are allowed' 22410000
, 50 22420000
) 22430000
; 22440000
INSERT INTO SESSION.PARMVALUE 22450000
VALUES ( 'EN1PART_MIS' 22460000
, 'Missing EN1PART. Must be part of PRIMARY ' 22470000
|| 'or UNIQUE INDEX (see Guideline 01.07.2013' 22480000
, 51 22490000
) 22500000
; 22510000
INSERT INTO SESSION.PARMVALUE 22520000
VALUES ( 'Index_Rule' 22530000
, 'no rules are defined. REASON: Unknown ELAR ' 22540000
|| 'table type (not documented, experimental type' 22550000
, 53 22560000
) 22570000
; 22580000
INSERT INTO SESSION.PARMVALUE 22590000
VALUES ( 'NoDatabaseExists' 22600000
, 'No DB2 Objects exists for DDL Check' 22610000
, 0 22620000
) 22630000
; 22640000
INSERT INTO SESSION.PARMVALUE 22650000
VALUES ( 'DatabaseNoFamily' 22660000
, 'DB2 Objects exists but no ELAR Meta data available' 22670000
, 0 22680000
) 22690000
; 22700000
-- Preperation of result list splitting complete string into option name22710000
-- and option value. Options separated by semicolon. Option and value 22720000
-- separated by equal sign. Elimination of duplicate entries and store 22730000
-- only entries with NOK in temporary table exception. 22740000
DECLARE GLOBAL TEMPORARY TABLE EXCEPTION 22750000
( OBJECT CHAR(60) 22760000
, OPTION CHAR(24) 22770000
, CORRECT_VALUES CHAR(120) 22780000
, FAMILY CHAR(30) 22790000
, CRITERIA CHAR(30) 22800000
, OPTIONNUM INTEGER 22810000
, SUBORD INTEGER 22820000
, COMMENT CHAR(20) 22830000
, OVALUE CHAR(03) 22840000
, CREATEDTS TIMESTAMP(6) 22850000
) 22860000
; 22870000
INSERT INTO SESSION.EXCEPTION ( 22880000
OBJECT 22890000
, OPTION 22900000
, CORRECT_VALUES 22910000
, FAMILY 22920000
, CRITERIA 22930000
, OPTIONNUM 22940000
, SUBORD 22950000
, COMMENT 22960000
, OVALUE 22970000
, CREATEDTS 22980000
) 22990000
WITH RESULT ( SNUM 23000000
, CRITERIA 23010000
, FAMILY 23020000
, OBJECT 23030000
, OPTIONNUM 23040000
, OPTION 23050000
, OVALUE 23060000
, CHECKVALUE 23070000
, COMMENT 23080000
, CREATEDTS 23090000
) 23100000
AS (SELECT T0.SNUM 23110000
, T0.CRITERIA 23120000
, T0.FAMILY 23130000
, T0.OBJECT 23140000
, 1 23150000
, CASE WHEN LOCATE('=', T0.OPTVALUE) > 0 23160000
THEN SUBSTR( T0.OPTVALUE 23170000
, 1 23180000
, LOCATE('=', T0.OPTVALUE) - 1 23190000
) 23200000
ELSE OPTVALUE 23210000
END AS OPTION 23220000
, CASE WHEN LOCATE('=', T0.OPTVALUE) > 0 23230000
THEN SUBSTR( T0.OPTVALUE 23240000
, LOCATE('=', T0.OPTVALUE) + 1 23250000
, LOCATE(';', T0.OPTVALUE) - 23260000
LOCATE('=', T0.OPTVALUE) - 1 23270000
) 23280000
ELSE OPTVALUE 23290000
END AS OVALUE 23300000
, CASE WHEN LOCATE(';', T0.OPTVALUE) > 0 23310000
THEN SUBSTR( T0.OPTVALUE 23320000
, LOCATE(';', T0.OPTVALUE) + 1 23330000
) 23340000
ELSE NULL 23350000
END AS OPTVALUE 23360000
, T0.COMMENT 23370000
, T0.CREATEDTS 23380000
FROM SESSION.TEMP T0 23390000
UNION ALL 23400000
SELECT T1.SNUM 23410000
, T1.CRITERIA 23420000
, T1.FAMILY 23430000
, T1.OBJECT 23440000
, T1.OPTIONNUM + 1 23450000
, CASE WHEN LOCATE('=', T1.CHECKVALUE) > 0 23460000
THEN SUBSTR( T1.CHECKVALUE 23470000
, 1 23480000
, LOCATE('=', T1.CHECKVALUE) - 1 23490000
) 23500000
ELSE CHECKVALUE 23510000
END AS OPTION 23520000
, CASE WHEN LOCATE('=', T1.CHECKVALUE) > 0 23530000
THEN CASE WHEN LOCATE(';', T1.CHECKVALUE) > 0 23540000
THEN SUBSTR( T1.CHECKVALUE 23550000
, LOCATE('=', T1.CHECKVALUE) 23560000
+ 1 23570000
, LOCATE(';', T1.CHECKVALUE) 23580000
- LOCATE('=', T1.CHECKVALUE) 23590000
- 1 23600000
) 23610000
ELSE SUBSTR( T1.CHECKVALUE 23620000
, LOCATE('=', T1.CHECKVALUE) 23630000
+ 1 23640000
) 23650000
END 23660000
ELSE CHECKVALUE 23670000
END AS OVALUE 23680000
, CASE WHEN LOCATE(';', T1.CHECKVALUE) > 0 23690000
THEN SUBSTR( T1.CHECKVALUE 23700000
, LOCATE(';', T1.CHECKVALUE) + 1 23710000
) 23720000
ELSE NULL 23730000
END AS OPTVALUE 23740000
, T1.COMMENT 23750000
, T1.CREATEDTS 23760000
FROM RESULT T1 23770000
WHERE T1.CHECKVALUE IS NOT NULL 23780000
) 23790000
SELECT DISTINCT 23800000
RE.OBJECT 23810000
, CAST(RE.OPTION AS CHAR(24)) AS OPTION 23820000
, PV.VALUE AS CORRECT_VALUES 23830000
, RE.FAMILY 23840000
, RE.CRITERIA 23850000
, RE.OPTIONNUM 23860000
, PV.SUBORD 23870000
, RE.COMMENT 23880000
, RE.OVALUE 23890000
, RE.CREATEDTS 23900000
FROM RESULT RE 23910000
INNER JOIN SESSION.PARMVALUE PV 23920000
ON RE.OPTION = PV.OPTION 23930000
WHERE RE.OVALUE = 'NOK' 23940000
; 23950000
-- 23960000
-- Generate final message. Three different message possible: 23970000
-- (1) Database not found => Database not created and 23980000
-- (2) No failures found => All check passed without failures 23990000
-- (3) Failuers found => Not all ckecks passed without failures 24000000
-- 24010000
INSERT INTO SESSION.EXCEPTION ( 24020000
OBJECT 24030000
, OPTION 24040000
, CORRECT_VALUES 24050000
, FAMILY 24060000
, CRITERIA 24070000
, OPTIONNUM 24080000
, SUBORD 24090000
, COMMENT 24100000
, OVALUE 24110000
, CREATEDTS 24120000
) 24130000
SELECT 'Database not found => no check possible' 24140000
, ' ' 24150000
, ' ' 24160000
, ' ' 24170000
, ' ' 24180000
, -100 24190000
, -100 24200000
, ' ' 24210000
, ' ' 24220000
, '0001-01-01-00.00.00.000000' 24230000
FROM SYSIBM.SYSDUMMY1 24240000
WHERE (SELECT COUNT(*) 24250000
FROM SESSION.FAMLISTE FAM 24260000
INNER JOIN SYSIBM.SYSDATABASE DB 24270000
ON FAM.DBNAME = DB.NAME 24280000
) = 0 24290000
AND (SELECT DBNAME FROM SESSION.FAMLISTE) IS NOT NULL 24291000
; 24300000
INSERT INTO SESSION.EXCEPTION ( 24310000
OBJECT 24320000
, OPTION 24330000
, CORRECT_VALUES 24340000
, FAMILY 24350000
, CRITERIA 24360000
, OPTIONNUM 24370000
, SUBORD 24380000
, COMMENT 24390000
, OVALUE 24400000
, CREATEDTS 24410000
) 24420000
SELECT 'No failures found' 24430000
, ' ' 24440000
, ' ' 24450000
, ' ' 24460000
, ' ' 24470000
, -101 24480000
, -101 24490000
, ' ' 24500000
, ' ' 24510000
, '0001-01-01-00.00.00.000000' 24520000
FROM SYSIBM.SYSDUMMY1 24530000
WHERE (SELECT COUNT(*) FROM SESSION.EXCEPTION) = 0 24540000
; 24550000
INSERT INTO SESSION.EXCEPTION ( 24560000
OBJECT 24570000
, OPTION 24580000
, CORRECT_VALUES 24590000
, FAMILY 24600000
, CRITERIA 24610000
, OPTIONNUM 24620000
, SUBORD 24630000
, COMMENT 24640000
, OVALUE 24650000
, CREATEDTS 24660000
) 24670000
SELECT 'Failure found, send list to elar' 24680000
, ' ' 24690000
, ' ' 24700000
, ' ' 24710000
, ' ' 24720000
, 0 24730000
, 0 24740000
, ' ' 24750000
, ' ' 24760000
, '0001-01-01-00.00.00.000000' 24770000
FROM SYSIBM.SYSDUMMY1 24780000
WHERE (SELECT COUNT(*) 24790000
FROM SESSION.EXCEPTION 24800000
WHERE OPTIONNUM NOT IN ( -100, -101 ) 24810000
AND SUBORD NOT IN ( -100, -101 ) 24820000
) > 0 24830000
; 24840000
-- 24850000
-- Generate Report 24860000
-- DDL-Report (Display migrated families first) 24870000
-- 24880000
SELECT OBJECT 24890000
, OPTION 24900000
, CORRECT_VALUES 24910000
, FAMILY 24920000
, COMMENT 24930000
, CRITERIA 24940000
, CREATEDTS 24950000
FROM (SELECT OBJECT AS OBJECT 24960000
, OPTION AS OPTION 24970000
, CORRECT_VALUES AS CORRECT_VALUES 24980000
, CASE WHEN SUBSTR(FAMILY,1,3) = 'FAM' 24990000
THEN ( SELECT UCASE(EAOF) 25000000
FROM BUA.TXBA131 25010000
WHERE ENOF = CAST( SUBSTR(T1.FAMILY,4,4) 25020000
AS INT 25030000
) - 1000 25040000
) 25050000
ELSE FAMILY 25060000
END AS FAMILY 25070000
, COMMENT AS COMMENT 25080000
, CRITERIA AS CRITERIA 25090000
, CREATEDTS AS CREATEDTS 25100000
, ORD0 25110000
, ORD1 25120000
, ORD2 25130000
, ORD3 25140000
FROM (SELECT OBJECT 25150000
, CASE WHEN LOCATE('_', OPTION) > 0 25160000
THEN SUBSTR( OPTION 25170000
, 1 25180000
, LOCATE('_',OPTION) - 1 25190000
) 25200000
ELSE OPTION 25210000
END AS OPTION 25220000
, CORRECT_VALUES 25230000
, UCASE(FAMILY) AS FAMILY 25240000
, SUBSTR(CRITERIA,2) AS CRITERIA 25250000
, SUBSTR(CRITERIA,1,1) AS ORD1 25260000
, LENGTH(STRIP(OBJECT)) AS ORD2 25270000
, SUBORD AS ORD3 25280000
, COMMENT 25290000
, CASE WHEN COMMENT <> ' ' 25300000
THEN 0 25310000
ELSE 1 25320000
END AS ORD0 25330000
, CHECK 25340000
, CREATEDTS 25350000
FROM (SELECT OVALUE AS CHECK 25360000
, OBJECT 25370000
, OPTION 25380000
, CORRECT_VALUES 25390000
, FAMILY 25400000
, CRITERIA 25410000
, SUBORD 25420000
, COMMENT 25430000
, CREATEDTS 25440000
FROM SESSION.EXCEPTION 25450000
) AS T2 25460000
) AS T1 25470000
) AS FINAL 25480000
ORDER BY FAMILY 25490000
, ORD0 25500000
, ORD1 25510000
, ORD2 25520000
, ORD3 25530000
WITH UR 25540000
; 25550000