Now let's look at the SMI tables that contain information about your disk space, chunks, and dbspace. There are four tables that contain this data.
• Sysdbspaces | DB Spaces |
• syschunks | Chunks |
• syschkio | I/O by Chunk |
• syschfree* | Free Space by Chunk |
Note: Syschfree is not a supported table.
The sysmaster database has three key tables containing dbspace and chunk information. The first one is sysdbspaces. This is a view that interprets the underlying table sysdbstab. Sysdbspaces serves two purposes: it translates a bit field containing flags into separate columns where 1 equals yes and 0 equals no, and, it allows the underlying table to change between releases without having to change code. The view is defined as follows:
Column Data TypeDescription dbsnum smallint dbspace number, name char(18) dbspace name, owner char(8) dbspace owner, fchunk smallint first chunk in dbspace, nchunks smallint number of chunks in dbspace, is mirrored bitval is dbspace mirrored, 1=Yes, 0= No is_blobspace bitval is dbspace a blob space, 1=Yes, 2=No is_temp bitval is dbspace temp, 1=Yes, 2=No flags smallint dbspace flags
The columns of type bitval are the flags that are extracted from the flags column by a stored procedure called bitval when the view is generated.
The syschunks table is also a view based on two actual tables, one for primary chunk information, syschktab, and one for mirror chunk information, sysmchktab. The following is the layout of syschunks:
Column | Data Type | Description |
chknum | smallint | chunk number |
dbsnum | smallint | dbspace number |
nxchknum | smallint | number of next chunk in dbspace |
chksize | integer | pages in chunk |
offset | integer | pages offset into device |
nfree | integer | free pages in chunk |
is_offline | bitval | is chunk offline, l=Yes, 0=No |
is_recovering | bitval | is chunk recovering, l=Yes, 0=No |
is_blobchunk | bitval | is chunk blobchunk, l=Yes, 0=No |
is_inconsistent | bitval | is chunk inconsistent, l=Yes, 0=No |
flags | smallint | chunk flags converted by bitval |
fname | char(128) | device pathname |
mfname | char(128) | mirror device pathname |
moffset | integer | pages offset into mirror device |
mis_offline | bitval | is mirror offline, l=Yes, 0=No |
mis_recovering | bitval | is mirror recovering, l=Yes, 0=No |
mflags | smallint | mirror chunk flags |
Now, we will take a look at several ways to use this dbspace and chunk information. One capability I have always wanted is a way to show the amount of dbspace used and free in the same format as the UNIX "df -k" command. The sysmaster database contains information about the dbspaces and chunks, so this can be generated with an SQL script. The following is an SQL script to generate the amount of free space in a dbspace. It uses the sysdbspaces and syschunks tables to collect its information.
---dbsfree.sql - display free dbspace like UNIX "df -k " command database sysmaster; select name[1,8]dbspace, -- name truncated to fit on one line sum(chksize) Pages_size, -- sum of all chunks size pages sum(chksize) - sum(nfree) Pages_used, sum(nfree) Pages_free, -- sum of all chunks free pages round ((sum(nfree)) / (sum(chksize)) * 100, 2) percent_free from sysdbspaces d, syschunks c where d.dbsnum = c.dbsnum group by 1 order by 1;
dbspace pages_size pages_used pages_free percent_free rootdbs 50000 13521 36479 72.96 dbspace1 100000 87532 12468 12.47 dbspace2 100000 62876 37124 37.12 dbspace3 100000 201 99799 99.80
The next script lists the status and characteristics of each chunk device
--chkstatus.sql - display information about a chunk database sysmaster; select name dbspace, -- dbspace name is_mirrored, -- dbspace is mirrored l=Yes 0=No is_blobspace, -- dbspace is blobspace l=Yes 0=No is_temp, -- dbspace is temp l=Yes 0=No chknum chunknum, -- chunk number fname device, -- dev path offset dev_offset, -- dev offset is_offline, -- Offline l=Yes 0=No is_recovering, -- Recovering l=Yes 0=No is_blobchunk, -- Blobspace l=Yes 0=No is_inconsistent, -- Inconsistent l=Yes 0=No chksize Pages_size, -- chunk size in pages (chksize - nfree) Pages_used, -- chunk pages used nfree Pages_free, -- chunk free pages round ((nfree / chksize) * 100, 2) percent_free, -- free mfname mirror_device, -- mirror dev path moffset mirror_offset, -- mirror dev offset mis_offline , -- mirror offline l=Yes 0=No mis_recovering -- mirror recovering l=Yes 0=No from sysdbspaces d, syschunks c where d.dbsnum = c.dbsnum order by dbspace, chunknum
In planning expansions, new databases, or when adding new tables to an existing server, I like to know what blocks of contiguous free space are available. This allows placing new tables in dbspaces where they will not be broken up by extents. One of the sysmaster tables tracks the chunk free list, which is the available space in a chunk.
Column | Data Type | Description |
chknum | integer | chunk number |
extnum | integer | extent number in chunk |
start | integer | physical addr of start |
leng | integer | length of extent |
The next script uses this table to create a list of free space and the size of each space that is available.
--chkflist.sql - display list of free space within a chunk database sysmaster; select name dbspace, -- dbspace name truncated to fit f.chknum, -- chunk number f.extnum, -- extent number of free space f.start, -- starting address of free space f.leng free_pages -- length of free space from sysdbspaces d, syschunks c, syschfree f where d.dbsnum = c.dbsnum and c.chknum = f.chknum order by dbspace, chknum
dbspace chknum extnum start free_pages rootdbs 1 0 11905 1608 rootdbs 1 1 15129 34871
Informix uses a view, syschkio, to collect information about the number of disk reads and writes per chunk. This view is based on the tables syschktab and symchktab.
Column Data Type Description chunknum smallint chunk number reads integer number of read ops pagesread integer number of pages read writes integer number of write ops pageswritten integer number of pages written mreads integer number of mirror read ops mpagesread integer number of mirror pages read mwrites integer number of mirror write ops mpageswritten integer number of mirror pages written
The following script displays I/O usage of chunk devices. It uses the base tables so the mirror chunks can be displayed on separate rows. It also joins with the base table that contains the dbspace name.
--chkio.sql - displays chunk IO status database sysmaster; select name[1,10] dbspace, -- truncated to fit 80 char screen line chknum, "Primary" chktype, reads, writes, pagesread, pageswritten from syschktab c, sysdbstab d where c.dbsnum = d.dbsnum union all select name[1,10] dbspace, chknum, "Mirror" chktype, reads, writes, pagesread, pageswritten from sysmchktab c, sysdbstab d where c.dbsnum = d.dbsnum order by 1,2,3;
dbspace chknum chktype reads writes pagesread pageswritten rootdbs 1 Primary 74209 165064 209177 308004 rootdbs 1 Mirror 69401 159832 209018 307985
A better view of your I/O is to see the percent of the total I/O that takes place per chunk. This next query collects I/O stats into a temp table, and then uses that to calculate total I/O stats for all chunks. Then each chunk's I/O is compared with the total to determine the percent of I/O by chunk. The following script uses the one above as a basis to show I/O by chunk as a percent of the total I/O.
--chkiosum.sql - calculates percent of IO by chunk database sysmaster; --Collect chunk IO stats into temp table A select name dbspace, chknum, "Primary" chktype, reads, writes, pagesread, pageswritten from syschktab c, sysdbstab d where c.dbsnum = d.dbsnum union all select name[1,10] dbspace, chknum, "Mirror" chktype, reads, writes, pagesread, pageswritten from sysmchktab c, sysdbstab d where c.dbsnum = d.dbsnum into temp A; --Collect total IO stats into temp table B select sum(reads) total_reads, sum(writes) total_writes, sum(pagesread) total_pgreads, sum(pageswritten) total_pgwrites from A into temp B; --Report showing each chunks percent of total IO select dbspace, chknum, chktype, reads, writes, pagesread, pageswritten, round((reads/total_reads) *100, 2) percent_reads, round((writes/total_writes) *100, 2) percent writes, round((pagesread/total_pgreads) *100, 2) per- cent_pg_reads, round((pageswritten/total_pgwrites) *100, 2) percent_pg_writes from A, B order by 11;-- order by percent page writes
Sample output for 1 chunk
dbspace datadbs
chknum 9
chktype Primary
reads 12001
writes 9804
pagesread 23894
pageswritte 14584
percent_rea 0.33
percent_writes 0.75
percent_pg_reads 37.59
percent_pg_writes 1.86