Log4j vulnerabilities for IBM i

The Log4j vulnerabilities came to light earlier this month. I have not written about it as others have a better understanding of how this effects the operating system we love, and have written good articles about it too.

I was sent this link to an IBM Blog entry that describes what you can to remediate these vulnerabilities. And I thought I would share it with you. The blog post is general to all IBM products, not just IBM i and Power systems.

https://www.ibm.com/blogs/psirt/an-update-on-the-apache-log4j-cve-2021-44228-vulnerability/

Please share this link with your IBM i system administrators, and ask them to check if any updates need to be applied to your IBM products and environments.

Update

Mike Mayer sent me a couple more links that might help too:

• Security bulletin for Power HMC
• IBM Product Security Incident Reponse blog (PSIRT)

While these are not related to Log4j, Peder Udesen shared these vulnerabilities in RDi:

• Security bulletin for RDi
• How to get the patches and install them

Searching for member information with SQL

Db2 optimizes very large SQL tables by dividing the data contained within into partitions. These partitions store rows of data separately from other rows. In Db2 for i these are implemented using something we are all familiar with, members. This allows us, IBM i users, to use the Db2 view SYSPARTITIONSTAT to get information about members in the files and tables in our systems.

Where do we use members? The two scenarios that I am sure spring to all of our minds are in source files and in data files.

I often use SYSPARTITIONSTAT to search for source members that have the same name in multiple source files. This allows me to determine if a member name has already been used. Or if I have multiple versions of the same source member in different source files.

Using SQL to retrieve information about output queues

I have written so many posts there are times when I think I have written one about something useful, and I find to my surprise that I have not. I use the SQL view OUTPUT_QUEUE_INFO often, and I found that I have not written about it. This post makes amends for that oversight.

OUTPUT_QUEUE_INFO has been around since IBM i 7.2. It returns similar information to the Work Output Queue command, WRKOUTQ, whose output is familiar to everyone who uses IBM i.

Work with All Output Queues Type options, press Enter. 2=Change 3=Hold 4=Delete 5=Work with 6=Releas 9=Work with Writers 14=Clear Opt Queue Library Files Writer Status OUTPUT #SYSLOADX 0 RLS CGIDEV2 CGIDEV2 0 RLS DATAOUTQ DATALIB 0 RLS IASAUDIT IASUSR13 0 HLD

Taking one string and breaking it apart into five consecutive rows

The question was: How can I break apart a hundred long field into five twenty long fields and then have them returned, one after another, as separate result rows using SQL?

Obviously substring is going to be used, but how to return the resulting rows one after another?

Fortunately the easiest solution for this scenario came in earlier Technology Refreshes for the currently supported releases of IBM i, 7.4 TR4 and 7.3 TR10.

Before I show that I need to have a file with a field that is a hundred characters long:

01 A R TESTFILER 02 A FLD001 100A

Converting a spool file with bar code to PDF

I keep getting asked this question: If I generate a spool file with a bar code in it and I copy it to PDF will the bar code still display?

I wrote about copying spool files to PDF in the IFS in March 2014. What I am going to describe here is based on that post.

If the people who ask the question had read that post they would have discovered how simple this is.

Let me start with the externally described printer file, as I have not used Output specifications for prints in nigh on 30 years. The printer file's definition only needs two lines:

Adding 1 month to the end of the month and always get the end of the next month

I was asked how to make sure if I added 1 month to the last day of this month I would get the end of next month. If I take a date from a month and add one month to it I get the end of the next month.

In RPG if I add one month to January 31 I get February 28, the last day of the next month.

wkDate = d'2022-01-31' + %months(1) ; dsply wkDate ; DSPLY 2022-02-28

When I add one month to February 28 I do not get the last day of March, I get March 28.

wkDate = d'2022-02-28' + %months(1) ; dsply wkDate ; DSPLY 2022-03-28

Using SQL LIKE with a Subselect

This is the second part of a two part story. In the first part I described how I had downloaded a list of libraries from an IFS file to a DDL table just using SQL. In this part of the story I want to check any of job descriptions on this IBM i partitions contain one of these libraries.

In the "real world" there were 58 libraries. In this example I am only using five. To check what these libraries are called I can using the following SQL statement over the file I built in part one:

SELECT * FROM OLDLIBS

The libraries I will be using are:

Copying data from IFS file using SQL, real life example

