Wednesday, August 16, 2017

Sometimes a List API is better than the SQL View

use list api and user space to get data about spool files

Sometimes it is just "better" to use a list API than it is to use the SQL View that "replaced" it. This week has given me an example of this. I was asked to create a program that would allow users to email themselves reports. I knew I would have to present the users with a list of spool files from which they could select the one they would want to email.

"No problem", I thought, "I will just use the Output Queue Entries SQL View."

I created a View over the OUTPUT_QUEUE_ENTIRES View, and then created a program using a multiple row fetch to get more than one row from the View. In the "test" IBM i partition the program ran a bit slow the first time I ran it, then ran faster each time after as the DB2 query engine created a temporary access path for me. Being a "test" partition there are not many output queues or spool files when compared to the "live" partition. I moved the objects I created to "live" and called the program there. It took more than 20 minutes to load the subfile with the list of user's spool files. With several hundred users and a very large number of spool files (many thousands, does anyone ever delete spool files?) in the "live" partition it was searching through all of them to find the few spool files belonging to me. The time taken made this method unacceptable. Back to the "drawing board".

Tuesday, August 15, 2017

We are not impostors

value of self taught programmers

Last week I found a wonderful article by Roger Pence in IBM Systems magazine.

It started with the title "Your are not an impostor", and continued with the subheading "Self-taught RPG programmers bring valuable skills to enterprise programming teams". As one of those self-taught programmers it caught my eye, and he made some good points about what would make me feel like an impostor, and what I can do to overcome it. It confirmed my opinion that there is a place in every organization for the self-taught and motivated programmers, alongside the formally educated graduates.

You can read the article on IBM Systems online magazine here.

If, like me, that site does not always load, I have placed a copy here (PDF file).

Alas, when I tried to go to the URL given for "The Impostor's Handbook: A primer for self-taught programmers", bit.ly/2ivK5lt, it went to a 404 page not found error page. If you have the correct URL for this page I would be grateful if you would post it in the comments section below.

Wednesday, August 9, 2017

Resetting the value of a variable

using reset operation code to set value back to initialization value

One of my colleagues was reading the post about initializing variables when defining them and asked a question that I had not considered when writing that post.

"What if I change the value in the variable," he asked, "can I change it back to its original value?"

Fortunately there is in RPG an operation code that will do this. The RESET operation resets the value of any variable or record format back to the value it was at the initialization phase of the program. It is not new operation, I have used it for as long as I have programmed on the AS400 - IBM i.

Thursday, August 3, 2017

Types of saves

types of saves

I have Steve Pitcher to thank for sharing this image so that I could post it here. It is from IBM's KnowledgeCenter showing the different types of saves that can be performed using the various save commands, on the right side, and the options on the SAVE menu, on the left.

list of the types of saves and what they save

Wednesday, August 2, 2017

Checking Software License expiration dates using SQL

checking softwatre license expiration dates using sql

Included in the latest Technical Refreshes for IBM i 7.3 and 7.2 is a SQL View and Procedure to help us monitor the licenses of the software on our IBM i.

IBM uses Software License Keys with certain IBM i products to ensure that I have paid the appropriate license fees to use these products on an IBM i. I know of several third party IBM i software vendors who also use this method to stop the unauthorized copying of their software to unlicensed servers. It is all standard stuff to prevent the distribution of unauthorized software.

Thursday, July 27, 2017

IBM i is more than an AS400

why ibm i  is not as400

I frequently get messages asking why does IBM call it IBM i when it is just an AS/400, or even System/38, by another name. Talking to other authors and IBM-ers I find that I am not alone to receive communications like this.

Steve Will, chief architect for IBM i, has felt compelled to dispel this fallacy. Earlier this month he made an online presentation, sponsored by HelpSystems, explaining how IBM i differs and is a lot more than AS/400. The video can be watched on the sponsor's website here.

The video lasts just over an hour, and, in my opinion, is worth watching to learn the evolution of the operating system to become the thoroughly modern IBM i.

Wednesday, July 26, 2017

Position cursor into field on screens

position cursor on a display file record format

