sysptnhdr

The sysptrhdr table contains information about partition headers.

Column Type Description
partnum integer Partnum of the table
flags integer Partition flags
rowsize integer Row size (maximum for variable)
ncols smallint Number of VARCHAR or BLOB columns
nkeys smallint Number of indexes
nextns smallint Number of extents
pagesize smallint Page size
created integer Date created
serialv integer Current SERIAL value
fextsiz integer First extent size, in pages
nextsiz integer Next extent size, in pages
nptotal integer Number of pages allocated
npused integer Number of pages used
npdata integer Number of data pages
octptnm integer Optical BLOB partnum
lockid integer Table lock ID
nrows bigint Number of data rows
ninserts bigint Number of insert operations
nupdates bigint Number of update operations
ndeletes bigint Number of delete operations
cur_serial8 int8 Current SERIAL8 value
cur_bigserial bigint Current BIGSERIAL value
dbsnum integer Number of partitions in the dbspace
pta_oldvers smallint In-place alter
pta_newvers smallint In-place alter
pta_bmpagenum integer In-place alter
pta_totpgs integer In-place alter
pta_opems_allocd integer In-place alter
pta_opems_filled integer In-place alter
glscollname char(32) In-place alter
flags2 integer Partition flags2
sid integer Temporary table session ID

You can run the following query to see the number of allocated pages for temporary tables:

SELECT i.sid, hex(i.flags) flags, hex(i.partnum) partition,
   trim(n.dbsname) || ":" || trim(n.owner) || ":" || trim(n.tabname) table,
   i.nptotal allocated_pages
  FROM sysmaster:systabnames n, sysmaster:sysptnhdr i
 WHERE (sysmaster:bitval(i.flags, "0x0020") = 1)
   AND i.partnum = n.partnum

For example, the query can return information similar to the following output:

session with query "select * from customer into temp good "
sid              60
flags            0x00000861
partition        0x00100249
table            demo:informix:good 
allocated_pages  8

session with temp table generated from query "select from <view>"
sid              64
flags            0x00008821
partition        0x00100249
table            demo:informix:_temptable 
allocated_pages  8

temp table from sorting
sid              33
flags            0x000048A0
partition        0x00200004
table            SORTTEMP:informix:th_tmprun_0x4a1b2370 
allocated_pages  128

temp table from hashing
sid              31
flags            0x000048A0
partition        0x00200003
table            HASHTEMP:informix:th_overflow_0xffffffffffffffff 
allocated_pages  16 

Copyright© 2019 HCL Technologies Limited