Cancelling a SQL job

Have you ever been in the position of having a SQL statement running in ACS's Run SQL Scripts, RSS, and you realize that the reason it is taking so long is something in your selection criteria is too large. I am sure the same thought has crossed all of our minds: "Wouldn't it be nice if I could just cancel this statement!"

There is a way to do this using the procedure: CANCEL_SQL. The documentation describes this as:

CANCEL_SQL() procedure provides an alternative to end job immediate

This is true I could cancel the RSS job with the End Job command, ENDJOB, with the *IMMED option:

Faster way to find who answered a message

Six years ago I wrote a post about using the MESSAGE_QUEUE_INFO SQL View for finding jobs that had errored, and who had answered those messages. What I would like to do today is to give an example of more efficient, faster, way to get the same results using the MESSAGE_QUEUE_INFO Table Function.

On the whole retrieving results from Table Functions tend to be faster than getting the same results from a View. Most Table Functions have parameters that are used to narrow down the results to a smaller set of results, which can then be interrogated with the Where clause. With a View I have all the results there can be, and then the Where clause has to search through all of those to find what I want.

The MESSAGE_QUEUE_INFO Table Function has four parameters. I am going to be using three in these examples:

Copying save file from PC to PUB400.COM

If like me you are a user of PUB400.COM you will probably find this video useful. There are times where I have a save file I copied from another IBM i partition that I want to restore onto PUB400.COM, this video explains how to do it.

Click on the image below for it to open in YouTube.

 

Convert character to hexadecimal with SQL

A couple of weeks ago I wrote about using Machine Instruction, MI, procedures in RPG to convert a character string to hexadecimal, and then convert it back again to character. Several people sent me examples of converting character to hexadecimal using a SQL statement, but no-one sent me the statement to convert the hexadecimal to character.

I decided to give an example of how I would do what I did last week in SQL. I embedded the SQL statements into a RPG program as this would be the way I would likely use it.

Below is my example program:

Now able to 3 part name table functions

IMHO this is a big deal, I can now use the SQL three part name to retrieve data from Db2 for i SQL Table functions on other partitions. The three part name is where I give the "database" name along with the schema and object name. In the IBM i world I can translate that to:

partition.library.object

The dot (otherwise known as: period or full stop ( . )) must be used as the separator, rather than the slash ( / ).

For example if I want to retrieve a list of objects of the files in my library on another partition, I will be calling the other partition OTHERSYS. I can use the following statement to retrieve data from the other partition using the three part name with the, for example the SYSFILE View:

List and changing object owner with SQL

At my work one of the things that auditors look for is who owns all the objects in the production libraries. If there are objects that do not belong to the expected user profile then they want an explanation of why they do not. The week before the auditors are due I do a "scan" of all the production libraries, I am responsible, for to look for any objects that do not belong to the expected user profile, it's better to be prepared than be surprised by the auditors.

Fortunately I can perform the "scan" of all those libraries at once with a SQL View, OBJECT_OWNERSHIP, to return a list of objects that do not belong to the expected user profile.

For the purposes of this post I am going to limit my examples to the objects in my personal library, MYLIB. And I am only interested in a few of the columns of this View:

Creating JSON array with SQL

Last week I showed how I could retrieve information from a JSON array using SQL. This week I thought it would be a good thing to do the opposite: create a JSON array and insert it into a column in a SQL table.

To accomplish this I will be using several Db2 for i Table functions:

• JSON_ARRAY:  Creates a JSON array
• JSON_ARRAYAGG:  Creates a JSON array with an array element from each result row returned from a SQL statement
• JSON_OBJECT:  Create a JSON object (not a physical object like a file)

Creating the most basic JSON array is very simple. I just need to use the JSON_ARRAY table function and pass values to it:

Convert character to hexadecimal

The idea for this post came from a question I was asked by a friend. In a RPG program is there a simple way to convert a character value to hexadecimal, and vice versa.

I know of two Machine Instruction procedures that will do this for me:

1. cvthc:  Convert character to hex
2. cvtch:  Convert hex to character

In my examples I am going to convert a couple of character strings to hex, and then convert one back from hex to character. All of this I performed in one RPG program. Let me start by showing all the definitions:

Retrieving data from a JSON array with SQL

I have noticed with a few of the enhancements made in recent Technology Refreshes to various SQL Views and Table functions that new columns have been added that contain JSON arrays. For example the BASED_ON_FILES column in the SYSFILES View. I want to be able to convert the data held within the JSON array into individually columns. With Join logical files, SQL Views and Indexes the object could be based upon more than one data source.

Before I show how to extract the data from the BASED_ON_FILES columns I want to give a simpler example that will allow me to explain how to do it. Before I start I need to thank Brian Hill for his help with the code.

My example JSON array looks like:

Using SQL to make a list of defective PTFs

This is something I know that System Admins should be checking, defective PTFs in their partitions. As part of the latest Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, is a SQL View that allows me to generate a list of defective PTFs. Previously this was only available in QMGTOOLS, alas I am not authorized to that in any of the partitions I have access to, therefore, you'll have to take my word that option 24, PTF Menu, then option 3, "Compare defective PTFs from IBM", takes you to the same information.

The View DEFECTIVE_PTF_CURRENCY is found in the SYSTOOLS library. It returns a list of defective PTFs that do not have the corrective PTF applied.

If this is the first time you have encountered this View I suggest you use the following statement to see what defective PTFs there are in your partition, and what columns of information are returned to you:

Creating a unique value across multiple partitions

This is another case of stumbling upon something that I am likely to use in the future. As I work in a multiple partition environment I need to keep rows of data unique not only with each file but also across partitions. Using an identity column will keep the rows unique within one table, but not across the same table in different partitions.

While searching for something else I found the SQL scalar function GENERATE_UNIQUE. This creates a 13 long bit data character value that is made up of an UTC timestamp and the system serial number. This can be used for a unique value in the table as each successive row that is added has a different timestamp value.

I can how what this looks like using the following statement:

Date arithmetic functions added to SQL

In the latest round of Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, a number of date arithmetic functions were added to SQL.

I have to admit I was surprised by this as I did not consider the way I had been doing date arithmetic with SQL as lacking.

These new SQL functions are:

• ADD_YEARS
• ADD_MONTHS
• ADD_DAYS
• ADD_HOURS
• ADD_MINUTES
• ADD_SECONDS

They all have the same syntax: