Writing Database Procedures › Coding Database Procedures

Coding Database Procedures

This section provides information to assist in writing database procedures.

You do not have to code or compile database procedures provided with CA IDMS/DB (for example, the IDMSNVLR procedure).

Considerations

There are two ways in which a database procedure can be invoked:

• It can be directly called by IDMSDBMS
• It can be called through a stub module that is called by IDMSDBMS.

Only fully reentrant assembler and LE-compliant COBOL and PL/I procedures can be invoked directly by IDMSDBMS. All other procedures must be called indirectly, at a cost in performance.

• Database procedures that are invoked directly by IDMSDBMS execute in system mode and MPMODE=ANY.
• For performance reasons, we recommend that all database procedures be written in fully reentrant assembler code.

Note: The methods that can be used for invoking a procedure depend on many factors including its language, calling conventions, reentrancy, and whether it issues CA IDMS DML Commands. For more information about the different methods for invoking procedures and how to choose one based on a procedure's characteristics, see 16.5, “Methods for Invoking Procedures”.

Issuing CA IDMS DML Commands in a Database Procedure

• While it is possible for database procedures to issue IDMS DML commands like navigational and SQL DML commands and commands that manipulate storage, scratch and queue resources, any such command can potentially result in a wait. A wait can result in deadlocks or degraded system performance because DBMS may be holding buffer locks when the procedure is called. If it is necessary to issue IDMS DML commands from within the procedure, consider the following:
  • Do not use the DBSTUB1 method described in B1 method described.
  • A directly-invoked assembler procedure must follow DC system mode calling conventions. For more information about DC system calling conventions and MPMODE, see "Calling Conventions for Numbered Exits" in the CA IDMS System Operations Guide.
  • A procedure that contains only IDMS DML commands associated with accessing a database can be compiled with a protocol of BATCH and execute in either a DC/UCF or a local mode address space. A COBOL or PL/I procedure that contains other (non-database access) IDMS DML commands must be compiled using the IDMS-DC protocol and can execute only in the DC/UCF address space. An assembler procedure can contain non-database access IDMS DML commands and execute in either environment provided the requested services are available. For example, requests for storage and scratch can be issued in either environment, whereas queue-related requests can only be issued within DC/UCF.
  • If the procedure accesses the database by binding a run unit or starting an SQL session, its database session is subordinate to that of the run unit under which the procedure is invoked. Therefore, actions such as FINISH or ROLLBACK that impact the invoking run-unit automatically have a similar impact on the procedure's database session if it is still active.
  • If the procedure accesses the same database as the invoking run-unit and database locks are being maintained, deadlocks between the two sessions are possible unless they are made to share the same transaction, see "Sharing Transactions Among Sessions" in the CA IDMS Navigational DML Programming Guide.
• Avoid using operating system functions that may cause the central version region to wait. This degrades performance.
• Ensure the module name is the name specified in the schema CALL statement. Database procedures are no longer linked with subschema modules. They are dynamically loaded by DBMS on the first call. The entry point name can be different from the module name.