This section discusses the following special considerations for table procedures, procedures and functions, commonly referred to as SQL-invoked external routines.
Since it is likely an SQL-invoked external routine will execute within a batch address space because of local mode access and within the DC/UCF address space, it should be independent of the runtime environment.
If your SQL-invoked external routine issues only database requests, use a protocol mode of BATCH to ensure it executes in local mode and within the DC/UCF address space.
If an SQL-invoked external routine is executed in local mode (for example, through IDMSBCF), it must limit itself to database requests only or be written in Assembler (or use an Assembler subroutine for DC/UCF requests) or CA ADS.
Even if the SQL-invoked external routine is written in Assembler, many DC services such as Print, Queue and Terminal I/O are not supported in local mode and should not be used. Scratch and storage requests issued from an assembler program are supported in local mode. Terminal I/O services are not supported in local mode or within the DC/UCF address space.
If an SQL-invoked external routine is executed within DC/UCF, it should not contain statements that interfere with, or are prohibited from, that environment. For example, you should avoid DISPLAY statements in COBOL and GETMAIN requests in Assembler. Follow the rules specified in the appropriate CA IDMS DML Reference Guide when coding your SQL-invoked external routine.
When an SQL-invoked external routine is written as a mapless dialog in CA ADS, the CA ADS batch environment must have been configured resulting in an ADSOOPTI load module being available in the local mode load libraries.
Run units and SQL transactions opened within an SQL-invoked external routine are managed automatically as part of the invoking SQL session's transaction. If the invoking application issues a COMMIT WORK, all subordinate transactions opened by SQL-invoked external routines are also committed. Similarly, if the invoking SQL session is rolled out, all subordinate transactions are also rolled out.
Although an SQL-invoked external routine is free to terminate its own transactions independently from the invoking SQL session, it should do so only if it made no changes to the database.
Terminating the invoking SQL transaction, either through a COMMIT or ROLLBACK operation, affects SQL-invoked external routines in the following ways:
When the invoking SQL transaction is committed through a COMMIT CONTINUE operation, the only effect on SQL-invoked external routines is that database changes that the SQL-invoked external routine made are committed.
If an SQL session which invokes an SQL-invoked external routine is suspended, the SQL-invoked external routine is also suspended, with the following results:
In most cases, no special action is required of the SQL-invoked external routine during a suspend operation. The SQL-invoked external routine is called once for each open scan that exists at the time the session is suspended. If the SQL-invoked external routine has acquired temporary storage, it might need to save its contents somewhere else (such as in kept storage or in a scratch area) that is preserved across a pseudo-converse; otherwise, the SQL-invoked external routine may ignore Suspend Scan requests.
Similarly, the SQL-invoked external routine may ignore Resume Scan requests unless it needs to restore a temporary storage area. The first request to an SQL-invoked external routine after a resume is a Resume Scan, if the request involves a scan that was previously suspended. If the request does not involve a previously-suspended scan, the SQL-invoked external routine might not be aware that a suspend and resume has occurred. The contents of the SQL-invoked external routine's work areas are the same as before the suspend, and any run units or SQL sessions previously started by the SQL-invoked external routine resume automatically on the next database request.
The SQL-invoked external routine has two arguments to signal an exception condition back to CA IDMS. These arguments consist of a five-character SQLSTATE code and an 80-byte message area. The following table lists valid SQLSTATE codes and their descriptions.
|
Value |
Description |
|---|---|
|
00000 |
Request was successful |
|
01Hxx |
Request was successful but the SQL-invoked external routine generated a warning message |
|
02000 |
No more rows to be returned |
|
38xxx |
The SQL-invoked external routine has detected an error during processing |
CA IDMS examines the SQLSTATE value to determine whether the operation was successful. An SQLSTATE value of 0200 indicates that all rows have been returned. It is meaningful only on a Next Row request and cannot be set while at the same time returning a row since CA IDMS ignores parameter arguments if an SQLSTATE value of 02000 is set.
If an SQLSTATE value indicates that an error or warning condition exists, CA IDMS embeds the message text returned by the SQL-invoked external routine in a standard DB message and returns it to the calling application through the message area of the SQLCA. It also translates the SQLSTATE value into one of the following SQLCODE values:
|
SQLSTATE Value |
SQLCODE Value |
|---|---|
|
00000 |
0 |
|
01Hxx |
1 |
|
02000 |
100 |
|
38xxx |
-4 |
If the SQL-invoked external routine signals an error, CA IDMS automatically rolls out all database changes that the SQL-invoked external routine made while processing the SQL statement that caused the SQL-invoked external routine to be invoked. For example, if the invoking SQL statement was a searched update and ten rows had been updated before the error was detected, changes to all ten rows are rolled out automatically. Database changes made prior to the execution of the searched update statement are not rolled out.
If an SQL-invoked external routine has a parameter with a data type of DATE, TIME, or TIMESTAMP, the values passed to and from the SQL-invoked external routine are in the eight-byte internal datetime format. To interpret incoming parameters, the SQL-invoked external routine must first convert them to external format using either the IDMSIN01 subroutine or the #XTRA macro. Similarly, before returning a datetime parameter value, the SQL-invoked external routine must convert the external format to the internal format again using either IDMSIN01 or #XTRA.
To avoid this type of conversion, you can define date/time parameters using a character data type which may then be converted to a DATE, TIME, or TIMESTAMP in DML statements using the CAST function. However, this method requires that the invoking application or end-user specify the CAST operation, and that the SQL-invoked external routine validate the datetime on update and insert values.
Note: For more information about datetime parameters, see "Calls to IDMSIN01" in the CA IDMS Callable Services Guide.
The transaction mode of an SQL session which invokes an SQL-invoked external routine is propagated to the subordinate transactions started by the SQL-invoked external routine. If the SQL-invoked external routine starts an SQL session, its default transaction mode is the same as the transaction mode associated with the invoking transaction. If the SQL-invoked external routine binds a run unit and the transaction mode of the invoking SQL transaction is READ ONLY, all update ready modes are converted automatically to SHARED RETRIEVAL. The net result is that an SQL-invoked external routine invoked by a transaction in a READ ONLY state is unable to update the database.
If the SQL-invoked external routine is executed within the DC/UCF address space, you must define the program to the DC/UCF system using one of the following:
Compile and link options vary depending on the language in which the SQL-invoked external routine is written and, in some cases, on the version of the compiler used. The following guidelines apply to compile and link options for SQL-invoked external routines:
You should not rely on the contents of COBOL working storage to be retained across calls, nor should you rely on initialization to establish values on the first request for a scan if the value might have been altered in a prior request.
The allocation and initialization of working storage varies depending on whether:
Use working storage only for the following:
Other types of data, such as first-time flags or variables set in one call that are used within another, must be defined within either a local or global work area.
|
Copyright © 2014 CA.
All rights reserved.
|
|