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 6.4/05).
• DM1203 - Call input parameters; (none of the DM12xx seem to do anything under 6.4/05).
• DM1204 - Call output parameters; (none of the DM12xx seem to do anything under 6.4/05).
• DM1210 - Call timing enable; (none of the DM12xx seem to do anything under 6.4/05).
• DM1211 - Call timing display; (none of the DM12xx seem to do anything under 6.4/05).
• DM1212 - Call histogram timing display.
• DM1213 - Call timing display; (none of the DM12xx seem to do anything under 6.4/05).
• DM1220 - Call memory checking; (none of the DM12xx seem to do anything under 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 untill 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.

This page is linked from the FAQ page of the North American Ingres Users' Association (NAIUA).