Selecting fixed number of random rows in the results

I have written about using SQL's RAND() to generate random numbers to be included in the returned results, but not to return a fixed number of randomly selected results. In other words I just want five randomly selected rows returned in the results.

In this example I have a file, and yes it is a DDS file, that contains just two fields:

• COMPANY: company number
• VALUE: a three long numeric field

For company number 1 there are twenty records with the sequential values 1 – 20.

The request is for, let's say, five randomly selected records for company number 1 only.

New address for IBM's RPG Cafe

In October IBM's developerWorks website started showing an announcement that it would be closing at the end of 2019. Then last month IBM's Barbara Morris made it known that the contents of the RPG Cafe would be moving to another IBM web site.

Good news! The new RPG Cafe is now online with a very long URL, so I will just say click here.

Having had a quick glance at it, it appears that all of the same information that was available in the old version is also in the new. I have to say I prefer the user interface of the new site.

A big thanks to IBM, and everyone else involved with this, for preserving the RPG Cafe information.

Equivalent of DSPPGM and DSPSRVPGM using SQL part 2

At least once a day I use the Display Program command, DSPPGM, and the Display Service Program command, DSPSRVPGM, to look at the service programs and modules bound to ILE programs and service programs I am analyzing. While the command give me the information I need the latest Technical Refreshes, IBM i 7.4 TR1 and 7.3 TR7, gives me another way to get the same information from two new SQL views:

1. BOUND_MODULE_INFO
2. BOUND_SRVPGM_INFO

In this post I am going to describe how I would use them to replace what I do with DSPPGM and DSPSRVPGM commands, and a few other pieces of information I have found that these Views make it easy for me to get to.

Equivalent of DSPPGM and DSPSRVPGM using SQL part 1

I am sure we are all familiar with the Display Program, DSPPGM, and with the Display Service Program commands, DSPSRVPGM as a way to get all sorts of information about the program or service program entered. My two biggest complaints about these commands are:

1. I can only get the information for one program at a time
2. The only output options offered are display or print, no output to file

With the latest Technology Refreshes, IBM i 7.4 TR1 and 7.3 TR7, there are a number of new Db2 for i, or SQL, Views to give me the same information I was getting from the commands.

In this post I will be describing two of these Views:

Using SQL to write to the job log

Another useful addition to IBM i with the latest round of Technical Refreshes, 7.4 TR1 and 7.3 TR7, is a SQL function that allows you to write directly to the job's job log.

Why would I want to write to the job's job log?

I would use this to write to the job log when something I would expect to happen did not. Or as a record that some part of the program ran, and completed successfully or failed.

The syntax for this function could not be simpler:

CALL SYSTOOLS.LPRINTF(string or variable)

Data Queues using SQL

The latest Technical Refreshes, IBM i 7.4 TR1 and 7.3 TR7, included four additions to SQL that allow us to do things with Data Queues.

In this post I will be describing how I would do things with data queues pre-the latest TRs and post. These are:

1. DATA_QUEUE_INFO view
2. CLEAR_DATA_QUEUE procedure
3. SEND_DATA_QUEUE procedures (there is more than one and I will describe the differences below)
4. RECEIVE_DATA_QUEUE table function

But, before I start describing any of the above I am going to need to have a data queue I can use. Here is a CL program I wrote to create my data queue:

developerWorks data to be moved to another IBM website

Last month I noticed a banner that appeared on the top of all the developerWorks pages notifying us that it was to be closed on December 31 2019.

This has alarmed many IBM i folks who have taken to social media asking what is to become of the valuable information contained within developerWorks?

Yesterday I noticed the following announcement from Barbara Morris, lead developer for the RPG compilers, in the RPG Cafe:

More SQL Table Functions for interrogating IFS

Last week I started writing about the new table functions introduced in IBM i 7.4 TR1 and 7.3 TR7, describing the first of the four table functions that allow me to retrieve information about IFS directories and files. In this post I will describe the other three:

• IFS_OBJECT_LOCK_INFO
• IFS_JOB_INFO
• IFS_OBJECT_REFERENCES_INFO

 

New Performance FAQ released

A new IBM i on Power Performance FAQ was released yesterday, November 21 2019.

