Ingres and OpenIngres Trace Points
                                                               

IMPORTANT -- The information contained here has been collected from a number of
sources who all say, repeatedly, do not use any trace points unless you know
what you are doing. They are NOT SUPPORTED by Computer Associates and may cause
irreversible damage to your system. Use at your own risk. 

LESS IMPORTANT -- Where possible, the versions of Ingres and OpenIngres for
which the trace points are valid are listed. Other versions may use the trace 
point but, perhaps for entirely different purposes. Trace points are typically
used by CA technical support and are not supported; i.e., they're for their
internal use only. If you find any errors here or would like to complete an
entry, please send email to IDEAMEN@WELL.COM. 

SPECIAL THANKS TO: Karl Schendel, Sten Selin, Robert Weber, Nick Wadge and
others for creating and contributing to this list. 

                                                               

    DM0001 - same as 'set lock_trace'.
    DM0010 - same as 'set io_trace'.

    DM11 - Dump DMF control blocks to trace file immediately; needs DM100
	turned on first (Ingres 6.4/05 /02) 
    DM100 - Enable/disable stuff (?) eg DM11 (Ingres 6.4/05 /02) 
    DM101 - Dump DMF stuff immediately, like DM11 but doesn't need DM100. 

    DM420 - Output DMF buffer cache stats to iidbms.log immedidately. Buffer
	management performance statistics. Use set trace output 'filename' 
	first (Ingres 6.4/05 /02; works fine on 6.4/05 vax.vms/00; unreliable 
	on 6.4/04 hp8.us5/00; does not work on 6.4/04 su4.us5/02)
    DM421 - Flush DMF buffer cache immediately (Ingres 6.4/05 /02)

    DM501 - Simple sort statistics, OI, to log, big sorts only?
	(Ingres 6.4/05 /02) 
    DM550 - "allows select * from bad btree, displays tid slot/posn info". Not
	confirmed. (Ingres 6.4/05 /02) 

    DM600..DM610 -- "BTREE" functions: All, Get, Del, Put, Replace, Split,
	Merge, Extend, Allocate, Search, Build. Exact use is unknown.
	(Ingres 6.4/05 /02) 
    DM612 - BTREE internal error additions, server wide, slight performance
	penalty (Ingres 6.4). 
    DM614 - Dump page to II_DBMS_LOG if corruption detected, server wide
	(Ingres 6.4). 
    DM615 - [baseId] [indexId] Dump index to II_DBMS_LOG. Get ids from
	iifile_info; baseId is enough if btree base table (Ingres 6.4/05 /02) 
    DM621..DM627 - Hash/Isam/Seq Get, Del, Put, Replace, Extend, Allocate,
	Search. Exact way to use them is unclear (Ingres 6.4/05 /02) 

    DM801 - DMF will skip bad rows (e.g., to allow modify of corrupted table). 
    DM802 - DMF will return garbage for bad rows (e.g., to allow deleting them)
	(Ingres 6.4/05 /02) 

    DM11xx -- Various DMF "crash on event", eg 1101 Btree delete secondary
	index, crash before leaf written. (Ingres 6.4/05 /02) 

    DM1200 - All call (?). 
    DM1202 - Call return status; (none of the DM12xx seem to do anything under
	Ingres 6.4/05)
    DM1203 - Call input parameters; (none of the DM12xx seem to do anything
	under Ingres 6.4/05)
    DM1204 - Call output parameters; (none of the DM12xx seem to do anything
	under Ingres 6.4/05) 
    DM1210 - Call timing enable; (none of the DM12xx seem to do anything under
	Ingres 6.4/05) 
    DM1211 - Call timing display; (none of the DM12xx seem to do anything under
	Ingres 6.4/05) 
    DM1212 - Call histogram timing display. 
    DM1213 - Call timing display; (none of the DM12xx seem to do anything under
	Ingres 6.4/05) 
    DM1220 - Call memory checking; (none of the DM12xx seem to do anything
	under Ingres 6.4/05) 

    DM1301 - fast-commit thread statistics. Writes to II_DBMS_LOG when a
	consistency point runs (Ingres 6.4/05/02) 
    DM1302 - write-behind thread statistics. Writes to II_DBMS_LOG when a
	consistency point runs (Ingres 6.4/05/02)
    DM1303 - consistency point EOF statistics. Writes to II_DBMS_LOG when a
	consistency point runs (Ingres 6.4/05/02) 
    DM1305 - Force a consistency point. 

    DM1420 - crashes logging system. 
    DM1440 - Show work (ie, sort) locations immediately; used to see if
	multisort location works. 

    OP129 - Dump out query tree; the following two flags are related and
	provide information on the building of the variable map for a query
	tree. This variable map is a bit vector which represents which 
	attributes are referenced in the subtree below the node. The attributes
	which are referenced are assigned numbers 0 to n- 1 where n is the total
    	number of attributes referenced in the query (Ingres 6.4/05 /02) 
    OP130 - Show variable map on entry (prior to update) of query node. 
    OP131 - Show variable map after equivalence updating of query node. 
    OP132 - Turns off query flattening. Might be useful if inner subselect not
	restricted and very expensive. (Ingres 6.4/05 /02) 
    OP133 - Forces query flattening. Workaround for DB procedure subquery bug,
	see 6.3/02 release notes. (Ingres 6.4/05 /02) 
    OP134 - aggregate function display. 
    OP135 - aggregate function display. 
    OP136 - aggregate function display. 
    OP137 - aggregate function display. 
    OP138 - aggregate function display. 
    OP139 - Trace aggregate compute-together computations (Ingres 6.4/05 /02) 
    OP135 - not supported in OpenIngres. 
    OP138 - not supported in OpenIngres. 
    OP139 - Trace aggregate compute-together computations. Not supported in
	OpenIngres. 
    OP140 - Trace aggregate result replacement into the query tree. 
    OP141 - Do not project out the by-domain for an aggregate function before
	perfoming the aggregate. This means that an aggregate like 'count (x by
	y where qualification)' will not aggregate like 'count (x by y where
	qualification)'.  Normally you will get results for these values of y
	and the count will be 0 for them (Ingres 6.4/05 /02; not supported
	in OpenIngres). 
    OP142 - Print time required to optimize (Ingres 6.4/05 /02; not supported
	in OpenIngres). 
    OP143 - Print info about aggregates which will be executed together. 
    OP145 - like SET QEP (Ingres 6.4/05 /02; not supported in OpenIngres). 
    OP146 - Dump query trees at start of aggregate processing. 
    OP147 - Trace aggregate combining processing (Ingres 6.4/05 /02) 
    OP148 - Print message when global range table variable is created. 
    OP149 - Trace function arguments (Ingres 6.4/05 /02; not supported in
	OpenIngres). 
    OP150 - Trace OPC structuress (looks like code gen output)
	(Ingres 6.4/05 /02) 
    OP151 - Turn off query compilation, useful to see if crash-problem is in
	OPF or OPC (Ingres 6.4/05 /02) 
    OP152 - like SET NOTIMEOUT (Ingres 6.4/05 /02; not supported in OpenIngres)
    OP153 - Trace key qualifiers (Ingres 6.4/05 /02; not supported in OpenIngres)
    OP154 - Trace OPC keying info (Ingres 6.4/05 /02) 
    OP155 - Trace fused keying (Ingres 6.4/05 /02) 
    OP156 - Print all joinop tables (Ingres 6.4/05 /02) 
    OP157 - Print map of equivalence classes for which histograms are required 
    OP158 - (Ingres 6.4/05 /02) 
    OP159 - Send warning messages to the user. 
    OP160 - like SET OPTIMIZEONLY (Ingres 6.4/05 /02) 
    OP161 - Dump data structures from OPF to OPC (Ingres 6.4/05 /02) 
    OP164 - Dump distributed query text for each CO tree node (distributed only). 
    OP165 - Don't use any statistics (Ingres 6.4/05 /02) 
    OP166 - Enable a fake gateway optimizer (Ingres 6.4/05 /02) 
    OP167 - Enable a fake multi-site query plan compilation. 
    OP168 - Create SQL text for CO nodes when SET QEP is enabled 
	(Ingres 6.4/05 /02) 
    OP173 - Enable the building of the qen_prow CXs in the QEN_NODE structure. 
    OP174 - change behaviour of control C to exit with best query plan 
	(Ingres 6.4/05 /02) 
    OP176 - Simply continue from float exceptions in the optimizer, don't
	recover. 
    OP177 - Always use keying strategies when possible, regardless of expense. 
    OP178 - Default the "complete" flag to TRUE (Ingres 6.4/05 /02) 
    OP180 - Look at cartesian product search space. Useful for multi-attribute
	keying in which cartesian-product of small tables could be used to key 
	into the large table (Ingres 6.4/05 /02)

    OP181 - look at reduced search space given heuristic estimates of index
	usage. 
    OP182 - Default the "complete" flag to TRUE when statistics are not
	available, the default prior to 6.4 (Ingres 6.4/05 /02) 
    OP183 - Ignore the "complete" flag (Ingres 6.4/05 /02) 
    OP184 - Don't use a Smart Disk scan, even if it is available. 
    OP185 - Print (non-obvious) reasons for rejecting Smart Disk scan. 
    OP186 - Trace qualification conjuncts using Smart Disk (Ingres 6.4/05 /02)

    OP187 - Turn off cartesian-product keying optimization (Ingres 6.4/05 /02)

    OP188 - Print QEP's as new better subplans are discovered
	(Ingres 6.4/05 /02) 
    OP189 - Assume some correlation between attributes (on proj-rest) so that
	selectivities do not become small as rapidly as normal
	(Ingres 6.4/05 /02) 
    OP191 - Do not assume some correlation between attributes when doing multi
	equivalence class boolean factors (Ingres 6.4/05 /02) 

    OP203 - Allows ambiguous replace in Ingres 6.4/06 which is otherwise not
	allowed. Was previously allowed in Ingres 6.4/04. 
    OP250 - Number bytes of OPF memory that a session can use. Questionable. 
    OP251 - Set DIO cost for scan (default is 1 DIO per 8 pages). To be used
	for bug catching, not tuning. (Ingres 6.4/05 /02) [note: questionable]. 
    OP251 - [note: questionable]. 
    OP252 - Normally blocking factor is 4 pages per heap block read on a scan,
	1/2 of this for btree, more complicated for hash and isam. Override 
	the default base value of 4. (Ingres 6.4/05 /02) [note: questionable]. 
    OP253 - Adjust conversion factor used to make time units out of cost units.
	100 means use default, 150 means look for better plans 50% longer, etc.
	(Ingres 6.4/05 /02) [note: questionable].

    OP255 - timeout after plan #plan-num evaluated (Ingres 6.4/05 /02) 

    PS016 - pst_header print queue; not sure it does anything anymore. 
    PS018 - pst_node (Ingres 6.4/05 /02); not sure it does anything anymore. 
    PS020 - pst_norml (Ingres 6.4/05 /02); not sure it does anything anymore. 
    PS021 - pst_resolve (Ingres 6.4/05 /02); not sure it does anything anymore. 
    PS051 - pst_permit (Ingres 6.4/05 /02); not sure it does anything anymore.

    PS070 - pst_show_tab (Ingres 6.4/05 /02); not sure it does anything anymore.
    PS071 - pst_print (Ingres 6.4/05 /02); not sure it does anything anymore. 
    PS072 - Disable validation of integrity (Ingres 6.4/05 /02); not sure
	it does anything anymore. 

    PS100 - Looks like repeated query trace - eg try "help" from sql.
	(Ingres 6.4/05 /02); not sure it does anything anymore. 

    QE90 - Show difference between estimated and actual tuples, disk, CPU at
	each query execution node (best used with SET QEP). If subquery number
	is specified then timeout on plan number only for that subquery.
	(Ingres 6.4/05 /02) 

    QS001 - Dump the object queue on session termination. 
    QS002 - Announce entry and exit of all QSF calls. 
    QS003 - Do consistency checks on session CB list (Ingres 6.4/05 /02) 
    QS004 - Dump the object queue after each object has been created. 
    QS005 - Do consistency checks on QSF's object queue (Ingres 6.4/05 /02) 
    QS006 - When set all object queue dumps will be in long format. 
    QS007 - Dump the queue only after a named object is created 
	(Ingres 6.4/05 /02) 
    QS008 - Show info when trying to clear an object from the object queue. 
    QS009 - Generate a mem-pool map whenever an object queue is dumped. 

    QS501 - QSF pool memory left, total, percent used (Ingres 6.4/05 /02) 
    QS502 - QSF pool objects & buckets summary. 
    QS503 - QSF hash buckets (Ingres 6.4/05 /02) 
    QS504 - QSF sessions (Ingres 6.4/05 /02) 
    QS505 - LRU queue info (Ingres 6.4/05 /02) 
    QS506 - Flush the QSF pool & procedure cache (Ingres 6.4/05 /02) 
    QS507 - Largest QSF pool allocated/requested (OpenIngres only). 
    QS510 - says it outputs ULM map but the code is dummied out in iimerge. 

    QS1001 - Send all info requests to log file as well as user 
	(Ingres 6.4/05 /02) 
    QS1002 - For each call to qsf_hash() for named objects show object ID and
	hash bucket (Ingres 6.4/05 /02) 
    QS1003 - For all calls to qsf_hash(), show object ID and hash bucket
	(Ingres 6.4/05 /02) 

    NOTE: RDR info dumps take 1 parameter. If that parameter is specified (any
	value except zero), then only objects owned by users are dumped. If
	the parameter is not specified or is specified to zero, then INGRES 
	owned objects are dumped as well. Not all the RDR trace points seem to
	work. 
    RD0001 - re-synchs the RDF cache with statistics from newly optimized
	tables. This saves the server having to be stopped & restarted 
	(Ingres 6.4/05)

    RD111 - Dump all RDR_INFO information (i.e.,do dumps 112 thru 122). 
    RD112 - Dump the relation information. (Ingres 6.4/05 /02) 
    RD113 - Dump the attributes information. 
    RD114 - Dump the indexes information. (Ingres 6.4/05 /02) 
    RD115 - Dump integrity tuples. (Ingres 6.4/05 /02) 
    RD116 - Dump protection tuples. (Ingres 6.4/05 /02) 
    RD117 - Dump the statistics information. (Ingres 6.4/05 /02) 
    RD118 - Dump attribute hash table information.. 
    RD119 - Dump primary key information. (Ingres 6.4/05 /02) 
    RD120 - Dump iirule tuples. (Ingres 6.4/05 /02) 
    RD121 - Dump view information. (Ingres 6.4/05 /02) 
    RD122 - Dump RDR_INFO structure (high level RDF info) (Ingres 6.4/05 /02) 

    SC0 - Print query text - use SET PRINTQRY (Ingres 6.4/05 /02) 
    SC1 - Trace extraordinary events. Not sure it works. (Ingres 6.4/05 /02) 
    SC2 - Print user alert events - use SET PRINTEVENTS. 
    SC3 - Log user alert events - use SET LOGEVENTS (Ingres 6.4/05 /02) 
    SC4 - Log SCE requests (from QEF) (Ingres 6.4/05 /02) 
    SC5 - Trace alert notifications to terminal (Ingres 6.4/05 /02) 
    SC6 - Trace alert state transitions to terminal (Ingres 6.4/05 /02) 

    SC900 - Declare exception handler for each utility service. 
    SC901 - Check SCF memory pool on each memory op (Ingres 6.4/05 /02) 
    SC902 - Write error messages to trace log. Writes all error messages to
	error log as well. Global. Sends user level error messages 
	(Ingres 6.4/05 /02) 
    SC903 - Send all logged messages to the client. . Sends errlog messages to
	user (Ingres 6.4/05 /02) 
    SC904 - Trace memory allocations to trace file (Ingres 6.4/05 /02) 
    SC906 - Log session startup and shutdown. Writes to error log. Global. 
    SC908 - Crash the server (Ingres 6.4/05 /02) 
    SC909 - Dump server statistics to dbms log immediately (Ingres 6.4/05 /02)

    SC911 - Dump server memory information to dbms log immediately 
	(Ingres 6.4/05 /02) 
    SC912 - Allows more information to be output for failed connection requests
	(Ingres 6.4/06). 
    SC921 - Dump local server event memory immediately (Ingres 6.4/05 /02) 
    SC922 - Dump cross server event memory immediately (Ingres 6.4/05 /02) 
    SC923 - Alter SCE event processing - (help = 1000) (Ingres 6.4/05 /02) 

    SC1000 - SCF trace point help (Ingres 6.4/05 /02) 

                                                               

