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