Saturday, January 30, 2016

DB2 IBM i SQLRPGLE Free and Date conversion

One annoying capability missing from RUNSQL in CLLE is the ability to use Select into a variable. Sure you can create temporary file with create table as and use RCVF to retrieve into our variable but that can be a pain if QTEMP is not where I believe it should be. The QTEMP story is for another day.

I am currently automating month end and daily sales reporting and wanted a better way than CEEDATE and wanted to use SQL. I decided to create a little SQLRPGLE program, when called, would return dates based on current date.

My CLLE programs need last month end date, last beginning quarter, last ending quarter, yesterday, and last month name for report selection criteria.

I took the opportunity to code completely in SQLRPGLE Free. This is my first totally Free program.

There is some default code that I copy from program to program and not used. I wanted to have examples of Free code for future programs.

Feel free (no pun intended) to critic my code. I never claimed to be a programmer but for some reason find myself programming. Go figure.


 ctl-opt option(*nodebugio) dftactgrp(*no) bnddir('QC2LE');
      //********************** M O D I F I C A T I O N S *************************
           //                                                                      **
      //                 Jacksonville, Florida 32223                          **
      //                                                                      **
      // This program returns last month end date, last month name, yesterday,**
      // last beginning quarter and last ending quarter.                      **
      //                                                                      **
      //                                                                      **
      //                                                                      **
      //                                                                      **
      //                                                                      **
      //******************** M O D I F I C A T I O N S *************************
      // Date       Programmer   Description                                  **
      // ---------- ------------ -------------------------------------------- **
      // 01/11/16   Richard Bryant                                            **
      //                                                                      **
      //                                                                      **
      //                                                                      **
      //*************************************************************************
      // ------------------------------------- Prototypes
       dcl-pr rtvdate ExtPgm;
         *n char(8);
         *n char(10);
         *n char(9);
         *n char(8);
         *n char(8);
         *n char(8);
       End-Pr;

       dcl-pi *n;
         medate char(8);     // YYYYMMDD Prior month end date
         medatef char(10);   // YYYY-MM-DD Prior month end date
         mename char(9);     // Month name Prior month end name
         lbegqtr char(8);    // YYYYMMDD last beginning quarter date
         lendqtr char(8);    // YYYYMMDD last ending quarter date
         yesterday char(8);  // YYYYMMDD yesterday
       End-Pi;


      // Program Status Data Structure
       dcl-ds PgmDs PSDS;
         pgmnam *PROC;
         prmnbr *PARMS;
         job char(10) pos(244);
         user char(10) pos(254);
       End-Ds;

      // Data Structures

      // Program Constants
       dcl-c crlf x'0d25';  // Carridge return / Line feed
       dcl-c s ' ';         // Space
       dcl-c d '-';         // Dash
       dcl-c @Apostr x'7d'; // '
       dcl-c @Dblqt x'7f';  // "
       dcl-c @Gt x'6e';     // >
       dcl-c @slgt x'616e'; // />

      // Date conversion fields
       dcl-s @numA zoned(6:0) inz(041205);
       dcl-s @dateA date(*YMD) inz(D'2004-12-04');

      // Standalone Variables
       dcl-s fd int(10);
       dcl-s $xml char(512);

       dcl-s @CmdStr char(512) inz;
       dcl-s message char(52) inz;
      *
       dcl-s sdays zoned(3:0);
       dcl-s ts timestamp;
       dcl-s date date;
       dcl-s sdate date;          // Sales date
       dcl-s currdt zoned(8:0);
       dcl-s currtm zoned(6:0);
       dcl-s current_quarter int(10);
       dcl-s last_quarter int(10);

      //************************************************************************
      //    MainLine
      //


       // Set SQL options
       Exec Sql Set Option Datfmt=*Iso, Commit=*None, Closqlcsr=*Endmod;

       ts = %timestamp();        // Get current date and time
       date = %date(ts);         // Load current date field
       currdt = %dec(%date: *ISO); // Current date yyyymmdd
       currtm = %dec(%time: *HMS); // Currrent time

       // Formated last month end character date YYYY_MM_DD
       Exec SQL Set :medatef = replace(char((current_date + 1 Month - DayofMonth
                   (current_date + 1 Month) Days - 1 month), iso), '-', '_');

       // Last month character date YYYYMMDD
       Exec SQL Set :medate = replace(char((current_date + 1 Month - DayOfMonth
                 (current_date + 1 Month) Days - 1 month), iso), '-', '');

       // Last month name
       Exec SQL Set :mename = monthname(current_date + 1 Month - DayOfMonth
                     (current_date + 1 Month) Days - 1 month);

       // Yesterday YYYYMMDD
       Exec SQL Set :yesterday = replace(char((current_date - 1 day),
                    iso), '-', '');

       // Last beginning and ending quarter
       Exec SQL Set :current_quarter = quarter(current_date);

       if current_quarter = 1;
         last_quarter = 4;
         else;
           last_quarter = current_quarter - 1;
       EndIf;

       Exec SQL Set :lbegqtr =
           case :last_quarter
             when 1 then substring(:medate,1,4) concat '0101'
             when 2 then substring(:medate,1,4) concat '0401'
             when 3 then substring(:medate,1,4) concat '0701'
             when 4 then substring(:medate,1,4) concat '1001'
           end;

       Exec SQL Set :lendqtr =
           case :last_quarter
             when 1 then substring(:medate,1,4) concat '0331'
             when 2 then substring(:medate,1,4) concat '0630'
             when 3 then substring(:medate,1,4) concat '0930'
             when 4 then substring(:medate,1,4) concat '1231'
           end;


       *inlr = *on ;    

