How to catch ctrl-c behavior in DB2 stored procedure

Question

I am using DB2 11.5.

I have a stored procedure that will run some complex tasks. Before running the tasks, it will first check from a log table if the job is already running, if yes, it signal for SQLSTATE 75002 with error meesage.

If it is not already running, it will insert a record of the job with status RUNNING, then run the tasks.

When it finishes, it update the status to FINISHED.

CREATE OR REPLACE PROCEDURE WORK.TEST_SP()
P1: BEGIN
   
   if exists(select 1 from db2inst1.job_log where job='abc' and status='RUNNING' and date=current date) then
      SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = 'Job abc is already running, please wait for it to finish';
   end if;

   insert into db2inst1.job_log values ('abc', 'RUNNING', current date);
   commit;

   -- Some complex tasks here
   call dbms_lock.sleep(120);

   update db2inst1.job_log set job_status='FINISHED' where job_name='abc' and job_date=current date
   commit;

END P1

My question is how do I handle sigint when user press ctrl-c that aborted the stored procedure when the complex tasks are running?

I want it to update the job_status to ABORTED when ctrl-c occurs so that the job will not be "running" forever.

#Edit 1

Users run the stored procedure with a windows .bat file on local machine with db2 client installed.

@echo off
@if ""%DB2CLP%""=="""" db2cmd /c /i /w ""%0"" && goto :EOF
db2 connect to mydb user db2inst1 using abc123
db2 "call WORK.TEST_SP()"

IF ERRORLEVEL 1 (echo Job failed) else (echo Job done)

db2 connect reset > nul
pause

Answer 1

If your MS-Windows batch file gets interrupted by a Control-C or other signal, then any already started/running stored-procedures invoked by that app will continue running by default. The stored procedure will be unaware that the client application has terminated. So your batch file (cmd/bat) will terminate but any currently running stored procedure will continue to execute on the Db2-server.

You cannot send operating-system signals directly to a Db2-LUW stored procedure, as they run on the Db2-server in the background and are usually owned by a different account than the userid performing the call.

Your stored-procedure should have its own condition handlers or exit handlers or undo handlers. Usually you want to issue a rollback if a hard error happens from which your procedure itself cannot recover. But Db2 itself will issue a rollback for specific sqlcodes (e.g. -911 ).

Db2-LUW also has a sysproc.cancel_work procedure which an application might use in specific situations. Refer to the Knowledge Centre for details. If WLM (workload management) or equivalent is enabled then stored procedures are subject to its configuration as regards resource consumption, and WLM also offers a wlm_cancel_activity routine.

Answer 2

There is no way to do this in SP.
Control is not passed to an exception handler defined in SP upon forcing a caller off the database, canceling activity and some other conditions (log full, for example).

So, don't put any flag / status management logic into SP exception handlers.

Answer 3

How is the stored procedure run? From the command line (db2)? If so, on what operating systems?

If, for instance, the command is run from bash on Linux, you can use trap myfunc SIGINT in Bash to run a custom Bash function myfunc if the user presses Ctrl-C. myfunc could then change the job status.

On Windows, you will have more control if you switch from plain .bat files to Powershell . Some related Stack Overflow questions:

• batch script if user press Ctrl+C do a command before exiting
• Gracefully stopping in Powershell