Is it possible on iSeries that an index is defined keeping in mind an sql select criteriea however when the queryis run this index does not get used.

1160 pts.
Tags:
IBM iSeries
Index Performance
iseries v5r4
SQL
SQL Select
Is it possible on i series that an index is defined keeping in mind an sql select criteriea however when the queryis run this index does not get used. We had an issue wherein an entire library had to be cleared and all files from had to be restored from a previous backup, after the backup was restored we found one of the sql queries taking much more time than before the restore , although all files and its logicals where restored.

 We ran dbmon on the sql job, and it suggested creation of few indexes which we created using names specified by us and using the recommeded fields. However when the sql was submitted we did not find any of the indexes created by us being used. Why is that?



Software/Hardware used:
os400, v5r4, i570

Answer Wiki

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

Hi JohnsonMumbai:

I do not know for sure since I had asked that question myself, but I think you should use the new index (which I assume is a logical file or view) in your query.

Then you can be sure it is used (specially if the query order matched the logical).

FlorenJM

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
  • MurrayInfoSys
    JohnsonMumbai - IBM has what I would call a Query Analyser. If you are able to, run the job in Debug mode with full job loging. It should tell you why it took the path that it did. I do this all the time with RPG/SQL programs. It can make a HUGE difference. Phil
    940 pointsBadges:
    report
  • TomLiotta
    Yes, it is possible. It is even likely. Most indexes are not used in most queries. You might have ten indexes over a table, but usually no more than one is used; and sometimes none are used. You might have an index over a column and reference the column in a WHERE or ORDER BY clause, and the index still might not be used. To find out why an index is used or not used, follow Phil's advice. Run the query in debug. SQL will log messages about the choices it makes when debug is active. iSeries Navigator also has a number of options that can help analyze how queries are run. Drill down into Databases and right-click on your system's database name. Select 'Run SQL Scripts...'. Review the menu options to see what's available. Pay special attention to the VisualExplain menu item. Tom
    125,585 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