zOs/TX/WK941DDL
------------$-{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.A941A;
commit;
CREATE TABLESPACE A941A
IN $db
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
FREEPAGE 5 PCTFREE 10
DSSIZE 4 G
NUMPARTS 256
BUFFERPOOL BP2
LOCKSIZE ANY
CLOSE NO
COMPRESS YES;
CREATE TABLE $creator.TNI250A101A
(CLSTRID CHAR(21) NOT NULL WITH DEFAULT,
PLATTFORMID CHAR(4) NOT NULL WITH DEFAULT '0000',
TSID CHAR(8) NOT NULL WITH DEFAULT 'BDLC0002',
SYSSIGN CHAR(1) NOT NULL WITH DEFAULT,
POSID CHAR(45) NOT NULL WITH DEFAULT,
ASOFDT DATE NOT NULL WITH DEFAULT,
DATAPOSAM CHAR(1) NOT NULL WITH DEFAULT,
UNITNOMBAL DECIMAL(31, 8) NOT NULL WITH DEFAULT,
MKTVALPC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
MKTVALVCOLD DECIMAL(31, 8) NOT NULL WITH DEFAULT,
MKTVALVCNEW DECIMAL(31, 8) NOT NULL WITH DEFAULT,
ACCRINTPC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
ACCRINTVCOLD DECIMAL(31, 8) NOT NULL WITH DEFAULT,
ACCRINTVCNEW DECIMAL(31, 8) NOT NULL WITH DEFAULT,
FWDMKTPRICE DECIMAL(13, 6) NOT NULL WITH DEFAULT,
FWDMARKETPRICE REAL NOT NULL WITH DEFAULT,
PREM DECIMAL(15, 3) NOT NULL WITH DEFAULT,
EXOTICOPTFLAG CHAR(1) NOT NULL WITH DEFAULT,
OPTMKTVALCHF DECIMAL(31, 8) NOT NULL WITH DEFAULT,
FUTURESTYLEFLAG CHAR(1) NOT NULL WITH DEFAULT,
PRICE DECIMAL(13, 6) NOT NULL WITH DEFAULT,
PRICEIND CHAR(1) NOT NULL WITH DEFAULT,
PRICEDT DATE NOT NULL WITH DEFAULT,
PRICECCYCD CHAR(4) NOT NULL WITH DEFAULT,
PRICETYPE CHAR(2) NOT NULL WITH DEFAULT,
STOCKEXCD CHAR(3) NOT NULL WITH DEFAULT,
DATAPOSRET CHAR(1) NOT NULL WITH DEFAULT,
PERSTARTDT DATE NOT NULL WITH DEFAULT,
PERENDDT DATE NOT NULL WITH DEFAULT,
SUMNCFGROSSPC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
SUMNCFGROSSVC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
SUMNCFNETOFTAXPC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
SUMNCFNETOFTAXVC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
SUMNCFNETOFFEEPC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
SUMNCFNETOFFEEVC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
SUMNCFNETOFTAXFEEPC DECIMAL(31, 8) NOT NULL
WITH DEFAULT,
SUMNCFNETOFTAXFEEVC DECIMAL(31, 8) NOT NULL
WITH DEFAULT,
SUMACFGROSSPC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
SUMACFGROSSVC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
SUMACFNETOFTAXPC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
SUMACFNETOFTAXVC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
SUMACFNETOFFEEPC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
SUMACFNETOFFEEVC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
SUMACFNETOFTAXFEEPC DECIMAL(31, 8) NOT NULL
WITH DEFAULT,
SUMACFNETOFTAXFEEVC DECIMAL(31, 8) NOT NULL
WITH DEFAULT,
RORGROSSPC DECIMAL(15, 9) NOT NULL WITH DEFAULT,
RORGROSSVC DECIMAL(15, 9) NOT NULL WITH DEFAULT,
RORNETOFFEEPC DECIMAL(15, 9) NOT NULL WITH DEFAULT,
RORNETOFFEEVC DECIMAL(15, 9) NOT NULL WITH DEFAULT,
RORNETOFTAXFEEPC DECIMAL(15, 9) NOT NULL WITH DEFAULT,
RORNETOFTAXFEEVC DECIMAL(15, 9) NOT NULL WITH DEFAULT,
RORNETOFTAXPC DECIMAL(15, 9) NOT NULL WITH DEFAULT,
RORNETOFTAXVC DECIMAL(15, 9) NOT NULL WITH DEFAULT,
IDNETOFFEEPC DECIMAL(15, 9) NOT NULL WITH DEFAULT,
DATAPOSFLO CHAR(1) NOT NULL WITH DEFAULT,
INCFGROSSPC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
INCFGROSSVC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
INCFWHTAXPC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
INCFWHTAXVC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
INCFADGPC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
INCFADGVC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
INCFVWGPC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
INCFVWGVC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
INCFIPCFEEPC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
INCFIPCFEEVC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
INCFRETFEEPC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
INCFRETFEEVC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
INTRXCOSTPC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
INTRXCOSTVC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
OUTCFGROSSPC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
OUTCFGROSSVC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
OUTCFWHTAXPC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
OUTCFWHTAXVC DECIMAL(31, 8) NOT NULL WITH DEFAULT,
OUTCFADGPC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
OUTCFADGVC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
OUTCFVWGPC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
OUTCFVWGVC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
OUTCFIPCFEEPC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
OUTCFIPCFEEVC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
OUTCFRETFEEPC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
OUTCFRETFEEVC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
OUTTRXCOSTPC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
OUTTRXCOSTVC DECIMAL(15, 3) NOT NULL WITH DEFAULT,
PIDCHANGE CHAR(8) NOT NULL WITH DEFAULT,
TIMESTMPCHG TIMESTAMP NOT NULL WITH DEFAULT,
CONSTRAINT CLSTRID
PRIMARY KEY (CLSTRID,
POSID,
ASOFDT,
TSID,
PLATTFORMID))
IN $db.A941A
PARTITION BY (CLSTRID ASC,
POSID ASC)
(PART 1 VALUES('S00030945517750009999'),
PART 2 VALUES('S00360811773250019999'),
PART 3 VALUES('S00600022854950009999'),
PART 4 VALUES('S00600032175150019999'),
PART 5 VALUES('S00600231082150009999'),
PART 6 VALUES('S00600315688350009999'),
PART 7 VALUES('S00600380260250009999'),
PART 8 VALUES('S00600469651250039999'),
PART 9 VALUES('S00600618026250009999'),
PART 10 VALUES('S00600749862250009999'),
PART 11 VALUES('S00600887045150009999'),
PART 12 VALUES('S00601483821850029999'),
PART 13 VALUES('S00610936449350009999'),
PART 14 VALUES('S00650168366150009999'),
PART 15 VALUES('S00650241279350009999'),
PART 16 VALUES('S00650296462150009999'),
PART 17 VALUES('S00650361848450009999'),
PART 18 VALUES('S00650395957550009999'),
PART 19 VALUES('S00650430890650009999'),
PART 20 VALUES('S00650460810350049999'),
PART 21 VALUES('S00650486985550009999'),
PART 22 VALUES('S00650519354150009999'),
PART 23 VALUES('S00650546527850009999'),
PART 24 VALUES('S00650563590750009999'),
PART 25 VALUES('S00650604254750009999'),
PART 26 VALUES('S00650637267250049999'),
PART 27 VALUES('S00650666285950009999'),
PART 28 VALUES('S00650691218550039999'),
PART 29 VALUES('S00650719516950009999'),
PART 30 VALUES('S00650752745950009999'),
PART 31 VALUES('S00650786014250009999'),
PART 32 VALUES('S00650816936850009999'),
PART 33 VALUES('S00650841086550009999'),
PART 34 VALUES('S00650882828250009999'),
PART 35 VALUES('S00650919874650009999'),
PART 36 VALUES('S00650981968650009999'),
PART 37 VALUES('S00680132412950009999'),
PART 38 VALUES('S00680166318750009999'),
PART 39 VALUES('S00680213517150009999'),
PART 40 VALUES('S00680255698850009999'),
PART 41 VALUES('S00680366077650009999'),
PART 42 VALUES('S00680540891850009999'),
PART 43 VALUES('S00680736905750009999'),
PART 44 VALUES('S00680889597650009999'),
PART 45 VALUES('S00710360590950009999'),
PART 46 VALUES('S00710748536850009999'),
PART 47 VALUES('S00770310601850009999'),
PART 48 VALUES('S00770676467950009999'),
PART 49 VALUES('S00830617295750009999'),
PART 50 VALUES('S00900763015550019999'),
PART 51 VALUES('S00940379967750019999'),
PART 52 VALUES('S00940826791650009999'),
PART 53 VALUES('S01120757146850009999'),
PART 54 VALUES('S01440336770350029999'),
PART 55 VALUES('S01600251349250009999'),
PART 56 VALUES('S01600627544750009999'),
PART 57 VALUES('S01601563597950009999'),
PART 58 VALUES('S01650860379550009999'),
PART 59 VALUES('S01720205789650009999'),
PART 60 VALUES('S01720226120550009999'),
PART 61 VALUES('S01720400135950019999'),
PART 62 VALUES('S01720576366050009999'),
PART 63 VALUES('S01720797959750019999'),
PART 64 VALUES('S01720997495950009999'),
PART 65 VALUES('S01750770350050009999'),
PART 66 VALUES('S01820497367550009999'),
PART 67 VALUES('S01870517405550009999'),
PART 68 VALUES('S01930526668250009999'),
PART 69 VALUES('S02070264614050009999'),
PART 70 VALUES('S02130881906450009999'),
PART 71 VALUES('S02280817656850009999'),
PART 72 VALUES('S02300659035750009999'),
PART 73 VALUES('S02350419104450019999'),
PART 74 VALUES('S02510018006850009999'),
PART 75 VALUES('S02510150120850009999'),
PART 76 VALUES('S02510229810450009999'),
PART 77 VALUES('S02510292946550009999'),
PART 78 VALUES('S02510347647250019999'),
PART 79 VALUES('S02510392410650009999'),
PART 80 VALUES('S02510439885950009999'),
PART 81 VALUES('S02510491784750009999'),
PART 82 VALUES('S02510544327050009999'),
PART 83 VALUES('S02510595111950009999'),
PART 84 VALUES('S02510651330150009999'),
PART 85 VALUES('S02510695910550009999'),
PART 86 VALUES('S02510732273950009999'),
PART 87 VALUES('S02510740476050009999'),
PART 88 VALUES('S02510748448850009999'),
PART 89 VALUES('S02510756344250009999'),
PART 90 VALUES('S02510763444750009999'),
PART 91 VALUES('S02510793203250009999'),
PART 92 VALUES('S02510852846550009999'),
PART 93 VALUES('S02510899889250009999'),
PART 94 VALUES('S02510939316150009999'),
PART 95 VALUES('S02510982825750009999'),
PART 96 VALUES('S02540392375650009999'),
PART 97 VALUES('S02700490680350019999'),
PART 98 VALUES('S02740190455250009999'),
PART 99 VALUES('S02880509832050009999'),
PART 100 VALUES('S03230108026250009999'),
PART 101 VALUES('S03390770309150009999'),
PART 102 VALUES('S03560898919050009999'),
PART 103 VALUES('S03810169125750009999'),
PART 104 VALUES('S03830489992950009999'),
PART 105 VALUES('S04000121689050009999'),
PART 106 VALUES('S04000367930850009999'),
PART 107 VALUES('S04000762509150019999'),
PART 108 VALUES('S04050587911450009999'),
PART 109 VALUES('S04250011302750029999'),
PART 110 VALUES('S04250027442050009999'),
PART 111 VALUES('S04250225513950009999'),
PART 112 VALUES('S04250404961750009999'),
PART 113 VALUES('S04250637228850009999'),
PART 114 VALUES('S04250837622150009999'),
PART 115 VALUES('S04460323671550009999'),
PART 116 VALUES('S04510596349550009999'),
PART 117 VALUES('S04550326163850009999'),
PART 118 VALUES('S04560172700950009999'),
PART 119 VALUES('S04560263883250009999'),
PART 120 VALUES('S04560311371750009999'),
PART 121 VALUES('S04560347143550019999'),
PART 122 VALUES('S04560437144250009999'),
PART 123 VALUES('S04560557817250009999'),
PART 124 VALUES('S04560674661350019999'),
PART 125 VALUES('S04560767131550009999'),
PART 126 VALUES('S04560874888550009999'),
PART 127 VALUES('S04560983224350009999'),
PART 128 VALUES('S04630167103250009999'),
PART 129 VALUES('S04630396946250009999'),
PART 130 VALUES('S04630722642150009999'),
PART 131 VALUES('S04631011919350009999'),
PART 132 VALUES('S04860263347350019999'),
PART 133 VALUES('S05030375083450009999'),
PART 134 VALUES('S05040324438950009999'),
PART 135 VALUES('S05060096253850009999'),
PART 136 VALUES('S05120253343150009999'),
PART 137 VALUES('S05160279823050009999'),
PART 138 VALUES('S05170787800150009999'),
PART 139 VALUES('S05180647452950009999'),
PART 140 VALUES('S05220980166250009999'),
PART 141 VALUES('S05270011422250009999'),
PART 142 VALUES('S05310055800550019999'),
PART 143 VALUES('S05310864009650009999'),
PART 144 VALUES('S05390194578750009999'),
PART 145 VALUES('S05400584490050009999'),
PART 146 VALUES('S05430304138950009999'),
PART 147 VALUES('S05440471113250009999'),
PART 148 VALUES('S05480463903150009999'),
PART 149 VALUES('S05530324506450009999'),
PART 150 VALUES('S05530735986350009999'),
PART 151 VALUES('S05540237826250009999'),
PART 152 VALUES('S05540505384450019999'),
PART 153 VALUES('S05540651171550009999'),
PART 154 VALUES('S05540917194050029999'),
PART 155 VALUES('S05560694863750009999'),
PART 156 VALUES('S05590898489650009999'),
PART 157 VALUES('S05640510022750009999'),
PART 158 VALUES('S05650273089050009999'),
PART 159 VALUES('S05730800181650009999'),
PART 160 VALUES('S05830808852250009999'),
PART 161 VALUES('S05910974012250009999'),
PART 162 VALUES('S05960554721950019999'),
PART 163 VALUES('S06140595004850019999'),
PART 164 VALUES('S06370039619550009999'),
PART 165 VALUES('S06370212479650009999'),
PART 166 VALUES('S06370668428150009999'),
PART 167 VALUES('S06440112787750009999'),
PART 168 VALUES('S06470273279850009999'),
PART 169 VALUES('S06470941143450009999'),
PART 170 VALUES('S06590119770350009999'),
PART 171 VALUES('S06590458724350009999'),
PART 172 VALUES('S06590955939650009999'),
PART 173 VALUES('S06740183568450009999'),
PART 174 VALUES('S06800062126250009999'),
PART 175 VALUES('S06850863860850009999'),
PART 176 VALUES('S07370600955550039999'),
PART 177 VALUES('S07470797326250009999'),
PART 178 VALUES('S07650404110350009999'),
PART 179 VALUES('S07770729105550009999'),
PART 180 VALUES('S07910638485750009999'),
PART 181 VALUES('S08110338499050009999'),
PART 182 VALUES('S08180945671450049999'),
PART 183 VALUES('S08230296102250009999'),
PART 184 VALUES('S08230791623850009999'),
PART 185 VALUES('S08350018565450009999'),
PART 186 VALUES('S08350050551950009999'),
PART 187 VALUES('S08350084279150009999'),
PART 188 VALUES('S08350110666750019999'),
PART 189 VALUES('S08350137895950009999'),
PART 190 VALUES('S08350152297250009999'),
PART 191 VALUES('S08350161879850009999'),
PART 192 VALUES('S08350174337550009999'),
PART 193 VALUES('S08350181676350029999'),
PART 194 VALUES('S08350190983450009999'),
PART 195 VALUES('S08350204385750009999'),
PART 196 VALUES('S08350225324050009999'),
PART 197 VALUES('S08350247715650009999'),
PART 198 VALUES('S08350268919650009999'),
PART 199 VALUES('S08350288679050009999'),
PART 200 VALUES('S08350308885450009999'),
PART 201 VALUES('S08350328089550009999'),
PART 202 VALUES('S08350348078950009999'),
PART 203 VALUES('S08350367986650009999'),
PART 204 VALUES('S08350385491350009999'),
PART 205 VALUES('S08350406013950009999'),
PART 206 VALUES('S08350425593250009999'),
PART 207 VALUES('S08350437613650009999'),
PART 208 VALUES('S08350454233450009999'),
PART 209 VALUES('S08350474347850009999'),
PART 210 VALUES('S08350491642450009999'),
PART 211 VALUES('S08350506002750009999'),
PART 212 VALUES('S08350517888650009999'),
PART 213 VALUES('S08350542059350009999'),
PART 214 VALUES('S08350557422550009999'),
PART 215 VALUES('S08350574425050009999'),
PART 216 VALUES('S08350588671550019999'),
PART 217 VALUES('S08350600803950009999'),
PART 218 VALUES('S08350629354550009999'),
PART 219 VALUES('S08350649460250009999'),
PART 220 VALUES('S08350667554250009999'),
PART 221 VALUES('S08350688967850009999'),
PART 222 VALUES('S08350699077050119999'),
PART 223 VALUES('S08350723327150009999'),
PART 224 VALUES('S08350736569050009999'),
PART 225 VALUES('S08350756569650009999'),
PART 226 VALUES('S08350787300450009999'),
PART 227 VALUES('S08350801677650009999'),
PART 228 VALUES('S08350817028050009999'),
PART 229 VALUES('S08350835961950009999'),
PART 230 VALUES('S08350848798550009999'),
PART 231 VALUES('S08350862161550009999'),
PART 232 VALUES('S08350874098850009999'),
PART 233 VALUES('S08350896175250019999'),
PART 234 VALUES('S08350897938650009999'),
PART 235 VALUES('S08350930349150009999'),
PART 236 VALUES('S08350948062750009999'),
PART 237 VALUES('S08350963266150009999'),
PART 238 VALUES('S08350973717550009999'),
PART 239 VALUES('S08350985717550009999'),
PART 240 VALUES('S08350994715850009999'),
PART 241 VALUES('S08351428590250009999'),
PART 242 VALUES('S08370530876750009999'),
PART 243 VALUES('S08390629049150009999'),
PART 244 VALUES('S08420542005250009999'),
PART 245 VALUES('S08420832973050009999'),
PART 246 VALUES('S08580090066850009999'),
PART 247 VALUES('S08600362166050009999'),
PART 248 VALUES('S08600853139250019999'),
PART 249 VALUES('S08620652532250009999'),
PART 250 VALUES('S08650840646250009999'),
PART 251 VALUES('S08720481057350009999'),
PART 252 VALUES('S08790335953450009999'),
PART 253 VALUES('S08790997253050009999'),
PART 254 VALUES('S08830682430750009999'),
PART 255 VALUES('S08860353174550009999'),
PART 256 VALUES('Z99999999999999999999'));
--
LABEL ON TABLE $creator.TNI250A101A IS 'POSITION RETURN';
--
------------------------------------------------------------------------
-- Database=$db
-- Index=$creator.INI250A1 On $creator.TNI250A101A
------------------------------------------------------------------------
--
CREATE UNIQUE INDEX $creator.INI250A1
ON $creator.TNI250A101A
(CLSTRID ASC,
POSID ASC,
ASOFDT ASC,
TSID ASC,
PLATTFORMID ASC)
USING STOGROUP GSMS
PRIQTY 2500000 SECQTY 250000
FREEPAGE 5 PCTFREE 10
CLUSTER
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 4 G;
CREATE UNIQUE INDEX $creator.INI250A2
ON $creator.TNI250A101A
(CLSTRID ASC,
POSID ASC,
PERENDDT ASC,
TSID ASC,
PLATTFORMID ASC,
ASOFDT ASC)
USING STOGROUP GSMS
PRIQTY 2500000 SECQTY 250000
FREEPAGE 5 PCTFREE 10
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 4 G;
CREATE INDEX $creator.INI250A3
ON $creator.TNI250A101A
(DATAPOSAM ASC)
USING STOGROUP GSMS
PRIQTY 2500000 SECQTY 250000
FREEPAGE 5 PCTFREE 10
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 4 G;
CREATE INDEX $creator.INI250A4
ON $creator.TNI250A101A
(DATAPOSRET ASC)
USING STOGROUP GSMS
PRIQTY 2500000 SECQTY 250000
FREEPAGE 5 PCTFREE 10
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 4 G;
CREATE INDEX $creator.INI250A5
ON $creator.TNI250A101A
(DATAPOSFLO DESC)
USING STOGROUP GSMS
PRIQTY 2500000 SECQTY 250000
FREEPAGE 5 PCTFREE 10
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 2 G;
CREATE INDEX $creator.INI250A6
ON $creator.TNI250A101A
(ASOFDT ASC)
USING STOGROUP GSMS
PRIQTY 2500000 SECQTY 250000
FREEPAGE 5 PCTFREE 10
BUFFERPOOL BP1
CLOSE NO
PIECESIZE 4 G;
COMMIT;
------------------------------------------------------------------------
set current schema = '$creator';
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","$creator" ;
drop VIEW $creator.VNI201A101A ;
CREATE VIEW $creator.VNI201A101A AS
SELECT ALL CLSTRID, SYSSIGN, POSID, ASOFDT, UNITNOMBAL, MKTVALPC,
MKTVALVCOLD, MKTVALVCNEW, ACCRINTPC, ACCRINTVCOLD,
ACCRINTVCNEW, FWDMKTPRICE, PREM, EXOTICOPTFLAG,
OPTMKTVALCHF, FUTURESTYLEFLAG, PRICE, PRICEDT, STOCKEXCD,
PRICECCYCD, FWDMARKETPRICE, PRICETYPE
FROM TNI250A101A
WHERE DATAPOSAM = 'Y' ;
drop VIEW $creator.VNI203A101A ;
CREATE VIEW $creator.VNI203A101A AS
SELECT ALL CLSTRID, SYSSIGN, POSID, PERENDDT, PERSTARTDT,
ASOFDT AS VLTNDT, SUMNCFGROSSPC AS NCFGROSSPC,
SUMNCFGROSSVC AS NCFGROSSVC,
SUMNCFNETOFTAXPC AS NCFNETOFTAXPC,
SUMNCFNETOFTAXVC AS NCFNETOFTAXVC,
SUMNCFNETOFFEEPC AS NCFNETOFFEEPC,
SUMNCFNETOFFEEVC AS NCFNETOFFEEVC,
SUMNCFNETOFTAXFEEPC AS NCFNETOFTAXFEEPC,
SUMNCFNETOFTAXFEEVC AS NCFNETOFTAXFEEVC,
SUMACFGROSSPC AS ACFGROSSPC, SUMACFGROSSVC AS ACFGROSSVC,
SUMACFNETOFTAXPC AS ACFNETOFTAXPC,
SUMACFNETOFTAXVC AS ACFNETOFTAXVC,
SUMACFNETOFFEEPC AS ACFNETOFFEEPC,
SUMACFNETOFFEEVC AS ACFNETOFFEEVC,
SUMACFNETOFTAXFEEPC AS ACFNETOFTAXFEEPC,
SUMACFNETOFTAXFEEVC AS ACFNETOFTAXFEEVC, RORGROSSPC,
RORGROSSVC, RORNETOFTAXPC, RORNETOFTAXVC, RORNETOFFEEPC,
RORNETOFFEEVC, RORNETOFTAXFEEPC, RORNETOFTAXFEEVC,
1 AS IDXGROSSPC, 1 AS IDXGROSSVC, 1 AS IDXNETOFTAXPC,
1 AS IDXNETOFTAXVC, 1 AS IDNETOFFEEPC, 1 AS IDXNETOFFEEVC,
1 AS IDXNETOFTAXFEEPC, 1 AS IDXNETOFTAXFEEVC
FROM TNI250A101A
WHERE DATAPOSRET = 'Y' ;
drop VIEW $creator.VNI205A101A ;
CREATE VIEW $creator.VNI205A101A AS
SELECT ALL A.CLSTRID AS CLSTRID, A.SYSSIGN AS SYSSIGN,
A.POSID AS POSID, A.ASOFDT AS ASOFDT, B.SRC AS SRC,
C.CIFNBR AS CIFNBR, C.SAFEKACCNBR AS SAFEKACCNBR,
C.BUSUNITCD AS BUSUNITCD, C.VLTNCCYCD AS VLTNCCYCD,
C.NEWVLTNCCYCD AS NEWVLTNCCYCD, C.CALCMTHDCD AS CALCMTHDCD,
B.POSTYPE AS POSTYPE, B.POSCCYCD AS POSCCYCD,
B.IPCRLVNTFLAG AS IPCRLVNTFLAG,
B.TANGASSETCD AS TANGASSETCD,
B.ASSETCLASSCD AS ASSETCLASSCD,
B.ISSUERCATCD AS ISSUERCATCD, B.INSTYCDTK AS INSTYCDTK,
B.INSTYCDMSCI AS INSTYCDMSCI, B.CNTRYCD AS CNTRYCD,
B.INSTRCCYCD AS INSTRCCYCD, B.OPACATCD AS OPACATCD,
B.POSSPECCCY AS POSSPECCCY, B.ACCNBR AS ACCNBR,
B.INTRATE AS INTRATE, B.MTYDT AS MTYDT, B.TRDDT AS TRDDT,
A.UNITNOMBAL AS UNITNOMBAL, B.FXCCY1 AS FXCCY1,
B.FXAMT1 AS FXAMT1, B.FXCCY2 AS FXCCY2, B.FXAMT2 AS FXAMT2,
B.ORDERNBR AS ORDERNBR, B.SECNBR AS SECNBR,
B.SEQNBR AS SEQNBR, B.POSDPTY AS POSDPTY,
B.EFFDPTY AS EFFDPTY, B.RSNCD AS RSNCD, B.CPNCD AS CPNCD,
B.PERYCD AS PERYCD, B.CONTRDT AS CONTRDT, B.DENOM AS DENOM,
B.HLDNGTYPECD AS HLDNGTYPECD, B.STRIKPRICE AS STRIKPRICE,
A.MKTVALPC AS MKTVALPC, A.MKTVALVCOLD AS MKTVALVCOLD,
A.MKTVALVCNEW AS MKTVALVCNEW, A.ACCRINTPC AS ACCRINTPC,
A.ACCRINTVCOLD AS ACCRINTVCOLD,
A.ACCRINTVCNEW AS ACCRINTVCNEW,
A.FWDMKTPRICE AS FWDMKTPRICE, A.PREM AS PREM,
A.EXOTICOPTFLAG AS EXOTICOPTFLAG,
A.OPTMKTVALCHF AS OPTMKTVALCHF, B.ORIGACCCCY AS ORIGACCCCY,
A.FUTURESTYLEFLAG AS FUTURESTYLEFLAG,
B.INTCORRDUEDT AS INTCORRDUEDT, A.PRICE AS PRICE,
A.PRICEDT AS PRICEDT, A.STOCKEXCD AS STOCKEXCD,
B.SUBASSETCLASSCD AS SUBASSETCLASSCD,
A.PRICECCYCD AS PRICECCYCD,
A.FWDMARKETPRICE AS FWDMARKETPRICE,
B.POSOPENINGDT AS POSOPENINGDT, A.PRICETYPE AS PRICETYPE
FROM TNI250A101A A,
oa1a.VNI200A101B B,
oa1a.VNI100A101A C
WHERE A.CLSTRID = B.CLSTRID
AND C.CLSTRID = A.CLSTRID
AND A.ASOFDT = (SELECT DATE(D.CTRLTIMESTMPFROM)
FROM oa1a.VNI600A101A D
WHERE D.CTRLID = CASE
WHEN '50' <=(SELECT E.CTRLMSG
FROM oa1a.VNI600A101A E
WHERE E.CTRLID = 'TEVPHASE'
) THEN 'TEVDAT-3' ELSE 'TEVDAT-2' END) ;
drop ALIAS ${creator}A.VNI205A101A;
CREATE ALIAS ${creator}A.VNI205A101A FOR $creator.VNI205A101A ;
drop ALIAS ${creator}B.VNI205A101A;
CREATE ALIAS ${creator}B.VNI205A101A FOR $creator.VNI205A101A ;
------------------------------------------------------------------------
SET CURRENT schema='$creator';
drop VIEW $creator.VNI250A101A ;
CREATE VIEW $creator.VNI250A101A AS
SELECT ALL CLSTRID, PLATTFORMID, TSID, SYSSIGN, POSID, ASOFDT,
DATAPOSAM, UNITNOMBAL, MKTVALPC, MKTVALVCOLD, MKTVALVCNEW,
ACCRINTPC, ACCRINTVCOLD, ACCRINTVCNEW, FWDMKTPRICE,
FWDMARKETPRICE, PREM, EXOTICOPTFLAG, OPTMKTVALCHF,
FUTURESTYLEFLAG, PRICE, PRICEIND, PRICEDT, PRICECCYCD,
PRICETYPE, STOCKEXCD, DATAPOSRET, PERSTARTDT, PERENDDT,
SUMNCFGROSSPC, SUMNCFGROSSVC, SUMNCFNETOFTAXPC,
SUMNCFNETOFTAXVC, SUMNCFNETOFFEEPC, SUMNCFNETOFFEEVC,
SUMNCFNETOFTAXFEEPC, SUMNCFNETOFTAXFEEVC, SUMACFGROSSPC,
SUMACFGROSSVC, SUMACFNETOFTAXPC, SUMACFNETOFTAXVC,
SUMACFNETOFFEEPC, SUMACFNETOFFEEVC, SUMACFNETOFTAXFEEPC,
SUMACFNETOFTAXFEEVC, RORGROSSPC, RORGROSSVC, RORNETOFFEEPC,
RORNETOFFEEVC, RORNETOFTAXFEEPC, RORNETOFTAXFEEVC,
RORNETOFTAXPC, RORNETOFTAXVC, IDNETOFFEEPC, DATAPOSFLO,
VALUE(INCFGROSSPC + OUTCFGROSSPC + INCFWHTAXPC +
OUTCFWHTAXPC, 0) AS NCFGROSSPC,
VALUE(INCFGROSSVC + OUTCFGROSSVC + INCFWHTAXVC +
OUTCFWHTAXVC, 0) AS NCFGROSSVC,
VALUE(INCFGROSSPC + OUTCFGROSSPC, 0) AS NCFNETOFTAXPC,
VALUE(INCFGROSSVC + OUTCFGROSSVC, 0) AS NCFNETOFTAXVC,
VALUE(INCFGROSSPC + OUTCFGROSSPC + INCFADGPC + OUTCFADGPC
+ INCFVWGPC + OUTCFVWGPC + INCFIPCFEEPC + OUTCFIPCFEEPC +
INCFRETFEEPC + OUTCFRETFEEPC + INCFWHTAXPC + OUTCFWHTAXPC,
0) AS NCFNETOFFEEPC,
VALUE(INCFGROSSVC + OUTCFGROSSVC + INCFADGVC + OUTCFADGVC
+ INCFVWGVC + OUTCFVWGVC + INCFIPCFEEVC + OUTCFIPCFEEVC +
INCFRETFEEVC + OUTCFRETFEEVC + INCFWHTAXVC + OUTCFWHTAXVC,
0) AS NCFNETOFFEEVC,
VALUE(INCFGROSSPC + OUTCFGROSSPC + INCFVWGPC + OUTCFVWGPC
+ INCFADGPC + OUTCFADGPC + INCFIPCFEEPC + OUTCFIPCFEEPC +
INCFRETFEEPC + OUTCFRETFEEPC, 0) AS NCFNETOFTAXFEEPC,
VALUE(INCFGROSSVC + OUTCFGROSSVC + INCFVWGVC + OUTCFVWGVC
+ INCFADGVC + OUTCFADGVC + INCFIPCFEEVC + OUTCFIPCFEEVC +
INCFRETFEEVC + OUTCFRETFEEVC, 0) AS NCFNETOFTAXFEEVC,
VALUE(INTRXCOSTVC + OUTTRXCOSTVC, 0) AS NETTRXCOSTVC,
VALUE(INCFADGVC + OUTCFADGVC, 0) AS NETADGVC,
VALUE(INCFVWGVC + OUTCFVWGVC, 0) AS NETVWGVC,
VALUE(INCFIPCFEEVC + OUTCFIPCFEEVC, 0) AS IPCFEEVC,
VALUE(INCFRETFEEVC + OUTCFRETFEEVC, 0) AS RETFEEVC,
PIDCHANGE AS PIDCHANGE, TIMESTMPCHG AS TIMESTMPCHG
FROM TNI250A101A ;
drop VIEW $creator.VNI250A101B ;
CREATE VIEW $creator.VNI250A101B AS
SELECT ALL CLSTRID, PLATTFORMID, TSID, SYSSIGN, POSID, ASOFDT,
DATAPOSAM, UNITNOMBAL, MKTVALPC, MKTVALVCOLD, MKTVALVCNEW,
ACCRINTPC, ACCRINTVCOLD, ACCRINTVCNEW, FWDMKTPRICE,
FWDMARKETPRICE, PREM, EXOTICOPTFLAG, OPTMKTVALCHF,
FUTURESTYLEFLAG, PRICE, PRICEIND, PRICEDT, PRICECCYCD,
PRICETYPE, STOCKEXCD, DATAPOSRET, PERSTARTDT, PERENDDT,
SUMNCFGROSSPC, SUMNCFGROSSVC, SUMNCFNETOFTAXPC,
SUMNCFNETOFTAXVC, SUMNCFNETOFFEEPC, SUMNCFNETOFFEEVC,
SUMNCFNETOFTAXFEEPC, SUMNCFNETOFTAXFEEVC, SUMACFGROSSPC,
SUMACFGROSSVC, SUMACFNETOFTAXPC, SUMACFNETOFTAXVC,
SUMACFNETOFFEEPC, SUMACFNETOFFEEVC, SUMACFNETOFTAXFEEPC,
SUMACFNETOFTAXFEEVC, RORGROSSPC, RORGROSSVC, RORNETOFFEEPC,
RORNETOFFEEVC, RORNETOFTAXFEEPC, RORNETOFTAXFEEVC,
RORNETOFTAXPC, RORNETOFTAXVC, IDNETOFFEEPC, DATAPOSFLO,
INCFGROSSPC, INCFGROSSVC,
VALUE(DECIMAL(INCFWHTAXVC, 15, 3), 0) AS INCFWHTAXVC,
VALUE(DECIMAL(INCFWHTAXPC, 15, 3), 0) AS INCFWHTAXPC,
INCFADGPC, INCFADGVC, INCFVWGPC, INCFVWGVC, INCFIPCFEEPC,
INCFIPCFEEVC, INCFRETFEEPC, INCFRETFEEVC, INTRXCOSTPC,
INTRXCOSTVC, OUTCFGROSSPC, OUTCFGROSSVC,
VALUE(DECIMAL(OUTCFWHTAXVC, 15, 3), 0) AS OUTCFWHTAXPC,
VALUE(DECIMAL(OUTCFWHTAXPC, 15, 3), 0) AS OUTCFWHTAXVC,
OUTCFADGPC, OUTCFADGVC, OUTCFVWGPC, OUTCFVWGVC,
OUTCFIPCFEEPC, OUTCFIPCFEEVC, OUTCFRETFEEPC, OUTCFRETFEEVC,
OUTTRXCOSTPC, OUTTRXCOSTVC, PIDCHANGE, TIMESTMPCHG
FROM TNI250A101A ;
drop VIEW $creator.VNI204A101A ;
CREATE VIEW $creator.VNI204A101A AS
SELECT CLSTRID, SYSSIGN, POSID, ASOFDT, NCFGROSSPC, NCFGROSSVC,
NCFNETOFTAXPC, NCFNETOFTAXVC, NCFNETOFFEEPC, NCFNETOFFEEVC,
NCFNETOFTAXFEEPC, NCFNETOFTAXFEEVC, NETTRXCOSTVC, NETADGVC,
NETVWGVC, IPCFEEVC, RETFEEVC
FROM VNI250A101A
WHERE DATAPOSFLO = 'Y' ;
drop ALIAS ${creator}B.VNI204A101A ;
CREATE ALIAS ${creator}B.VNI204A101A FOR $creator.VNI204A101A ;
drop ALIAS ${creator}A.VNI204A101A ;
CREATE ALIAS ${creator}A.VNI204A101A FOR $creator.VNI204A101A ;
------------------------------------------------------------------------
SET CURRENT schema='$creator';
SET CURRENT schema='$creator';
drop VIEW $creator.VNI204A101B ;
CREATE VIEW $creator.VNI204A101B AS
SELECT CLSTRID, SYSSIGN, POSID, ASOFDT, NCFGROSSPC, NCFGROSSVC,
NCFNETOFTAXPC, NCFNETOFTAXVC, NCFNETOFFEEPC, NCFNETOFFEEVC,
NCFNETOFTAXFEEPC, NCFNETOFTAXFEEVC, NETTRXCOSTVC, NETADGVC,
NETVWGVC, IPCFEEVC, RETFEEVC
FROM VNI250A101A
WHERE DATAPOSFLO = 'Y' ;
drop ALIAS ${creator}B.VNI204A101B ;
CREATE ALIAS ${creator}B.VNI204A101B FOR $creator.VNI204A101B ;
drop ALIAS ${creator}A.VNI204A101B ;
CREATE ALIAS ${creator}A.VNI204A101B FOR $creator.VNI204A101B ;
COMMIT;