Db2 Stored & SQL Procedures - Episode #5: (In the Db2 User Group – Recap from Robert Catterall’s Tridex)

Are there still good use cases for external stored procedures?

Yes – there are two good examples:

- Need for functionality beyond what can be provided by a native SQL procedure

• That would have to be pretty advanced functionality – how advanced does data layer have to be?

- Access to data outside of Db2 (native SQL procedure can only issue SQL statements)

• If stored procedure will access data in VSAM file, and volume of execution will be fairly high, good idea to have the stored procedure access the VSAM file through a CICS transaction (if you have CICS)

• Why? Because if a stored procedure directly accesses VSAM data, the VSAM file will be opened and closed for each execution of the stored procedure – not good for scalability

• When VSAM data is accessed by way of a CICS transaction, the file is opened and allocated to CICS, and it stays open and allocated to CICS

- Note that there are ways in which a native SQL procedure can access data outside of Db2

• A native SQL procedure can call another stored procedure

• A native SQL procedure can interact with MQ via Db2 functions such as MQSEND and MQRECEIVE

• A native SQL procedure can consume Web services via Db2 functions such as SOAPHTTPNV

COBOL stored procedures versus COBOL subroutines

• Suppose that you have a COBOL program that needs to invoke another COBOL program – should the second COBOL program be invoked as a subroutine (COBOL CALL) or as a stored procedure (SQL call)?

- From a CPU efficiency perspective, right choice is COBOL CALL

• If COBOL stored procedure is invoked via SQL call, that involves a separate TCB (to which first COBOL program’s Db2 thread must be switched) in separate address space (stored procedure address space)

• COBOL subroutine would run under caller’s task in caller’s address space

- Could be a code re-use argument for using SQL CALL to invoke COBOL stored procedure

• The COBOL stored procedure could be invoked by any process that can issue a SQL statement

- If secondary COBOL program will be invoked very frequently from other COBOL programs andinvoked in some cases by non-COBOL programs, may want to maintain it in both subroutine and Db2 stored procedure form

• Oftentimes, there is very little code difference between those two forms

Recommendations for external stored procedures (1)

Stored procedure address spaces should have same priority (per z/OS WLM policy) as Db2 MSTR, DBM1 and DIST address spaces.

-       those address spaces should have priority below SYSSTC but higher than CICS application regions or IMS message regions (IRLM should be assigned to SYSSTC service class).

Why: if stored procedure address space has too-low priority, and system gets reallybusy, there could be significant delays in scheduling called stored procedures for execution – could even lead to calling program receiving SQL error code.

Question: will external stored procedures run at the priority of the address space in which they execute?

-        Answer: no – priority of stored procedure address space applies only to address space’s main task (a stored procedure – external or native SQL – always inherits the priority of its caller).

Recommendations for external stored procedures (2)

PROGRAM TYPE: SUB versus MAIN.

     -    PROGRAM TYPE is an option for CREATE (or ALTER) PROCEDURE for an external stored

          Procedure.

PROGRAM TYPE SUB has been observed to reduce CPU consumption associated with a stored procedure by 10% in some cases.

    -    However, TYPE SUB means that the program is responsible for initialization of work areas.

    -    Some users tried TYPE SUB, then went back to TYPE MAIN because former led to

         “unpredictable results,” due to stored procedure programs not effectively initializing work areas.

TYPE SUB is good for performance, but ensure that your stored procedure programs are well suited to run as subroutines.

Recommendations for external stored procedures (3)

STAY RESIDENT: YES versus NO.

STAY RESIDENT is another option for CREATE (or ALTER) PROCEDURE for an external stored procedure.

YES can improve stored procedure CPU efficiency, but it should NOT be used for stored procedure programs compiled and linked as non-reentrant and non-reusable.

-       Go with STAY RESIDENT NO for stored procedure programs that are non-reentrant and   nonreusable.

-    If STAY RESIDENT NO is specified for a frequently-executed stored procedure, module load

      time can be reduced by loading from the z/OS Virtual Lookaside Facility (VLF).