zOs/TX/QBSEQDDL

set current sqlid = 'S100447';
drop   TABLESPACE $db.qbSeq;
drop   sequence $creator.sQbSeq1;
commit;
$@[
if $phase = 1 then $@=[
create sequence $creator.sQbSeq1;
$] else if $phase = 2 then $@=[
create sequence $creator.sQbSeq1 no cache order;
$] else if $phase = 3 then $@=[
create sequence $creator.sQbSeq1 no cache order;
 SET CURRENT SQLID = 'S100447';


    CREATE TABLESPACE qbSeq
           in $db
           BUFFERPOOL BP2
           SEGSIZE 64
           DEFINE YES
           LOGGED
           LOCKSIZE ANY
           MAXROWS 255
           CCSID EBCDIC
                     USING STOGROUP GSMS
                                    PRIQTY -1
                                    SECQTY -1
                                    ERASE NO
                     PCTFREE 5
                     FREEPAGE 0
                     GBPCACHE CHANGED
                     COMPRESS YES
                     TRACKMOD YES
 ;


    CREATE TABLE $creator.TqbSeqA1
        (id int not null generated always as identity
        ,txt char(20) not null
        ,num int not null with default
        )
        IN $db.qbSeq
   APPEND NO
   WITH RESTRICT ON DROP
   NOT VOLATILE CARDINALITY
   DATA CAPTURE CHANGES
   AUDIT NONE
   CCSID EBCDIC
;
CREATE TRIGGER $creator.TqbSeqUpd
  no cascade before update ON $creator.TqbSeqA1
  REFERENCING new AS new
  FOR EACH ROW MODE DB2SQL
     set new.num = next value for $creator.sQbSeq1
;
CREATE TRIGGER $creator.TqbSeqIns
  no cascade before insert ON $creator.TqbSeqA1
  REFERENCING new AS new
  FOR EACH ROW MODE DB2SQL
     set new.num = next value for $creator.sQbSeq1
;
commit;
insert into $creator.tQbSeqA1 (txt, num) values ('eins', 100);
insert into $creator.tQbSeqA1 (txt, num) values ('zwei', 200);
select * from $creator.tQbSeqa1;
update $creator.tQbSeqA1 set txt = strip(txt) || '+';
update $creator.tQbSeqA1 set txt = strip(txt) || '+';
select * from $creator.tQbSeqa1;
$]
$]
commit;