Using CL to get records from a file for display

I have been receiving a lot of messages asking for more examples using the CL programming language. For those of you who have been asking I hope you find this useful.

A few weeks ago I received a request for help from a reader, just a beginner in the IBM i world, with an assignment he had to accomplish just using CL. He needed to write a program, or programs, that would allow the entry an employee id, and the program would retrieve information from the employee file and pass it to the display file for display. For anyone with a little RPG experience this is a very simple task, and we could all write a RPG program to do this in a few minutes. Many IBM i programmers balk to do anything like this in CL, but programming is programming, and the same steps you would perform in RPG can be duplicated in CL.

Using command to zip and unzip files

In the past I have written about zipping files in the IFS using Java jar and APIs, this week a colleague showed me a simpler way to do it using a couple of IBM i commands.

He explained that these commands had been introduced in IBM i 7.2, and can zip and unzip files in the IFS as well as files in, what I call, the native IBM i environment. With these commands the zipped/compressed files are called "archive files", therefore, IBM named them:

• CPYTOARCF:  Copy to archive file
• CPYFRMARCF:  Copy from archive file

 

Converting spool file data into data file

I was surprised that I was asked this question, but as I have been asked six times in the last week it must be something that is important for people to find an answer for. Copying data from a spool file to a data file with separate fields is not an efficient way to get data. If you are considering using this method for capturing system information, spool files, active job info, etc, then you should be using the Views, Tables, etc that IBM has been creating for us to use. You can search this web site to see if I have written about getting to the information you desire using SQL. Trust me it is easier that what I am describing below.

In this example I am going to use the Work Output Queue command, WRKOUTQ, to generate a list of spool files, and I will be writing that data to a file. If I was doing this in the real world I would using the SQL and the method described in Output queue entries information via SQL. Now I have vented my feelings on this, let me proceed.

I want to know the following information about spool files in the QEZJOBLOG output queue:

Using new SQL built in function to convert character to number

Included in the technical refreshes for IBM i 7.3 TR5 and 7.2 TR9 was a new Db2 for i built in function to convert character strings into numbers.

Why is this needed when I can just CAST the one type of a column to another?

01 SELECT COLUMN1, 02 CAST(COLUMN1 AS DECIMAL(10,2)) 03 FROM QTEMP.TESTFILE

Numbers are problematic as I can make character representations of them in many different ways. I created a few examples of character representations of numbers, and then used the above SQL statement to display them.