Extra iimonitor things: 

    show smstats - show semaphore statistics. 

    clear smstats - Clear/reset semaphore stats. 

    debug - Dump SCB, session stack for session. 

    Use adb to get syms for hex addrs from stack 

    show mutex mid owner - Show mutex info; mutex id comes from eg DM11; don't
	know what it wants for owner though, some number. 

    (no)printqry - Print out the query before optimizing and executing it. 

    (no)lock_trace - Print info about every lock acquired . 

    (no)io_trace - Print info about every disk io. 

    joinop timeout - The optimizer will stop optimizing after it gets a plan
	that is 'good enough', or runs out of time. Default is 10, valid range
	is 1-10000. Units MS of CPU. Larger values means that the optimizer 
	runs longer before the 'good enough' criteria is satisfied. Note:
	repeat queries' value is raised to 100 temporarily.

    joinop notimeout - Optimize untill all QEP's are evaluated, or until it
	runs out of memory in the enumeration area. This goes into the
	j_freesz3 buffer. If this is a repeat query then this space is saved 
	for as long as the repeat query is active. Otherwise it is reused by 
	the next query.

    (no)qep - print the query execution plan before executing it.

    j_mergefact - when sorts are so big (see j_sortbufsize) that they must
	spill to disk, this is the merge factor used in the merge passes.
	Default is 10. Valid range 3-15 inclusive. Suggest prime number like 11.

    j_cpufactor - when computing the cost of a QEP, both CPU and diskI/O are
	estimated for a total cost: total=diskI/O + (cpu/j_cpufactor). This
	value could be changed for computers where the relationship between
	cpu and disk performance is different than the VAX 750 which this value
	was originally set for. Probably won?t make much difference in most
	cases. Default 100. Any integral value is valid. 

To William's Ingres Reference

Ingres Q & A

To William's Home Page

© William Yuan 2000

Email William