Padding columns using SQL

On cold morning in So Cal (we do get them) I was asked: "Is there any way to pad the values in FIELD1 in TESTFILE with zeroes?".

FIELD1 is a 20 long alphanumeric field in TESTFILE, despite its size only the first six characters are ever used.

....+....1....+....2 FIELD1 875 6891 3131 24187 40731 127963 684443

My mission was to add leading zeroes to the values that were not six long to make them that length.

Create SQL tables, views, and indexes with long names

I received a question asking me how to create a table with a long name that also had a good short name, and the long column names within the table would have meaningful short names too.

I can easily create a table with a long name and long column names:

01 CREATE TABLE MYLIB.THIS_IS_A_LONG_FILE_NAME ( 02 FIRST_LONG_FIELD_NAME CHAR(7), 03 SECOND_LONG_FIELD_NAME TIMESTAMP 04 ) ;

If I use PDM to look for the object I just generated it is not clear that this is the table from its generated short, or system, name.

Re-enable disabled IFS user id

When I am mapping an IFS folder to a Windows network drive I know that there are times where I will perform a typo on my password and the IBM i operating system will block me. This is where it gets interesting, I have disabled the user id I use trying to perform this connection, but I can still signon to the same IBM i.

When I create a user profile the command appears to create two:

1. What I call the "IBM i" that I use to signon to a 5250 ("green screen") session.
2. A NetServer user profile, that is used when I use the NetServer functions like mapping a Windows network drive to an IFS share.

Create and share IFS folder with Windows

In my last post I wrote about making it easier to copy IBM i files to an IFS folder. I have my personal IFS folder set up as a Microsoft Windows network drive, which allows me to open the mapped folder in Windows Explorer, and just drag-n-drop files from the mapped network drive into folders on my PC. Here I am going to describe how you can do the same.

I am going to use Client Access's Operations Navigator, I could use IBM Navigator for i instead, but I have found that not many sites have not enabled it. To be able to do what I am describing you are going to need the full version of Operation Navigator, and the appropriate authority to create folders and shares in the IBM i partition you use. If you do not have the full version of Operations Navigator and/or you do not have the necessary authorities feel free to share this with your System Operation or Tech Support person who can perform these steps for you.

This process can be divided into three steps:

Making copy a file to the IFS easier

Often I find it easier to analyze the contents of a file using Microsoft's Excel than using the IBM i based tools. I take the file, or table, and copy it to my folder in the IFS, and then copy it from there to my PC. A colleague asked how did I do this so quickly, and having showed him how I do, he recommended that this would be a good subject for a post in this blog.

I am surprised how few developers I talk to build their own set of programs to automate tasks they perform every day. Too many times I see these people type in command names, prompt with F4, and fill in the same parameters time and again to do the same task. I admit I don't want to do that, if I need to perform the same task more than just a few times I will create a program containing all the commands I need. Then I can call the program with just a few parameters, and the program will do everything. If I want to copy a file to the IFS I just need the file and library, the rest will always be the same.

Creating a screen with two side-by-side subfiles

I have written about subfiles, and even a screen with two subfiles on it. When I was asked about a screen with two subfiles, side-by-side, I was intrigued how to do it.

The first problem to overcome is that if I code two subfiles, one to occupy the left side of the screen and the other the right, whichever subfile's control record I write last will overlay the other.

After using Google I could find reference to an example of using DDS windows to contain the subfiles, but the links to the article only returned a "page not found". With that hint I created the following.

Let me start by saying that this display file is a "stripped down" version. I have removed a lot of the things I put in display files so not to clutter this example. Let me start with the file level keywords and a record format I have called HEADER.

Welcome to 2019

A new year has started, and I am always excited to look back on the old year and forward to what the new year will bring.

The past few years have been an exciting time for IBM i community. Just in the life of this blog I have seen RPG undergo a radical make over, and numerous wonderful things added to Db2 for i, or SQL.

You feel the same as the most popular articles from 2018 have been:

1. Creating a XML file
2. Processing simple XML using XML-INTO
3. Using RPG data structures with SQL insert and update
4. Displaying more than one subfile at a time
5. Copying any data to and from a file in the IFS

Have you liked others? If so which ones?

RPG400 manuals found

In my previous post I stated that IBM had removed the RPG400 user's guide and reference from the KnowledgeCenter.

Having performed a good deal of searching of the internet I have been able to find copies of both of these manuals in a dark corner of the IBM website. The manuals are from 2000. That may seem a long time ago for a "current" manual, but RPG3 has not been enhanced since RPG4 was introduced 23 years ago.

I have uploaded both of these PDF files to my Dropbox account, and you can reach them by using the following links:

• RPG user's guide:  view   download
• RPG400 reference:  view   download

Having given you these links I trust you will only refer to these manuals to remind yourself of some of the anachronisms of old RPG, rather than use to develop new code.

RPG400 manuals no longer available

As I have said in previous posts if you are programming only in RPG400, or RPG3, and not learning and using the latest version of RPG you are risking your future.

I found further proof yesterday browsing IBM's KnowledgeCenter looking for the latest RPG reference manual, when I noticed that the RPG400 (RPG3) manuals are no longer offered on line.

The following manuals are not included in the IBM i Information Center...

• RPG/400 Reference...
• RPG/400 User's Guide...

Searching strings using SQL

In a previous post I had written about using regular expressions to search for data within a string, but I did not mention the easier to use LOCATE_IN_STRING SQL scalar function. I believe this was introduced as part of IBM i Technical Refresh 2 for 7.3 and TR6 for 7.2.

The LOCATE_IN_STRING has four parameters:

1. The source string to be searched (mandatory)
2. The string used to search (mandatory)
3. Starting position (optional)
4. Instance, which number occurrence to find (optional)

I created a SQL DDL table, which I called TESTFILE, which I will be using in these examples. It contains one column, called DESCRIPTION, that is 40 long character, and the table contains just one row: