zOs/TX/WK925DDL
------------$(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 ';
--
$@{
if $phase = 1 then $@[
fehler .... drop TABLESPACE $db.A925A;
commit;
CREATE TABLESPACE A925A
IN $db
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 4 PCTFREE 50
GBPCACHE CHANGED
TRACKMOD YES
LOGGED
numParts 6
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255;
COMMIT;
------------------------------------------------------------------------
CREATE TABLE $creator.TWK925A1
(part SMALLINT NOT NULL WITH DEFAULT 2,
KS835010 CHAR(20) FOR SBCS DATA NOT NULL,
KS835020 CHAR(20) FOR SBCS DATA NOT NULL,
KS835030 CHAR(20) FOR SBCS DATA NOT NULL,
KS835040 INTEGER NOT NULL,
KS835050 CHAR(20) FOR SBCS DATA NOT NULL,
KS835060 CHAR(4) FOR SBCS DATA NOT NULL,
KS835070 CHAR(20) FOR SBCS DATA NOT NULL,
KS835080 CHAR(20) FOR SBCS DATA NOT NULL,
KS835090 CHAR(140) FOR SBCS DATA NOT NULL,
KS835100 CHAR(2) FOR SBCS DATA NOT NULL,
KS835110 DATE NOT NULL,
KS835120 DATE NOT NULL,
KS835130 DATE NOT NULL,
KS835140 DATE NOT NULL,
KS835150 TIMESTAMP NOT NULL,
KS835160 CHAR(10) FOR SBCS DATA NOT NULL,
KS835170 TIMESTAMP NOT NULL,
PRIMARY KEY (part,
KS835010,
KS835030,
KS835040,
KS835020,
KS835050,
KS835060)
)
partition by (part)
( partition 1 ending at (1)
, partition 2 ending at (2)
, partition 3 ending at (3)
, partition 4 ending at (4)
, partition 5 ending at (5)
, partition 6 ending at (6)
)
IN $db.A925A
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE
APPEND NO ;
COMMIT;
------------------------------------------------------------------------
CREATE UNIQUE INDEX $creator.IWK925A0
ON $creator.TWK925A1
(PART ASC,
KS835010 ASC,
KS835030 ASC,
KS835040 ASC,
KS835020 ASC,
KS835050 ASC,
KS835060 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 50
GBPCACHE CHANGED
partitioned
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
------------------------------------------------------------------------
CREATE INDEX $creator.IWK925A1
ON $creator.TWK925A1
(PART ASC,
KS835080 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 30
GBPCACHE CHANGED
NOT CLUSTER
partitioned
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
CREATE INDEX $creator.IWK925A10
ON $creator.TWK925A1
(PART ASC,
KS835030 ASC,
KS835040 ASC,
KS835010 ASC,
KS835080 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
NOT CLUSTER
partitioned
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES
;
COMMIT;
CREATE INDEX $creator.IWK925A3
ON $creator.TWK925A1
(PART ASC,
KS835050 ASC,
KS835060 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 40
GBPCACHE CHANGED
NOT CLUSTER
partitioned
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
CREATE INDEX $creator.IWK925A4
ON $creator.TWK925A1
(PART ASC,
KS835020 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 30
GBPCACHE CHANGED
NOT CLUSTER
partitioned
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
CREATE INDEX $creator.IWK925A5
ON $creator.TWK925A1
(PART ASC,
KS835030 ASC,
KS835040 ASC,
KS835060 ASC,
KS835050 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 40
GBPCACHE CHANGED
CLUSTER
partitioned
COMPRESS NO
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFER NO
DEFINE YES;
COMMIT;
CREATE INDEX $creator.IWK925A6
ON $creator.TWK925A1
(PART ASC,
KS835050 DESC,
KS835060 DESC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 30
GBPCACHE CHANGED
partitioned
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
COMMIT;
CREATE INDEX $creator.IWK925A7
ON $creator.TWK925A1
(PART ASC,
KS835130 DESC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 30
GBPCACHE CHANGED
partitioned
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
CREATE INDEX $creator.IWK925A8
ON $creator.TWK925A1
(PART ASC,
KS835010 ASC,
KS835080 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 30
GBPCACHE CHANGED
partitioned
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
CREATE INDEX $creator.IWK925A9TEMP
ON $creator.TWK925A1
(PART ASC,
KS835070 ASC)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 30
GBPCACHE CHANGED
partitioned
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES
;
COMMIT;
$] else if $phase = 2 then $@[
drop TABLESPACE $db.A925B;
commit;
alter table $creator.TWK925A1 alter PART set default 3;
commit;
CREATE TABLESPACE A925B
IN $db
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 4 PCTFREE 50
GBPCACHE CHANGED
TRACKMOD YES
LOGGED
segsize 64
BUFFERPOOL BP2
LOCKSIZE ROW
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID EBCDIC
DEFINE YES
MAXROWS 255;
CREATE TABLE $creator.TWK925SWITCH
(tst timestamp not null
,part smallint
)
IN $db.A925B
AUDIT NONE
DATA CAPTURE NONE
CCSID EBCDIC
NOT VOLATILE
APPEND NO ;
COMMIT;
------------------------------------------------------------------------
CREATE UNIQUE INDEX $creator.IWK925SW0
ON $creator.TWK925Switch
(tst ASC,
part ASC
)
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 8 PCTFREE 50
GBPCACHE CHANGED
NOT CLUSTER
COMPRESS NO
BUFFERPOOL BP1
CLOSE NO
COPY NO
DEFER NO
DEFINE YES;
commit;
------------------------------------------------------------------------
insert into $creator.TWK925SWITCH values
(current timestamp - 1 month, 1) ;
insert into $creator.TWK925SWITCH values
(current timestamp - 1 hour, 2) ;
commit;
create view $creator.VWK925current (part) as
(
select part
from a540769.twk925switch
where tst = (select max(tst)
from a540769.twk925switch
where tst <= current timestamp
)
)
;
create view $creator.VWK925A1 as
(
select t.*
from $creator.TWK925A1 t, $creator.VWK925current v
where t.part = v.part
)
;
$]
$}
---||| end ddl testcase $mbr env $env phase $phase