Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Embedded SQL in COBOL ILE

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.


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)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.