The germ for this post's subject came from a two part question I was asked:

  1. How can I know which field the cursor is in when Enter is pressed?
  2. How can I position a cursor on some field without using indicators? No indicators are available in that very old screen.

Fortunately there are ways to do this by adding a few keywords and fields to the display file's DDS. In these examples I am going to use one display file with three screens, each showing a way to do what has been asked, and the RPG code needed too (and I am going to cheat on the second part).

Monday, July 24, 2017

DB2 name change

db2 family renamed

Last week at OCEAN User Group's Technical Conference I attended a couple of sessions by Scott Forstie, IBM's Business Architect for DB2 for i. I noticed in his presentation he had used "Db2" rather than "DB2".

When I asked him about this he explained that last week (Monday July 17, 2017) the DB2 family had been officially renamed "Db2". The Db2 family is:

  • Db2 for i
  • Db2 for z/OS (mainframe)
  • Db2 for Linux, Unix and Windows

I found that that Db2 page on IBM's website has already been changed, see here.

Now I need to remember to use the new name when writing about it in this blog.

Wednesday, July 19, 2017

Finding the number of times a character is in a field using SQL

using sql to count the number of times a character is in a field

I needed to determine, first, if a certain character was present in a file's field, and then how many times it occurs. Using RPG's file input I would not be able to do this without reading and checking every record's field. Could this be done easier in SQL? Yes.

I am going to give examples of various ways of being able to tell if a certain character is in a field in a file, using increasing complex methods I found. At first I will just give the SQL Select statement for each, and at the end the SQLRPGLE code I created to do this in a program.

Wednesday, July 12, 2017

Updating one row at a time using SQL

update current row/record using sql

I have been asked to write some more about using SQL embedded in RPG programs. One question I have been asked by several people is how to update a row from a file that had been previously Fetched, just like READ and UPDATE using RPG's native database access. I thought it would be useful to show examples using both RPG native database access and SQL.

First I need a file, as this is for example purposes it very simple, just two fields and one key field:

Wednesday, July 5, 2017

SQL null byte map as a 2-dimensional array

using a 2 dimensional array for sql null byte map in rpg

Nulls are something we are all going to have to get use to handling as we move from DDS files to SQL tables, or start using data from DB2 of i or other non-IBM i databases. Recently I was working on a project with a SQL Table with more than 300 columns. If there was no data in a particular field it was null. If you think about it null is a better alternative than blank or zero as those values could be valid values for the contents of that column.

I have written about handling null in SQL in an earlier post, but in this scenario the method I described to define the null byte map was not practical. I am not going to list 300+ subfields in a data structure array for the null indicators for the matching data structure array I would be moving the SQL Table's data into.

Thursday, June 29, 2017

RPGPGM.COM four years old

Wow! I find it hard to believe that I have been writing this blog for four years.

We have been fortunate to share this journey in an exciting time for the IBM i operating system. In all my years of working with this operating system, and is predecessors, there seems to be so much more functionality added every year than there was in the past. During these four years I have written almost 400 posts and pages, trying to share my enthusiasm for IBM i and what it can do.

Wednesday, June 28, 2017

Using relative record number in SQL

sql rrn function to retrieve relative record number from file

I have written about using the Relative Record Number, RRN, in RPG and said that I could not think of a reason why I would ever need to use RRN in one of my programs. Well, I have to take that statement back as I recently found a really good reason for using it. I needed a quick way to retrieve the last record from several different "flat files", and check if it started with the characters "END:". I could do it in RPG, but what is the fun with doing that when I could do the same using SQL?

What is my definition of what a "flat file" is? See here.

I am not describe these "flat files" as it is not really relevant to what I am going to show. I will say that I had a variety of these files from several sources, the layout of the contents is different, and the maximum record lengths are different to. They all have the following in common:

Wednesday, June 21, 2017

Happy birthday AS400

happy 29th birthday as400

Today is the 29th anniversary of the launch of the AS400. It was June 21, 1988, when IBM announced their latest midsize server. You can see a video recording of the announcement here.

At its launch the AS400 server and its OS400 operating were ground breaking.

Tuesday, June 20, 2017

IBM VP reaffirms commitment to RPG and IBM i

ibm vp reaffirms future of rpg

In my opinion it is always good when IBM publishes something about their future commitment to PowerSystems servers, IBM i operating system, and the RPG programming language. Steve Pitcher sent me a copy of a letter, from June 9 2017, by Steve Sibley, the Vice President of IBM Cognitive Systems, that hits this trifecta.

At the start of his letter Steve leaves us in no doubt IBM's regard for RPG:

Wednesday, June 14, 2017

SQL Views for Authorization Lists

views for authorization lists to view user authorities and objects belonging to them

Many companies that use IBM i secure their objects using Authorization Lists. The Authorization List is its own object type, *AUTL. If you have not used or heard of them before I found this good description of them in IBM's KnowledgeCenter.

Like a group profile, an authorization list allows you to group objects with similar security requirements and associate the group with a list of users and user authorities.

Authorization lists provide an efficient way to manage the authority to similar objects on the system and aid in the recovery of security information.

I am not going to discuss in this post how to create Authorization Lists, how to add objects to the lists, or anything similar. I am just going to describe two new SQL Views that were added in the latest IBM i Technical Refreshes, 7.3 TR2 and 7.2 TR6:

Tuesday, June 13, 2017

Using SQL to load data into a test instance

using 3 part sql path to update test files

Today's post is written by David Taylor.

From time to time, we need to load data from a production system to a development system. To make the process more complex, the data often resides in more than one file. Using SQL, we finally found a way to load the data into multiple files from the same cursor. We include in the SELECT clause any fields from any of the files in the data set to refine the cursor. Once we have the right data set, we can use the final version for the multiple file loads. Keep in mind, we need the data from all three files for the process test to work correctly. We need not just any set of records; we need the data that matches the relationships and limits defined in the cursor. In the samples each file ends with a letter. That same letter is the first character for the field names. For example, FILEA has fields like AKEYFLD1, AFIELD1, and so forth.

Wednesday, June 7, 2017

Repositioning file pointer after EOF in CL

stop end of file from happening in a cl program

I am always grateful for the feedback I receive from you, the readers of this web site. There are many times you mention ways of doing things that either did not think of or was unaware of. Today is a good example. I have Andrew Norton to thank for bringing to my attention a command that is a better solution for the scenario I described in the previous version of this post.

Those of us who have programmed in CL have found that when read a file when the end of file has been encountered I thought there was no way I could use the file again in the same CL program. In RPG I could use the Set Lower Limits operation, SETLL operation to reposition the file pointer. But in CL I was stuck.

Tuesday, June 6, 2017

Display files in CL

how to code dspf in clp or clle

I could give this a subtitle: "How to put a bad date in a date field", but this is not the reason of this post, just an accidental discovery. Having previously written about how to use database files in a CL program I have to mention using display files in CL.

You cannot use CL for anything as complex as a subfile. But you can for what I call "report submission screens", you know the type: a screen is presented to the user, they enter the selection criteria they desire and press Enter, and a program is submitted to batch to generate either a paper report or an email attachment. My example display file has two record formats, and is very simple:

Wednesday, May 31, 2017

Using OPNQRYF for file I/O in CL

opnqryf and cpyfrmqry examples

In my last post about the various way I could perform file I/O in CL code I deliberately omitted mention of the Open Query file command, OPNQRYF, as I wanted to cover it in its own post. I am sure everyone who has been programming on the IBM i or its predecessors have encountered and used this command.

I used it too, but not anymore. In the days before embedded SQL become efficient and fast within RPG programs I would often use OPNQRYF. This command allowed me to select fields and records, sort the data in a different order to the files' access paths, etc. thereby reducing the amount of checking and processing the following RPG program would need to perform. But it was always slow as it used the Classic Query Engine, CQE, to access the IBM i's database. In version 7.2 OPNQRYF was moved from using CQE to the faster and more efficient, SQL Query Engine, SQE. While this did improve its speed and performance it is still more efficient, and in my opinion easier and clearer, to do the equivalent using SQL embedded in RPG.

I am going to be giving examples of how to use OPNQRYF to do some of the things I talked about in yesterday's post.

Tuesday, May 30, 2017

Read, write, and update a file in CL

equivalent of rpg read, write, update, setll, chain in cl

Someone messaged me telling me that there were not, in their opinion, many good articles about CL programming, and asked if I would write some. Personally I think the majority of IBM i developers under utilize this language, keeping it just as a simple control language. I use CL programs and procedures a lot, and wrote about my thoughts on the need to modernize your CL in a post last year.

Where to start? Coincidentally I received an email from another asking about reading, writing, and updating a file just using CL. Let's start with data base access. In this post I will use the RPG file operation's name and then describe how to do the equivalent in CL.

Wednesday, May 24, 2017

Determining the screen size

determine screen size if it is *ds3 24x80 or *ds4 27x132

Most of us are aware that TN5250 sessions (displays) on the IBM i can be configured in two sizes:

  • 24 x 80 commonly known as *DS3
  • 27 x 132 commonly known as *DS4

Most of the time IBM i operating system handles this for us, only displaying "wide" screens when my session is *DS4 and the information to be displayed is wider than 80 columns.

Tuesday, May 23, 2017

My favorite CL shortcuts

using cl shortcuts rather than long hand code

If I am going to write and give examples of programming in CL I need to start by explaining some of the CL language shortcuts I use. Perhaps "shortcuts" are not the right word, maybe "shorthand". Oh well, I am sure you will work out what I mean as you read this.

I use these shortcuts extensively in my CL code as for a couple of reasons:

  1. I think it is easier for someone else to understand the code I have written.
  2. It is just quicker to write, the faster I can write code the more I can get done.

I am going to start with my favorite three.

Wednesday, May 17, 2017

Defining LDA in free format RPG

how to define the lda in a free format rpg program

It has been pointed out that this web site lacks a good description of how to retrieve the data from the Local Data Area, LDA, in the post about defining variables in free format RPG. I agree, which is why I have written this about retrieving and updating the LDA.

I am going assume that we all know what the LDA is. If you do not then you can read IBM's definition of what it is.

There are two common ways to bring in and update the data from the LDA in fixed format RPG.

Monday, May 15, 2017

Why is my RPG program not writing to the file?

rpg output buffer

I have been asked this question a lot recently:

When I am in debug my RPG program does not write to the file. What is wrong?

I have decided to write this post so I can, in the future, refer people here.

Let me give a very simple example RPG program so I can explain what is going on.

Wednesday, May 10, 2017

Using SQL for message queue data

new view for viewing messages in message queues

One of the new enhancements that came with the latest TRS for IBM i 7.3 and 7.2 is a new SQL View: MESSAGE_QUEUE_INFO. This view returns one line for each message in a message queue, in a similar manner to what is returned by the Display Messages command, DSPMSG.

This will allow me to have a way to quickly, and easily, search a message queue for a message. I must have *USE authority to the message queue, and *EXECUTE authority to the library it is in. One thing to remember is that message queues do get full, and when they do messages from them will be deleted. Also certain message queues are cleared when the IBM i partition is IPL-ed. If you are looking for a message that may have been issued sometime ago you might have better luck searching the History log via SQL.

Wednesday, May 3, 2017

Viewing object authority using a SQL view

getting authority data from object_privileges view rather than dspobjaut or edtobjaut

One of the new SQL Views added with the latest Technical Refreshes for IBM i, 7.3 TR2 and 7.2 TR6, is going to be very useful to me. Once a year, at audit time, I am asked to produce a list of all objects within the production library list that are not adequately secured. With the new Object Privileges SQL view I can generate all of the information I need in minutes, using a few SQL statements. This is going to save me a lot of time and effort.

The Object Privileges view, OBJECT_PRIVILEGES, like many of the new Views is to be found in the library QSYS2. The information it contains is the same as the information I can generate using in the Display Object Authority command, DSPOBJAUT. Rather than list all of the columns I will list them when I use them in various SQL statements. If you want to see a full list of all the columns visit the link I have provided a link to the IBM documentation for this View at the bottom of this post.

Tuesday, May 2, 2017