While you may not think there will be anything in it for you as you only program in RPG, there is a section for RPG and Cobol native I/O.

This is available as a PDF from IBM's website here.

Using SQL to list directories and files in IFS

Thanks to the folks at RZKH who downloaded and applied the PTFs for IBM i 7.4 TR1 on the first day they became available, what great service!, I have been able to explore some of the new features added to the operating system we know and love. Amongst the additions to Db2 for i, or SQL, is a number of table functions to list information about the directories and files in the Integrated File System, IFS. My original thought was to write one post about them all, but the more I played with the IFS_OBJECT_STATISTICS table function I came to the conclusion that it deserved its own post.

The IFS_OBJECT_STATISTICS table function returns similar information to the RTVDSKINF command. All I want is just some basic information about the directories and files I choose, but there are many more columns returned by the table function. I encourage you to check the link at the bottom of this post which will take you to the appropriate page in IBM's documentation.

In my folder, MyFolder, I have created the following files and subdirectories:

Technology Refresh release day

Today is the day the new Technology Refresh PTFs become available for download for the two most current releases of IBM i 7.4 TR1 and 7.3 TR7.

I wrote about what is included in these TRs last month:

• Db2 for i enhancements
• RPG enhancements

What are the PTF numbers for the TRs?

Fortunately IBM has pages that have all this information, including links to download the PTFs:

Listing Server Authority users

When I wrote about securing DDM files I bemoaned the lack of a WRKSVRAUTE command. While ago I stumbled across a Db2 for i View that will display a list of all the users I with DDM server authority.

This view has one of the longest names I have used to date: DRDA_AUTHENTICATION_ENTRY_INFO, and it contains the following four columns:

Long column name	Short column name	Description
AUTORIZATION_NAME	USER_NAME	User profile
SERVER_NAME	SRVR_NAME	Target server for the authentication entry
SERVER_AUTHORIZATION_NAME	SRVR_USER	User profile on the target server
PASSWORD_STORED	PW_STORED	Is the password stored in the authentication entry?

Example of why using SQL can be better than RPG

Here is another example of using SQL can make your life easier, coping with a change of the key in file without having to change the program.

At work we are gradually moving all of the US based IBM i partitions from a PowerSystems model 720 to a brand new model 900. I always thought PowerSystems model 900s could only run IBM i 7.3 and 7.4, it turns out that it will also run IBM i 7.2 but a whole load of PTFs have to be loaded and applied when moving from an older model (that was probably not up to date with PTFs too). The moves have been pretty uneventful, apart a change that makes a process I wrote many years ago fail.

The file QA1ALG in library QUSRBRM has changed, a new timestamp field has been added and that is used to replace the existing date and time fields in the file's key:

The future for IBM i looks beyond 2030

The post I wrote last month about the developerWorks closing at the end of this year. This caused a flurry of messages asking me if this meant that this was the end of the "AS400" too.

This is NOT a signal that the end of IBM i is nigh. I have been at various user group meetings where folks from IBM have told us that they have plans for this operating system going many years into the future. They also share an image showing past and the planned future releases of IBM i. After a good deal of "googling" I found this image that was updated after the launch of IBM i 7.4 .

Too many spool files: Which outqs, users, and the oldest

At work we monitor the percentage of ASP used to make sure we do not cross the threshold into "critical storage". Recently the amount of ASP has be slowly climbing, and I was part of a trio asked to determine what could be done to reduce the percentage. One looked for unapplied PTFs, another for exceedingly large files, and I was told to look at "everything else".

I know that one long neglected aspect, that is often overlooked, are the number, size, and age of the spool files on this IBM i partition. There are some spool files that need to keep, certain financial period end reports, but others, compile listing, job logs, do not.

First thing is to determine the size of the problem. Fortunately there is a SQL View that allows me to do this easily, OUTPUT_QUEUE_ENTRIES. I can just run the following statement and have the total number of spool files and total size returned to me:

More tips for using Temporal tables

I first wrote about using Temporal tables when they were introduced as part of IBM i 7.3, back in 2016. I thought they would become widely used, as they provide a way to look at the same data as it was at different times in the past.

