iSeries query for most recent data stored

5 pts.
Tags:
AS 400
iSeries
How do I write a query on the iSeries to return the record with the most recent data stored?

Answer Wiki

Thanks. We'll let you know when a new response is added.

A lot depends on how your file is currently defined and how you are going to access it. Unless you have a date/time stamp for a SQL type query then the only thing you can do is base it on is the latest record by RRN and retrieve via an RPGLE program. More details would help provide a better answer.

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Splat
    One caveat: RRN isn't much help identifying the most recent record added if your file has the REUSEDLT attribute of *YES.
    12,450 pointsBadges:
    report
  • ToddN2000
    @splat: so true on that REUSEDLT.. I think the default is *NO. Which has saved us in the past being able to recover deleted records (deleted records still take up space in the file).  

    If it was *YES then it would eliminate the need to do a RGZPFM on those files with a lot of add/delete activity. The RGZPFM requires the file to be in a restricted state and that may not be easy to schedule if you run a 24/7 shop.
    102,585 pointsBadges:
    report
  • RossHowatson
    The default settings for creating a file(table) with DDS (CRTPF) is reuse deleted records (*NO). The default settings for creating a table(file) with SQL (CREATE TABLE) is reuse deleted records (*YES). Use DSPFD for the file and scan for the string "Reuse deleted records" and this will tell you if you reuse deleted records. Query/400 does not allow you to select the last N records in a file(table). SQL does BUT if the table is set up to reuse deleted records then you cannot determine where the new records were placed in the table. Look for a date added within the file(table) layout is your only recourse. If necessary convert the date added to a YYYY-MM-DD format within query.  Use DIGITS(dateField) and SUBSTR(dateField,N,N1) || "-" || SUBSTR(dateField,N2,N3) || "-" || SUBSTR(dateField,N4,N5) to convert to a YYYY-MM-DD format string. This allows you to search based upon a range (i.e. CVTDATE RANGE '2017-12-01' '2017-12-31'. The RANGE values can be dynamically created field names so you can even set the query up to always run with a dates of either this month or last month. For the end of the month just hard code '31' and for months with less than 31 days it does not care ("2017-02-28"and "2017-02-31" will not cascade over to March 3rd (it is a text string you are working with not a true date field).
    540 pointsBadges:
    report
  • Splat
    @ToddN2000, I make use of REUSEDLT(*YES) rather a lot. We're a 24/7 shop with limited opportunities to run file reorganisations & I have a visceral objection to a file where 1/4 to 1/3 of the records are deleted.
    12,450 pointsBadges:
    report
  • ToddN2000
    @splat: I hear you there. Our purchased software package has a few files that are set to *no and should be *yes.  Things like Order in use or PO in use files. The app writes the order# / po# to the file when opened to show it's in use then deletes it when closed. You may have 10 - 15 active records and 200,000 + deleted..
    Bad database design in my opinion. Either flag the record in the header files or set the reuse flag. It's a no brainer.
    102,585 pointsBadges:
    report
  • Splat
    @ToddN2000, I'd call that bad process design but in either category it's just a waste of resources.
    12,450 pointsBadges:
    report
  • GregManzo
    Assuming your file has some form of timestamp field of when the record was written/maintained (and if it's important to see the latest data then it should), then you can do something like this:
    select * from FILE order by TimeStamp desc fetch first n rows.
    or the RPG equivalent. Build an access path over the timestamp field.
    2,515 pointsBadges:
    report
  • GregManzo
    @Todd: We use those little 'in use' files a lot. Write the key to the order/pickslip/whatever into a file, then re-chain & lock the record to show that it's in use. That way we don't need to lock or update the actual header record. There are a few other things to keep in mind too:
    a) Reuse deleted records = yes (the contents of the file are irrelevant, only the record lock is important).
    b) Do not use journalling or commit on that file (this way you can issue a commit to your database without dropping the lock on your record).
    c) Schedule a CLRPFM / RGZPFM at regular intervals during quiet times or just after an IPL.
    d) If a job ends unexpectedly, the lock is dropped for you. :-)
    2,515 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: