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.