ALTER TABLE PROCEDURE

The ALTER TABLE PROCEDURE data description statement modifies the definition of a table procedure in the dictionary. It is also a CA IDMS extension of the SQL standard. Using the ALTER TABLE PROCEDURE statement, you can:
idmscu19
The ALTER TABLE PROCEDURE data description statement modifies the definition of a table procedure in the dictionary. It is also a CA IDMS extension of the SQL standard. Using the ALTER TABLE PROCEDURE statement, you can:
  • Add a new parameter to a table procedure
  • Revise the estimated row and I/O counts
  • Change the external name of the procedure
  • Change the size and characteristics of the work areas passed to the procedure
  • Change the execution mode of the procedure
  • Update the table procedure's synchronization timestamp
  • Change the table procedure's default database option
  • Change the table procedure's transaction sharing option
This article describes the following information:
2
2
Authorization
To issue an ALTER TABLE PROCEDURE statement, you must own or hold the ALTER privilege on the table procedure named in the statement.
Syntax
  ►►─── ALTER TABLE PROCEDURE ─┬────────────────┬─ 
table-procedure-identifier
 ──►                              └─ 
schema-name
. ─┘  ►─┬─ ADD 
parameter-definition
 ────────────────────────────┬──────────────────►◄    │         ┌────────── , ───────────┐                    │    ├─ ADD ( ─▼─ 
parameter-definition
 ─┴─ ) ────────────────┤    ├─ EXTERNAL NAME 
external-routine-name
 ─────────────────┤    ├──ESTIMATED ROWS 
row-count
 ────────────────────────────┤    ├──ESTIMATED IOS 
io-count
 ──────────────────────────────┤    ├──LOCAL WORK AREA 
local-stge-size
 ─────────────────────┤    ├──GLOBAL WORK AREA 
global-stge-size
 ─┬────────────────┬┤    │                                     └─ KEY ┬ 
key-ID
 ┬┘│    │                                            └─ NULL .┘ │    ├──USER MODE ───────────────────────────────────────────┤    ├──SYSTEM MODE ─────────────────────────────────────────┤    ├──TIMESTAMP 
timestamp-value
 ───────────────────────────┤    ├──DEFAULT DATABASE ───────────────┬─── NULL ──────┬────┤    │                                  └─── CURRENT ───┘    │    └──TRANSACTION SHARING ─────────────┬── ON ────────┬────┘                                        ├── OFF ───────┤                                        └── DEFAULT ───┘  
Expansion of parameter-definition
  ►►─── 
parameter-name
 ── 
data-type
 ─┬────────────────┬─────────────────────────►◄                                    └─ WITH DEFAULT ─┘  
Parameters
  • table-procedure-identifier
    Specifies the name of the table procedure being modified.
    Table-procedure-identifier
    must identify a table procedure defined in the dictionary.
  • schema-name
    Identifies the schema associated with the named table procedure. If you do not specify a
    schema-name
    , it defaults to:
    • The current schema associated with your SQL session, if the statement is entered 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 one or more new parameters to be associated with the table procedure. New parameters are added, in the order specified, after the last existing parameter.
    For a description of
    parameter-definition
    , see CREATE TABLE PROCEDURE.
  • external-routine-name
    Specifies the one- to eight-character name of the program which CA IDMS calls to process references to the table procedure.
  • row-count
    Specifies an integer value, in the range of 0 through 2,147,483,647, which represents the average number of rows that the table procedure returns for a given set of input parameters.
  • io-count
    Specifies an integer value, in the range of 0 through 2,147,483,647, which represents the average number of disk accesses that the table procedure generates for a given set of input parameters.
  • local-stge-size
    Specifies an integer, in the range of 0 through 32767, which represents the size, in bytes, of a local storage area that CA IDMS allocates at runtime and passes 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 related through reference to the same cursor. OPEN, CLOSE, FETCH, POSITIONED UPDATE and POSITIONED DELETE statements are related through a cursor. CA IDMS passes the same local storage area to the table procedure for all calls for one statement or related statements. CA IDMS releases the local work area when the SQL statement has completed execution or at the time the cursor is closed.
  • global-stge-size
    Specifies an integer, in the range of 0 through 32767, which represents the size, in bytes, of a 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 routines that have the same global storage key.
    If you do not specify a storage key, its value remains unchanged. To remove a storage key, specify
    NULL
    as the key.
  • USER MODE
    Specifies the table procedure should execute as a user-mode application program within CA IDMS.
  • SYSTEM MODE
    Specifies the table procedure should execute as a system-mode application program. To execute as a system mode application, 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
  • 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.
  • 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 should become the default for any database session started by the table procedure.
  • TRANSACTION SHARING
    Specifies whether transaction sharing should be enabled for database sessions started by the table procedure. If transaction sharing is enabled for a table procedure's database session, it will share 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 procedure is invoked should be retained.
Parameters for Expansion of parameter-definition
  • parameter-name
    Specifies a 1- 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
    Directs CA IDMS to pass a default value for the named parameter if no value for the parameter is specified.
    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
Specifying a Synchronization Stamp
When defining or altering a table procedure, you can specify a value for its synchronization stamp. You should use care when doing so 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.
Examples
Adding Parameters to a Table Procedure
The following ALTER TABLE PROCEDURE statement adds two new parameters to the EMP.ORG table procedure:
alter table procedure emp.org   add (job_level    decimal(1),        job_title    char(20));