zOs/TX/QBLOBDDL

------------$-[right(userid() sysvar(sysnode) date(s) time(), 60, '-')$]
-- testCase $dsn
-- env      $env      phase $phase
-- dbSys    $dbSys      db $db       creator $creator
-----------------------------------------------------------------------
---||| begin ddl testcase $mbr env $env phase $phase
SET CURRENT SQLID='S100447';
SET CURRENT SCHEMA = $creator;
drop tablespace $db.ABLOBORI;
drop tablespace $db.ABLOBLOB;
commit;
$@[
if $phase >= 1 then $@=[
CREATE TABLESPACE ABLOBORI in $db
    USING STOGROUP GSMS
    BUFFERPOOL BP2
    SEGSIZE 64
    CLOSE YES
    GBPCACHE CHANGED
    DEFINE YES
    LOGGED
    LOCKSIZE ANY LOCKMAX SYSTEM
    CCSID EBCDIC
;
CREATE LOB TABLESPACE ABLOBLOB in $db
    USING STOGROUP GSMS
    BUFFERPOOL BP2
    CLOSE yes
    DSSIZE 4G
    LOCKSIZE ANY LOCKMAX SYSTEM
    GBPCACHE CHANGED
    DEFINE YES
    LOGGED
;
CREATE TABLE $creator.tqz91qbLobORI
  ( name CHARACTER(16) not null
  , text char(30) not null
  , ix int
  --    ,EXLOBNTRIES_ROWID ROWID   GENERATED ALWAYS
  , lob1 clob(1G) with default null
  , fEnd char($-[11-$phase$]) not null with default
  ) in $db.ABLOBORI
    APPEND NO
    NOT VOLATILE CARDINALITY
    DATA CAPTURE NONE
    AUDIT NONE
    CCSID EBCDIC
;
create UNIQUE INDEX $creator.iqz91qbLobORI1
    on $creator.tqz91qbLobORI
    (name)
    CLUSTER
    DEFINE YES
    COMPRESS NO
    BUFFERPOOL BP1
    CLOSE yes
    DEFER NO
    COPY NO
    USING STOGROUP GSMS
    GBPCACHE CHANGED
    PIECESIZE 2M
;
create auxiliary table $creator.tqz91qbLobLOB
    in $db.aBloblob
    stores $creator.tqz91qbLobORI
    append no
    column lob1
;
    -- auxilary index ist notwendig
create UNIQUE INDEX $creator.iqz91qbLobLOB1
    on $creator.tqz91qbLobLOB
    DEFINE YES
    COMPRESS NO
    BUFFERPOOL BP1
    CLOSE yes
    COPY NO
    USING STOGROUP GSMS
    GBPCACHE CHANGED
    PIECESIZE 2M
;
insert into $creator.tqz91qbLobOri
    (name, text, ix, lob1, fEnd)
    with u (ix, ll) as
    (
       select -1, cast(null as clob) from sysibm.sysDummy1
       union all select -2, cast(null as clob) from sysibm.sysDummy1
       union all select 0, cast('' as clob) from sysibm.sysDummy1
       union all select ix+1
          , clob('<' || strip(ix+1) || '--- ') || ll
          || clob(' ---' || strip(ix+1) || '--- ') || ll
          || clob(' ---' || strip(ix+1) || '>')
           from u where ix < 18 and ix >= 0
     )     -- 20 --> 22MB, groesser gibt meist abend
    select 'name ' || strip(ix)
         , 'text ' || strip(ix)
         , ix
         , ll
         , 'end ' || strip(ix)
        from u where ix < 99
;
commit
$]
$]