This is the first of a two part story. I had found a number of libraries on an IBM i partition from the 2000s and 2010s for applications that had been used, but had been replaced by the main ERP application. These libraries were just wasting disk space, as during the conversion to the ERP all of the historical information had been copied from them to the ERP. I wanted to delete these libraries.

The list of these libraries had been circulated to all of the interested parties to confirm that no-one was using any of them. I copied the list from the email to Notepad, and saved as the file name: old_libraries.txt.

I uploaded the text file to my IFS folder using ACS's "Integrated File System" tool.

Searching for empty IFS folders using SQL

I was asked how to make a list of empty IFS folders using SQL. My first thought was to go to the table function IFS_OBJECT_STATISTICS. By using this table function I can retrieve a list of, for examples, just folders or the objects in a particular folder.

My thought was to do this in two steps:

1. Make a list of all the folders
2. Generate a count of the objects in each folder

New columns added to SYSCOLUMNS2 SQL View

I need to keep reminding myself to use the SYSCOLUMNS2 SQL View rather that SYSCOLUMNS as IBM tell us that it gets the results faster than the old View. IBM keeps enhancing the new View, and as part of the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, they added 14 new columns.

These new columns are particularly useful when working with DDS files as some of these columns deal with field attributes that are not available in DDL Tables and Views.

Prior to these additions I would use the Display Field Description command, DSPFFD, to generate an output file for the files I desired. This meant that this was always a two-step process:

1. Create the output file
2. "Read" the data in the output file

Now I can get the information I want in just one step, by accessing the data directly from the SYSCOLUMNS2 View.

User index information by SQL

As part of the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, we received a SQL View and Table function to use to get information about User Indexes.

I do not recall ever using a User Index, but some of the ERP I have worked with through the years had them. The IBM documentation describes them as:

A user index is an object that allows search functions for data in the index and automatically sorts data based on the value of the data...
They have an object type of *USRIDX and a maximum size of 1 terabyte. They help streamline table searching, cross-referencing, and ordering of data.

Until these TRs I would need to use APIs, ILE C, or MI instructions to access a User Index and the information contained within. Now IBM has provided us with the following:

• USER_INDEX_INFO View
• USER_INDEX_ENTRIES Table function

Retrieving the source of SQL objects

GENERATE_SQL_OBJECTS is a SQL procedure I have used which I have found very useful, but have not written about. It allows me to recreate the SQL statement that was used to create a SQL object or objects into a source file member. I could change many of SQL objects using the ALTER statement. But, in my opinion, it is easier to change the statement in a source member. I make sure that the create statement is CREATE OR REPLACE, create the changed object and old one is replaced.

There are two things I consider minor inconveniences to using this SQL procedure:

1. I cannot pass the name of the object to the procedure. All of the objects I wish to retrieve the create statement for must be placed in a DDL Table, that procedure then "reads".
2. The source member that is generated contains the create statements for all of the objects listed in the above Table.

I can use this with most SQL object types. In this example I am only using it with a few I have mentioned in previous posts.

Delaying a CL program by a fraction of a second with USLEEP

I have already written about how to delay a RPG program by less than a second by using the USLEEP procedure. Now I need to do the same in a CL program.

As we are in 2021 I hope that all of your CL source members are all the type CLLE. The program I am going to show must be compiled using the Create Bound CL program command, CRTBNDCL, not using the Create CL program command, CRTCLPGM, which is the default for type CLP.

USLEEP is a UNIX type API. This means that it is case sensitive. When I use it in a program I must use usleep, and not USLEEP or Usleep. I am just going to refer to it in the text of this post as USLEEP as it is easier for you to see its name amongst the rest of the text.

Creating an auto-scrolling subfile

The idea for this post came from a question that was asked on Facebook. Someone wanted to create a subfile that would be projected onto a screen at an event. The subfile would scroll to the next screen of results every so many seconds without the need for someone to press a key on the keyboard.

I was surprised by the replies to the question, some said it was not possible, others gave over complicated examples of how they thought it could be achieved. I knew that to do this was simple, and all the information needed to do this exists in various posts on this website.

I decided to write a program to do what was asked before. I decided to make this a little more interesting:

New SQL View gives DSPFD information

I have wanted to have a way to get to the same information via SQL that I can by using the Display File Description command, DSPFD. This absence has often led me to having to use the DSPFD command to create an output file, that I would then use SQL to gather the results I desired.

My wishes were answered in the Fall 2021 Technology Refreshes, IBM i 7.4 TR5 and IBM i 7.3 TR11, with the introduction of a new SQL view: SYSFILES.

Unlike the DSPFD command SYSFILES only contains data about what I would call "data files", what the DSPFD calls "*PF" and "*LF". Source files are included, but the column FILE_TYPE allows me to differentiate between source and data files.

The simplest Select statement to use this view is:

Using Lateral in SQL

In the past I have only given examples of using LATERAL in SQL Select statements to get data from Table function. As such I have only mentioned it in passing. In this post I wanted to go into more detail so you can see that it can be used for more than just getting data from a Table function.

Having searched for the best description I could find to explain what a LATERAL is, I found that IBM does best with the following:

A lateral join is essentially a foreach loop in SQL. A lateral join is represented by the keyword LATERAL with an inner subquery in the FROM clause, as shown in the following simple representation:

SELECT <columns>
  FROM <tableReference>
  LATERAL <innerSubquery>

More Audit Journal table functions added

As part of the last Technology Refresh four SQL table functions were added that allowed me to extract certain journal entry types from the system audit journal. In the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, twelve new Table functions to extract other journal entries types were introduced:

• Command string (CD)
• Create object (CO)
• User profile changes (CP)
• Delete operations (DO)
• Environment variable (EV)
• Generic record (GR)
• Action to system value (SV)

New Navigator for i available

When the new version of ACS was released we were informed that a new version of "Navigator for i" would be forthcoming.

Today I find that this new "Navigator for i" has been released. It is only available via PTF for the latest releases of IBM i:

• IBM i 7.4 SI76981
• IBM i 7.3 SI76982

You do not access the new "Navigator for i" via ACS, it has its own URL. On the partition I am using the URL is:

Debugging constants in RPG

There have been times when I am debugging a multi-thousand line program when I find a line of code that can look something like:

1414.00 dou (X = Const1) ;

I can see what value is in variable X. But my attempts to see what is in Const1 is met with:

EVAL Const1 Identifier does not exist.

New BiF added to SORTA to make sorting of data structure arrays easier

I use data structure arrays in many of my RPG programs. It has always been impossible to sort the data in the data structure array by more than subfield, that is until the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11.

Included within the new TRs is a Built in Function, BiF, for RPG's Sort Array operation code, SORTA, that allows me to sort by more than one data structure array subfield. The syntax for using %FIELDS with a data structure array is:

ACS 1.1.8.8 released

It has been a busy couple of weeks in the IBM i world. We have had the announcement of the IBM Power 10 chips, new E1080 server, and new Technology Refreshes. On Friday all of these were joined by the announcement of an updated version of Access Client Solutions, ACS.

New version of the IBM Power Performance Capabilities Reference document

The new Power 10 chips and server, E1080, were announced last week and it should come as no surprise that IBM is releasing updates to their documentation.

This document, IBM Power Performance Capabilities Reference, has been updated with the CPW values for various models of the E1080 server running with the IBM i 7.4 operating system.

It also includes the same information for all of the Power 10, 9, and 8 servers going back to the Power 824 running IBM i 7.2, back in April 2014.

I won't ruin the "plot" by giving you examples of how fast the new E1080 is, the CPW numbers are impressive. You will have to check out the document for yourself to discover them.

You can download the PDF here.

New RPG BiF to retrieve greatest and lowest value in an array

The latest round of Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, brought in three new additions to the RPG language. I thought I would start with two new Built in Functions, BiFs, that return the greatest or lowest value from an array.

The two new BiFs, %MAXARRAY and %MINARRAY, have the same syntax:

Video: IBM Power10 E1080 top facts and highlights

Video by Nigel Griffiths of IBM UK

IBM Power 10 chips and server announced

In what will be probably be the biggest announcement in the IBM i world this year IBM has announced their first server using the Power 10 chip, the E1080. I am not really a hardware guy, but I do appreciate the improved performance that the new Power 10 chip gives.

The ERP vendor SAP has developed their own SAP Application Performance Standard, SAPS, which they use to compare servers to one another. Their benchmark tests for the IBM Power 10 show that it outperforms Dell EMC PowerEdge with the Intel Xeon Platinum 8380 processor, SAPS 955.050 versus 248.530.

Even in a cloud environment 8 sockets, 160 cores of the IBM E1080 outperforms 16 sockets, 448 cores Google Cloud and HPE Superdome Flex.

Fall Technology Refreshes announced

To accompany the announcement of the IBM Power 10 launch we also have the announcement of the Fall 2021 Technology Refreshes, TR, for the two supported releases of the IBM i operating system, 7.3 and 7.4 . These TRs are necessary if you want to move to the new Power 10 E1080 server.