Having had three years to "play" with Temporal tables, I have learned many things I can do with them, that I did not know when I first wrote about them. I thought this would be a good opportunity to revisit them, and to share some of the extra tips and tricks I have learned.

One thing to remember, and I always forget, is that all of the tables used must be journaled. For the easiest way to do this refer to my original post about Temporal tables.

For a Temporal table I need a table that will be the "live" table. I have deliberately kept this example very simple, so the data does not get in the way of the important columns needed for a Temporal table. My example table will only contain two "data" columns, the rest are used by the Temporal table process. This is the example table I will be using:

The end of developerWorks is nigh

I noticed at the top of all the IBM developerWorks pages I visit there is the following message:

The developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its content (wikis, blogs, forums, files, activities) will no longer be available.

Finding the machine number and retrieving data from the job log

The following question was posed on Twitter last week:

For the life of me I can't figure how to get an IBM i machine type programmatically. Suggestions?

While I was looking in all the places I could think of ex-IBM-er Dawn May provided an answer I could not better:

Additions to RPG in new TRs

On Tuesday I wrote about the new additions to Db2 for i in the latest Technology Refreshes, IBM i 7.2 TR1 and 7.3 TR7. Today I bring you the additions to the RPG programming language.

Barbara Morris, lead developer for the RPG compilers at IBM, published a blog in the RPG Cafe part of the IBM Community website. In this blog she introduces us to the three additions coming with these TRs:

Putting multiple SQL Selects together to make one row

I was working with one of the Tech Support guys at work putting together bits of information into a one row of data that would allow him to display information on any IBM i partition. This is not something I had done before, and after a little while "playing" I had a statement that put bits of information from different sources together into one row.

I am sure we are familiar with using a Select to get information from a table/file, or perhaps join tables/files together, to get the results. But they are always related, with columns/fields that can be used to link the tables/files together. In this scenario I am running several independent Select statements, each producing one row of results, I want to combine into one. The information want is:

1. Partition name
2. Date and time
3. Default CCSID, from system value
4. Number of spool files in QEZJOBLOG output queue
5. Model number
6. Release of IBM i

Db2 enhancements in IBM i 7.4 TR1 and 7.3 TR7

It is a good job I checked the pages for the new Technology Refreshes today as they now contain data, and I can find announcement documents too. But there has been no fanfare on platforms like Twitter that I would have expected.

It would appear that the two TRs contain the same Db2 for i enhancements. Just having a quick look I can see several I am very interested in trying:

• Views to see which modules and service programs and bound to programs
• Db2 services for data queues
• Db2 services for the IFS
• A way to use SQL to write directly to the job log

In other words lots of good stuff to play with and write about in the future.

Waiting for the new TRs

It is now October, and I am impatiently waiting for the new Technology Refreshes for IBM i.

In previous years the announcement had been made in August or September for the second round of the biannual TRs. This year we are still waiting.

Pages were added to the IBM Community wiki on September 29, which makes me think it cannot be too long before the announcement is made.

You can find the pages here...

• 7.4 TR1
• 7.3 TR7

As soon as the announcements are made you will be able to read about them here.

Getting database relationships using SQL

I was asked if it was possible to get the same information that is shown using the Display Database Relations command, DSPDBR, via SQL. You can generate an output file using the DSPDBR command, but that was not what was wanted.

There is a Db2 for i view SYSVIEWDEP which returns the dependencies of a view to its "parent". But it just contains only SQL views and indexes, no information for logical files. As I work in an environment where there are thousands of logical files in the ERP application this view does not give the dependency information I needed.

If the information is available via a command it must be contained somewhere within the IBM i operating system. In this case the dependency information is in the file QADBFDEP in QSYS.

Creating many different dates, days, months, and years in just one SQL statement

The germ for this post came from a program I found that was used in a job I was making changes to. The program made a data structure of information about various dates, you'll see exactly what below, that was passed to various programs. The program was several thousand lines of unnecessarily complicated RPG code. Looking at the results it was passing back to the calling program I knew it could be done a lot simpler using SQL, rather than the complex calculations this program contained.

