/* 
16,12,1997 
  ä«íáó½Ñ¡« »«½Ñ CHANGEOPERATION ó ¿¡ñÑ¬ß Γáí½¿µδ ¿ßΓ«α¿¿ 


-------------------------------- 
¥Γ«Γ ß¬α¿»Γ »αÑñ¡áº¡áτÑ¡ ñ½∩ ºáóÑñÑ¡¿∩ Γáí½¿µ ¿ßΓ«α¿¿ «í¡«ó½Ñ¡¿⌐. á óσ«ñÑ Ñ¼π 
π¬áºδóáεΓ 
Γáí½¿µδ, ñ½∩ ¬«Γ«αδσ ªÑ½áΓѽ∞¡« ºáóÑñÑ¡¿Ñ ¿ßΓ«α¿¿ (»« ¼á߬ἠ¿¼Ñ¡¿ ¿ ßσѼδ). 
á óδσ«ñÑ ¼δ »«½πτáѼ ß¬α¿»Γ, ¬«Γ«αδ⌐: 


1). ä«íáó½∩ÑΓ φΓ¿¼ Γáí½¿µá¼ »«½∩ 
  X_ADDSTAMP TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP, 
  X_ADDUSER  CHAR(8)   NOT NULL WITH DEFAULT USER, 
  Ñß½¿ Γᬫóδσ »«½Ñ⌐ ¡Ñ ¡áΦ½«ß∞. 


2). âÑ¡Ñα¿απεΓ Γáí½¿µδ ¿ßΓ«α¿¿. ¥Γ¿ Γáí½¿µδ ¿¼ÑεΓ ΓÑ ªÑ »«½∩ + »«½Ñ 
  CHANGEOPERATION CHAR(1) NOT NULL CHECK (CHANGEOPERATION IN ('U", 'D')) 
  ê¼∩ Γáí½¿µδ «ßΓáÑΓß∩ íѺ ¿º¼Ñ¡Ñ¡¿⌐, ¬ ßσÑ¼Ñ Γáí½¿µδ ß½Ñóá ñ«íáó½∩ÑΓß∩ Z 
(τΓ«íδ 
  »α¿ ß«αΓ¿α«ó¬Ñ «¡¿ «¬áºδó὿ß∞ 󡿺π ß»¿ß¬á) ß ó«º¼«ª¡δ¼ πßÑτÑ¡¿Ñ¼ ¿¼Ñ¡¿ ñ« 8 
ß¿¼ó«½«ó. 
  è½ετ Γáí½¿µδ ¿ßΓ«α¿¿ = ¬½ετπ ßΓáα«⌐ + ADDSTAMP (Ñß½¿ Ñú« ¡Ñ í佫 ó ¬½ετÑ). 


3). âÑ¡Ñα¿απεΓ Γα¿úúÑαδ ¡á ¿ßσ«ñ¡δÑ Γáí½¿µδ: 
  ÅÑαÑñ «í¡«ó½Ñ¡¿Ñ¼ (ºá¼Ñ¡á ADDSTAMP ¿ ADDUSER ¡á ΓѬπΘ¿Ñ) 
  Å«ß½Ñ «í¡«ó½Ñ¡¿∩ (ºá»¿ß∞ ßΓáαδσ º¡áτÑ¡¿⌐ + 'U') 
  Å«ß½Ñ πñá½Ñ¡¿∩ (ºá»¿ß∞ ßΓáαδσ º¡áτÑ¡¿⌐ + 'D') 
*/ 


call rxfuncadd 'sqlexec', 'db2ar', 'sqlexec' 
call rxfuncadd 'sqldbs',  'db2ar', 'sqldbs' 

/* Å«½∩, αÑñá¬Γ¿απѼδÑ »«½∞º«óáΓѽѼ ß¬α¿»Γá: */ 
base='tickproc' 
user='***'
using='****'
whereSchema = "where (tabschema like 'FFP_REPL%')" 
andWhereTabName="and not (tabname like '%TABLE%')" 
/* ******************************-************ */ 


/*****************************-******************************-*************/ 


st. 1  = "select distinct tabschema from syscat.tables "whereSchema" order by tabschema" 
st. 2  = "select tabname, colcount from syscat.tables where (type='T') AND (tabschema=?) "andWhereTabname' order by tabname' 
st. 3  = 'select colname, typename, length, scale, default, nulls, remarks from SYSCAT.COLUMNS where (tabschema=?) and (tabname=?) order by colname' 
st. 4  = 'select colnames from SYSCAT.INDEXES where (tabschema=?) and (tabname=?) and '|| "(uniquerule='P')" 
cnt =  4  


call sqlexek 'connect to 'base 'user 'user 'using 'using
do i= 1  to cnt 
  ss = st.i 
  call sqlexek 'prepare s'i' from :ss' 
  call sqlexek 'declare c'i' cursor for s'i 
end 


call sqlexek 'open c1' 
do forever /* ä½∩ ¬áªñ«⌐ ßσѼδ */ 
  call sqlexek 'FETCH c1 INTO :tabschema' 
  if sqlca.sqlcode <> 0  then leave 
  call sqlexek 'open c2 using :tabschema' /* ä½∩ ¬áªñ«⌐ ßσѼδ */ 
  do forever 
    call sqlexek 'fetch c2 into :tabname, :colcount' 
    if sqlca.sqlcode <> 0  then leave 
    haveAddStamp =  0  
    haveAddUser  =  0  
    curcolumn =  1  
    newTabSchema = strip(tabschema)'_Z'
/*
    if length(newTabSchema)>8 then newTabSchema = left(newTabSchema,8) 
*/
    call sqlexek 'open c3 using :tabschema, :tabname' 
    do forever 
      call sqlexek 'fetch c3 into :colname, :typename, :l :li, :sc :sc_i, :dflt :dflt_i, :nulls, :remarks :remarks_i' 
      if sqlca.sqlcode <> 0  then leave 
      if (typename='BLOB')      | (typename='CLOB')    then typename=typename'('l')' 
      if (typename='CHARACTER') | (typename='VARCHAR') then typename=typename'('l')' 
      if (typename='DECIMAL') then typename=typename'('l','sc')' 
      s = '  '||left('"'colname'"', 40 ) 
      if nulls='N' then s = s left(typename, 17 )' NOT NULL' 
        else s = s strip(typename) 
      if dflt_i>= 0  then s = s||' WITH DEFAULT 'dflt 
      columns.curcolumn.def = s 
      columns.curcolumn.clm = colname 
      curcolumn=curcolumn+ 1  
      if colname = 'X_ADDSTAMP' then haveAddStamp =  1  
      if colname = 'X_ADDUSER'  then haveAddUser  =  1  
    end 
    call sqlexek 'close c3' 
    if \haveAddStamp then do 
      t = '  X_ADDSTAMP TIMESTAMP NOT NULL WITH DEFAULT' 
      columns.curcolumn.def = t 
      columns.curcolumn.clm = 'X_ADDSTAMP' 
      curcolumn=curcolumn+ 1  
      say 'ALTER TABLE "'strip(tabschema)'"."'tabname'"' 
      say '  ADD COLUMN 't';' 
      say 
    end 
    if \haveAddUser then do 
      t = '  X_ADDUSER VARCHAR(32) NOT NULL WITH DEFAULT USER' 
      columns.curcolumn.def = t 
      columns.curcolumn.clm = 'X_ADDUSER' 
      curcolumn=curcolumn+ 1  
      say 'ALTER TABLE "'strip(tabschema)'"."'tabname'"' 
      say '  ADD COLUMN 't';' 
      say 
    end 
    say 'CREATE TABLE "'newTabSchema'"."'tabname'"(' 
    do i =  1  to curcolumn- 1  
      say columns.i.def',' 
    end 
    say '  CHANGEOPERATION CHAR(1) NOT NULL,' 
    say "  CHECK(CHANGEOPERATION IN ('U', 'D'))" 
    say ');' 
    say 
    call sqlexek 'open c4 using :tabschema, :tabname' 
    do forever 
      call sqlexek 'FETCH c4 INTO :colnames' 
      if sqlca.sqlcode <> 0  then leave 
      colnames = translate(colnames,',','+') 
      colnames = right(colnames, length(colnames)- 1 ) 
      if pos('X_ADDSTAMP', colnames)= 0  then colnames=colnames||',X_ADDSTAMP' 
      colnames=colnames||',CHANGEOPERATION' 
      say 'ALTER TABLE "'newTabSchema'"."'tabname'"' 
      say '  ADD PRIMARY KEY('colnames');' 
      say 
    end 
    call sqlexek 'close c4' 
    xtabname = strip(/*left*/(tabname /*,16*/)) 
    say 'CREATE TRIGGER "'newTabSchema'"."'xtabname||'_BU" NO CASCADE BEFORE UPDATE' 
    say 'ON "'strip(TabSchema)'"."'tabname'"' 
    say 'REFERENCING OLD AS O NEW AS N' 
    say 'FOR EACH ROW MODE DB2SQL' 
    say 'SET N.X_ADDSTAMP = CURRENT TIMESTAMP, N.X_ADDUSER = USER;' 
    say 
    collist1 = '' 
    collist2 = '' 
    do i =  1  to curcolumn- 1  
      collist1 = collist1||'  "'columns.i.clm'", ' 
      collist2 = collist2||'O."'columns.i.clm'", ' 
    end 
    collist1 = collist1 || 'CHANGEOPERATION' 
    collist3 = collist2 || "'D'" 
    collist2 = collist2 || "'U'" 
    say 'CREATE TRIGGER "'newTabSchema'"."'xtabname||'_AU" AFTER UPDATE' 
    say 'ON "'strip(TabSchema)'"."'tabname'"' 
    say 'REFERENCING OLD AS O ' 
    say 'FOR EACH ROW MODE DB2SQL' 
    say 'INSERT INTO "'newTabSchema'"."'tabname'"' 
    say '('collist1')' 
    say 'VALUES' 
    say '('collist2');' 
    say 
    say 'CREATE TRIGGER "'newTabSchema'"."'xtabname||'_AD" AFTER DELETE' 
    say 'ON "'strip(TabSchema)'"."'tabname'"' 
    say 'REFERENCING OLD AS O ' 
    say 'FOR EACH ROW MODE DB2SQL' 
    say 'INSERT INTO "'newTabSchema'"."'tabname'"' 
    say '('collist1')' 
    say 'VALUES' 
    say '('collist3');' 
    say 
  end 
  call sqlexek 'close c2' 
