Version 4.2.6 for Mainframes
 —  Database Management System Interfaces  —

NDB - Example Stored Procedure

This section describes the example stored procedure NDBPURGN, a Natural subprogram which purges Natural objects from the buffer pool used by the Natural stored procedures server.

This section covers the following topics:


Members of NDBPURGN

The example stored procedure NDBPURGN comprises the following text members which are stored in the Natural system library SYSDB2:

Member Explanation
CR5PURGN

Input member for SYSDB2 ISQL.

Contains SQL statements used to declare NDBPURGN in DB2 Version 5.

CR6PURGN

Input member for SYSDB2 ISQL.

Contains SQL statements used to declare NDBPURGN in DB2 Version 6 and above.

NDBPURGP The client (Natural) program which
  • Requests the name of the program to be purged and the library where it resides,

  • Invokes the stored procedure NDBPURGN and

  • Reports the outcome of the request.

NDBPURGN

The stored procedure which purges objects from the buffer pool.

NDBPURGN invokes the application programming interface USR0340N supplied in the Natural system library SYSEXT.

Therefore, USR0340N must be available in the library defined as the steplib for the execution environment.

Top of page

Defining the Stored Procedure NDBPURGN

Start of instruction setTo define the example stored procedure NDBPURGN

  1. Define the stored procedure in the DB2 catalog by using the SQL statements provided as text members CR5PURGN (for DB2 Version 5) and CR6PURGN (for DB2 Version 6).

  2. Specify the name of the Natural stored procedure stub (here: NDB41SRV) as LOADMOD (V5) or EXTERNAL NAME (V6). The Natural stored procedure stub is generated during the installation by assembling the NDBSTUB macro.

  3. As the first parameter, pass the internal Natural parameter STCB to the stored procedure. The STCB parameter is a VARCHAR field which contains information required to invoke the stored procedure in Natural:

    The STCB parameter is generated automatically by the CALLMODE=NATURAL clause of the CALLDBPROC statement and is removed from the parameters passed to the Natural stored procedure by the server stub. Thus, STCB is invisible to the caller and the stored procedure. However, if a non-Natural client intends to call a Natural stored procedure, the client has to pass the STCB parameter explicitly. See also Stored Procedure Control Block below.

Stored Procedure Control Block (STCB)

Below is the Stored Procedure Control Block (STBC) generated by the CALLMODE=NATURAL clause as generated by the stored procedure NDBPURGN before and after execution. Changed values are emphasized in boldface:

STCB before Execution:

004C82   0132F0F3  F0F6E2E3  C3C2F3F1  F040C8C7  *..0306STCB310 HG*    11097D42
004C92   D2404040  4040C8C7  D2404040  4040D5C4  *K     SAG     ND*    11097D52
004CA2   C2D7E4D9  C7D74040  40404040  4040F0F5  *BPURGP        05*    11097D62
004CB2   F7F0D5C4  C2D7E4D9  C7D5F0F0  F0F6F0F9  *70NDBPURGN000609*    11097D72
004CC2   F9F9F940  40404040  40404040  40404040  *999             *    11097D82
004CD2   40404040  40404040  40404040  40404040  *                *    11097D92
004CE2   40404040  40404040  40404040  40404040  *                *    11097DA2
004CF2   40404040  40404040  40404040  40404040  *                *    11097DB2
004D02   40404040  40404040  40404040  40404040  *                *    11097DC2
004D12   40404040  40404040  40404040  40404040  *                *    11097DD2
004D22   40404040  40404040  40404040  40404040  *                *    11097DE2
004D32   40404040  40404040  40404040  40404040  *                *    11097DF2
004D42   40404040  40404040  40404040  40404040  *                *    11097E02
004D52   40404040  40404040  40404040  40404040  *                *    11097E12
004D62   40404040  40404040  40404040  40404040  *                *    11097E22
004D72   40404040  40404040  40404040  40404040  *                *    11097E32
004D82   40404040  40404040  40404040  40404040  *                *    11097E42
004D92   40404040  D4C1F86B  F0D4C1F4  F06BF0D4  *    MA8,0MA40,0M*    11097E52
004DA2   C2F26BF0  D4C2F26B  F0D4C9F2  6BF0D4C9  *I2,0MI2,0MI2,0MI*    11097E62
004DB2   F26BF04B                                *2,0.            *    11097E72

STCB after Execution:

004C82   0132F0F3  F0F6E2E3  C3C2F3F1  F040C8C7  *..0306STCB310 HG*    11097D42
004C92   D2404040  4040C8C7  D2404040  4040D5C4  *K     SAG     ND*    11097D52
004CA2   C2D7E4D9  C7D74040  40404040  4040F0F5  *BPURGP        05*    11097D62
004CB2   F7F0D5C4  C2D7E4D9  C7D5F0F0  F0F6F0F0  *70NDBPURGN000600*    11097D72
004CC2   F0F0F040  40404040  40404040  40404040  *000             *    11097D82
004CD2   40404040  40404040  40404040  40404040  *                *    11097D92
004CE2   40404040  40404040  40404040  40404040  *                *    11097DA2
004CF2   40404040  40404040  40404040  40404040  *                *    11097DB2
004D02   40404040  40404040  40404040  40404040  *                *    11097DC2
004D12   40404040  40404040  40404040  40404040  *                *    11097DD2
004D22   40404040  40404040  40404040  40404040  *                *    11097DE2
004D32   40404040  40404040  40404040  40404040  *                *    11097DF2
004D42   40404040  40404040  40404040  40404040  *                *    11097E02
004D52   40404040  40404040  40404040  40404040  *                *    11097E12
004D62   40404040  40404040  40404040  40404040  *                *    11097E22
004D72   40404040  40404040  40404040  40404040  *                *    11097E32
004D82   40404040  40404040  40404040  40404040  *                *    11097E42
004D92   40404040  D4C1F86B  F0D4C1F4  F06BF0D4  *    MA8,0MA40,0M*    11097E52
004DA2   C2F26BF0  D4C2F26B  F0D4C9F2  6BF0D4C9  *I2,0MI2,0MI2,0MI*    11097E62
004DB2   F26BF04B                                *2,0.            *    11097E72

Top of page