zOs/SQL/VDPS004
SET CURRENT SQLID='OA1X';
--drop tablespace VV21A1X.vdps004;
commit;
CREATE TABLESPACE VDPS004
IN VV21A1X
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
LOGGED
NUMPARTS 30
BUFFERPOOL BP32K
LOCKSIZE ROW
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255 DSSIZE 16 G
;
--
CREATE TABLE OA1X.VTINSTRFLATDATA
(INSTRUMENTID INTEGER NOT NULL,
PAYFREQUENCY INTEGER WITH DEFAULT NULL,
CREATETIMESTAMP VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
UPDATETIMESTAMP VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
ISCALLFORTXREONLY SMALLINT WITH DEFAULT NULL,
ISREDEEMABLE SMALLINT WITH DEFAULT NULL,
ISRETRACTABLE SMALLINT WITH DEFAULT NULL,
ISCONVERTBYHOLDER SMALLINT WITH DEFAULT NULL,
ISCONVERTBYISSUER SMALLINT WITH DEFAULT NULL,
HASVOTINGRIGHTNO SMALLINT WITH DEFAULT NULL,
HASREDMPINVARCURR SMALLINT WITH DEFAULT NULL,
HASMULTIUNDERLYERS SMALLINT WITH DEFAULT NULL,
CSMAINTRADPLACE INTEGER WITH DEFAULT NULL,
ORIGINALISSUER INTEGER WITH DEFAULT NULL,
ISSUER INTEGER WITH DEFAULT NULL,
UNDERINSTRUMENT INTEGER WITH DEFAULT NULL,
NOMINALCURR INTEGER WITH DEFAULT NULL,
CSRISKCURRENCY INTEGER WITH DEFAULT NULL,
CSRISKCOUNTRY INTEGER WITH DEFAULT NULL,
CSRISINSTRUMENT INTEGER WITH DEFAULT NULL,
REPAYPRICECURR INTEGER WITH DEFAULT NULL,
ISSUEPRICECURR INTEGER WITH DEFAULT NULL,
EXERCISECURR INTEGER WITH DEFAULT NULL,
MAINDEPOSITARY INTEGER WITH DEFAULT NULL,
NOMINALAMOUNT FLOAT WITH DEFAULT NULL,
MINDENOMINATION FLOAT WITH DEFAULT NULL,
EFFINTERESTRATE FLOAT WITH DEFAULT NULL,
REPAYPRICEAMOUNT FLOAT WITH DEFAULT NULL,
REPAYPRICERATE FLOAT WITH DEFAULT NULL,
POOLFACTOR FLOAT WITH DEFAULT NULL,
VOTINGRIGHTNO FLOAT WITH DEFAULT NULL,
OUTSTANDINGCAPITAL FLOAT WITH DEFAULT NULL,
ISSUEDCAPITAL FLOAT WITH DEFAULT NULL,
OUTSTANDCAPITALNO FLOAT WITH DEFAULT NULL,
ISSUEDCAPITALNO FLOAT WITH DEFAULT NULL,
ISSUEAMOUNT FLOAT WITH DEFAULT NULL,
ISSUEPRICE FLOAT WITH DEFAULT NULL,
EXERCISEAMOUNT FLOAT WITH DEFAULT NULL,
MINEXERCISEAMOUNT FLOAT WITH DEFAULT NULL,
HELDAMOUNT FLOAT WITH DEFAULT NULL,
PAYAMOUNT FLOAT WITH DEFAULT NULL,
INCOMETAXRATE FLOAT WITH DEFAULT NULL,
PREMIUMRATE FLOAT WITH DEFAULT NULL,
PARTICIPATIONRATE FLOAT WITH DEFAULT NULL,
REBATE FLOAT WITH DEFAULT NULL,
CSRATING CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
CSRISKINDIDSYMBOL CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
CSRISKINDSCHEMESYM CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
CSRISKINSTGRPIDSYM CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
CSRISKINSGRPSCHSYM CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
MDYRATING CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
SPRATING CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
FITCHRATING CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
LISTINGSYMBOLTKH CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
LISTINGSYMBOLRIC CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
LISTINGSYMBOLBBI CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
NOMINALCURR_I1 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
NOMINALCURR_I2 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
NOMINALCURR_WS80 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
CSRISKCURRENCY_I1 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
CSRISKCURRENCY_I2 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
CSRISKCURRENCY_WS80 CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSRISINSTRUMENT_CH CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
CSRISINSTRUMENT_ISIN CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSRISINSTRUMENT_CSFI CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSRISKCOUNTRY_A2 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
CSRISKCOUNTRY_WS80 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
REPAYPRICECURR_I1 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
REPAYPRICECURR_I2 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
REPAYPRICECURR_WS80 CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
ISSUEPRICECURR_I1 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
ISSUEPRICECURR_I2 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
ISSUEPRICECURR_WS80 CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
EXERCISECURR_I1 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
EXERCISECURR_I2 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
EXERCISECURR_WS80 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
CSMAINTRADPLACE_BCN CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSMAINTRADPLACE_WS80 CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSMAINTRADPLACE_RTX CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSMAINTRADPLACE_BBX CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
UNDERINSTRUMENT_CH CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
UNDERINSTRUMENT_ISIN CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
UNDERINSTRUMENT_CSFI CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
ORIGINALISSUER_GK CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
ISSUER_GK CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
MAINDEPOSITARY_GK CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
EXPIRATIONDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
MATURITYDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
FIRSTPAYDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
FIRSTPYDATEPERYEAR VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
INTRSTDVDENFRDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSRATINGDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
MDYRATINGDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
SPRATINGDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
FITCHRATINGDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
SUBSCRBEGINDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
PAYMENTDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
EXERCISEBEGINDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
SUBSCRPLNDENDDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
EXERCISEENDDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
BVGTYPE SMALLINT WITH DEFAULT NULL,
CREDITWORTHINESSTP SMALLINT WITH DEFAULT NULL,
COMMISSIONCODETYPE SMALLINT WITH DEFAULT NULL,
UNITTYPE SMALLINT WITH DEFAULT NULL,
CSUNITTYPE SMALLINT WITH DEFAULT NULL,
EXPIRATIONTYPE SMALLINT WITH DEFAULT NULL,
STATUSTYPE SMALLINT WITH DEFAULT NULL,
REGISTRATIONTYPE SMALLINT WITH DEFAULT NULL,
BORROWERCATTYPE SMALLINT WITH DEFAULT NULL,
CSLISTINGCODETYPE SMALLINT WITH DEFAULT NULL,
CSSTATUSTYPE SMALLINT WITH DEFAULT NULL,
STATUSDETAILTYPE SMALLINT WITH DEFAULT NULL,
INCOMEMODETYPE SMALLINT WITH DEFAULT NULL,
INTERESTCALCTYPE SMALLINT WITH DEFAULT NULL,
INTERESTTYPE SMALLINT WITH DEFAULT NULL,
PYFREQUNITTYPE SMALLINT WITH DEFAULT NULL,
SUBSCRMETHODTYPE SMALLINT WITH DEFAULT NULL,
PLACEMENTTYPE SMALLINT WITH DEFAULT NULL,
PAYDIRECTIONTYPE SMALLINT WITH DEFAULT NULL,
OPTIONTYPE SMALLINT WITH DEFAULT NULL,
SETTLESTYLETYPE SMALLINT WITH DEFAULT NULL,
CSFUNDTYPE SMALLINT WITH DEFAULT NULL,
TRADINGSTATUSTYPE SMALLINT WITH DEFAULT NULL,
LISTINGSTATUSTYPE SMALLINT WITH DEFAULT NULL,
EXERCISEPLACE VARCHAR(254) FOR SBCS DATA
WITH DEFAULT NULL,
NAME35_G VARCHAR(35) FOR SBCS DATA
WITH DEFAULT NULL,
NAME35_E VARCHAR(35) FOR SBCS DATA
WITH DEFAULT NULL,
NAME35_F VARCHAR(35) FOR SBCS DATA
WITH DEFAULT NULL,
NAME35_I VARCHAR(35) FOR SBCS DATA
WITH DEFAULT NULL,
NAME35_N VARCHAR(35) FOR SBCS DATA
WITH DEFAULT NULL,
NAME45_G VARCHAR(45) FOR SBCS DATA
WITH DEFAULT NULL,
NAME45_E VARCHAR(45) FOR SBCS DATA
WITH DEFAULT NULL,
NAME45_F VARCHAR(45) FOR SBCS DATA
WITH DEFAULT NULL,
NAME45_I VARCHAR(45) FOR SBCS DATA
WITH DEFAULT NULL,
NAME45_N VARCHAR(45) FOR SBCS DATA
WITH DEFAULT NULL,
SHORTNAME_G VARCHAR(80) FOR SBCS DATA
WITH DEFAULT NULL,
SHORTNAME_E VARCHAR(80) FOR SBCS DATA
WITH DEFAULT NULL,
SHORTNAME_F VARCHAR(80) FOR SBCS DATA
WITH DEFAULT NULL,
SHORTNAME_I VARCHAR(80) FOR SBCS DATA
WITH DEFAULT NULL,
SHORTNAME_N VARCHAR(80) FOR SBCS DATA
WITH DEFAULT NULL,
PREPARTICLE_G VARCHAR(60) FOR SBCS DATA
WITH DEFAULT NULL,
PREPARTICLE_E VARCHAR(60) FOR SBCS DATA
WITH DEFAULT NULL,
PREPARTICLE_F VARCHAR(60) FOR SBCS DATA
WITH DEFAULT NULL,
PREPARTICLE_I VARCHAR(60) FOR SBCS DATA
WITH DEFAULT NULL,
PREPARTICLE_N VARCHAR(60) FOR SBCS DATA
WITH DEFAULT NULL,
POSTPARTICLE_G VARCHAR(60) FOR SBCS DATA
WITH DEFAULT NULL,
POSTPARTICLE_E VARCHAR(60) FOR SBCS DATA
WITH DEFAULT NULL,
POSTPARTICLE_F VARCHAR(60) FOR SBCS DATA
WITH DEFAULT NULL,
POSTPARTICLE_I VARCHAR(60) FOR SBCS DATA
WITH DEFAULT NULL,
POSTPARTICLE_N VARCHAR(60) FOR SBCS DATA
WITH DEFAULT NULL,
ISINTERNAL SMALLINT WITH DEFAULT NULL,
ISOTC SMALLINT WITH DEFAULT NULL,
HASWITHHOLDINGTAX SMALLINT WITH DEFAULT NULL,
HASFEDERALSTAMPDUTY SMALLINT WITH DEFAULT NULL,
ISCSMIFIDCOMPLEX SMALLINT WITH DEFAULT NULL,
MOSTLIQUIDMARKETLOCATION INTEGER WITH DEFAULT NULL,
MOSTLIQUIDMARKETLOCATION_A2 CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
MOSTLIQUIDMARKETLOCATION_A3 CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
MOSTLIQUIDMARKETLOCATION_WS80 CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
MDYWATCHLISTSTATUSTYPE SMALLINT WITH DEFAULT NULL,
SPWATCHLISTSTATUSTYPE SMALLINT WITH DEFAULT NULL,
FITCHWATCHLISTSTATUSTYPE SMALLINT WITH DEFAULT NULL,
OPTIONSTYLETYPE SMALLINT WITH DEFAULT NULL,
CSMAINTRADINGCURR INTEGER WITH DEFAULT NULL,
CSMAINTRADINGCURR_I1 CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSMAINTRADINGCURR_I2 CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSMAINTRADINGCURR_WS80 CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSRISKCOUNTRY_A3 CHAR(30) FOR SBCS DATA WITH DEFAULT NULL,
LIQUIDATIONRIGHTTYPE SMALLINT WITH DEFAULT NULL,
CSMAINPARENTTRADINGPLACE INTEGER WITH DEFAULT NULL,
CSMAINPARENTTRADINGPLACE_BCN CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSMAINPARENTTRADINGPLACE_RTX CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSMAINPARENTTRADINGPLACE_MIC CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSMAINPARENTTRADINGPLACE_BBX CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSMAINPARENTTRADINGPLACE_WS80 CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
ISMANAGEDBYCS SMALLINT WITH DEFAULT NULL,
EVENTFUNCTIONTYPE SMALLINT WITH DEFAULT NULL,
MAINLANGUAGETYPE SMALLINT WITH DEFAULT NULL,
EFFECTIVEDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
ISSUEPRICERATE FLOAT WITH DEFAULT NULL,
TITELARTZUSATZTEXT VARCHAR(12) FOR SBCS DATA
WITH DEFAULT NULL,
BORROWINGLIMITTYPE SMALLINT WITH DEFAULT NULL,
ISVSTQUALITYCHECKED SMALLINT WITH DEFAULT NULL,
MININCREMENTAMOUNT FLOAT WITH DEFAULT NULL,
DEL_P42GENERATION SMALLINT WITH DEFAULT NULL,
ACCRUEDINTERESTTYPE SMALLINT WITH DEFAULT NULL,
DEL_INSTRUMENTUPDATETIMESTAMP VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
DEL_INSTRUMENTCREATETIMESTAMP VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSRELEVANCEDATE VARCHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
CSMAINTRADPLACE_MIC CHAR(30) FOR SBCS DATA
WITH DEFAULT NULL,
HASHOLDINGS SMALLINT WITH DEFAULT NULL,
HASHOLDINGSSBIP SMALLINT WITH DEFAULT NULL,
NAME_G VARCHAR(255) FOR SBCS DATA
WITH DEFAULT NULL,
NAME_E VARCHAR(255) FOR SBCS DATA
WITH DEFAULT NULL,
NAME_F VARCHAR(255) FOR SBCS DATA
WITH DEFAULT NULL,
NAME_I VARCHAR(255) FOR SBCS DATA
WITH DEFAULT NULL,
NAME_N VARCHAR(255) FOR SBCS DATA
WITH DEFAULT NULL,
CONSTRAINT INSTRUMENTID
PRIMARY KEY (INSTRUMENTID))
partition by (instrumentid)
( partition 1 ending at ( 22760349)
, partition 2 ending at ( 28830651)
, partition 3 ending at ( 38861452)
, partition 4 ending at ( 45038740)
, partition 5 ending at ( 49047582)
, partition 6 ending at ( 53000000)
, partition 7 ending at ( 57000000)
, partition 8 ending at ( 61000000)
, partition 9 ending at ( 65000000)
, partition 10 ending at ( 69000000)
, partition 11 ending at ( 73000000)
, partition 12 ending at ( 77000000)
, partition 13 ending at ( 81000000)
, partition 14 ending at ( 85000000)
, partition 15 ending at ( 89000000)
, partition 16 ending at ( 93000000)
, partition 17 ending at ( 97000000)
, partition 18 ending at (101000000)
, partition 19 ending at (105000000)
, partition 20 ending at (109000000)
, partition 21 ending at (113000000)
, partition 22 ending at (117000000)
, partition 23 ending at (121000000)
, partition 24 ending at (125000000)
, partition 25 ending at (129000000)
, partition 26 ending at (133000000)
, partition 27 ending at (137000000)
, partition 28 ending at (141000000)
, partition 29 ending at (145000000)
, partition 30 ending at (149000000)
)
IN VV21A1X.VDPS004
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE
APPEND NO ;
--
--
CREATE UNIQUE INDEX OA1X.VTXINSTRFLATDATA1
ON OA1X.VTINSTRFLATDATA
(INSTRUMENTID ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
PARTITIONED
CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES
;
--
commit ;
--
-- restliche Indexe, views usw. wie bisher