zOs/TX/M2UTSDDL

set current sqlid = 'S100447';
DROP TABLESPACE  $db.am2utsa  ;
COMMIT ;

$@ if $phase >= 1 then $@=/p1/
CREATE TABLESPACE am2utsa
    IN $db
    USING STOGROUP GSMS
    PRIQTY -1 SECQTY -1
    GBPCACHE CHANGED
    TRACKMOD NO
    LOGGED
    BUFFERPOOL BP32K
    LOCKSIZE ANY
    LOCKMAX SYSTEM
    CLOSE YES
    COMPRESS YES
    CCSID      ebcdic
    DEFINE YES
$= segSize =- if($phase // 10 = 7, 64, 16)
$@  if $phase <= 9 then $@=[
    SEGSIZE $segSize -- segmented
$] $@  else if $phase <= 19 then $@=[   $** partitioned classic
    numparts 5   -- classic partitioned
    dssize 16 g
$] $@ else if $phase <= 29 then $@=[   $** UTS PBG
    maxpartitions 1   -- uts PBG partitioned by growth
    SEGSIZE $segSize
    dssize 16 g
$] $@ else if $phase <= 39 then $@=[   $** UTS PBR
    numparts 5   -- uts PBR partitioned by range
    SEGSIZE $segSize
    dssize 1  g
$]
    MAXROWS 255
$@ if $phase // 10  = 6 then
           FREEPAGE 66
$@ else
           FREEPAGE 0
;
CREATE TABLE $creator.tqz91m2utsA
    ( c1 char(15) not null with default
    , c2 char(10) not null with default
$@ if $phase // 10 < 3 then
    , c3 char(10) not null with default
$@ else
    , c3 char(13) not null with default
$@ if $phase // 10 = 4 then
    , c4 char(10) not null with default
    )
        IN $db.am2utsa
   CCSID         EBCDIC
$@ if ($phase>=10 & $phase<=19) | ($phase>=30 & $phase<=39) then $@=[
   partition by (c1)
   ( partition 1 ending ('j')
   , partition 2 ending ('z')
   , partition 3 ending ('A')
   , partition 4 ending ('J')
   , partition 5 ending (maxvalue)
   )
$]
;
comment on table $creator.tqz91m2utsA is
       'comment on tb $creator.tqz91m2utsA phase=$phase';
comment on $creator.tqz91m2utsA
     ( c1 is 'comment on c1'
     , c2 is 'comment on c1 phase=$phase' );
label on table $creator.tqz91m2utsA is
       'label on tqz91m2utsA phase=$phase';
label on $creator.tqz91m2utsA
     ( c1 is 'label on c1'
     , c2 is 'label on c1 phase=$phase' );
CREATE UNIQUE INDEX $creator.Im2utsA1
           ON $creator.tqz91m2utsA
          (c1 asc
          )
           CLUSTER
$@ if ($phase>=10 & $phase<=19) | ($phase>=30 & $phase<=39) then
           partitioned
           DEFINE YES
           COMPRESS NO
           BUFFERPOOL BP1
           CLOSE YES
           DEFER NO
           COPY NO
           USING STOGROUP "GSMS    "
               PRIQTY -1
               SECQTY -1
               ERASE NO
$@ if $phase = 3 then
           PIECESIZE 8M
$@ if $phase // 10  = 8 then
           FREEPAGE 88
$@ else
           FREEPAGE 0
           PCTFREE 0
           GBPCACHE CHANGED
;
CREATE  INDEX $creator.Im2utsA2
           ON $creator.tqz91m2utsA
          (c1 asc, c2
          )
           NOT CLUSTER
$@ if ($phase>=10 & $phase<=19) | ($phase>=30 & $phase<=39) then
           partitioned
           DEFINE YES
           COMPRESS NO
           BUFFERPOOL BP32K
           CLOSE YES
           DEFER NO
           COPY NO
           USING STOGROUP "GSMS    "
               PRIQTY -1
               SECQTY -1
               ERASE NO
$@ if $phase = 3 then
           PIECESIZE 8M
$@ if $phase // 10  = 8 then
           FREEPAGE 88
$@ else
           FREEPAGE 0
           PCTFREE 0
           GBPCACHE CHANGED
;
insert into $creator.tqz91m2utsA (c1, c2) values('eins', 'eins 1');
insert into $creator.tqz91m2utsA (c1, c2) values('zwei', 'zwei 2');
insert into $creator.tqz91m2utsA (c1, c2) values('drei', 'drei 3');
insert into $creator.tqz91m2utsA (c1, c2) values('vier', 'vier 4');
insert into $creator.tqz91m2utsA (c1, c2) values('fuenf', 'fuenf 5');
insert into $creator.tqz91m2utsA (c1, c2) values('sechs', 'sechs 6');
commit;
$/p1/