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:

ACS 1.1.9.3 released

 

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

 

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: