DB2 stored procedures

DB2 Stored Procedures managed by WLM-Managed DB2 SPAS (Stored Procedure Address Spaces) have been supported by tableBASE since Version 6. Each DB2 SPAS environment is similar to using tableBASE in a multitasking batch environment (see Online and multitasking environment considerations).

The first Stored Procedure that runs in a DB2 SPAS causes tableBASE to initialize and a local TSR is created for that DB2 SPAS. Therefore, tables that are in a local TSR are not shared between DB2 SPAS. A VTS-TSR must be used for Stored Procedure usage.

The local TSR should not be used for tables created or accessed by Stored Procedures. Since the WLM-Managed DB2 SPAS determines the address space(s) in which a Stored Procedure executes, it will be unpredictable as to which DB2 SPAS a Stored Procedure will end up executing in. In addition, if the first Stored Procedure that uses tableBASE in the WLM-Managed DB2 SPAS abends, the local TSR in that DB2 SPAS vanishes.

Linked VTS tables should also not be used in a Stored Procedure (these are tables with a VTS:xxxx entry in the ML-List or tables accessed using the VTSFIRST and VTSLAST TBOPT options). When accessing linked VTS tables, tableBASE creates an entry in the local TSR which re-directs accesses of the table to the VTS-TSR.

The user should also understand the following:

  • tableBASE manages tables in-memory, and consequently, relies on the user to store (ST) any updated tables.
  • If for any reason the VTS-TSR being used by the DB2 SPAS abends or comes down, any table updates not yet stored are lost. However, if the DB2 SPAS ends abnormally, the table updates are still available in the VTS-TSR and may be stored by transactions initiated after the DB2 SPAS is re-started.

All DB2 Stored Procedures accessing tableBASE must be linked AMODE(31), RMODE(ANY).

Run-time JCL for SPAS is similar to the JCL stream used in tableBASE batch environments (see MVS run-time JCL requirements), and should include DDs for any tableBASE libraries to be accessed by applications (DB2 Stored Procedures).