Altering a user defined function (external scalar)

An external scalar function is defined to the database with a reference to a load module that is executed when the function is invoked. External scalar functions are useful for supporting distinct types. Each time a scalar function is invoked, it receives a single value for each argument and returns a single-value result.

Authorities and privileges

To alter a user defined function (external scalar):

1. Open the Alter User Defined Function (External Scalar) notebook.

2. Make any of the following changes for the user defined function:
   • On the General properties page, specify or modify the general properties for the external function.

   • On the Environment page, specify or modify environment variables.

   • On the Parameter page, modify the language or run-time options for the external function.

   • Optional: On the Return page, view the return options for the external function that you specified when you created the function. These options cannot be changed.

3. Click OK to alter the external scalar function.

Related information

• Creating a user defined function (external scalar)
• Creating a user defined function (external table)

Modifying the general properties of the external function:

These steps are part of the larger task of altering an external function. When you complete the steps for modifying the function properties, return to altering a user defined function (external scalar).

To specify or modify the general properties:

1. View the values in the following fields:
   • The Function schema field displays the name of the schema for the function that you selected when you created the function. This is a read-only field. A user-defined function resides in the schema in which it was registered. The schema cannot be SYSIBM.

   • The Function name identifies the name of the function that you are altering. 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.

   • The Specific name field, shows the name of the external user-defined function that was created when you created the function. This name can be used to refer to the external function. DB2 can generate the name.

2. 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, select the External name check box and type an external name. This external name follows the MVS naming restrictions. The name must not be longer than 8 characters.

3. Optional: Specify a package collection identifier to identify 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.

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

5. Use the Program type radio buttons to 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.

6. View the Results are deterministic check box. You cannot change this. If selected, the function returns the same results for identical input arguments. If selected, 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 selected deterministic when you created the function, DB2 uses 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.

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

8. 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 this check box for non-reentrant functions and select it for reentrant functions.

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

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

11. Use the Allow parallel execution check box to specify whether to use parallelism when the function is invoked. Although parallelism can be used for most scalar functions, some functions, such as those that depend on a single copy of the scratch pad cannot, be invoked with parallel tasks.

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

13. 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 ]

Modifying the environment

These steps are part of the larger task of altering an external function. When you complete the steps for modifying the environment, return to altering a user defined function (external scalar).

The steps for modifying and maintaining the user-defined function environment are the same as for 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. You are the definer. Then you invoke the user defined function from an application. You are the invoker.

To modify the environment:

1. 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.
2. View the External action check box. If selected, the function takes an action that changes the state of an object that DB2 does not manage. You cannot change the state of this check box. 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 external action. DB2 can use this information to optimize the processing of views for SELECT, UPDATE, DELETE, or INSERT statements. If you specified EXTERNAL ACTION when you created the function, 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.

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

4. Specify the Workload manager (WLM) environment:
   1. In the Name field, specify a 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 ]

Modifying parameters for the external function

These steps are part of the larger task of altering an external function. When you complete the steps for modifying the function parameters, return to altering a user defined function (external scalar).

To modify parameters:

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

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

3. In the Parameters field, view the data types and definitions that you created with the function. You cannot add or modify the parameters.

[Return to main task ]