Altering a user defined function (external scalar or table)

Authorities and privileges

To modify a user defined function (external):

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

2. On the General Properties page, modify general properties for the external function.

3. On the Environment page, modify environment variables.

4. On the Parameter page, modify parameters for the external function.

5. On the Return page, you can only view the return options for the external function. This is a read-only page.

6. Click OK to modify the external 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 creating an external function. When you complete the steps for modifying the function properties, return to altering a user defined function, external.

To modify the general properties:

1. On the General Page, the Function schema, Function name, and the Specific name fields, are all read-only. They were defined when you created the external function.

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

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

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. Optional: If you are altering an external table, 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.

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

7. The Results are deterministic check box is read-only. This is a value you specified to decide whether the function returns the same results for identical input arguments.

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

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

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

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

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 creating an external function. When you complete the steps for modifying the environment, return to altering a user defined function.

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 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. The External action check box is read-only. You specified 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.

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

Modifying 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 altering a user defined function..

[Return to main task ]

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

[Return to main task ]