When you execute a clpplus script like script.clpplus that
returns a warning for files execution via STA command, clpplus
fails to send commit to database server.
Example:
If you have an "alter table delete column" statement in
rmp-remove-002.sql file, and if server returns a warning for
this DDL, clpplus fails to flow a commit to server which causes
the query execution to go in an inconsistent state.
Hence, when reorg is executed in succession, it might fail to
see the modified table and the alter table execution shows no
effect i.e the column is not deleted.
script.clpplus:
set echo on
set define on
SET CURRENT SQLID = '$DB2SQLID';
SET CURRENT PATH = CURRENT PATH,'$DB2SQLID';
column c_date new_value v_date
column c_ldb_alias new_value v_ldb_alias
column c_sqlid new_value v_sqlid
select VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS')
c_date, rtrim(current server) c_ldb_alias, rtrim(current sqlid)
c_sqlid from sysibm.sysdummy1;
select '&v_ldb_alias','&v_date','&v_sqlid' from
sysibm.sysdummy1;
--whenever sqlerror exit
--whenever oserror exit
STA @drop-triggers.sql
STA @rmp-remove-001.sql
STA @rmp-remove-002.sql
! ksh93 reorgs.sh
QUIT
In above example, whenever there is a warning received from
server for file execution like rmp-remove-001.sql or
rmp-remove-002.sql, JCC driver is not flowing commit in warning
case as CLPPLUS fails to send command complete message to JCC.
Expected behavior is the clpplus flows the commit before the
reorgs.sh shell script execution.
To workaorund the problem, split the file execution across
multiple files as below:
-sh-4.2$ cat script1.clpplus
set echo on
set define onSET CURRENT SQLID = '$DB2SQLID';
SET CURRENT PATH = CURRENT PATH,'$DB2SQLID';
column c_date new_value v_date
column c_ldb_alias new_value v_ldb_alias
column c_sqlid new_value v_sqlid
select VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS')
c_date, rtrim(current server) c_ldb_alias, rtrim(current sqlid)
c_sqlid from sysibm.sysdummy1;
select '&v_ldb_alias','&v_date','&v_sqlid' from
sysibm.sysdummy1;
--whenever sqlerror exit
--whenever oserror exitSTA @drop-triggers.sqlSTA
@rmp-remove-001.sql
STA @rmp-remove-002.sql
QUIT
-sh-4.2$ cat script2.clpplus
set echo on
set define on
SET CURRENT SQLID = '$DB2SQLID';
SET CURRENT PATH = CURRENT PATH,'$DB2SQLID';
column c_date new_value v_date
column c_ldb_alias new_value v_ldb_alias
column c_sqlid new_value v_sqlid
select VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS')
c_date, rtrim(current server) c_ldb_alias, rtrim(current sqlid)
c_sqlid from sysibm.sysdummy1;
select '&v_ldb_alias','&v_date','&v_sqlid' from
sysibm.sysdummy1;
--whenever sqlerror exit
--whenever oserror exit
! ksh93 reorgs.sh
QUIT |
