CREATE FUNCTION

The CREATE FUNCTION data description statement stores the definition of a function in the SQL catalog. You can then invoke the function in any value-expression of an SQL statement except in the search condition of a table's check constraint. The function invocation results in CA IDMS calling the corresponding routine. Such routines can perform any action and return a single scalar value. You use the formal parameters of a function definition to specify the data type and format of the data to be passed to the function. Similarly, the data type of the return value is specified in the function definition.

Functions can be defined with a language of SQL, in which case, the routine actions written as SQL statements are specified and stored together with the function definition in the SQL catalog.

Authorization

To issue a CREATE FUNCTION statement, you must own the schema in which the function is being defined or hold the CREATE privilege on the named function.

Syntax

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

     ┌─────────── , ──────────┐
 ►─ (─▼─ parameter-definition ─┴) ── RETURNS ── data-type ────────────────────►

 ►─ EXTERNAL NAME external-routine-name ──────────────────────────────────────►

 ►───┬───────────────────┬───┬─────────────────────────────┬──────────────────►
     └─ language-clause ─┘   └── PROTOCOL ───┬── IDMS ──┬──┘
                                             └── ADS ───┘
 ►───┬────────────────────────────┬─┬──────────────────────────┬──────────────►
     └─ 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 ─────────────┘

 ►───┬───────────────────────────────────────────────────────────────────────┬►◄
     └┬──────────────────────────────────────────────┬ procedure-statement ──┘
      │                     ┌──────────────────┐     │
      └ ADS COMPILE OPTION ─▼─ compile-option ─┴─ ; ─┘

Expansion of parameter-definition

►►─── parameter-name ── data-type ─┬────────────────┬─────────────────────────►◄
                                   └─ WITH DEFAULT ─┘

Expansion of language-clause

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

Expansion of procedure-statement

 ►────┬── SQL-AM-mgmt-stmt ───────────┬────────────────────────────────────────►◄
      ├── SQL-authorization-stmt ─────┤
      ├── SQL-Control-stmt ───────────┤
      ├── SQL-Diagnostics-stmt ───────┤
      ├── SQL-DDL-stmt ───────────────┤
      ├── SQL-DML-stmt ───────────────┤
      ├── SQL-session-mgmt-stmt ──────┤
      └── SQL-transaction-mgmt-stmt ──┘

Parameters

function-identifier

Specifies the 1- to 18-character name of the function you are creating. Function-identifier must:

Be unique among the function, table, table procedure, procedure and view identifiers within the schema associated with the function
Follow conventions for SQL identifiers

schema-name

Specifies the schema name qualifier to be associated with the function. Schema-name must identify a schema 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 function. Parameters pass to the function in the order you specify them. You must enclose the list of parameters in parentheses. You must separate multiple parameter definitions by commas.

Expanded syntax for parameter-definition is shown above immediately following the CREATE FUNCTION syntax. Descriptions for these parameters are located at the end of this section.

RETURNS data-type

Specifies the data type of the returned value. The returned value is implicitly nullable and can be set to NULL in the external routine. The returned value is accessible to the external routine as an extra parameter with the implicit name USER_FUNC, which comes immediately after the function parameters.

external-routine-name

Specifies the one- to eight-character name of the program or mapless dialog that CA IDMS calls to process function invocation.

For functions written in SQL, the external-routine-name should specify a name that is unique within the dictionary that holds the function definition. In other words, the name should be different from any other external name of any SQL-invoked routine and from any CA ADS dialog, RCM, or AM name.

row-count

Specifies an integer value, in the range 0 through 2,147,483,647, representing the average number of rows the CA IDMS optimizer uses for cost calculation of the function invocation.

io-count

Specifies an integer value, in the range 0 through 2,147,483,647, representing the average number of disk accesses generated by the function for a given set of input parameters.

language-clause

Specifies the programming language of the function. This clause is required for functions written in SQL. For others, it is documentational only. if the language is not specified, it is treated as null. Expanded syntax for language-clause is shown above immediately following the CREATE FUNCTION syntax. Descriptions for these parameters are located at the end of this section.

PROTOCOL

