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