Thursday, September 12, 2019

End of support for IBM i 7.2 is announced

end of life for ibmi release 7.2 in 2021

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.

Wednesday, September 11, 2019

Using SQL to determine which subsystems are active or not

list all subsystems and whether they are active

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:

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

Wednesday, September 4, 2019

Variable length arrays in RPG

arrays with varying number of elements

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.

Wednesday, August 28, 2019

Parms built in function added to CL

using bif parms to find number of parameters passed

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:

Friday, August 23, 2019

Wednesday, August 21, 2019

Executing CL commands using RUNSQLSTM

adding cl commands to member used by 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

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

Wednesday, August 14, 2019

More about SQL Sequences

sequences view, select next value, select previous value

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:





Wednesday, August 7, 2019

Using a SQL Sequence to renumber a column

sql sequence to provide a sequentail number to a sql statement

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.

Wednesday, July 31, 2019

Extracting the job's name from the Job Name

extracting name part of 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:


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.

Saturday, July 27, 2019

Thank you OCEAN UG

I wanted to take this opportunity to publicly thank the the organizers of OCEAN User Group's TechCon19 for an excellent event.

A "thank you" also goes to all the presenters who were at the event.

And another "thank you" to those who attended my presentation. I hope you found something interesting from it, that you will be use.

Wednesday, July 24, 2019

Using SQL to determine if subsystem is started

check if subsystem active and count of jobs in it

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:

Saturday, July 20, 2019

Ocean’s TechCon 2019 in 6 days

In six days I will be giving my presentation SQL at Ocean UG’s TechCon 2019. I have my presentation finished, and I am ready to present it on Friday (July 26) at 1:00 PM in room 201. If you are attending this conference I hope you will consider attending my presentation.

If you are in So Cal there is still time to register to attend the best User Group conference in the West.

I hope to see many of you there!

Wednesday, July 17, 2019

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

this is the easiest way to get the cl program name

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.

Wednesday, July 10, 2019

Which users are authorized to these files and tables?

systabaut, system table authority, sql view

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:

Wednesday, July 3, 2019

Checking for Firmware updates

check 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:

Wednesday, June 26, 2019

It is now easier searching message files

new sql view for 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_INFO 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:

Monday, June 24, 2019

Friday, June 21, 2019

New release, IBM i 7.4, day

ibm i release 7.4 is available today

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:

Wednesday, June 19, 2019

New table function to break apart values in columns and fields

suing string able function to break long strings into smaller parts

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.

Monday, June 17, 2019

Online presentation is this week

This Thursday, June 20, at 11 AM PDT I will be giving an online presentation with New Generation Software (NGS). My part of the presentation will be about using SQL views to make it easier to get data from your IBM i database.

If you would like to watch the presentation live you will need to register on NGS's website here.

If you cannot attend the presentation at the given time, NGS will be recording the presentation and uploading it to their web site. You will still need to register to watch the recording.

I am looking forward to doing this, and I hope you will be able to attend the live event.

If you want to know what time 11 AM PDT is in your time zone click here.

Thursday, June 13, 2019

New subfields added to Program Status Data Structure

two new subfields added to the new 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.

Wednesday, June 12, 2019

Using SAMEPOS in data structures

using keyword samepos to position subfields 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.

Monday, June 10, 2019

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:

Wednesday, June 5, 2019

Using Data Areas with SQL

retrieving data from data areas using 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.

Thursday, May 30, 2019

Updated executive guide for IBM i

executive guided updated for IBM i 7.4

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.

Wednesday, May 29, 2019

Using SQL to retrieve data from spooled files.

spooled_file_data sql table function to extract data from a spool file

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.

Friday, May 24, 2019

IBM i: A platform for innovators, by innovators


IBM i 7.4 is here! We are excited for this release and looking forward to the future of the IBM i platform as you continue to innovate with us. Here’s to 30+ years.

Wednesday, May 22, 2019

Using Check BiFs in CL

check and 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:

Wednesday, May 15, 2019

Get information about a job, including current SQL statement

get_job_info to retrieve 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

Friday, May 10, 2019

Today is TR6 day

ibm i 7.3 tr6 is out today

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:

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:

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

Wednesday, May 8, 2019

Parsing SQL statements using a table function

parse_statement sql table function

Someone brought to my attention a Db2 for i table function that parses the names of the tables and columns used in string passed to it. I have to admit I had not heard of this, therefore, I decided to test out what kinds of information it could return.

The PARSE_STATEMENT appears to have been introduced as part of the IBM i release 7.2 . It takes a string that contains a SQL statement and returns the columns, objects, etc. contained within. The basic syntax is:

SELECT * FROM TABLE(<sql string>)) AS A

For example:

Monday, May 6, 2019

Time to download ACS

access client solutions or acs for ibm i

April 30 marked the end of IBM's support for one piece of IBM i software we have all used, Client Access. Its replacement is Access Client Solutions, ACS. It does everything that Client Access did, including 5250 emulation.

Why have I mentioned this?

I often get asked which 5250 emulator people should to attach to free IBM i partitions like RZKH's free IBM i partition PUB400.COM. The problem has been that the best emulators were not free.

ACS can be downloaded for free. And once installed its 5250 emulator can easily be activated to connect to servers like PUB400. Now you can have a quality 5250 emulator that does so much more than those others you have used in the past.

Wednesday, May 1, 2019

Conditioning display file size

24x80 *ds3 and 27x132 *ds4 display size

In the past couple of week I have been asked by several people how to condition whether a display file is shown in *DS3 (24 rows x 80 columns) or *DS4 (27 x 132) format. Rather than writing the same message to each one, I have decided to write this post so I can direct them to it.

These days I doubt if any of us IBM i developers are still using "dumb" workstations, we are all using PCs. In the settings of our 5250 emulator there is a configuration setting so we can set our emulated session to have a screen size of either 24x80 or 27x132. Personally I always configure my sessions to be 27x132.

You can see, and change, this setting depending on the emulator application you use.

Wednesday, April 24, 2019

More about IBM i version 7.4 and 7.3 TR6

more new features in ibm i 7.4

After all of yesterday’s excitement with the announcement of a new release of IBM i 7.4, and a Technology Refresh for 7.3 TR6, I have had a chance to review the documentation and these are the things that caught my eye:

The links below are to the relevant pages in the KnowledgeCenter.

SQL: New

Tuesday, April 23, 2019

IBM i version 7.4 is announced

Update at 5:00AM (following day)

I went through and picked out what I thought be my favorites here

Update at 10:09AM

Today is announcement day as IBM has announced a new release of IBM i, 7.4, and a new Technology Refresh for IBM i 7.3 .

Since my first post this is what I have found:

The new release, 7.4, will only be supported on the PowerSystem servers with the Power8 and Power9 processors.

The availability dates for these are:

Wednesday, April 17, 2019

Discover size of library using SQL

get library size using sql

Someone asked me if there was a way, using SQL, to determine the size of a library?

This is a trick question as few people realize that the size of a library and the total size of all the objects within are different.

The questioner clarified that they wanted to know the total size of all of the objects in the library. But I gave him examples showing the difference between the two.

He was using the Display Library command, DSPLIB and was trying to find a way to get the same total from this command in a way it could be retrieved programmatically.

In the days before all the cool Db2 for i views, table functions, etc. I would have used the Display Object Description command, DSPOBJD. To get the size of the library I would have used the following:

Monday, April 15, 2019

Presenting at Ocean UG

Tomorrow, Tuesday April 16, I will be one of four presenters at the Ocean Users' Group meeting. This is the first time I will be presenting to an IBM i user group, and I am looking forward to it.

If you can attend the event is at the National University in Costa Mesa. Dinner is at 5:30 PM, with the presentations starting at 6:30 PM.

Wednesday, April 10, 2019

Validating SQL statements

qsqschks api used to validate sql statements

This post is about an API I stumbled across when looking for another. The Syntax Check SQL Statement API, QSQCHKS, will validate any SQL statement passed to it. I can see this being useful when I build a SQL statement in a variable before executing it. If I was to validate the statement it would allow me identify any errors I may have made.

When I show my code, below, you might think it looks complicated, with several strangely names data structures, which is why I have placed it in its own procedure with just one parameter passed to it and one returned. K.I.S.S. .

I have created two procedures to achieve my goal: to return a character variable containing the SQL error message's text. To this end I have created two procedures, the first in RPG, the second in CL. I could have combined them into a single service program, but as this is an example I left them as modules, RPGMOD001 and CLMOD001, to be bound to the calling program at program creation (compile) time.

Thursday, April 4, 2019

Happy St Isidore of Seville day

Today is the day of the Catholic church's patron saint of computers, computer programmers, and the internet. St Isidore of Seville was a sixth century scholar and bishop who was given this special role in 1997 by Pope John Paul II.

Therefore, if you are having one of those days where every is just going to pieces it might be time to ask for divine help with a quick prayer to St Isidore.

You can learn more about this saint clicking this link here.

Wednesday, April 3, 2019

Using FTP with IFS files

ftp with files in ifs

In previous posts I have written about using FTP to copy files from one IBM i partition to another. Almost all of the examples I gave were for transferring of a file, or files, in what I call the "IBM i environment", good old libraries and files.

Earlier this week one of my colleagues came to me and asked if there was a way to use FTP to copy a file in the IFS of one IBM i to the IFS of another partition. I looked back at the posts I had written and realized that I did not have a good example of how to do this. I thought I would share what we played with so he could understand what to do.

Before we get started I need to explain that FTP with IBM i has two naming conventions for the file's path:

Wednesday, March 27, 2019

Delaying a job for a fraction of a second

pause a program for a fraction of a second

Someone asked me if there was a way to pause a job for a fraction of a second. Their colleague had advised them to create a Do-loop that would be performed a certain number of times and that would be "good enough". I was very pleased to learn that the person who asked this question was not satisfied with that answer, and correctly thought that there must be a better way.

I am sure we are all know of the Delay Job command, DLYJOB, but it will only delay a job for whole seconds and not fractions. Fortunately IBM i has two external C functions that can be used to delay a job:

Wednesday, March 20, 2019

API to check if debug is active

determine if debug running

There are some APIs I stumble across and I am left wondering why they were created? The API I will be writing about today, QteRetrieveDebugAttribute, is one of those. This API is used to determine what the debug attribute values are for a job when it is being debugged. I am not going to list which debug attributes they are as it is not relevant to what I am trying to determine. If you are interested in learning what they are click on the link to IBM's documentation at the bottom of this post.

In this example I am just interest to know if the job is being debugged.

I am going to have a procedure within a module call the API, this way I can either bind it into the calling program or included it in a service program, which is my preference. Let me start with the module:

Thursday, March 14, 2019

IBM i 7.4 section now in KnowledgeCenter

ibm knowledge center page for coming ibm i release 7.4

In January I found signs that a new release of IBM i would happen this year. This week I found another sign when I discovered that the KnowledgeCenter now has a section for IBM i 7.4.

Alas, it is just a "place holder", with no information is within it, but it is another sign of the pending new release.

If you are interested in visiting the new section click here.

Wednesday, March 13, 2019

Using SQL to check if a batch job is active

sql to find if job is active in subsystem

A colleague asked me if there was an easy way, using SQL, to tell if a job was active in a subsystem. This subsystem, I am going to call it TESTSBS, should contain two jobs that remain active all day, copying and sending data between various IBM i partitions. During the day-end process the jobs are ended, and then resubmitted after the backups.

Recently there have been problems with these jobs. If one of these jobs errored the system operators would answer the message with "C", which would end the job and the data would not be transferred until someone else noticed. Or the jobs would not end during the day-end process, therefore, after the backups completed two new versions of the job would be submitted resulting in four jobs, two of each.

My colleague had looked into ways of determining which jobs were active in the subsystem. During the day if one of the jobs was missing it could be resubmitted. After the backup finished only if the job was not active would it be submitted. All of the methods she had come up with to determine if the jobs were active were, in her opinion, too complicated. Which is why she came to me.

Tuesday, March 12, 2019

Does end of Google+ mean the end of this website?

Over the past week I have received several communications asking if the end of Google+ means that this site will also be closing.

This site is hosted on another Google product, Blogger. Google's commitment to the Blogger platform continues unabated. There have been some minor changes to Blogger because of Google+'s demise, but none of these will effect your viewing pleasure of this website.

If you have included me in your Google+ circle and want to continue that relationship feel free to send me a Facebook friend request.

Wednesday, March 6, 2019

Copying part of an existing spool file to a new one

copying part of one spool file to a new one

I was asked this week how to send the last few pages of a report to a remote output queue. The queue had been configured in a way that even though I could change the range of pages to print, number of copies, etc. despite this a single copy of the entire report would be printed. Such a waste of paper when the report was 450 pages.

The way I overcame this is to create a new spool file, copying data from the original. In a previous post I have shown how to copy multiple spool files and have them print as one. The spool files are copied to a physical file, then the data from the physical file is copied back to a printer file. If I only want a few pages I just need to find the relative record number of the start and end of the section I want to print, and then enter that data when I use the Copy File command, CPYF. For the new spool file to look like the old I also need to know the page width of the original spool file, the characters and lines per inch, CPI and LPI. I can find these myself by looking at the original.

It is not a big deal to do this manually, but my philosophy is if I need to do it more than a few times then it is best to write a program to do it for me. This example program is based upon a program I wrote to do this, make a new spool file that contains a subset of some of the pages from the original spool file, that can then be sent to the remote output queue.

Wednesday, February 27, 2019

Finding really big objects in my libraries quickly

quickly finding size of objects in library using object_statistics

The shout goes out: "The IBM i is at 92% of disk capacity! Delete as much test and work stuff as you can!"

We don't have time to run the best way to find the largest objects on this IBM i. At this point I am just concerned with the objects in my personal, work and test libraries.

I don't want to use the Display object Description command, DSPOBJD, as I will have to build a file, to then query, and that will only add to the amount of used disk space.

Fortunately Db2 for i provides me with a solution I can get data from immediately. In a prior post written about using the OBJECT_STATISTICS table function to find objects that have not been used for a long time. I can use the same table function to identify find the largest objects in my personal, work, and test libraries.

Friday, February 22, 2019

Another sign a TR for IBM i 7.3 is imminent

IBM has started teasing us with information about a new Technology Refresh.

Yesterday a new page was published in the "IBM i Technology Update" wiki for TR6 for IBM i version 7.3. At present the page is just a place holder with the words "stay tuned..." on it. You can visit it here.

What I find interesting is that there is no mention of an equivalent TR for IBM i 7.2 . This would be TR10.

Wednesday, February 20, 2019

Capturing system statistics using SQL

system_status_info sql view rather than wrksyssts command

I thought this question would be a good reason to introduce another Db2 for i view that I find useful when monitoring IBM i:

I would like to collect the percentage CPU used and percentage system ASP used (from WRKSYSSTS) every day at 00:00 hours and save it in an outfile or print it to an spool file or send the values as a message to a user.

I could dump the contents of WRKSYSSTS to spool file, copy that to a data file, and then extract the information I want from the data file, or I could use an API. But why would I bother to go through all that effort to get the same information I can get from the Db2 for i view: SYSYTEM_STATUS_INFO

What information do I want? On the WRKSYSSTS display I only want the following:

Wednesday, February 13, 2019

Returning rows where the last few characters are not numeric

checking last 3 characters if not number

Sometimes I am asked a question by a colleague I think would be a good example to share in this blog. This week I was asked was there a way in a SQL statement to only return the records from a file where the last three positions are not numeric. For example:

  • A2CDEF = Include
  • ABC4EF = Exclude

There is an added complication as the string I need to test is contained within a ten long character field, but it can be of any length 1 - 10.

I quickly made a test file, TESTFILE, that I can use for these examples. The file contains the following records:

Wednesday, February 6, 2019

Screen at a time subfile using SQL

screen at a time subfile written using sql

In my last post I gave an example of how to write a "screen at a time" subfile in RPG. When I finished writing that post I thought to myself how would it be possible to do the same using SQL.

There are two ways I could read, Fetch, data from the input file:

  1. Single row fetch
  2. Multiple row fetch

The single row Fetch is almost as inefficient as a RPG read operation, retrieving one record at a time from the file. The multiple row fetch is more efficient and the method I am using in this example.

The data file this program uses, PERSONP, is the same as in my last post, therefore, I am not going to describe it. If you care to refresh your memory then you should go to that post.

I made three changes to the display.

Monday, February 4, 2019

Presenting at OCEAN

I will be one of five presenters at the April 16 OCEAN user group meeting. I have been given 10-15 minutes, and have decided to spend that time giving some examples of what I consider are ways to get the most from SQL.

The meeting starts at 5:30 PM, and am I to assume as my name is first on the list I am the first person presenting?

Details of April's meeting.

I hope I will see you there!