|
View previous topic :: View next topic
|
| Author |
Message |
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
Hi there everybody,
analyzing and explaining access paths is a very helpful feature and I use it a lot.
It’s easy to explain any SQL statements in a program by using the “EXPLAIN(YES)” clause when (re-) binding a package.
And It’s easy as well using the EXPLAIN ALL (SET QUERYNO = 12345) FOR statement for analyzing a single spufi query.
Particularly the second method I use a lot.
Some days ago – when creating a Plan_Table (with its tablespace, its indexes etc.), I recognized there is a remarks column in this table. According to the manuals this column is sometimes used when there are some ‘certain situations’ during package bind. But the manuals say furthermore the column can be used for ‘my own remarks’:
| Quote: |
| A field into which you can insert any character string... |
Is there a special statement / clause / option / parameter to store user remarks in this column (when explaining a single query)?
Or is the only way to do so by manually updating the column after storing the explain results? This would be very awkward ‘cause first I would have to determine the keys / EXPLAIN_TIME timestamp for updating (only) the relevant rows.
IMO, the most elegant way would be a phrasing like
| Code: |
EXPLAIN ALL
SET QUERYNO = 12345
, REMARKS = ‘The query I am examine’
FOR … |
But I guess, a statement options like this might be an illusion… :-(
Thank you very much for your hints, ideas, ... |
|
| Back to top |
|
 |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello There,
I just read it from Manual .
| Quote: |
Db2 inserts a value into the REMARKS column at bind or rebind when the EXPLAIN(ONLY) option is specified and reuse or comparison fails for an access path. The value might include the following information:
A reason code that corresponds to the reason codes in SQLCODE +395 when reuse fails
The name of the unmatched PLAN_TABLE column for which comparison failed
A string that identifies that unmatched rows where found
Db2 inserts a value into the REMARKS column when selectivity overrides cannot be used for a statement . The value contains a reason code that indicates why the selectivity override was not used. The value might also contain additional diagnostic information.
The reason code values correspond to SQLCODE +395 reason codes:
'1'-'41'
Indicate that an optimization hint that was generated as part of the extended optimization process cannot be applied. Use only a single selectivity instance.
'42'
Indicates that the structure of the selectivity override is not valid. Generate the selectivity override again.
'43'
The selectivity override cannot be applied because of an unexpected error. If the problem persists, you might need to contact IBM Software Support.
'44'-'99'
Indicate that an optimization hint that was generated as part of the extended optimization process cannot be applied. Use only a single selectivity instance. |
|
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
@ guptae:
Thank you first for your answer.
But this is exactly what I meant with »According to the manuals this column is sometimes used when there are some ‘certain situations’ during package bind.« 
But it does not answering the question »But the manuals say furthermore the column can be used for ‘my own remarks’: A field into which you can insert any character string...«  |
|
| Back to top |
|
|
|
|
