zOs/TX/WK576DDL
------------$-{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.A576A ;
drop TABLESPACE $db.A576B ;
commit;
$@[
if $phase > 0 then $@=[
------------------------------------------------------------------------
CREATE TABLESPACE A576A
IN $db
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
FREEPAGE 5 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255 SEGSIZE 64
;
CREATE TABLE $creator.TWK576A1
(WB510001 CHAR(4) FOR SBCS DATA NOT NULL,
WB510002 CHAR(12) FOR SBCS DATA NOT NULL,
WB510003 CHAR(1) FOR SBCS DATA NOT NULL,
WB510004 CHAR(20) FOR SBCS DATA NOT NULL,
WB510005 CHAR(5) FOR SBCS DATA NOT NULL,
WB510006 CHAR(3) FOR SBCS DATA NOT NULL,
WB510007 DECIMAL(15, 3) NOT NULL,
WB510008 DECIMAL(15, 3) NOT NULL,
WB510009 DECIMAL(13, 5) NOT NULL,
WB510010 CHAR(2) FOR SBCS DATA NOT NULL,
WB510011 CHAR(8) FOR SBCS DATA NOT NULL,
WB510012 CHAR(3) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WB510013 DATE NOT NULL WITH DEFAULT,
WB510014 TIMESTAMP NOT NULL,
WB510015 TIMESTAMP NOT NULL,
WB510016 CHAR(64) FOR BIT DATA NOT NULL,
WB510017 CHAR(64) FOR BIT DATA NOT NULL,
WB510018 DECIMAL(13, 5) NOT NULL WITH DEFAULT,
WB510019 DATE NOT NULL WITH DEFAULT,
WB510020 CHAR(2) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WB510021 CHAR(13) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WB510022 CHAR(4) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WB510023 CHAR(4) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WB510024 CHAR(13) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WB510025 CHAR(3) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WB510026 DATE NOT NULL WITH DEFAULT,
WB510027 DATE NOT NULL WITH DEFAULT,
WB510028 DATE NOT NULL WITH DEFAULT,
WB510029 CHAR(2) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WB510030 DATE NOT NULL WITH DEFAULT,
WB510031 CHAR(2) FOR SBCS DATA NOT NULL
WITH DEFAULT,
WB510032 TIMESTAMP NOT NULL WITH DEFAULT
$@[
if $phase = 2 then $@=[
, WB510033 CHAR(10) FOR SBCS DATA NOT NULL
WITH DEFAULT
$** , WB510034 CHAR(10) FOR SBCS DATA NOT NULL
$] else if $phase >= 2 then $@=[
$** , WB510033bef CHAR(22) FOR SBCS DATA NOT NULL
$** WITH DEFAULT
, WB510033 CHAR(2) FOR SBCS DATA NOT NULL
WITH DEFAULT
$** , WB510034 CHAR(7) FOR SBCS DATA NOT NULL
$** WITH DEFAULT
$]
$]
, PRIMARY KEY (WB510004)
)
IN $db.A576A
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE;
COMMENT ON TABLE $creator.TWK576A1
IS 'Order Daten WS80-Börse (P90-Extrakt)';
LABEL ON $creator.TWK576A1
(
$@[
if $phase >= 2 then $@=[
WB510033 IS 'ACTION_ID',
$]
$]
WB510032 IS 'TS_MUTATION',
WB510031 IS 'LAUFNR_GA90',
WB510030 IS 'STATUS_61_DATE',
WB510029 IS 'ANZAHL_GA90',
WB510028 IS 'NEXT_VERARB',
WB510027 IS 'LAST_VERARB',
WB510026 IS 'PFLEGE_DATE',
WB510025 IS 'DEPOT_ART_KEY',
WB510024 IS 'DEPOT',
WB510023 IS 'INT_HANDELSBKST',
WB510022 IS 'KUND_BKST',
WB510021 IS 'DEPOT_EBSTA',
WB510020 IS 'AUFTR_PHASE',
WB510019 IS 'REKAP_DATE',
WB510018 IS 'LIMITE_AUSLOESUNG',
WB510017 IS 'STATUS',
WB510016 IS 'EIGENSCHAFT',
WB510015 IS 'TS_INSERT',
WB510014 IS 'EINGANGSZEIT',
WB510013 IS 'GUELTIG_BIS',
WB510012 IS 'BOERSENPLATZ',
WB510011 IS 'AUFTRAGSART',
WB510010 IS 'LIQ_ULTIMO',
WB510009 IS 'LIMITE',
WB510008 IS 'PRAEMIE_AUFTRAG',
WB510007 IS 'NOMINAL_STUECK',
WB510006 IS 'GESCHAEFTSART',
WB510005 IS 'HANDELSKREIS',
WB510004 IS 'AUFTRAGSNUMMER',
WB510003 IS 'INT_HANDELSSTELLE',
WB510002 IS 'VALORENNUMMER',
WB510001 IS 'ABWICKLUNGSBKST');
--
COMMIT;
CREATE INDEX $creator.IWK576A3
ON $creator.TWK576A1
(WB510001 ASC,
WB510003 ASC,
WB510004 ASC,
WB510005 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 10 PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
--
COMMIT;
--
------------------------------------------------------------------------
-- Database=$db
-- Index=$creator.IWK576A1 On $creator.TWK576A1
------------------------------------------------------------------------
--
CREATE INDEX $creator.IWK576A1
ON $creator.TWK576A1
(WB510003 ASC,
WB510001 ASC,
WB510005 ASC,
WB510002 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 10 PCTFREE 10
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
------------------------------------------------------------------------
CREATE INDEX $creator.IWK576A2
ON $creator.TWK576A1
(WB510002 ASC,
WB510001 ASC,
WB510003 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 10 PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
------------------------------------------------------------------------
CREATE INDEX $creator.IWK576A4
ON $creator.TWK576A1
(WB510012 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 10 PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
------------------------------------------------------------------------
CREATE INDEX $creator.IWK576A5
ON $creator.TWK576A1
(WB510005 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 10 PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
------------------------------------------------------------------------
CREATE UNIQUE INDEX $creator.IWK576A0
ON $creator.TWK576A1
(WB510004 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 10 PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
------------------------------------------------------------------------
CREATE TABLESPACE A576B
IN $db
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
FREEPAGE 5 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255 SEGSIZE 64
;
CREATE TABLE $creator.TWK576B1
(WB5121 CHAR(1) FOR SBCS DATA NOT NULL,
WB5122 CHAR(3) FOR SBCS DATA NOT NULL,
WB5123 CHAR(20) FOR SBCS DATA NOT NULL,
$@[
if $phase = 2 then $@=[
WB5124 CHAR(10) FOR SBCS DATA NOT NULL
WITH DEFAULT,
$] else if $phase >= 3 then $@=[
WB5124RENA CHAR(8) FOR SBCS DATA NOT NULL
WITH DEFAULT,
$]
$]
PRIMARY KEY (WB5123,
WB5122,
WB5121))
IN $db.A576B
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE;
LABEL ON TABLE $creator.TWK576B1 IS 'Auftragsstati';
COMMENT ON TABLE $creator.TWK576B1
IS 'Order Daten WS80-Börse: Stati, Eigenschaften, Flags';
LABEL ON $creator.TWK576B1
(
$@[
if $phase = 2 then $@=[
WB5124 IS 'Action_ID',
$] else if $phase >= 3 then $@=[
WB5124Rena IS 'Action_ID Rena',
$]
$]
WB5123 IS 'Auftragsnummer',
WB5122 IS 'Kennung',
WB5121 IS 'Typ');
COMMIT;
------------------------------------------------------------------------
$@[
if $phase >= 2 then $@=[
CREATE INDEX $creator.IWK576B2
ON $creator.TWK576B1
(
$@[
if $phase <= 2 then $@=[
WB5124 ASC,
$] else if $phase >= 3 then $@=[
WB5124Rena ASC,
$]
$]
WB5123 ASC,
WB5122 ASC,
WB5121 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 10 PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
$]
$]
------------------------------------------------------------------------
CREATE INDEX $creator.IWK576B1
ON $creator.TWK576B1
(WB5122 ASC,
WB5121 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 10 PCTFREE 40
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
------------------------------------------------------------------------
CREATE UNIQUE INDEX $creator.IWK576B0
ON $creator.TWK576B1
(WB5123 ASC,
WB5122 ASC,
WB5121 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 10 PCTFREE 40
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
------------------------------------------------------------------------
--SET CURRENT PATH = '"SYSIBM"','"SYSFUN"',"SYSPROC","S100447" ;
CREATE VIEW $creator.VWB510A1V(ABWICKLUNGSBKST, VALORENNUMMER,
INT_HANDELSSTELLE, AUFTRAGSNUMMER, HANDELSKREIS, GESCHAEFTSART,
NOMINAL_STUECK, PRAEMIE_AUFTRAG, LIMITE, LIQ_ULTIMO,
AUFTRAGSART, BOERSENPLATZ, GUELTIG_BIS, EINGANGSZEIT, TS_INSERT,
EIGENSCHAFT, STATUS, LIMITE_AUSLOESUNG, REKAP_DATE, AUFTR_PHASE,
DEPOT_EBSTA, KUND_BKST, INT_HANDELSBKST, DEPOT, DEPOT_ART_KEY,
PFLEGE_DATE, LAST_VERARB, NEXT_VERARB, ANZAHL_GA90,
STATUS_61_DATE, LAUFNR_GA90, TS_MUTATION) AS
SELECT ALL WB510001, WB510002, WB510003, WB510004, WB510005,
WB510006, WB510007, WB510008, WB510009, WB510010, WB510011,
WB510012, WB510013, WB510014, WB510015, WB510016, WB510017,
WB510018, WB510019, WB510020, WB510021, WB510022, WB510023,
WB510024, WB510025, WB510026, WB510027, WB510028, WB510029,
WB510030, WB510031, WB510032
FROM $creator.TWK576A1 ;
COMMIT;
------------------------------------------------------------------------
$@[
if $phase >= 2 then $@=[
CREATE VIEW $creator.VWB510A2V(ABWICKLUNGSBKST, VALORENNUMMER,
INT_HANDELSSTELLE, AUFTRAGSNUMMER, HANDELSKREIS, GESCHAEFTSART,
NOMINAL_STUECK, PRAEMIE_AUFTRAG, LIMITE, LIQ_ULTIMO,
AUFTRAGSART, BOERSENPLATZ, GUELTIG_BIS, EINGANGSZEIT, TS_INSERT,
EIGENSCHAFT, STATUS, LIMITE_AUSLOESUNG, REKAP_DATE, AUFTR_PHASE,
DEPOT_EBSTA, KUND_BKST, INT_HANDELSBKST, DEPOT, DEPOT_ART_KEY,
PFLEGE_DATE, LAST_VERARB, NEXT_VERARB, ANZAHL_GA90,
STATUS_61_DATE, LAUFNR_GA90, TS_MUTATION, ACTION_ID) AS
SELECT ALL WB510001, WB510002, WB510003, WB510004, WB510005,
WB510006, WB510007, WB510008, WB510009, WB510010, WB510011,
WB510012, WB510013, WB510014, WB510015, WB510016, WB510017,
WB510018, WB510019, WB510020, WB510021, WB510022, WB510023,
WB510024, WB510025, WB510026, WB510027, WB510028, WB510029,
WB510030, WB510031, WB510032, WB510033
FROM $creator.TWK576A1 ;
$]
$]
------------------------------------------------------------------------
CREATE VIEW $creator.VWB512A1V(Typ, Kennung, Auftragsnummer) AS
SELECT ALL WB5121, WB5122, WB5123
FROM $creator.TWK576B1 ;
COMMIT;
------------------------------------------------------------------------
$@[
if $phase >= 2 then $@=[
CREATE VIEW $creator.VWB512A2V(TYP, KENNUNG, AUFTRAGSNUMMER, ACTION_ID)
AS
SELECT ALL WB5121, WB5122, WB5123,
$@[
if $phase <= 2 then $@=[
WB5124
$] else if $phase >= 3 then $@=[
WB5124Rena
$]
$]
FROM $creator.TWK576B1 ;
$]
$]
------------------------------------------------------------------------
ALTER TABLE $creator.TWK576B1 FOREIGN KEY CWB510A
(WB5123)
REFERENCES $creator.TWK576A1
(WB510004)
ON DELETE CASCADE ENFORCED ;
COMMIT;
$]
$]
---||| end ddl testcase $mbr env $env phase $phase