I have written about using the Relative Record Number, RRN, in RPG and said that I could not think of a reason why I would ever need to use RRN in one of my programs. Well, I have to take that statement back as I recently found a really good reason for using it. I needed a quick way to retrieve the last record from several different "flat files", and check if it started with the characters "END:". I could do it in RPG, but what is the fun with doing that when I could do the same using SQL?
What is my definition of what a "flat file" is? See here.
I am not describe these "flat files" as it is not really relevant to what I am going to show. I will say that I had a variety of these files from several sources, the layout of the contents is different, and the maximum record lengths are different to. They all have the following in common:
- The columns within are all CSV, Comma Separated Variable length.
- They all contain three different record types:
- One header record, HDR:, as the first record.
- One or more detail records.
- One end record, END:, as the last record.
I want to create one program that would be able to check if the last record started with "END:", if it does not I do not have a complete file. I want to do is pass the name of the file to the program, and return if an end record was found. As the program I am going to show is just an example, rather than return a found/not found value, I am going to display the number of records in the file and the first four characters of the record instead.
SQL has a RRN function that will return the RRN of the row retrieved, and I can use this to retrieve the last record in the file.
01 dcl-s FileName char(10) ;
02 dcl-s RRN int(10) ;
03 dcl-s Row char(1000) ;
04 dcl-s String char(100) ;
05 String = 'CREATE ALIAS QTEMP.SOME_ALIAS FOR ' + FileName ;
06 exec sql EXECUTE IMMEDIATE :String ;
07 exec sql SELECT RRN(A),A.*
08 INTO :RRN,:Row
09 FROM QTEMP.SOME_ALIAS A
10 ORDER BY RRN(A) DESC
11 FETCH FIRST ROW ONLY ;
12 exec sql DROP ALIAS QTEMP.SOME_ALIAS ;
13 dsply (%triml(FileName) + ': +
Value=<' + %subst(Row:1:4) + '> +
RRN=<' + %trim(%editc(RRN:'J')) + '>') ;
|
Lines 1 – 4: These are the definitions of the variables I will be using in this program.
Lines 5 and 6: I thought about how I could handle multiple files, it dawned on me that I could use the CREATE ALIAS statement, which I have used for multiple member files, and if I do not give a member name the first member in the file is used for the Alias. I make my CREATE ALIAS statement in a variable, line 5, and then execute the statement in that variable using the EXECUTE IMMEDIATE statement to create the Alias. Notice that I created the Alias in QTEMP, because we should all use QTEMP for our work files, Aliases, etc.
Lines 7 – 11: Now I am going to get the last record and its RRN.
Line 7: I am selecting the RRN using the RRN function and all the fields from my file, I say "all fields" in reality it is just one field whose name is differs depending upon the "flat file".
Line 8: I am placing these retrieve values into two variables. As the "flat file" contains only one field I can place it in just one variable.
Line 9: I am retrieving this data from the Alias I previously created.
Line 10: If I sort the contents of the file by the RRN in descending order then the last record will be retrieved first.
Line 11: I only want to retrieve one row/record, which is the last record in the "flat flat".
Line 12: As I am finished using the Alias I delete it.
Line 13: Using the DSPLY operation code I can make a string and display it. All the fields I substring together must be character, therefore, I need to convert the numeric value in the variable RRN to character. If I use the %EDITC built in function it will convert the number to character applying edit code J to it. I need to trim the result to remove the leading blanks and the blank where the negative sign would be from the end.
When I run this over five different "flat files" I get the following results.
DSPLY TFRFILE001: Value=<END:> RRN=<16> DSPLY TFRFILE002: Value=<END:> RRN=<212> DSPLY TFRFILE003: Value=<END:> RRN=<42,614> DSPLY TFRFILE004: Value=<END:> RRN=<439> DSPLY TFRFILE005: Value=<3,"1> RRN=<4> |
The first four files are all complete. As the fifth file does not end with an "END:" record it is not complete, and I need to get a new version this file sent from the vendor.
I could also use the RRN to retrieve where in a file a particular record is. For example:
dcl-s RRN int(10) ;
exec sql SELECT RRN(A)
INTO :RRN
FROM ORDHDR A
WHERE ORDNBR = '0425213' ;
dsply ('RRN=<' + %trim(%editc(RRN:'J')) + '>') ;
|
When this program it called the following is displayed:
DSPLY RRN=<1,171> |
While I will be using the RRN function to determine if the last record, I am not sure if I would use it for anything else. Do you use the RRN? If so to do what and how?
You can learn more about SQL's RRN function from the IBM website here.
This article was written for IBM i 7.3, and should work for earlier releases too.
What is a flat file?
Let me quickly define what I consider a "flat file" to be. It is a file that has no field definitions. Its file name, record format, and field name are all the same. If I was to create one I would use the following command:
CRTPF FILE(SOME_LIB/FLATFILE) RCDLEN(1000) |
I normally use them when I am transferring data from another data source in a CSV format, or something similar.
HDR: 1,"G2025","PAPER TOWEL SINGLE ROLL",1.0892 10,"G2059","USA MADE STEEL THREAD 12 MICRON",212.2264 100,"G2061","LOCTITE GLUE 19ML TWIN PACK",5.8564 END: ****** END OF DATA ****** |
What about deletes and reusing deleted records? Could you delete a record (lets say rrn = 51) in a file with 123 records so max rrn = 123 and then use that deleted record (lets say rrn = 51) as the last record inserted to the file?
ReplyDeleteYou're a powerful and talented man, Simon. That's a great technique. Thanks.
ReplyDeletePersonally, I prefer to use the row_number() function instead of RRN() since the latter is not always a sequential integer. Try it out!
ReplyDeleteSELECT row_number() over () as rowNum,
personID,
firstName,
lastName,
birthDate
FROM MySchema.MyTable
order by rowNum desc
fetch first row only
As an aside, ROW_NUMBER() OVER() does not guarantee to build a running number over the table, the query optimizer might use an index and then the running number is built over the key sequence
DeleteBirgitta
@Birgitta; Thank you for the clarification.
DeleteI've used SELECT * FROM myfile WHERE RRN(myfile) = 12345 when I want to see the specific record that is shown in a job's open files. Have also used it when I wanted to create a sample output file (every 100th record): CREATE TABLE mylib/myfile
ReplyDeleteAS
(SELECT * FROM yourlib/yourfile
WHERE MOD((RRN(yourfile),100) = 0) with data
As an aside before relase 7.1 or when the CQE must be used a tables scan is performed to find the relative record no. This is no problem for a table with 500 rows, but becomes a performance problem for a 500 Billion row table.
DeleteMeanwhile values lists are used to find the relative record no which performs much faster.
... and since Release 7.1 TR 5 it is also possible to create an index over the relative record no.
Hi, may i know how can i create index on a physical file's rrn?
DeleteCREATE INDEX QTEMP.INDEX1
DeleteON file name
(RRN(file name))
Thanks Simon. I have a query that runs on RRN, it's a range query and it refuses to use the RRN index.
DeleteSomething like this
Select a, b, c, from amcmst where rrn(amcmst) >= 1 and rrn(amcmst) <= 13678
I have index created
Create index amcmst 01 on amcmst (rrn(amcmst))
Fetch statement
Exec SQl fetch relative :wrrn from c1 for :hdnrec into :dswrow
Hdnrec 999 rows
The above execution won't trigger index
Yes, it is a design "oversight" with Db2 for i and Query that you cannot query an Index.
DeleteRetrieve the SQL from the Query and make a SQLRPGLE to do what you need.
Getting the SQL statement out of Query/400
I have used rrn to help me delete duplicates. I find my duplicates then I delete the ones that have the higher rrn.
ReplyDeleteThank you
ReplyDeleteHi Simon, Nice blog, I have a small request, can we select records based on RRN using WRKQRY.
ReplyDeleteQuery does not have the ability to use RRN.
DeleteThe only suggestion I have is to build a work file using the technique described above and then use Query on the work file.
I've used RRN to help quickly generate files for performance testing with a unique key:
ReplyDeleteCREATE TABLE T ( A INT)
INSERT INTO T VALUES(1)
INSERT INTO T SELECT * FROM T -- Repeat this to double number of records to get as large as you want.
UPDATE T SET A = RRN(T)
-- Then either add a unique constraint or copy file to a similar file but with unique key defined.
Simon, thanks for sharing, retrieval of a record by the RRN, is a very good ideal sometimes. Thanks for another learning moment.
ReplyDelete