Physical file over 600 000 000 records

AS/400 performance
Physical File

We have a Physical file which has more than 60 million records in it. In our application we search for the particular records based on 6 fields as a search criteria (web based) , search process are done in SQL Procedures. Even when i creatd a LF based on the mandatory search fields , we get  a timeout exception error.How can that be rectified.

!.Tried with Lf , not much difference.

2.Tried using fetching first 300 records also takes more time.

Can anyone help me out on how the search results can be made faster.

Thanks in advance

Software/Hardware used:
as400 SQL

Answer Wiki

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

You may want to investigate the STRDBMON command. This collects database access and performance statistics for the job(s) you specify, and its output goes to an outfile. The database manager may recommend creating a new access path, or index, for the file to improve performance. If it can’t use the existing ones that you have to retrieve the requested records, some explanation messages may also be in the job log.

Are you running a Select statement, or Update? If you run an update and are changing key fields, it’s possible that the SQL engine is not able to use any of your existing access paths if they contain those key fields, in order to avoid a never ending loop. Thus, an index which doesn’t contain those fields in the key may be necessary. The database manager will tell you what it recommends.


Discuss This Question: 5  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.
  • Kccrosser
    The overall size of the database is not usually what causes a problem. It is usually how many records your query is returning and whether those records can be efficiently selected via an index. If the number of records meeting the query criteria is very large, you could just be running out of time in the return of the data. If the number of records being returned is small, but there is no useful index to find the records, the timeout is because it is taking too long to find the records. If you are always searching with some "mandatory" search fields, then your indexes should always include them. For sake of argument assume that the six criteria fields are F1, F2, ... F6, and the "mandatory" fields are F3 and F5 (i.e., these values are ALWAYS required as part of the search). Then I would create multiple "covering" indexes, like: create index F351 on <table>(F3, F5, F1) create index F356 on <table>(F3, F5, F6) When a query includes either F1 or F6 (plus F3 and F5 of course), the query optimizer should choose the appropriate index for the search. Now - some other considerations... Your index fields should be "discriminant" - i.e., the range of possible values in the field should be relatively large. For example, an index on Sex is often useless - in a 60 Million row table, and index on Sex might only split the table in half. An index such as that is non-discriminant and will generally be ignored by a good query optimizer. Second, when creating multi-column indexes, the order of the fields is usually important, with the most discriminant fields listed first. For example, assume we have a table of LastName, FirstName, DateOfBirth, ResidenceState, ResidenceZipCode. In the U.S., there are about 60,000 different last names, only around 5,000 different first names, and 50 different states. There are theoretically 100,000 Zip codes, but in practice there are a lot less than that. There are 365 possible DOB values per year, so for a database of U.S. individuals there could easily be 20,000+ distinct DOB values. With that kind of distribution, the most effective indexes would be: (Zip, LastName, DOB, FirstName) (LastName, DOB, FirstName) -- if Zip isn't mandatory Note that creating six different indexes is NOT very efficient, since the most efficient single-column index (ZIP) is still going to find at least 300 persons, and a search by State would return possibly more than 120,000 records. Even a search by first name would likely return over 1000 records, and a search by last name could return a huge set if the name is a common one.
    3,830 pointsBadges:
  • ITTechPT
    Is it possible to divide the data file in 4 physical files each one with 150,000,000 records? The ideia is to creat 4 PF with no records and with sql insert 25% of the main file in each new single file. Then search via an index in each file that have less records in overall. This is just an ideia if you can create new PF, maintaining a backup of the original one.
    95 pointsBadges:
  • philpl1jb
    You will find that performance is improved by creating the correct SQL indexes and selecting data from the parent file. The index will need to be a composite of the fields used in the where clause and the STRDBMON should give you info on what indexes to create. With the correct indexes in place, the system will be performing a binary search of the index which is very fast. Of course if your equipment is undersized or overutilized you will need more horsepower to get good preformance.
    54,090 pointsBadges:
  • TomLiotta
    Can you show an example of a WHERE clause that gets processed? Depending on the kinds of searches, different results might be influenced in different ways. For example, {COL1 like '%ABC%' } would need a treatment different from {COL1 > 'ABC'} or {COL1 in('X','Y','Z')}. Tom
    125,585 pointsBadges:
  • graybeard52
    Run the query thru Visual Explain. It will suggest indexes if needed, and alos let you know if the SQE engine or the CQE engine is used. Also, check that file QUSRSYS/QAQQINI has IGNORE_DERIVED_INDEX set to *YES.
    3,115 pointsBadges:

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.


Share this item with your network: