zOs/TX/WK931DDL
SET CURRENT SQLID='S100447';
drop tablespace $db.A931A;
commit;
CREATE TABLESPACE A931A
IN $db
USING STOGROUP GSMS
PRIQTY 128 SECQTY 7200
FREEPAGE 0 PCTFREE 0
GBPCACHE CHANGED
TRACKMOD YES
BUFFERPOOL BP32K
SEGSIZE 64
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 1;
CREATE TABLE $creator.TWK931A1
(INSTR_KEY INTEGER NOT NULL,
TRADING_CCY_NUM_CD CHAR(4) FOR SBCS DATA NOT NULL,
VENUE_CD CHAR(3) FOR SBCS DATA NOT NULL,
INSTR_ID_VDPS CHAR(12) FOR SBCS DATA NOT NULL,
FIRST_TRADING_DATE DATE WITH DEFAULT NULL,
LAST_TRADING_DATE DATE WITH DEFAULT NULL,
$@{
if $phase = 1 then $@[
TICKER_ID CHAR(20) FOR SBCS DATA NOT NULL,
$] else $@[
TICKER_ID CHAR(30) FOR SBCS DATA NOT NULL,
$]
if $phase < 3 then $@[
BLOOMBERG_ID CHAR(20) FOR SBCS DATA NOT NULL,
RIC_ID CHAR(20) FOR SBCS DATA NOT NULL,
$] else $@[
BLOOMBERG_ID CHAR(30) FOR SBCS DATA NOT NULL,
RIC_ID CHAR(30) FOR SBCS DATA NOT NULL,
$]
$}
LISTING_STATUS_TK INTEGER NOT NULL,
CLOSING_PRICE DECIMAL(31, 8) NOT NULL,
LISTING_GEN_FLAG SMALLINT NOT NULL,
LISTING_STATUS_CS INTEGER NOT NULL,
CLOSING_PRICE_TYPE CHAR(2) FOR SBCS DATA NOT NULL,
CLOSING_PRICE_DATE DATE WITH DEFAULT NULL,
CLOSING_PRICE_CCY CHAR(4) FOR SBCS DATA NOT NULL,
CLOSING_PRICE_VEN CHAR(3) FOR SBCS DATA NOT NULL,
MOST_LIQUID_FLAG SMALLINT NOT NULL,
MAIN_TR_PLACE_CS SMALLINT NOT NULL,
HOME_TR_PLACE_VDPS SMALLINT NOT NULL,
MULTIPLE_FACTOR DECIMAL(31, 8) NOT NULL,
LISTING_OTF_FLAG SMALLINT NOT NULL,
TRAD_LISTING_OTEX CHAR(50) FOR SBCS DATA NOT NULL,
TRADE_GROUP INTEGER NOT NULL,
DELETE_FLAG CHAR(1) FOR SBCS DATA NOT NULL,
DELETE_DATE CHAR(8) FOR SBCS DATA NOT NULL,
DELETE_USER CHAR(10) FOR SBCS DATA NOT NULL,
UPD_USER CHAR(10) FOR SBCS DATA NOT NULL,
UPD_TMSTMP TIMESTAMP NOT NULL,
INS_USER CHAR(10) FOR SBCS DATA NOT NULL,
INS_TMSTMP TIMESTAMP NOT NULL,
EXT_TRADING_CCY CHAR(4) FOR SBCS DATA NOT NULL
WITH DEFAULT,
BEST_OVERALL_VENUE SMALLINT NOT NULL WITH DEFAULT,
BEST_VENUE_PER_CCY SMALLINT NOT NULL WITH DEFAULT,
VENUE_CD_INT CHAR(3) FOR SBCS DATA NOT NULL
WITH DEFAULT,
CONSTRAINT INSTR_KEY
PRIMARY KEY (INSTR_KEY,
TRADING_CCY_NUM_CD,
VENUE_CD))
IN $db.A931A
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE;
LABEL ON TABLE $creator.TWK931A1 IS 'Listing Tabelle';
COMMENT ON TABLE $creator.TWK931A1 IS '?';
COMMIT;
CREATE UNIQUE INDEX $creator.IWK931A0
ON $creator.TWK931A1
(INSTR_KEY ASC,
TRADING_CCY_NUM_CD ASC,
VENUE_CD ASC)
USING STOGROUP GSMS
PRIQTY 12 SECQTY 7200
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
------------------------------------------------------------------------
CREATE INDEX $creator.IWK931A1
ON $creator.TWK931A1
(TICKER_ID ASC)
USING STOGROUP GSMS
PRIQTY 12 SECQTY 7200
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
------------------------------------------------------------------------
CREATE INDEX $creator.IWK931A2
ON $creator.TWK931A1
(BLOOMBERG_ID ASC)
USING STOGROUP GSMS
PRIQTY 12 SECQTY 7200
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
------------------------------------------------------------------------
CREATE INDEX $creator.IWK931A3
ON $creator.TWK931A1
(RIC_ID ASC)
USING STOGROUP GSMS
PRIQTY 12 SECQTY 7200
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
------------------------------------------------------------------------
CREATE INDEX $creator.IWK931A4
ON $creator.TWK931A1
(INSTR_ID_VDPS ASC)
USING STOGROUP GSMS
PRIQTY 12 SECQTY 7200
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
CREATE UNIQUE INDEX $creator.IWK931A5
ON $creator.TWK931A1
(INSTR_KEY ASC,
VENUE_CD ASC,
TRADING_CCY_NUM_CD ASC,
DELETE_FLAG ASC,
TICKER_ID ASC,
BLOOMBERG_ID ASC,
RIC_ID ASC,
MULTIPLE_FACTOR ASC)
USING STOGROUP GSMS
PRIQTY 12 SECQTY 7200
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
------------------------------------------------------------------------
CREATE TABLE $creator.TWK931H1
(INSTR_KEY INTEGER NOT NULL,
TRADING_CCY_NUM_CD CHAR(4) FOR SBCS DATA NOT NULL,
VENUE_CD CHAR(3) FOR SBCS DATA NOT NULL,
INSTR_ID_VDPS CHAR(12) FOR SBCS DATA NOT NULL,
FIRST_TRADING_DATE DATE WITH DEFAULT NULL,
LAST_TRADING_DATE DATE WITH DEFAULT NULL,
$@{
if $phase = 1 then $@[
TICKER_ID CHAR(20) FOR SBCS DATA NOT NULL,
$] else $@[
TICKER_ID CHAR(30) FOR SBCS DATA NOT NULL,
$]
if $phase < 3 then $@[
BLOOMBERG_ID CHAR(20) FOR SBCS DATA NOT NULL,
RIC_ID CHAR(20) FOR SBCS DATA NOT NULL,
$] else $@[
BLOOMBERG_ID CHAR(30) FOR SBCS DATA NOT NULL,
RIC_ID CHAR(30) FOR SBCS DATA NOT NULL,
$]
$}
LISTING_STATUS_TK INTEGER NOT NULL,
CLOSING_PRICE DECIMAL(31, 8) NOT NULL,
LISTING_GEN_FLAG SMALLINT NOT NULL,
LISTING_STATUS_CS INTEGER NOT NULL,
CLOSING_PRICE_TYPE CHAR(2) FOR SBCS DATA NOT NULL,
CLOSING_PRICE_DATE DATE WITH DEFAULT NULL,
CLOSING_PRICE_CCY CHAR(4) FOR SBCS DATA NOT NULL,
CLOSING_PRICE_VEN CHAR(3) FOR SBCS DATA NOT NULL,
MOST_LIQUID_FLAG SMALLINT NOT NULL,
MAIN_TR_PLACE_CS SMALLINT NOT NULL,
HOME_TR_PLACE_VDPS SMALLINT NOT NULL,
MULTIPLE_FACTOR DECIMAL(31, 8) NOT NULL,
LISTING_OTF_FLAG SMALLINT NOT NULL,
TRAD_LISTING_OTEX CHAR(50) FOR SBCS DATA NOT NULL,
TRADE_GROUP INTEGER NOT NULL,
DELETE_FLAG CHAR(1) FOR SBCS DATA NOT NULL,
DELETE_DATE CHAR(8) FOR SBCS DATA NOT NULL,
DELETE_USER CHAR(10) FOR SBCS DATA NOT NULL,
UPD_USER CHAR(10) FOR SBCS DATA NOT NULL,
UPD_TMSTMP TIMESTAMP NOT NULL,
INS_USER CHAR(10) FOR SBCS DATA NOT NULL,
INS_TMSTMP TIMESTAMP NOT NULL,
EXT_TRADING_CCY CHAR(4) FOR SBCS DATA NOT NULL
WITH DEFAULT,
BEST_OVERALL_VENUE SMALLINT NOT NULL WITH DEFAULT,
BEST_VENUE_PER_CCY SMALLINT NOT NULL WITH DEFAULT,
VENUE_CD_INT CHAR(3) FOR SBCS DATA NOT NULL
WITH DEFAULT,
CONSTRAINT INSTR_KEY
PRIMARY KEY (INSTR_KEY,
TRADING_CCY_NUM_CD,
VENUE_CD))
IN $db.A931A
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE;
CREATE UNIQUE INDEX $creator.IWK931H0
ON $creator.TWK931H1
(INSTR_KEY ASC,
TRADING_CCY_NUM_CD ASC,
VENUE_CD ASC)
USING STOGROUP GSMS
PRIQTY 12 SECQTY 7200
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
COMMIT;
------------------------------------------------------------------------
CREATE TABLE $creator.TWK931R1
(RESTRICT_KEY INTEGER NOT NULL GENERATED BY DEFAULT
AS IDENTITY
(START WITH 1, INCREMENT BY 1, CACHE 20, NO CYCLE,
NO ORDER, MAXVALUE 250000000, MINVALUE 1),
PREDECESSORID INTEGER WITH DEFAULT NULL,
CHANNELKEY SMALLINT NOT NULL,
PRIO SMALLINT NOT NULL,
BUID CHAR(4) FOR SBCS DATA NOT NULL,
ORDER_MODE INTEGER NOT NULL WITH DEFAULT,
INSTR_TYPE_CS CHAR(3) FOR SBCS DATA NOT NULL,
PID CHAR(10) FOR SBCS DATA NOT NULL,
CIF CHAR(12) FOR SBCS DATA NOT NULL,
VENUE_CD CHAR(3) FOR SBCS DATA NOT NULL,
SHORT_ALLOWED SMALLINT NOT NULL,
LEVEL CHAR(1) FOR SBCS DATA NOT NULL,
MAX_CHF_VALUE_BUY DECIMAL(31, 8) NOT NULL,
MAX_CHF_VALUE_SELL DECIMAL(31, 8) NOT NULL,
STATE SMALLINT NOT NULL,
INS_USER CHAR(10) FOR SBCS DATA NOT NULL,
INS_TMSTMP TIMESTAMP NOT NULL WITH DEFAULT,
INS_CONFIRM_USER CHAR(10) FOR SBCS DATA NOT NULL,
INS_CONFIRM_TMSTMP TIMESTAMP WITH DEFAULT NULL,
UPD_USER CHAR(10) FOR SBCS DATA NOT NULL,
UPD_TMSTMP TIMESTAMP WITH DEFAULT NULL,
UPD_CONFIRM_USER CHAR(10) FOR SBCS DATA NOT NULL,
UPD_CONFIRM_TMSTMP TIMESTAMP WITH DEFAULT NULL,
"COMMENT" VARCHAR(200) FOR SBCS DATA NOT NULL,
$@{
if $phase > 1 then $@[
BIC CHAR(11) FOR SBCS DATA NOT NULL
WITH DEFAULT,
$]
$}
CONSTRAINT RESTRICT_KEY
PRIMARY KEY (RESTRICT_KEY))
IN $db.A931A
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE;
LABEL ON TABLE $creator.TWK931R1 IS 'Auftrags - Wert';
COMMENT ON TABLE $creator.TWK931R1
$@{
if $phase = 1 then $@[
IS 'Beinhaltet Daten zum Wert eines uftrages';
$] else $@[
IS 'Beinhaltet Daten zum Wert eines Auftrages';
$]
$}
CREATE UNIQUE INDEX $creator.IWK931R0
ON $creator.TWK931R1
(RESTRICT_KEY ASC)
USING STOGROUP GSMS
PRIQTY 12 SECQTY 12
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
CREATE INDEX $creator.IWK931R2
ON $creator.TWK931R1
(CHANNELKEY ASC,
LEVEL ASC,
STATE ASC,
PRIO ASC)
USING STOGROUP GSMS
PRIQTY 12 SECQTY 12
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
PIECESIZE 2 G;
--
COMMIT;
--
------------------------------------------------------------------------
-- View=$creator.VWK931A1V
------------------------------------------------------------------------
--
--
$@{
if $phase <= 2 then $@[
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","pha12" ;
$] else $@[
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","$creator" ;
$]
$}
CREATE VIEW $creator.VWK931A1V AS
SELECT INSTR_KEY, TRADING_CCY_NUM_CD, VENUE_CD, INSTR_ID_VDPS,
FIRST_TRADING_DATE, LAST_TRADING_DATE, TICKER_ID,
BLOOMBERG_ID, RIC_ID, LISTING_STATUS_TK, CLOSING_PRICE,
LISTING_GEN_FLAG, LISTING_STATUS_CS, CLOSING_PRICE_TYPE,
CLOSING_PRICE_DATE, CLOSING_PRICE_CCY, CLOSING_PRICE_VEN,
MOST_LIQUID_FLAG, MAIN_TR_PLACE_CS, HOME_TR_PLACE_VDPS,
MULTIPLE_FACTOR, LISTING_OTF_FLAG, TRAD_LISTING_OTEX,
TRADE_GROUP, DELETE_FLAG, DELETE_DATE, DELETE_USER,
UPD_USER, UPD_TMSTMP, INS_USER, INS_TMSTMP,
EXT_TRADING_CCY, BEST_OVERALL_VENUE, BEST_VENUE_PER_CCY,
VENUE_CD_INT
FROM $creator.TWK931A1 ;
CREATE VIEW $creator.VWK931H1V AS
SELECT INSTR_KEY, TRADING_CCY_NUM_CD, VENUE_CD, INSTR_ID_VDPS,
FIRST_TRADING_DATE, LAST_TRADING_DATE, TICKER_ID,
BLOOMBERG_ID, RIC_ID, LISTING_STATUS_TK, CLOSING_PRICE,
LISTING_GEN_FLAG, LISTING_STATUS_CS, CLOSING_PRICE_TYPE,
CLOSING_PRICE_DATE, CLOSING_PRICE_CCY, CLOSING_PRICE_VEN,
MOST_LIQUID_FLAG, MAIN_TR_PLACE_CS, HOME_TR_PLACE_VDPS,
MULTIPLE_FACTOR, LISTING_OTF_FLAG, TRAD_LISTING_OTEX,
TRADE_GROUP, DELETE_FLAG, DELETE_DATE, DELETE_USER,
UPD_USER, UPD_TMSTMP, INS_USER, INS_TMSTMP,
EXT_TRADING_CCY, BEST_OVERALL_VENUE, BEST_VENUE_PER_CCY,
VENUE_CD_INT
FROM $creator.TWK931H1 ;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","$creator" ;
CREATE VIEW $creator.VWK931R1V AS
SELECT RESTRICT_KEY, PREDECESSORID, CHANNELKEY, PRIO, BUID,
ORDER_MODE, INSTR_TYPE_CS, PID, CIF, VENUE_CD,
SHORT_ALLOWED, LEVEL, MAX_CHF_VALUE_BUY,
MAX_CHF_VALUE_SELL, STATE, INS_USER, INS_TMSTMP,
INS_CONFIRM_USER, INS_CONFIRM_TMSTMP, UPD_USER, UPD_TMSTMP,
UPD_CONFIRM_USER, UPD_CONFIRM_TMSTMP, "COMMENT"
$@{
if $phase > 1 then $@[
, BIC
$]
$}
FROM $creator.TWK931R1 ;
--
COMMIT;
--
ALTER TABLE $creator.TWK931R1 FOREIGN KEY CWK931RK
(PREDECESSORID)
REFERENCES $creator.TWK931R1
(RESTRICT_KEY)
ON DELETE NO ACTION ENFORCED ;
--
COMMIT;
--
------------------------------------------------------------------------
-- ADB2GEN - End of generated DDL --
------------------------------------------------------------------------
--