Second parameter added to SQL's LTRIM and RTRIM

2nd parameter added to sql trim built in functions rtrim and ltrim

As part of the goodies that came with the recent Technical Refreshes, TR, was the addition of a second parameter to SQL's Left trim, LTRIM, and Right trim, RTRIM, built in functions. The second parameter has the same functionality as it does in CL and RPG's trim built in functions, when used trimming particular character from either the left or right of the string.

The syntax of these SQL functions is remarkably similar to that of the CL built in function:

Wednesday, April 26, 2017

Using SQL to aggregate columns into one returned result

aggregating multiple values into one result set using sql

There are times when I have want an easy way to put values from a file, or table, "horizontally" into one column rather than vertically with more than one row.

"Vertical" "Horizontal"
TYPE        THING
ANIMAL     ELK
ANIMAL     HAWK
ANIMAL     MEERKAT
ANIMAL     ORCA
TYPE       THINGS
ANIMAL     ELK, HAWK, MEERKAT, ORCA, SEA L
FRUIT      APPLE, FUJI, BLACKBERRY, GRAPEF
VEGETABLE  SQUASH

Wednesday, April 19, 2017

Min and Max built in functions added to RPG

using the rpg min and max built in functions

A pair of introductions to RPG as part of IBM i 7.3 TR2, and 7.2 TR6, are the minimum and maximum built in functions, %MIN and %MAX. The maximum built in function returns the greatest value in a list of variables and/or values. The minimum returns the lowest value from a list of variables and/or values. Otherwise the way they are defined and the rules governing them are the same.

To use these functions there must be at least two values, and they must be of the same type. What I mean is that all of the values must be some type of numeric, character, date, time, or timestamp. I cannot mix types in the values. Pointers cannot be used.

They are so simple I can go straight to examples of using them. Let me start with a variety of numeric data types:

Thursday, April 13, 2017

Change to SQLRPGLE compile listings

type of rpg used in sqlrpgle compile listing changed to free format

Something I have noticed since the installation of IBM i 7.3 TR2 and 7.2 TR6 on the partitions I use, is a change to the compile listings of SQLRPGLE programs.

The process to create an object from a SQLRPGLE source member is a multi-step process:

  1. The syntax of the SQL within the source member is checked to ensure it is valid.

Wednesday, April 12, 2017

Nested data structures in RPG

nested data structures with other data structures

I am grateful to the folks at RZKH for applying the PTFs for the latest TR for IBM i 7.3, TR2. I started looking at the new features and functions added to RPG as part of this TR and decided to write about those first (Note: Same functions are included in 7.2 TR6 too). There were three additions, the one that first caught my eye was the ability to nest data structures.

Nested data structures are only available when defining data structures in free format RPG. If you are still using fixed format definitions this is another sign from IBM that it is time to move to free format RPG.

Inserting a nested data structure is pretty much as I would have expected, see below.

Tuesday, April 11, 2017

End of support for IBM i 7.1 announced

Steve Will, chief architect for the IBM i operating system, announced in a tweet today that the end of support, EOS, for release 7.1 will be on April 30, 2018.

Tweet from Steve Will: One of the FAQs I receive now has an official answer: IBMi 7.1 EOS 30 April 2018

Wednesday, April 5, 2017

How to write data to a multiple member file

handle multiple member files in rpg

Often I get the same question asked by several people, this time I have been asked the same question five times just this week, and I have been told that this is a question people have been asked in interviews:

How do you write data to different members in the same file using RPGLE?
The file has ten members, and you need to write data to the fifth and sixth members in one RPGLE program.

Tuesday, April 4, 2017

Saint Isidore of Seville day

Happy St Isidore of Seville day!

Saint Isidore is the Catholic patron saint for computers, programmers, and the internet. When things go wrong, very badly wrong, and everything else is failing it is always nice to know there is someone we can call on for divine intervention.

I am sure other denominations and faiths of saints, deities, etc we can ask for help from? If you know of any please post their details in the comment section below.

Monday, April 3, 2017

IBM i performance FAQ updated

ibmi performance faq updated april 3 2017

An updated version IBM's IBM I on Power – Performance FAQ was published today, called a "Spring refresh" by Steve Will the IBM i chief architect.

The What's new in the latest version?, section 1.3 on page 11, lists eight new questions added to the document.

If you are just a programmer type, not dealing with the finer points of tuning a Power server, there are sections to do with optimizing Cobol and RPG too:

  • Section 8.14 p57: How do I tune RPG/COBOL application performance and native I/O file access?
  • Section 9 p62-68: Database performance
  • Section 10 p69-70: RPG/Cobol native I/O

Good stuff to read, learn, and apply to your work.

The document is a PDF file and can be found on IBM's web site here.

Wednesday, March 29, 2017

Command syntax validation API

using qcmdchk and qmhrcvpm api

Like many posts in this blog, this started off as a simple explanation of one API, and quickly became a more complex scenario giving more useful information to you, the readers of this blog. It all started when I wrote about using the QCMDEXC SQL procedure. I received a message asking me why I had not used the QCMDCHK, Check Command Syntax, API before using QCMDEXC. This is a good point: How can I check if a CL string being received is valid before I pass it to either the QCMDEXC SQL function or API? And if it is not valid how can I return a useful error message?

When writing the example RPG code for this post I found that the most interesting information QCMDCHK generated about any error was written to the program message queue. This is information I want, so to get these diagnostic messages from the program message queue I would need to use another API, the Receive Program Message API, QMHRCVPM.

Wednesday, March 22, 2017

Determine the end of month using CL

get end of month date only using cl

The idea for this post came from several people who asked me the same question: "How to find the date of the end of the month? only using CL". When I quizzed them they explained that this was asked during an interview. If anyone asks me for help I always ask them how they would do it, and they all had pretty much the same solution, but struggled with how to determine if February should have 28 or 29 days. They asked how I would have done it. I came up with two solutions that I will share here.

The first version is very similar to what they can come up with. In this example the date is in *DMY format.

Tuesday, March 21, 2017

System, Partition, and Processor pool information all in one place

qlzarcapi for system, partition, server pool information

I am the first admit I do not know everything about IBM i as, in my opinion, there is just too much to know. Therefore, I am always grateful when people send me things they have found as there are many times they are new to me too. I have Laurent Gomes to thank for bringing to my attention the API QLZARCAPI.

I find it interesting that I cannot find any mention of it IBM's KnowledgeCenter, and only a passing reference in the documentation for the QMGTOOLS. What this API does is to display the system, partition, and processor pool information for the current IBM i partition.

Friday, March 17, 2017

TR2 and TR6 released today

ibmi 7.3 tr2 and 7.2 tr6 out now

Today is the day that the new Technology Refreshes, TRs, become available for IBM i 7.3 and 7.2. You can see what is included in them in an earlier post I wrote about 7.3 TR2 and 7.2 TR6.

TRs are available as PTFs that are downloaded from IBM's Support site:

New PowerSystems S812 out today

power8 s812 out now

The latest of the PowerSystems server is out today. The S812 is the lowest end Power8 server, for up to 25 users. Alas, it is an either IBM i or AIX scenario as the server can only host one partition. It is in P05 tier and will be marketed to new small size customers looking for a robust server, and as a replacement for the smaller Power6 and Power7 servers.

You will find more information about this new PowerSystems server:

Thursday, March 16, 2017

Interview with IBM Systems Magazine

ibm system magazine interview with rpgpgm.com author simon hutchinson

About a month ago I was contacted by Paul Tuohy and asked if I would consider being interviewed about this blog, RPGPGM.COM, for his iTalk with Tuohy podcast for the IBM Systems Magazine. I was very flattered that he considered me worthy, as he has interviewed members of IBM's IBM i and PowerSystems teams, and many of the IBM Champions.

Yesterday the interview was published on IBM Systems Magazine's website, with the title Simon Hutchinson on RPG Code and RPGPGM.com, in both text and audio. You have all read how I write, now you can hear what I sound like.

I just want to thank Paul for making the interview so comfortable, I feel I could have continued talking for a lot longer than the allotted time.