Comparing the page for IBM i 7.3 TR11 and 7.4 TR5 they have the same additions and changes:

IBM Power 10 chip announcement tomorrow

With the Power 10 announcement happening tomorrow I received a link to a sneak peek from IBM, which you can watch here.

Look for the "orbs" and click on them as they will reveal more information.

If you have not registered for the announcement tomorrow, Wednesday September 8, click on this link to do so.

Finding which display file field and record format the cursor is in

It has been a long time since I last wrote a post about display files, but I thought this was worth sharing. I was asked how you could determine which field the cursor is in when the prompt key is pressed. The prompt key is the SAA standard F4 key.

The person had found a very old program where the position of the cursor was returned as its location on the screen, as the row and column numbers. If this method was use they would have to determine the location of each field in the display file's record format.

I was asked is there an easier way?

IBM Power announcement next week

Next week will be the biggest announcement from IBM this year, the next generation of IBM Power.

You can be there and learn about IBM Power 10 from the experts by attending this virtual announcement by signing up here.

Date:  Wednesday September 8, 2021
Time:  10:30 AM (US Eastern time)
Duration:  45 minutes

Getting information about the partition's disk drives using SQL

Back in IBM i release 7.2 the SYSDISKSTAT SQL View was added. It was introduced to give similar information to what can be see using the Work with Disk Status command, WRKDSKSTS, and allow us to do more with that information. Over the next couple of releases there have been new columns added to it making it ever more useful. I have to admit I have been remiss not writing about it until now.

In the latest Technology Refreshes, IBM i 7.4 TR4 and 7.3 TR10, 37 new columns were added. There is also now a SYSDISKSTAT SQL Table Function, more about that later. In the examples I am going to give I am not going to use all 37 of the new columns, therefore, if you want to learn about them all click to the links to the IBM documentation at the bottom of this post.

With this statement I am going to get a snapshot of the which disks are the top five when it comes to the percent of the disk is used:

Changing user profiles using SQL

I have to admit when I read that a SQL table function to change user profiles had been introduced in the latest Technology Refreshes for IBM i 7.4 and 7.3 I was skeptical as to whether it was something that I would find practical and useful. Having had some time to play with it I can now appreciate its usefulness.

But before I get to that, let me explain how the CHANGE_USER_PROFILE table function works.

The table function will only change certain information of a user profile. The parameters you can change are:

CTE example: Calculating the maximum length of 80% of the rows in a column

I don't have a good example of using a Common Table Expression, CTE, in this blog so I decided to write this one as an example of how I can determine the maximum length of 80% of the rows in a SQL Table.

It does sound a bizarre thing to calculate, but it was used in the post from last week where I discussed the fastest way to copy data from one file or table to another. IBM's recommendation was if you use a VARCHAR to define a column in a table using the ALLOCATE it could reduce the number of I/O operations that would be performed. The suggested allocation for any column is that the number of bytes allocated will include 80% of the rows in the table.

I decided to write the SQL statement I will show, below, to perform that calculation. I broke up what I wanted to do into "parts", this would allow me to test my results before going to the next "part".

Latest IBM i roadmap

I just want to show the latest roadmap for IBM i. It shows how IBM has plans for the operating system we love beyond 2031 with the next two planned releases.

It also shows the extended support for releases 7.1 and 7.2, identified as SE1 for 7.1 and SE for 7.2 .

If you are using 7.1 or 7.2 I would seriously consider moving to the latest release supported by your Power server. There have been a lot of security upgrades in later releases, not having these could leave your IBM i partitions vulnerable to hacking or malware attacks.

Finding fastest way to copy data – part 2

A couple of days ago I published a post about testing a friend had performed looking at the fastest way to copy data from one table to another. I thought his results were really interesting, which is why I shared them.

But there are another couple of ways I would have tested. In this post I will describe them.

With all the example programs I am not displaying the entire program, just the interesting parts.

The tests were performed over the same SQL tables as before:

Finding fastest way to copy data from one table to another

This post is based on the presentation Thomas Leo Swint made to the Central Texas IBM i User Group last night. Thomas performed a series of tests looking to find the fastest methods to "read" several million rows of data, and also to "read" the data from one table and output it to another table. When he shared his results with me, I was surprised what he had discovered. I asked him to make a presentation to the CTXiUG, and for his permission to share his results here.

