IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

User Remarks in Plan_Table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Auryn

New User


Joined: 11 Jan 2006
Posts: 67
Location: Lower Saxony (DE)

PostPosted: Fri Oct 26, 2018 2:30 pm
Reply with quote

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
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1206
Location: Bangalore,India

PostPosted: Thu Nov 22, 2018 9:59 am
Reply with quote

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
View user's profile Send private message
Auryn

New User


Joined: 11 Jan 2006
Posts: 67
Location: Lower Saxony (DE)

PostPosted: Wed Jan 09, 2019 6:23 pm
Reply with quote

@ 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.« icon_eek.gif

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...« icon_sad.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Revoke on DB2 to remove an user DB2 4
This topic is locked: you cannot edit posts or make replies. Is there a way to restrict user login... All Other Mainframe Topics 11
No new posts DITTO/ESA R3 User's Guide and Referen... IBM Tools 2
No new posts conversational program not reading t... IMS DB/DC 2
No new posts User question - dbzTHEdinosaur General Talk & Fun Stuff 2
Search our Forums:

Back to Top