EXEC ADS

The EXEC ADS statement is a CA IDMS extension that enables inserting CA ADS code in SQL routines.

Syntax

                ┌─────────────────────┐
►►─── EXEC ADS ─▼─ ads-process-stmnt ─┴─ ; ───────────────────────────────────►◄

Parameters

ads-process-stmnt: Specifies a CA ADS statement to be executed.

Usage

Allowable CA ADS statements

Only CA ADS statements that are allowed in a mapless dialog can be included in the body of an SQL routine.

Care should be taken in coding SQL transaction and session management statements because a ROLLBACK or COMMIT breaks the atomicity of a compound statement containing the EXEC ADS statement.

Referencing SQL-defined data

SQL-defined data can be referenced by respecting the mapping rules for identifiers and data types between SQL and CA ADS:

Underscore characters are mapped to dashes.
VARCHAR data are structures that start with a smallint field that holds the length of the character data, followed by the character data itself. The name of the structure is the mapped SQL identifier. The name of the length field is the mapped SQL identifier suffixed with "-LEN". The name of the data field is the mapped SQL identifier suffixed with "-TEXT".
Nullable SQL data must have their NULL indicators managed properly. All SQL parameters and local variables are nullable.
Date, time, and timestamp data types must be correctly processed.

Using IDD records and record elements from the dictionary

It is possible to use records and record elements that are defined as IDD records. This requires the specification of "ADD RECORD record name" in the ADS Compile Option of the CREATE PROCEDURE or CREATE FUNCTION statements. See the following example. For more information, see CREATE PROCEDURE and CREATE FUNCTION.

Using EXEC ADS for debugging

Some of the ADS utility commands can be used for debugging SQL routines. SNAP, TRACE, and WRITE TO LOG are of particular interest. See the second example below. All the SQL local variables and internal variables are contained in predefined ADS records. The name of these predefined records is constructed as follows: SQLLOCnnnnxxxxxxxx with xxxxxxxx representing the external name of the SQL routine and nnnn a four digit number with values starting from 0 for the internal variables to the total count of compound statements.

Assume an SQL routine with an associated external name of 'GETLNAME' containing two compound statements, then the content of all internal and SQL local variables can be dumped to the log as follows:

 exec ADS snap record(SQLLOC0000GETLNAME
                   , SQLLOC0001GETLNAME
                   , SQLLOC0002GETLNAME).;

A complete report of the SQL routine, including the layout of all the records can be obtained by executing the batch utility ADSORPTS. For the SQL routine with external name GETLNAME, the control statement input for ADSORPTS would look like the following:

 DIALOG=(GETLNAME),REPORTS=ALL

For the TRACE to be functional, the ADS dialog associated with the SQL routine needs to be compiled with symbol table information. This option can be turned on by specifying SYMBOL TABLE IS YES in the ADS Compile Option of the CREATE PROCEDURE or CREATE FUNCTION statements. See the next example. For more information, see CREATE PROCEDURE and CREATE FUNCTION.

Example

Using EXEC ADS to obtain the current LTERM

The SQL function USER01.TEXECADS2 returns the LTERM ID of the LTERM on which the function is being executed.

set options command delimiter '++';
create function USER01.TEXECADS2
  ( P_DUMMY   char(1)
  ) returns   char(8)
    external name TEXECAD2 language SQL
begin not atomic
 /*
 ** SQL Function to return LTERM ID using EXEC ADS
 */
 declare L_LTERMID char (8) default ' ';
 exec ads
          ACCEPT LTERM ID INTO L-LTERMID. ;
 return L_LTERMID;
end
++
set options command delimiter default++
commit;

select USER01.TEXECADS2()
  from SYSCA.SINGLETON_NULL;
*+
*+ USER_FUNC
*+ ---------
*+ VL71001

Using EXEC ADS to debug a SQL routine

In the following example, the ADS COMPILE OPTION is used to add the ADSO-APPLICATION-GLOBAL-RECORD so that it can be accessed within the SQL function. Furthermore, the ADS dialog associated with the SQL function is compiled with diagnostics and symbol table so that debugging and diagnostic information is available at run time.

An EXEC ADS statement is placed as the very first executable statement of the SQL function to snap the local variables to the IDMS log to verify the initialization and to turn on ADS tracing. The EXEC ADS statement at the end, snaps the local variables and the ADSO-APPLICATION-GLOBALE-RECORD before returning to the invoker of the function. This statement also turns the ADS tracing off.

set options command delimiter '++';
create function GET_NAME
  (P_ID NUMERIC(4))  RETURNS varchar(40)
   EXTERNAL NAME DEMOGETN LANGUAGE SQL
   ADS COMPILE OPTION
    symbol table is yes
    diagnostic is yes
    add record ADSO-APPLICATION-GLOBAL-RECORD;
 begin not atomic
 /*
 ** Get name of employee
 */
  declare FNAME char(20) default ' ';
  declare LNAME char(20) default ' ';
  declare L_STATEMENT char(160);
  EXEC ADS snap record(SQLLOC0001DEMOGETN).
                 trace all.;
  set L_STATEMENT =
     'select EMP_FNAME, EMP_LNAME' ||
     ' from DEMOEMPL.EMPLOYEE where EMP_ID = ?';
  prepare 'DYN1' from L_STATEMENT
    describe output using descriptor SQLDA;
  allocate 'CUR1' cursor for 'DYN1';
  open 'CUR1' using P_ID;
  fetch 'CUR1' into FNAME, LNAME;
  EXEC ADS snap record(SQLLOC0001DEMOGETN).
         snap record(ADSO-APPLICATION-GLOBAL-RECORD).
         trace off.;
  return trim(FNAME)|| ' ' || trim(LNAME);
end++
set options command delimiter default++
commit;
select GET_NAME(1003) from SYSCA.SINGLETON_NULL;