Wednesday, February 29, 2012

RPGLE Embedded SQL, update file

Another long hard day of chasing my tail. I am learning how to embed SQL in  RPGLE, it has become quite a challenge. I have had great success with looking up my issues and a few helpful comments to keep me going. This afternoon I got stuck with Fetch, DUH, Fetch into data structure. That hurt.

The problem:
Read records in file, update file field to identify type of sales. Then read the file again summarized quantity of item, color, sales type and write XML records to an IFS file.

Should I update the sales type in the same program? Say, one SQL subroutine to update and then another SQL subroutine to sum and write the same file? Or should I just have two separate programs?

After taking a break it took me five minutes to find the last piece of the puzzle bmyers.net/faq.


This program reads records, but will not allow updates or deletes. To make the program update-capable, a few simple changes are necessary.
First, the cursor declaration must remove the "For Fetch Only" restriction. To be able to update all fields in the record, use the following example:
  Exec Sql Declare Mycursor Cursor For
                            Select * From Mylib/Myfile;
Or, you may restrict the updates to specific field(s):
  Exec Sql Declare Mycursor Cursor For
                            Select * From Mylib/Myfile
                            For Update of Myfield;
When your program is ready to update or delete a record, you will use a special syntax of the SQL Update or Delete statement to associate it with the currently fetched record:
  Exec Sql Update Myfile
                  Set Myfield = Newvalue
           Where Current of Mycursor;
Or:
  Exec Sql Delete From Myfile
           Where Current of Mycursor;
Based on what I have now I believe I can finish tomorrow. My personal deadline is today but overall I am still on track.
~Richard 
It should be noted that no ethically-trained software engineer would ever consent to write a DestroyBaghdad procedure.  Basic professional ethics would instead require him to write a DestroyCity procedure, to which Baghdad could be given as a parameter.  ~Nathaniel S. Borenstein

Tuesday, February 28, 2012

Character in CAST argument not valid.

Tough day today, I quickly introduced myself to SQL error codes. I have all the code written and  theoretically should work. Unfortunately the fetch procedure fails with the following error.

Message ID . . . . . . :   SQL0420    Severity . . . . . . . :   30       
Message type . . . . . :   Diagnostic                                        
Date sent  . . . . . . :   02/28/12   Time sent  . . . . . . :   14:37:30
                                                                             
Message . . . . :   Character in CAST argument not valid.                    
Cause . . . . . :   A character in the argument for the CAST function was not
  correct.                                                               
Recovery  . . . :   Change the result data type to one that recognizes the   
  characters in the CAST argument, or change the argument to contain a valid
  representation of a value for the result data type. Try the request again.

I learned that there is a slew of error codes that can be monitored for. Also received a great link from a previous post. I was aware of the error codes and the need to use them but never had to really figure it out before. Working with debug I was able to monitor SQLSTT variable and found several other issues that were easy to fix. I got a good look at how SQL error codes are generated.  

But now I am stuck, I can run the statement in SQL Explorer and interactive SQL green screen and get the desired results. I’ll post the code in the morning, I spent a couple of hours debugging. I am sure it will turn out to be something silly in the morning.

I going to read my Game of Thrones book for a while and get a fresh start in the morning.


~Richard

Monday, February 27, 2012

SQL - Extract decimal portion of a number...

Got a really early start today arriving on site at 6:30am. My current task is to output sales transaction history to three XML documents. The history is a compilation of daily sales from 300 retail stores that are stored on iSeries in a DB2 table.

After some discussion with the IT Manager it was decided that we would add records to a new file during nightly processing. This file will mimic a file currently in the process except for added status and date/time stamp fields for use in the MidRetail processing.

I first have to determine how I can identify sales based on three categories; regular, promo and markdown. The file does not contain proper coding to identify these types of sales, if it did this would be to easy.

One clue I am given is that markdown’s all end with 99 cents and there is a discount code but not consistent. The first question is how do I get at the number to the right of the decimal place. Should be easy I should be able to do this mathematically but do not know the formula. I want to be able to do this with SQL and did not take long to find answers like Floor() and PARSENAME but not exactly what I am looking for. I went over all the BIF’s again and did not see any silver bullets. Thought this was going to be easy, not!

