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.
idmscu19
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.
This article describes the following information:
2
2
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.
    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.
    Compile-option
    can be specified only if language is SQL.
    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.
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.
    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.
    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 Using SET OPTIONS to Select Options.
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 ++