I am working on a job that is taking more time in SQL only. I would like to reduce it a bit more. Is there anything that I can do other than binding the package with DEGREE(ANY).

Views: 78

Replies to This Discussion

Of Course... Please start doing an Explain. of your SQL statement. It'll show its access path. Then there are ways to tune it. Rewriting your SQL, Adding/Modifying Indexes etc.
On top of what Cuneyt wrote:
You need current RUNSTATS before EXPLAIN.
If you need more help - look at DB2-L listgroup and be ready to give the tables & indexes statistics + explain + sql.
One of the better techniques to try is one of the visual explain options, ranging from Optimization Service Center to Visual Explain. Often you can see what is happening. Many performance tools could help, if you have them.

Here are a few SQL performance rules:

Promote Stage 2’s/Residuals if possible
FOR z/OS:Value BETWEEN COL1 AND COL2 is Stage 2
Rewrite to: (Value >= COL1 AND value <= COL2)
COL NOT IN (K, S, T) = COL IN (known values)
SELECT only the columns needed
Disallow SELECT *
SELECT only the rows needed
Disallow program filtering
Use constants and literals if the values will not change in the next 3 years
Increase optimizer accuracy for static values
Do not SELECT columns with known static values
Disallow COLn from SELECT list if COLn = value

Once these are checked, use Optimization Service Center (free download from IBM) to:
Collect information regarding SQL
Format the SQL if it is messy
Get all physical designs of referenced objects
Check if statistics are missing
View Access path graphically or in PLAN_TABLE

Mentally compute, is access path efficient?

You may need to change the optimizer's mind.

Sheryl M. Larsen
Thank you for all your suggestions. Here is more information related to the application. We were trying to implement DB2 Spatial Plus to store the geographic maps. Looking at the internals of the Spatial I found that DB2 is using BLOB to store the map contents (polygons), and is also allowing to create an Index against this BLOB. After running the RUNSTATS and forcing the query to choose this BLOB index, application did perform a little faster but not much.


Featured Downloads

Try BLU Acceleration on Cloud

© 2019   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service