Wednesday, November 14, 2018

Converting spool file data into data file

copy spool file data to physical 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:

Wednesday, November 7, 2018

Using new SQL built in function to convert character to number

using sql built in funtion to_number decfloat_format to convert alphanumeric number to decimal 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.

Wednesday, October 31, 2018

Easiest way to search job descriptions

easy way to get information from job description using sql view

I have written about getting the library list from a job description using the QWDRJOBD API. An API is fine for doing things for one job description, but when I want to search all job descriptions that, for example, contain a library in their library list it is not easy to do with an API. A SQL view would be ideal for this.

IBM has come to rescue and I can now retrieve data from job descriptions using the SQL view JOB_DESCRIPTION_INFO. It is listed as one of the enhancements added with the latest round of IBM i Technical Refreshes, 7.3 TR5 and 7.2 TR9, but I have found it on a instance of IBM i that has not had the latest TR applied.

The view contains every column you need to know about a job description. I am not going to list them in this post, as I want to show examples of the kinds of information I have been asked from get from job descriptions in the past. If you want to see all the columns that are available there is a link at the bottom of this post to the IBM KnowledgeCenter page describing this view.

Wednesday, October 24, 2018

Extracting parts of date and time using a SQL function

enhanced extract for getting information from dates, times, timestamps

Another of the enhancements with the latest round of Technical Refreshes, TR5 for IBM i 7.3 and TR9 for 7.2, is to the Db2 for i Extract function. This function will retrieve parts of dates and times from date, time, and timestamp variables. Prior to these TRs I could extract just basic information from the date (year, month, day) or time (hour, minute, second).

The enhancements allows me to retrieve a whole lot more information than I will ever need to know about a date or time.

Let me start with a SQL DDL table I built to contain the data for my examples:

Wednesday, October 17, 2018

SQL NOW and playing with timestamps

using sql to get the timestamp

This post started off as one thing and quickly morphed into something more. I was going through all of the additions and changes in the new Technical Refreshes for IBM i 7.3 and started playing with the Db2 for i (SQL) NOW built in function.

NOW returns the current timestamp, and it has been around since at least 7.1. The latest TR allows me to give the decimal precision (fraction) of the seconds, for an example see below.

  2018-10-17-18.26.32.206964

As I said now I can give NOW the decimal precision I want from zero to twelve.

Wednesday, October 10, 2018

Removing multiple alpha characters using SQL

using sql translate function to translate characters

I needed to remove alphabetic characters (A - Z) from a field in a file, and left justify the remaining numeric characters. These alpha characters could be before and/or after any numbers contained within the string. As usual I need data in a test file, TESTFILE, that I can play with to find a way to do what I want.

In these examples, as the subject I have only bothered with two records/rows in my table/file. Why have more when I know what works in these examples will work in any scenario.

COLUMN
abc1234defghijklmnop
aaBbcCDd1mmg2k3LLkkH

The problem is that I want to replace any alpha character, no matter where in the string and what order they come in.

Friday, October 5, 2018

What's this thing called IBM i

A video from IBM Champion Trevor Perry explaining what IBM i is, and what is its future.

Video published April 20, 2017

Wednesday, October 3, 2018

Creating uniqueness using rowid

rowid column in sql table to ensure record uniqueness

The Db2 for i manual offers using a row id column, ROWID, as an alternative to an Identity column for ensuring table row uniqueness. While I wrote about using a ROWID SQL data type in RPG, I did not describe how the ROWID works when defined as a column in a table.

ROWID can only be used in SQL DDL tables, I cannot add it to a DDS file, using ALTER TABLE. It is defined in a similar manner to an Identity column where I say that the columns contents are generated by Db2 for i automatically.

For example, here is the DDL table I will be using:

01  CREATE TABLE MYLIB.TESTTABLE
02  (COLUMN1 CHAR(3) NOT NULL,
03   COLUMN2 CHAR(10),
04   COLUMN3 ROWID GENERATED ALWAYS IMPLICITLY HIDDEN
05  )

Wednesday, September 26, 2018

SQL type variables in RPG

sqltype variables used when dcl-s in rpg

I have written about defining RPG variables defined as SQL types in past posts, which made me curious about all there are. Having searched in IBM's Knowledge Center I was disappointed to find that there was no one page with all of this information upon. Which gave me idea to write this, a list of all SQL types available.

If I am defining variables as SQL types my source needs to be compiled as a SQLRPGLE, as the RPG SQL precompiler converts these SQL data types into more familiar RPG equivalents.

The syntax for these variable definitions is simple, it is just:

Thursday, September 20, 2018

Closing all the files with one operation in RPG

closing multiple files with one rpg close operation code

There are times I stumble across things in the IBM manuals I felt I should have known. One of these is the ability to close all the files in a program with just one close statement. I have no idea how old this ability is, but it is relevant if you are programming your RPG in a modern manner.

Before main procedures were introduced to the RPG world, our programs were simple. We defined our files, and knew that they would close when the program ended.

01  **free
02  ctl-opt option(*nodebugio:*srcstmt:*nounref) ;

03  dcl-f PFILE1 ;
04  dcl-f DSPFILE workstn ;
05  dcl-f PRTFILE printer ;

06  *inlr = *on ;

Wednesday, September 19, 2018

Getting data for my auditors is simple using SQL

easy way to get user info using sql

It is that time of the year when the auditors start asking me for system information about the IBM i I am responsible for. The reports they want from me today are:

  1. Users who have not signed onto the IBM i for over a year.
  2. Users who have a user profile, but have never signed onto the IBM i.

It is also a good opportunity to show how some of the things I have written about in the past come together to make my job a lot easier.

A colleague, who is responsible for a different IBM i partition, asked me if there was an easy way to get this information?

Tuesday, September 18, 2018

Get a specific number row using SQL

getting the 98th row from a file using sql

This is going to be a quickie. I was asked:

How can I get the 98th record from a file using SQL?

Fortunately this very simple, but first let me go through setting up my example.

I created a Db2 for i table to use in this example. I could have used a DDS file just as well. But this gives me an excuse to use one of what I consider one of the cool features of Db2 for i tables, identity columns.

If I am using a table with an identity column I don't have to increment some value in a field to keep a unique field in a file or table. The value for an identity columns are auto generated by Db2 for i, so all I have to do is just define them in my table. My example table just contains an identity column.

Friday, September 14, 2018

IBM i 7.3 TR5 and 7.2 TR9 now released

ibm i 7.3 tr5 7.2 tr9 out now

Today is an exciting day as the latest Technical Refreshes for the currently supported releases of the IBM i are available to download as PTFs:

  • IBM i 7.3 TR5
  • IBM i 7.2 TR9

I am not going to repeat what these TRs include, I am going to give you a]the link to the post I wrote when they were announced in August, see IBM i 7.3 TR5 and 7.2 TR9 announced.

The PTFs you will need to download and apply to install the new TR on your IBM i are:

Wednesday, September 12, 2018

Generic program to use SQL count for any file

program to get count of records from any file using sql

The germ for this post came from a question I found in an IBM i Facebook group. How could this person write a program where he would pass the name of any file to SQL and get a count of records in that file. I gave my answer to that question, but the more I pondered I came up with what I consider to be a better solution. Which is what I am going to describe in this post.

I have written about creating and executing SQL statements contained in program variables before, and this just builds upon that.

Fortunately the SQL syntax to count the number of records is the same no matter what file or table I use:

SELECT COUNT(*) FROM some_table
 WHERE some_column(s) = some_value(s)

Wednesday, September 5, 2018

Using SQL to determine how many records end with...

determine what alpha fields end with

I was asked an intriguing question that I thought it would make a good post for this blog:

Using SQL, how would I get a count of all records in a file where the value in an alphanumeric field ends with 18?

Rather than show just the finished SQL statement I am going to show how, and why, I built each part of the select statement.

Let me start with my test file. It will come as no surprise to regular readers of this blog that it is called TESTFILE, and is in the library QTEMP. The file contains one alphanumeric/character field, CHARFIELD, its length is irrelevant. Its contents looks like:

Wednesday, August 29, 2018

Getting the System Name using SQL

get as400 system name using only sql

What appeared at first look to be a simple challenge given on Twitter:

How would you get the system name of a partition via a SQL statement?

In a CL program this is simple as I can retrieve the system name using the Retrieve Network Attributes command, RTVNETA.

03  RTVNETA SYSNAME(&SYSNAME)

So how to get the same information using SQL?

Wednesday, August 22, 2018

Using Environmental Variables

environment values are more than just widening the strdbg window

This all started with what was going to be a very short post about using an Environment Variable to set the width of your debug screen. But I became curious and by the time I finished playing with them this grew into how to create your own Environment Variables, and use them.

Let me start with the question: What is an Environment Variable?

Environment Variables come in two types:

  1. System – these Environment Variables are stored in the global environment space and are available to all jobs running on this IBM i system, and are persistent even when the system is IPL-ed.
  2. Job – the variables are outside the program's space, and are job specific, when the job ends they are automatically deleted.

Wednesday, August 15, 2018

Getting data from XML file directly into data structure array

xml straight into file

I have written before about retrieving data from a XML file in the IFS. In that example I took that data from the file into a XML column in a DDL (SQL) table and then processed it from there. Since I published the example I have received two messages, one from Birgitta Hauser and another from Jan Koefoed-Nielsen, giving me examples of how to retrieve the data from the file and format it into columns, not using a DDL table as an interim step.

In this example I will not be directly outputting the information from the IFS XML file directly into an output file or table in IBM i. My experience of receiving XML files makes me want to validate what I am sent, before I start updating production files. A comma in a number, currency symbols, characters in what should be numeric value, and untranslatable characters should all be handled before updating any file. To this end I will be retrieving the data from the XML file and putting it into a data structure array. I can then "read" the array and perform any validations I want.

Monday, August 13, 2018

AS400 to IBM i: And we're just getting started

Video featuring Steve Will, chief architect of IBM i, and Alison Butterill, product offering manager for IBM i, showing ways IBM i clients have innovated over the past 30 years.

Runs just over 1 hour.

Hosted by HelpSystems, recorded July 18, 2018.

Wednesday, August 8, 2018

Using reference fields

using reference fields in dds, rpg, sql

When I first started programming on the AS400 the biggest improvement I encountered, compared to the System/36, was the external database. No longer did I have to have file specifications in my RPG programs, and I now had the ability to define fields by referencing another. Over the years I have used the referencing ability whenever I can had to define fields in files or variables in my RPG programs. By using reference fields makes database changes easier as a field's definition is changed in one place, then by recompiling all of the objects with the referencing, the new objects all have the new definition of the field in them.

In the past fortnight I have been asked several times about using reference fields, and in my experience there are many more who would like to ask the same question but feel intimated to do so. Therefore, I am writing this post to give examples of how I use them and why I do things the way I do.

All well-built databases have a data dictionary that defines the types of all the fields, or columns, within it. On AS400 and IBM i many of us achieved something similar using a reference file. This file contains the definition of every type of field that could be contained in any of the other files in the application.

Tuesday, August 7, 2018

IBM i 7.3 TR5 and 7.2 TR9 announced

ibmi 7.3 tr5 7.2 tr9 announced

The awaited announcement of new Technical Refreshes for IBM i 7.3, TR5, and 7.2, TR9, has been made. So what's new?

On the hardware side is the announcement of the Power9 chip based IBM Power E980 model 9080-M9S server.

I am not going to list all the changes on the software side just those that catch my eye at first glance.

Wednesday, August 1, 2018

Copying any data to and from a file in the IFS

using sql to write to a text ifs file

In previous posts I have shown how to write SQL data to a file in the IFS and then how to retrieve the XML data from an IFS file, but what about just plain old text?

In this post I am going to keep the example as simple as possible so that you can see how easy this is. I am going to write three strings of data, "records", to an IFS file, retrieve the data from the file, then move each the "record" into an array. Some of what I am describing here I have also mentioned in my earlier posts, but I think it is valuable to have it in here too.

My example program is only 32 lines, which goes to show easy this is. All the hard work is performed by Db2 for i.

Wednesday, July 25, 2018

Using SQL UDF with RPG

using values from sql udf in rpg program

This short post comes from a discussion I was having with two other IBM i developers at my place of work. I had shown them a number of SQL User Defined Functions, UDF, I had created to make my SQL statements easier.

"That's fine," started one of the developers, "but how does that help me in RPG. Won't I have to duplicate the procedures so I can call them in RPG?"

"No", I replied, "you can call them using SQL".

At first they were skeptical. Having shown them how easy it was to do they were sold.

Wednesday, July 18, 2018

Getting data from a XML file using SQL

retrieve xml data from file in ifs

A couple of months ago I write a post about creating XML from an IBM i hosted file and write it to a file in the IFS. It was obvious to me that I would have to write this post, describing how to get data from the XML file using SQL.

I could extract the data from the XML file in the IFS using the RPG operation code XML-INTO, but I want to use SQL to do this.

Where to start? I guess with the XML data. The XML file, xmlfile.xml, is in my folder, MyFolder, in the IFS. It contains the same data I used in the post about writing to the XML file. In the file the data is not formatted, it is just one long string of data.

Wednesday, July 11, 2018

Using an output queue to send spool files to another partition

create remote outq to another as400

The germ of the idea for this post came from a conversation I was having with two other programmers I work with. The conversation became finding an easy way to transfer spool files from a development IBM i partition to a production partition.

Where I work there are multiple partitions on one PowerSystems server. These partitions are used by various subsidiary companies in different parts of the USA, and three locations in Europe. If a modification is made to a spool file layout, for example: invoice, how do I show a user in Germany or Illinois what this new layout looks like when I am in California? The development partition does not connect to all the printers used, as we do not want test data printing on a "live" printer when testing. In my experience if I copy the spool file to a PDF the user is not convinced. The only way they will be convinced that the change works is for it to print on their printer.

Wednesday, July 4, 2018

Securing DDM files

making ddm file connections secure

Using DDM files is a very easy way to access data from another IBM i. I frequently use them to pass data from one IBM i partition to another and back.

All I need to do is to create the DDM file with the following command and be able to access the data in the file SOMEFILE in the library SOMEIB in the IBM i partition RMTSYS.

                            Create DDM File (CRTDDMF)

Type choices, press Enter.

DDM file . . . . . . > DDMFILE  
  Library  . . . . . >   QTEMP     
Remote file:
  File . . . . . . . > SOMEFILE  
    Library  . . . . >   SOMELIB   


Remote location:
  Name or address  . > RMTSYS                                  


Type . . . . . . . . . *SNA     *SNA, *IP
  

Wednesday, June 27, 2018

Different types of numbers in files and tables

different types of numbers that can be used when defining dds files and sql tables

In a previous post I wrote about the different types of numbers that could be used in RPG. Alas, we have been spoiled with RPG as there are less types to choose from in when defining numbers fields in DDS files and columns (SQL) DDL tables.

 

DDS files

DDS files come in different forms, and they all have their own rules as to the types of numbers are valid. I am only going cover the major types, which in my opinion are:

  1. Physical files
  2. Display files
  3. Printer files

Saturday, June 23, 2018

OCEAN Technical Conference 2018

I have just paid my registration for this year's Ocean user group Technical Conference, Ocean TechCon18. This year the conference runs from Thursday July 19 – Saturday July 21.

This is the best IBM i event on the west side of the USA. I am always amazed at the speakers (PDF), and I always leave with my head full of new ideas to use at work.

The venue is the National University in Costa Mesa, CA, which is just off I-405 in Orange County. For a map of the location and to get directions click here. If, like me, you live too far to commute there are plenty of hotels in the vicinity to fit most people's budgets.

If you live in So Cal, in my opinion, this is event is a must if you are serious about keeping your skills up to date. It is also great place to meet many other like-minded people, and a great place to share ideas with your peers.

I will be there on Friday July 20, and if you see me come over and say "Hi".

Thursday, June 21, 2018

30th anniversary of AS400 launch

30th anniversary of as400

There has been a lot of fanfare about this year being the 30th anniversary of the launch of the AS400, the esteemed ancestor of the IBM i operating system. Many others have written tomes about what this anniversary means to them, and I thought I would add a few thoughts of my own.