2/1/16 - Whoops, my SQL is flawed. Today, 2/1/16, returned previous month end 1/29/16. I found a blog written by Sam Lennon, programmer who opened my eyes to SQL, to figure out where I went wrong. Here is the corrected code...

 // Formated last month end character date YYYY_MM_DD                                         
       Exec SQL Set :medatef = replace(char((current_date -                                         
                               day(current_date) days), iso), '-', '_');                            
                                                                                                    
       // Last month character date YYYYMMDD                                                        
       Exec SQL Set :medate = replace(char((current_date -                                          
                              day(current_date) days), iso), '-', '');                              
                                                                                                    
       // Last month name                                                                           
       Exec SQL Set :mename = monthname(current_date -                                              
                              day(current_date) days);                                              
                                                                                                    
       // Yesterday YYYYMMDD                                                                        
       Exec SQL Set :yesterday = replace(char((current_date - 1 day),                               
                    iso), '-', '');                                                                 
                                                                                                    
       // Last beginning and ending quarter                                                         
       Exec SQL Set :current_quarter = quarter(current_date);                                       
                                                                                                    
       if current_quarter = 1;                                                                      
         last_quarter = 4;                                                                          
         else;                                                                                      
           last_quarter = current_quarter - 1;                                                      
       EndIf;                                                                                       
                                                                                                    
       Exec SQL Set :lbegqtr =                                                                      
           case :last_quarter                                                                       
             when 1 then substring(:medate,1,4) concat '0101'                                       
             when 2 then substring(:medate,1,4) concat '0401'                                       
             when 3 then substring(:medate,1,4) concat '0701'                                       
             when 4 then substring(:medate,1,4) concat '1001'                                       
           end;                                                                                     
                                                                                                    
       Exec SQL Set :lendqtr =                                                                      
           case :last_quarter                                                                       
             when 1 then substring(:medate,1,4) concat '0331'                                       
             when 2 then substring(:medate,1,4) concat '0630'                                       
             when 3 then substring(:medate,1,4) concat '0930'                                       
             when 4 then substring(:medate,1,4) concat '1231'                                       
           end;                                           


~Richard 


“f u cn rd ths, u cn gt a gd jb n sftwr tstng.” (Anonymous) 
                    

Friday, January 29, 2016

IBM i Access Client Solutions, AWESOME!

I have been running Client Solutions since October and have found it is now a full replacement for Iseries Access and Ops Navigator. The last update added the last missing piece, Run SQL scripts.

For the most part I use Linoma's Surveyor/400 software which is awesome and I really miss it when I have to work on machine it is not licensed on. That's where Client Solutions comes to play, and play well, it now does.


I have to say even over remote VPN client it all works very well. I am starting to use Navigator i for all monitoring replacing green screen.

One gotcha to be aware of. I went to delete 125mb save file from IFS folder and the machine ground to a halt. Not a good thing in the middle of the day. Had to sweat it out for 20 minutes before the machine would respond.

I knew there was a December update that I had not loaded so I downloaded and installed. Install was painless and now have run SQL option.

Planning to wait until off hours on Sunday to try deleting from IFS and see if issue was resolved in last update.

Have a great day!

~Richard

Friday, August 28, 2015

SQL is cool!

SQL is cool!



~ Richard

I really hate this darn machine;
I wish that they would sell it.
It won't do what I want it to,
but only what I tell it.
~Author Unknown

Wednesday, December 17, 2014

Web service call from SQLRPGLE

