I need a little help with V5R2. I have questions regarding embedded SQL in COBOL ILE; the Query Optimizer used all Indexes to Optimize SQL.
1. Index was created to improve performance for simple (only one file) SQL SELECT statement (Index Keys are matches selection criteria in WHERE)
This SQL performance was improved significantly (10 times)
2. Some other jobs started to use this new index (we checked Access Plan through PRTSQLINF) and performance of these jobs become very bad. (SQL Select in these jobs is more complex -- 11 files, selection criteria is also complex) Total execution time in these jobs become to be 10-20 times greater then before creating this new index.)
Question: Do we need PTF or it is how Query Optimizer is suppose to work? Do we need to optimize these jobs (at least 2) to improve performance?
In other words, is it true that any index could potentially make performance very bad for some jobs?
No, creating a new index should not negatively impact the performance of other jobs. First you should verify that the latest Database Group PTF is applied on your system -- if not, get to the most current level. Next you should review the indexes that are defined over the tables involved to ensure that the best sets of indexes are defined. Please read the following "Indexing Strategy" white paper to help with the review.
MORE INFORMATION ON THIS TOPIC
Check out this Search400.com Featured Topic: Database issues resolved
Search400.com's targeted search engine: Get relevant information on DB2/400.
The Best Web Links: Tips, tutorials and more.
Dig Deeper on DB2 UDB (universal databases)
Related Q&A from Kent Milligan
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ... Continue Reading
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading
When developing tables in a parent-child relationship, use a primary key and a foreign key along with a unique ID to make your database easier to ... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.