"verbose" explain

Database
Enthusiast

"verbose" explain

Does anyone know how to do (for lack of a better term) a verbose explain? This will show extra information such as what fields the optimizer is choosing to redistribute on... I can't find this information in the documentation anywhere. Thanks a lot!!! --Todd

redistributed by hash code to all AMPs with hash fields (

"Spool_10.ORDER_NUM ,Spool_10.BUSINESS_UNIT_ID") into 28 hash join

partitions.

As apposed to the regular EXPLAIN:

redistributed by hash code to all AMPs into 27 hash join

partitions.

We're on V2R6.1
ODBC 3.05.00.05
6 REPLIES
Senior Apprentice

Re: "verbose" explain

Most people want the exact opposite of a verbose explain :-)

Try this, it's exactly what you're looking for:

DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION;

Dieter
Enthusiast

Re: "verbose" explain

I guess I'm a glutton for punishment. ;-)

That's great Dieter. Thank you so much!!!

--Todd
Enthusiast

Re: "verbose" explain

hi dieter,

Can you tell me how to use this command

DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION;

Suppose i want to check the verbose explain for a query should i give as,

select count(*) from somedb.XXXXX_table
DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION;

The above query gave error...I even tried several combinations and none worked. Can you tell me the exact syntax of it?
Senior Apprentice

Re: "verbose" explain

Hi Sakthi,
it's a separate command, just submit:
DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION;

Any explain after that will be "verbose" until you logoff:

Dieter
Enthusiast

Re: "verbose" explain

Hi Dieter,

That worked.
New learning through u :-)
Thank you

Regards,
Sakthi
Enthusiast

Re: "verbose" explain

Hi All,

can anone help me what is the key word Spool Asgnlist  in verbose explain plan.

Thanks,

Venky