Yesterdays challenge is to replace a scheduled timed event with a dynamic process. Give's us an opportunity to try REST from RPGLE using new SYSTOOLS HTTPGETCLOB.

The program is simple and just prompts the Web programming to capture funds and invoice order in ERP backend. Responds with OK and if not send me an E-mail.

Very cool and once I managed to get all the special chatters in the right place works great. This can also be done in a CLLE with RUNSQL command and CREATE TABLE AS. I initially blew it in CLLE and reported to IBM could not get it working. They came back a few days later, right after I got RPGLE version working, and informed me that '+' continuation line character had a space before and that was causing SQL to fail with SQLSTT 38000. DUH!

The next step is a SOAP message so we can trade data between web and back end ERP.

I wish I had the time to learn PowerRuby. I believe I can do this with Ruby as well.

My next endeavor is JSON to link back end VAI ERP to DESK.COM. Again I think PowerRuby can help and really need to figure out how to get there.

//******************Begin sub-routines*******************************
       //********************************************************************
       // Run web service and check response
       //********************************************************************

       begsr xxxx;

       // Change Job CCSID to 37
       CmdStr = 'CHGJOB CCSID(37)';
       callp(e) ExcCmd(%trim(CmdStr) : %len(%trim(CmdStr)));

       // Call Web Service Action=doInvoicing
       Exec SQL
            SELECT data into :m_data FROM
                   (VALUES(SYSTOOLS.HTTPGETCLOB
                   ('http://xxxxxx.xxxxxxxx.com/xxx/+
                   xxxxxxx.cfm?action=doinvoicing',+
                   '<httpHeader connectTimeout="10000"> +
                   </httpHeader>'))) WS(data);

       // Change Job CCSID to system default
       CmdStr = 'CHGJOB CCSID(65535)';
       callp(e) ExcCmd(%trim(CmdStr) : %len(%trim(CmdStr)));

       // If Web service response not OK - send Email
       if m_data <> 'OK';
        exsr email_err;
       EndIf;

       endsr;

        //********************************************************************
        // Send email if failed to get response from Web
        //********************************************************************

       begsr email_err;
        // Build send email command                                                 
         CmdStr = 'SNDMAIL RECIPIENT((RICHARD xxxxxxxx@xxxxxxxx.COM))';
         CmdStr = %trim(CmdStr) + ' SENDER(QCAPIBMI xxxxxx@xxxxxxxxx.COM)';
         CmdStr = %trim(CmdStr) + ' SUBJECT(' + Quote + 'PO Receipt processed';
         CmdStr = %trim(CmdStr) + ' ***Web Capture funds not processed ***';
         CmdStr = %trim(CmdStr) + Quote + ') MESSAGE(' + Quote;
         CmdStr = %trim(CmdStr) + 'EDIARDSCF failed to receive response OK';
         CmdStr = %trim(CmdStr) + ' *** Contact WEB ***';
         CmdStr = %trim(CmdStr) + Quote + ')';

       // Send email                                                               

       callp(e) ExcCmd(%trim(CmdStr) : %len(%trim(CmdStr)));

       EndSr;




~Richard

One man's crappy software is another man's full time job. ~Jessica Gaston, 2008


Monday, December 8, 2014

Microsoft Virtual PC..Oh what fun it is to ride...

My current challenge is connecting to a customers Sonicwall with GVC client. I already have one connection using GVC. I have been supplied with full GVC installer and it's a couple of versions behind what I currently have installed. Rather than take a chance of corrupting my current setup I find it's time to set up a virtual machine. 

I started with MS Virtual PC and it has been a real challenge to get running. In the end the challenge was my lack of knowledge on what OS is supported in MS Virtual PC.

I downloaded MS Virtual PC and install went easy enough but from there the road became bumpy.  My PC is running Windows 7 Home Premium and I am suspicious is not playing with MS Virtual PC very well. I finally found an article listing compatibility and even though shows yes the foot note states no integration with host. The interface was choppy with video issues and response time not good.

Now my direction is changed to "How to upgrade MS Home Premium to Windows 7 Pro?". I found Windows Anytime Upgrade web page and followed my nose from there. 

My host PC upgraded flawlessly in about 15 minutes. $89.00 poorer, four hours of research and I am now running Windows 7 Pro on desktop. 

I then turned my attention to VM I set up based on Home Premium. Like a dummy I deleted the VM file when I found was not going to fully work. I found the deleted VM in recycle bin and restored with one mouse click. 

The real pain in setting up VM is all the MS updates that have to be applied. My host machine is up to date so did not need MS updates installed. The guest is a different story. I built it from my original Home Premium DVD. It took a couple of hours and several reboots before it was up to date. Honestly not sure how long since I let go by itself periodically visiting PC during the day while I did some household chores.

At this point I am not sure how Windows Anytime Upgrade will work with a VM. One of the initial options is "do you have key", yes I do and I pumped it in  and installer took off. After about 15 minutes VM rebooted and displayed "Failed to install upgrade". Bummer. 

Thought about it for a minute and stated don't you know who I am? I started the process over and this time I got ......


This is bad to the bone. I have access to host PC drives through integration feature which is now working flawlessly. So far I have installed Chrome, IBMi Access and Sonic Wall GVC. Connected to client site with no problem!

And now for my next trick....install and upgrade PTF's for V6R1 IBMi model 550 with three partitions via HMC remotely.

Have you best day!

~Richard

Success seems to be largely a matter of hanging on after others have let go. ~William Feather


  
   

Wednesday, December 3, 2014

As the wind blows...

My PowerRuby ambitions are forced to take a back seat for a while. Work that pays is new focus with planning for IBM I5/550 OS PTF update on three partitions and iTERA HA. Includes HMC update as well.

Also planning for new IBMi Power8 install and migration from Power6 box. The old Power6 running since 2007 only had a few problems resulting limited down time. Battery cache and one drive went bad but we never missed a beat. An unused controller card failed last week and we decided to just vary it off. Ran 24/7 with automatic IPL monthly and limited operator intervention (me). I almost finished converting nightly tape to the cloud. Need just a wee bit more disk since I have no dedup software. The new box more that doubles disk. Got to love it.

Hopefully get back to PowerRuby after x-mas. It's all loaded and ready to go. I need to update with latest PTF cumulative and groups for this box.

My first challenge is adding VPN connection to my already crowded PC. Thank fully it's Sonic Wall and unfortunately I currently only have access the the full install client with new connection parameters embedded. I do not want to take a chance and install two on same machine/OS.

Time for MS Virtual PC. Downloaded from MS and ran install and get to 93%, BAM, "Installed failed reverting configuration".  I am getting 0x80004005 error code. Can not have more than 13 interfaces, who knew? I dig into regedit and find one of the interfaces is Shrewsoft. Ha, don't need that. Uninstalled Shrewsoft and checked registry entry and find it's gone and now there are 12 interfaces.

Run MS .msi and SUCCESS! It's been a long day I'll figure out how to use it tomorrow night.

~Richard

I may not be there yet, but I'm closer than I was yesterday. ~Author Unknown



Monday, December 1, 2014

PowerRuby....installation

Over Thanksgiving weekend, while wife not watching, I downloaded PowerRuby to PC. Took a while for little over 200mb to download. Took even longer to upload to IBMi.

I discovered that IBM 5733SC1 - IBM Portable Utilities for i5/OS was not installed. Lucky for me complete OS image is stored in IFS, it's nice to have plenty of disk. 

I really like Virtual optical drive and image catalogs. Here is a good article explaining image catalogs and how to setup and use. Peace of cake -

WRKIMGCLG on command line....

Select option 12, Work with Entries....

Once DVD-ISO image is mounted select F12 and return back to work with Image Catalogs..


All you need to do now is option 8 Load.

Virtual Optical drive OPTVRT01 is now ready to use to restore licensed program.

THE IBM* PORTABLE UTILITIES FOR i5/OS* consists of a *BASE and Option 1. Both must be installed with CL commands similar to these:

RSTLICPGM LICPGM(5733SC1) DEV(OPTxx) OPTION(*BASE) RSTOBJ(*ALL) LNG(2924)

RSTLICPGM LICPGM(5733SC1) DEV(OPTxx) OPTION(1)RSTOBJ(*PGM)  

No issues, RSTLICPGM, like other steps took longer than expected. I think all told about 15 minutes.

I do need to go and get all the IBM PTF's to complete the install but that will have to wait till this coming weekend. I run full PTF update twice a year and am overdue for second update. Major rule I have is not to install PTF's on a month end weekend. Been there and got bite on ass to prove it.

Oh yeah, this is about PowerRuby.....

At this point I uploaded to IBMi following PowerRuby instuctions

No issues and went smoothly. I now have PowerRuby installed and ready for my first new rails app from scratch....

Unfortunately will have to wait. I have a bunch of side work including replacing daily tape backup with save to cloud (Carbonite) and possible V5R4 upgrade.


~Richard

When you catch bugs early, you also get fewer compound bugs. Compound bugs are two separate bugs that interact: you trip going downstairs, and when you reach for the handrail it comes off in your hand. ~Paul Graham, "The Other Road Ahead," 2001