Reading and processing AS400 file using imbedded SQLRPGLE

Here's a brief example of how to select records in SQLRPGLE from an AS400 file, read through them, and perform processing on them.  It is a very powerful way of manipulating data once you get started with it.

So, the source code is below.  I put comments around the source code, in my attempt to explain how the code works.  If you have any questions, comment or drop me a line!

ffile1     uf a e           k disk                                   

 *  The file above  matches the file below in layout.  In this example we are copying from

 *  file2 to file1 based on the SQL query below                                                                

d FileDS        e ds                  extname(file2)              

 * file2 is the input file that we will be reading & processing through SQL                      

c/exec sql    

 * In SQLRPGLE, at the beginning of every SQL statement, the exec statement 

 * must be performed

c+  declare C1 cursor for                                   

c+  select * from file1 where                            

c+  field1 = 'AAA' and field2 = 'BBB'   

c/end-exec           

 * In the above SQL, we are declaring a cursor called C1.  We are selecting from file1 

 * where field1 & field 2 equals the specified values.                                             

c/exec sql 
c+ open C1                                                        

c/end-exec

* The above SQL command opens the cursor and gets it ready for use.                                                         

                                                                   

c/exec sql                                                         

c+  fetch C1 into :FileDS                                          

c/end-exec                                                         

* The above SQL command reads the first record of the series into the FileDS data structure

* defined at the top of the program.  This is based on file2, which has the same fields

* as file1.  Basically it is an empty copy of file1.

                                                                   

*  if sqlcod = 0, it means that we have a record to process.  Once sqlcod is not equal to 0, 

*  it means that the end of record set has been reached, and the do loop will end.                  

c                   dow       sqlcod = 0         

*  While inside the do loop, we can do whatever processing we want.  We have 

* selected the records using the SQL query, and are only processing the records
* that were selected.  In this case, I am writing to file1 because the record format
* and fields are identical to file2.  

c                   write     file1                               

c/exec sql     

* Next, we just fetch the next record in and go through the do loop again.                                                    

c+  fetch C1                                                       

c+  into :FileDS                                                   

c/end-exec                                                         

c                   enddo     

* When finished, we need to close the cursor.  Leaving the cursor open will cause

* undesirable results.  If the program is called again, and it attempts to open the 

* cursor while it was already opened previously, it will cause an error.                                     

c/exec sql                                               

c+  close C1                                             

c/end-exec                                               

*                 - Finally, when all is complete, set on *INLR and end the program                                                        

c                   eval      *inlr = '1'      

I hope someone finds this useful.  Once you get the hang of it, you can do amazing things!

 

AS400 SQL to figure out what duplicate records exist in a file, but a field value contains special value

My dilemma today has been that I have to create an SQL to tell me out of a 700+ million record file, what duplicate records there are in the table, and to only select them if a certain field is a certain value.  I should be able to put some brains together tomorrow and figure this out, but if anyone out there knows the magic for this I'd be happy to hear it ahead of time so I can impress management with my technical prowess.  :)

So here's the problem:

1.  I have an AS400 file that contains more than 700 million records (growing daily).  In this file, there are 100 fields all together, and there are 5 fields that when combined, identify the record as unique (to me anyway).  There are 3 other fields besides the unique key of importance in this file.

2.  In the file, I am trying to come up with the SQL statement to retrieve all records from the file where there is not a corresponding record with the same unique key (field1 + field2 + field3 + field4 + field5), and where field6 = 'AAA' and field 7 = 'XX' and field8 = 'XX'. 

3.  The final output should give me only records that are in the table once, based on the 5 key fields, and only when field6, field7, and field8 equals the input values.

I think I am very close with the following SQL statement.  I was running late today, so didn't have time to put the finishing touches on it.

select * from file1 where                 

(field1,field2,field3,field4,field5) in      

(                                                   

select field1,field2,field3,field4,field5    

from file1                                       

group by                                            

field1,field2,field3,field4,field5           

having count(*)=1                                   

)                                                   

and field6 = 'AAA'                                   

and field7 = 'XX'                                   

and field8 = 'XX'                                                      

I think this is giving the correct information, although I need to validate it some more.  I am telling the SQL query to select all fields from file1, where the 5 unique key fields are found in the inner table one time only with the having count(*)=1 statement, grouping by the 5 key fields.  On the outer query, I am limiting the results to only those where the other 3 fields are equal to the specific values.

I will put the finishing touches on this query tomorrow (if needed), and will update my entry if anything changed.

Selecting from SQL distinct

I'm learning all sorts of fun stuff with this catastrophic customer I'm working with.  Today, I learned how to SQL an AS400 table, and get only one occurrence of a certain field per record set.  For example, I needed to find out how many different values a certain field was possible of having depending on how other fields were set.  Here's the final product:

select distinct field1 from file1 where              
(field1,field2,field3) in   
(                                                       
select field1,field2,field3 
from file1 where field2='AAA' and field1 <> 'BBB'    
group by                                                
field1,field2,field3

In the above statement, the distinct keyword does the magic.  I'm joining the file1 with itself, where field2 is equal to AAA and field1 is equal to BBB.  If field 1 is set to AAA 300 times, it will be listed in the output only once.  It will also list each other value that is set to in the table one time, so that you can easily see how many different values can occur in the field in the table.  In my case, the file had over 700 million records, so it was a fast way to get the information I needed.  I hope this information was useful!    

Joining 2 files with same record format and field definitions

Recently, I have found myself dealing with a customer which has a highly customized software system running on their AS400, which has caused me much grief.  However, during this phase I have learned quite a bit imbedded RPG SQL, and basic SQL queries.  I am mainly writing this stuff down so that I will remember in the future.  I figured it might help someone else along the way, so here goes....

Today, I applied some queries to figure out some complex data scenarios in the system.  Basically,  I needed to join 2 files together to compare them.  The files had the same record format and field names (one was basically a copy of the other, but with a different AS400 file name).  In STRSQL on the AS400, I performed statements similar to the below:

select *                                             
from file1 a                                      
join file2 b on                                     
a.field1||a.field2||a.field3 =   
b.field1||b.field2||b.field3 where
a.field4 <> b.field4                                 
order by a.field1, a.field2                          

In the above statement, I used the identifier "a" for file1, and "b" for file2.  This allows SQL to recognize which file you are referencing in the other statements.  In plain English, the above statement will join file1 & file2 together where field1, field2, & field3 concatenated together is the same between both files.  Once the join is established, only records where field4 from the first file does not equal field4 from the 2nd file.  The concatenation of fields is a very powerfull tool I didn't realize existed until recently, so I hope that information is helpful to someone.