How to reduce the time taken to select the records from huge keyed physical file having 180 Million records

695 pts.
Tags:
AS400 Command
DB2/400
Physical File
Hi All, I have a report program which uses the OPNQRYF to select the records to generate a report based on user selection in display screen, but it is taking lot of time (20 to 30 minuates) to select the records and write write into output file. I am accessing keyed logical file based on physical which contains 180 Million records. Can anyone please suggest any ideas to reduce the time taken to select the records from this huge input file to generate the output extract. your help is greatly appreaciated.

Answer Wiki

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

It’s almost impossible to troubleshoot something like this in a vacuum… to give a good answer we would need to know the layout of the table(s) involved and the indexes for starters. But there are a couple of things to consider

Like @TomLiotta said stop using OPNQRYF. It’s old and it does not take advantage of the SQL Query Engine (SQE) on the system, there are just too many benefits to using the SQE to cover them here… check out these articles:

http://www.as400tutorials.com/start-saving-time-today-with-sql/
A good article by Kent Milligan:
http://www.mcpressonline.com/database/db2/get-your-system-humming-7-great-tips-for-tuning-db2-for-i.html

Also gathering some statistics with STRDBMON will at least help identify potential indexes to be built.

-John Andersen

Discuss This Question: 15  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
  • TomLiotta
    Can anyone please suggest any ideas to reduce the time...   The thought that comes immediately to mind is to stop using OPNQRYF for this process; use SQL. And this seems much too large to be creating access paths at run time.   Also, if you already have a keyed logical file for access, why would you use OPNQRYF in the first place? If you can explain that part, it might be easier to come up with a full suggestion.   Tom
    125,585 pointsBadges:
    report
  • ToddN2000
    Just curious as to what the parameters are they are searching for. We had a similar situation on a large file. The were a lot of common parameters being used so we created a few new logical files with keys based on their search fields. You can even add select/omit criteria to streamline it further What took just over 10 minutes is down to about 10 seconds. Just a suggestion. 
    11,170 pointsBadges:
    report
  • Mohan K
    Hi Tom and others, Thanks for your suggestions, Here is the scenario in my program, User is allowed to select the period, country and 3 more fileds on screen to pull the report, So in our existing program we have Opnqryf to select the records from master file(Keyed logical file) and joining other files also to pull the records based on screen selection and then we are reading the resultant records from above query in do while loop and apply some more business conditions to filter the records and write the final output to file and create a CSV formatted file and send it to user as an attachment. Program is taking time to select the records at OPNQRYF, I am not allowed to change the entire structure of the program, since it is very important report. so i am thinking of putting some small patch to the existing program to reduce the time taken to select the records.    
    695 pointsBadges:
    report
  • TomLiotta
    I am not allowed to change the entire structure of the program, since it is very important report.   If you can't change the programming, then you probably can't change anything that will make much difference. Tell your manager that nothing can be done as long as the programming can't be fixed.   What are the other three fields that a user can enter?   Tom
    125,585 pointsBadges:
    report
  • ToddN2000
    What are the other 3 fields and business conditions being applied? It my be that is where the bottleneck in processing the data is. Having never worked with a file that large myself I am curious as to the scope of the file. For example our sales detail file has just under 5 million records going back to 1998 for a company that does approx 265 mill a year.
    11,170 pointsBadges:
    report
  • TomLiotta
    The period and country fields might be good candidates for EVIs. The other three fields are total unknowns, but any one of them might also be an EVI candidate. EVIs are potential ways to significantly speed up the process.   Of course, it's not at all clear how OPNQRYF fits with "accessing keyed logical file" unless the LF is effectively being created by the OPNQRYF. An existing LF wouldn't seem to be useful at all except potentially for the initial compile. Without seeing any programming, it's pure guesswork.   As for the file size, I've seen much larger files for businesses smaller than $265M/yr. There are many reasons for larger files.   Tom
    125,585 pointsBadges:
    report
  • rajeshece
    Using Sql is the best option. Or Create logical file based on some known selection creteria and use sql on that logical file
    1,215 pointsBadges:
    report
  • rajeshece
    Have 1 point. Create a logical File with multiple members. Arrange the date.  you just select the correct member based on the User's input. Read that Member and Filter the Records Eg:  Data should be arranged based on period and Country. So you can select the members which is related to res.country and period. then start filtering based on remaining 3 fields. PLs suggest will it work.
    1,215 pointsBadges:
    report
  • philpl1jb
    The fact that it's a keyed file doesn't help when the selection is not by the key or is a range or list.  You may need to build this cl using different Logicals for different selection rules so the query engine doesn't have to seach the entire data set.
    49,950 pointsBadges:
    report
  • TomLiotta
    PLs suggest will it work.   It can be made to work, but it's more difficult than simply indexing by the appropriate columns. It could also be significantly slower.   Tom
    125,585 pointsBadges:
    report
  • nuccio
    Are you using OPNQRYF directly to read and write in printer file or CPYFRMQRYF ?The use of OPNQRYF can be optimized in various mode and it is particularly useful in programs because it allows you to use the normal definitions of files. The first thing to be done is reduce the number of fields in base to the real necessity, using a proper format, than use logical files only if correspond in arrangement  with OPNQRYF, otherwise  use physical file.
    290 pointsBadges:
    report
  • TomLiotta
    The use of OPNQRYF can be optimized...   That's true.   The use of plutonium for generating heat can also be optimized, but that doesn't mean that a block of plutonium makes a good heater for my living room.   So far, the OP
    125,585 pointsBadges:
    report
  • TomLiotta
    (Previous comment was truncated on input...)   So far, the OP hasn't told us anything useful about the table structure nor answered questions about the other three values. We don't know what records look like. We haven't even seen the OPNQRYF command yet.   The target program has a trivial description and sounds easy. It almost seems like it could be replaced by a single Qshell db2 statement. It's hard to tell how it could be so difficult to change.   As far as we know, the OPNQRYF is already optimized to maximum possible degree. Maybe "20-30 minutes" is the best possible time on that system for that table.   Tom
    125,585 pointsBadges:
    report
  • graybeard52
    One more suggestion- do the query directly over the PF.  Building a query over a LF is usually much slower.
    3,115 pointsBadges:
    report
  • philpl1jb
    block of plutonium leading to truncation?   Certainly words to wake up homeland security.
    49,950 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