Then I found an obscure post on a forum about CAST function. It was not exactly what I needed but showed promise. After tinkering around with the function taking care of the positive/negative potential issue I was able to come up with the following, vnew is the selling price and vold is the orginal price:

abs(cast(vnew as int)- vnew)   

Now armed with something that will work I created these three statements:

//Regular
Select vstr, vdte, vcls, vven, vsty, vclr, vsiz, vqty, vold, vnew,abs(cast(vnew as int)- vnew) as cents from rbryant/midsalespf
where abs(cast(vnew as int)- vnew) <> .99
order by vstr, vdte, vcls, vven, vsty, vclr;

//Promo
Select vstr, vdte, vcls, vven, vsty, vclr, vsiz, vqty, vold, vnew,abs(cast(vnew as int)- vnew) as cents from rbryant/midsalespf
where  vnew <> vold
order by vstr, vdte, vcls, vven, vsty, vclr;

//Markdown
Select vstr, vdte, vcls, vven, vsty, vclr, vsiz, vqty, vold, vnew,abs(cast(vnew as int)- vnew) as cents from rbryant/midsalespf
where abs(cast(vnew as int)- vnew) = .99
order by vstr, vdte, vcls, vven, vsty, vclr;

I add sum(vqty) and Group by to the equation I have my daily sales summed to store, date, class, vendor, style and color. I add size and I’ll have my summary to the size. I am sure the weekly summary to the color level will be easy.

After analyzing the data a few different ways and counting up records to make sure nothing missed I took my results to the IT Manager. Looks good and we can move forward.

Tomorrow I look into how to add these statements in my RPGLE program to create XML documents.  

~Richard

Saturday, February 25, 2012

Fast and successful week...

It was a really good week, it went by very fast and I have managed to get ahead of schedule. I hoped to finish off the week with a home run but best I could do was lay the foundation for a good start next week. ;)

I am unable to see the recently setup MidRetail test server from the test LPAR. So I spent the morning verifying the iSeries TCP and NetServer configuration and testing the QNTC file system. I took my findings to the IT Manager and he reached the same conclusion, there is a DNS problem. Come to find out, the MS DNS server for the test network recently died and new server had to be reconfigured manually. Unfortunately the iSeries entries were missed. I do not have access to the server so have to wait until Monday to have the network guys figure it out.

The iSeries QNTC file system is part of the iSeries Netserver and allows the iSeries programs output stream files, STMF, directly to a Microsoft Windows share. Very cool stuff, easy to setup and really handy when you need to feed your back end data to a PC application.

I have been using MidRetail on the the production server since the iSeries test LPAR is on a different Domain and unable to directly communicate. While waiting on the Microsoft Application test server to be setup I have been moving documents between systems manually to test my XML output. Once we get the connection issues resolved I will be able to install the MidRetail application on the test server and be able to start daily full cycle testing of the completed interface programs.

I finished polishing the Store interface program and moved on to the Sales History interface program. Each program grows in complexity as I work through the project. This interface requires that store sales be summed to Date, Store, Style, Color and Date, Store, Style, Color, Size daily and  Date, Store, Style, Color weekly. The input data is from a file that is populated nightly from sales of over 300 stores. This is looking like a good fit for my first embedded SQL, whoopee!

I may break the task into two programs, daily and weekly, that creates three XML documents. Some consideration will be needed on handling missed Stores and how to process them once data is retrieved.

I have been writing and learning RPGLE Free and now ready for trying my hand at some embedded SQL. I have seen working examples but have never actually coded myself. I do use SQL Explorer on a regular basis to write Select queries to verify data and find it extremely useful. I have a day or two to work out the techniques but if it looks like it will take me too long to figure out I will just fall back to RPGLE.

After a couple of hours with the IT Manager I have enough information to start analyzing data and putting together a plan. I ran a few SQL queries over the file and have a good feeling for what needs to be done.  

~Richard



Three things are certain:
Death, taxes, and lost data.
Guess which has occurred.
~David Dixon, 1998, winning entry of the Haiku Error Messages 21st Challenge by Charlie Varon and Jim Rosenau, sponsored by Salon.com

Thursday, February 23, 2012

DB2 to XML....

Today I finished polishing my item master daily update and gave a copy to the Director of Application Development for code review. He commented this is the Apollo 11 version compared to his old style RPGII coding and smiled. I will take that as a compliment. ;)

