Redpaper: Tools and Solutions for Modernizing

IBM has published a new Redpaper today called Tools and Solutions for Modernizing Your IBM i Applications, it is a companion to the earlier Redbook Modernize IBM i Applications from the Database up to the User Interface and Everything in Between, from July 2014.

It describes independent software vendor (ISV) and Business Partner tools that can be used to modernize your IBM i applications.

It includes the following chapters:

1. Mobile, web, and client solutions
2. Database modernization tools
3. Security
4. Tools for understanding and modernizing RPG and COBOL
5. Source control and project management

You can download it, as a PDF, here.

You can find a link to its companion Redbook here.

How to map an IFS folder as a Windows share folder

After reading the post Easy way to convert CSV file to DDS file Martin Coates emailed me with the following question:

How do you map a folder in the IFS and share is on the pc? I am an As400 “purist” but more and more I find programming requests overflowing into the pc domain.

In this post I will discuss how to create a folder in the IFS, and then how to share it as a folder that can be accessed by a Windows PC. If you already know how to create folders in the IFS feel free to skip to the second part.

• How to create a folder in the IFS
• How to share the IFS folder
• How to map the folder to the Windows PC

Use QTEMP for your work files

This post is inspired by a situation that I encountered a couple of weeks ago that left me flabbergasted. It started with a phone call from a department head complaining that another department was eating into his department's time to run a report. At first I was confused, and after talking to three department heads I discovered that each one has a time they can run this report. Department A can run the report with any selection criteria they desire from 8:00 – 8:59 AM, Department B can do the same from 9:00 – 9:59 AM, and Department C from 10:00 – 10:59 AM. Anyone can run the report in the afternoon, but only after calling the other departments to make sure they are not running the report at the same time.

Why was this an issue? In this job the programmer who had created it used a work file that was in a production library. Therefore, only one person could run the report at a time to ensure that only one department's data was included in the report. If two people ran the report at the same time the work file could contain mixed data from both jobs. I also found that when the job completed the work file was not cleared, still containing all of the data from the last run, wasting disk space.

The programmer who created these programs has retired so I was not able to ask the question I wanted to: Why?

Looking at library list for the jobs from this application I found that QTEMP was in the library below all of the production libraries. Could this be the reason why?

Accessing multiple member files in SQL

I was recently asked the following question, via Facebook:

How we can read a particular member of physical file thru SQL EXEC in RPGLESQL program ?

Others suggested using "Override with Data Base File" (OVRDBF) command, which could be used. But there is a way to do this just in SQL.

Row and Column Access Contol Redbook now available

RCAC stands for "Row and Column Access Control", which is one of the features introduced in release 7.2 to DB2 for i (SQL).

Since the release announcement I have seen many questions asking what it is and how to implement it. IBM must have seen the same and have created a new Redbook called: Row and Column Access Support in DB2 for IBM i. You can download it here.

To accompany the release of the Redbook is this brief video of Mike Cain, one of the members of the DB2 for i Center of Excellence team, giving an overview of RCAC.

FTP i-to-i part 3

In the past two posts about this subject I discussed how to manually copy files from one IBM i to another using FTP, File Transfer Protocol. In this post I am going to demonstrate how to create a program to start FTP, execute FTP subcommands, and then produce a printed log of what happened.

The program I will show is very simple and can be used for any types FTP subcommands. In this post I will use the example I discussed in Part 2, to copy a number of files from one library on QSRV1 to another library on QSRV2, and then call the program on QSRV2 to process the copied files.

FTP i-to-i part 2

Having gone through a lot of the basics of using FTP, File Transfer Protocol, on an IBM i in the post FTP i-to-i part 1, in this post I will describe how I can transfer physical files, using FTP, to another instance of IBM i, whether it be another partition or another server.

In this scenario I have a job on QSRV1 that generates physical files that need to be transferred to QSRV2 to be processed. If this was a real job I would automate the FTP transfer, I will explain how to do it in Part 3. But in this post I am going describe how I would do it manually, one FTP subcommand at a time.

FTP i-to-i part 1

FTP, File Transfer Protocol, is probably the most widely used way to transfer objects between computers. It should come as no surprise to you that FTP has been available since the AS400 era.

I work in an environment with 11 IBM i partitions and servers, and I can spend a lot of time moving objects between. To move objects between partitions on the same server I use the SAVRSTOBJ and SAVRSTLIB commands. To move objects from one server to another, especially if they are in different geographical locations, I use FTP.

Why FTP rather than SNDNETF or SAVRSTOBJ? In my experience when moving very large amounts of data between servers it can be several hours quicker to FTP the file rather than use the other methods.

This post is going to first of several posts explaining how to FTP a file or files from one IBM i server to another. This method is not secure and I only do this within my company's network or while part of a VPN. I do not recommend doing this in an unsecure environment, i.e. over the internet while not being part of a VPN, as it can be "sniffed" and the data compromised. If you do want to send data via an unsecure environment then use SFTP, Secure FTP.

Retrieve file's Column Headings using API

In May I posted two examples of how to retrieve the Column Headings for fields in files to be used as the column headings in a CSV file in the IFS. The first example, Adding Column Headings to a file in the IFS, I used DSPFFD. I was sent an alternative method that I gave in a later post, Adding Column Headings to a file in the IFS, continued, which used SQL to extract the information from the SYSCOLUMNS table.

Shortly after the second post I received a message from David Gomes. In it he explained a third method:

Whenever there is an API available to get the data, you should be using that and not running a CL command to produce a output file to read. In this situation I would recommend using the List Fields API (QUSLFLD). This will allow you to retrieve the Field name, Column or Text descriptions for the field. Using the X-Ref tables like SYSCOLUMNS is useless if the file is in QTEMP.

As I have explained how to use a List API, like QUSLFLD, that outputs to a User Space in the post User Space introduction in this post I am just going to explain how to use QUSLFLD.

User Spaces introduction continued

After publishing yesterday's post User Spaces introduction Sam posted a comment:

It seems you forgot one important thing: Change User Space Attribute API
Automatic extendibility. Whether or not the user space is automatically extended by the system when the end of the space is encountered.
0 The user space is not automatically extendible.
1 The user space is automatically extendible.

While I write each post I test the code I give as examples. I did not encounter an error while testing that the User Space was full. But that could be that I did not fill the 128k size I made the User Space in my example.

Perhaps I needed to use a file with more fields? Or what would happen if I created the User Space with a size of 1 byte?

User Spaces introduction

User Space is used by many IBM APIs to hold the generated results. It is similar to a data area, but can be a lot larger and will expand in size as more data is added to it. What I consider to be a drawback for using them is that they can only be accessed by API. As I want to talk about some APIs in future posts I thought it would be a good idea to give this introduction to using User Spaces first.

In this example I will be using the following User Space APIs to:

• QUSCRTUS – Create user space
• QUSDLTUS – Delete user space
• QUSPTRUS – Get pointer to user space

In this example I am going to use the List Fields API, QUSLFLD, to generate a list of the fields in a file. I just chose this API at almost random to show how to use the APIs in a very simple manner to demonstrate how they work. You will probably want to add more complicated code to your use of these APIs to cope with errors, etc.

User Group: NiSUG becomes i-UG

The United Kingdom based user group the National iSystems User Group, NiSUG, changed their name to match the IBM i operating system and become the i-UG.

They have also created a new web site at i-ug.co.uk, which I have replaced their old NiSUG web site with on the IBM i user group page. There is a link to the user group page at the top of every page of this web site.

Feel free to check out the user groups listed, if you know of one that is not on the list please use the Contact form to send me its details. If you find one near you I encourage you to make contact with them and attend their meetings.