IBM Mainframe Forum

Please suggest some tips for Performance Tuning the SQL queries in DB2

Hi Sandhya,

similar questions answered earlier in this forum, please go through those also.. you can find some info in the following link..

http://www.ibmmainframeforum.com/system-programming/topic475.html

Apart from the above, I have done performance tuning in my previous project which i am listing below..

1. Build indexes on tables whereever necessary if you are using search fields
2. Optimize the access path - Check with your DBA
3. look for unnecessary fetching of data by any query
4. WHERE clause in the queries is the place where you can improve performance a lot by giving appropriate values
5. Check for unnecessary loops related to data fetching
6. Check for data integrity
7. improper coding also sometimes cause for performance issues.. so code optimization is needed by a senior person

Hope this info helps...

Regards,
Jayind

Hello,

Please suggest some tips for Performance Tuning the SQL queries in DB2

Other than the very obvious (i.e. use a key to directly access data rather than a full table scan), production tuning involves identifying where time is being spent - then determining a better way to get the same result. Rather than waiting for productoin problems, this should be done as early as possible during development because often the "fix" requires some amount of re-design.

One of the biggest problems i see today is that many projects test with the barest possible data (which works acceptably) then when more realistic volumes are used, the unacceptable response time/run time is discovered.

To repeat: Tuning should not begin when there are critical production issues. . . Tuning should be part of the earliest design.

In addition to my eariler posting,

8. RUNSTATS is another performance improvement task
9. Clearing the logs/cache part of weekly maintenance

I will continue adding to it if I remember few more...

Regards,
jayind

In our project we are using dynamic SQL for retrieving the values and the condition for this SQL is set by the users in the front end.

In the Dynamic SQL the value in the where predicates will be assigned from the values passed through the front end
Select * from employee where
Name = '%%%%%%'
ID = '%%%%%' ;

This is causing problem when we dont specify any condition from the front end. It try getting all the values from the Table and it is creating a big perfrmamce issue..

Please let me know how can we procees with this..

Hello,

One thought is to reject the request if they do not provide some value.

The reason people do a blank search is either they dont know what value to enter, dont have information or just to see what values exist in the system...

I suggest

1. You can default values to the Name and ID if possible
2. Make those as mandatory fields on the screen and validate against the values entered


Regards,
jayind

Thanks

We have declared a cursor and doing some process based on the value retrieved from the cursor and in the downstream process updating a table.

For doing updation we are locking the table in Exclusive mode.

We are hitting contention error -911 SQL code during locking. The table locking will be tried for 100 times and it fails even after trying for 100 times it will error out. After trying it for 10 times it locked the table successfully it does the update and doing a commit.

When I again come back and try to fetch the next record i am hitting -501. I have used WITH HOLD option in the cursor to remain OPEN.

Please help me regarding this.

Hello,

Locking an entire table is not even permitted on many (most?) systems because of problems like you are experiencing and other impact problems. I've supported several hundred online/batch systems and none lock entire tables.

I'd suggest looking for a solution that locks only the needed rows and then only for a brief amount of time.

To do this may require considerable re-work. . .