I continued and started the Store master file daily update program. I completed the coding and the program compiled but I am getting pretty tired and decided I could finish up in the morning. This program needed some date manipulation changing six digit numeric field, YYMMDD, to character representation, CCYY-MM-DD.  Things sure have gotten easier with RPGLE and with Google to the rescue I had no problem catching on.

The fields are not true dates so my friend Sam Lennon's article on RPG IV legacy dates cheat sheet was not where I landed. I did find another article that I have looked at from time to time. The RPG free-format date conversion cheat sheet by Mitchel Laman is just what I needed.

I added a couple of fields to my D spec’s -

D @numA       S      6  0  inz(041205)                      yymmdd
D @dateA       S        d    inz(D’2004-12-04’) date formatted field
D Strod           A     10 ccyy-mm-dd alpha

I added a subroutine to my code to deal with the conversion.

begsr datesr;
/free
@numA = sodte; // Store open date field from input file
@dateA = %date(@numA:*ymd);      // yymmdd to D’ccyy-mm-dd’
endsr;
/end-free

At the time I was not sure if the dashes would carry through. I then added the following to my routine that creates the XML for the IFS write. There is more to the XML but you get were I’m heading.

$xml = %char(@dateA)

I am really liking this RPGLE Free, much easier than when I worked with RPGII years ago.

~ Richard  

Wednesday, February 22, 2012

RPGLE Free learn something new every day...

While working on my XML project I ran in to a little problem. I have been building off my first concept program and have completed three programs that generate six XML documents. As I am working with the last program that captures additions and changes to the item master file I stumbled a little.  

I had to move my do loop outside the routine that writes to the IFS and I forgot to move the close file with it. I spotted the problem quickly and made adjustment but I had ran the program several times checking data in debug before I noticed the problem. So I run the program and no output was generated in the IFS. I scratched my head for a while thinking I goofed up the program and then ran one of my completed programs that I know works and no file generated, WTF!

I then ran WRKLNK to the directory I am working with and received error, to many files open. OK, I realize that I created the problem earlier in the day but did not know how to fix the problem. I prompt CLOF and need a PF name, but these are IFS stream files. Google to the rescue.

I quickly discovered that WRKJOB has a few more options other than I have used before like *OPNF. I ran the command and yes I see I have many open files, guess I ran testing a little more than I thought. But I still do not know how to fix the problem. DOH! Sign off and back on stupid!

So I resolved my problem and before I walked out for the day I ran my new program and generated the output I am looking for. :)

I taught myself a lot today. The program input is a file that is added to nightly from the item master journal file. I incorporated an external data structure for the before and after images based on the item master file with a prefix.

d Befjrn    e  ds  extname(ipithdr)

     prefix(b_)
d Aftjrn      e  ds extname(ipithdr)
     prefix(a_)           

I figured out how to load the array using rpgle free, very cool and much better than adding the from and to positions for each field.

I had already written this program in rpgle and it tested great but since my other programs are rpgle free I wanted to change it to free. There are a few caveats in free that I was not aware of but quickly over came each challenge and by the end of the day I am very pleased.

I think I am going to figure out check for open files if program fails, that will be tomorrows task.

My programming skills are growing by leaps and bounds. I have been writing small amounts of code and debugging a lot of code over the last 15 years. I am really starting to feel well about my programming. I am sure I will have many challenges to overcome as time moves on.

Seems like I am not missing juggling many balls, answering phone calls, E-mail and educating users as operations manager as I thought I would. Just put in the ear-buds, turn up the music and jam on the keyboard, eight hours flies by!

~Richard    



Any fool can write code that a computer can understand.  Good programmers write code that humans can understand.  ~Martin Fowler

Tuesday, February 21, 2012

iSeries DB2 to XML example

I managed to finish one of the two product interface programs but got distracted figuring out why the NFS server is not working on the test system. Someone changed the domain in TCPCFG and the partition was IPL'd to fix a backup issue. When the system restated none of the TCP servers started and subsystem QPGMR did not start. I tighten up a little bit and got everything working but now NFS won't start. I had to realign the test partition domain to the windows server domain and an IPL tomorrow morning will correct the issue. If not I will be chasing it down.