Never one to turn down a challenge over the weekend I wrote a SQL RPG program that generates the same information in the data structure just using one SQL Select statement. I thought this would be an interesting thing to share due to some of the SQL functions I used, some I have written before others I have not, and you might find useful if you have to do something similar.

When called this program returned a data structure that contained the following:

Using SQL to rank results

When I wrote about using a SQL Sequence to place sequential values in a column I used the example of ranking students based upon on their test results. Looking back this may not have been the best example, as if two students had the same score they would have been given a different sequence number.

Fortunately SQL does have a function I can use to properly rank these students, based on their test scores.

Let me start with the students and their test scores:

LAST_NAME FIRST_NAME MID_INITIAL SCORE --------- ---------- ----------- ----- FUESTER MAX 25.69 ECKERT TRISTAN M 49.86 APUZZO CAROL C 14.89 TORRES JOSE 94.96 PENA JUANITA M 74.09 HONG XIA 18.54 REED STEVE J 65.83 OKEEFE VINCENT 6.02 KING CATHERINA K 18.76 WILEY CHRISTIAN J 60.77 MORALES MARIA A 65.83

Steve Reed and Maria Morales have the same test score, therefore, they need to have the same ranking. Let me show the SQL statement I would use to rank these eleven students by their score.

End of support for IBM i 7.2 is announced

On September 10 IBM announced the end of marketing of IBM i 7.2 on April 30, 2020, and the end of support for this release effective April 30, 2021.

IBM i 7.2 was released in May 2014, and by 2021 it will have had a life of 7 years.

If you have partitions running IBM i 7.2 it is time to determine if your Power server can support the newer releases 7.3 and 7.4, and if it does to start making plans upgrade to one of those newer releases.

You can read the announcement document from IBM here.

Using SQL to determine which subsystems are active or not

When I wrote about how it is possible to check if a subsystem is active or not someone asked me if there is a way to list all the subsystems and whether they are active or not.

Having had some time to "play" I have developed a way of doing this by combining data from the following two SQL table functions:

• OBJECT_STATISTICS
• ACTIVE_JOB_INFO

I can get a list of all subsystem description objects by using the OBJECT_STATISTICS table function:

Variable length arrays in RPG

One of the new additions to RPG in IBM i 7.4, and not to the equivalent TR of 7.3, was the ability to have variable length arrays.

Having read the documentation, and having had a chance to "play", my findings mean I can divide this post three sections:

1. Setting the number of elements in the array
2. Expanding number of elements
3. Compile time array

Before I get started I want to show what the maximum size of an array, this number has not changed since at least IBM I 7.2 . It is not the number of elements in the array that is the limit, it is the total size of the array. The array must not exceed 16,773,104 bytes. For example, this is valid as an array of 16,773,104 elements of one character is valid.

Parms built in function added to CL

With every new release I go through all the "What's new for this release" section of IBM's KnowledgeCenter. For IBM i 7.4 and the CL programming language there is only one addition this release: %PARMS built in function. It appears to be identical to the %PARMS built in function in RPG. It is the way to determine how many parameters have been passed to a program or procedure, and from that stop the program from erroring if too few parameters are passed.

In the past I wrote about coping with to few parameters being passed to a CL program, but the new BiF is a lot neater and easier for someone else to understand.

As I said above %PARMS is available in RPG:

Executing CL commands using RUNSQLSTM

The Run SQL Statements command, RUNSQLSTM, runs all of the SQL statements that are in a particular source member. I use the command a lot as I put all of my SQL statements to create Tables, Views, Indexes, and the statements to Alter them into source members, and I update the member whenever I make a change to the object. I also put miscellaneous groups of SQL statements into source members, that I can then execute whenever I want.

I use RUNSQLSTM so much I have a PDM option defined so I don't have to type in the command and all the parameters I care about.

Opt Command RS RUNSQLSTM SRCFILE(&L/&F) SRCMBR(&N) COMMIT(*NONE) ERRLVL(20)

In the past if I ever wanted to run a CL command in one of these member I would use SQL's QCMDEXC procedure.

More about SQL Sequences

Last week I wrote a post introducing what SQL Sequences are. Having had a chance to "play" with them some more I wanted to write about what I have discovered.

Before I go into any examples I need to have some Sequences to "play" with:

CREATE SEQUENCE MYLIB.BY_ONE START WITH 1 INCREMENT BY 1 NO MAXVALUE NO CYCLE ; CREATE SEQUENCE MYLIB.BY_TEN START WITH 10 INCREMENT BY 10 NO MAXVALUE NO CYCLE ; CREATE SEQUENCE MYLIB.BY_100 START WITH 100 INCREMENT BY 100 NO MAXVALUE NO CYCLE ; CREATE SEQUENCE MYLIB.BY_THREE START WITH 30 INCREMENT BY 3 NO MAXVALUE NO CYCLE ;

Using a SQL Sequence to renumber a column

The title sounds a bit strange, but so is the subject of this post. There is a thing in Db2 for i that I can create called a Sequence. A Sequence uses the rules I give it to return to me a sequential value I can use to update column in a table.

I am sure it will become a bit clearer when I give my example.

I have ten students who have completed taken a course. At the end of the course the students take a test. The results of this test are contained in a SQL table I created, along with their name.

Extracting the job's name from the Job Name

I was asked was there an easy way to extract the name from the Job Name column in the Db2 for i table function ACTIVE_JOB_INFO.

The IBM i Job Name consists of three parts:

1. Number
2. User profile
3. Name given to the job when it was started

For example:

741232/SIMONH/QPADEV0001

The issue when extracting the name from the Job Name is it does not always start in the same position. The number is always six long, but the user profile can be any length from one to ten. This means that the name could start anywhere from the tenth to the nineteenth position in the Job Name.

Using SQL to determine if subsystem is started

The question came in two parts:

1. Is it possible to know whether a subsystem has been started?
2. If it is started how can I determine the number of jobs running in it?

To answer these questions I decided to use a Db2 for i table function I have written about before ACTIVE_JOB_INFO.

One of the parameters that can be used with ACTIVE_JOB_INFO is one that narrows the results to just all active subsystems. If I wanted to know if QINTER subsystem was active I could use the following SQL statement in my favorite SQL client:

Easiest way to retrieve the program's name in a CL program

It has always been a bit of a pain to get the name of the CL current program using the Receive Message command, RCVMSG. Therefore, I was excited when I received an example program showing a much simpler way of getting the same information by using a Machine Interface program.

The program I am going to show needs to be created as a CLLE member, and compiled using the Create Bound CL program , CRTBNDCL. As the call procedure command, CALLPRC, is not permitted in CLP source members.

My CLLE program will be calling the MI program _MATPGMNM, it is important to notice that the program name starts with an underscore ( _ ). I cannot use a CALL command, I must use the CALLPRC command instead.

My first example is the simplest program I could write to call this MI program, and have the current program name and library returned.

Which users are authorized to these files and tables?

This post started as one idea, and the more I played it morphed into something else.

The starting point was the SYSTABAUTH view. This view displays the authority of all DDS PF and LF files, SQL DDL tables, views and indexes, in all libraries, except QTEMP. It contains the same information that is shown when I use the Display Object Authority command, DSPOBJAUT. As it is a view I can retrieve the results from SQL statements and manipulate it any way I desire.

In these examples I will be using a few of the view's columns:

1. GRANTEE:  the user profile that has been authorized to the object
2. SYSTEM_TABLE_SCHEMA:  the system name of the library that contains the object
3. SYSTEM_TABLE_NAME:  the system name of the object
4. PRIVILEGE_TYPE:  the various authorities granted to the user profile to this object

There are seven different types of privilege that can be given to an object:

Checking for Firmware updates

You might want to share this post with the System Admin of the IBM i you use, as this will probably more of more interest to him or her than to a developer type of person.

Wikipedia defines firmware as:

Firmware is a specific class of computer software that provides the low-level control for the device's specific hardware.

Wikipedia: Firmware

Despite the term "Firmware Currency" being widely used by many different hardware platforms I have been unable to find a definition. From what I can ascertain "firmware currency" appears to be the equivalent of firmware release.

With the latest Technology Refresh for IBM i, TR6, a new view has been added to allow us to compared the firmware fix level installed on the IBM i partition to the recommended fix level from the remote Fix Level Recommendation Tool, FLRT. To be able to use this view the IBM i partition being used must be able to access the internet and be able to reach the FLRT website:
https://www14.software.ibm.com/support/customercare/flrt/liteTable?prodKey=fw&format=json

It is now easier searching message files

I have always found it a bother to search for IBM i message ids. Which message file contains the particular message id I want? What messages could I use for a date validation error? Etc. I always had wished for an easier way to perform searches like this.

Fortunately the latest Technology Refresh, IBM i 7.3 TR6, has a new Db2 for i view to make my searches for messages so much easier. The view MESSAGE_FILE_DATA returns a row for each message from all the message files in the IBM i partition I am using. I am not going to describe what the columns are here, as I think their names explain what they contain. For a full list of all the columns contained in this view click on the link to IBM's documentation at the bottom of this post.

Everything you can see with the Display Message Description command, DSPMSGD, is in this view. Using this view I do not have to give the message file's name when searching for a particular message id. For example if I am looking for the description for the message "RSC0082" I would have used the DSPMSGD command:

Thursday's video now online

The video recording of the presentation I made with New Generation Software, Inc. is now online, you can watch it by clicking here.

New release, IBM i 7.4, day

The new release of IBM i is out today!

If your Power server has a POWER8 or POWER9 chip, then you are one of the fortunate ones who can install the new release.

You can read the announcement document for IBM i 7.4 here.

Most of the new programming features I have been writing about in IBM i 7.3 TR6 are in 7.4 too.

The biggest thing to happen to Db2 for i for years, Db2 Mirror for i, is only available for this new release. If you are one of those moving to 7.4 and you start using Db2 Mirror for i I am interest to hear your thoughts about it.

For more information on what is available in the new release, and 7.3 TR6, visit these links:

• My initial findings on the day of the announcement
• My pick for the biggest things in IBM i 7.4

New table function to break apart values in columns and fields

A couple of years, and a few Technical Refreshes ago, the LISTAGG SQL built in function was added to Db2 for i. Until the latest TR there was not some easy way to do the opposite, take a string from one column and break it into separate results. I am not saying it was not possible to do this before, it has just got a whole lot easier with the introduction of the SPLIT table function in the lastest IBM i 7.3 TR6.

The syntax of this new table function is as follows:

SPLIT(input list or column,separator character)

SPLIT returns two columns in its results:

1. ELEMENT:  the values extracted from the "input list". This is a CLOB variable that is 2 gigabytes in size.
2. ORDINAL_POSITION:  Not ORDINAL as is given in the IBM documentation for SPLIT. This is the relative position of the value returned in ELEMENT from the original "input list".

Here is an example using an "input list", or a string to you and I, of names.

New subfields added to Program Status Data Structure

The latest Technology Refresh for IBM i 7.3, TR6, has seen two new subfields added to RPG's Program Data Structure. This data strucutre provides me with a wealth information about the status of the program while it is running, and when it errors.

I always add the Program Status Data Structure, PSDS, to all of my RPG programs. I can dump the program and learn a lot of what happened from the information contains within the PSDS.

Rather than manually entering the same data structure into every program, I have my PSDS in a member I just copy, or include, it into the source members of others.

Using SAMEPOS in data structures

Having written about a couple of the Db2 for i (SQL) additions that were made in the latest Technology Refresh, TR6, to IBM i 7.3 I thought I would write about the first of the two new additions to the RPG programming language, the SAMEPOS keyword used in data structures.

We have all created data structures where we have needed to overlay some subfields with another subfield. The way I am use to doing it is to determine at which position of the data structure I wish to start my new subfield, and use the POS to denote where this subfield starts.

01 dcl-ds *n ; 02 SubField1 char(1) ; 03 SubField2 char(1) ; 04 SubField3 char(1) ; 05 SubField4 char(1) ; 06 SubField5 char(3) pos(2) ; 07 end-ds ;

Subfield5, line 6, will overlay Subfield2, Subfield3, and Subfield4.

The SAMEPOS keyword, line 6 below, makes it easier as all I have to give is the data structure subfield I want to start my overlay.

RPGPGM.COM 6th birthday

I know this is going to sound glib, but RPGPGM.COM’s anniversary always creeps up and surprises me. I cannot believe that I have been writing this blog about all my favorite IBM i things for six years.

In past twelve months I wrote the 500th post for this blog. I still worry about running out of ideas, but I have fortunate to have lots of new material as IBM keeps adding new things to IBM i, via the twice yearly Technology Refreshes and a couple of new releases.

Each anniversary I pick what I think have been five of the most interesting things I have written about in the past twelve months. My picks from the last year are:

Using Data Areas with SQL

The latest technology refresh, IBM i 7.3 TR6, and the new release. IBM i 7.4, brought us a new view and table function that allows us to retrieve information from data areas. The closest thing we have had to this before is the Retrieve Data Area command, RTVDTAARA, but these gives us more than just the value held in the data area. The only down side with these being a view and table function is I cannot update the data area using them.

They both have the same name, DATA_AREA_INFO, and returned columns have the same names. The only difference is that the view has two additional columns. If I wanted to retrieve the information for just one data area I would use the table function. The view will list all data areas that fit the selection criteria. I could still get the information for the one data area using the view, but it is faster using the table function.

Updated executive guide for IBM i

The latest version of IBM's "Executive guide to the strategy and roadmap for the IBM i integrated operating environment for Power Systems" has been updated for IBM i 7.4.

This is a document you should consider sharing with senior management to help them realize that this platform, PowerSystems, and operating system, IBM i, is not the "same old 400". It is something way better.

You can download the document by clicking here.

Using SQL to retrieve data from spooled files.

One of the additions to IBM i with release 7.3 TR6 was a Db2 for i (SQL) table function that allows me to retrieve the contents of any spool file in my IBM i. I am sure that there are some people who are thinking "So what, I can do that with DSPSPLF"

Yes, I can view the contents of a spool file, but I cannot copy data from it. Why would I want to copy data from a spool file?

I am sure we all have reports in our ERP applications that the users would prefer as a spreadsheet. You are reluctant to change the ERP program as by doing so it will invalidate the support contract. Therefore, you copy the spool file to a physical file, and then parse the report's columns into fields, that are then written to the output file.

Using this new table function it becomes, in my opinion, a whole lot easier.

Using Check BiFs in CL

It has been several been several months since I last wrote a post about CL programming, therefore, I thought it would be a good excuse to share something I was using in a CL program I wrote a couple or so weeks ago. I am sure many have used the Check, %CHECK, and the Check reverse, %CHECKR build in functions, BiFs, in RPG to check a character string for the first place a certain character(s) is not. In the CL program I was writing I needed the same functionality.

IBM has been adding BiFs to CL that are similar to the ones to be found in RPG. Fortunately there are %CHECK and %CHECKR BiFs in CL too. The general format of them is similar to the RPG equivalent:

%CHECK(<test values> <variable> <starting position>) %CHECKR(<test values> <variable> <starting position>)

I found I could use these BiFs in my CL in two ways:

Get information about a job, including current SQL statement

I knew of the GET_JOB_INFO table function, but having played with it I did not see a reason why I would use it, I preferred the ACTIVE_JOB_INFO table function.

It was not until someone pointed out to me that I could see the current SQL statement being executed in a job that my interested with GET_JOB_INFO was piqued, especially as this would allow me to see what those data base server jobs are doing.

I decided to compare what GET_JOB_INFO would show me if I executed the following statement via two ways:

1. Using Operations Navigator's "Run SQL Scripts" client
2. Using the STRSQL command

Today is TR6 day

Today, Friday May 10, 2019, the new Technology Refresh for IBM i 7.3 is available!

When it was announced last month I went and picked what I thought my favorites would be. You can read about them here.

The PTFs for TR6 are:

• SF99727 730 Technology Refresh - level 6

At present I cannot find any mention if this PTF included the Db2 for i, RPG, etc enhancements too.

I will update this post as I find more information.

These are what i thought look to be the most interesting enhancements with TR6:

• The initial 7.4 and 7.3 TR6 annoucement
• My picks for the biggest things in 7.4 and 7.3 TR6

We have to wait until Friday June 21, 2019 for the the new release, IBM i 7.4 .