IBM i 7.1 Technology refresh 8 was released on June 6, 2014, and it has another group of small database enhancements worth reviewing. IBM i 7.2 users will find that they already have these features. This tip assumes that you have IBM i 7.2 or IBM i 7.1 with Database Group PTF Level 29 or higher installed.

QSYS2.GENERATE_SQL Stored Procedure

This is one sweet new stored procedure IBM has given us. Its purpose is to programmatically retrieve SQL source code into a source member.

This procedure can accept a boatload of parameters as shown below. Many of the parameters control

In SQL Functions You Didn’t Know You Had, Part 1, I showed you how to make an SQL function that runs a subprocedure in a service program. What I showed you is fine up to a point, but it’s not the entire story.

The technique that I shared with you breaks down when null values enter the picture. First, the subprocedures in the ADDR service program cannot accept null arguments into parameters. Second, those subprocedures cannot return a null value to the SQL query.

These behaviors are specified in two clauses of the CREATE FUNCTION statement:

• PARAMETER STYLE GENERAL

I just read your article on creating a skeleton program for controlling IBM i job queues creating a skeleton program for controlling IBM i job queues. In your job queue physical file (WRKJOBQPF) that contains the list of jobs inside a specific job queue, you keyed that file by the six-digit job number of each job in queue. I think that’s a bad idea. Here’s why. . .

–Charles

Charles continues:

“On busy systems where the job number can flip (over to 000001) daily, it introduces a period of time where the results will not be desirable. Needless to