Separating parts of qualified job name with SQL

In a previous post I explained how I could extract the parts of the qualified job name using the LOCATE_IN_STRING scalar function. With the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, a number of Db2 for i scalar functions and a table function were added to make this easier. These are:

• JOB_NAME scalar function:  Returns the job name
• JOB_NUMBER scalar function:  Returns the job number
• JOB_USER scalar function:  Returns the job user
• JOB_NAME_DETAILS table function:  Returns all of the above

All of the above are found in the library SYSTOOLS.

Validating CL commands with SQL

There has been an API to validate CL commands for as long as I can remember, but is not simple to use. Fortunately, as part of the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, a Db2 for i scalar function was introduced that performs the same function.

CHECK_COMMAND_SYNTAX requires one parameter, the command string to be validated. The string can be up to 32,000 characters, which should cover most command strings I use. It returns a Boolean value:

• True = the command string is valid
• False = the command string is invalid

Here are a couple of examples I made and ran in ACS's Run SQL Scripts, RSS:

Creating and using a data journal reader

Every so often IBM delivers something for the IBM i that makes me say "Wow!" Data journal readers are one of these cases. It has changed the way I am thinking of retrieving data from journals.

The CREATE_DATA_JOURNAL_READER SQL scalar function was introduced as part of IBM i 7.6 TR2 and 7.5 TR8, and is very simple to set up. It creates a SQL table function that allows me to see the entries for one of the files whose data is within a journal.

Before I can show examples of this, I need a file and it needs to be journaled.

I created a DDS file, TESTFILE, in my library, MYLIB. It contains nine fields I named FIELD1 to FIELD9. The layout of the file is immaterial to showing how to create and use a data journal reader.

Once the file was created, I needed it to be journaled. Which I did with the following commands:

ACS 1.1.9.15 available now

Just a few weeks after ACS 1.1.9.14 was launched, a new release of ACS, 1.1.9.15, has been made available for download.

On IBM's ACS page it announces that the error that occured with an SSL certificate, in 1.1.9.14, has been fixed.

There are no new enhancements included.

IBM Redbook covers modernization in depth

IBM has published a Redbook about modernization: "Modernizing IBM i Applications".

The list of authors and contributors is very impressive, including both IBM and IBM i community experts.

The 810-page guide covers what it describes as "all levels of the operating system", including the development concepts needed to achieve effective modernization. It takes us on the journey from from the database to the front-end, and everything in between, including RPG and COBOL's place.

You can download it here as a PDF.

Happy reading!

Convert number to hexadecimal with SQL

In an earlier post I wrote about how to convert a character string to hexadecimal, and back to character again. The obvious question followed:

What if wanted to convert a number to hexadecimal with SQL?

One of the issues is that numbers come in different forms in modern RPG and in SQL. Here are the common types of numbers I encounter:

Type	RPG	SQL
Packed	PACKED	DECIMAL, DEC
Signed	ZONED	NUMERIC, NUM
Integer	INT	INTEGER, INT, SMALLINT, BIGINT

In this post I am going to give examples of how to convert each of these RPG number types to hexadecimal, and then back again.

This is my program:

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 1.1.9.14 is replaced

 

The original contents of this page have become obsolete, go to this page for up-to-date information.

 

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

RPGPGM.COM-unity on the West Coast

I was at OCEAN’s TechCon26 conference earlier this month, and I was able to enroll a few more people into the RPGPGM.COM-unity. You can see who these people are here.

If you see me at an IBM i event feel free to introduce yourself to me, ask for your own RPGPGM.COM-unity ribbon, and you will become part of this community. All I ask in return is a photograph of you with it.

If you would like to learn more about the RPGPGM.COM-unity click here.

It is now so easy to view CVE using SQL

Before I start describing how to use this new SQL Table Function, I need to explain what CVE are.

Common Vulnerabilities and Exposures, CVE, are standardized, publicly identified cybersecurity flaws in software or firmware. When IBM, or a researcher, find a flaw in IBM i, or related software, it is given a unique id. These ids are cataloged globally by the MITRE Corporation, and published with their severity and impacts.

You can learn more about CVE on IBM's "What is CVE?" page.

I cannot find a CL command that allows me to view CVE details. I would have to search for a particular CVE using the IBM Product Security Central page.

With the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, is a new SQL Table Function that allows me view, and search, all of CVE from my IBM i partition.

The CVE_INFO Table Function is found in the library SYSTOOL, it has one parameter the IBM i release number. I went through and checked for each release to find that V5R3 is the earliest there are results for: