New pseudo columns added for triggers

I have written before about how to create triggers, RPG trigger and SQL trigger, and as I have written more, and talked with other people, SQL triggers are the better trigger.

This has been reinforced an addition to Db2 for i (SQL) as part of the latest rounds of Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, what have been called "trigger pseudo columns":

• TRIGGER_FILE_NAME:  File that caused the trigger to execute
• TRIGGER_FILE_LIBRARY_NAME:  The library that contains that file
• TRIGGER_FILE_MEMBER_NAME:  Member in that file

I am going to add a trigger to the DDS file TESTFILE. First, I need to "clone" the file to make the trigger output file. I can do this simply with SQL:

01 CREATE TABLE MYLIB.TESTFTRG AS 02 (SELECT * FROM MYLIB.TESTFILE) DEFINITION ONLY

ACS release 1.1.9.14 out now

On Saturday, August 8, my ACS alerted me a new release, 1.1.9.14, is available.

IBM's ACS page stated that the new release remedied some vulnerabilities that have been found in all prior releases of ACS, and a number of CVE security fixes.

There are no new enhancements included.

QCMDEXC scalar function can write to job log

The QCMDEXC SQL scalar function is one of my favorite SQL features. I have used it many times in my work, and here in articles on this website. As part of the latest Technology Refresh, IBM i 7.6 TR2 and 7.5 TR8, it is further enhanced by now being able to write the command performed to the job log.

QCMDEXC scalar function now has two parameters:

1. COMMAND:  The CL command to perform
2. PRINT:  Whether the CL command should be written to the job log. This can contain the following:
   • ERROR:  Only write to the job log if the command fails
   • NONE:  No command is written to the job log. This is the default
   • VERBOSE:  The command is always written to the job log

Before I start describing in details what this change does, I need to have something I can do using the QCMDEXC scalar function. In this scenario I have a DDL table, TESTTABLE, that contains a list of files that I want to delete using a Delete File command, DTLF, in this scalar function.

I can show the contents of this table with the following:

01 SELECT * FROM TESTTABLE

Handling pipe separated data into a Db2 file

It started with a question:

If my file.txt is pipe filed separated, how can I make the records fall into the physical file to the corresponding fields.

A good question. Most of the time my incoming files are comma separated. The same methods I would use for those will work for pipe separated files too.

First I need a file with pipe separators. I made a copy of some of the columns from my PERSON DDL table to an output file in QTEMP:

01 CREATE TABLE QTEMP.OUTFILE AS 02 (SELECT PID,LNAME,FNAME FROM PERSON) 03 WITH DATA