CREATE TABLE PROCEDURE

The CREATE TABLE PROCEDURE data description statement stores the definition of a table procedure in the SQL catalog. You can refer to the table procedure in SQL SELECT, INSERT, UPDATE, and DELETE statements just as you would a table. These references result in CA IDMS calls to the corresponding external routine. Although such routines can perform any action, you use them typically to manipulate data stored in some other organization (for example, in a network-defined database or in a set of VSAM files).
idmscu19
The CREATE TABLE PROCEDURE data description statement stores the definition of a table procedure in the SQL catalog. You can refer to the table procedure in SQL SELECT, INSERT, UPDATE, and DELETE statements just as you would a table. These references result in CA IDMS calls to the corresponding external routine. Although such routines can perform any action, you use them typically to manipulate data stored in some other organization (for example, in a network-defined database or in a set of VSAM files).
You use the formal parameters of a table procedure definition like the columns of a table during a procedure invocation. You can put values in and return them from the table procedure using column-like syntax.
The CREATE TABLE PROCEDURE statement is a CA IDMS extension of the SQL standard.
This article describes the following information:
2
2
Authorization
To issue a CREATE TABLE PROCEDURE statement, you must either own the schema in which the table procedure is being defined or hold the CREATE privilege on the named table procedure.
Syntax
►►─── CREATE TABLE PROCEDURE ─┬───────────────┬─
table-procedure-identifier
──► └─
schema-name
. ┘ ┌──────────── , ────────┐ ►─── ( ─▼─
parameter-definition
┴ ) EXTERNAL NAME
external-routine-name
─────► ►────┬─────────────────────────────────┬─┬──────────────────────────┬────────► └─── ESTIMATED ROWS
row-count
────┘ └─ ESTIMATED IOS
io-count
─┘ ►────┬───────────────┬───────────────────────────────────────────────────────► ├─ USER MODE ◄──┤ └─ SYSTEM MODE ─┘ ►────┬──────────────────────────────────────┬────────────────────────────────► └─ LOCAL WORK AREA ──
local-stge-size
─┘ ►────┬────────────────────────────────────────────────────────┬──────────────► └─ GLOBAL WORK AREA ──
global-stge-size
──┬──────────────┤ └─
KEY key-id
─┘ ►────┬───────────────────────────────────────────────────────────────┬───────► └─ TRANSACTION SHARING ───────────────────┬─ ON ───────┬────────┘ ├─ OFF ──────┤ └─ DEFAULT ◄─┘ ►────┬───────────────────────────────────────────────────────────────┬───────► └─ DEFAULT DATABASE ────────────────────┬ NULL ◄───┬───────────┘ └─CURRENT──┘ ►────┬───────────────────────────────────────────┬───────────────────────────►◄ └── TIMESTAMP
timestamp-value
─────────────┘
Expansion of parameter-definition
►►───
parameter-name
──
data-type
─┬────────────────┬─────────────────────────►◄ └─ WITH DEFAULT ─┘
Parameters
  • table-procedure-identifier
    Specifies the 1-character to 18-character name of the table procedure you are creating.
    table-procedure-identifier
    must:
    • Be unique among the table, view, function, procedure, and table procedure identifiers within the schema associated with the table procedure
    • Follow conventions for SQL identifiers
  • schema-name
    Specifies the schema name qualifier to be associated with the table procedure.
    Schema-name
    must identify a schema that is defined in the dictionary. If you do not specify a
    schema-name
    , it defaults to:
    • The current schema associated with your SQL session, if the statement is specified through the Command Facility or executed dynamically
    • The schema associated with the access module used at runtime, if the statement is embedded in an application program
  • parameter-definition
    Defines a parameter to be associated with the table procedure. Parameters are passed to the table procedure in the order they are specified. The list of parameters must be enclosed in parentheses. Multiple parameter definitions must be separated by commas.
    Expanded syntax for
    parameter-definition
    is shown immediately following the CREATE TABLE PROCEDURE syntax. See descriptions for these parameters at the end of this section.
  • external-routine-name
    Specifies the one- to eight-character name of the program which is called to process references to the table procedure.
  • row-count
    Specifies an integer value, in the range 0 through 2,147,483,647, representing the average number of rows returned by the table procedure for a given set of input parameters.
  • io-count
    Specifies an integer value, in the range 0 through 2,147,483,647, that represents the average number of disk accesses generated by the table procedure for a given set of input parameters.
  • USER MODE
    Specifies the table procedure should execute as a user-mode application program within CA IDMS. This value is the default, unless SYSTEM MODE is specified.
  • SYSTEM MODE
    Specifies the table procedure should execute as a system mode application program. To execute in system mode, the program must be fully reentrant and be written in either:
    • Assembler using DC calling conventions
    • COBOL or PL/I and compiled with an LE-compliant compiler
  • local-stge-size
    Specifies an integer, in the range 0 through 32767, which represents the size, in bytes, of a local storage area which is allocated by CA IDMS at runtime and passed to the table procedure on each invocation.
    CA IDMS allocates a local storage area on the first call to a table procedure for each SQL statement within a transaction or for a set of SQL statements which are related through reference to the same cursor (OPEN, FETCH, CLOSE, positioned UPDATE, and DELETE statements are related through a cursor). The same local storage area is passed to the table procedure for all calls for one statement or related statements. When the SQL statement has completed execution or when the cursor is closed, the local work area is released.
    If you do not code a LOCAL WORK AREA clause, the default local storage size is 1024 bytes.
  • global-stge-size
    Specifies an integer, in the range 0 through 32767, that represents the size, in bytes, of the global storage area that CA IDMS allocates at runtime and passes to the table procedure on each invocation.
    CA IDMS allocates a global storage area once within a transaction and retains it until the transaction terminates.
  • key-id
    Specifies the one- to four-character identifier for the global storage area. CA IDMS passes the same piece of global storage within a transaction to all SQL routines that have the same global storage key.
    If you do not specify a storage key, CA IDMS allocates each table procedure its own global storage area. This storage area is not used for any other routine within the transaction.
  • TRANSACTION SHARING
    Specifies whether transaction sharing should be enabled for database sessions started by the table procedure. If transaction sharing is enabled for a database session, it shares the current SQL session's transaction.
    • ON
      Specifies that transaction sharing should be enabled
    • OFF
      Specifies that transaction sharing should be disabled.
    • DEFAULT
      Specifies that the transaction sharing setting that is in effect when the table procedure is invoked should be retained.
  • DEFAULT DATABASE
    Specifies whether a default database should be established for database sessions started by the table procedure.
    • NULL
      Specifies that no default database should be established.
    • CURRENT
      Specifies that the database to which the SQL session is connected become the default for any database session that is started by the table procedure.
  • timestamp-value
    Specifies the value of the synchronization stamp to be assigned to the table procedure.
    Timestamp-value
    must be a valid external representation of a timestamp.
