Question & Answer
Question
How can you use a SQL statement to find out the latest UPDATE STATISTICS HIGH and UPDATE STATISTICS MEDIUM commands that were run on a table?
Answer
INTRODUCTION
You can use a SQL statement to obtain information regarding each of the most recent UPDATE STATISTICS HIGH and UPDATE STATISTICS MEDIUM commands that ran against a table.
The type of information you can obtain from the most recent update statistics is the following:
- The date on which it ran
- The mode used (high or medium)
- The column(s) it ran against
- The resolution used
- The confidence level used
STEPS
You can run this SELECT statement from any application:
- SELECT UNIQUE tabname,colname,constructed,mode,
resolution,confidence
FROM systables t, syscolumns c, sysdistrib d
WHERE d.tabid = c.tabid AND d.colno = c.colno AND
d.tabid = t.tabid AND
t.tabname='tablename'
ORDER BY 1,2;
- tablename
- The name of the table you want the distribution information for.
The SELECT statement will output columns. The following table indicates their meaning:
Column | Description |
tabname | The name of the table |
colname | The name of the particular column in the table that the distribution had ran. |
constructed | The date when the UPDATE STATISTICS statement had ran. (The date of construction of the data distribution.) |
mode | What mode the UPDATE STATISTICS used. H = HIGH M = MEDIUM There will never be an L because UPDATE STATISTICS LOW does not construct a data distribution. |
resolution | The resolution used by the UPDATE STATISTICS statement |
confidence | The confidence level used by the UPDATE STATISTICS statement |
- Note: The confidence level for distributions run in high mode is irrelevant so it should always be set to zero.
Example:
This example shows that UPDATE STATISTICS was last run on 8 July 2003. The column named code of the table named state had its distribution updated in High mode. It used a resolution of .5. The mode was high so there is no confidence level.
The column named sname in the same table had its distribution built in medium mode. It used a resolution of 2.5 and a confidence level of approximately 0.95.
- tabname state
colname code
constructed 07/08/2003
mode H
resolution 0.5
confidence 0.00
tabname state
colname sname
constructed 07/08/2003
mode M
resolution 2.500000000000
confidence 0.94999999
You can also find this information from the UNIX or Windows command line using the dbschema utility (see Related Information section).
COMMON PROBLEMS
Symptom: You run the SELECT previously mentioned and you see this message
- No rows found is returned.
Resolution: It means the table does not have data distribution. You can run the UPDATE STATISTICS command for the table to generate the data distribution.
Related Information
[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF025","label":"Platform Independent"},{"code":"PF008","label":"DYNIX\/ptx"},{"code":"PF010","label":"HP-UX"},{"code":"PF015","label":"IRIX"},{"code":"PF016","label":"Linux"},{"code":"PF026","label":"Reliant UNIX"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.0;11.1;7.3;9.4;11.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSGU5D","label":"Informix Extended Parallel Server"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Not Applicable","Platform":[{"code":"","label":"Multi-Platform"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
More support for:
Informix Servers
Software version:
10.0, 11.1, 7.3, 9.4, 11.5
Operating system(s):
AIX, DYNIX/ptx, HP-UX, IRIX, Linux, Reliant UNIX, Solaris, Windows
Document number:
335493
Modified date:
03 June 2021
UID
swg21154913
Manage My Notification Subscriptions