How to track OPNQRYF performance

695 pts.
Tags:
AS/400
OPNQRYF
Hi all, Recently i have done OPNQRYF on one transaction file which has around millions of records Program is taking same time to process as doing without OPNQRF. then what is the use of doing OPNQRYF. how can i track howmuch time OPNQRY has consumed. Regards, Mohan K

Answer Wiki

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

Mohan K

So I take it you got the open query working.

OPNQRYF simplifies the RPG,
1. you only see the records/fields you want.
2. it’s dynamic the Query Select can be built on the fly, or you can have different ones for different cases without having to alter the RPG
3. the open query can be used through a number of processes within the job

Since SQL was added to RPG we see less use of OPNQRYF but it does have some advantages
For speed, you might get a big improvedment with an SQL with the correct indexes. Of couse, this means your system is maintaining additional index(s).

If you could accept the date as the primary key you could drop the OPNQRYF and just use SETLL – Read Loop with an logical built on Year – Month – Day (whatever additional fields) looping until you reach a date that’s got the date > upper limit. Of course this means your system will be maintaining an additional logical.

Phil

Discuss This Question: 3  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Yorkshireman
    There can be nothing magical about SQL or OPNQRYF or RPG for that matter when accessing millions of records - with no design optimisation you have to start at the beginning, and move all the data through the program to get to the end. But as Phil says, if you think about which records are needed, you can design access paths to reduce the number of records accessed, and avoid the system building an access path each time. You asked how to find out these things - use the Performance Tools and collect performance data. Start with your RPG and a number of runs to get some baseline information. Use the OPNQRYF version, and collect data. See if it is processor or disk bound. Do the usual stuff depending on what it tells you. Avoid disk accesses, block files if you can, and so on. When you know what is causing delays, you can set about removing them.
    5,580 pointsBadges:
    report
  • Satsho
    Using indexes created over the files defined within the OPNQRYF statement helps improve the performance. I've also read that stopping the status messages also improves. Additional help -
    1,245 pointsBadges:
    report
  • Satsho
    Using indexes created over the files defined within the OPNQRYF statement helps improve the performance. I’ve also read that stopping the status messages also improves. Run this command before the query runs - CHGJOB STSMSG(*NONE). To reset it back - CHGJOB STSMSG(*NORMAL) Additional help - Link
    1,245 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following