end 
call sqlexek 'close c1' 


return 


SQLEXEK: 
  parse arg qqqq 
  call SQLEXEC qqqq 
  call CHECKERR qqqq 
return 


CHECKERR: 
  arg errloc 


  if  ( SQLCA.SQLCODE >=  0  ) then 
    return  0  
  else do 
    say '--- error report ---' 
    say 'ERROR occured :' errloc 
    say 'SQLCODE :' SQLCA.SQLCODE 


    /*****************************-*\ 
    * GET ERROR MESSAGE API called * 
    \*****************************-*/ 
    call SQLDBS 'GET MESSAGE INTO :errmsg LINEWIDTH 80' 
    say errmsg 
    say '--- end error report ---' 


    if (SQLCA.SQLCODE <  0  ) then 
      exit 
    else do 
      say 'WARNING - CONTINUING PROGRAM WITH ERRORS' 
      return  0  
    end 
  end 
return  0  

/* 
16,12,1997 
  Добавлено поле CHANGEOPERATION в индекс таблицы истории 


-------------------------------- 
Этот скрипт предназначен для заведения таблиц истории обновлений. а входе ему 
указывают 
таблицы, для которых желательно заведение истории (по маскам имени и схемы). 
а выходе мы получаем скрипт, который: 


1). Добавляет этим таблицам поля 
  X_ADDSTAMP TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP, 
  X_ADDUSER  CHAR(8)   NOT NULL WITH DEFAULT USER, 
  если таковых полей не нашлось. 


2). Генерируют таблицы истории. Эти таблицы имеют те же поля + поле 
  CHANGEOPERATION CHAR(1) NOT NULL CHECK (CHANGEOPERATION IN ('U", 'D')) 
  Имя таблицы остается без изменений, к схеме таблицы слева добавляется Z 
(чтобы 
  при сортировке они оказывались внизу списка) с возможным усечением имени до 8 
символов. 
  Ключ таблицы истории = ключу старой + ADDSTAMP (если его не было в ключе). 


3). Генерируют триггеры на исходные таблицы: 
  Перед обновлением (замена ADDSTAMP и ADDUSER на текущие) 
  После обновления (запись старых значений + 'U') 
  После удаления (запись старых значений + 'D') 
*/ 


call rxfuncadd 'sqlexec', 'db2ar', 'sqlexec' 
call rxfuncadd 'sqldbs',  'db2ar', 'sqldbs' 

/* Поля, редактируемые пользователем скрипта: */ 
base='tickproc' 
user='***'
using='****'
whereSchema = "where (tabschema like 'FFP_REPL%')" 
andWhereTabName="and not (tabname like '%TABLE%')" 
/* ******************************-************ */ 


/*****************************-******************************-*************/ 


st. 1  = "select distinct tabschema from syscat.tables "whereSchema" order by tabschema" 
st. 2  = "select tabname, colcount from syscat.tables where (type='T') AND (tabschema=?) "andWhereTabname' order by tabname' 
st. 3  = 'select colname, typename, length, scale, default, nulls, remarks from SYSCAT.COLUMNS where (tabschema=?) and (tabname=?) order by colname' 
st. 4  = 'select colnames from SYSCAT.INDEXES where (tabschema=?) and (tabname=?) and '|| "(uniquerule='P')" 
cnt =  4  


call sqlexek 'connect to 'base 'user 'user 'using 'using
do i= 1  to cnt 
  ss = st.i 
  call sqlexek 'prepare s'i' from :ss' 
  call sqlexek 'declare c'i' cursor for s'i 
end 


call sqlexek 'open c1' 
do forever /* Для каждой схемы */ 
  call sqlexek 'FETCH c1 INTO :tabschema' 
  if sqlca.sqlcode <> 0  then leave 
  call sqlexek 'open c2 using :tabschema' /* Для каждой схемы */ 
  do forever 
    call sqlexek 'fetch c2 into :tabname, :colcount' 
    if sqlca.sqlcode <> 0  then leave 
    haveAddStamp =  0  
    haveAddUser  =  0  
    curcolumn =  1  
    newTabSchema = strip(tabschema)'_Z'
/*
    if length(newTabSchema)>8 then newTabSchema = left(newTabSchema,8) 
*/
    call sqlexek 'open c3 using :tabschema, :tabname' 
    do forever 
      call sqlexek 'fetch c3 into :colname, :typename, :l :li, :sc :sc_i, :dflt :dflt_i, :nulls, :remarks :remarks_i' 
      if sqlca.sqlcode <> 0  then leave 
      if (typename='BLOB')      | (typename='CLOB')    then typename=typename'('l')' 
      if (typename='CHARACTER') | (typename='VARCHAR') then typename=typename'('l')' 
      if (typename='DECIMAL') then typename=typename'('l','sc')' 
      s = '  '||left('"'colname'"', 40 ) 
      if nulls='N' then s = s left(typename, 17 )' NOT NULL' 
        else s = s strip(typename) 
      if dflt_i>= 0  then s = s||' WITH DEFAULT 'dflt 
      columns.curcolumn.def = s 
      columns.curcolumn.clm = colname 
      curcolumn=curcolumn+ 1  
      if colname = 'X_ADDSTAMP' then haveAddStamp =  1  
      if colname = 'X_ADDUSER'  then haveAddUser  =  1  
    end 
    call sqlexek 'close c3' 
    if \haveAddStamp then do 
      t = '  X_ADDSTAMP TIMESTAMP NOT NULL WITH DEFAULT' 
      columns.curcolumn.def = t 
      columns.curcolumn.clm = 'X_ADDSTAMP' 
      curcolumn=curcolumn+ 1  
      say 'ALTER TABLE "'strip(tabschema)'"."'tabname'"' 
      say '  ADD COLUMN 't';' 
      say 
    end 
    if \haveAddUser then do 
      t = '  X_ADDUSER VARCHAR(32) NOT NULL WITH DEFAULT USER' 
      columns.curcolumn.def = t 
      columns.curcolumn.clm = 'X_ADDUSER' 
      curcolumn=curcolumn+ 1  
      say 'ALTER TABLE "'strip(tabschema)'"."'tabname'"' 
      say '  ADD COLUMN 't';' 
      say 
    end 
    say 'CREATE TABLE "'newTabSchema'"."'tabname'"(' 
    do i =  1  to curcolumn- 1  
      say columns.i.def',' 
    end 
    say '  CHANGEOPERATION CHAR(1) NOT NULL,' 
    say "  CHECK(CHANGEOPERATION IN ('U', 'D'))" 
    say ');' 
    say 
    call sqlexek 'open c4 using :tabschema, :tabname' 
    do forever 
      call sqlexek 'FETCH c4 INTO :colnames' 
      if sqlca.sqlcode <> 0  then leave 
      colnames = translate(colnames,',','+') 
      colnames = right(colnames, length(colnames)- 1 ) 
      if pos('X_ADDSTAMP', colnames)= 0  then colnames=colnames||',X_ADDSTAMP' 
      colnames=colnames||',CHANGEOPERATION' 
      say 'ALTER TABLE "'newTabSchema'"."'tabname'"' 
      say '  ADD PRIMARY KEY('colnames');' 
      say 
    end 
    call sqlexek 'close c4' 
    xtabname = strip(/*left*/(tabname /*,16*/)) 
    say 'CREATE TRIGGER "'newTabSchema'"."'xtabname||'_BU" NO CASCADE BEFORE UPDATE' 
    say 'ON "'strip(TabSchema)'"."'tabname'"' 
    say 'REFERENCING OLD AS O NEW AS N' 
    say 'FOR EACH ROW MODE DB2SQL' 
    say 'SET N.X_ADDSTAMP = CURRENT TIMESTAMP, N.X_ADDUSER = USER;' 
    say 
    collist1 = '' 
    collist2 = '' 
    do i =  1  to curcolumn- 1  
      collist1 = collist1||'  "'columns.i.clm'", ' 
      collist2 = collist2||'O."'columns.i.clm'", ' 
    end 
    collist1 = collist1 || 'CHANGEOPERATION' 
    collist3 = collist2 || "'D'" 
    collist2 = collist2 || "'U'" 
    say 'CREATE TRIGGER "'newTabSchema'"."'xtabname||'_AU" AFTER UPDATE' 
    say 'ON "'strip(TabSchema)'"."'tabname'"' 
    say 'REFERENCING OLD AS O ' 
    say 'FOR EACH ROW MODE DB2SQL' 
    say 'INSERT INTO "'newTabSchema'"."'tabname'"' 
    say '('collist1')' 
    say 'VALUES' 
    say '('collist2');' 
    say 
    say 'CREATE TRIGGER "'newTabSchema'"."'xtabname||'_AD" AFTER DELETE' 
    say 'ON "'strip(TabSchema)'"."'tabname'"' 
    say 'REFERENCING OLD AS O ' 
    say 'FOR EACH ROW MODE DB2SQL' 
    say 'INSERT INTO "'newTabSchema'"."'tabname'"' 
    say '('collist1')' 
    say 'VALUES' 
    say '('collist3');' 
    say 
  end 
  call sqlexek 'close c2' 
end 
call sqlexek 'close c1' 


return 


SQLEXEK: 
  parse arg qqqq 
  call SQLEXEC qqqq 
  call CHECKERR qqqq 
return 


CHECKERR: 
  arg errloc 


  if  ( SQLCA.SQLCODE >=  0  ) then 
    return  0  
  else do 
    say '--- error report ---' 
    say 'ERROR occured :' errloc 
    say 'SQLCODE :' SQLCA.SQLCODE 


    /*****************************-*\ 
    * GET ERROR MESSAGE API called * 
    \*****************************-*/ 
    call SQLDBS 'GET MESSAGE INTO :errmsg LINEWIDTH 80' 
    say errmsg 
    say '--- end error report ---' 


    if (SQLCA.SQLCODE <  0  ) then 
      exit 
    else do 
      say 'WARNING - CONTINUING PROGRAM WITH ERRORS' 
      return  0  
    end 
  end 
return  0  

CREATE TABLE "FFP_REPL"."ADM_USERS"(
  "DEPARTMENT_ID"                          INTEGER            NOT NULL,
  "FIRST_NAME"                             VARCHAR( 35 ),
  "LAST_NAME"                              VARCHAR( 35 ),
  "PASSWORD"                               VARCHAR( 30 ),
  "USE_USER_ID"                            INTEGER,
  "USER_ID"                                INTEGER           NOT NULL,
  "USER_NAME"                              VARCHAR( 30 )       NOT NULL
);

ALTER TABLE "FFP_REPL"."ADM_USERS"
  ADD PRIMARY KEY(USER_ID);

ALTER TABLE "FFP_REPL"."ADM_USERS"
  ADD COLUMN   X_ADDSTAMP TIMESTAMP NOT NULL WITH DEFAULT;

ALTER TABLE "FFP_REPL"."ADM_USERS"
  ADD COLUMN   X_ADDUSER VARCHAR( 32 ) NOT NULL WITH DEFAULT USER;

CREATE TABLE "FFP_REPL_Z"."ADM_USERS"(
  "DEPARTMENT_ID"                          INTEGER            NOT NULL,
  "FIRST_NAME"                             VARCHAR( 35 ),
  "LAST_NAME"                              VARCHAR( 35 ),
  "PASSWORD"                               VARCHAR( 30 ),
  "USE_USER_ID"                            INTEGER,
  "USER_ID"                                INTEGER            NOT NULL,
  "USER_NAME"                              VARCHAR( 30 )       NOT NULL,
  X_ADDSTAMP TIMESTAMP NOT NULL WITH DEFAULT,
  X_ADDUSER VARCHAR( 32 ) NOT NULL WITH DEFAULT USER,
  CHANGEOPERATION CHAR( 1 ) NOT NULL,
  CHECK(CHANGEOPERATION IN ('U', 'D'))
);

ALTER TABLE "FFP_REPL_Z"."ADM_USERS"
  ADD PRIMARY KEY(USER_ID,X_ADDSTAMP,CHANGEOPERATION);

CREATE TRIGGER "FFP_REPL_Z"."ADM_USERS_BU" NO CASCADE BEFORE UPDATE
ON "FFP_REPL"."ADM_USERS"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW MODE DB2SQL
SET N.X_ADDSTAMP = CURRENT TIMESTAMP, N.X_ADDUSER = USER;

CREATE TRIGGER "FFP_REPL_Z"."ADM_USERS_AU" AFTER UPDATE
ON "FFP_REPL"."ADM_USERS"
REFERENCING OLD AS O 
FOR EACH ROW MODE DB2SQL
INSERT INTO "FFP_REPL_Z"."ADM_USERS"
(  "DEPARTMENT_ID",   "FIRST_NAME",   "LAST_NAME",   "PASSWORD",   "USE_USER_ID",   "USER_ID",   "USER_NAME",   "X_ADDSTAMP",   "X_ADDUSER", CHANGEOPERATION)
VALUES
(O."DEPARTMENT_ID", O."FIRST_NAME", O."LAST_NAME", O."PASSWORD", O."USE_USER_ID", O."USER_ID", O."USER_NAME", O."X_ADDSTAMP", O."X_ADDUSER", 'U');

CREATE TRIGGER "FFP_REPL_Z"."ADM_USERS_AD" AFTER DELETE
ON "FFP_REPL"."ADM_USERS"
REFERENCING OLD AS O 
FOR EACH ROW MODE DB2SQL
INSERT INTO "FFP_REPL_Z"."ADM_USERS"
(  "DEPARTMENT_ID",   "FIRST_NAME",   "LAST_NAME",   "PASSWORD",   "USE_USER_ID",   "USER_ID",   "USER_NAME",   "X_ADDSTAMP",   "X_ADDUSER", CHANGEOPERATION)
VALUES
(O."DEPARTMENT_ID", O."FIRST_NAME", O."LAST_NAME", O."PASSWORD", O."USE_USER_ID", O."USER_ID", O."USER_NAME", O."X_ADDSTAMP", O."X_ADDUSER", 'D');

>>-CREATE TRIGGER--trigger-name--+-NO CASCADE BEFORE-+---------->
                                 +-AFTER-------------+
                                 '-INSTEAD OF--------'

>--+-INSERT--------------------------+--ON--+-table-name-+------>
   +-DELETE--------------------------+      '-view-name--'
   '-UPDATE--+---------------------+-'
             |     .-,-----------. |
             |     V             | |
             '-OF----column-name-+-'

   then 
        set @Details = (values @Details concat ' fld');
    end if;