Creating a user defined function (external table)

Use the Create User Defined Function (External Table) notebook to define a function that is written in a programming language and returns a complete table. User defined functions are registered to DB2 in catalog table SYSIBM.SYSROUTINES after they are created.

A table function can be used in the FROM clause of a SELECT statement. It returns a table to the SELECT one row at a time.

Authorities and privileges

To create a user defined function (external table):

Open the Create User Defined Function (External Table) notebook.
On the General Properties page, specify general properties for the external function.
On the Environment page, specify environment variables.
On the Parameter page, specify parameters for the external function.
On the Return page, specify return options for the external function.
Click OK to add the external table function.

Related information

Defining the general properties of the external function:

These steps are part of the larger task of creating an external function. When you complete the steps for defining the function properties, return to creating a user defined function, external table.

To define the general properties:

On the General Page, in the Function schema field, specify a schema for the function or click the push button to select from an available list of schemas. A user-defined function resides in the schema in which it was registered. The schema cannot be SYSIBM.
Type a Function name (a long SQL identifier) to identify the function. The name is implicitly or explicitly qualified by a schema name. The combination of name, schema name, the number of parameters, and the data type of each parameter (without regard for any length, precision, scale, subtype, or encoding scheme attributes of the data type) must not identify a user-defined function that exists at the current server.

If the function has more than 30 parameters, the combination of name, schema name, and the number of parameters must not identify a user-defined function that exists at the current server.
You can use the same name for more than one function if the function signature of each function is unique.
Optional: In the Specific name field, type a name (a long SQL identifier) for an external user-defined function. This is the specific name for the user-defined function. DB2 can generate the name.
Select the External name check box and type an external name if the function being registered is based on code that is written in an external programming language and adheres to the documented linkage conventions and interface of that language. This external name follows the MVS naming restrictions. The name must not be longer than 8 characters.
Optional: Specify a package collection identifier. This identifies the collection that is used when the function is executed. This is the package collection into which the DBRM that is associated with the function program is bound.
In the SQL field, specify whether the function issues any SQL statements and, if so, what type.
- If you accept the default, Contains SQL, the function can contain any SQL statements except CLOSE, COMMIT, DECLARE CURSOR, DELETE, DESCRIBE, FETCH, INSERT, OPEN, PREPARE, ROLLBACK, SELECT, or UPDATE.
- If you select READS SQL DATA , the function can contain any SQL statements except COMMIT, DELETE, INSERT, ROLLBACK, or UPDATE.
- If you select MODIFIES SQL DATA, the function can contain any SQL statements except COMMIT or ROLLBACK.
- If you select NO SQL, the function cannot contain any SQL statements.
Optional: In the Cardinality field, type an integer to represent an estimate of the number of rows that you expect the function to return. The number is used for optimization purposes. The value of the integer must range from 0 to 2147483647. If you do not specify a number, DB2 assumes a finite value. The finite value is the same value that DB2 assumes for tables for which the RUNSTATS utility has not gathered statistics.
In the Program type radio buttons, specify whether the function program runs as a main routine or a subroutine. The function defaults to running as a subroutine. The main difference between a user-defined function coded as a main program and as a subprogram is that when a main program starts, Language Environment allocates the application program storage that the external user-defined function uses. When a main program ends, Language Environment closes files and releases dynamically allocated storage. If you code your user-defined function as a subprogram and manage the storage and files yourself, you can get better performance. The user-defined function should always free any allocated storage before it exits. To keep data between invocations of the user-defined function, use a scratch pad.
Use the Results are deterministic check box to specify whether the function returns the same results for identical input arguments. If you choose to make the function deterministic, the function depends on some state values that affect the results. DB2 uses this information when processing a SELECT, UPDATE, DELETE, or INSERT statement to disable merging of views that refer to the function. An example of a function that is not deterministic is one that generates random numbers. If you select deterministic, DB2 can use this information to optimize view processing for SELECT, UPDATE, DELETE, or INSERT statements. An example of a deterministic function is a function that calculates the square root of the input.
Use the DB2 information is passed check box to specify whether specific information that DB2 knows is passed to the function when it is invoked. If you select this check box, an additional argument is passed when the function is invoked. The argument is a structure that contains information such as the application run-time authorization ID, the schema name, the name of a table or column that the function might be inserting into or updating, and identification of the database server that invoked the function.
Use the Stay resident at exit check box to specify whether the load module for the function remains resident in memory when the function ends. If not selected, the load module is deleted from memory after the function ends. Clear the check box for non-reentrant functions and select it for reentrant functions.
Use the Allow function call with null arguments check box to specify whether the function is called regardless of whether any of the input arguments is null. The function can return a null or non-null value.
Use the Final call to function check box to specify whether a final call is made to the function. A final call enables the function to free any system resources that it has acquired. A final call is useful when the function is defined with an allocated scratch pad. The function acquires system resources and anchors them in the scratch pad.
Optional: Select the Limit service units to check box to specify a total amount of processor time, in CPU service units, that a single invocation of the function can run. Setting a limit can be helpful if your function gets caught in a loop. The limit on the service units is a positive integer in the range of 1 to 2 GB. If the function uses more service units than the value specified here, DB2 cancels the function.
Optional: In the Comment field, type any comments you want to include with the function. This adds a COMMENT ON statement which adds comments in the description of the user-defined function in the REMARKS column of the SYSIBM.SYSROUTINES catalog table for the row that describes the function. The maximum number of characters is 254.

[Return to main task ]

Setting up the environment

These steps are part of the larger task of creating an external function. When you complete the steps for setting up the environment, return to Creating a user defined function, external table.

The steps for setting up and maintaining the user-defined function environment are the same as for setting and maintaining the environment for stored procedures in WLM-established address spaces.

You need to write and prepare the user defined function. This makes you the function implementor. You must also define the user defined function to DB2. This is called the definer. Then you invoke the user defined function from an application. This is called the invoker.

To set up the environment:

On the Environment page, in the External security field specify how the function interacts with an external security product, such as RACF, to control access to non-SQL resources. If you select DB2, the function does not require an external security environment. If the function accesses resources that an external security product protects, the access is performed using the authorization ID that is associated with the WLM-established stored procedure address space. If you select User, or Definer, an external security environment should be established for the function. If the function accesses resources that the external security product protects, the access is performed using either:
- The process that invoked the function for User.
- The authorization ID of the owner of the function for Definer.
Use the External action check box to specify whether the function takes an action that changes the state of an object that DB2 does not manage. An example of an external action is sending a message or writing a record to a file. Because DB2 uses the RRS attachment for external functions, DB2 can participate in two-phase commit with any other resource manager that uses RRS. For resource managers that do not use RRS, there is no coordination of commit or rollback operations on non-DB2 resources. The default is No. DB2 can use this information to optimize the processing of views for SELECT, UPDATE, DELETE, or INSERT statements. If you specify EXTERNAL ACTION, DB2:
- Materializes the views in UPDATE, DELETE, or INSERT statements that refer to the function.
- Does not move the function from one task control block (TCB) to another between FETCH operations.
- Does not allow another function or stored procedure to use the TCB until the cursor is closed. This is also applicable for cursors declared WITH HOLD.
Use the Allocate a scratch pad check box to specify whether DB2 provides a scratch pad for the function. If you select this check box, type the length of the scratch pad in the Set length to field. It is strongly recommended that external functions be reentrant, and a scratch pad provides an area for the function to save information from one invocation to the next. If you select to use a scratch pad, DB2 allocates memory for a scratch pad when the function is invoked for the first time. A scratch pad has the following characteristics:
- Length must be between 1 and 32767. The default value is 100 bytes.
- DB2 initializes the scratch pad to all binary zeros (X'00''s).
- The scope of a scratch pad is the SQL statement. For each reference to the function in an SQL statement, there is one scratch pad. If the function is run under parallel tasks, one scratch pad is allocated for each parallel task of each reference to the function in the SQL statement. It is not recommended that you run your function under parallel tasks because you can get unpredictable results. For example, if a function uses the scratch pad to count the number of times that it is invoked, the count reflects the number of invocations done by the parallel task and not the SQL statement.
- The scratch pad is persistent. DB2 preserves its content from one invocation of the function to the next.
Specify radio buttons within the Workload manager (WLM) environment box to establish a WLM environment. An MVS workload manager routes work to address spaces based on the application environment name and service class associated with the function. You must use WLM panels to associate an application environment name with the JCL procedure used to start an address space.
- Specify the Use the default WLM environment radio button to run the function in the MVS WLM-established stored procedure address space that is specified at installation time.
- Specify the Use the following WLM environment radio button to specify a WLM environment name and address space if needed. At the Name field, type the WLM environment name to use. The name is a long SQL identifier that must not contain an underscore. Specify the Use different address space radio button or Use same WLM environment radio button for any nested functions.

[Return to main task ]

Defining parameters for the external function:

These steps are part of the larger task of creating an external function. When you complete the steps for defining the function parameters, return to creating a user defined function, external table.

[Return to main task ]

To define parameters:

On the Parameters page, in the Language field, specify the application programming language in which the function program is written. All programs must be designed to run in IBM's Language Environment (LE) environment.

ASSEMBLE

The function is written in Assembler.

C

The function is written in C or C++.

COBOL

The function is written in COBOL, including the object oriented language extensions.

PLI

The function is written in PL/I.
In the Run-time options field, specify the Language Environment (LE) run-time options that are used for the function. This is a character string that is no longer than 254 bytes. If you do not type anything in this field, DB2 does not pass any run-time options to Language Environment. In that case, LE uses its installation defaults.
Optional: Click the Add push button to add an input parameter. This opens the Add Parameter window.
Optional: Click the Change push button to modify a selected input parameter. This opens the Change Parameter window.
Optional: Click the Remove push button to delete a selected input parameter from the list.
Click the Move Up or Move Down push button to change the position of any input parameters in the list as needed. The order of the parameters in the Parameters text area determines the order in which the function receives them.

[Return to main task ]

Defining the return options of the external function:

These steps are part of the larger task of creating an external function. When you complete the steps for defining the function return options, return to creating a user defined function, external table.

This identifies the output of the function. Consider this clause in conjunction with the optional CAST FROM clause.

To define the return options:

On the Return page, click on the Add push button to open the Add Data Type window to add a column specification for the output parameter.
Click on the Change push button to open the Change Data Type window to modify a column specification for a selected output parameter.

[Return to main task ]