zOs/TX/TATDDL

set current sqlid = 'S100447';
--ter table $creator.tqz91TATA disable archive;
DROP TABLESPACE  $db.aTatH    ;
DROP TABLESPACE  $db.aTatA    ;
COMMIT ;

CREATE TABLESPACE aTATa
    IN $db
    USING STOGROUP GSMS
    PRIQTY -1 SECQTY -1
    GBPCACHE CHANGED
    TRACKMOD NO
    LOGGED
    BUFFERPOOL BP2
    LOCKSIZE ANY
    LOCKMAX SYSTEM
    CLOSE YES
    COMPRESS YES
    CCSID      ebcdic
    DEFINE YES
    SEGSIZE 64
    maxpartitions 100   -- uts PBG partitioned by growth
    dssize 1  G
    MAXROWS 255
    FREEPAGE 0
;

CREATE TABLESPACE aTATH
    IN $db
    USING STOGROUP GSMS
    PRIQTY -1 SECQTY -1
    GBPCACHE CHANGED
    TRACKMOD NO
    LOGGED
    BUFFERPOOL BP2
    LOCKSIZE ANY
    LOCKMAX SYSTEM
    CLOSE YES
    COMPRESS YES
    CCSID      ebcdic
    DEFINE YES
    SEGSIZE 64
    maxpartitions 100   -- uts PBG partitioned by growth
    dssize 1  G
    MAXROWS 255
    FREEPAGE 0
;
CREATE TABLE $creator.tqz91TATA
    ( i1 integer not null generated always as identity
    , c2 varchar(4000) not null with default
$@ if $phase = 2 then $@=[
    , rowBegin timestamp(12) not null generated always as row begin
    , rowEnd   timestamp(12) not null generated always as row end
    , rowChange not null generated always
           for each row on update as row change timestamp
$]
    )
    in $db.aTATa;
CREATE INDEX $creator.ITATA1
           ON $creator.tqz91TATA
          (i1 asc
          )
           CLUSTER
           DEFINE YES
           COMPRESS NO
           BUFFERPOOL BP1
           CLOSE YES
           DEFER NO
           COPY NO
           USING STOGROUP "GSMS    "
               PRIQTY -1
               SECQTY -1
               ERASE NO
           PIECESIZE 8M
           FREEPAGE 0
           PCTFREE 0
           GBPCACHE CHANGED
;
-- like does not work with row change .......
--EATE TABLE $creator.tqz91TATH like $creator.tqz91TATA
--  in $db.aTATH;
CREATE TABLE $creator.tqz91TATH
    ( i1 integer not null -- generated always as identity
    , c2 varchar(4000) not null with default
$@ if $phase = 2 then $@=[
    , rowBegin timestamp(12) not null -- generated always as row begin
    , rowEnd   timestamp(12) not null -- generated always as row end
    , rowChange not null generated always
           for each row on update as row change timestamp
$]
    )
    in $db.aTATH;
CREATE INDEX $creator.ITATH1
           ON $creator.tqz91TATH
          (i1 asc
          )
           CLUSTER
           DEFINE YES
           COMPRESS NO
           BUFFERPOOL BP1
           CLOSE YES
           DEFER NO
           COPY NO
           USING STOGROUP "GSMS    "
               PRIQTY -1
               SECQTY -1
               ERASE NO
           PIECESIZE 8M
           FREEPAGE 0
           PCTFREE 0
           GBPCACHE CHANGED
;
alter table $creator.tqz91TATA enable archive
   use $creator.tqz91TATH
;
CREATE view $creator.vqz91TATA as
    select * from $creator.tqz91TATA;
commit
;
insert into $creator.tqz91TATA (c2)
     select char(current timestamp)
          || repeat(left(' ' || strip(creator) || '.' || strip(name)
                          || ' in ' || strip(dbName) || '.' || tsname
                     , 50), 70)
       from sysibm.sysTables fetch first 1000 rows only;
;
commit
;
insert into $creator.tqz91TATH select * from $creator.tqz91TATA