EXECUTE

The EXECUTE statement executes a dynamically-compiled SQL statement other than SELECT. You can use this statement only in SQL that is embedded in a program.
idmscu19
The EXECUTE statement executes a dynamically-compiled SQL statement other than SELECT. You can use this statement only in SQL that is embedded in a program.
This article describes the following information:
2
2
Authorization
None required.
Syntax
  ►►─ EXECUTE 
statement-name
 ───────────────────────────────────────────────────►  ►─┬──────────────────────────────────────────────────────────────┬───────────►◄    │           ┌─────── , ─────────────┐                          │    └─ USING ─┬─▼┬─ 
host-variable
 ─────┬┴────────────────────────┬─┘              │  ├─ 
local-variable
 ────┤                         │              │  └─ 
routine-parameter
 ─┘                         │              ├─ :
dyn-buff
 sql DESCRIPTOR 
descriptor-area-name
 ──┤              ├─ BULK :
bulk-buffer
 
bulk-options
 ─────────────────┤              └─ BULK :
dyn-buff
 
dynamic-bulk-options1
 ───────────┘  
Expansion of bulk-options
  ►►──┬──────────────────────────────┬──────────────────────────────────────────►     └─ START :
start-variable-name
 ─┘  ►──┬─────────────────────────────────┬───────────────────────────────────────►◄     └─ ROWS :
row-count-variable-name
 ─┘  
Expansion of dynamic-bulk-options1
  ►►──┬──────────────────────────────┬──────────────────────────────────────────►     └─ START :
start-variable-name
 ─┘  ►── ROWS :
row-count-variable-name
 ───────────────────────────────────────────►  ►── sql DESCRIPTOR 
descriptor-area-name
 ─────────────────────────────────────►◄  
Parameters
  • statement-name
    Identifies the statement being executed.
    For detailed information, see Expansion of Statement-name.
  • USING
    Supplies values for the dynamic parameters embedded in the text of the statement.
    • host-variable
      Identifies the host variables from which CA IDMS is to retrieve values for the dynamic parameters. CA IDMS assigns the value of the first host variable to the first dynamic parameter, the second host variable to the second dynamic parameter, and so on.
      You must specify the same number of host variables in the USING parameter as the number of dynamic parameter markers in the statement text.
      In COBOL,
      host-variable
      can be an elementary data item or a non-bulk structure. If a non-bulk structure is specified, each sub-element of the structure is counted as a host variable. For detailed information, see Expansion of Host-variable.
    • local-variable
    • routine-parameter
      Identifies the local variable or routine parameter from which CA IDMS is to retrieve values for the dynamic parameters. CA IDMS assigns the value of the first local variable or routine parameter to the first dynamic parameter, the second local variable or routine parameter to the second dynamic parameter, and so on. You must specify the same number of local variables and routine parameters in the USING parameter as the number of dynamic parameter markers in the statement text.
    • :
      dyn-buff
      Identifies the variable or bulk-buffer from which CA IDMS is to retrieve values for the dynamic parameters.
      Dyn-buff
      must identify a variable previously declared in the host-language application program or SQL routine.
      The size of
      dyn-buff
      must be sufficient to hold a complete set of dynamic parameter values for a single execution of the statement. If specified as part of the BULK parameter,
      dyn-buff
      must be sufficient to hold
      row-count-variable
      sets of dynamic parameters. The format of the data in
      dyn-buff
      must conform to the description in the SQL descriptor area specified by
      descriptor-area-name
    • BULK
      Directs CA IDMS to execute the statement one or more times and to use a contiguous storage area to retrieve input values for the dynamic parameters. The specification of BULK is a CA IDMS extension of the SQL Standard.
      BULK may only be specified if the statement being executed is an INSERT statement.
    • :
      bulk-buffer
      Identifies a variable from which CA IDMS is to retrieve one or more sets of input values.
      Bulk-buffer
      must identify a variable previously declared in the host-language application program or SQL routine.
      Bulk-buffer
      must be defined as a multiple-occurring structure having the same number of sub-elements as there are dynamic parameters in the statement.
    • bulk-options
      Optionally specify the location in
      bulk-buffer
      for the first row and the number of rows to be inserted. Expanded syntax for
      bulk-options
      immediately follows the statement syntax.
    • dynamic-bulk-options1
      Provides specification for inserting one or more rows into a table.
      Expanded syntax for
      dynamic-bulk-options1
      appears immediately following the expanded syntax for
      bulk-options
      . Descriptions of
      dynamic-bulk-options1
      parameters appear above.
dyn-buff
,
bulk-buffer
,
start-variable-name
, and
row-count-variable-name
are variables that can be host variables or when the statement is used in an SQL routine local variables or routine parameters. In this case, their names must not be preceded with a colon.
Parameters for Expansion of bulk-options
  • START :
    start-variable-name
    Identifies a variable containing the relative position within the bulk buffer from which CA IDMS is to retrieve values for the first row to be inserted. Values in subsequent entries in the bulk buffer are retrieved sequentially, each set corresponding to a row to be inserted.
    Start-variable-name
    must be a variable previously declared in the host-language application program or SQL routine. The value in the variable must be an integer in the natural range of subscripts for arrays in the language in which the application program is written.
    If you do not specify the START parameter, CA IDMS retrieves the values from the first entry in the bulk buffer.
  • ROWS :
    row-count-variable-name
    Identifies a variable that specifies the number of rows CA IDMS is to retrieve from the bulk buffer.
    Row-count-variable-name
    must be a variable previously declared in the host-language application program or SQL routine. The value in the variable must be in the range 1 through the number of rows that will fit in the bulk buffer.
    If you do not specify the ROWS parameter, CA IDMS retrieves rows from the array sequentially until reaching the end of the buffer.
Parameters for Expansion of dynamic-bulk-options1
An additional parameter is used with
dynamic-bulk-options1
.
  • SQL DESCRIPTOR
    Specifies the SQL descriptor area that describes the format of the dynamic parameter values contained in
    dyn-buff
    .
  • descriptor-area-name
    Directs CA IDMS to use the named area as the descriptor area.
    Descriptor-area-name
    must identify an SQL descriptor area.
Usage
Dynamically-compiled SELECT Statements
You cannot use the EXECUTE statement with a dynamically-compiled SELECT statement. To retrieve data using a dynamically-compiled SELECT statement, you must define a cursor and use the FETCH statement.
Use of the Descriptor Area
When describing the format of dynamic parameters with an SQL descriptor area, you can use the INPUT option of the DESCRIBE statement to determine the format of the parameters that CA IDMS has assumed based on the context in which they appear. You can alter the contents of the descriptor area provided that the data types remain compatible. However, all changes to the descriptor area must be made
before the first time the EXECUTE statement for the given dynamically-compiled statement is executed
. The contents of the descriptor area must remain unchanged for each subsequent execution.
Examples
Executing a Dynamically-compiled Statement
The following EXECUTE statement executes the dynamically-compiled statement named DYN_PROJ:
EXEC SQL    EXECUTE DYN_PROJ END EXEC
More Information