zOs/TX/SZ002DDL

------------------------------------------------------------------------
SET CURRENT SQLID='S100447';
------------------------------------------------------------------------
DROP TABLESPACE $db.ASZ02A;
DROP TABLESPACE $db.ASZ02B;
------------------------------------------------------------------------
COMMIT;
------------------------------------------------------------------------
$@ if $phase >= 1 then $@=/pha1/ $**************************************

CREATE TABLESPACE ASZ02A
    IN $db
    USING STOGROUP GSMS
    PRIQTY -1 SECQTY -1
    ERASE  NO
    FREEPAGE 0
    PCTFREE $-[5+$phase$]   -- SRC: 7 TRG: 6
    GBPCACHE CHANGED
    TRACKMOD YES
    SEGSIZE 64
    MAXPARTITIONS 10
    BUFFERPOOL BP2
    LOCKSIZE ANY
    LOCKMAX SYSTEM
    CLOSE YES
    COMPRESS YES
    CCSID EBCDIC
    DEFINE YES
    MAXROWS 255
;

CREATE TABLESPACE ASZ02B
    IN $db
    USING STOGROUP GSMS
    PRIQTY -1 SECQTY -1
    ERASE  NO

    $@ if $phase = 1 then $@=[
        FREEPAGE 0          -- Standard
    $] $@ else $@=[
        FREEPAGE 4
    $]

    $@ if $phase = 1 then $@=[
        PCTFREE 5           -- Standard
    $] $@ else $@=[
        PCTFREE 10
    $]

    GBPCACHE CHANGED

    $@ if $phase = 1 then $@=[
        TRACKMOD YES        -- Standard
    $] $@ else $@=[
        TRACKMOD NO
    $]

    SEGSIZE  64

    $@ if $phase = 1 then $@=[
        MAXPARTITIONS 10    -- Standard
    $] $@ else $@=[
        MAXPARTITIONS 20
    $]

    $@ if $phase = 1 then $@=[
        BUFFERPOOL BP2      -- Standard
    $] $@ else $@=[
        BUFFERPOOL BP0
    $]

    $@ if $phase = 1 then $@=[
        LOCKSIZE ANY        -- Standard
    $] $@ else $@=[
        LOCKSIZE ROW
    $]

    $@ if $phase = 1 then $@=[
        LOCKMAX SYSTEM      -- Standard
    $] $@ else $@=[
        LOCKMAX 1000
    $]

    $@ if $phase = 1 then $@=[
        CLOSE YES           -- Standard
    $] $@ else $@=[
        CLOSE NO
    $]

    $@ if $phase = 1 then $@=[
        COMPRESS YES        -- Standard
    $] $@ else $@=[
        COMPRESS NO
    $]

    CCSID EBCDIC
    DEFINE YES

    $@ if $phase = 1 then $@=[
        MAXROWS 255         -- Standard
    $] $@ else $@=[
        MAXROWS 155
    $]

    $@ if $phase = 1 then $@=[
                            -- Standard
    $] $@ else $@=[
        MEMBER CLUSTER
    $]

;
------------------------------------------------------------------------

CREATE TABLE $creator.TDASZ02A
     (SZ002CH20            CHAR(20) FOR SBCS DATA not null with default
     ,SZ002CH5             CHAR(5) FOR SBCS DATA not null with default
     ,SZ002CH2             CHAR(2) FOR SBCS DATA not null with default
     ,SZ002TST             TIMESTAMP not null with default
$@ if $phase = 1 then $@=[ $********************************************

$] $@ else $@=[ $*******************************************************
     ,SZ002CH3             CHAR(3) FOR SBCS DATA not null with default
$] $********************************************************************
     )
    IN $db.ASZ02A
    AUDIT NONE
    DATA CAPTURE CHANGES
    CCSID EBCDIC
    NOT VOLATILE
;

CREATE TABLE $creator.TDASZ02B
     (SZ002CH20            CHAR(20) FOR SBCS DATA not null with default
     ,SZ002CH5             CHAR(5) FOR SBCS DATA not null with default
     ,SZ002CH2             CHAR(2) FOR SBCS DATA not null with default
     ,SZ002TST             TIMESTAMP not null with default
$@ if $phase = 1 then $@=[ $********************************************

$] $@ else $@=[ $*******************************************************
     ,SZ002CH3             CHAR(3) FOR SBCS DATA not null with default
$] $********************************************************************
     )
    IN $db.ASZ02B
    AUDIT NONE
    DATA CAPTURE CHANGES
    CCSID EBCDIC
    NOT VOLATILE
;

------------------------------------------------------------------------
COMMIT;
------------------------------------------------------------------------

CREATE UNIQUE INDEX $creator.IDASZ02A
  ON $creator.TDASZ02A
   (SZ002CH20             ASC)
  USING STOGROUP GSMS
  PRIQTY -1 SECQTY -1
  ERASE  NO
  FREEPAGE 0
  PCTFREE 10
  GBPCACHE CHANGED
  BUFFERPOOL BP1
  CLOSE YES
  COPY NO
  DEFINE YES
  PIECESIZE 2 G
  CLUSTER
  COMPRESS NO
;

