zOs/TX/WK940DDL
------------$-{right(userid() sysvar(sysnode) date(s) time(), 60, '-')}
-- testCase $dsn
-- env $env phase $phase
-- subsys $subsys db $db creator $creator
------------------------------------------------------------------------
---||| begin ddl testcase $mbr env $env phase $phase
SET CURRENT SQLID='S100447';
drop tablespace $db.A940A;
commit;
------------------------------------------------------------------------
$@[
if $phase < 90 then $@=[
CREATE TABLESPACE A940A
IN $db
USING STOGROUP GSMS
PRIQTY 720 SECQTY 7200
FREEPAGE 0 PCTFREE 30
$-[if($phase<=10, NUMPARTS 27 DSSIZE 4 G , segSize 64)$]
BUFFERPOOL BP2
LOCKSIZE ROW
LOCKMAX SYSTEM
COMPRESS YES;
COMMIT;
------------------------------------------------------------------------
$]
if $phase < 10 then $@=[
CREATE TABLE $creator.TMI100A1
(I_ID_UNIT CHAR(4) NOT NULL,
I_RSK_EVNT CHAR(20) NOT NULL,
D_TIME_CREATED TIMESTAMP NOT NULL,
I_ID_CREATOR CHAR(20) NOT NULL,
D_TIME_MODIFIED TIMESTAMP NOT NULL,
I_ID_MODIFIER CHAR(20) NOT NULL,
D_TIME_MODTICKET TIMESTAMP NOT NULL,
I_ID_MODTICKET CHAR(20) NOT NULL,
I_ID_CSTMR CHAR(28) NOT NULL,
I_ID_BSNS_NMBR CHAR(40) NOT NULL,
C_BSNS_NMBR INTEGER NOT NULL,
D_FRST_PRRT_RD DATE NOT NULL,
D_MTN_TO_RD DATE NOT NULL,
D_ASCRB DATE NOT NULL,
N_DURTN_DYS INTEGER NOT NULL,
C_PRTY INTEGER NOT NULL,
F_OPN_INSTRCTN INTEGER NOT NULL,
C_TRND INTEGER NOT NULL,
I_RSK_EVNT_TP INTEGER NOT NULL,
I_RSK_EVNT_CLS INTEGER NOT NULL,
T_RSN CHAR(40) NOT NULL,
I_CRNCY_ISO_T_REP CHAR(3) NOT NULL,
A_RSK_EVNT_REP DECIMAL(31, 8) NOT NULL,
I_CRNCY_ISO_T_OVR CHAR(3) NOT NULL,
A_RSK_EVNT_OVR DECIMAL(31, 8) NOT NULL,
F_DLY INTEGER NOT NULL,
D_DLY DATE NOT NULL,
-- F_RCVRY INTEGER NOT NULL,
F_RCVRYAB DATE NOT NULL,
D_EOD DATE NOT NULL,
F_CHNG INTEGER NOT NULL WITH DEFAULT,
P_PRTFL_LTV DECIMAL(15, 8) NOT NULL WITH DEFAULT,
P_IMPLD_LTV DECIMAL(15, 8) NOT NULL WITH DEFAULT,
P_CNSMD_HRCT DECIMAL(15, 8) NOT NULL WITH DEFAULT,
P_CVR_RT DECIMAL(15, 8) NOT NULL WITH DEFAULT,
A_CVRGE_PRE_REP DECIMAL(31, 8) NOT NULL WITH DEFAULT,
A_CVRGE_PRE_ACC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
A_CVRGE_PST_REP DECIMAL(31, 8) NOT NULL WITH DEFAULT,
A_CVRGE_PST_ACC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
P_WRNG DECIMAL(15, 8) NOT NULL WITH DEFAULT,
CONSTRAINT PKEY_TMI100A1
PRIMARY KEY (I_ID_UNIT,
I_RSK_EVNT))
IN $db.A940A
PARTITION BY (I_ID_UNIT ASC)
(PART 1 VALUES('0058'),
PART 2 VALUES('0059'),
PART 3 VALUES('0061'),
PART 4 VALUES('0065'),
PART 5 VALUES('0071'),
PART 6 VALUES('0072'),
PART 7 VALUES('0073'),
PART 8 VALUES('0074'),
PART 9 VALUES('0075'),
PART 10 VALUES('0076'),
PART 11 VALUES('0077'),
PART 12 VALUES('0078'),
PART 13 VALUES('0079'),
PART 14 VALUES('0080'),
PART 15 VALUES('0081'),
PART 16 VALUES('0082'),
PART 17 VALUES('0083'),
PART 18 VALUES('0085'),
PART 19 VALUES('0086'),
PART 20 VALUES('0094'),
PART 21 VALUES('0101'),
PART 22 VALUES('0102'),
PART 23 VALUES('0103'),
PART 24 VALUES('0104'),
PART 25 VALUES('0105'),
PART 26 VALUES('0106'),
PART 27 VALUES('9999'));
LABEL ON TABLE $creator.TMI100A1 IS 'RSIN Risk Event';
LABEL ON $creator.TMI100A1
(P_WRNG IS 'Percentage of Warning',
A_CVRGE_PST_ACC IS 'Coverage post in acc ccy',
A_CVRGE_PST_REP IS 'Coverage post in rep ccy',
A_CVRGE_PRE_ACC IS 'Coverage pre in acc ccy',
A_CVRGE_PRE_REP IS 'Coverage pre in rep ccy',
P_CVR_RT IS 'Cover Ratio (MM) in %',
P_CNSMD_HRCT IS 'Consumed Hair Cut in %',
P_IMPLD_LTV IS 'Implied LTV in %',
P_PRTFL_LTV IS 'Portfolio LTV in %',
F_CHNG IS 'Änderung des Trends (WFI)',
D_EOD IS 'Datum TEV-Verarb.',
-- F_RCVRY IS 'Flag Recovery',
F_RCVRYAB IS 'AB Flag Recovery',
D_DLY IS 'Datum des Ablaufs',
F_DLY IS 'Flag Verzögerung',
A_RSK_EVNT_OVR IS 'Risk Event Betr. akt.',
I_CRNCY_ISO_T_OVR IS 'Währung',
A_RSK_EVNT_REP IS 'Risk Event Betr. REP',
I_CRNCY_ISO_T_REP IS 'Währung REP',
T_RSN IS 'Risk Event Grund',
I_RSK_EVNT_CLS IS 'Risk Event Klasse',
I_RSK_EVNT_TP IS 'Risk Event Typ',
C_TRND IS 'Risk Event Status/Trend',
F_OPN_INSTRCTN IS 'Flag offene Anord.',
C_PRTY IS 'Priorität',
N_DURTN_DYS IS 'Tage Dauer',
D_ASCRB IS 'Datum zurückgeführt',
D_MTN_TO_RD IS 'Datum letzmals Rot',
D_FRST_PRRT_RD IS 'Datum 1stes Risk Event',
C_BSNS_NMBR IS 'Geschäftsnummern Typ',
I_ID_BSNS_NMBR IS 'Geschäftsnummer',
I_ID_CSTMR IS 'Kunden ID',
I_ID_MODTICKET IS 'Identifikation ModTicket',
D_TIME_MODTICKET IS 'Zeitpunkt ModTicket',
I_ID_MODIFIER IS 'Identifikation Update',
D_TIME_MODIFIED IS 'Zeitpunkt Update',
I_ID_CREATOR IS 'Identifikation Insert',
D_TIME_CREATED IS 'Zeitpunkt Insert',
I_RSK_EVNT IS 'Risk Event ID',
I_ID_UNIT IS 'Business Unit');
CREATE UNIQUE INDEX $creator.IMI100A0
ON $creator.TMI100A1
(I_ID_UNIT ASC,
I_RSK_EVNT ASC)
USING STOGROUP GSMS
PRIQTY 720 SECQTY 7200
PIECESIZE 2 G
FREEPAGE 10 PCTFREE 30
BUFFERPOOL BP1;
COMMIT;
$@[
if $phase >= 3 then $@=[
alter TABLE $creator.TMI100A1
alter PARTition 27 ending('0999');
$]
$]
$] if $phase >= 11 & $phase < 90 then $@=[
CREATE TABLE $creator.Twk940A1
(TMB001PNA CHAR(8) NOT NULL WITH DEFAULT,
TMB001ERT CHAR(8) NOT NULL WITH DEFAULT,
TMB001ERN CHAR(8) NOT NULL WITH DEFAULT,
TMB001INS CHAR(8) NOT NULL WITH DEFAULT,
TMB001LUT TIMESTAMP NOT NULL WITH DEFAULT,
$-[if($phase \= 12, TMB001PER, TMPB001QQQQ)$]
TIMESTAMP NOT NULL WITH DEFAULT,
TMB001CNT CHAR(8) NOT NULL WITH DEFAULT)
IN $db.A940A ;
COMMENT ON TABLE $creator.Twk940A1 IS 'MAIL KONTROLL TABELLE';
CREATE UNIQUE INDEX $creator.Iwk940A1
ON $creator.Twk940A1
(TMB001PNA ASC,
TMB001ERT ASC,
TMB001ERN ASC,
TMB001INS ASC,
TMB001LUT ASC)
USING STOGROUP GSMSDQ0G
PRIQTY 12 SECQTY 7200
BUFFERPOOL BP1;
$] if $phase >= 90 then $@=[
CREATE TABLESPACE A940A
IN $db
USING STOGROUP GSMS
PRIQTY 12 SECQTY 7200
FREEPAGE 7 PCTFREE 10
SEGSIZE 8
BUFFERPOOL BP2
LOCKSIZE ANY
COMPRESS YES;
CREATE TABLE $creator.TADM07A1
(DB_NAME CHAR(10) NOT NULL WITH DEFAULT,
TS_NAME CHAR(10) NOT NULL WITH DEFAULT,
PARTITIONS_TOTAL INTEGER NOT NULL WITH DEFAULT,
PARTITIONS_NR INTEGER NOT NULL WITH DEFAULT,
DS_SIZE INTEGER NOT NULL WITH DEFAULT,
LARGERENAME CHAR(1) NOT NULL WITH DEFAULT,
DSN_NAME CHAR(45) NOT NULL WITH DEFAULT,
SPACE INTEGER NOT NULL WITH DEFAULT,
DATUM DATE NOT NULL WITH DEFAULT)
IN $db.A940A ;
--
LABEL ON TABLE $creator.TADM07A1 IS 'GB Grenze';
--
COMMENT ON TABLE $creator.TADM07A1
IS 'Diese Daten kommen vom DB2 Catalog';
--
LABEL ON $creator.TADM07A1
(SPACE IS 'DB2 Catalog Space angabe',
DSN_NAME IS 'dataset Name',
DS_SIZE IS 'Gigabyte angabe',
PARTITIONS_NR IS 'Partitions Nummer',
PARTITIONS_TOTAL IS 'Partitions Gesammt',
TS_NAME IS 'Tablespace Namen',
DB_NAME IS 'Datenbank Namen');
--
CREATE INDEX $creator.IADM07A0
ON $creator.TADM07A1
(DB_NAME ASC,
TS_NAME ASC,
PARTITIONS_NR ASC)
USING STOGROUP GSMS
FREEPAGE 0 PCTFREE 5
BUFFERPOOL BP1;
$]
$]
commit;