ids pagesize and maximum number of extents

Since IDS Version 10.00 dbspaces can be created with other than the default 2k/4k pagesize.

This feature has multiple benefits:

less disk I/O with larger pagesizes (8k ... 16k)

more rows per data/index page (max. 255 Rows / data page)

higher upper limit of rows per table (max. 16777216 pages / table fragment)

higher upper limit of table/index extents

reduce "-136 ISAM error: no more extents." Errors

Example (stores_demo database):

 

IDS 7.31, 9.40, 10.00, 11.50 / 2k dbspace pagesize:

database_name   stores_demo

tabname         customer

dbspace_name    root_dbs

extents_act     140

max_extents     232

 

IDS 10.00, 11.50 / 16k dbspace pagesize:

database_name   stores_demo

tabname         customer

dbspace_name    dat_dbs16k_001

extents_act     351

max_extents     2023

 

 

You can use the following select to show the upper limit of table/index extent sizes:

 

SELECT --+ORDERED,INDEX(a,systabs_pnix),INDEX(b,sysptnhdridx),INDEX(c,syspaghdridx),INDEX(d,sysdbstab_dbsnum)

       a.dbsname AS database_name,

       a.tabname,

       d.name AS dbspace_name,

       b.nextns AS extents_act,

       TRUNC(c.pg_frcnt / 8) + b.nextns AS max_extents

 FROM sysmaster:sysdbstab d,

      sysmaster:syspaghdr c,

      sysmaster:systabnames a,

      sysmaster:sysptnhdr b

  WHERE c.pg_partnum = sysmaster:partaddr(d.dbsnum, 1)

    AND sysmaster:bitval(c.pg_flags, 2) = 1

    AND c.pg_nslots = 5

    AND a.partnum = sysmaster:partaddr(d.dbsnum, c.pg_pagenum)

    AND a.partnum = b.partnum;

https://www.ibm.com/developerworks/community/blogs/informix_admins_blog/entry/ids_pagesize_and_maximum_number_of_extents5?lang=en

http://www-304.ibm.com/support/knowledgecenter/api/content/nl/ko/SSGU8G_11.50.0/com.ibm.perf.doc/ids_prf_311.htm

http://www-304.ibm.com/support/knowledgecenter/api/content/nl/ko/SSGU8G_11.50.0/com.ibm.perf.doc/ids_prf_316.htm