zOs/TX/PER14DDL

------------$-[right(userid() sysvar(sysnode) date(s) time(), 60, '-')$]
-- testCase $dsn
-- env      $env      phase $phase
-- subsys   $dbSys     db $db       creator $creator
------------------------------------------------------------------------
---||| begin ddl testcase $mbr env $env phase $phase
SET CURRENT SQLID='S100447';
$@/wsh/
if $phase <= 1 then $@=[
drop tablespace $db.aper14;
commit;
$]
if $phase = 1 then $@=[
------------------------------------------------------------------------
CREATE TABLESPACE aper14
    IN $db
    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      EBCDIC
    DEFINE YES
    MAXROWS 255
;  ---------------------------------------------------------------------
  CREATE TABLE $creator.tqz91per14
     ( cr varchar(128) not null
     , nm varchar(128) not null
     , type  char(1)   not null
     , keys smallint   not null
     , kNos varchar(400) not null
     , kNames varchar(2000) not null
     )
     IN $db.aper14
     AUDIT NONE
     DATA CAPTURE NONE
     CCSID      EBCDIC
     NOT VOLATILE ;
CREATE unique INDEX $creator.Iqz91per14i1
  ON $creator.tqz91per14
   (cr, nm, keys, kNos)
  USING STOGROUP GSMS
  PRIQTY -1 SECQTY -1
  ERASE  NO
  FREEPAGE 0 PCTFREE 10
  GBPCACHE CHANGED
      cluster
  BUFFERPOOL BP1
  CLOSE YES
  COPY NO
  DEFER NO
  DEFINE YES ;
  commit;
$]
if $phase = 2 then $@=[
    delete from $creator.tqz91per14;
    insert into $creator.tqz91per14
        select creator, name, 'i', 0, '', ''
            from sysibm.sysIndexes;
$@do cx=1  to 50 $@=[
    update $creator.tqz91per14 p
        set keys = $cx,
            kNos = $-[if($cx=1, "", "kNos || ")$]
                    (select strip(char(k.colno)) || k.ordering
                         from sysibm.sysKeys k
                         where k.ixCreator = p.cr
                           and k.ixName    = p.nm
                           and k.colSeq    = $cx) ,
            kNames = $-[if($cx=1, "", "kNames || ',' ||")$]
                    (select strip(k.colName) || ' ' || k.ordering
                         from sysibm.sysKeys k
                         where k.ixCreator = p.cr
                           and k.ixName    = p.nm
                           and k.colSeq    = $cx)
        where type = 'i' and keys = $-[$cx-1$]
            and exists (select 1
                         from sysibm.sysKeys k
                         where k.ixCreator = p.cr
                           and k.ixName    = p.nm
                           and k.colSeq    = $cx)
    ;
    $]
    commit;
$]
if $phase = 3 then $@=[
    delete from $creator.tqz91per14 where type = 't';
    insert into $creator.tqz91per14
        select t.creator, t.name, 't', 0, '', ''
            from sysibm.sysTables t
            join sysibm.sysTableSpace s
            on t.dbName = s.dbName and t.tsName = s.name
        where t.type = 'T' and s.partitions > 0 and  partkeyColNum > 0;
$@do cx=1  to 20 $@=[
    update $creator.tqz91per14 p
        set keys = $cx,
            kNos = $-[if($cx=1, "", "kNos || ")$]
                    (select strip(char(c.colno)) || c.partKey_ordering
                         from sysibm.sysColumns c
                         where c.tbCreator = p.cr
                           and c.tbName    = p.nm
                           and c.partKey_colSeq = $cx) ,
            kNames = $-[if($cx=1, "", "kNames || ',' ||")$]
                (select strip(c.name) || ' ' || c.partKey_ordering
                         from sysibm.sysColumns c
                         where c.tbCreator = p.cr
                           and c.tbName    = p.nm
                           and c.partKey_colSeq = $cx)
        where type = 't' and keys = $-{$cx-1}
            and exists (select 1
                         from sysibm.sysColumns c
                         where c.tbCreator = p.cr
                           and c.tbName    = p.nm
                           and c.partKey_colSeq = $cx)
    ;
    $]
    commit;
$]
if $phase = 4 then $@=[
    delete from $creator.tqz91per14 where type = 'u';
    insert into $creator.tqz91per14
        select t.creator, t.name, 'u', q.keys, q.kNos, q.kNames
            from sysibm.sysTables t
            join sysibm.sysTableSpace s
              on t.dbName = s.dbName and t.tsName = s.name
            join sysibm.sysTablePart  p
              on t.dbName = p.dbName and t.tsName = p.tsName
               and p.partition = 1
            join $creator.tqz91per14 q
              on q.cr = p.ixCreator and q.nm = p.ixName
        where t.type = 'T' and s.partitions > 0 and  partkeyColNum = 0;
    ;
    commit;
$]
$/wsh/