SQL and null

In my last post, Handling null in RPG, I gave examples of how to cope with null values in RPG. As more of us are working with SQL we are going to need to know how to cope with nulls within it too.

Before I start I need to thank Paul Tuohy as my examples of using the null byte maps are based upon a presentation he gave at the OCEAN Technical Conference, in 2015.

Let me start with the basics, below is a SQL table with five columns (fields). I could have used the GENERATE_SQL SQL procedure to create this table from the DDS file I used in my previous story, you can learn about this SQL procedure here.

Handling null in RPG

I am finding that I am encountering variables containing null more often as I interface data to the IBM i that originated from other software and databases. How to handle nulls is becoming one of the more popular questions I am asked.

What is null? Dictionary.com defines it as:

1. without value, effect, consequence, or significance.
2. being or amounting to nothing; nil; lacking; nonexistent.

Copying multiple spool files into one

Last week I received a request from my manager to merge three reports into one so that he can read it easily on his mobile phone. It has been a long time since I last did it, and when I asked my colleagues if they knew how to do it I received a lot of blank looks.

Let me start by saying I do not want to change the report programs, the printer files they produce are the right width to display on a mobile phone. Fortunately merging spool files is very simple, I can do it all with a few CL commands.

New RPG compiler directives /SET and /RESTORE

I am not sure how I missed this, two new compiler directives were added to RPG in IBM i 7.2, and I presume the equivalent 7.1 TRs: /SET and its opposite /RESTORE. I would have thought /RESET would have been a better choice than /RESTORE, but I am not IBM.

/SET will temporarily change, what I can only describe as, the definition of definitions. For example, if I want part of my program to use a different CCSID than the rest. Or perhaps I have a piece of code in a "copybook" (a source file member that is only ever /COPY-ed into another) that uses a different date and time format.

What can I change using the /SET:

A better way to read a file in the IFS with RPG

 

Update

Before using what is described below consider using SQL to read IFS files.

 

---

 

After publishing a post about reading an IFS file using RPG I received an interesting communication from Jeff Davis:

I would also say explore the fopen, fget and fclose. With these apis you don't have to scan for the crlf characters as they parse the data on those.

So I did what he suggested and investigated the C APIs he mentioned: fopen, fgets, and fclose. What I found convinced me that this is the better way to read an IFS file using RPG.

Defining files in subprocedures, part 2

In my last post, which you can see here, I wrote about defining files within subprocedures and I mentioned that you have to use a File data structure to read the record into. These are defined using the LIKEREC keyword when defining the data structure in the DCL-S (or Data Definition specification in fixed definitions). The LIKEREC defines which kinds of fields of the record are to be included in the data structure:

• LIKEREC(record_format:*INPUT) - input capable fields only
• LIKEREC(record_format:*OUTPUT) - output capable fields only
• LIKEREC(record_format:*ALL) - all fields

Defining files in subprocedures

This is a subject I have mentioned in passing in other posts, but I have not addressed this subject in its own post. I had a discussion with some folks on Facebook who wanted to know how to define and use a file in a subprocedure. It got difficult referring to one part of one post, and another part of another post, etc. I thought it would be a good idea to write a post just about this subject.

Prior to IBM i 6.1 it was not possible to include a file in a subprocedure. You had to define it in the main part of the program to be able to use it in any subprocedure, see the example below. The problem is what when you do this when you use the file in one subprocedure the file pointer is left in the position where it was last used, when you exit the subprocedure and enter another the file pointer remains in that position and can cause unexpected results. This can cause problems as I will show later in this post.

Removing duplicate records from a file

How to remove duplicate records from a file? is a question I have been asked by so many different people I feel that it must be on one of those interview questions lists. What I mean by duplicate records is where there is more than one record in a file were values in all are the same as another record. I do not mean records with duplicate keys, as that is whole other "can of worms".

In a well built database each "master" file or table should have a unique key, which prevents duplicate keys and records:

Steve Will talks of future TRs and releases

In the past few months I have written about sightings suggesting that a new release, IBM i 7.3, might be coming:

• Is IBM i 7.3 already out?
• More proof IBM i 7.3 is coming

Steve Will, IBM i's Chief Architect, wrote a post on the "You and i" blog titled "Signs and Portents for IBM i Announcements". In it he talked, in general terms, about Technology Refreshes and future releases:

Retrieving the job's library list using SQL

Before IBM i 7.2 if I wanted to retrieve the current job's library list I would have to use either the Retrieve Job Attributes command, RTVJOBA, or the Retrieve Job Information API, QUSRJOBI. As part of "librarian services" a new View was introduced, LIBRARY_LIST_INFO, and as its name suggests it allows me to retrieve information about my job's library list.

Like all the other new Views and Table Functions LIBRARY_LIST_INFO resides in the library QSYS2. It is possibly the smallest View I have written about as it has only six columns (fields):

Submit Job passing packed decimals

Every once in a while I get several people asking me the same question. As the examples accompanying tend to be the same I assume that this question has become part of some organization's interview question list. Recently it has been questions about using packed decimal variables with the Submit Job command, SBMJOB.

Most of us when we started on the IBM i (or one of its earlier forms) tried a SBMJOB with a packed decimal variable. The submitted job produced the following error message: "MCH1202: Decimal data error". After trying several different ways we admitted defeat, convert the packed decimal variable to character and used that in the SBMJOB.

Journal information using SQL

It is Monday morning and I receive an email from my superior asking about the journals on the production IBM i. How can there be so many? How many are active? How much space do they take up? How many are remote journals? I need a quick and easy way to get to information about the journals. There is a Retrieve Journal Information API I could use. Fortunately there is a SQL View, added in IBM i 7.2, that does the basically the same thing as the API, but it is a heck of a lot easier to get the information from.

Those of you who are regular readers of this blog will not be surprised to learn that the JOURNAL_INFO View is located in the library QSYS2. It contains a lot more information that I have been asked to provide. Rather than list all of the columns I am going to refer you to the relevant page in the IBM's KnowledgeCenter here.