Wednesday, January 23, 2019

Padding columns using SQL

padding fields 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.

Wednesday, January 16, 2019

Create SQL tables, views, and indexes with long names

create long and short names for sql tables, indexes, and views and their columns

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.

Thursday, January 10, 2019

Re-enable disabled IFS user id

enable disabled netserver ifs user id or profile

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.

Wednesday, January 9, 2019

Create and share IFS folder with Windows

creating ifs folder and sharing to microsoft 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:

Tuesday, January 8, 2019

Making copy a file to the IFS easier

automate copy of file to ifs

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.

Wednesday, January 2, 2019

Creating a screen with two side-by-side subfiles

2 subfiles next to each other

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.

Tuesday, January 1, 2019

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?