Specifies the protocol with which the function is invoked. This specification is required except with language SQL. If LANGUAGE SQL is specified, PROTOCOL must be ADS or the clause must not be specified.

IDMS: Use IDMS for functions that are written in COBOL, PL/I, or Assembler.
ADS: Use ADS for functions that are written in CA ADS. The name of the dialog that is loaded and executed when the function is invoked is specified by the external-routine-name in the EXTERNAL NAME clause. ADS is the default if LANGUAGE SQL is specified.

USER MODE

Specifies that the function should execute as a user-mode application program within CA IDMS. This cannot be specified with language SQL or protocol ADS. For other languages and protocols, it is the default.

SYSTEM MODE

Specifies that the function should execute as a system-mode application program. SYSTEM MODE is the default if language is SQL.

To execute as a system mode application, the program must be fully reentrant and be written in either:

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

Note: If protocol is set to ADS, you must specify MODE SYSTEM.

local-stge-size

Specifies an integer value, in the range 0 through 32767, representing the size, in bytes, of a local storage area that CA IDMS allocates at runtime and passes to the function on each invocation.

global-stge-size

Specifies an integer value, in the range 0 through 32767, representing the size, in bytes, of the 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, CA IDMS allocates each function its own global storage area, which will not be used for any other routine within the transaction.

TRANSACTION SHARING

Specifies whether transaction sharing should be enabled for database sessions started by the function. If transaction sharing is enabled for a function's database session, it shares the current transaction of the SQL session. If language SQL is specified, TRANSACTION SHARING must be ON or the clause must not be specified.

ON: Specifies that transaction sharing should be enabled. ON is the default if language is SQL.
OFF: Specifies that transaction sharing should be disabled.
DEFAULT: Specifies that the transaction sharing setting in effect when the function is invoked should be retained. DEFAULT is the default for languages other than SQL.

compile-option

Specifies a CA ADS option to be used when compiling the dialog associated with an SQL function. The options that can be specified and the syntax to use are given in the CA ADS Reference Guide, Appendix D.2.6 Dialog-expression. Compile-option can be specified only if language is SQL.

Note: The ability to specify the ADS COMPILE OPTION clause is a CA IDMS extension.

procedure-statement

Specifies the actions taken in the function. Procedure-statement is required if language is SQL. It cannot be specified otherwise. Expanded syntax for procedure-statement is shown above immediately following the CREATE FUNCTION syntax. Descriptions for these parameters are located at the end of this section.

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.

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.

Parameters for Expansion of parameter-definition

parameter-name

Specifies a 1- to 32-character name of a parameter that passes to the function. Parameter-name must:

Be unique within the function that you are defining
Follow the conventions for SQL identifiers

All parameters are implicitly nullable and thus can be assigned NULL as a parameter value.

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 you do not specify a value for the function invocation.

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)

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.

Parameters for Expansion of procedure-statement

SQL-AM-mgmt-stmt

Specifies a statement from the Access Module Management Statements category.

SQL-authorization-stmt

Specifies a statement from the Authorization Statements category.

SQL-Control-stmt

Specifies a statement from the Control Statements category.

SQL-Diagnostics-stmt

Specifies a statement from the Diagnostics Statements category.

SQL-DDL-stmt

Specifies a statement from the Data Description Statements category.

SQL-DML-stmt

Specifies a statement from the Data Manipulation Statements category.

SQL-session-mgmt-stmt

Specifies a statement from the Session Management Statements category.

Note: The ability to include a RELEASE, SUSPEND, or RESUME statement in an SQL routine is a CA IDMS extension.

SQL-transaction-mgmt-stmt

Specifies a statement from the Transaction Management Statements category.

Note: The ability to include a COMMIT or ROLLBACK statement in an SQL routine is a CA IDMS extension.

Usage

Coding functions with language SQL

