Friday, August 14, 2020

New version of ACS:

acs version download

A new version of IBM's Access Client Solutions, ACS, has been released. ACS includes:

  • 5250 emulator
  • Printer emulator
  • Navigator for i
  • Run SQL scripts
  • File transfers
  • And a lot more

And it is free to download from IBM's website.

If you are using a free IBM i service, like RZKH's, then this is what you should use as it gives you a lot more than the other 5250 emulators.

Wednesday, August 12, 2020

Using table function rather than view for object privilege information


One of the new arrivals with the latest rounds of Technology Refreshes was a table function to retrieve the authority for a particular object. "Wait!" I hear you regular readers say, "Wasn't there already a SQL View added to show that information?" And you are correct in IBM i 7.3 TR2 the OBJECT_PRIVILEGES View was released.

With a Table function I pass it a set of parameters and only the results for those values are returned. If I use a View then the entire View has to be searched for the rows matching the selection criteria. By using a Table function, rather than a View, I can get the information I want quicker. I will demonstrate this in the examples below.

The OBJECT_PRIVILEGES Table function and View have the same columns. The only annoying difference is the name of the authorized user profile in the View is AUTHORIZATION_NAME, while in the Table function it is AUTHORIZATION_USER.

In these example I am only interested in determining if user profile is authorized to use a file, TESTFILE. The profiles authority is held in the column OBJECT_AUTHORITY. Therefore, if I want to see who is authorized to use the file TESTFILE in the library MYLIB I would need to pass the Table function the following:

Tuesday, August 11, 2020

Removing deleted records faster than RGZPFM

cpyf quicker than rgzpfm

The subject of this post is not new, but I thought I would share this as this is the quickest way I know to get rid of millions of deleted records taking up space in your files. The last time I used this method was with a file that contained 1 million "active" and 11 million deleted records. The application owner of this file had a fixed amount of time to remove the deleted records in their weekly maintenance "window". Having performed tests using RGZPFM she found that it took longer than the allowed, and came to me for ideas.


The part of this process that many people forget is all the access paths are reorganized too. In this case there were a plethora of logical files built over this file, I forget exactly how many but too many for my liking.

What was my suggested alternative?

Thursday, August 6, 2020

Viewing IFS object's authority using SQL

using sql to view authority of files and folders in ifs

Included with the latest Technology Refreshes is a new table function which allows me to view objects' privileges of the folders and files in the IFS. Object privileges/authorities are something that I am asked for during audits, therefore, I was really interested to learn what information I can get from it.

The only gotcha is as a table function IFS_OBJECT_PRIVILEGES returns the results for one set of objects, rather give me the ability to get information for all objects as I would using a view.

As I have done in many of my previous examples I am not going to show you all the columns returned by this table function, just the ones I find interesting. There is a link at the bottom of this post to IBM's documentation for this table function which includes a full list and description for all of the columns.

The columns I am going to work with are:

Wednesday, August 5, 2020

SELECT with EXCEPT clause to find differences between records in two files

using except clause to join 2 select statements together to see differences

I saw this mentioned in a Facebook discussion where someone said he was using what I will describe below to determine the difference between the records in two files. I had not heard of this method so I wanted to try it out for myself and determine whether it is something I could use in the future. Two SQL Select statements joined with an EXCEPT clause. But before I show examples of that I need data.

I am going to:

  • Create a file
  • Add data to it
  • "Clone" the file to make an exact copying, including the data
  • Change the data in the "clone"
  • Compare the two files

In these examples I am just going to use a file with one field. The same principles work with one field as it would with a hundred fields. I decided to K.I.S.S. and just have one field in my file. The layout for the file is:

Thursday, July 30, 2020

Viewing HTTP server information using SQL

view data about apache http server on ibm i

I want to give credit to IBM's Db2 for i team for continually adding SQL views, table functions, etc. to information that was not easy to get using APIs or other tools. Another example of their excellent work, introduced with the latest Technology Refreshes, is the view HTTP_SERVER_INFO. Previously this information was only available using the Web Administration for i GUI.

This view, which is located in the library QSYS2, displays information about the Apache server in the HTTP server for IBM i, and will only display data about the enabled and active functions.

As usual with my examples of Db2 for i views and table functions I am not going to list all of the available columns, just the ones I found interesting:

Wednesday, July 29, 2020

Removing apostrophe in a field using SQL

replace 1 character with another using rpg and sql

There are occasions when interfacing data to non-IBM i database where an apostrophe ( ' ) in a field can cause a conversion error. In this post I am going to give some simple examples of how apostrophes can be converted to something else using SQL and RPG.

Let me start with the DDS file I will be using in these examples. The file is called TESTFILE and contains one field:

A          R TESTFILER
A            COMPNAME      35A

The field contains the following three records:

Saturday, July 25, 2020

It's been IBM i for longer than AS/400

In a post on LinkedIn yesterday Steve Will, chief architect of the IBM i, shared an interesting milestone in the life of the this operating system.

This operating system had been known as IBM i for a long as it was known as AS/400.

Wednesday, July 22, 2020

Subtracting days from a "date" when it is a number in a file

substract 35 days from a date when it is a packed numeric field

The germ for this post came from a question:

How can I subtract 35 days from a date in my files using SQL? The date is a packed number

Most of us who deal with older databases find that the "dates" in the files are not true dates fields, they are numbers masquerading as dates. I have, in a previous post, shown how to change a numeric representation of a date to a date with SQL, this is taking it a step further by converting the date back to a number and updating the file with the new value.

In these examples I will be using the numeric representation of the date in two formats:

Tuesday, July 21, 2020

How to view pending database commits using SQL

finding uncommited transactions using DB_TRANSACTION_INFO view

Another addition to Db2 for i in the latest Technology Refresh, IBM i TR2 and 7.3 TR8, was a view called DB_TRANSACTION_INFO. This view lists what the IBM documentation describes as: "returns one row for each commitment definition".

What does that mean? Having played with this View for a while I have found that it just lists all uncommitted database transactions to a file or table that is using commitment control. Database operations to files and tables that do not use commitment control do not appear in this View.

There are far more columns in this View than I am going to use in my examples, so I am going to say to you if you want to know what all of the columns are click on the link to IBM's documentation at the bottom of this post.

Thursday, July 16, 2020

Viewing all of the subsystems autostart jobs in one place

view subsystem autostart jobs using sql

If I needed to see which autostart jobs were in my IBM i partitions I would either have to use the Display Subsystem Description command, DSPSBSD, to view the autostart jobs for each subsystem or use an API. Fortunately the latest Technology Refreshes include a Db2 for i View that gives me the information I want.

Before I give examples of using the new View I want to show how to get to the same information using the DSPSBSD command so that you can compare the data from the two.

If I want to see what autostart jobs there are in the QCTL subsystem I would use the following command:


When the Enter key is pressed the following menu is displayed:

Wednesday, July 15, 2020

Listing SQL Indexes' keys

using sysindexes and syskeys to make a list of index keys

Most of the time I do not care what the keys are for any SQL Indexes I have built over DDL tables or DDS tables. When I want to extract data from a table or file using SQL I build the statement using the table or physical file, and Db2 of i is smart enough to find the best access paths (logical file or SQL Index) for the statement.

I recently encountered a company where their programmers had been building Indexes. Building Indexes is not a problem, but each programmer had been building their own Indexes without consulting with the other members of the team. The team leader was concerned that they now had many indexes that had the same keys. He asked me if I could help him identify the following:

  1. Indexes built over which table or file
  2. The keys of those Indexes

Wednesday, July 8, 2020

Make second line appear and disappear on a subfile

subfile that expands to a 2nd line and contracts

I was asked how to do this by someone who said that no-one in his department knew how to: have a second record appear in a subfile when a function key is pressed, and disappear when the function key was pressed again.

Fortunately this is a simple thing to do with just three keywords in the display file's DDS:


And there is not much we have to do in the RPG program that uses the subfile.

I have deliberately stripped down the display file and RPG code to what I consider a minimum to, in my opinion, makes it easier to understand.

Let me jump right in and show you the display file I will be using in this example:

Tuesday, July 7, 2020

Selects with Intersect to find common records in two files

sql selects with intersect to find common data

Having written about Selects with Union I thought it was time to mention to Selects with Intersect.

Select statements with the INTERSECT combines the matching results from two tables into one set of results. To reiterate "matching" means that the same value must appear in the given column(s) in both files.

I have two example tables with the same column names, they contain the following data:

Table 1 Table 2

Wednesday, July 1, 2020

Writing to the same spool file from multiple programs

output from 2 programs to same spool file

The question sounded simple: How can I write to the same spool file from two programs?

I have written before about copying from more than one spool file to create a new one. But this time I will be writing to the same spool file directly from two programs.

Let me start with the DDS for the printer file:

01 A          R HEADER
02 A                                     1'PAGE HEADER'
03 A                                      SKIPB(001)
04 A          R FORMAT01
05 A                                     1'PROGRAM 1'
06 A                                      SPACEB(001)
07 A            COUNTER        3  0     +2EDTCDE(Z)
08 A          R FORMAT02
09 A                                     1'PROGRAM 2'
10 A                                      SPACEB(001)
11 A            COUNTER   R             +2REFFLD(COUNTER *SRC)
12 A                                      EDTCDE(Z)
13 A          R ENDREPORT                                            
14 A                                     1'* * * END OF REPORT * * *'
15 A                                      SPACEB(001)

Tuesday, June 30, 2020

Subsystem status using SQL

check status of subsystem using new sql view

Last year I wrote about a way to check if a subsystem was active or not. The statement combined two table functions and could only check if a subsystem was active, if it was not returned in the results it was assumed to be inactive. Included in the latest round of Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, is a new SQL View all about subsystems, now I can get the same information just from one View, including the status of the subsystem.

The information I am interested from the View SUBSYSTEM_INFO is:

  • Subsystem name and the library it resides in
  • Status: active, inactive, etc
  • Number of jobs currently running in the subsystem
  • Maximum number of jobs that could run in the subsystem
  • Job that is monitoring the subsystem

Thursday, June 25, 2020

Searching for reserved characters in SQL

search strings for sql special characters

SQL like all other programming languages has a number of characters that are reserved for certain uses. What happens if you need to search for one of those characters in a table or file? In this post I am going to give you two ways you can do this.

To show how this works I have a test file with five records in it:

100% REAL

What I want to do is…

  1. Find the record with the apostrophe ( ' ) in it
  2. Find the record with the percent sign ( % ) in it

Wednesday, June 24, 2020

Using SQL to validate packed fields

validate packed number fields with sql

When I heard of these three table functions being introduced as part of the latest round of Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, I was really interested to know what they did as their names, "Validate data", really piqued my interested. These table functions will validate packed numeric fields at, what I like to call, three levels:

  1. VALIDATE_DATA:  validates at the member level
  2. VALIDATE_DATA_FILE:  validates at the file level
  3. VALIDATE_DATA_LIBRARY:  validates at the library level

These three exist in the SYSTOOLS library.

It is nigh impossible to put bad data into date, time, and timestamp fields in files. But it is not too hard to put bad data into a packed numeric field. While rare, I am seeing this more as data is taken from other types of databases and applications, and loaded into files in IBM i.

Wednesday, June 17, 2020

Control progression of cursor in display file

control cursor movement on dspf

The question was how to control the way the cursor moves from field to field on a display. The display had two columns of fields and the questioner wanted the cursor to progress down the first column's fields before moving to the top of the second column.

My example display file contains two records format. The first I will be using to demonstrate the default cursor progression, and the second how I can move down the first column's fields before moving over to the second column.

The DDS code for the first record format looks like:

A          R SCREEN0
A            Z001           3   B  2  3
A            Z002           3   B  2 10
A            Z003           3   B  3  3
A            Z004           3   B  3 10
A            Z005           3   B  4  3
A            Z006           3   B  4 10

Tuesday, June 16, 2020

Deleting spool files using SQL

deleting old spool files using sql procedure

Many of the IBM i partitions I have worked upon have had an issue of old spool files occupying valuable storage space. Since IBM i 7.2 there has been a way for me to analyze the spool files, using SQL, and make decisions on which would be deleted. The process of doing the deleting had to be entrusted to a program containing CL commands.

The latest round of Technology Refreshes, 7.4 TR2 and 7.3 TR8, gave me a way I can delete old spool files using a SQL procedure, DELETE_OLD_SPOOLED_FILES

Warning:  Use this SQL procedure carefully. If you do not you run the risk of deleting all spool files from your partition.

DELETE_OLD_SPOOLED_FILES has five parameters:

Wednesday, June 10, 2020

Happy 7th birthday RPGPGM.COM

Another year has passed and this blog has reached its seventh anniversary. We are living in interesting times with IBM i, and over the years I have been fortunate to write about many things, that in my opinion, have made significant improvements to programming with this operating system. As well as a whole lot of cool additions to Db2 for i (SQL). I have learn a lot from what I have written about, and I am sure that I write better code than I use to.

The past year has been an exciting time, not only for this blog but for me too. I would include the following as my personal high points, in no particular order:

Tuesday, June 9, 2020

Making a hash of records for comparisons

sql hash_row to allow comparison of records in files

Another addition to Db2 for i as part of the latest Technology Refreshes for IBM i 7.4 TR2 only, is the ability to create a hash value for a record from a file using the HASH_ROW SQL built in function. I am not going to describe what hash values are, if you are interested there is a Wikipedia article describing it. I am just going to say that hash-ing a string produces hexadecimal value of the data.

Previously I had been able to hash individual fields, or columns, using the various standards of hash. As HASH_ROW uses SHA512 I can do the same for a field using HASH_SHA512:

01  SELECT HASH_SHA512('A') AS "Hash",
02         LENGTH(TRIM(HASH_SHA512('A'))) AS "Length"

Wednesday, June 3, 2020

Using SQL to compare files for differences

sql table function compare_files to compare layout and data

Having looked at, and played, with a number of the new additions to Db2 for i in the latest new Technology Refresh this is my favorite and in my opinion the most useful: table function COMPARE_FILE.

COMPARE_FILE gives me the ability to identify differences between physical files, source files, and DDL (SQL) tables. I can compare two files layout and the data within them with this table function, and have a results row returned for each difference. Wow, both of those with the same table function!

Alas, it is only available in the Technology Refresh for 7.4 only, IBM i 7.4 TR2. It is not available in IBM i 7.3 TR8.

COMPARE_FILES has a number of parameters:

Tuesday, June 2, 2020

New SQL table function to display a job's locks

retrieving job lock information using sql

Before the latest round of new Technology Refreshes, 7.4 TR2 and 7.3 TR8, the only ways I knew to get Job Lock information was either by using an API or the Display Job command, DSPJOB. These new TRs introduced a new table function, JOB_LOCK_INFO, that allows me to see the same information with just a simple SQL statement.

JOB_LOCK_INFO has three parameters:

  1. Job name: This is the only required parameter. This can either be the full job name, or for information about the current job an asterisk ( * ) can be used
  2. Internal objects only: Optional. NO, which is the default, shows only external objects. YES shows internal objects, internal space objects, and external objects
  3. Ignore error: Optional. NO when an error is encountered it is returned. YES, which is the default, only a warning is returned

Thursday, May 28, 2020

Define a new Data Structure from one that is nested

using nested data structures in likeds

In the past I have written about nesting data structures, a way to contain data structure(s) within another data structure. And there are times I have wanted to define a new data structure to be identical to another using the LIKEDS keyword.

01  dcl-ds One qualified ;
02    OneSF1 char(1) ;
03    dcl-ds Two ;
04      TwoSF char(5) ;
05    end-ds ;
06    OneSF2 char(1) ;
07  end-ds ;

08  dcl-ds Test1DS likeds(One) ;

Wednesday, May 27, 2020

Improvements to RPG's timestamps

improvements to rpg timestamp

Ever since timestamps were introduction to RPG I have always felt aggrieved that they never contained the entire timestamp. Timestamps have six decimal places following the seconds and only the first three positions, milliseconds, were filled. The last three positions were always zero. Occasionally I have found in timestamp fields in two records with same value. I would unable to determine which record is older, without using other fields or the file's relative record number.

The latest round of new Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, brought improvements to the way RPG does timestamps.

Some of what I am going to describe here may have been available in earlier releases or TRs.

Tuesday, May 26, 2020

Key data structure big improvement

change to RPG %kds

I do not use the Keyed data structure, but I can appreciate this change that occurred as part of the latest new Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, was significant.

Before the latest TRs the second parameter of the %KDS built function had to be a number to denote how many of the key fields were to be used. The change in the new TRs now allows for that second parameter to also allow a variable.

The old way would look something like this:

01    if (NbrKeyFlds = 1) ;
02      chain %kds(FileKeys:1) TESTFILER ;
03    elseif (NbrKeyFlds = 2) ;
04      chain %kds(FileKeys:2) TESTFILER ;
06    endif ;

Wednesday, May 20, 2020

Retrieving information about a library using SQL

new table function library_info to get library data using sql

As part of the new Technology Refreshes, 7.4 TR2 and 7.3 TR8, came a table function to return information about a single library. I have to admit that I don't really care about some of the information returned in the results, but there are three columns I am going to find very useful.

The new table function LIBRARY_INFO is found in the library QSYS2. It combines data elements from the RTVLIBD and DSPOBJD commands. When calling this table function there are two parameters, one mandatory and the other is optional:

  1. Library name:  No surprise that this is the mandatory parameter
  2. Ignore errors?  What to do when an error is encountered. This is the optional parameter. Valid values are: YES and NO

In these examples I am not going to use the second parameter.

Right, let's jump right in and see what information we can retrieve about one of my libraries:

Friday, May 15, 2020

Today is 7.4 TR2 and 7.3 TR8 day

today 7.4 tr2 and 7.3 tr8 ptf are available

Today is the day the PTFs for the latest Technology Refreshes are available for download, IBM i 7.4 TR2 and 7.3 TR8.

The relevant PTFs are:

You can find links to what are in these Technology Refreshes here.

I am now going to generate a support ticket asking RZKH to load these TRs on their partitions. When they have I can start sharing how to use these enhancements.

Wednesday, May 13, 2020

Discovering the database's limits

using sql to see the maximum sizes of the ibm i database

Every so often I stumble across something in Db2 for i that I may not have a practical use for, but I find interesting. The SQL table SQL_SIZING is a good example of this. It contains one row for each limit of the IBM i Db2 database manager.

The table contains just four columns:

  1. SIZING_ID:  The ANSI, and ISO, number that defines the database sizing id
  2. SIZING_NAME:  The ANSI, and ISO, database sizing id name
  3. SUPPORTED_VALUE:  Sizing limit (value). If null the sizing limit is not applicable for IBM i
  4. COMMENTS:  Even though the sizing name is big enough to contain an adequate description of the sizing id this column gives a more detailed description

Wednesday, May 6, 2020

Adding RPG compiler parameters into the SQL Option statement

sql option compileopt in sql rpgle program

This feature was brought to my attention by someone who was converting a fixed format SQLRPGLE program to modern all free RPG code, and wondered the purpose of this code snippet.

03  C+             COMPILEOPT = 'DBGVIEW(*ALL)'

She understood the purpose of line 2. It was line 3 she had not seen before. Neither had I, and was intrigued as to what its purpose was and how else could it be used.

Tuesday, May 5, 2020

IBM performance FAQ update

The latest updated version of IBM's "IBM i on Power – Performance FAQ" was published at the beginning of this month, May 1.

While it includes the usual system information, in the latter half it contains advice for:

  • Db2 for i
  • RPG
  • Cobol
  • C
  • Java

To view the guide, which is a PDF, click here.

Wednesday, April 29, 2020

View the most recent Identity column value

in sql using identity_val_local to get last identity number generated

In my opinion identity columns are one of the cool things in Db2 for i. Basically an identity column is a column in a table that is updated by Db2 with the next sequential value. Depending upon how I code the identity column I can guarantee that they will never repeat, and provide a great unique key that can be used as a foreign key in another table. Most of time I do not have to bother with what the identity column's value as I know it will be unique and the only way I can reuse numbers is to reset the identity column.

I have to admit at times I am curious as to what the last value used in a table's identity column. I stumbled across a SQL function that will return to me the last used identity column for the table. But before I describe how to use this function I need a table and some data within it to show how to use the function.

My table is simple, it just contains two columns:

  1. Identity column
  2. Another column that will contain some data

Wednesday, April 22, 2020

Inserting one row with every columns' default value

sql insert 1 row with default values only

I wrote before how to SQL Insert a row into a table with the columns' defaults. Included within the last Technology Refreshes for IBM i 7.4 TR1 and 7.3 TR7 was an enhancement to the SQL Insert statement that would add a new row to the table with the columns' defaults without having to say "DEFAULT" for each column.

Before I show the enhanced Insert statement I need a table to insert the row into:

07   COLUMN3A DATE DEFAULT '01/01/1900',
09   COLUMN4A TIMESTAMP DEFAULT '0001-01-01-',
11  )

Tuesday, April 14, 2020

IBM i 7.4 TR2 and 7.3 TR8 announced

more information about ibmi 7.4 tr2 and 7.3 tr8

After last night's publication of the enhancements in the forthcoming Technical Refreshes, IBM i 7.4 TR2 and 7.3 TR2, this morning the announcement documents have been published.

From this we learn that the projected availability date for the PTFs for these TRs will be May 15, 2020.

Db2 enhancements in IBM i 7.4 TR2 and 7.3 TR8

ibmi 7.4 tr2 and 7.3 tr8 enhancements

The Db2 enhancements for the new Technical Refreshes of IBM i were published tonight, 7.3 TR8 and 7.4 TR2.

I have only had the opportunity for a quick glance at the pages for the two TRs and I find that there are some additions that have been made to 7.4, and not to 7.3:

  • Functional enhancement: Weakly typed distinct types
  • Functional enhancement: HASH_ROW built-in function

The same additions to the RPG language were made for both versions. These are:

Wednesday, April 8, 2020

System Reply List and using SQL to view the list

using system reply list

My original plan was to answer a question about the best way to retrieve the data from the system reply list on a particular IBM i partition. Before I wrote that post I asked my colleagues whether they knew about reply lists and had they used them? From their reactions I think I need to discuss what the system reply list is, and include the answer to my question within this post.

What the system reply list allows me to create a list of entries that will handle inquiry messages. On my travels I seen a few sites that use this feature, while most do not.

Reply lists have been around forever, all the way back to the days of the AS400. From what I remembered while creating the examples for this post I don't think anything has changed since I first played with them.

Saturday, April 4, 2020

Patron Saint of Computers day

In 1997 Pope John Paul II made Saint Isidore of Seville the patron saint of computers, computer programming, and the internet, and today is his feast day.

Saint Isidore was a sixth century scholar famed for his wisdom and writings at a critical time for the Catholic Church in Spain. He was bishop of Seville for 32 years, which this is used as part of his name to distinguish him from Saint Isidore the Farmer.

You can learn more about Saint Isidore of Seville by clicking here.

Do any other faiths have saints, spirits, or deities that are associated with computers? If you know of any please leave a comment below.

Wednesday, April 1, 2020

Playing with RPG's indicator array

changing indicators within the *in array

This all started with a conversation with a colleague. He asked about how he could change multiple RPG's numbered indicators without having to hard code the indicators. As the indicators are held in an array he was asking couldn't he just change a number of elements in one statement, rather than the individual indicators? He gave this example of fixed format RPG which changes indicators 10 - 13.

C                   MOVEA     '1001'        *IN(10)

When we place a debug breakpoint after this line we could see that indicators 10 – 13 contained the values he desired:

> EV *IN
*IN(10) = '1'
*IN(11) = '0'
*IN(12) = '0'
*IN(13) = '1'

Before I go any further I want to give my opinion about numbered indicators. I am not against indicators. I use them all the time in my work, and you have seen many examples of me using indicators in posts in this blog. But these are named indicators, not the numbered indicators those of us old enough to have programmed in RPG2 or RPG3 had to use. In RPG4 there is no excuse to use numbered indicators. They are confusing as a numbered indicator gives me no idea as to its function. Do you know what *IN80 is used for? If I use a named indicator instead then everyone knows its purpose.

Wednesday, March 25, 2020

Customizing email address for SNDSMTPEMM

giving each snsmtpemm user their own email address

I was asked is it possible for each person who sends email using the Send SMTP Email command, SNDSMTPEMM, to have their own unique email address. This command is used to send SMTP emails from IBM i. I am not going to go into details on how to use and configure your IBM i, I am going to refer you to the following posts:

When writing this post I had to use a Security Officer, *SECOFR, equivalent user profile to do what I needed to do. I do not have an System Operator, *SYSOPR, equivalent profile, therefore, I do not know if that gives you the necessary changes I will describe below.

Wednesday, March 18, 2020

Check if program is running interactive or batch

interactive or batch using sql rpg

In the past I wrote about a CL program that submit itself to batch, to do that the CL program must be able to determine if it running interactive or batch. The post describes how you can determine that in a CL program. But what about a RPG program or in SQL?

In this post I will give examples in both RPG and SQL how I can determine if the program is interactive or batch. This is not to replace my tried and trusted CL method. It is just an alternative that might prove useful in certain scenarios.


Wednesday, March 11, 2020

Variable length field in DDS file

This all started as a question from a member of another programming team at work. They had been asked to add a new field to an existing file. Most of the time this field would be empty, but it could contain up to 2,000 characters of data in some cases.

"It would be easy with a SQL table," they said "I could just make a new column VARCHAR (variable length character field) and make the default value null. But I don't know how to do that with a DDS field."

By using a variable length character field will mean that the field will not always be 2,000 characters. It will be as long as the data within it. And when there is no data in the field it will take up zero space. What a disk space savings.

Fortunately doing this with a DDS file is as easy as it is with a SQL table using the right keywords when defining the file. And it is just as easy to handle the variable length and null value in a RPG program. I decide to create my example file and the program on an IBM i partition that is running 7.2 just to show that there is nothing from a newer release need to do this.

Wednesday, March 4, 2020

Adding subtotals and a total to a SQL Select

adding subtotals and total to sql select

I have often looked at the results returned to me by a SQL statement and wondered to myself "Wouldn't it be nice if I could add a subtotal to these results".

Yesterday I decided to have a search using my favorite search engine to see if there is an easy way to do this. I could find examples in other flavors of SQL, but not in Db2 for i. Feeling piqued I decided to try one of these examples with one of my SQL Select statements, and was really pleased to find that what was given in the example also worked in Db2 for i too. Thank goodness for SQL interoperability and standardization.

I am not going to give the source code for the SQL DDL table I will be using in these examples. My table, TABLE_OF_THINGS, has three columns:

Monday, March 2, 2020

User Groups month is over

As the end of February has passed so has my IBM i User Groups month. During the month of February I reach out to you, the readers of this website, and ask for help to find new IBM i User Groups websites and social media accounts to add to my user groups page.

This year, with your help, I was able to add the following websites to the list:

  1. Common Slovensko (Slovakia)
  2. Georgia IBM Power Systems User Group Meetup which appears to have replaced the IBM i Tech Community of Atlanta
  3. Delaware Valley Computer User Group which is now found at a new URL

And the following social media accounts too:

Wednesday, February 26, 2020

Checking if Job has error, and sends an email

rpogram to monitor job for error message and send email

The germ for this post came from a question I was asked about if it is possible to monitor a job, and if it errors to send an email. In previous posts I have given examples of the pieces need to do this, and in this post I will just put it all together, like assembling pieces of Lego.

The one perquisite that is needed to make this work is that the name of the Job has to be unique, and always the same. Each time the job is submitted to batch it has to be submitted with the same unique name. In this example the job I want to monitor will always have the name: SIMON_JOB


If the name of this job is not unique then there is no way I can identify it, and I will be able to check its status.

The program is quite simple I just need to do two things:

Wednesday, February 19, 2020

Additions to Object Statistics table function

Changes to the OBJECT_STATISTICS table function

One of my favorite SQL table functions, OBJECT_STATISTICS, was introduced in IBM i 7.2 and it is something I use on a regular basis to identify unused objects that can be deleted. Over the last two releases of IBM i and numerous Technology Refreshes new columns have been added to this table function making it, in my opinion, better. In this post I am going to describe three of these enhancements, and how I can see them helping me.



One of the things I found using OBJECT_STATISTICS was certain object types' Last Used Timestamp was not updated when the object is used. This would fool me into thinking that it was an unused object that could be deleted as it was not being used. Then I would find all of the other objects of the same type were missing the Last Used Timestamp too. I ended up building a complicated select statement, referencing certain object types and conditioning the values in columns, to make an output table I could then use to find obsolete objects.

Wednesday, February 12, 2020

Calculating prime numbers in RPG

calculating prime numbers in rpg

The germ for this post is from a question that was asked in an interview. This person, a self confessed RPG "fresher", had been asked to write a "RPG free" program to calculate the prime numbers between 1 and 11. This request intrigued me, and made me think what kind of program the interviewer probably wanted, and what I would write if I need to use this at work.

Let me start with the basics, the definition of what is a prime number:

Any integer other than 0 or 1 that is not divisible without remainder by any other integers except 1 and the integer itself

Taken from the Merriam Webster dictionary.

What are the prime numbers in the desired range:

Thursday, February 6, 2020

Migration of developerWorks Technology Refresh information

The migration of content from IBM's developerWorks portal, which was sunset at the end of 2019, continued with Scott Forstie, IBM Db2 for i Business Architect, announcing the migration of two new sites:

As usual great information from IBM, and two sites I will be visiting on a regular basis.

Wednesday, February 5, 2020

Object owner information from SQL View

object_ownership sql view rather than wrkobjown

Tucked within the latest round of Technology Refreshes for IBM i for 7.4, TR1,and 7.3, TR7, was the addition of a SQL View, OBJECT_OWNERSHIP. This View returns the same information as the Work Object Owner command, WRKOBJOWN. I do not use WRKOBJOWN frequently, but I do before deleting an user profile to see which objects are owned by the profile, and an intermittent check on the production libraries to make sure that all of the objects are owned by the generic profile created for this purpose.

Alas, I am restricted on the IBM i partition I use to write these posts to only being able to see objects I own. Therefore, my examples will only show these objects.

The WRKOBJOWN is one of the easier commands to use. I can either just type it at a command line and press Enter, or press F4 to prompt to view the command's parameters.

Monday, February 3, 2020

Second annual IBM i User Group month

looking for information about ibm i user groups

If you are a regular readers of this blog you will know I am great fan of IBM i User Groups, and getting everyone involved with their local group. So much so I created a page of worldwide user groups, and added a link to it at the top of every page of this website.

Last year I started a campaign to update this list of User Groups. For the month of February I asked you, members of the IBM i community, to examine the list and if you knew of any groups that were not listed to let me know about them.

This year I am having the Second annual IBM i user group month. This month, February, I am asking you to do the same: review the User Groups page for any groups that are not listed. I am looking for any of the following information for any user groups: