Dbspace and Chunk Information
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.
Dbspace configuration: sysdbspaces
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:
View sysdbspaces
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.
Chunk configuration: syschunks
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:
View 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 |
Displaying free dbspace
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;
Sample output
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
Displaying chunk status
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
Displaying blocks of free space in a chunk: syscchfree
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.
Table syschfree
| 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
Sample Output
dbspace chknum extnum start free_pages rootdbs 1 0 11905 1608 rootdbs 1 1 15129 34871
I/O statistics by chunk devices: syschkio
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.
View syschkio
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;
Sample Output
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