The rules for coding the procedure body of an SQL function are given by procedure-statement. A procedure body typically contains multiple SQL statements and according to the SQL grammar, SQL statements are terminated by the semi-colon. However, to define SQL routines, the Command Facility (OCF, IDMSBCF, or Visual DBA OCF console) needs to be used. It also has the semi-colon as the default command terminator. Before a new command can be specified, the CREATE FUNCTION needs to be terminated by a semi-colon. Clearly, the semi-colon cannot concurrently be used as a terminator by both the SQL procedure language and the Command Facility. Therefore, when procedure-statement contains multiple SQL statements or when the ADS COMPILE OPTION is specified, the Command Facility needs to use a terminator different from the semi-colon. To accomplish this, a SET OPTIONS COMMAND DELIMITER 'delimiter-string' must be executed. Changing the terminator of the Command Facility remains in effect until the end of the session or until a new SET OPTIONS COMMAND DELIMITER is encountered. For more information about SET OPTIONS, see the Command Facility chapter in the CA IDMS Common Facilities Guide.

Language SQL

If LANGUAGE SQL is specified, the following attribute settings are established by default and must not be overridden to a different value:

Protocol is ADS
Mode is SYSTEM
Transaction sharing is ON

Functions whose language is SQL are implemented through an automatically generated CA ADS dialog whose name is external-routine-name.

An error while parsing procedure-statement or an error while compiling the associated CA ADS dialog causes termination of the CREATE FUNCTION statement with a warning instead of a statement error. This allows the erroneous procedure-statement syntax to be saved in the catalog for later correction using the DISPLAY FUNCTION command. The CA ADS dialog and associated access module are not created.

Specifying CA ADS Compile Options

If LANGUAGE SQL is specified, you can specify one or more compile options to be used when the associated dialog is compiled. Specifying compile options can be useful for debugging purposes to enable tracing and the use of online debugging facilities. Compile options can also be used to include additional work records and SQL tables which can be referenced in native CA ADS code included in the routine body.

Some useful compile options include:

SYMBOL TABLE IS YES - to allow the use of symbols by the TRACE command and the online debug facilities
ADD RECORD record-name - to enable manipulation of elements from the specified record
ADD SQL TABLE table-name - to enable manipulation of columns or parameters of the specified SQL table-like object.

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.

Grouping Procedure Statements into a Single Statement

Multiple procedure statements can be grouped together as a compound statement. A compound statement is a control statement and therefore it is also a procedure statement.

Examples

Example for CREATE FUNCTION

CREATE FUNCTION FIN.UDF_FUNBONUS
       ( F_EMP_ID        DECIMAL(4)     )
         RETURNS DECIMAL(10)
         EXTERNAL NAME FUNBONUS PROTOCOL IDMS
         DEFAULT DATABASE CURRENT
         USER MODE
         LOCAL WORK AREA 0
         ;

Example for Language SQL

set options command delimiter '++';
drop function USER01.TCNTEQNAME++
commit++
create function USER01.TCNTEQNAME
  ( TITLE     varchar(40) with default
  , P_FNAME   char(20)
  , P_COUNT     integer
  , RESULT    varchar(10)
  ) RETURNS   varchar(20)
    EXTERNAL NAME TCNTEQN LANGUAGE SQL

Label_700:
begin not atomic
 /*
 ** Count number of employees with equal Firstname
 */
  declare FNAME       char(20);
  declare LNAME       varchar(20);
  declare P_COUNT_SAV integer default 0;

  declare EMP1 CURSOR FOR
        Select EMP_FNAME, EMP_LNAME
          From DEMOEMPL.EMPLOYEE
         where EMP_FNAME = P_FNAME;
   open EMP1;
  fetch EMP1 into FNAME, LNAME;

  fetching_loop:
  loop
      if (SQLSTATE < > '00000')
        then leave fetching_loop;
      end if;
      set P_COUNT = P_COUNT + 1;
      fetch EMP1 into FNAME, LNAME;
  end loop fetching_loop;
  set RESULT = SQLSTATE;

  close EMP1;
  if (P_COUNT < = P_COUNT_SAV)
    then return null;
    else return 'Res: ' || cast(P_COUNT as char(5));
  end if;
end
++

More Information

For more information about coding the external routine, see Defining and Using Procedures.
For more information about Control Statements, see Control Statements.
For more information about Diagnostics Statements see GET DIAGNOSTICS.
For more information about the other categories see Statement Categories.
For more information about the SET OPTIONS COMMAND DELIMITER, see the "Using SET OPTIONS to Select Options" topic in the CA IDMS Common Facilities Guide.