CREATE UNIQUE INDEX $creator.IDASZ02B
  ON $creator.TDASZ02B
   (SZ002CH20             ASC)
  USING STOGROUP GSMS
  PRIQTY -1 SECQTY -1
  ERASE  NO

  $@ if $phase = 1 then $@=[
      FREEPAGE 0          -- Standard
  $] $@ else $@=[
      FREEPAGE 4
  $]

  $@ if $phase = 1 then $@=[
      PCTFREE 10          -- Standard
  $] $@ else $@=[
      PCTFREE 20
  $]

  GBPCACHE CHANGED

  $@ if $phase = 1 then $@=[
      BUFFERPOOL BP1      -- Standard
  $] $@ else $@=[
      BUFFERPOOL BP32K7   -- BP32K wäre Standard bei COMPRESSED=YES
  $]

  $@ if $phase = 1 then $@=[
      CLOSE YES           -- Standard
  $] $@ else $@=[
      CLOSE NO
  $]

  COPY NO
  DEFINE YES
  PIECESIZE 2 G
  CLUSTER

  $@ if $phase = 1 then $@=[
      COMPRESS NO         -- Standard
  $] $@ else $@=[
      COMPRESS YES
  $]

;

ALTER TABLE OA1P.TDASZ02A
      ADD CONSTRAINT PK_TDASZ02A
      PRIMARY KEY (SZ002CH20);

ALTER TABLE OA1P.TDASZ02B
      ADD CONSTRAINT PK_TDASZ02B
      PRIMARY KEY (SZ002CH20);

ALTER TABLE OA1P.TDASZ02A
      ADD RESTRICT ON DROP;

ALTER TABLE OA1P.TDASZ02B
      ADD RESTRICT ON DROP;

------------------------------------------------------------------------
COMMIT;
------------------------------------------------------------------------

$@ if $phase = 1 then $@=[ $********************************************

INSERT INTO $creator.TDASZ02A
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST ) VALUES
       ('erstens', 'e'      , '1'     , current timestamp           );

INSERT INTO $creator.TDASZ02A
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST ) VALUES
       ('zweitens', 'zw'    , '2'     , current timestamp - 2 minute);

INSERT INTO $creator.TDASZ02A
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST ) VALUES
       ('drittens', 'dre'   , '3'     , current timestamp - 3 hours);

INSERT INTO $creator.TDASZ02A
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST ) VALUES
       ('viertens', 'vier',   '4',      current timestamp - 4 days);

INSERT INTO $creator.TDASZ02A
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST ) VALUES
       ('fuenftens', 'fuenf', '5'     , current timestamp - 5 months );

------------------------------------------------------------------------

INSERT INTO $creator.TDASZ02B
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST ) VALUES
       ('erstens', 'e'      , '1'     , current timestamp           );

INSERT INTO $creator.TDASZ02B
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST ) VALUES
       ('zweitens', 'zw'    , '2'     , current timestamp - 2 minute);

INSERT INTO $creator.TDASZ02B
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST ) VALUES
       ('drittens', 'dre'   , '3'     , current timestamp - 3 hours);

INSERT INTO $creator.TDASZ02B
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST ) VALUES
       ('viertens', 'vier',   '4',      current timestamp - 4 days);

INSERT INTO $creator.TDASZ02B
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST ) VALUES
       ('fuenftens', 'fuenf', '5'     , current timestamp - 5 months );

$] $@ else $@=[ $*******************************************************

INSERT INTO $creator.TDASZ02A
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST, SZ002CH3 ) VALUES
       ('erstens', 'e'      , '1'     , current timestamp           ,
        'C11');

INSERT INTO $creator.TDASZ02A
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST, SZ002CH3 ) VALUES
       ('zweitens', 'zw'    , '2'     , current timestamp - 2 minute,
        'C22');

INSERT INTO $creator.TDASZ02A
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST, SZ002CH3 ) VALUES
       ('drittens', 'dre'   , '3'     , current timestamp - 3 hours,
        'C33');

INSERT INTO $creator.TDASZ02A
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST, SZ002CH3 ) VALUES
       ('viertens', 'vier',   '4',      current timestamp - 4 days,
        'C44');

INSERT INTO $creator.TDASZ02A
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST, SZ002CH3 ) VALUES
       ('fuenftens', 'fuenf', '5'     , current timestamp - 5 months,
        'C55');

----------------------------------------------------------------------

INSERT INTO $creator.TDASZ02B
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST, SZ002CH3 ) VALUES
       ('erstens', 'e'      , '1'     , current timestamp           ,
        'C11');

INSERT INTO $creator.TDASZ02B
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST, SZ002CH3 ) VALUES
       ('zweitens', 'zw'    , '2'     , current timestamp - 2 minute,
        'C22');

INSERT INTO $creator.TDASZ02B
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST, SZ002CH3 ) VALUES
       ('drittens', 'dre'   , '3'     , current timestamp - 3 hours,
        'C33');

INSERT INTO $creator.TDASZ02B
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST, SZ002CH3 ) VALUES
       ('viertens', 'vier',   '4',      current timestamp - 4 days,
        'C44');

INSERT INTO $creator.TDASZ02B
       (SZ002CH20,  SZ002CH5, SZ002CH2, SZ002TST, SZ002CH3 ) VALUES
       ('fuenftens', 'fuenf', '5'     , current timestamp - 5 months,
        'C55');

$] $********************************************************************
$/pha1/ $***************************************************************