Parameters for Expansion of parameter-definition
  • parameter-name
    Specifies a 1-character to 32-character name of a parameter to be passed to the table procedure.
    Parameter-name
    must:
    • Be unique within the table procedure that you are defining
    • Follow the conventions for SQL identifiers
    All parameters are implicitly nullable. Input parameters can be assigned NULL as a parameter value and output parameters can return NULL.
  • data-type
    Defines the data type for the named parameter. For expanded
    data-type
    syntax, see Expansion of Data-type.
  • WITH DEFAULT
    if no value for the parameter is specified, CA IDMS passes a default value for the named parameter.
    The default value for a parameter is based on its data type:
Column data type
Default value
CHARACTER
Blanks
VARCHAR
A character string literal with a length of zero (that is, '')
GRAPHIC
Double-byte blanks
VARGRAPHIC
A double-byte character string literal with a length of zero
DATE
The value in the CURRENT DATE special register
TIME
The value in the CURRENT TIME special register
TIMESTAMP
The value in the CURRENT TIMESTAMP special register
All numeric data types
0 (zero)
Usage
Influencing Join Strategies
CA IDMS uses estimated row and I/O counts to determine the cost of joining a table procedure with other tables, views, or table procedures. To determine the optimal access strategy, CA IDMS examines different sequences for retrieving information. By providing the estimated row and I/O counts for the table procedure and for each access key that is used by the table procedure, CA IDMS can select the optimal access strategy.
To determine the cost of a specific access strategy, CA IDMS uses estimates that are provided in CREATE TABLE PROCEDURE. If input values are available for the parameters that are included in a key, CA IDMS uses the estimates that are specified in the CREATE KEY statement instead.
Specifying a Synchronization Stamp
When defining or altering a table procedure, you can specify a value for its synchronization stamp. However, use care because the purpose of the stamp is to enable the detection of discrepancies between an entity and its definition. If explicitly specified, you must set the synchronization stamp to a new value following a change so that the change is detectable by the runtime system.
If not specified, the synchronization stamp is automatically set to the current date and time.
Example
The following CREATE TABLE PROCEDURE statement defines a table procedure.
create table procedure emp.org (top_key unsigned numeric(4), level smallint, mgr_id unsigned numeric(4), mgr_lname char(25) emp_id unsigned numeric (4), emp_lname char(25) start_date DATE, structure_code char(2)) external name procorgu local work area 800 global work area 600 KEY EMP estimated rows 100 estimated ios 50;
More Information