On June 21 1988 the server and operating system that had be known by insiders as Silverlake was launched to the world as the AS400. It was the brain child of Dr Frank Soltis to create a system for midsized businesses.

Wednesday, June 20, 2018

Different types of numbers in RPG

using different types of number fields in rpg

For those of you like me who have been programming in IBM i and its predecessors for some time this might seem a simple subject matter. But I have received quite a few questions regarding numbers in the past few weeks. So I thought I would write something answering those questions and a bit more about the different types of numbers you will find in IBM i.

The most basic types of numbers everyone who has programmed on an IBM i has used are packed and zoned numbers. Zoned numbers are not anything new they are just the name that is used for what we use to call signed numbers. What is the difference? Why would I want to use packed rather than zoned? It really comes down to space. Back when computer first started using disk storage it was very expensive, therefore, everyone tried to reduce the amount of disk space they use. Someone came up with the idea of "packing" numbers to reduce the amount of space they use. "Packing" is a method I can store two numbers in each byte. Rather than spend paragraphs explaining what this is I think an example, like a picture, paints a thousand words.

Wednesday, June 13, 2018

Using Built–in global variables

using sql built in global variables

I wrote about creating my own global variables in the past, but I failed to mention the "built-in" ones. A global variable is a SQL variable you can create, place a value into, and then use within the same session. This is a great way to pass SQL variables from one program or procedure to the next. When the SQL session ends the variables are return to their default. If I change a global variable in one job, it does not change the value in the global variable in another job.

What I failed to mention in that post is since IBM i 7.2 there are some "built-in" global values that contain system information, and are maintained by the operating system. This allows me to get the values from these global variables and use them in my own programs and procedures. These built-in global variables can be found in two libraries, SYSIBM and QSYS2, I have no idea why IBM decided to split them between the two.

As with the user created global variables they are all CLE service programs:

Sunday, June 10, 2018

Happy fifth birthday to RPGPGM.COM

Today marks the fifth anniversary of this blog. These five years have been an exciting time for IBM i, and I have been fortunate to have been able to write about many of the wonderful features and functions that have been added to this operating system. Over these years I have written over 450 posts, and if I had to select five of my most favorite things I have written about, and frequently use, they would be:

Wednesday, June 6, 2018

Creating a XML file in just one step using SQL

writing xml directly from sql to ifs file

When I published the post about creating a XML file using SQL I received a message from Birgitta Hauser making a suggestion:

When using RPG Variables defined as SQLTYPE(CLOB: Length) the maximum supported length is 16 MB (RPG Limit) But there is no need to use RPG functions. Instead of writing the data into a variable first, It can be directly written into the XML-File in the VALUES ... INTO Statement.

She makes a very valid point. Many of my example programs have extra steps in them so that you, the reader, can see interim results. I feel this is a good thing as it helps to understand the processes shown.

Wednesday, May 30, 2018

Generating random numbers using SQL

generate random numbers using sql

In the past I have written about generating pseudo-random numbers using APIs, CEERAN0 and C's rand. But I always return to using SQL to generate the random numbers for me. I think it is because it is so simple to use.

In its simplest form if I want one pseudo-random number I can just use the following statement:

SELECT RAND() FROM SYSIBM.SYSDUMMY1

               RAND ( )
5.3215124973296302E-001

Wednesday, May 23, 2018

How to SQL table's long names in RPG program

using long column names from sql table

The seed for this post came from a conversation I had with one of my work colleagues. He complained that even though he could give the columns (fields) in a DDL (SQL) tables names longer than ten characters he found that it was hard to use these names in programs, especially those using printer or display files.

He is not the first person who had made this observation to me. Over the time I have written this web site others have made the same observation.

Let me show what I shared with him so that he could easily overcome this perceived shortcoming.

Monday, May 21, 2018

New Technical Refreshes coming in September?

While poking around in IBM's developerWorks wiki I noticed that pages for the next Technical Refreshes for IBM i 7.3 and 7.2 have already been added.

Wednesday, May 16, 2018

Deleting the current row when using cursor

delete current sql cursor row

