Updated November 2021
Abstract
If an Informix instance warns that one of your temp dbspaces is full, it is useful to have SQL quickly to list what it contains, or to be run automatically if dbspace usage goes over a maximum acceptable percentage. Such a “sysmaster” query is provided in this article.
Content
The following SQL, which works with all Informix Dynamic Server (IDS) versions, lists objects registered in the “sysmaster” database as being located in temp dbspaces:
SELECT t2.owner [1,8],
t2.dbsname [1,18] AS database,
t2.tabname [1,22] AS table,
t3.name [1,10] AS dbspace,
(CURRENT - DBINFO('utc_to_datetime', ti_created))
:: INTERVAL DAY(4) TO SECOND AS life_time,
(ti_nptotal * ti_pagesize/1024)
:: INT AS size_kb
FROM systabinfo AS t1,
systabnames AS t2,
sysdbspaces AS t3
WHERE t2.partnum = ti_partnum
AND t3.dbsnum = TRUNC(t2.partnum/1024/1024)
AND TRUNC(MOD(ti_flags,256)/16) > 0
ORDER BY 6 DESC, 5 DESC;
Actual sample output is:
owner | database | table | dbspace | life_time | size_kb |
---|---|---|---|---|---|
doug | stores_demo | doug_temp_1 | tempdbs | 0 00:00:24 | 2012 |
doug | stores_demo | doug_temp_2 | tempdbs | 0 00:00:24 | 768 |
Utilisation by each object as a percentage of the dbspace
We had an interesting question from Pravin Bankar who asked how to extend the query to present utilization by each object as a percentage of the dbspace. This was the proposed approach:
SELECT
q1.*,
(100 * size_kb / dbs_size)
:: DECIMAL(5,2) AS percent
FROM
(
SELECT
t2.owner [1,8],
t2.dbsname [1,18] AS database,
t2.tabname [1,22] AS table,
t3.name [1,10] AS dbspace,
(CURRENT - DBINFO('utc_to_datetime', ti_created))
:: INTERVAL DAY(4) TO SECOND AS life_time,
(ti_nptotal * ti_pagesize/1024)
:: INT AS size_kb
FROM
systabinfo AS t1,
systabnames AS t2,
sysdbspaces AS t3
WHERE t2.partnum = ti_partnum
AND t3.dbsnum = TRUNC(t2.partnum/1024/1024)
AND TRUNC(MOD(ti_flags,256)/16) > 0
) AS q1,
(
SELECT
name AS dbspace,
SUM(chksize * d1.pagesize/1024) AS dbs_size
FROM
syschunks AS d1,
sysdbspaces AS d2
WHERE d1.dbsnum = d2.dbsnum
GROUP BY 1
) AS q2
WHERE q1.dbspace = q2.dbspace
ORDER BY 6 DESC, 5 DESC;
The above solutions primarily use view “systabinfo” with underlying pseudo-table “sysptnhdr”. From IDS 12 onwards, that table – but not the view – has an additional column “sid” populated for temporary tables with the ID of the creating session. That was included in standard view “syssessiontempspaceusage” from IDS 14.10.FC6 which exposed the existence of the new column. We have used that in the following improved solution. We also now believe that a view is more practical:
CREATE VIEW view_temp_tables
(
owner,
database,
table,
dbspace,
life_time,
size_kb,
sid,
pid,
hostname,
percent
)
AS
SELECT
q1.*,
(100 * size_kb / dbs_size)
:: DECIMAL(5,2) AS percent
FROM
(
SELECT
t2.owner [1,8],
t2.dbsname [1,18] AS database,
t2.tabname [1,22] AS table,
t3.name [1,10] AS dbspace,
(CURRENT - DBINFO('utc_to_datetime', t1.created))
:: INTERVAL DAY(4) TO SECOND AS life_time,
(t1.nptotal * t1.pagesize/1024)
:: INT AS size_kb,
t4.sid,
t4.pid,
t4.hostname [1,20]
FROM sysmaster:sysptnhdr AS t1
JOIN sysmaster:systabnames AS t2 ON t2.partnum = t1.partnum
JOIN sysmaster:sysdbspaces AS t3 ON t3.dbsnum = TRUNC(t1.partnum/POWER(2,20))
JOIN sysmaster:syssessions AS t4 ON t4.sid = t1.sid
WHERE t1.sid != DBINFO('SESSIONID') -- exclude current session
) AS q1,
(
SELECT
d2.name AS dbspace,
SUM(d1.chksize * d1.pagesize/1024) AS dbs_size
FROM sysmaster:syschunks AS d1
JOIN sysmaster:sysdbspaces AS d2 ON d1.dbsnum = d2.dbsnum
GROUP BY 1
) AS q2
WHERE q1.dbspace = q2.dbspace;
SQL to use that might be:
SELECT *
FROM view_temp_tables
ORDER BY 6 DESC, 5 DESC;
Actual sample output follows:
owner | database | table | dbspace | life_time | size_kb | sid | pid | hostname | percent |
---|---|---|---|---|---|---|---|---|---|
doug | stores_demo | doug_temp_1 | tempdbs | 0 00:02:04 | 27572 | 280 | -1 | 172.17.0.1 | 27.57 |
doug | stores_demo | doug_temp_2 | tempdbs | 0 00:02:04 | 8334 | 280 | -1 | 172.17.0.1 | 8.33 |
Note that JDBC clients present “-1” instead of an actual process ID.
We will be requesting that AGS include session details where possible in the Server Studio “Temp Table Usage” report.
Conclusion
Rapid access to this information is possible via SQL, and can help identify which applications are filling temp dbspaces.
If you have any questions or would like to find out more about listing DBspaces and Informix, please contact us.
Disclaimer
Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.