zOs/SQL/BINDDDL
set current sqlid = 'S100447';
xrop table oa1p.tQZ040BindPgm;
xrop tablespace QZ01A1P.a041a ;
xrop tablespace QZ01A1P.a041h ;
drop tablespace QZ01A1P.a042a ;
drop tablespace QZ01A1P.a042h ;
drop tablespace QZ01A1P.a043a ;
drop tablespace QZ01A1P.a044a ;
commit;
set current sqlid = 'S100447';
drop table oa1p.tQZ040BindPgm;
create global temporary table oa1p.tQZ040BindPgm
( appl char(4) not null
, pgm char(8) not null
, install date
, id integer
, info char(70)
) ccsid unicode;
CREATE TABLESPACE a041a
IN QZ01A1P
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID UNICODE
DEFINE YES
MAXROWS 255
;
CREATE TABLESPACE a041h
IN QZ01A1P
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID UNICODE
DEFINE YES
MAXROWS 255
;
CREATE TABLESPACE a042a
IN QZ01A1P
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID UNICODE
DEFINE YES
MAXROWS 255
;
CREATE TABLESPACE a042h
IN QZ01A1P
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID UNICODE
DEFINE YES
MAXROWS 255
;
CREATE TABLE oa1p.tQZ041BindParm
( appl CHAR(4) NOT NULL default '%'
, pgm CHAR(8) NOT NULL default '%'
, PRIO smallint NOT NULL
, rdl char(10) NOT NULL default 'default'
, cq char(10) NOT NULL default 'default'
, ov char(10) NOT NULL default 'default'
, installBegin date NOT NULL WITH DEFAULT
, installEnd date NOT NULL WITH DEFAULT '31.12.9999'
, rowBegin not null generated always as row begin
, rowEnd not null generated always as row end
, rowtrans not null generated always as transaction start id
, period business_time (installBegin, installEnd)
, period system_time (rowBegin, rowEnd)
, primary key (appl, pgm, business_time without overlaps)
)
in QZ01A1P.a041a
;
CREATE UNIQUE INDEX oa1p.iQZ041A1
ON oa1p.tQZ041BindParm
(appl, pgm, business_time without overlaps)
include (prio, rdl, cq, ov)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
not CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
CREATE TABLE oa1p.tQZ041BindParmHist
like oa1p.tQZ041BindParm
in QZ01A1P.a041H
AUDIT NONE
DATA CAPTURE NONE
CCSID unicode
NOT VOLATILE
;
alter table oa1p.tQZ041BindParm add versioning
use history table oa1p.tQZ041BindParmHist
;
CREATE TABLE oa1p.tQZ042BindCnfg
( typ char(3) not null
, key char(10) not null
, seq smallint not null
, va1 char(16) not null
, va2 char(16) not null
, va3 char(16) not null with default
, va4 char(16) not null with default
, installBegin date NOT NULL WITH DEFAULT
, installEnd date NOT NULL WITH DEFAULT '31.12.9999'
, rowBegin not null generated always as row begin
, rowEnd not null generated always as row end
, rowtrans not null generated always as transaction start id
, period business_time (installBegin, installEnd)
, period system_time (rowBegin, rowEnd)
, primary key (typ, key, seq, business_time without overlaps)
)
in QZ01A1P.a042a
;
CREATE UNIQUE INDEX oa1p.iQZ042A1
ON oa1p.tQZ042BindCnfg
(typ, key, seq, business_time without overlaps)
include (va1, va2, va3, va4)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
not CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
CREATE TABLE oa1p.tQZ042BindCnfgHist
like oa1p.tQZ042BindCnfg
in QZ01A1P.a042H
AUDIT NONE
DATA CAPTURE NONE
CCSID unicode
NOT VOLATILE
;
alter table oa1p.tQZ042BindCnfg add versioning
use history table oa1p.tQZ042BindCnfgHist
;
CREATE TABLESPACE a043a
IN QZ01A1P
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID UNICODE
DEFINE YES
MAXROWS 255
;
set current sqlid = 'S100447';
alter TABLE oa1p.tQZ043BindGen
add cmRes char(4) not null with default
;
alter TABLE oa1p.tQZ043BindGen
add errMsg varchar(2000) not null with default;
commit;
x;
CREATE TABLE oa1p.tQZ043BindGen
( genId integer NOT NULL
generated by default as identity
, genTst timestamp not null with default
, appl CHAR(4) NOT NULL
, install date NOT NULL
, rz char(3) not null
, dbSys char(4) not null
, pgm char(8) not null
, conTok char(8) for bit data
not null with default x'0000000000000000'
, cmPkg char(10) not null
, cmFun char(1) not null with default '?'
, com char(20) not null
, cmJob char(20) not null with default
, result char(4)
, resTst timestamp
, cmRes char(4) not null with default
, errMsg varchar(2000) not null with default
, primary key (genId)
, unique (genTst, pgm)
)
in QZ01A1P.a043a
;
CREATE UNIQUE INDEX oa1p.iQZ043A1
ON oa1p.tQZ043BindGen
(genId)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
not CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
CREATE UNIQUE INDEX oa1p.iQZ043A2
ON oa1p.tQZ043BindGen
(genTst, pgm)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
not CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
CREATE unique INDEX oa1p.iQZ043A3
ON oa1p.tQZ043BindGen
(cmPkg, pgm, gentst)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
not CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
CREATE TABLESPACE a044a
IN QZ01A1P
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID UNICODE
DEFINE YES
MAXROWS 255
;
CREATE TABLE oa1p.tQZ044BindLine
( genId integer NOT NULL
, seq smallint not null
, line char(80) not null
, primary key (genId, seq)
)
in QZ01A1P.a044a
;
CREATE UNIQUE INDEX oa1p.iQZ044A1
ON oa1p.tQZ044BindLine
(genId, seq)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
not CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
PIECESIZE 2 G
;
commit;