Thomas needed to copy millions of rows of data from one set of SQL tables to another. This process needed to take the least amount of time possible. He created four test tables with the character columns defined in different ways. He compared the time taken to "copy" rows from the tables to another table using:

Finding records with a certain character in a field using SQL regex

This question came from a colleague of mine. He was trying to identify which records from a DDS file contains one of several special characters within a particular field. To make it a bit more interesting these special characters could be in any position of the field, maybe the first position, last or in the middle. He had tried several SQL statements but had not managed to create one he felt comfortable with.

I am sure you can understand why I cannot share the file he was using. I have created my own file to illustrate the problem and my solution. I am sure you will not be surprised to find I called it TESTFILE.

To see the contents of the file I can use this simple SQL statement:

01 SELECT * FROM TESTFILE ;

And the following results are returned:

Symbolic link location added to IFS Object Statistics

The IFS_OBJECT_STATISTICS table function has always returned whether the object in the IFS is a symbolic link, *SYMLNK, but not where its links. As part of the last of the latest round of Technology Refreshes a new column has been added to the table function's results to give us that information.

I feel I need to explain what a symbolic link is before I give an example from IFS_OBJECT_STATISTICS.

Symbolic links are a special kind of object only found in the IFS. They are identified by their own object type, *SYMLNK. Think of them like a Windows shortcut. These files contain the path name to another file or location in your partition.

You create symbolic links with the Add Link command, ADDLNK:

USER_INFO_BASIC faster way to get data for user profiles

This is going to be a short post about a new SQL View, USER_PROFILE_BASIC, that was released as part of the latest round of Technology Refreshes. As the name suggests this new View contains a subset of the columns found in the USER_INFO View.

By having less columns USER_INFO_BASIC fetches and calculates its results faster than USER_INFO.

The syntax for this statement is simple:

SELECT * FROM QSYS2.USER_INFO_BASIC ;

The column names are the same in USER_INFO_BASIC as they are in USER_INFO.

Security information from a SQL View

Introduced as part of the latest Technology Refreshes for IBM i 7.4 and 7.3 is a SQL View which lists all of the security information for the partition I am using.

The View SECURITY_INFO only has one row, and shows the same information as the Display Security Attributes, DSPSECA, and Display Security Auditing commands, DSPSECAUD. Many of the columns in this View are taken from the security system values.

To be able to see the contents of the audit receiver columns I must have Object operation authority, *OBJAOPR, to the Audit journal, QSYS/QAUDJRN, or to the attached journal receiver.

In its simplest form to see all of the security I can just use the following statement:

SELECT * FROM QSYS2.SECURITY_INFO ;

Object statistics wildcard makes it easier to list objects

A small enhancement to the Object Statistics table function in the latest round of Technology Refreshes is going to make it easier for me to get the results I want. Previously if I wanted to get the results for a group of objects I would have to ask the table function for a list of all the objects in the library, and then limit the returned results only to the ones I want using a where clause.

SELECT OBJNAME,OBJTYPE,OBJATTRIBUTE FROM TABLE(QSYS2.OBJECT_STATISTICS( OBJECT_SCHEMA => 'MYLIB', OBJTYPELIST => '*ALL', OBJECT_NAME => '*ALL')) WHERE OBJNAME LIKE 'TEST%' ORDER BY OBJTYPE,OBJNAME ;

I am sure you can appreciate that this is not the most efficient way to get the results I want, a list of objects that start with the letters: TEST. I have had to return the results of all the objects in the library MYLIB, and then select from that list the rows I want, where the name start with TEST.

IBM updates Power branding

IBM has updated their branding for the Power range of servers and architecture. I could also say the processor has been rebranded too, but in my opinion POWER9 has been replaced by the newer, faster Power10 processor. They have designed what I think is a pretty cool logo for the Power10, that I have placed to the right of this text.

Retrieving specific audit journal data by SQL table functions

As part of the latest round of latest Technology Refreshes for IBM i 7.4 and 7.3 are four SQL table functions that allow me to be able to easily get the following information from the system audit journal:

• Authority failures (journal entry type AF)
• Authority changes (CA)
• Ownership changes (OW)
• Password entries (PW)

The syntax for all of these table functions is the same:

New columns added to Job Info table function

The latest round of Technology Refreshes sees the addition of the same three columns to the JOB_INFO table function as were added to the ACTIVE_JOB_INFO table function.

These new columns break apart the full job name placing its parts into three new columns:

• JOB_NAME_SHORT
• JOB_USER
• JOB_NUMBER