Wednesday, March 15, 2017

Difference between UNION and JOIN

difference between selection with join and select with union

After last week's post about using a Select statement with an UNION several people have asked me to clarify the differences between an Union and a Join.

I think using some simple graphics makes it a whole lot easier to explain. I have two files:

File 1
           
           
File 2
           
           

Monday, March 13, 2017

New in DB2 for i in forthcoming TRs from COMMON

Last Tuesday (March 7, 2017) the COMMON user group posted a video called What new in DB2 for i, presented by IBM's Scott Forstie, to their website.

In this 40 minute video Scott discuss the enhancements to DB2 for i (SQL) that are being delivered at part of IBM i 7.2 TR6 and 7.3 TR2, at the end of this month.

Fortunately this video is available to the general public on COMMON's website here.

Wednesday, March 8, 2017

Using Union to combine data from two files into one View

sql union clause to combine data from more than one file

When, in the ERP application my employer uses, an order is closed and posted to General Ledger the order's data is copied from the live file to the history file, and then deleted from the live file. This becomes an inconvenience when I am asked to produce a list of orders for a customer over a date range; the eligible data could be in both files. I have always wanted a way to link the live and history files together so that I can get the data from one place in the order I want.

Like most IBM i developers I want to use SQL to get data rather than use RPG's data access operation codes. I want to link these files together using SQL as I can get large chunks of data in one SQL operation faster than I can in RPG. Fortunately there is something in SQL to give me what I need, the UNION clause, that will allow me to join two or more selects statements together.

Wednesday, March 1, 2017

Using Get Diagnostic for SQL errors

get diagnostics for errors

I have already written about using DB2 for i's (SQL) GET DIAGNOSTICS to get the number of rows changed by a SQL statement. I can use the GET DIAGNOSTICS statement to get a lot more information about the SQL statement that has just occurred. In fact there are approximately 100 different types of information that can be retrieved using this statement, which is too much for one post. Therefore, I have decided to concentrate upon those keywords I think would be useful for diagnosing errors.

I have identified seven of keywords that I would find useful when trying to determine the cause of an error:

Thursday, February 23, 2017

PTF cover letters for 7.3 TR2 and 7.2 TR6

ptf cover letter for ibmi 7.3 tr2 7.2 tr6

The cover letters for the PTFs for IBM i 7.3 TR2 and 7.2 TR6 were released onto IBM's Support site yesterday. You can find them at:

The PTFs will be available for download on March 17, 2017.

For the details about what will be in these Technical Refreshes see IBM i 7.3 TR2 and 7.2 TR6 announced, which gives you an overview and has links to all the information from IBM.

Wednesday, February 22, 2017

Why I should be using the QCMDEXC SQL procedure

using sql procedure qcmdexc rather than api

My favorite API has to be QCMDEXC. I think it was the first API I used when I when I was making the transition from RPGII to RPGIII many, many years ago. I use it so often I think half the programs I write have it in them. QCMDEXC allows me to execute any CL command that does not return any values.

For several releases there has been a SQL procedure QCMDEXC, which does the same as the API. Then in IBM i 7.1 TR7 there was an update to the SQL procedure. You will find that I have used it in several examples in prior posts, but I have not given it its own post until now. I have to confess that I am so accustomed to using the API program version of QCMDEXC, I keep forgetting to use the SQL procedure.

Wednesday, February 15, 2017

Using API to test Help Panels

quhdsph api to diplay uim help module text

After publishing the post about how to create help text for display files using UIM several people messaged me that there is an IBM i API that can be used to test the help, without needing to add code to a display file. I have to admit I was unaware of the API, and am grateful to those who messaged me.

I decided to create a program to call the Display Help, QUHDSPH, API that way I can have a simple user interface to use, I only need to enter the variables I want and leave the rest with there defaults. I am going to show a very simple example here to illustrate how QUHDSPH can be used. As this is a simple example program a lot of the code I would add for myself, for validation etc. is not present.

For simplicity I am using a display file to enter the variables for the help module I want to see. There are other ways I could have done this, perhaps using a command instead, but I want this to be K.I.S.S compliant.