Run a SQL statement on another IBM i

I work in an environment with many different IBM i systems. I use the word "systems" as some of these are partitions on the same PowerSystems server, while others reside on different servers. Often there is a need to extract information from one system, and send it to another where it will be used.

I have previously written about using DDM files to get information from other systems. But as I use SQL more I find they are limiting, as I cannot extract information from a DDM file using SQL statements. If only there was a way to extract data from one system by using a SQL Select statement issued from a second system.

After searching the new KnowledgeCenter I discovered a way!

Easy way to initialize columns when using SQL insert

Regular readers of this blog know that I like to keep things simple and easy. Partly for my own peace of mind, and partly if it is simple then other programmers can quickly understand what my code does. A colleague calls this the K.I.S.S. methodology, which stands for "Keep It Simple Stupid", although she often changes the last "S" to "Simon" (I am not sure what she is trying to imply by this).

A good example is when using SQL to insert a row/record into a table/file. If not all the columns/fields need to be filled I need to give them a default value. This means that I need to look at the table and determine the data type of each column and whether it can be null or not, which takes time and can leave to errors if I try and accidentally insert a columns/field with the wrong default value.

Write to a file in the IFS using RPG

 

Update

Before using what is described below consider using SQL to write to IFS files.

 

---

 

After writing about how to read an IFS file in a RPG program I received a message from Domenico asking me how to do the opposite.

Very interesting article, as always, the only suggestion is: since, surfing internet, it is possible to find many examples about using _c_ifs_open but not one (!!) of _c_ifs_write .. it would be very very interesting and useful for our community.

Before I get started explaining how to write to an IFS file using RPG, let me say I am not going to repeat what I said in my earlier post A better way to read a file in the IFS with RPG, so you might want to review that before continuing with this post.

Happy 28th birthday!

Today is the 28th anniversary of the launch of the AS400.

You can watch a video of the launch announcement here.

Tonight I will be raising a glass of my favorite beverage to wish the AS400 and its descendents, up to the IBM i, a happy birthday!

You can also learn what your PC would have been like at the time of the launch here.

And the family tree that took the AS400 and evolved to became the IBM i here.

Calling a program on a remote IBM i using SNADS

I received a question from a reader asking if there was a way to copy data from a central IBM i to a number of remote IBM i using SNADS, and then have the central server call a program on the remote ones. Fortunately this is easy to do just using a few simple commands and a bit of common sense.

Distributed Data Management (DDM) files have been available on IBM computers for as many years as I can remember. DDM files are a quick and easy way to copy data to and from one IBM computer to another. Most of my experience with them has between AS400 and later IBM i, but I have also worked receiving data from an IBM mainframe via DDM files.

Using SQL to update a column with the greater value of two other columns

I received a call to action from my superior. "The Order Close Date in the Order History file has been messed up. Can you write a program to fix it?"

"When do you want this for?" I replied.

"Next week" he shrugged.

I have more than enough time, four days, to write a RPG program, but where is the fun in that when I could do something with SQL? Especially when this is something I have never done before: compare two values and use the greater to update another value.

Debugging a batch job

It is a surprisingly common misconception that you cannot debug a program that runs in batch. While you have to perform some extra steps, which identify the job you want to debug within, the debugging interface is identical to that of a interactive debug.

To be able to run source debug, whether interactive or batch, you need to create (compile) your CLLE, RPGLE, and SQLRPGLE objects with the appropriate debug view parameter. To learn about the various debug views see the post Debug views finding your favorite.

New KnowledgeCenter

The new IBM's KnowledgeCenter is now online, and after a few minutes of using it I have to say that it is an improvement on the old. It has addressed both of the issues I had with the old version: web site speed and ease of search.

New KnowledgeCenter on Sunday

This morning when I went into IBM's KnowledgeCenter web site a pop up message, below, informing me that a "brand new" version of the site will be "live" on May 29.

Being a curious fellow I copied the URL to a new browser window to see what is coming in the new site (come on IBM this should have been a link, not just text). You don't have to copy that URL as you can just click here to reach the same destination.

The link goes to a blog post by James Roberts where he explains some of the improvements we will see:

Debug views finding your favorite

How many of us have considered the different types of debug views that are available with the create (compile) program and module commands? We may have customized the create commands when installing a new release or, if we are using PDM, created our own customized PDM option for the create command. After that we get lazy, and this includes me, and we just leave the parameters the way they are without bothering to check what the various options do. By doing this we miss out on some very useful features that would make our use of debug easier.

In this post I am just going to cover the following create commands and what the different debug views show:

The different flavors of free format RPG

I am sure that I have confused readers with the names I call the different flavors of free format RPG. I thought it would be best to have a post where I describe each flavor, and then refer the reader to this post from others when I need to explain the differences.

Before I start I just want to explain that these are the names I call the different flavors. These might not be (probably definitely not) the names that IBM would call them, but I do call them this.

In my opinion there are three flavors of free format RPG:

Moving from Output specs to Printer files

The end of the usefulness RPG's Output specifications, O-specs, was signaled by IBM when they did not give a free format equivalent when they gave us "free format definition" RPG, IBM i 7.1 TR7 in November 2013. If you start programming in "fully free" RPG, 7.2 TR1 and 7.1 TR11 November 2014, you will find that you cannot easily use non-free format code, such as O-specs. So this is the time to move away from O-specs and embrace printer files.

I am writing this post in response to a couple of people who contacted me asking about the differences in coding printer files, to O-specs, and how to write the RPG to use them. I know that there are tools to convert O-specs to externally described printer files, and I have used a couple myself. But this post is going to assume that this is a "vanilla" configuration of IBM i with none of those tools present.