RE: [sqr-users] DB2 Optimizer question

["Kutz, Frederick" <fkutz@surebridge.com>]

Thanks!!!!

The key was putting the change to the optimization in the begin-setup.
Now it works great.  You are a lifesaver!

Fred...

-----Original Message-----
From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org] On
Behalf Of Peter Burton
Sent: Friday, January 30, 2004 5:33 PM
To: 'sqr-users@sqrug.org'
Subject: RE: [sqr-users] DB2 Optimizer question


Fred,

Run this "SQR {Program} {Connectivity}" as you will see the default
level (in my case a 5).  Running this as "SQR {Program} {Connectivity}
-DEBUG" will show the level as a "3".  Therefore, setting the level
within a Begin-SQL block affects the program as a whole.

                #ifDef Debug
                Begin-Setup
                 Begin-SQL
                  set current query optimization 3
                 End-SQL
                End-Setup
                #EndIf

                Begin-Report
                Move 'current query optimization' To $C
                Begin-Select
                [$C] &cqo=number
                 From Dual
                End-Select
                 Show &cqo
                End-Report

Peter

> -----Original Message-----
> From: sqr-users-admin@sqrug.org@HYPERION  On Behalf Of "Kutz,
Frederick"
> <fkutz@surebridge.com>
> Sent: Friday, January 30, 2004 4:18 PM
> To:   sqr-users@sqrug.org
> Subject:      [sqr-users] DB2 Optimizer question
> 
> I'm an old hat at basic SQR using older versions, but new to DB2, as 
> well as new to the latest versions of SQR.
> 
> We have some SQR reports that are running dog slow, because the SQL is

> complex enough to give the DB2 optmizer too many choices.  So we spend

> hours waiting for it to figure out the right "path" to execute, and 
> seconds actually getting results.  The same SQL statements could be 
> compiled and run in a few seconds, if the optimizer level was set 
> lower.
> 
> My question is pretty basic.  Any idea how to do this in an SQR?
> 
> I tried setting the optimizer level in a SQL block, but I fear that it

> only applies to one cursor at a time.  I also tried using the old 
> syntax "begin-sql -cx" (x being a cursor number), and using the same 
> thing in the begin-select, in hopes of using the same cursor with the 
> optimizer level being set on it.  I just got back an error though.
> 
> Other suggestions?
> 
> Fred...
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users
> 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users