Sysmaster interface (SMI) pseudo tables for query probing data

The SMI tables provide a way to access probing data in a relational form, which is most convenient for further processing.

The sysmaster database provides the following pseudo tables for accessing probing data:

For tables: sysprobetables
For columns: sysprobecolumns
For join descriptors: sysprobejds
For join predicates: sysprobejps

The sysprobetables table

The schema for the sysprobetables table is:

CREATE TABLE informix.sysprobetables
(
	dbname	char(128),   { database name }
	sql_id	int8,	       { statement id in syssqltrace }
	tabid	integer,      { table id }
	fact	char(1)        { table is fact table (y/n) }
);
REVOKE ALL ON informix.sysprobetables FROM public AS informix;
GRANT SELECT ON informix.sysprobetables TO public as informix;

The sysprobecolumns table

The schema for the sysprobecolumns table is:

CREATE TABLE informix.sysprobecolumns
(
	dbname	char(128),	 { database name }
	sql_id	int8,	      { statement id in syssqltrace }
	tabid	integer,	     { table id }
	colno	smallint	     { column number }
);
REVOKE ALL ON informix.sysprobecolumns FROM public AS informix;
GRANT SELECT ON informix.sysprobecolumns TO public AS informix;

The sysprobejds table

The schema for the sysprobejds table is:

CREATE TABLE informix.sysprobejds
(
	dbname	char(128),	{ database name }
	sql_id	int8,	{ statement id in syssqltrace }
	jd	integer,	{ join descriptor sequence number }
	ctabid	integer,	{ child table id }
	ptabid	integer,	{ parent table id }
	type	char(1),	{ join type: inner (i), left outer (l) }
	uniq	char(1)	{ parent table has unique index (y/n) }
);
REVOKE ALL ON informix.sysprobejds FROM public AS informix;
GRANT SELECT ON informix.sysprobejds TO public AS informix;

The sysprobejps table

The schema for the sysprobejps table:

CREATE TABLE informix.sysprobejps
(
	dbname	char(128),	  { database name }
	sql_id	int8,	       { statement id in syssqltrace }
	jd	integer,	         { join descriptor sequence number }
	jp	integer,	         { join predicate sequence number }
	ccolno	smallint,	   { child table column number }
	pcolno	smallint	    { parent table column number }
);
REVOKE ALL ON informix.sysprobejps FROM public AS informix;
GRANT SELECT ON informix.sysprobejps TO public AS informix;