In a previous post I wrote about how to perform an update of the current row/record when using a SQL cursor in a RPG program. I have been asked what could be considered the obvious follow on question: How I delete the current row/record when using a SQL cursor?

Fortunately it is as simple as the Update process. All I need to do is...

  • Define the cursor
  • Open the cursor
  • Fetch from the cursor
  • Delete
  • Close the cursor

In its simplest form a program could just be like this:

Wednesday, May 9, 2018

Creating a XML file

create xml file using sql and rpg

In an earlier post I described how to retrieve information from a XML file. This left me thinking how do I create a XML file just using native IBM i functionality?

I wanted to keep my program simple, K.I.S.S. After a good deal of research I have an example program of 28 lines, which takes data from a DDS file or DDL table, converts the data to XML, and outputs the data to a file in the IFS. I need acknowledge Birgitta Hauser here. She is someone whose work I study, and articles I always read. Some of the code in this example is based upon a series of example procedures and programs she posted on Github. I will post a link to her Github at the bottom of this post along with the other reference links.

Before I continue I want to describe the parts of a XML document so you understand when I call an element something what it is. Below is an example of a XML document:

Wednesday, May 2, 2018

Update the current row when using cursor

updating current SQL cursor

I was asked how to update the current row if I am "reading" a file/table using a SQL cursor. At first this question took me aback, it was not that I did not know how to do it, but more a question of why?

What I consider to be one of the biggest benefits of using SQL to process (fetch, delete, and update) data from your database in sets, "chunks" of data. If I need to process a hundred rows/records of data why get each one individually? as that would be a hundred I/O operations. I can get all hundred rows in one I/O operation, thereby, reducing the amount of time and system resources needed to get the data.

I spent some time thinking of a scenario where I might want to fetch rows/records one at a time. I came up with the scenario of an interface. One program would add data to a file, and another would retrieve the data from the file and process the transaction. My example will be that second program.

Wednesday, April 25, 2018

Easy to create shortcut commands

creating your own shortcut commands

After my post about how to create your own commands I received messages from readers giving me examples of how they have created their own shortcut commands, to do the same as the standard IBM i command but shorter. In one example the command SP did the same as WRKSPLF.

I don't think many people know of proxy commands. These allow me to create shortcut commands of the regular IBM i commands without any programming. You can even make proxies of proxy commands, although I cannot think of a reason why I would ever think to do so.

In this example I am going to create a proxy command, SP, for the Work with Spooled Files command, WRKSPLF.

Wednesday, April 18, 2018

Sending your own program created messages to the error subfile

filling the error subfile, errsfl, with program generated message

I have previously written about using the error subfile to display error messages, rather than the message subfile. Personally I like using the error subfile as it is another case of allowing the operating system to do the work for me.

Since publishing that article I have received messages asking how can people create their own error message text, in the RPG program, that is displayed in the error subfile.

Fortunately there is a simple way to do this that will work in either RPG in CL.

No matter which language I use the display file can be the same. Here is the display file I will be using in this example.

Wednesday, April 11, 2018

Built in function to return name of procedure

%proc built in function to return procedure name

Until the last round of Technical Refreshes, IBM i 7.2 TR7 and 7.3 TR3, I could not find a way to retrieve the name of the procedure that I was in. The program data structure enticed me with the *PROC keyword. This was not the procedure's name, but rather either the program's or module's name.

01  DPgmDs           SDS                  qualified
02  D ProcName          *PROC

01  dcl-ds PgmDs psds qualified ;
02    ProcName *proc ;
03  end-ds ;

Fortunately these two Technical Refreshes introduced a new built in function, BiF, to returns the name of the current procedure: %PROC

This example is of what I call a closed subprocedure and how this new BiF works.

Wednesday, April 4, 2018

Handling nulls in CL

in a cl program how to handle a null value from a file

I have written previously about how to handle nulls values in RPG and in SQL, but until this week I had never had a problem with null in a CL program.

The CL program was "reading" a SQL table, where some columns/fields were null.

    ORDNBR      ORDSTS       ORDVALUE
01 PO18087       P            145.67
02 PO18556       C             15.36
03 PO18223       -                  -
04 PO18777       D                  -

The CL program is simple.

Happy patron saint's day!

Today is the saint's day of Saint Isidore of Seville.

In 1997 Pope John Paul II, of the Catholic Church, decided that computers and the internet needed a patron saint. His choice was St Isidore of Seville, "the last of the great scholars of the ancient world".

I hope you all have a good St Isidore Seville day. But if you need divine intervention for your computer problem this is the day could be the right day to ask for divine inspiration.

To learn more about St Isidore of Seville see here.

I need to say "St Isidore of Seville" rather than just "St Isidore" as there is another saint of the same name, "St Isidore the farmer", who is the patron saint of farm workers and the communities they live in.

Wednesday, March 28, 2018

Processing simple XML using XML-INTO

xml using rpg xml-into

I had received notification from a supplier that they were changing the order file they send my employer. Previously they sent a Microsoft Excel spreadsheet, starting the end of this month it would now be a XML file. In the past if I need to convert a XML into data in an IBM i file I would use the EDI application to do the conversion. As the XML is so simple I decided to process its contents in my own RPG program.

The operation code XML-INTO takes information from elements in the XML document and, in my example, places them into data structure subfields. I believe that this operation code has been around since V5R4, I am just a late comer to using it.

The XML document I will be using in these (very) simple examples contains a (very short) list of names and the city and state the person is in. The proposed format of the XML file the supplier sending me is very simple too, no need for anything complicated.

Wednesday, March 21, 2018

Handling divided by zero in SQL

coping with divide by 0 in sql update

One of the messages that annoys me the most is "Attempt to divide by zero". I am sure every programmer, no matter, what language they use, has encountered this at least once. It frustrates me is there is not much we can do to fix the error once it has happened. It annoys me that this can be handled very well within the program to prevent the error.

Before I get started I know there are a couple of different ways the division calculation can be described, I did check. I will be using the following:

   Result = Dividend / Divisor 

It is the Divisor that must be zero for this error to occur, as:

Monday, March 19, 2018

Creating a list of journaled files, improved

finding which files are journaled using sql object-statistics

In my previous post about making a list of files in a library that are journaled I stated that I could not find this information in any Db2 for i view or table function.

Krister Karlsson brought to my attention that the information is available in the OBJECT_STATISTICS table function. Looking at the documentation from IBM it would appear to have been added in the previous round of Technology Refreshes.

Rather than using the Display Object Description command, DSPOBJD, I can get the information I desire from a simple Select statement.

Friday, March 16, 2018

New Technical Refreshes released today

ibm i 7.3 tr4 7.2 tr8 made available

The latest IBM i Technical Refreshes for version 7.2 and 7.3, that were announced in February, were released earlier today.

The information about the PTFs can be found here:

One thing to be aware of is that these probably do not contain the PTFs for the latest changes to RPG, these will be released on Monday March 19. These PTFs are listed here.

Wednesday, March 14, 2018

Creating a list of journaled files

make list of files being journaled

"How do we know what files are journaled in library x?" one of the programmers asked me. A straight forward question, but where to find the information?

I could not find reference to journals in either the Db2 for i Views SYSTABLES or SYSTABLESTAT.

I could find a fields for journal information using the Display File Description command, DSPFD, but only in the types of information that I can display or print, not in those that create an outfile.

I might have used an API, QUSLOBJ with format type OBJD0500 or QUSROBJD format OBJD0400, if I only wanted to know about one file, but I want a "list" of files.

Wednesday, March 7, 2018

Creating your own commands, part 2

creating command validation program, help for the commands, and return value to calling program

In the last post, Creating your own commands, part 1, I wrote about a lot of the basics in creating your own IBM i commands. In this post I am going to show how to write a validation program for the command, how to add help, and return a value from a command.

I am going to create a validation program for the command with the file and library I showed in the previous post.

                   File & library (TESTCMD)

Type choices, press Enter.                                  

File . . . . . . . . . . . . . .                Name
  Library  . . . . . . . . . . .     *LIBL      Name, *LIBL

My validation program will check if the file entered exists. Like the program the command calls the validation program is passed one parameter for each parameter the command has. This command will pass a parameter, 20 characters long, that contains the file and library name.