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.
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.
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.
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
|