onstat -g dic Data dictionary cache
The onstat -g dic command displays the tables whose data dictionary information is currently stored within the dictionary cache in memory.
Dictionary Cache: Number of lists: 31, Maximum list size: 10
list# size refcnt dirty? heapptr table name
--------------------------------------------------------
1 3 1 no 14b5d890 wbe@oninit_shm:informix.t0010url
1 no 14cbb820 wbe@oninit_shm:informix.t9051themeval
0 no 14b63c20 wbe@oninit_shm:informix.t0060hits
2 2 0 no 14b97420 wbe@oninit_shm:informix.t0120import
1 no 14b6c820 wbe@oninit_shm:informix.t9110domain
3 3 0 no 14bce020 wbe@oninit_shm:informix.t0150url
0 no 14d3d820 contact@oninit_shm:informix.wbtags
0 no 14c87420 wbe@oninit_shm:informix.wbtags
4 1 0 no 14b7a420 drug@oninit_shm:viagra.product
.....
Total number of dictionary entries: 36
</div>
Output Description
| Heading | Description | Format |
|---|---|---|
| list# | The data dictionary hash chain ID | Dec |
| size | The number of entries in this hash | Dec |
| refcnt | The number of SQL statements currently referencing one of the entries in this cache. | Dec |
| dirty? | Whether this entry has been modified relative to its entry on disk. | Str |
| heapptr | The in-memory address for the heap used to store this entry. | Hex |
| table name | The name of the table in the cache. | Str |
The onstat -g dic command can also take a tablename. This can be very useful when tracking down indices, constraint ID etc without using SQL
IBM Informix Dynamic Server Version 14.10.FC3DE -- On-Line -- Up 00:04:28 -- 299964 Kbytes
Dictionary entry for table: latency1 [hashes to list#: 28]
ddt_tabfullname: eric@strider_net:informix.latency1
ddt_partnum: 1049059 ddtab-address: 5480c058
ddt_fextsize: 16 ddt_nextsize: 16 ddt_locklevel: 1
ddt_flag: -2147483648 ddt_flag2: 33554432 ddt_ps: 0 ddt_row: 5480c038
ddt_dbalthash: 10 ddt_dbaltcount: 178 ddt_ncols: 2
ddt_rowsize: 15 ddt_nallidxs: 1 ddt_nindexes: 1 ddt_type: T
ddt_nrows: 1 ddt_npused: 1 ddt_tabid: 142
ddt_majversion: 145 ddt_minversion: 58 ddt_perms: 5480c9b0
Table Permissions:
Userthread <informix> has <su idxar="">
Userthread <public> has <su-idx--->
ddt_cols: 5480c460 ddt_indexes: 5480c248 ddt_uniq: 5480cb60
ddt_ref: 0 ddt_check: 5480ca40 ddt_dummytab: 5480c560
ddt_secpolicyid: 0 ddt_protgranularity: -
ddt_rowver_pos: 0 ddt_rowchk_pos: 0
ddt_nopcls: 0 ddt_opcls: 0 ddt_numreftabs: 0
ddt_reftabs: 0 ddt_next: 53f96858 ddt_prev: 547ef058
ddt_refcount: 1 ddt_frags: 0
ddt_fraghdr: 0
ddt_viotid: 0
ddt_diatid: 0
Column Descriptors:
ddc_name: id ddc_colno: 1 ddc_default: 0
ddc_flags: 8388608 ddc_type: 262 ddc_xid: 0 ddc_start: 0
ddc_len: 4 ddc_nunique: 1 ddc_next: 5480c4e0
ddc_name: ins_dte ddc_colno: 2 ddc_default: 0
ddc_flags: 0 ddc_type: 10 ddc_xid: 0 ddc_start: 4
ddc_len: 4879 ddc_nunique: 0 ddc_next: 0
Index Descriptors:
ddil_name: pk_latency1 ddil_keylen: 4 ddil_flags: 34824
ddil_next: 0 ddil_colno: { 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 }
ddil_count: 1 ddil_frags: 5481ab08
lf_nfrags: 1 lf_strategy: 1 (none)
lf_afrag: 5481ac28 lf_fragpart: 0 lf_collist: 0
Fragment# 1 lf_frag: 5481ab78
lf_flags: 0 lf_fragid: 1049065
lf_loc: rootdbs
lf_ploc: rootdbs
lf_expr: 0 lf_linexpr: 0 lf_linsize: 0
lf_levels/clust: 1/0 frag_leaves/nunique: 1/1
ddil_state: E
ddil_collation: en_US.819
Referential Constraints:
Unique Constraints:
ddc_constrid: 46 ddc_owner: informix ddc_name: pk_latency1
ddc_type: P ddc_flags: 0 ddc_uindex: 5480c248
ddc_urefc: 0 ddc_selfrefcnt: 0
ddc_colnml: { id }
ddc_state: E
ddc_collation: en_US.819
Check Constraints:
ddc_constrid: 45 ddc_owner: informix ddc_name: n142_45
ddc_type: C ddc_flags: 64 ddc_checkexp: 5480cf80
ddc_checktxt: <nil> ddc_txtsize: 0
ddc_colnml: { }
ddc_state: E
ddc_collation: en_US.819
ddc_constrid: 46 ddc_owner: informix ddc_name: pk_latency1
ddc_type: C ddc_flags: 32 ddc_checkexp: 5480ccd0
ddc_checktxt: <nil> ddc_txtsize: 0
ddc_colnml: { }
ddc_state: E
ddc_collation: en_US.819
Triggers:
ddtr_trigid: 8 ddtr_owner: informix ddtr_name: trg_latency1
ddtr_type: I
ddtr_trigcol: { }
ddtr_ptree: 54841168
ddtr_state: E
ddtr_collation: en_US.819
1 entries found.
</nil></nil></su-idx---></public></su></informix>There are other operations beyond DDL that will cause a data dictionary entry to be marked as dirty. An example is any update statistics operation. A dirty entry will remain visible in the cache until all references to the cache entry have been removed. Any active SQL statement against a table will generate a reference to that table's data dictionary cache entry, as will declared cursors and stored procedures. Since ER has to know about the structure of a table in order to know how to interpret logical log records then it's likely it will also hold a reference to the dictionary cache.