 |
|
|
 |
Figure 5. Supported SMI tables
Supported tables and views: (OnLine 7.23) sysadtinfo Auditing configuration table sysaudit Auditing event masks table syschkio Chunk I/O statistics view syschunks Chunk information view sysconfig Configuration information view sysdatabases Database information view sysdbslocale Locale information view sysdbspaces Dbspace information view sysdri Data replication view sysextents Table extent allocation view syslocks Current lock information view syslogs Logical Log status view sysprofile Current system profile view sysptptof Current table profile view syssessions Current user sessions view sysseswts Session wait times view systabnames Table information table sysvpprof Current VP profile view
There are several key differences between the sysmaster database and other databases you might create. Reminder that this is a database that points to the server's shared memory structures and not to tables that are stored on disk. Some of the differences are:
The sysmaster database reads the same shared memory structures read by the command line utility "onstat". The statistical data is reset to zero when OnLine is shut down and restarted.
It is also reset to zero when the "onstat -z" command to reset statistics is used. Individual user statistical data is lost when a user disconnects from the server.
Now, let's examine some of the more interesting tables in the sysmaster database and what else can be done with them.
This first section will look at how you determine the state and configuration of your INFORMIX-OnLine server from the sysmaster database. We will look at four tables and how to use them.
The view sysonfig contains configuration information from the OnLine server. This information was read from the ONCONFIG file when the server was started. Have you ever needed to know from within a program how your server was setup? Or, what TAPEDEV is set to?
View sysconfig
Column Data Type Description
cf_id integer unique numeric identifier
cf_name char(18) config parameter name
cf_flags integer flags, 0 = in view sysconfig
cf_original char(256) value in ONCONFIG at boottime
cf_effective char(256) value effectively in use
cf_default char(256) value by default
Example queries:
The sysprofile table is a view based on values in a table called syshmhdr. Syshmhdr points to the same shared memory area as the onstat utility with the -p option. When you zero out the statistics with "onstat -z", all values in the syshmhdr table are reset to zero.
View sysprofile
Column Data Type Description
name char(16) profile element name
value integer current value
One of the best uses of this data is for developing alarms when certain values fall below acceptable levels. The Informix documentation says that tables in the sysmaster database do not run triggers. This is because the updates to these tables take place within OnLine shared memory and not through SQL which activates triggers. However, you can create a program to poll this table at specified intervals to select data and see if it falls below your expectations.
Syslogs is a view based on the table syslogfil. This is an example where the SMI views are a great tool in presenting the data in a more understandable format. Syslogfil has a field called flags which contains status information encoded in boolean smallint. The view syslogs decodes that data into six fields: is_used, is_current, is_backed_up, is_new, is_archived, and is_temp, with a 1 if true or a 0 if false.
View syslogs
Column Data Type Description
number smallint logfile number
uniqid integer logfile uniqid
size integer pages in logfile
used integer pages used in logfile
is_used integer 1 for used, 0 for free
is_current integer 1 for current
is_backed_up integer 1 for backuped
is_new integer 1 for new
is_archived integer 1 for archived
is_temp integer 1 for temp
flags smallint logfile flags
Sysvpprof is another view that is more readable than the underlying table sysvplst. As with the view syslogs in the above paragraph, this view has data that is converted to make it more understandable. This time the flags are converted to text descriptions from the flags_text table.
View sysvpprof
Column Data Type Description
vpid integer VP id
txt char(50) VP class name
usecs_user float number of unix secs of user time
usecs_sys float number of unix secs of system time
The following query on the base table sysvplst achieves the same results as the view.
Figure 6. SQL script to display VP status
-- vpstat.sql
select vpid,
txt[1,5] class,
pid,
usecs_user,
usecs_sys,
num_ready
from sysvplst a, flags_text b
where a.flags != 6
and a.class = b.flags
and b.tabname = 'sysvplst';
SQL Output
vpid class pid usecs_user usecs_sys num_ready
1 cpu 335 793.61 30.46 0
2 adm 336 0.02 0.11 0
3 lio 337 1.15 5.98 0
4 pio 338 0.19 1.13 0
5 aio 339 0.94 4.27 0
6 msc 340 0.15 0.14 0
7 aio 341 0.81 5.72 0
8 tli 342 1.79 3.02 0
9 aio 343 0.52 2.50 0
10 aio 344 0.28 1.16 0
11 aio 345 0.09 0.86 0
12 aio 346 0.16 0.48 0
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.
* 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:
View sysdbspaces
Column Data Type Description
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:
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, 1=Yes, 0=No
is_recovering bitval is chunk recovering, 1=Yes, 0=No
is_blobchunk bitval is chunk blobchunk, 1=Yes, 0=No
is_inconsistent bitval is chunk inconsistent, 1=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, 1=Yes, 0=No
mis_recovering bitval is mirror recovering, 1=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.
Figure 7. SQL script to display free dbspace
-- 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
The next script lists the status and characteristics of each chunk device.
Figure 8. SQL script showing chunk status
-- chkstatus.sql - display information about a chunk
database sysmaster;
select
name dbspace, -- dbspace name
is_mirrored, -- dbspace is mirrored 1=Yes 0=No
is_blobspace, -- dbspace is blobspace 1=Yes 0=No
is_temp, -- dbspace is temp 1=Yes 0=No
chknum chunknum, -- chunk number
fname device, -- dev path
offset dev_offset, -- dev offset
is_offline, -- Offline 1=Yes 0=No
is_recovering, -- Recovering 1=Yes 0=No
is_blobchunk, -- Blobspace 1=Yes 0=No
is_inconsistent, -- Inconsistent 1=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 1=Yes 0=No
mis_recovering -- mirror recovering 1=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.
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.
Figure 9. SQL script showing free space on chunks
-- 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
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 IO 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.
Figure 10. SQL script displaying chunk I/O
-- 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 IO is to see the percent of the total IO that takes place per chunk. This next query collects IO stats into a temp table, and then uses that to calculate total IO stats for all chunks. Then each chunk's IO is compared with the total to determine the percent of IO by chunk. The following script uses the one above as a basis to show IO by chunk as a percent of the total IO.
Figure 11. SQL script chunk I/O summary
-- 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) percent_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
pageswritten 14584
percent_reads 0.33
percent_writes 0.75
percent_pg_reads 37.59
percent_pg_writes 1.86
The next five tables we will look at store information on your tables and extents. They are:
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, 1=Yes, 0= No
is_buff_log bitval buffered logging, 1=Yes, 0= No
is_ansi bitval ANSI mode database, 1=Yes, 0= No
is_nls bitval NLS support, 1=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.
Figure 12. SQL script listing all databases on the server
-- 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
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) database name
tabname char(18) table name
start 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 IO 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-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.
Figure 13. SQL script showing tables and extents
-- 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".
Figure 14. SQL script showing table layout on chunks
-- 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
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.
Figure 15. SQL script show table I/O activity
-- 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 1361 16
sysmaster sysdatabases 534 2073 902
This last set of SMI tables deals with users and information about their sessions. These tables were used in our example script "dbwho" at the beginning of this chapter.
This view contains information from two shared memory structures, the user control and thread control table. This tells you who is logged in to your server and some basic data about their session.
View syssessions
Column Data Type Description
sid integer Session id number
username char(8) User name
uid smallint User unix id
pid integer User process id
hostname char(16) Hostname
tty char(16) TTY port
connected integer Time user connected
feprogram char(16) Program name
pooladdr integer Pointer to private session pool
is_wlatch integer Flag 1=Yes, 0=No, wait on latch
is_wlock integer Flag 1=Yes, 0=No, wait on lock
is_wbuff integer Flag 1=Yes, 0=No, wait on buffer
is_wckpt integer Flag 1=Yes, 0=No, wait on checkpoint
is_wlogbuf integer Flag 1=Yes, 0=No, wait on log buffer
is_wtrans integer Flag 1=Yes, 0=No, wait on a transaction
is_monitor integer Flag 1=Yes, 0=No, a monitoring process
is_incrit integer Flag 1=Yes, 0=No, in crtical section
state integer Flags
The following is a quick query to tell who is using your server.
Figure 16. SQL script showing user sessions
-- sessions.sql
select sid,
username,
pid,
hostname,
l2date(connected) startdate -- convert unix time to date
from syssessions
Sample Output
sid username pid hostname startdate
47 lester 11564 merlin 07/14/1997
This next query list all users and their session status. The objective is to show who is blocked waiting on another user, lock, or some other OnLine process. The five fields are yes/no flags where 1 = yes and 0 = no. If all the fields are 0, then none of the sessions are blocked. In the following example, one session is blocked waiting on a locked record.
Figure 17. SQL script users waiting on resources
-- seswait.sql
select username,
is_wlatch, -- blocked waiting on a latch
is_wlock, -- blocked waiting on a locked record or table
is_wbuff, -- blocked waiting on a buffer
is_wckpt, -- blocked waiting on a checkpoint
is_incrit -- session is in a critical section of transaction
-- (e.g writting to disk)
from syssessions
order by username;
Sample Output
username is_wlatch is_wlock is_wbuff is_wckpt is_incrit
lester 0 1 0 0 0
lester 0 0 0 0 0
lester 0 0 0 0 0
This view syssesprof provides a way to find out at a given point in time how much of your server resources each user is using. The view contains the following information.
View syssesprof
Column Data Type Description
sid integer, Session Id
lockreqs decimal(16,0) Locks requested
locksheld decimal(16,0) Locks held
lockwts decimal(16,0) Locks waits
deadlks decimal(16,0) Deadlocks detected
lktouts decimal(16,0) Deadlock timeouts
logrecs decimal(16,0) Logical Log records written
isreads decimal(16,0) Reads
iswrites decimal(16,0) Writes
isrewrites decimal(16,0) Rewrites
isdeletes decimal(16,0) Deletes
iscommits decimal(16,0) Commits
isrollbacks decimal(16,0) Rollbacks
longtxs decimal(16,0) Long transactions
bufreads decimal(16,0) Buffer reads
bufwrites decimal(16,0) Buffer writes
seqscans decimal(16,0) Sequential scans
pagreads decimal(16,0) Page reads
pagwrites decimal(16,0) Page writes
total_sorts decimal(16,0) Total sorts
dsksorts decimal(16,0) Sorts to disk
max_sortdiskspace decimal(16,0) Max space used by a sort
logspused decimal(16,0) Current log bytes used
maxlogsp decimal(16,0) Max bytes of logical logs used
This table contains data since the user logged on. Each time a user disconnects their data is lost so you cannot use this data for charging the user for server usage. Also, when a DBA resets the server statistics with the command "tbstat -z", all profile data is reset to zero.
I like to monitor the number of locks used by each user and their buffer usage. The following is an example query.
Figure 19. SQL script to monitor resource usage by user
-- sesprof.sql
select username,
syssesprof.sid,
lockreqs,
bufreads,
bufwrites
from syssesprof, syssessions
where syssesprof.sid = syssessions.sid
order by bufreads desc
This view contains information about all active locks on your server. It can be very large; if you have a lot of users and your server is configured to handle a large number of locks, you could end up with hundreds of thousands or more records in this view. This view is composed of six tables, and queries on this view will create a temp table which is logged to your logical log. The performance may be a bit slow because of the sheer volume of data produced by this view. However, the data this view contains can be very helpful to understanding how your system is performing.
View syslocks
Column Data Type Description
dbsname char(18) Database name
tabname char(18) Table name
rowidlk integer Rowid for index key lock
keynum smallint Key number of index key lock
owner integer Session ID of lock owner
waiter integer Session ID of first waiter
type char(4) Type of Lock
Types of Locks
B - byte lock
IS - intent shared lock
S - shared lock
XS - repeatable read shared key
U - update lock
IX - intent exclusive lock
SIX - shared intent exclusive
X - exclusive lock
XR - repeatable read exclusive
Basically there are three types of locks: a shared lock (S), an exclusive lock (X), and an update lock(U). A shared lock allows other users to also read the data but none may change it. An exclusive lock does not allow anyone else to lock that data even in shared mode. An update lock prevents other users from changing data while you are changing it.
There are six objects that can be locked in OnLine.
One of the key data elements missing from this view is the username and session id (sid) of the user who has a lock. The following query adds the user's name and session id and uses the underlying tables to improve performance. It also puts the data into a temp table from which you can select subsets of data much more quickly than if you were to repeat the query.
Figure 20. SQL script to show all locks
-- locks.sql
select dbsname,
b.tabname,
rowidr,
keynum,
e.txt type,
d.sid owner,
g.username ownername,
f.sid waiter,
h.username waitname
from syslcktab a,
systabnames b,
systxptab c,
sysrstcb d,
sysscblst g,
flags_text e,
outer ( sysrstcb f , sysscblst h )
where a.partnum = b.partnum
and a.owner = c.address
and c.owner = d.address
and a.wtlist = f.address
and d.sid = g.sid
and e.tabname = 'syslcktab'
and e.flags = a.type
and f.sid = h.sid
into temp A;
select dbsname,
tabname,
rowidr,
keynum,
type[1,4],
owner,
ownername ,
waiter,
waitname
from A;
Example SQL Output
dbsname sysmaster
tabname a
rowidr 0
keynum 0
type X
owner 47
ownername lester
waiter
waitname
The above example SQL output shows the row from syslocks that displays the exclusive lock I created on the temp table "A" while running the query.
A more important use of this query is to find out when one user is waiting on the lock owned by another user. When a user has a database object locked, the first user waiting on the object can be displayed. (This will only occur when a user has set lock mode to WAIT). The following script displays only the users that have locks where someone else is waiting on their process. There is one key difference between this script and the one above. The tables sysrstcb and sysscblst in this script do not use an outer join, so only rows that have waiters will be returned. In this example "linda" has an update lock on a row and "lester" is waiting for that update to complete.
Figure 21. SQL script to show users waiting on locks
-- lockwaits.sql
database sysmaster;
select dbsname,
b.tabname,
rowidr,
keynum,
e.txt type,
d.sid owner,
g.username ownername,
f.sid waiter,
h.username waitname
from syslcktab a,
systabnames b,
systxptab c,
sysrstcb d,
sysscblst g,
flags_text e,
sysrstcb f , sysscblst h
where a.partnum = b.partnum
and a.owner = c.address
and c.owner = d.address
and a.wtlist = f.address
and d.sid = g.sid
and e.tabname = 'syslcktab'
and e.flags = a.type
and f.sid = h.sid
into temp A;
select dbsname,
tabname,
type[1,4],
owner,
ownername ,
waitname
from A;
SQL Output
dbsname tabname type owner ownername waitname
stores7 items U 29 linda lester
This is a supported view that shows all sessions that are blocked and waiting on a database object. It shows the amount of time a user has been waiting. On a well tuned system this table should be empty. However, when the table is not empty, it provides useful information on what is causing your performance to slow down.
View sysseswts
Column Data Type Description
sid integer Session ID
reason char(50) Description of reason for wait
numwaits integer Number of waits for this reason
cumtime float Cumulative wait time for this reason
maxtime integer Max wait time for this reason
Several of the SMI tables are not documented and not officially supported. These could change in future releases. Two additional unsupported tables I have found helpful are systrans and syssqexplain.
Three of the fields in systrans are very helpful to determine what logical log number a transaction began in, and the current logical log number in use by a transaction.
Key systrans fields
Column Data Type Description
tx_id integer pointer to transaction table
tx_logbeg integer transaction starting logical log
tx_loguniq integer transaction current logical log number
This can be used to create a script to determine what logical log files have active transactions. The output of this will tell you what logical logs are free and available for reuse. This first script lists all user transactions and what logs they are using.
Figure 22. SQL script to display transactions and logs used
-- txlogpos.sql
select
t.username,
t.sid,
tx_logbeg,
tx_loguniq,
tx_logpos
from systrans x, sysrstcb t
where tx_owner = t.address
SQL Output
username sid tx_logbeg tx_loguniq tx_logpos
informix 1 0 16 892952 informix 0 0 0 0 informix 8 0 0 0 lester 53 0 0 0 informix 12 0 0 0 lester 51 14 16 0
This shows that my logical logs numbered 14 to 16 are in use by transactions.
Another helpful use of this view is to summarize the transactions by logical logs. This next script show my transaction status by logical log.
Figure 23. SQL script to view logical logs status
-- logstat.sql
database sysmaster;
-- select transaction data into a temp table
select tx_logbeg, tx_loguniq
from systrans
into temp b;
-- count how may transactions begin in each log
select tx_logbeg, count(*) cnt
from B
where tx_logbeg > 0
group by tx_logbeg
into temp C;
-- count how many transactions currently are in each log
select tx_loguniq, count(*) cnt
from B
where tx_loguniq > 0
group by tx_loguniq
into temp D;
-- join data from counts with syslogs
select
uniqid,
size,
is_backed_up, -- 0 = no, 1 = yes log is backed up
is_archived, -- 0 = no, 1 = yes log is on last archive
c.cnt tx_beg_cnt,
d.cnt tx_curr_cnt
from syslogs, outer c, outer D
where uniqid = c.tx_logbeg
and uniqid = d.tx_loguniq
order by uniqid
SQL Output
uniqid size is_backed_up is_archived tx_beg_cnt tx_curr_cnt
10 500 1 1
11 500 1 1
12 500 1 1
13 500 1 1
14 500 1 1
15 500 1 1
16 500 0 1 1 2
This shows that all logs are backed up except the current one, and it has two active transactions.
Have you ever wanted to run a query to see what your users were doing? The view syssqexplain contains some of the data from a user's session, including the sql that they are currently executing. Try this query on your system sometime to see your user's SQL.
Figure 24. SQL to view current executing SQL
-- syssql.sql
select username,
sqx_sessionid,
sqx_conbno,
sqx_sqlstatement
from syssqexplain, sysscblst
where sqx_sessionid = sid
SQL Output
username lester
sqx_sessionid 55
sqx_conbno 2
sqx_sqlstatement select username,sqx_sessionid, sqx_conbno, sqx_sqlstatement
from syssqexplain, sysscblst
where sqx_sessionid = sid
username lester
sqx_sessionid 51
sqx_conbno 0
sqx_sqlstatement update items set total_price = 300 where item_num = 1
The sysmaster database is a great tool for a DBA to monitor the Informix server. If you have any questions or suggestions please send me E-mail at lester@advancedatatools.com. Also, if you have any creative scripts for monitoring your server with the sysmaster database, please send them in and I may include them in the future publications.
|
Украинская баннерная сеть
|