IZ66371: SQL774N AFTER INTERRUPT OF SQL PROCEDURE CONTAINING ONE OR MORE ATOMIC COMPOUND STATEMENTS

DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• If you terminate an SQL procedure containing an atomic compound
  statement by interrupting the procedure, any statement that run
  on that connection (outside of the stored procedure) may result
  in SQLCODE -774.
  
  Example:
  
  db2 "call proc_stat_chngs_1(1)"
  ^CSQL0952N Processing was cancelled due to an interrupt.
  SQLSTATE=57014
  
  $ db2 commit
  SQL0774N  The statement cannot be executed within an ATOMIC
  compound SQL
  statement.  SQLSTATE=2D522
  
  db2 "insert into session.del_milestones values ('1','1','1')"
  DB21034E The command was processed as an SQL statement because
  it was not a
  valid Command Line Processor command.  During SQL processing it
  returned:
  SQL0774N  The statement cannot be executed within an ATOMIC
  compound SQL
  statement.  SQLSTATE=2D522
  
  db2 terminate
  SQL0774N The statement cannot be executed within an ATOMIC
  compound SQL
  statement.  SQLSTATE=2D522
  

Local fix

• Add the NEW SAVEPOINT LEVEL option to the CREATE PROCEDURE
  statement for an SQL procedure.  That procedure will not cause
  subsequent -774 errors if interrupted.
  
  CREATE PROCEDURE TEST(in N integer)
  LANGUAGE SQL
  NEW SAVEPOINT LEVEL
  BEGIN ATOMIC
    -- ...
  END
  
  This modification need only be applied to SQL procedures
  containing BEGIN ATOMIC statements, and only to those that are
  likely subject to interruption (e.g., by Ctrl-C, FORCE
  APPLICATION, etc.).  You must recompile any modified procedures
  to apply the workaround.
  

Problem summary

• If you terminate an SQL procedure containing an atomic compound
  statement by interrupting the procedure, any statement that run
  on that connection (outside of the stored procedure) may result
  in SQLCODE -774.
  
  Example:
  
  db2 "call proc_stat_chngs_1(1)"
  ^CSQL0952N Processing was cancelled due to an interrupt.
  SQLSTATE=57014
  
  $ db2 commit
  SQL0774N  The statement cannot be executed within an ATOMIC
  compound SQL
  statement.  SQLSTATE=2D522
  
  db2 "insert into session.del_milestones values ('1','1','1')"
  DB21034E The command was processed as an SQL statement because
  it was not a
  valid Command Line Processor command.  During SQL processing it
  returned:
  SQL0774N  The statement cannot be executed within an ATOMIC
  compound SQL
  statement.  SQLSTATE=2D522
  
  db2 terminate
  SQL0774N The statement cannot be executed within an ATOMIC
  compound SQL
  statement.  SQLSTATE=2D522
  

Problem conclusion

• First fixed in DB2 UDB Version 9.1, FixPak 9.
  

Temporary fix

• LOCAL FIX:
  
  Add the NEW SAVEPOINT LEVEL option to the CREATE PROCEDURE
  statement for an SQL procedure.  That procedure will not cause
  subsequent -774 errors if interrupted.
  
  CREATE PROCEDURE TEST(in N integer)
  LANGUAGE SQL
  NEW SAVEPOINT LEVEL
  BEGIN ATOMIC
    -- ...
  END
  
  This modification need only be applied to SQL procedures
  containing BEGIN ATOMIC statements, and only to those that are
  likely subject to interruption (e.g., by Ctrl-C, FORCE
  APPLICATION, etc.).  You must recompile any modified procedures
  to apply the workaround.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  edures
  ose that are
  ORCE
  ed procedures
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  IC64958 IC64962

Fix information

• Fixed component name

  DB2 UDB ESE SOL

• Fixed component ID

  5765F4102

Applicable component levels

• R910 PSY

     UP