Re: %check() workalike in SQL

["Dave Odom" <Dave.Odom@xxxxxxxxxxxx>]

Adam,
 
As far as I know REXX is standard on the i5/OS and all the way back to
certain versions of OS/400 where that operating system "came into the
fold" and adopted the standard REXX language.  Of course, being as how
it would be a "high offense" to use anything other than CL and RPG on an
AS/400, etc., REXX didn't get much use in the AS/400/i5 environment
unless you were from another IBM platforms (VM or MVS where REXX has
been common for decades) or worked in other languages in previous lives.

 
As to examples, besides the HUGE numbers of books and articles on REXX
there are examples in the REXX manuals for the i5/OS (REXX/400
Programmer's Guide and REXX/400 Reference [The DB2/400 manuals also help
with the SQL]) and I've included a small piece of my code that does some
string manipulation of a list of files that appear in the output of the
FTP LS command sent to a site that has several files for our use, each
with a different name which is a date string.  The FTP file has the date
string in one format; I need to insert it into a  DB2 table in another
format for more efficient use by SQL.  When I do these types of things I
try to use the strong points of REXX and SQL so I don't have to create
convoluted functions in either.   I can also help with other examples
depending on what you are trying to do.  So, here you are:
 
  ADDRESS '*COMMAND'                                             
    'OVRDBF FILE(INPUT) TOFILE(HTEUSR/PRNECFTP) MBR(FTPLS)'      
    'OVRDBF FILE(OUTPUT) TOFILE(HTEUSR/PRNECFTP) MBR(FTPLSLOG)'  
      'FTP RMTSYS("transfer.xxxxxxxxx.com") port(*dft)';     
    'DLTOVR FILE(INPUT)'                                         
    'DLTOVR FILE(OUTPUT)'                                        
       SIGNAL ON ERROR                                           
'OVRDBF FILE(STDIN) TOFILE(HTEUSR/PRNECFTP) MBR(FTPLSLOG)'       
RECCNT = 0                                                       
 'RTVMBRD FILE(HTEUSR/PRNECFTP) MBR(FTPLSLOG) NBRCURRCD(&RECCNT)'
                                                                 
                                                                   
 DO RECCNT                                                         
   PULL OBJECT1                                                    
    PARSE VAR OBJECT1 fname "." exten                              
   IF exten = 'TXT' THEN                                           
     do                                                            
     ADDRESS '*COMMAND'                                            
        rfname = RIGHT(fname,8)                                    
        PARSE VAR rfname 1 yr 5 mo 7 da                            
        fname1 = yr||'-'||mo||'-'||da                              
  /*    ADDRESS '*COMMAND' */                                      
         'CLRPFM FILE(HTEUSR/PRNECFTP) MBR(FTPGET)'                
         'OVRDBF FILE(STDOUT) TOFILE(HTEUSR/PRNECFTP) MBR(FTPGET)' 
                                                                   
    INSERT_STMT = 'INSERT INTO HTEUSR/PRNCELS   VALUES(?,?)'       
                                                                   
                                                                 
  ADDRESS '*EXECSQL'                                             
       EXECSQL 'SET OPTION COMMIT = *NONE'                       
       EXECSQL,                                                  
                'PREPARE S1 FROM :INSERT_STMT'                   
       EXECSQL 'EXECUTE S1 USING :fname1, :exten'                
                                                                 
    /*  if RC = 0 then say "OK"; else say "Error, RC = "RC */    
     End;                                                        
   End;                                                          
 
HTH,
 
Dave