As for the XML output, the only real magic is coming from the C API in QC2LE binding directory and the IFSIO_H copy source. There is a lot of code and glad I did not have to write it or have to understand the details. It just works, giving me more time to focus on the business issues. I found all of the information HERE.

The code looks much better in WDSC and if you want a copy of the source just ask.

I'll post more when I can. Soon I will be parsing incoming XML documents. If you have any suggestions or questions please do not hesitate to ask.



      **************************************************************************
     h option(*nodebugio) dftactgrp(*no) bnddir('QC2LE')
      ********************** M O D I F I C A T I O N S *************************
      **                                                                      **
      **  This progam is designed to run every night and fully reloads        **
      **  Department, Class, Color and Size files from Island Pacific         **
      **  master files to MidRetail folders in QNTC IFS file system.          **
      **                                                                      **
      **  ip_color.xml -> /MIDRetailData/Color                                **
      **  ip_size.xml -> /MIDRetailData/Size                                  **
      **  ip_dept.xml -> /MIDRetailData/Hierarchy                             **
      **  ip_class.xml -> /MIDRetailData/Hierarchy                            **
      **                                                                      **
      **                                                                      **
      **                                                                      **
      **                                                                      **
      ********************** M O D I F I C A T I O N S *************************
      ** Date       Programmer   Description                                  **
      ** ---------- ------------ -------------------------------------------- **
      ** 2/20/12    Richard Bryant                                            **
      **                                                                      **
      **                                                                      **
      **                                                                      **
      **************************************************************************
     Fipcolor   if   e           k disk                                         Color
     Fipdepts   if   e           k disk                                         Departments
     Fipclass   if   e           k disk                                         Class
     Fipsizes   if   e           k disk                                         Sizes
      *
      **************************************************************************
      *
     D                SDS
     D  PRGNAM                 1     10

      /copy *libl/qrpglesrc,IFSIO_H

      * Program Constants
     d fd              s             10I 0
     d crlf            c                   x'0D25'
     d $xml            s            512a
     d @CmdStr         s            512a   inz
     d @Apostr         s              1a   inz(X'7D')                           '
     d @Dblqt          s              1a   inz(X'7F')                           "
     d @Gt             s              1a   inz(X'6E')                           >
     d @slgt           s              2a   inz(X'616E')                         />
     D B               C                   CONST('|')
     D D               C                   CONST('-')
      *
      * Color XML constants
      *
     d cstr            c                   '<Color '
     d ccde            c                   'Code='
     d cnam            c                   'Name='
     d cend            c                   '</Color>'
     d cpath           c                   '/MidRetail/ip_color.xml'
     d ctrg            c                   '/MidRetail/ip_color.xml.trg'
     d cschema         c                   '<Colors xmlns="http://tempuri.org-
     d                                     /ColorCodesLoadSchema.xsd">'
      *
      * Size XML constants
      *
     d sstr            c                   '<Size '
     d scde            c                   'Code='
     d spri            c                   'Primary='
     d scat            c                   'ProductCategory='
     d send            c                   '</Size>'
     d spath           c                   '/MidRetail/ip_size.xml'
     d strg            c                   '/MidRetail/ip_size.xml.trg'
     d sschema         c                   '<Sizes xmlns="http://tempuri.org-
     d                                     /SizeCodesLoadSchema.xsd">'
      *
      * Department & Class XML constants
      *
     d dstr            c                   '<Product '
     d dpar            c                   'Parent='
     d did             c                   'ID='
     d dnme            c                   'Name='
     d ddes            c                   'Description='
     d dend            c                   '</Product>'
     d hstr            c                   '</Hierarchy ID="BodyC">'
     d dpath           c                   '/MidRetail/ip_dept.xml'
     d dtrg            c                   '/MidRetail/ip_dept.xml.trg'
     d clpath          c                   '/MidRetail/ip_class.xml'
     d cltrg           c                   '/MidRetail/ip_class.xml.trg'
     d dschema         c                   '<Hierarchies xmlns="http://tempuri-
     d                                     .org/HierarchyLoadSchema.xsd">'
      *
     d FilePath        s            250a
     d SchemaPath      s            100a
      *
     D HSTYLE          S             13A
     D NSTYLE          S             13A
     D ASTYLE          S             13A
     D DFLAG           S              1  0
     D VENDOR          S              5  0
      *
      * Mid-Retail
     D H_ID            c                   CONST('BodyC')
      *
      *
     ***************************************************************************
      *    MainLine
      *
      /free

        exsr Color_Load;
        exsr Size_Load;
        exsr Dept_Load;
        exsr Class_Load;
        eval *inlr = *on;
        return;

       // ******************Begin sub-routines*********************************

       // Color Full Load **************************************

        begsr color_load;
        clear FilePath;
        clear SchemaPath;
        eval FilePath=cpath;
        eval SchemaPath=cschema;
        exsr ClrHdr;

        setll *loval ipcolor;
        dou %eof (ipcolor);
        read ipcolor;

        if not %eof;
        $xml = cstr
             + ccde + @Dblqt + %editc(cclr:'X')+ @Dblqt + ' '
             + cnam + @Dblqt + %trim(clrn) + @Dblqt + @gt
             + cend + crlf;

        callp write(fd: %addr($xml): %len(%trim($xml))); // Write to IFS
        endif;

        enddo;

       // Close file
        $xml = '</Colors>' + crlf;
        callp write(fd: %addr($xml): %len(%trim($xml)));
        callp close(fd);

       // Set up empty trigger file
        clear FilePath;
        eval FilePath=ctrg;
        exsr SetTrg;

        endsr;

       // Size Full Load ***************************************

        begsr size_load;
        clear FilePath;
        clear SchemaPath;
        eval FilePath=spath;
        eval SchemaPath=sschema;
        exsr ClrHdr;

        setll *loval ipsizes;
        dou %eof (ipsizes);
        read ipsizes;
        if not %eof;

        if ssiz = 0000;             // Only if size equal to zero
          eval snam = 'NOSIZE';
        endif;

        $xml = sstr
             + scde + @Dblqt + %editc(ssiz:'X')+ @Dblqt + ' '
             + spri + @Dblqt + %trim(snam) + @Dblqt + ' '
             + scat + @Dblqt + 'ALL' + @Dblqt + @gt
             + send + crlf;

        callp write(fd: %addr($xml): %len(%trim($xml)));

        endif;

        enddo;

       // Close file
        $xml = '</Sizes>' + crlf;
        callp write(fd: %addr($xml): %len(%trim($xml)));
        callp close(fd);

       // Set up empty trigger file
        clear FilePath;
        eval FilePath=strg;
        exsr SetTrg;

        endsr;

       // Class Full Load ***************************************

        begsr class_load;
        clear FilePath;
        clear SchemaPath;
        eval FilePath=clpath;
        eval SchemaPath=dschema;
        exsr ClrHdr;

       // Example output - </Hierarchy ID="BodyC">
        $xml = hstr + crlf;
        callp write(fd: %addr($xml): %len(%trim($xml)));

        setll *loval ipclass;
        dou %eof (ipclass);
        read ipclass;
        if not %eof;

        if cdpt < 18;      // Only if department less than 18

       // Example - <Product Parent="001" ID="0498" Name="Class 0498"
       //           Description="DEPT 01 DRESSES"></Product>
        $xml = dstr
             + dpar + @Dblqt + %editc(cdpt:'X') + @Dblqt + ' '
             + did + @Dblqt + %editc(ccls:'X') + @Dblqt + ' '
             + dnme + @Dblqt + 'Class ' + %editc(ccls:'X') + @Dblqt + ' '
             + ddes + @Dblqt + %trim(clnm) + @Dblqt + @gt
             + dend + crlf;

        callp write(fd: %addr($xml): %len(%trim($xml)));

        endif;
        endif;

        enddo;

       // Close file
        $xml = '</Hierarchy>' + crlf + '</Hierarchies>' + crlf;
        callp write(fd: %addr($xml): %len(%trim($xml)));
        callp close(fd);

       // Set up empty trigger file
        clear FilePath;
        eval FilePath=cltrg;
        exsr SetTrg;

        endsr;

       // Department Full Load **********************************

        begsr dept_load;
        clear FilePath;
        clear SchemaPath;
        eval FilePath=dpath;
        eval SchemaPath=dschema;
        exsr ClrHdr;

       // Example output - </Hierarchy ID="BodyC">
        $xml = hstr + crlf;
        callp write(fd: %addr($xml): %len(%trim($xml)));

        setll *loval ipdepts;
        dou %eof (ipdepts);
        read ipdepts;
        if not %eof;

        if ddpt < 18;      // Only if department less than 18

       // Example - <Product Parent="BodyC" ID="001" Name="Dept 001"
       //           Description="DRESSES"></Product>
        $xml = dstr
             + dpar + @Dblqt + H_ID + @Dblqt + ' '
             + did + @Dblqt + %editc(ddpt:'X') + @Dblqt + ' '
             + dnme + @Dblqt + 'Dept ' + %editc(ddpt:'X') + @Dblqt + ' '
             + ddes + @Dblqt + %trim(dnam) + @Dblqt + @gt
             + dend + crlf;

        callp write(fd: %addr($xml): %len(%trim($xml)));

        endif;
        endif;

        enddo;

       // Close file
        $xml = '</Hierarchy>' + crlf + '</Hierarchies>' + crlf;
        callp write(fd: %addr($xml): %len(%trim($xml)));
        callp close(fd);

       // Set up empty trigger file
        clear FilePath;
        eval FilePath=dtrg;
        exsr SetTrg;

        endsr;

       // Create or replace XML output file and header *********

       // Create XML output file in IFS
       // Variable depending on what file is be created

        begsr ClrHdr;
        fd = open(%trim(filepath)
                  : O_WRONLY+O_CREAT+O_TRUNC+O_CCSID
                  : S_IRGRP + S_IWGRP + S_IXGRP +
                     S_IRUSR + S_IWUSR + S_IXUSR
                  : 819);
        callp close(fd);
        fd = open(%trim(filepath):O_WRONLY+O_TEXTDATA);

        $xml = '<?xml version="1.0" encoding="UTF-8"?>' + crlf +
         %trim(schemapath) + crlf;

        callp write(fd: %addr($xml): %len(%trim($xml)));

        endsr;

       // Create .TRG empty file for MR ************************

        begsr SetTrg;
        fd = open(%trim(filepath)
                  : O_WRONLY+O_CREAT+O_TRUNC+O_CCSID
                  : S_IRGRP + S_IWGRP + S_IXGRP +
                     S_IRUSR + S_IWUSR + S_IXUSR
                  : 819);
        callp close(fd);
        fd = open(%trim(filepath):O_WRONLY+O_TEXTDATA);

        $xml = '<?xml version="1.0" encoding="UTF-8"?>' + crlf +
         %trim(schemapath) + crlf;

        callp write(fd: %addr($xml): %len(%trim($xml)));

        // Close file

        callp write(fd: %addr($xml): %len(%trim($xml)));
        callp close(fd);

        endsr;

      /end-free
      *
      **************************************************************************








Monday, February 20, 2012

iSeries DB2 to XML

Arrived at the office before 7 am and spent an hour looking at third party xml solutions then decided to move forward with what I have already tested. During the meeting last Friday it was decided to spend a couple of weeks looking into rpgle oar. Since time is short and we don't want to miss our due date we decided to spend the next two weeks moving forward with what I have already tested know works. I believe that I can have the interfaces ready for stress testing in that time frame.

I put in the ear buds, turned up the music and starting coding. I only got up three times, one coffee refill and two bathroom. Took a 15min chat break along the way. By 2:30pm I had a commented and clean looking rpgle program that creates or replaces four xml documents containing data from four iSeries master files in an IFS folder. I pretty satisfied.

One down, six to go.


As soon as the network guys can get the test server up and running I will be able to set up daily full cycle loads. While I'm waiting I am going to start on the product sync programs.

I'll post the code and more details in the near future. Any critqueing will be greatly appreciated.

~Richard

Sunday, February 19, 2012

XML to DB2 Exciting and Mind Numbing....

The past two weeks have been an exciting and mind numbing. My current task is to create interface programs that create and decompose pipe delimited text and XML documents between an iSeries @ V6R1 and a MS Windows 2008 application server application.  I will be using  the iSeries IFS /QNTC directory which is a share on the Microsoft 2008 application server to move documents between systems.

The iSeries data is created and used by an old unsupported version of Island Pacific ERP (IP). The MS application is Mid-Retail (MR). MR is providing the API’s to access their data.

After two weeks I was able to come up with an idea of how long it will take to get MR in the users hands. I have created some programs that can read and write XML based on the requirements. I spent the past couple of days putting my spreadsheets and documentation together to create a PowerPoint presentation. Friday the Project Lead (PL) and I presented our plan to the Director of Application Programming. After two hours we received the go ahead and approved my contract to the end of March. :)

The director did catch me a little off guard. In going over the possibilities for XML transformation I mentioned DB2 to XML Extender and RPGLE OAR. He showed great interest in using RPGLE OAR. After discussing the option further it was decided that was the direction we want to take.

I believe the right way to accomplish this goal is to
use RPGLE OAR and write the Handler program. Unfortunately I have never created a handler and my programming skills may not be enough to create one in the time frame needed. I have already created programs that create and read XML using RPGLE XML-SAX, IFS API, External Data Structures, Procedures/Sub Procedures and Copy source files and I am comfortable with the current time frame for the project.

I explained my concerns about my current skills and the deadline for the project. They had no problem and are willing to let me spend some time getting up to speed. I am impressed that they are willing to help me increase my skill set and are comfortable with me proceeding with the project.

After my last employer spent a year destroying my self confidence I am finely returning to normal. I have increased my skills with every step in my career and turned negative into positive. What has made me successful is, while I may not know the details of everything I know how to find and learn what I need to accomplish any task at hand.

I have never really wanted to be a full time programmer and I get a bigger sense of accomplishment being in operations helping users, managers, customers with day to day technology and data issues. But I may be changing my mind, figuring out the XML to DB2 is a big challenge and so far is fulfilling my appetite for accomplishment.

All that I needed is available on the Internet. Below are a few of the sites I gleaned information from:

The Ins and outs of XML and DB2 for i5/OS
RGPLE XML Parser example code
Working with the IFS RPG IV
IBM Rational Open Access: RPG Edtion V1.1
IBM Systems Magazine - Getting a Handle on RPG Open Access
iSeries read IFS file
DB2 XML Extender Hints and Tips

A special thanks to the following people, without them there would be no Milk Bones for the dogs.


~Richard

Saturday, February 4, 2012

iSeries DDM file copy rookie mistake....


It’s been a long time since I have clobbered production data, but sooner or later everyone gets bit. I am currently working on a project to interface iSeries application with a Windows SQL based application. While working on the concept for the technical design document I needed fresh test data.

The iSeries system I am working on has two LPAR’s, one production and one test. I only have access to the test system. I have never had the opportunity to work on a partitioned system but have studied the setup more than a few times. I have seen DDM in passing but have not worked on multiple networked iSeries in a long time.

Last week, when discussing the need for test data, the Manager quickly created a DDM  file on the test LPAR. He then ran the copy command to populate the test file from the production system. I missed what system he ran the copy from and assumed it was on the test system. That was pretty cool and looked easy enough. I should have stopped right there and studied up on DDM!

Yesterday I needed some fresh data to validate that my program is working as desired. I ran a WRKOBJ *ALL/SOMEFILE and observed two objects one specified as DDM. I displayed the DDM object and observed the remote system is the production partition. I opened up the IBM Information Center and search on DDM. I read that the DDM file only needs to be created once so I know I don’t need create it again. I determined that I need to run the following command;


This should copy the file from production to test. I copied the command right out of the book and replaced with correct names.

The command failed with User not authorized, cool security is working.

I then asked another programmer if he had access and could run the copy for me. I copied the command and sent it to him via E-mail. As soon as the copy was done I started looking at the data and found it was not updated. We then get together and start analyzing the production data and find it is not correct, change date was today and should not be. The file is updated nightly.

Somehow we miscommunicated and we think he ran the copy command on the production system. I assume if you run the command on the production system the data is copied from test system, there does not seem to be any other explanation.

I did not realize that the DDM was a two way street and after studying the manual I have not found where that is documented. The DDM file is only on the test system, I could see this being an issue if a DDM file existed on the production system pointing to the test system.

In the end it turned out not to be a big problem, the file stepped on was created from a journal file and only took five minutes to correct the production file. Good thing since the tape backup failed the previous night.

All is well that ends well!

A failure is a man who has blundered, but is not able to cash in the experience.  ~Elbert Hubbard

~Richard