ALTER FUNCTION

The ALTER FUNCTION data description statement modifies the definition of a function in the dictionary.

Using the ALTER FUNCTION statement, you can:

Revise the estimated row and I/O counts
Change the external name of the function
Change the size and characteristics of the work areas passed to the function
Change the execution mode of the function
Change the protocol
Change the language of the function
Change the timestamp
Change the default database
Change the transaction sharing mode

The ability to change attributes other than language and external name is a CA IDMS extension of the SQL standard.

Authorization

To issue an ALTER FUNCTION statement, you must either own or hold the ALTER privilege on the function named in the statement.

Syntax

►►─ ALTER FUNCTION ─┬────────────────┬─ function-identifier ──────────────────►
                    └─ schema-name. ─┘

 ►─┬─ 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 ─────────────────────────────────────────┤
   ├─ PROTOCOL ───────────────────────┬─── IDMS ──────┬────┤
   │                                  └─── ADS ───────┘    │
   ├─ language-clause ─────────────────────────────────────┤
   ├─ TIMESTAMP timestamp-value ───────────────────────────┤
   ├─ DEFAULT DATABASE ───────────────┬─── NULL ──────┬────┤
   │                                  └─── CURRENT ───┘    │
   └─ TRANSACTION SHARING ─────────────┬── ON ────────┬────┘
                                       ├── OFF ───────┤
                                       └── DEFAULT ───┘

Expansion of language-clause

►►─── LANGUAGE ────────────────────┬─ ADS ──────────┬──────────────────────────►◄
                                   ├─ ASSEMBLER ────┤
                                   ├─ COBOL ────────┤
                                   ├─ PLI ──────────┤
                                   └─ SQL ──────────┘

Parameters

function-identifier

Specifies the name of the function being modified. Function-identifier must identify a function defined in the dictionary.

schema-name

Identifies the schema associated with the named function.

If you do not specify a schema-name, the default value is:

The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically.
The SQL schema associated with the access module used at runtime, if the statement is embedded in an application program.

external-routine-name

Specifies the one- to eight-character name of the program which CA IDMS calls to process function invocations.

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 CA IDMS optimizer uses for cost calculation of the function invocation.

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 function 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 function on each invocation.

CA IDMS allocates a local storage area on the first call to a function.

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 function 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 that the function should execute as a user-mode application program within CA IDMS. Do not specify user mode for functions specified with protocol ADS, such as is the case with functions written as CA ADS mapless dialogs or written in SQL.

SYSTEM MODE

Specifies that the procedure should execute as a system-mode application program. To execute as a system mode application, the program must be fully reentrant and written in either:

ADS as a mapless dialog
SQL
Assembler using DC calling conventions
COBOL or PL/I and compiled with an LE-compliant compiler

PROTOCOL

Specifies the environment.

IDMS: Use IDMS for SQL-invoked functions that are written in COBOL, PL/I, or Assembler.
ADS: Use ADS for SQL-invoked functions that are written in SQL or CA ADS. The name of the dialog that will be loaded and run when the SQL function is invoked is given by the external-routine-name in the EXTERNAL NAME clause. Setting the protocol to ADS, requires the function to have its mode set to system.

language-clause

Specifies the programming language of the function.

timestamp-value

Specifies the value of the synchronization stamp to be assigned to the function. 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 function.

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 function.

TRANSACTION SHARING

Specifies whether to enable transaction sharing for database sessions started by the function. If transaction sharing is enabled for a function's database session, it will share the current SQL session's transaction.

ON: Specifies to enable transaction sharing.
OFF: Specifies to disable transaction sharing.
DEFAULT: Specifies to retain the transaction sharing setting that is in effect when the function is invoked.

Parameters for Expansion of language-clause

ADS: Specifies that the SQL routine is written in the CA ADS language.
ASSEMBLER: Specifies that the SQL routine is written in the assembler language.
COBOL: Specifies that the SQL routine is written in the COBOL language.
PLI: Specifies that the SQL routine is written in the PL/I language.
SQL: Specifies that the SQL routine is written in the SQL language.

Note: The ability to specify ADS or ASSEMBLER as a language is a CA IDMS extension.

Usage

Changing the language of a function

A function with language SQL cannot be changed to any other language and a function whose language is not SQL cannot be changed to language SQL.

Specifying a Synchronization Stamp

When defining or altering a function, 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.

Note: For more information about creating a function, see CREATE FUNCTION.

Example

The following example shows the use of ALTER FUNCTION to change the external name of a function.

alter function fin.udf_funbonus external name funbon09;