For example I can get the information about my job by leaving out the parameters for the table function:

Additions to the Active Job Info table function

In the past few Technology Refreshes there have been additions to one of my favorite Db2 for i table functions. I use the ACTIVE_JOB_INFO a lot, I have even created a program to display jobs in message wait in a subfile using it.

The first change I am going to show makes it easier to search for all jobs that have the same job name. Three new columns have been added, "breaking apart" the full job name into its individual parts:

SELECT JOB_NAME,JOB_NAME_SHORT,JOB_USER,JOB_NUMBER FROM TABLE(ACTIVE_JOB_INFO()) ;

Combining data from the same file in multiple libraries into a view

When anyone asks for information that they are going to "pull" from the IBM i into another database or to interface to another application on the IBM i I do not want them to access the "live" files for several reasons:

1. Control the information they can get to, don't show them what they do not need
2. If there are calculations or other mapping that needs to be performed perform them, rather than rely on them to do it and then find they have not done so in the right manner
3. Map data from the files and tables into the type of data format they want
4. Prevent them from being able to insert, change, or delete data from files and tables

In my opinion the perfect tool for this is a SQL View. I can build it the way I want and tell them to use that.

Getting data from User Spaces made easy with SQL

The only way I have known how to write data to a User Space and read it has been by using APIs. With the last round of Technology Refreshes for IBM i 7.4 and 7.3 came an alternative to using an API to read.

There are two additions to SQL to help with User Space:

• View to list all User Space
• Table Function to view the data within an User Space

 

QCMDEXC scalar function added to SQL

I am sure everyone who has programmed using IBM i has encountered QCMDEXC. Most of us have used the QCMDEXC API, or the QCMDEXC procedure in SQL to execute CL commands from another program or a SQL statement. Included within the latest round of Technology Refreshes for 7.4 and 7.3 is a QCMDEXC scalar function.

A scalar function has to be executed as part of a SQL statement, a Select for example, rather than called directly. Like all the other scalar functions I know, it will return "1" when it executed successfully, and "-1" when it did not.

I have to admit I had to think for a while of a situation I could use this in. Then I remembered one where this could be a perfect fit.

Many years ago I came across a couple of programs that were part of a month end job to release and hold job queues that were used by the month end. There was a file that contained a list of job queues, the library they are found in, and what was called the "flag" field. All the flag was used for if it was not "1" the job queue was not processed.

I have built a SQL table to mimic that file:

Providing the same date in different formats using SQL

I have been working with a group of Windows programmers to interface data from an IBM i partition to a Microsoft SQL database. The biggest issue I had was the formats of the dates. These Windows programmers insisted I pass all the dates as character format including the slash character ( / ) as the separator, which is no big deal. What had us going around in circles was that the same date would need to be provided in different date formats depending on the part of the interface. As I am in the USA most of the time they wanted me to pass the date in MDY format (MM/DD/YY), sometimes in USA format (MM/DD/YYYY), and for a couple of dates I need to pass them in the European DMY format (DD/MM/YY).

The date was coming from a DDS physical file. To illustrate how I could simply provide the date in the formats they desired I have created a file, called TESTFILE:

01 A R TESTFILER 02 A TEST_DATE L 03 A TEST_STAMP Z

Getting results from dynamically built SQL statements

I have written about executing dynamic generated SQL in a RPG program before, all of the examples were to do something and not to return results from those statements.

This post will show how you can build SQL Select statements in a variable, execute them, and get the results returned.

I need to give credit to Hassan Farooqi for providing me with the information to show a simple way to do this. The code is based upon examples he shared with me. His were far more complicated than what I am showing here, but I just want to show simple of examples of what you can do. You can make yours as complicated as you like.

All of the examples have three steps:

Getting to message queue data faster

Prior to the latest round of Technology Refreshes for 7.4 and 7.3 if I wanted to get the data from a particular message queue I would need to use the MESSAGE_QUEUE_INFO SQL View. Being a View it contains all the data from all of the message queues, not just the one I would be interested. If I wanted to retrieve the messages from one message queue it would take a while for the results to be found and returned.

There is now a MESSAGE_QUEUE_INFO Table function. Table functions are faster than Views as they are sort of like an API, I pass the message queue name to the Table function and only that message queue is searched for the information I want.

The results returned by the View and Table function are almost identical. The only difference is that the View returns columns for the name of the message queue and the library it resides. As I pass that information to the Table function it is irrelevant to its results.

MESSAGE_QUEUE_INFO Table function has four parameters: