DB2 - Problem description
Problem IT39058 | Status: Closed |
CLPPLUS FAILS TO FLOW COMMIT FOR WARNING CASE SCENARIOS FROM STACOMMAND WHEN RUN AS A SCRIPT | |
product: | |
DB2 CONNECT / DB2CONNCT / B50 - DB2 | |
Problem description: | |
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 | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * All Clpplus App when warnings involved * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * upgrade to 11.5.8.0 * **************************************************************** | |
Local Fix: | |
Split the file into multiple files as shown in example | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * All Clpplus App when warnings involved * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * upgrade to 11.5.8.0 * **************************************************************** | |
Comment | |
Fixed in 11.5.8.0 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 12.11.2021 07.10.2022 24.10.2022 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |