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