Display info about stopped Stored Procedure

Dear Martin, Yes. refreshing Db2 will reset the status for stored procedures and UDFs to available.

Sincerely,

The Db2 for z/OS Team

Hi

Just have to ask one question. Does this mean that all Stored Procedures that were stopped are automatically available for execution after Db2 refresh?

Martin

Dear Martin, Thank you for your reply. Our dev team reviewed it and determined that our stop procedure list is cleaned up when Db2 is refreshed. Therefore, you can issue -Stop Proc command again if you still need some procedures in the STOPPED state.

Sincerely,

The Db2 for z/OS Team

Hi

We IPL once every two weeks, so we have recycled Db2 several times during these four months.

As far as I know noone has issued a -STA PROC command for that procedure. I am right now checking with our System Automation guys to make sure they do not have that built into SA at start of Db2.

You say "The duration of the list where we save the stopped procedure info is the lifetime of Db2.". I guess that is the cause of the problem for us. That is why we cannot see that it is stopped and that is probably what we think should change in Db2.

Regards Martin

Dear Martin, Thank you for your update. Development has the following questions for you:

• Did you recycle your Db2 subsystem during these four months? The duration of the list where we save the stopped procedure info is the lifetime of Db2.

• Had anyone issued a -Start Procedure command?

The STOPPED procedures can only be started by a -Start Proc command. Db2 won't issue that command automatically.

Sincerely,

The Db2 for z/OS Tam

Hi Janet

Here is an example for a Cobol External Procedure

On Sept 15th 2023 I issued

-STO PROC(KEMI.KEMIL001) ACTION(REJECT) SCOPE(GROUP)

Got this message

DSNX947I -DE1A DSNX9SP2 STOP PROCEDURE SUCCESSFUL FOR KEMI.KEMIL001
DSN9035I -DE1A BEGIN OF DISPLAY FOR MEMBER: DE2A
---------------------------------------------
DSNX947I -DE2A DSNX9SP2 STOP PROCEDURE SUCCESSFUL FOR KEMI.KEMIL001
--------END OF DISPLAY FOR MEMBER: DE2A --------
DSN9022I -DE1A DSNX9COM '-STOP PROC' NORMAL COMPLETION

After that I issued this

-DIS PROC(KEMI.KEMIL001) SCOPE(GROUP)

and got this result

DSNX940I -DE1A DSNX9DIS DISPLAY PROCEDURE REPORT FOLLOWS -
------- SCHEMA=KEMI
PROCEDURE STATUS ACTIVE QUED MAXQ TIMEOUT FAIL WLM_ENV
KEMIL001
STOPREJ 0 0 0 0 0
DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE
DSN9035I -DE1A BEGIN OF DISPLAY FOR MEMBER: DE2A
---------------------------------------------
------- SCHEMA=KEMI
PROCEDURE STATUS ACTIVE QUED MAXQ TIMEOUT FAIL WLM_ENV
KEMIL001
STOPREJ 0 0 0 0 0
DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE
--------END OF DISPLAY FOR MEMBER: DE2A --------
DSN9022I -DE1A DSNX9COM '-DISPLAY PROC' NORMAL COMPLETION

I issue the same today (four months later

-DIS PROC('KEMI.KEMIL001') SCOPE(GROUP)

and get

DSNX940I -DE1A DSNX9DIS DISPLAY PROCEDURE REPORT FOLLOWS
------- SCHEMA=KEMI
DSNX9DIS PROCEDURE HAS NOT BEEN ACCESSED OR IS NOT DEFINED
DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE
DSN9035I -DE1A BEGIN OF DISPLAY FOR MEMBER: DE2A
---------------------------------------------
------- SCHEMA=KEMI
DSNX9DIS PROCEDURE HAS NOT BEEN ACCESSED OR IS NOT DEFINED
DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE
--------END OF DISPLAY FOR MEMBER: DE2A --------
DSN9022I -DE1A DSNX9COM '-DISPLAY PROC' NORMAL COMPLETION

From this information I cannot tell if the Stored Procedure is stopped or not.

I have seen it for several other SPs, both External and Native.

Martin

Hello Martin, Is this request for external procedures or SQLPL procedures? In what situation does the -display procedure command not report the STOPPED procedures?

For external and SQLPL stored procedures, if we explicitly issue -STOP PROCEDURE command against a specific procedure, it’ll be in the STOP state and the status will stay until a START PROCEDURE command is issued. Please see our experiments below.

-DB2ASTOP PROC(A.B)

-DB2ASTOP PROC(A.C) ACTION(REJECT)

-DB2ADIS PROC(A.*)

DSNX940I -DB2A DSNX9DIS DISPLAY PROCEDURE REPORT FOLLOWS -

------- SCHEMA=A

PROCEDURE STATUS ACTIVE QUED MAXQ TIMEOUT FAIL WLM_ENV

B

STOPQUE 0 0 0 0 0

C

STOPREJ 0 0 0 0 0

DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE

DSN9022I -DB2A DSNX9COM '-DISPLAY PROC' NORMAL COMPLETION

If an external stored procedure abended and Db2 disabled it (ie, stopped automatically because of error), it will also show in the report. SQLPL procedures will NOT in this case.

-DB2ADIS PROC(ADMF001.WLMSP1)

DSNX940I -DB2A DSNX9DIS DISPLAY PROCEDURE REPORT FOLLOWS - 208

------- SCHEMA=ADMF001

PROCEDURE STATUS ACTIVE QUED MAXQ TIMEOUT FAIL WLM_ENV

WLMSP1

STOPABN 0 0 1 0 1 WLMENV3

DSNX9DIS DISPLAY PROCEDURE REPORT COMPLETE

DSN9022I -DB2A DSNX9COM '-DISPLAY PROC' NORMAL COMPLETION

As you can see -DISPLAY PROCEDUR report includes the STOPPED procedures.

We look forward to hearing from you.

Sincerely,

The Db2 for z/OS Team