Database and Table Information
The next five tables we will look at store information on your tables and extents. They are:
| • sysdatabase | Databases |
| • systabnames | Tables |
| • sysextents | Tables extents |
| • sysptprof | Tables I/O |
Information on all databases on a server: sysdatabases
This view has data on all databases on a server. Have you ever needed to create a pop-up list of databases within a program? This table now allows programs to give users a list of databases to select from without resorting to ESQL/C. The following is the definition of this view:
View sysdatabases
Column Data Type Description
name char(18) database name
partnum integer table id for systables
owner char(8) user name of creator
created integer date created
is_logging bitval unbuffered logging, l=Yes, 0= No
is_buff_log bitval buffered logging, l=Yes,0= No
is_ansi bitval ANSI mode database, l=Yes, 0= No
is_nls bitval NLS support, l=Yes, 0= No
flags smallint flags indicating logging
The following is a script to list all databases, owners, dbspaces, and logging status. Notice the function dbinfo is used. This is a new function in 7.X with several uses, one of which is to convert the partnum of a database into its corresponding dbspace. This function will be used in several examples that follow.
--dblist.sql - List all databases, owner and logging status
database sysmaster;
select
dbinfo("DBSPACE",partnum) dbspace,
name database,
owner,
is_logging,
is_buff_log
from sysdatabases
order by dbspace, name;
Sample Output
dbspace database owner is_logging is_buff_log rootdbs central lester 0 0 rootdbs datatools lester 0 0 rootdbs dba lester 0 0 rootdbs roster lester 0 0 rootdbs stores7 lester 0 0 rootdbs sunset linda 0 0 rootdbs sysmaster Informix 1 0 rootdbs zip lester 1 1
Information about tables: systabnames, sysextents, and sysptprof
Three tables contain all the data you need from the sysmaster database about tables in your database. The first of these is a real table defined as follows:
Table systabnames. All tables on the server
| Column | Data Type | Description |
| partnum | integer | table id for table |
| dbsname | char(18) | database name |
| owner | char(8) | table owner |
| tabname | char(18) | table name |
| collate | char(32) | collation assoc with NLS DB |
View sysextents. Tables and each extent on the server
| Column | Data Type | Description |
| Dbsname | char(18) | data base name |
| tabname | char(18) | table name |
| star | integer | physical addr for this extent |
| size | integer | size of this extent |
The view sysextents is based on a table, sysptnext, defined as follows:
Table sysptnext
| Column | Data Type | Description |
| pe_partnum | integer | partnum for this partition |
| pe_extnum | smallint | extent number |
| pe_phys | integer | physical addr for this extent |
| pe_size | integer | size of this extent |
| pe_log | integer | logical page for start |
View sysptprof. Tables I/O profile
| Column | Data Type | Description |
| Dbsname | char(18) | database name |
| tabname | char(18) | table name |
| partnum | integer | partnum for this table |
| lockreqs | integer | lock requests |
| lockwts | integer | lock waits |
| deadlks | integer | deadlocks |
| lktouts | integer | lock timeouts |
| isreads | integer | reads |
| iswrites | integer | writes |
| isrewrites | integer | rewrites |
| isdeletes | integer | deletes |
| bufreads | integer | buffer reads |
| bufwrites | integer | buffer writes |
| seqscans | integer | sequential scans |
| pagreads | integer | disk reads |
| pagwrites | integer | disk writes |
These tables allow us to develop scripts to display tables, the number of extents, and pages used. We can also present a layout of dbspace, databases, tables, and extents similar to the command "tbcheck -pe." And finally, we can show table usage statistics sorted by which tables have the most hits based on reads, writes, or locks. These scripts will enable a DBA to monitor and tune the database server.
Extents are created when a table's initial space has been filled up and it needs more space. OnLine will allocate additional space for a table. However, the table will no longer be contiguous, and performance will start to degrade. Informix will display warning messages when a table reaches more than 8 extents. Depending on a number of factors, at approximately 180 to 230 extents a table will not be able to expand and no additional rows can be inserted. The following script lists all tables sorted by the number of extents. The tables that show up with many extents may need to be unloaded and rebuilt.
--tabextent.sql - List tables, number of extents and size of table. database sysmaster; select dbsname, tabname, count(*) num_of_extents, sum( pe_size ) total_size from systabnames, sysptnext where partnum = pe_partnum group by 1, 2 order by 3 desc, 4 desc;
Sample Output
dbsname tabname num_of_extents total_size rootdbs TBLSpace 8 400 sysmaster syscolumns 6 56 sunset inventory 3 376 sunset sales_items 3 96 sunset sales_header 3 48 sunset parts 3 48 sunset customer 3 40 sunset syscolumnext 3 32 sunset employee 3 32
Sometimes it is helpful to see how the tables are interspersed on disk. The following script lists by dbspace each table and the location of each extent. This is similar to the output from "oncheck-pe."
--tablayout.sql - Show layout of tables and extents database sysmaster; select dbinfo( "DBSPACE" , pe_partnum ) dbspace, dbsname[1,10], tabname, pe_phys start, pe_size size from sysptnext, outer systabnames where pe_partnum = partnum order by dbspace, start;
Sample output
dbspace dbsname tabname start size rootdbs rootdbs TBLSpace 1048589 50 rootdbs sysmaster sysdatabases 1050639 4 rootdbs sysmaster systables 1050643 8 rootdbs sysmaster syscolumns 1050651 16 rootdbs sysmaster sysindexes 1050667 8 rootdbs sysmaster systabauth 1050675 8 rootdbs sysmaster syscolauth 1050683 8 rootdbs sysmaster sysviews 1050691 8 rootdbs sysmaster sysusers 1050699 8 rootdbs Sysmaster sysdepend 1050707 8 rootdbs sysmaster syssynonyms 1050715 8
I/O performance of tables
Have you ever wanted to know which tables have the most reads, writes, or locks? The last script in this article shows the performance profile of tables. By changing the columns displayed and the sort order of the script, you can display the tables with the most reads, writes, or locks first.
--tabprof.sql database sysmaster; select dbsname, tabname, isreads, bufreads, pagreads --uncomment the following to show writes --iswrites, --bufwrites, --pagwrites --uncomment the following to show locks --lockreqs, --lockwts, --deadlks from sysptprof order by isreads desc; -- change this sort to whatever you need to monitor.
Sample Output
dbsname tabname isreads bufreads pagreads zip zip 334175 35876509 1111 sysmaster sysviews 259712 634102 1119 sysmaster systables 60999 240018 1878 zip systables 3491 8228 543 sysmaster sysusers 2406 8936 87 sysmaster sysprocauth 1276 5104 12 sunset systables 705 2251 26 sysmaster sysprocedures 640 2562 21 sysmaster syscolumns 637 1512 49 stores7 systables 565 136 16 sysmaster sysdatabases 534 2073 902