HOWTO:SQL

<translate>

SQL User Defined Functions

SQL user defined functions (UDF) are internal SQL objects.

Manage the UDF through the generated service program

When the UDF creates an external object, it is in the form of a service program. We can use this concept inside TD/OMS to manage the UDF.

The steps to follow are:

• Create a new object template for a SQL UDF with a label
• Create a special compile instruction for this service program

We will take the following scalar function as an example:

1 Create or Replace FUNCTION PriorityDate(indate DATE) RETURNS CHAR(7)

 2 LANGUAGE SQL
 3 PROGRAM NAME UDFPTYDATE
 4 BEGIN
 5 RETURN(
 6       CASE   WHEN indate>CURRENT DATE-3 DAYS THEN 'HIGH'
 7         WHEN indate>CURRENT DATE-7 DAYS THEN 'MEDIUM'
 8         ELSE 'LOW'
 9      END
10      );
11 END

Note the program name UDFPTYDATE on line 3. This will create a service program with this name

Creating the Object Template

Inside the GUI, create this new object template:

With the following attributes:

Please note the label SQL because with this, we can control the object to behave exactly how we want.

Creating the Compile Instruction

When a *SRVPGM with this label is processed, we want to use RUNSQLSTM to create it. As part of the create, we also want to stamp the object with source information so that TD/OMS can find the source and associate it with the *SRVPGM.

Compile Command

RUNSQLSTM SRCFILE(&TSRL/&TSRF)   
          SRCMBR(&TSRM)          
          COMMIT(*NONE)          
          DFTRDBCOL(&TOOL)       

Stamp the source into the object

CHGOBJDOMS OBJ(&TOOL/&OBJC) 
           OBJTYPE(&OBJT)   
           FILE(&SRCL/&SRCF)
           MEMBER(&SRCM)

Creating the Compile Selection

This compile instruction is fired when a service program with this label is processed.

Manage the UDF through a virtual object

In case you do not want to manage the service program but just execute the SQL statement, you can perform the same steps as above, but in this case, create a virtual object. Then use the virtual object as a carrier to execute your SQL statements.

The POST compile step to stamp the source must not be specified in this case.

Manage the UDF through a source only

In case you do not want to manage the service program but just execute the SQL statement, you can create a source member or an IFS file. Also add a label so that you can control how the object is compiled.

Please note that in case of a source member or IFS object, you also have to create a PRE compile command to copy the member or IFS file before running the RUNSQLSTM command. The copy command must not be executed when you are compiling in place. </translate>

How to prevent rerouting of SQL objects when renaming or moving

IBM has implemented a rerouting mechanism to make sure SQL schema references do not break when renaming or moving objects.

Read the details here

When a procedure or function is created, the routine information is stored within the *PGM or *SRVPGM. Previously, when librarian commands were used to copy/move/rename the object, the QSYS2/SYSROUTINE, SYSPARMS and SYSRTNDEP catalogs were left unchanged. The following commands (and their API counterparts) are changed to keep the catalogs in sync with the executable object for procedures and functions:

• Create Duplicate Object (CRTDUPOBJ) - the routine catalog information is duplicated and the SYSROUTINE EXTERNAL_NAME column points to the newly duplicated executable object
• Copy Library (CPYLIB) - the routine catalog information is duplicated and the SYSROUTINE EXTERNAL_NAME column points to the newly duplicated executable object
• Rename Object (RNMOBJ) - the routine catalog information is modified with the SYSROUTINE EXTERNAL_NAME column pointing to the renamed executable object
• Move Object (MOVOBJ) - the routine catalog information is modified with the SYSROUTINE EXTERNAL_NAME column pointing to the moved executable object

TD/OMS moves and renames SQL implementation objects before they are replaced with new versions. This causes the side effects mentioned above which are unwanted.

To prevent the re-routing, an environment variable must be set at the job or system level.

  ADDENVVAR ENVVAR(QIBM_SQL_NO_CATALOG_UPDATE) LEVEL(*JOB/*SYS)

Create an action that gets executed at UEP 1 with the "Suppress Subsequent" flag set to '1' to avoid setting it multiple times