zOs/TX/WK579DDL
------------$-{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.A579K;
commit;
CREATE TABLESPACE A579K
IN $db
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
DSSIZE 32 G
NUMPARTS 10
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255;
COMMIT;
------------------------------------------------------------------------
CREATE TABLE $creator.TWK579K1
(WK579K001 CHAR(1) FOR SBCS DATA NOT NULL,
WK579K002 CHAR(4) FOR SBCS DATA NOT NULL,
WK579K003 CHAR(20) FOR SBCS DATA NOT NULL,
WK579K004 DATE NOT NULL WITH DEFAULT,
WK579K005 CHAR(3) FOR SBCS DATA NOT NULL,
$@[
if $phase <= 1 then $@=[
WK579K006 SMALLINT NOT NULL,
$] else if $phase = 2 then $@=[
WK579K006 DECIMAL(15, 0) NOT NULL,
$] else $@=[
WK579K006 DECIMAL(15, 0) NOT NULL WITH DEFAULT,
$]
$]
WK579K007 TIMESTAMP NOT NULL WITH DEFAULT,
WK579K008 CHAR(4) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K009 CHAR(3) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K010 CHAR(3) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K011 CHAR(20) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K012 CHAR(3) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K013 CHAR(4) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K014 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K015 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K016 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K017 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K018 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K019 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K020 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K021 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K022 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K023 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K024 CHAR(18) FOR SBCS DATA NOT NULL
WITH DEFAULT,
$@[
if $phase <= 1 then $@=[
WK579K025 SMALLINT NOT NULL WITH DEFAULT,
$] else if $phase = 2 then $@=[
WK579K025 DECIMAL(15, 0) NOT NULL WITH DEFAULT,
$] else $@=[
WK579K025 DECIMAL(15, 0) NOT NULL,
$]
$]
WK579K026 CHAR(1) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K027 DATE NOT NULL WITH DEFAULT,
WK579K028 CHAR(4) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K029 CHAR(1) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K030 DATE NOT NULL WITH DEFAULT,
WK579K031 DATE NOT NULL WITH DEFAULT,
WK579K032 DATE NOT NULL WITH DEFAULT,
WK579K033 DATE NOT NULL WITH DEFAULT,
WK579K034 DATE NOT NULL WITH DEFAULT,
WK579K035 DATE NOT NULL WITH DEFAULT,
$@[
if $phase <= 1 then $@=[
WK579K036 SMALLINT NOT NULL WITH DEFAULT,
WK579K037 SMALLINT NOT NULL WITH DEFAULT,
$] else $@=[
WK579K036 DECIMAL(15, 0) NOT NULL WITH DEFAULT,
WK579K037 DECIMAL(15, 0) NOT NULL WITH DEFAULT,
$]
$]
WK579K038 CHAR(30) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K039 CHAR(30) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K040 CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K041 CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K042 CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K043 CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K044 CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K045 CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K046 TIMESTAMP NOT NULL WITH DEFAULT,
WK579K047 TIMESTAMP NOT NULL WITH DEFAULT,
WK579K048 TIMESTAMP NOT NULL WITH DEFAULT,
WK579K049 TIMESTAMP NOT NULL WITH DEFAULT,
WK579K050 TIMESTAMP NOT NULL WITH DEFAULT,
WK579K051 TIMESTAMP NOT NULL WITH DEFAULT,
WK579K052 CHAR(1) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K053 CHAR(1) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K054 CHAR(1) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K055 CHAR(1) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K056 CHAR(1) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K057 CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K058 CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K059 CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K060 CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K061 CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WK579K062 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K063 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K064 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K065 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
WK579K066 DECIMAL(31, 8) NOT NULL WITH DEFAULT,
CONSTRAINT WK579K001
PRIMARY KEY (WK579K001,
WK579K002,
WK579K003,
WK579K004,
WK579K005,
WK579K006))
IN $db.A579K
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE;
LABEL ON TABLE $creator.TWK579K1 IS ' NAC FEE SHADOW pha $phase';
LABEL ON $creator.TWK579K1
(WK579K066 IS 'RESERVE',
WK579K065 IS 'RESERVE',
WK579K064 IS 'RESERVE',
WK579K063 IS 'RESERVE',
WK579K062 IS 'RESERVE',
WK579K061 IS 'RESERVE',
WK579K060 IS 'RESERVE',
WK579K059 IS 'RESERVE',
WK579K058 IS 'RESERVE',
WK579K057 IS 'MODEL pha $phase',
WK579K056 IS 'RESERVE',
WK579K055 IS 'RESERVE',
WK579K054 IS 'RESERVE',
WK579K053 IS 'RESERVE',
WK579K052 IS 'RESERVE',
WK579K051 IS 'TS - RECALCULATED',
WK579K050 IS 'TS - DEBIT',
WK579K049 IS 'TS - SURCHRGE',
WK579K048 IS 'TS - DISCOUNT',
WK579K047 IS 'TS - CANCEL',
WK579K046 IS 'TS - INSERT',
WK579K045 IS 'PID - RECALCULATED',
WK579K044 IS 'PID - DEBIT',
WK579K043 IS 'PID - SURCHRGE',
WK579K042 IS 'PID - DISCOUNT',
WK579K041 IS 'PID - CANCEL',
WK579K040 IS 'PID - INSERT',
WK579K039 IS 'DEBIT INTERNAL TEXT',
WK579K038 IS 'DEBIT INTERNAL TEXT',
WK579K037 IS 'DEBIT FREQUENCY',
WK579K036 IS 'CALCULATION FREQUENCY',
WK579K035 IS 'DEBIT PERIOD TO',
WK579K034 IS 'DEBIT PERIOD FROM',
WK579K033 IS 'DEBIT DATE',
WK579K032 IS 'CALCULATION PERIOD TO',
WK579K031 IS 'CALCULATION PERIOD FROM',
WK579K030 IS 'CALCULATION DATE',
WK579K029 IS 'CLOSURE TRANSFER CODE',
WK579K028 IS 'CANCELLATION CODE',
WK579K027 IS 'VALUE DATE',
WK579K026 IS 'NAC CODE 1 (Y/N) pha $phase',
WK579K025 IS 'CALCULATION STATUS',
WK579K024 IS 'ORDER NUMBER',
WK579K023 IS 'NAC - NETTO',
WK579K022 IS 'NAC - BRUTTO',
WK579K021 IS 'TECHNICAL DISCOUNT',
WK579K020 IS 'TECHNICAL SURCHARGE',
WK579K019 IS 'ADHOC DISCOUNT',
WK579K018 IS 'ADHOC SURCHARGE',
WK579K017 IS 'SC DISCOUNT AMOUNT',
WK579K016 IS 'SC DISCOUNT %',
WK579K015 IS 'NAC FLAT AMOUNT',
WK579K014 IS 'NAC DEFAULT AMOUNT',
WK579K013 IS 'ACCOUNT CURRENCY',
WK579K012 IS 'ACCOUNT TYPE',
WK579K011 IS 'ACCOUNT NUMBER',
WK579K010 IS 'DOMICILE',
WK579K009 IS 'CUSTOMER TYPE',
WK579K008 IS 'BUID',
WK579K007 IS 'TECH MUTATION DATE',
WK579K006 IS 'KEY RUNNING NUMBER',
WK579K005 IS 'KEY DUE DATE',
WK579K004 IS 'KEY POSITION AS DATE',
WK579K003 IS 'KEY CIF NUMBER',
WK579K002 IS 'KEY CALCULATION YEAR',
WK579K001 IS 'KEY PARTITION');
COMMIT;
------------------------------------------------------------------------
CREATE UNIQUE INDEX $creator.IWK579K1
ON $creator.TWK579K1
(WK579K001 ASC,
WK579K002 ASC,
WK579K003 ASC,
WK579K004 ASC,
WK579K005 ASC,
WK579K006 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
CLUSTER
(PART 1 VALUES('0'),
PART 2 VALUES('1'),
PART 3 VALUES('2'),
PART 4 VALUES('3'),
PART 5 VALUES('4'),
PART 6 VALUES('5'),
PART 7 VALUES('6'),
PART 8 VALUES('7'),
PART 9 VALUES('8'),
PART 10 VALUES('9'))
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES;
COMMIT;
------------------------------------------------------------------------
CREATE INDEX $creator.IWK579K2
ON $creator.TWK579K1
(WK579K001 ASC,
WK579K002 ASC,
WK579K003 ASC,
WK579K005 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 4 G;
COMMIT;
------------------------------------------------------------------------
CREATE INDEX $creator.IWK579K3
ON $creator.TWK579K1
(WK579K025 ASC,
WK579K024 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 4 G;
COMMIT;
------------------------------------------------------------------------
$@[
if \ ${? noViews } then
$=noViews=0
if $noViews then $@=[
-- no views because $"$noViews = 1"
$] else $@=[
CREATE VIEW $creator.VWK579K1A(WK579K001, WK579K002, WK579K003,
WK579K004, WK579K005, WK579K006, WK579K007, WK579K008,
WK579K009, WK579K010, WK579K011, WK579K012, WK579K013,
WK579K014, WK579K015, WK579K016, WK579K017, WK579K018,
WK579K019, WK579K020, WK579K021, WK579K022, WK579K023,
WK579K024, WK579K025, WK579K026, WK579K027, WK579K028,
WK579K029, WK579K030, WK579K031, WK579K032, WK579K033,
WK579K034, WK579K035, WK579K036, WK579K037, WK579K038,
WK579K039, WK579K040, WK579K041, WK579K042, WK579K043,
WK579K044, WK579K045, WK579K046, WK579K047, WK579K048,
WK579K049, WK579K050, WK579K051, WK579K052, WK579K053,
WK579K054, WK579K055, WK579K056, WK579K057, WK579K058,
WK579K059, WK579K060, WK579K061, WK579K062, WK579K063,
WK579K064, WK579K065, WK579K066) AS
SELECT WK579K001, WK579K002, WK579K003, WK579K004, WK579K005,
WK579K006, WK579K007, WK579K008, WK579K009, WK579K010,
WK579K011, WK579K012, WK579K013, WK579K014, WK579K015,
WK579K016, WK579K017, WK579K018, WK579K019, WK579K020,
WK579K021, WK579K022, WK579K023, WK579K024, WK579K025,
WK579K026, WK579K027, WK579K028, WK579K029, WK579K030,
WK579K031, WK579K032, WK579K033, WK579K034, WK579K035,
WK579K036, WK579K037, WK579K038, WK579K039, WK579K040,
WK579K041, WK579K042, WK579K043, WK579K044, WK579K045,
WK579K046, WK579K047, WK579K048, WK579K049, WK579K050,
WK579K051, WK579K052, WK579K053, WK579K054, WK579K055,
WK579K056, WK579K057, WK579K058, WK579K059, WK579K060,
WK579K061, WK579K062, WK579K063, WK579K064, WK579K065,
WK579K066
FROM $creator.TWK579K1 ;
$@[
if $phase <= 1 then $@=[
LABEL ON TABLE $creator.VWK579K1A IS 'NAC FEE SHDW TABLE';
$]
$]
LABEL ON $creator.VWK579K1A
(WK579K066 IS 'RESERVE',
WK579K065 IS 'RESERVE',
WK579K064 IS 'RESERVE',
WK579K063 IS 'RESERVE',
WK579K062 IS 'RESERVE',
WK579K061 IS 'RESERVE',
WK579K060 IS 'RESERVE',
WK579K059 IS 'RESERVE',
WK579K058 IS 'RESERVE',
WK579K057 IS 'MODEL pha $phase',
WK579K056 IS 'RESERVE',
WK579K055 IS 'RESERVE',
$@[
if $phase <= 1 then $@=[
WK579K054 IS 'RESERVE pha $phase',
$] else $@=[
WK579K053 IS 'RESERVE pha $phase',
$]
$]
WK579K052 IS 'RESERVE',
WK579K051 IS 'TS - RECALCULATED',
WK579K050 IS 'TS - DEBIT',
WK579K049 IS 'TS - SURCHRGE',
WK579K048 IS 'TS - DISCOUNT',
WK579K047 IS 'TS - CANCEL',
WK579K046 IS 'TS - INSERT',
WK579K045 IS 'PID - RECALCULATED',
WK579K044 IS 'PID - DEBIT',
WK579K043 IS 'PID - SURCHRGE',
WK579K042 IS 'PID - DISCOUNT',
WK579K041 IS 'PID - CANCEL',
WK579K040 IS 'PID - INSERT',
WK579K039 IS 'DEBIT INTERNAL TEXT',
WK579K038 IS 'DEBIT INTERNAL TEXT',
WK579K037 IS 'DEBIT FREQUENCY',
WK579K036 IS 'CALCULATION FREQUENCY',
WK579K035 IS 'DEBIT PERIOD TO',
WK579K034 IS 'DEBIT PERIOD FROM',
WK579K033 IS 'DEBIT DATE',
WK579K032 IS 'CALCULATION PERIOD TO',
WK579K031 IS 'CALCULATION PERIOD FROM',
WK579K030 IS 'CALCULATION DATE',
WK579K029 IS 'CLOSURE TRANSFER CODE',
WK579K028 IS 'CANCELLATION CODE',
WK579K027 IS 'VALUE DATE',
WK579K026 IS 'NAC CODE 1 (Y/N) pha $phase',
WK579K025 IS 'CALCULATION STATUS',
WK579K024 IS 'ORDER NUMBER',
WK579K023 IS 'NAC - NETTO',
WK579K022 IS 'NAC - BRUTTO',
WK579K021 IS 'TECHNICAL DISCOUNT',
WK579K020 IS 'TECHNICAL SURCHARGE',
WK579K019 IS 'ADHOC DISCOUNT',
WK579K018 IS 'ADHOC SURCHARGE',
WK579K017 IS 'SC DISCOUNT AMOUNT',
WK579K016 IS 'SC DISCOUNT %',
WK579K015 IS 'NAC FLAT AMOUNT',
WK579K014 IS 'NAC DEFAULT AMOUNT',
WK579K013 IS 'ACCOUNT CURRENCY',
WK579K012 IS 'ACCOUNT TYPE',
WK579K011 IS 'ACCOUNT NUMBER',
WK579K010 IS 'DOMICILE',
WK579K009 IS 'CUSTOMER TYPE',
WK579K008 IS 'BUID',
WK579K007 IS 'TECH MUTATION DATE',
WK579K006 IS 'KEY RUNNING NUMBER',
WK579K005 IS 'KEY DUE DATE',
WK579K004 IS 'KEY POSITION AS DATE',
WK579K003 IS 'KEY CIF NUMBER',
WK579K002 IS 'KEY CALCULATION YEAR',
WK579K001 IS 'KEY PARTITION');
COMMIT;
$]
$]
---||| end ddl testcase $mbr env $env phase $phase