Troubleshooting
Problem
This document explains how you can use a SQL statement to find out the temporary tables that are currently created
Resolving The Problem
INTRODUCTION
You can use a SQL statement to obtain information regarding each of the temporary tables that are currently created.
STEPS
You can run the following SELECT statement from any application:
- SELECT t.tabname ,t.dbsname,t.owner,
DECODE(d.is_logging,1,"Y","N") AS db_with_log,
s.name AS dbspace,
DBINFO("UTC_TO_DATETIME",ti_created) AS created,
DECODE(hex(mod(ti_flags,256)/16),6,"Y","N") AS
table_using_log,ti_npused AS num_usedpages,
ti_nptotal AS num_pages
FROM sysmaster:systabnames t, sysmaster:systabinfo i,
sysmaster:sysdbspaces s, sysmaster:sysdatabases d
WHERE t.partnum=ti_partnum AND
d.name=t.dbsname AND
s.dbsnum=TRUNC(t.partnum/1048576) AND
hex(mod(ti_flags,256)/16) IN ( 6,2 )
Column | Description |
tabname | The name of the temporary table |
dbsname | The name of the database where the temporary table was created |
owner | The name of the user that created the temporary table |
db_with_log | If the database is not currently using logging a N will be added to this column. And a Y will be added if the database is using logging. |
dbspace | Name of the dbspace where the temporary table is created |
created | The date and time when the temporary table was created |
table_using_log | If the temporary table is using logging a Y will be added to this column. And a N will be added if the temporary table is not using logging. |
num_usedpages | Number of pages current in use by the temporary table |
num_pages | Total number of pages allocated for the temporary table |
Example:
This example shows two temporary tables created on May 23rd, 2005. The temp1 table was created without the WITH NO LOG option. The temp2 table was created using the WITH NO LOG option.
- tabname temp1
dbsname stores_demo
owner informix
db_with_log Y
dbspace rootdbs
created 2005-05-23 16:44:24
table_using_log Y
num_usedpages 1
num_pages 8
tabname temp2
dbsname stores_demo
owner informix
db_with_log Y
dbspace rootdbs
created 2005-05-23 16:44:24
table_using_log N
num_usedpages 1
num_pages 8
COMMON PROBLEMS
Symptom: After you execute the SELECT previously mentioned, the following message is returned:
- No rows found.
Resolution: It means that no temporary table has been created.
Symptom: After you execute the SELECT previously mentioned, all the temporary tables have the table_using_log column with a N value, and the db_with_log column has a N value.
Resolution: The symptom described above is expected since all temporary tables created in a database with no logging, are also created as no logging tables.
[{"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","Edition":"","Line of Business":{"code":"","label":""}}]
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21174524