Performance issue

20 pts.
Tags:
Application development
AS/400
AS/400 careers
Backup & recovery
CLP
DataCenter
DB2 Universal Database
Email
IBM Marketing Sound Off
PC/Windows Connectivity
Printing
RPG
RPGLE
Security
Web development
Hi I am using an OPNQRYF in a program which is used quite frequently. Now, the program takes about 20 secs when called once. I tried to create some logicals which can expedite the access to files but there has not been any appreciable change in the performance. Can anyone suggest any other method which will quicken the file access process?

Answer Wiki

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

I normally work with SQL, but, the same tactics should apply to query as well. In SQL if you do a STRDBG before opening SQL, you can view the job log to see any temporary access paths that the system built, as well as what logicals it considered, which it used, and why it ruled out the rest. Without specifics on what you’re querying, and how data joins together it’s a bit hard to offer concrete suggestions, although, if you are using computed, trimmed, or substringed fields in a join, things can get very kludgy very fast.

Tuning slow queries can be very useful, I’ve cut 50-75% off of your normal system usage just by consistantly going after any heavy hitters that show up on the system, and building the right logicals when possible, or fixing faulty logic in queries when not.

A last resort that has often worked for me. for complicated queries with a lot of joins, you can often speed things considerably by breaking the query into steps. but, indexing, or rewriting is preferred.

==========================================================

If OPNQRYF is performing too slowly, then don’t use OPNQRYF. That command is going to build an access path when it runs. If that’s too slow, then build the access path by creating a LF and then using the LF in your program. Or use SQL instead — use debug during development to guide creation of supporting indexes.

OPNQRYF has uses. E.g., it can create access paths with compound keys that include components from multiple physical files; and, AFAIK, there are no other tools that can do that.

But basic selection, dynamic or otherwise, is long since done better in other ways.

Tom

Discuss This Question: 2  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
  • TimRac
    Depending on the access needed, in the OVRDBF before the OPNQRYF, in addition to the SHARE(*YES), override to the logical that will provide the access needed. What the user selected to do should decide how the file should be overridden.
    25 pointsBadges:
    report
  • JPLamontre
    try to check your query (in sql syntax) with the visual performance analyser in iseries navigators. Usefull & efficient. details ? look at http://jplamontre.free.fr/AS400/SqlExplain.htm
    0 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