System Administration Certification exam 918 for IBM Informix Dynamic Server 11 prep, Part 3: Troubleshooting

Explore the topic of troubleshooting for IBM® Informix® Dynamic Server. After a brief overview, you'll learn about the various tools that are available for troubleshooting, and then look at examples of how to examine the database server to solve problems. The third in a series of eight tutorials, use this tutorial to help prepare for the IDS 11 exam 918.

Joseph W. Baric, Jr. (jbaric@us.ibm.com), Advanced Support Engineer, IBM

Joe Baric photoJoe Baric has been working in Advanced Support for Informix Products for over ten years. In that time he has coded new features, provided bug fixes, served as a subject matter expert, helped develop course materials, and taught various classes on IDS. He can be reached at jbaric@us.ibm.com.



24 May 2007

Also available in Chinese

Before you start

About this series

Thinking about seeking certification on System Administration for IBM IDS 11 (Exam 918)? If so, you're in the right spot. This series of eight IDS certification preparation tutorials covers all the basics -- the topics you'll need to understand before you read the first exam question. Even if you're not planning to seek certification right away, this set of tutorials is a great place to start learning what's new in IDS 11.

About this tutorial

IDS is a large and complicated product. It can sometimes be mystifying wondering about where to start when looking how to diagnose a problem.

This tutorial is designed to introduce you to a variety of monitoring tools that are available with IDS 11 and to show you how each are used. In this tutorial, you will learn about:

  • The sysmaster database
  • The sysadmin database
  • The onstat utility
  • Use concurrency

This tutorial is the third in a series of eight tutorials that can help you prepare for the System Administration for IBM IDS V11 Certification (Exam 918). The material in this tutorial primarily covers the objectives in Section 3 of the test, which is entitled "Troubleshooting."

Objectives

After completing this tutorial, you should be able to:

  • Use system-monitoring interface
  • Know the tables in the sysadmin database
  • Use onstat to examine shared memory
  • Know use concurrency with lock level option

Prerequisites

IDS 11 installation is covered in part 1 of this tutorial series. If you haven't already done so, consider downloading and installing a copy of IBM IDS 11. Installing IDS will help you understand many of the concepts that are tested on the System Administration for IBM IDS V11 Certification exam.

System requirements

You do not need a copy of IDS to complete this tutorial. However, you will get more out of the tutorial if you download the free trial version of IBM IDS 11 to work along with this tutorial.


Using IDS databases and tools for troubleshooting

You can use a variety of tools and databases to examine the state of the engine and shared memory structures. This tutorial covers the following databases and tools:

  • The onstat utility
  • The sysmaster database
  • The sysadmin database

Troubleshooting in general

Troubleshooting is an iterative process. To approach troubleshooting, it is necessary to understand the processes of what you are looking at.

To utilize the various approaches that are introduced here, it is necessary to understand what is normal behavior and what is not. Once you have found out what is wrong with what you are looking at, then you can use these tools to further dig into why.

This tutorial shows general techniques to approach the following kinds of issues:

  • Memory issues
  • Disk space issues
  • Networking issues
  • Buffer pool issues
  • Locking and concurrency

Tracing is not covered in this tutorial.

Getting started

One of the big problems with an application such as IDS is knowing where to start. Typically, it is very helpful to try to state the problem in a single sentence to codify what the issue is. The closer that you can codify this to IDS terms, the easier it is to apply.

As an example, an issue that a DBA may run into is that a client application appears locked.

So, to look at the problem with IDS perspective, you can ask "What is the application waiting on?"

From understanding the architecture you understand that the entry point of an application to the engine is the sqlexec. So, the next question to ask is "Which sqlexec thread?"

You can then look at all userthreads by doing an onstat -u, which you can tie back to your application (using of the tty column OR the username).

Now, from the flags of the onstat -u, you may get exactly what you need. Say that the first column of the flags is a B. That explains that you are waiting on a buffer. The questions you then have to ask are "Which buffer? And who owns it?"

Run an onstat -b | grep <the address in the wait column> to return the line of that buffer.

In that line is the owner column. You can map the owner column back to the owner with an onstat -u | grep <address in the owner column>.

You can then get the session id from that onstat -u output.

This information can also be gathered by querying the sysmaster database. The benefit of the sysmaster database is that queries can be saved for future use. So once a problem has been tracked down, the methodology can then be reused with sql rather than typing the commands out. It's just a matter of preference

Throughout the tutorial, there are examples of how to apply the techniques to help narrow down problems with IDS.


The onstat utility

onstat is a powerful utility and can be used to look at a variety of things for troubleshooting purposes, including memory usage, network usage, session activities, buffer pool usage, and disk usage. In this section, examine how you can use onstat for troubleshooting each of these areas.

Memory usage

There are several commands that track memory usage:

onstat -g mem

Initiating the onstat -g mem command provides a list of memory usage of different pools in memory.

Listing 1. onstat -g mem output
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 2 days 01:28:21
 -- 39936 Kbytes

Pool Summary:
afpool       V     10ad28040        8192             2488             5	         3
tpcpool      V     10b034040        40960            4776             32	         3
seqpool      V     10b06a040        4096             768              2           1
pnlpool      V     10b037040        77824            4344             69	         5
sbtlist      V     10ae10040        20480            7232             4	         3
dstpool      V     10b033040        8192             3320             2	         2
sqcrypto     V     10b21b040        4096             504              2           1
ampool       V     10b061040        8192             3088             22	         1
Blkpool Summary:
name         class addr             size             #blks
mt           V     10ad2b450        1527808          21
global       V     10ad26290        0                0

Table 1 describes the information provided in the pool summary.

Table 1. Pool summary description
NameDescriptoin
NamePool name
ClassShared memory class (R=Resident, V=Virtual)
AddrPool header memory address
sizeTotal size of the pool in bytes
Freesize Free memory in the pool
#allocfrag Number of allocated fragments
#freefrag Number of free fragments
Table 2. Blkpool summary description
NameDescriptoin
NamePool name
ClassShared memory class (R=Resident, V=Virtual)
AddrPool header memory address
sizeTotal size of the pool in bytes
#blksize Number of blocks in the pool

onstat -g ses

onstat -g ses can show memory usage for a single session. This can be very handy in looking for such things as a memory leak if you do an onstat -g ses -r and see memory allocation growing constantly but the memory usage is not.

Let's take a look at the general output first:

Listing 2. onstat -g ses output
$  onstat -g ses

IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 2 days 01:52:52
 -- 39936 Kbytes

session                                      #RSAM    total      used       dynamic
id       user     tty      pid      hostname threads  memory     memory     explain
35       informix -        0        -        0        12288      11592      off
18       informix -        0        -        1        425984     338128     off
17       informix -        0        -        1        434176     337408     off
16       informix -        0        -        1        282624     227560     off
5        informix -        0        -        0        12288      11592      off
3        informix -        0        -        0        16384      13176      off
2        informix -        0        -        0        12288      11592      off
35	informix -        0        -        0        81920	 76360	   off

The most important parts are the total memory and used memory. But you can granulate even further by using onstat -g ses for a particular session id.

Listing 3. Output for onstat -g ses for a particular session
$ onstat -g ses 35

IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 2 days 01:55:28 
-- 39936 Kbytes

session                                      #RSAM    total      used       dynamic
id       user     tty      pid      hostname threads  memory     memory     explain
35       informix 53       15046    ryleh    1        81920      76360      off

tid      name     rstcb            flags    curstk   status
59       sqlexec  10afaa7d0        Y--P---  7791     cond wait(sm_read)

Memory pools    count 2
name         class addr              totalsize  freesize   #allocfrag #freefrag
35           V     10bf99040        77824      4720       110        5
35*O0        V     10c072040        4096       840        1          1

name           free       used           name           free       used
overhead       0          6512           scb            0          144
opentable      0          2568           filetable      0          496
log            0          12096          temprec        0          1696
keys           0          800            ralloc         0          18992
gentcb         0          1640           ostcb          0          2864
sqscb          0          18880          sql            0          7
rdahead        0          160            hashfiletab    0          552
osenv          0          2880           buft_buffer    0          2168
sqtcb          0          3216           fragman        0          488
sapi           0          64

sqscb info
scb              sqscb            optofc   pdqpriority sqlstats optcompind  directives
10bde6400        10bf63028        0        0           0        2           1


Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers Explain

35    SELECT         sysmaster          CR  Not Wait   0    0    9.24 Off


Current statement name : slctcur

Current SQL statement :
  select * from systables

Last parsed SQL statement :
  select * from systables

onstat -g afr

The onstat -g afr option prints allocated memory fragments for a specified session or shared-memory pool. Each session is allocated a pool of shared memory.

This is a good command to start when trying to granulate which pool of memory is being allocated to. For instance, if you constantly see the ralloc pool growing as you run this repetitively (onstat -g afr -r), then you may have isolated what pool of memory is growing unchecked.

Listing 4. Output for onstat -g afr
$ onstat -g afr 35
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 2 days 02:12:18 
-- 39936 Kbytes

Allocations for pool name 35:
addr             size       memid
10bf99000        3256       overhead
10bf99cb8        80         scb
10bf99d08        64         scb
1bf99d48         64         ostcb
10bf99d88        552        opentable
10bf99fb0        80         osenv
10bf63000        6856       sqscb
10bf64ac8        64         sqscb
10bf64b08        72         sql
10bf64b50        72         filetable
10bf64b98        80         fragman
10bf64be8        80         sqscb
10bf64c38        64         sqscb
.
.
.
.
.
.
10bf57f78        136        fragman
10c071000        2744       ralloc
10c071ab8        1024       ralloc
10c073000        2168       buft_buffer
Table 3. Column descriptions
OutputDescription
addrMemory address of the pool fragment
sizeSize, in bytes, of the pool fragment
memidMemory ID of the pool fragment

onstat -g ffr

Much like the afr is the ffr, which shows the free fragments for a pool of shared memory.

Listing 5. Output for onstat -g ffr
$ onstat -g ffr 35
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 2 days 02:15:57
 -- 39936 Kbytes

Free lists for pool name 35:
addr             size        idx
10c073878        1928        1
10bf9ba40        104         11
10c071eb8        328         39
10bf9cdd8        552         66
10bf548a8        1808        99
Table 4. Column descriptions
OutputDescription
addrMemory address of the pool fragment
sizeSize, in bytes, of the pool fragment

Troubleshooting a memory issue

Let's say that the System Administrator calls you and says that IDS is taking up too much memory, and that it's constantly growing. If you have not made any configuration changes, the first thing to investigate is if there are any sessions that are taking up a lot of memory.

Listing 6. onstat -g ses output
$  onstat -g ses

IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 2 days 01:52:52
 -- 39936 Kbytes

session                                      #RSAM    total      used       dynamic
id       user     tty      pid      hostname threads  memory     memory     explain
35       informix -        0        -        0        12288      11592      off
18       informix -        0        -        1        425984     338128     off
17       informix -        0        -        1        434176     337408     off
.
.
.
2        informix -        0        -        0        12288      11592      off
2301     bad_app  -        0        -        1      3203072      16384 	   off
8220     bad_app  -        0        -        1      3194880      16384 	   off
1704     bad_app  -        0        -        1      3203072      16384 	   off
430      bad_app  -        0        -        1     19169280      16384 	   off
1991     bad_app  -        0        -        1      3203072      16384 	   off

You can see that there is an app that has grabbed only uses around 16KB of memory and yet has allocated, at least in one session, over a gigabyte of memory.

Let's take a look at that session again.

Listing 7. onstat -g ses | grep 430
430      bad_app  -        0        -        1     19202048      16384 	    off

Wow! It's still growing. What you have here is a memory leak. At this point, you can put a call in to your developers and ask what changes they have made.

Network usage

onstat -g nta

Using the onstat -g nta prints combined network statistics from -g ntd, -g ntm, -g ntt, and -g ntu. If MaxConnect is installed, this command prints statistics that you can use to tune MaxConnect performance.

onstat -g ntd

onstat -g ntd prints global network information:

Listing 8. Output for onstat -g ntd
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 00:00:44 
-- 38912 Kbytes

global network information:
#netscb connects     read    write    q-free  q-limits  q-exceed alloc/max
6/   6        1        8        8    0/   0  135/  10    0/   0    1/   1

Client Type     Calls   Accepted   Rejected       Read      Write
sqlexec         yes            1          0          7          8
srvinfx         yes            0          0          0          0
onspace         yes            0          0          0          0
onlog           yes            0          0          0          0
onparam         yes            0          0          0          0
oncheck         yes            0          0          0          0
onload          yes            0          0          0          0
onunload        yes            0          0          0          0
onmonitor       yes            0          0          0          0
dr_accept       yes            0          0          0          0
cdraccept       no             0          0          0          0
ontape          yes            0          0          0          0
srvstat         yes            0          0          0          0
asfecho         yes            0          0          0          0
listener        yes            0          0          1          0
crsamexec       yes            0          0          0          0
onutil          yes            0          0          0          0
safe            yes            0          0          0          0
drdaexec        no             0          0          0          0
smx             yes            0          0          0          0
Totals                         1          0          8          8

onstat -g ntm

onstat -g ntm prints network mail statistics.

Listing 9. Output for onstat -g ntm
$ onstat -g ntm
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 00:03:35 
-- 38912 Kbytes

global network information:
#netscb connects     read    write    q-free  q-limits  q-exceed alloc/max
6/   6        1        8        8    0/   0  135/  10    0/   0    1/   1

Network mailbox information:
box           netscb thread name     max received   in box   max in box full signal
5        10b239928 tlitcppoll       10        4        0        2        0	yes
6        10b250928 tlitcplst        10        0        0        0        0	no

onstat -g ntt

onstat -g ntt prints full network times.

Listing 10. Output for onstat -g ntt
$ onstat -g ntt
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 00:04:55
 -- 38912 Kbytes

global network information:
#netscb connects     read    write    q-free  q-limits  q-exceed alloc/max
6/   6        1        8        8    0/   0  135/  10    0/   0    1/   1

Individual thread network information (times):
   netscb thread name    sid     open     read    write address
10b4bf368 sqlexec          4 07:34:44 07:34:55 07:34:55
10c10ccd0                 17 07:34:28
10b270790                 16 07:34:28
10b3abd18                 15 07:34:28
10b250928 tlitcplst        3 07:34:22 07:34:44          ryleh|1537|tlitcp
10b239928 tlitcppoll       2 07:34:22

Notice that the output shows a great deal of information in the write address column. It gives the server | port | protocol combination out of the sqlhosts file. This can be handy in trying to track down problems if you have multiple listener threads.

onstat -g ntu

onstat -g ntu prints network user statistics.

Listing 11. Output for onstat -g ntu
$ onstat -g ntu
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 00:08:28 
-- 38912 Kbytes

global network information:
#netscb connects     read    write    q-free  q-limits  q-exceed alloc/max
6/   6        1        8        8    0/   0  135/  10    0/   0    1/   1

Individual thread network information (basic):
netscb type   thread name    sid   fd poll    reads   writes q-nrm q-pvt q-exp
10b4bf368 tlitcp sqlexec          4    2    5        8        8  0/ 1  1/1  0/ 0
10c10ccd0 tlitcp unknown         17    0    0        0        0  0/ 0  0/ 0  0/ 0
10b270790 tlitcp unknown         16    0    0        0        0  0/ 0  0/0  0/ 0
10b3abd18 tlitcp unknown         15    0    0        0        0  0/ 0  0/ 0  0/ 0
10b250928 tlitcp tlitcplst        3    1    5        1        0  0/ 0  0/0  0/ 0
10b239928 tlitcp tlitcppoll       2    0    5        7        0  0/ 0  0/0/ 0

Troubleshooting a network issue

Let's say that as a DBA you start getting calls from users saying that some of them are having trouble getting connected.

A quick look at the online log doesn't show anything. So, let's run an onstat -g nta to see if there is anything anomalous.

Listing 12. onstat-g nta
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 01:16:37 -- 389
12 Kbytes

global network information:
  #netscb connects     read    write    q-free  q-limits  q-exceed alloc/max
   6/   7       15      936      714    1/   1  135/  10    0/   0    1/   1

Individual thread network information (basic):
          netscb type   thread name    sid   fd poll    reads   writes q-nrm q-p
vt q-exp
       10b434b88 tlitcp unknown         19    0    0        0        0  0/ 0  0/
 0  0/ 0
       10b294848 tlitcp unknown         18    0    0        0        0  0/ 0  0/
 0  0/ 0
       10b270ae0 tlitcp unknown         17    0    0        0        0  0/ 0  0/
 0  0/ 0
       10b274928 tlitcp tlitcplst        4    2    5        0        0  0/ 0  0/
 0  0/ 0
       10b250928 tlitcp tlitcplst        3    1    5       15        0  0/ 0  0/
 0  0/ 0
       10b239928 tlitcp tlitcppoll       2    0    5      936        0  0/ 0  0/
 0  0/ 0
       10b239928 tlitcp tlitcppoll       2    0    5      936        0  0/ 0  0/
 0  0/ 0

Individual thread network information (times):
          netscb thread name    sid     open     read    write address

       10b434b88                 19 09:54:00

       10b294848                 18 09:54:00

       10b270ae0                 17 09:54:00

       10b274928 tlitcplst        4 09:53:54 10:04:03          ryleh|1538|tlitcp

       10b250928 tlitcplst        3 09:53:54 11:54:38          ryleh|1537|tlitcp

       10b239928 tlitcppoll       2 09:53:54


Network mailbox information:
 box           netscb thread name     max received   in box   max in box full si
gnal
   5        10b239928 tlitcppoll       10       49        0        4        0
gnal
   5        10b239928 tlitcppoll       10       49        0        4        0
 yes
   6        10b250928 tlitcplst        10        0        0        0        0
  no
   7        10b274928 tlitcplst        10        0        0        0        0
  no

Client Type     Calls   Accepted   Rejected       Read      Write
sqlexec         yes           15          0        921        714
srvinfx         yes            0          0          0          0
onspace         yes            0          0          0          0
onlog           yes            0          0          0          0
onparam         yes            0          0          0          0
oncheck         yes            0          0          0          0
onload          yes            0          0          0          0
onunload        yes            0          0          0          0
onmonitor       yes            0          0          0          0
dr_accept       yes            0          0          0          0
cdraccept       no             0          0          0          0
ontape          yes            0          0          0          0
srvstat         yes            0          0          0          0
asfecho         yes            0          0          0          0
listener        yes            0          0         15          0
onunload        yes            0          0          0          0
onmonitor       yes            0          0          0          0
dr_accept       yes            0          0          0          0
cdraccept       no             0          0          0          0
ontape          yes            0          0          0          0
srvstat         yes            0          0          0          0
asfecho         yes            0          0          0          0
listener        yes            0          0         15          0
crsamexec       yes            0          0          0          0
onutil          yes            0          0          0          0
safe            yes            0          0          0          0
drdaexec        no             0          0          0          0
smx             yes            0          0          0          0
Totals                        15          0        936        714


No MaxConnect instances connected

IO statistics for each MaxConnect instance:
   IMCid    header      data   partial   blocked      data   partial   blocked
             reads     reads     reads     reads    writes    writes    writes
       -         -         -         -         -         -         -         -

From the onstat -g ntm output (the mailboxes), you see that one of the listener threads has not done any reading in quite a while.

You can correlate the mailboxes to a specific port/DBSERVERALIAS combination by looking in the sqlhosts file for the corresponding entry in the first column.

The line from onstat -g ntm
       10b274928 tlitcplst        4 09:53:54 10:04:03          ryleh|1538|tlitcp
The line from sqlhosts
demo_alias	ontlitcp	ryleh	1538
The line from the ONCONFIG
DBSERVERALIASES	demo_alias

So, in a production environment, the facile solution is to have your users switch to another DBSERVERALIAS or DBSERVERNAME. To pursue the root cause is beyond the scope of this tutorial.

Session activities

onstat -g ses

Running the onstat -g ses without a particular session id generates a one-line summation of each session currently active on the system.

As mentioned earlier, this command is very useful in tracking down memory leaks on a client application.

It can be run with the -r option ( onstat -g ses -r ) and watch if the total memory is growing constantly while the total amount of used memory is static.

Listing 13. Output for onstat -g ses
$ onstat -g ses
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 00:14:47 
-- 38912 Kbytes

session                                      #RSAM    total      used       dynamic
id       user     tty      pid      hostname threads  memory     memory     explain
18       informix -        0        -        0        12288      11592      off
17       informix -        0        -        1        303104     260672     off
16       informix -        0        -        1        307200     265480     off
15       informix -        0        -        1        278528     227640     off
4        informix 53       25457    ryleh    1        98304      91928      off
3        informix -        0        -        0        16384      13176      off
2        informix -        0        -        0        12288      11592      off

From here you can look at the pid of a process that is a compliant application, number of threads it may have, and total memory.

For more information about a particular session, you can use onstat -g ses with a particular session id:

Listing 14. Output for onstat -g ses with a specified session id
$ onstat -g ses 35

IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 2 days 01:55:28
 -- 39936 Kbytes

session                                      #RSAM    total      used       dynamic
id       user     tty      pid      hostname threads  memory     memory     explain
35       informix 53       15046    ryleh    1        81920      76360      off

tid      name     rstcb            flags    curstk   status
59       sqlexec  10afaa7d0        Y--P---  7791     cond wait(sm_read)

Memory pools    count 2
name         class addr              totalsize  freesize   #allocfrag #freefrag
35           V     10bf99040        77824      4720       110        5
35*O0        V     10c072040        4096       840        1          1

name           free       used           name           free       used
overhead       0          6512           scb            0          144
opentable      0          2568           filetable      0          496
log            0          12096          temprec        0          1696
keys           0          800            ralloc         0          18992
gentcb         0          1640           ostcb          0          2864
sqscb          0          18880          sql            0          7
rdahead        0          160            hashfiletab    0          552
osenv          0          2880           buft_buffer    0          2168
sqtcb          0          3216           fragman        0          488
sapi           0          64

sqscb info
scb              sqscb            optofc   pdqpriority sqlstats optcompind  directives
10bde6400        10bf63028        0        0           0        2           1


Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers Explain

35    SELECT         sysmaster          CR  Not Wait   0    0    9.24 Off


Current statement name : slctcur

Current SQL statement :
  select * from systables

Last parsed SQL statement :
  select * from systables

As you can see, there is far more information about the session here. You can find the isolation level, the current sql statement, the front end version, as well as memory usage (previously discussed).

A note about the rstcb. This corresponds to the first column in the output of onstat -u.

Troubleshooting at the session level

Let's say that after you resolve your issue with that DBSREVERALIAS, there is still one user who is complaining that his application is still hung. So, you ask him his username: bad_user.

Listing 15. onstat -g ses | grep bad_user
2257        bad_user -        0        -        0        122288      121592      off

There isn't anything really alarming here. The memory used is pretty close to what is allocated. Let's use onstat -g ses to see what this user is doing:

Listing 16. Output for onstat -g ses with a specified session id
$ onstat -g ses 35

IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 2 days 01:55:28
 -- 39936 Kbytes

session                                      #RSAM    total      used       dynamic
id       user     tty      pid      hostname threads  memory     memory     explain
2257     bad_user        15098      ryleh    1        122288      121592     off

tid      name     rstcb            flags    curstk   status
2070     sqlexec  10afaa7d0        Y--P---  7791     cond wait(sm_read)

Memory pools    count 2
name         class addr              totalsize  freesize   #allocfrag #freefrag
35           V     10bf99040        77824      4720       110        5
35*O0        V     10c072040        4096       840        1          1

name           free       used           name           free       used
overhead       0          6512           scb            0          144
opentable      0          2568           filetable      0          496
log            0          12096          temprec        0          1696
keys           0          800            ralloc         0          18992
gentcb         0          1640           ostcb          0          2864
sqscb          0          18880          sql            0          7
rdahead        0          160            hashfiletab    0          552
osenv          0          2880           buft_buffer    0          2168
sqtcb          0          3216           fragman        0          488
sapi           0          64

sqscb info
scb              sqscb            optofc   pdqpriority sqlstats optcompind  directives
10bde6400        10bf63028        0        0           0        2           1


Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers Explain

35    INSERT         sysmaster          CR  Not Wait   0    0    9.24 Off


Current statement name : slctcur

Current SQL statement :
  INSERT INTO COMP_PREP_1_6   (contrct_id,cstomer_id,
    rep_id,run_mode,canvass_code, canvass_issue_year,
    channel_code,sort_ind)         SELECT  {+ ORDERED}
    CONTRACT.contract_id,         DPOP_CST_TMP.customer_id,
    ASSIGNMENT.rep_id,            'B',
    ASSIGNMENT.canvass_code,
    ASSIGNMENT.canvass_issue_year,ASSIGNMENT.channel_code,      4
                   FROM DPOP_CST_TMP,          CONTRACT,
    ASSIGNMENT,                   CLOSE_CANVASS_TMP             WHERE
    CONTRACT.cstomer_id =  DPOP_CST_TMP.cstomer_id    AND
    CONTRACT.contract_status = 'R'                          AND
    ASSIGNMENT.assignment_id = CONTRACT.assignment_id       AND
    ASSIGNMENT.canvass_code = CLOSE_CANVASS_TMP.canvass_code AND
    ASSIGNMENT.canvass_issue_year =
    CLOSE_CANVASS_TMP.canvass_issue_year                        AND
    (ASSIGNMENT.channel_code =CLOSE_CANVASS_TMP.channel_code OR
    CLOSE_CANVASS_TMP.channel_code = '**')                  AND EXISTS (
                SELECT 1                     FROM CTR_TRACKING CTRTRK1,
     CTR_TRACKING CTRTRK2     WHERE CTRTRK1.contract_id =
    CONTRACT.contract_id          AND CTRTRK1.contract_status = 'R'
                   AND CTRTRK1.ctap_event_id > ?
    AND CTRTRK2.contract_id =     CONTRACT.contract_id          AND
    CTRTRK2.contract_status = 'O'                           AND
    CTRTRK2.ctap_event_id = ( SELECT
    MAX(CTRTRK3.ctap_event_id)    FROM CTR_TRACKING CTRTRK3     WHERE
    CTRTRK3.contract_id =   CTRTRK1.contract_id           AND
    CTRTRK3.ctap_event_id = CTRTRK1.ctap_event_id  ))

Last parsed SQL statement :
  select * from CONTRACTS

That is a complicated insert. But wait. Look at the flags and the state of the thread.

The flags are Y--P---. From looking in the manual, you can see that the thread is yielding to some condition. And from the state of the thread, you know which condition that is: cond_wait(smread).

So, at this point, you ask the customer to look at the client application because the sqlexec is waiting for the client app to tell it to do something.

Buffer pool usage

onstat -p

onstat -p is a useful utility to look at buffer pool usage:

Listing 17. Output for onstat -p
$ onstat -p
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 00:17:03 
-- 3812 Kbytes

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
933      961      101742   99.08   173      441      6221     97.22


isamtot  open     start    read     write    rewrite  delete   commit     rollbk
19993    1533     2346     6077     685      37       346      807	 0

gp_read    gp_write   gp_rewrt   gp_del     gp_alloc   gp_free    gp_curs
0          0          0          0          0          0          0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes
0          0            0          3.55     0.52     2          2

bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seq scans
240      0        9130     0        0        0        385      137 0

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits
38         5          162        203        3

The first thing to look at is the ovbuff. Ovbuff is the number of times that the engine ran out of buffers to work with. If ovbuffs is increasing, then increasing the size of the buffer pool is warranted.

Note: This same information is contained in the SMI table sysprofile.

onstat -F

onstat -F is used to obtain a count by write type of the writes performed.

Listing 18. Output for onstat -F
$ onstat -F
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 00:20:45 
-- 38912 Kbytes

Fg Writes     LRU Writes    Chunk Writes
0             0             98

address           flusher  state    data     # LRU    Chunk    Wakeups  Idle Time
10afa5820        0        I        0        0        1        1239     1237.670
states: Exit Idle Chunk Lru

If Fg writes is increasing, then tuning is warranted.

onstat -R

onstat -R is used to monitor the LRU queues. For each queue, onstat -R lists the number of buffers in the queue, and the number and percentage of buffers that have been modified.

Listing 19. Output for onstat -R
$ onstat -R
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 00:30:51
 -- 3812 Kbytes
Buffer pool page size: 2048

8 buffer LRU queue pairs              priority levels
# f/m   pair total     % of    length       LOW      HIGH
0 F        624     100.0%      624        624          0
1 m                  0.0%        0          0          0
2 f        625     100.0%      625        625          0
3 m                  0.0%        0          0          0
4 f        626     100.0%      626        626          0
5 m                  0.0%        0          0          0
6 f        625     100.0%      625        625          0
7 m                  0.0%        0          0          0
8 f        625     100.0%      625        625          0
9 m                  0.0%        0          0          0
10 f        625     100.0%      625        625          0
11 m                  0.0%        0          0          0
12 f        625     100.0%      625        625          0
13 m                  0.0%        0          0          0>
14 f        625     100.0%      625        625          0
15 m                  0.0%        0          0          0
0 dirty, 5000 queued, 5000 total, 8192 hash buckets, 2048 buffer size
start clean at  60.000% (of pair total) dirty, or 374 buffs dirty, stop at
 50.000%
Table 5. Interpreting the output
ColumnDescription
Buffer pool page sizeThe page size of the buffer pool in bytes
#Shows the queue number. Each LRU queue is composed of two subqueues: an FLRU queue and an MLRU queue. (For a definition of FLRU and MLRU queues, see LRU queues in the shared-memory chapter of the IBM Informix Administrator's Guide.) Queues 0 and 1 belong to the first LRU queue, queues 2 and 3 belong to the second LRU queue, and so on.
f/mIdentifies queue type: This field has four possible values:
f -- Free LRU queue. In this context, free means not modified. Although nearly all the buffers in an LRU queue are available for use, the database server attempts to use buffers from the FLRU queue, rather than the MLRU queue. (A modified buffer must be written to disk before the database server can use the buffer.)
F -- Free LRU with fewest elements. The database server uses this estimate to determine where to put unmodified (free) buffers next.
m -- MLRU queue
M -- MLRU queue that a flusher is cleaning
lengthTracks the length of the queue measured in buffers
% ofShows the percent of LRU queue that this subqueue composes.
For example, suppose that an LRU queue has 50 buffers, with 30 of those buffers in the MLRU queue, and 20 in the FLRU queue. The % of column would list percents of 60.00 and 40.00, respectively.
pair totalProvides the total number of buffers in this LRU queue
priority levelsDisplays the priority levels: LOW, MED_LOW, MED_HIGH, HIGH

Summary information follows the individual LRU queue information. You can interpret the summary information as follows:

Table 6. Interpreting the output
ColumnDescription
dirtyThe total number of buffers that have been modified in all LRU queues
queuedThe total number of buffers in LRU queues
totalThe total number of buffers
hash bucketsThe number of hash buckets
buffer sizeThe size of each buffer
start cleanThe value of LRU_MAX_DIRTY
stop atThe value of LRU_MIN_DIRTY
priority downgradesThe number of LRU queues downgraded to a lower priority
priority upgradesThe number of LRU queues upgraded to a higher priority

Troubleshooting a buffer issue

Let's say that as the DBA you are starting to get users who call in and say that performance is slowing down.

One thing you may run just to see if there is any bottlenecks is an onstat -p output. Running it once is not necessarily helpful because it is just a snapshot But you can use the -r output to see if there is anything that is really slowing you down.

Listing 20. onstat -pr snippet
$ onstat -pr
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 00:17:03 
-- 3812 Kbytes

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
2007     1000     201742   99.08   173      441      6221     97.22


isamtot  open     start    read     write    rewrite  delete   commit     rollbk
19993    1533     2346     6077     685      37       346      807	 0

gp_read    gp_write   gp_rewrt   gp_del     gp_alloc   gp_free    gp_curs
0          0          0          0          0          0          0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes
0          0            0          3.53     0.52     2          2

bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seq scans
2400      0        9130     0        0        0        385      137 0

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits
38         5          162        203        3
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 00:17:03
-- 3812 Kbytes

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
2100     1005      203742   99.08   173      441      6221     97.22


isamtot  open     start    read     write    rewrite  delete   commit     rollbk
19993    1533     2346     6077     685      37       346      807	 0

gp_read    gp_write   gp_rewrt   gp_del     gp_alloc   gp_free    gp_curs
0          0          0          0          0          0          0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes
0          0            0          3.55     0.54     2          2

bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seq scans
2706      0        9130     0        0        0        385      137 0

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits
38         5          162        203        3
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 00:17:03
-- 3812 Kbytes

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
2103      1020      206641   99.08   173      441      6221     97.22


isamtot  open     start    read     write    rewrite  delete   commit     rollbk
19993    1533     2346     6077     685      37       346      807	 0

gp_read    gp_write   gp_rewrt   gp_del     gp_alloc   gp_free    gp_curs
0          0          0          0          0          0          0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes
0          0            0          3.57     0.54     2          2

bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seq scans
3002      0        9130     0        0        0        385      137 0

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits
38         5          162        203        3

From looking at the Listing 10, you know that bufwaits is increasing very quickly. There must be some page(s) that are very hot.

There are a couple of ways to try to dig down from this angle. My personal preference is try to find out what buffer is hot by looking at the onstat -X. The onstat -X output will print buffer accessor information.

Listing 21. Output for onstat -X
$ onstat -X

IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 01:38:47 -- 389
12 Kbytes

Buffers (Access)
address          owner            flags pagenum          memaddr          nslots
10a21ede0        0                103  11:5893         10a627800        9
pgflgs scount   waiter
80e    90     	10afa5028
 		10afa5820
 		10afa6028
 		.
 		.
 		.
 		10afaa028

Buffer pool page size: 2048
 200 modified, 5000 total, 8192 hash buckets, 2048 buffer size

You have one page that has a lot of people trying to access that page. From there, it becomes a simple matter of mapping that to the onstat -u/onstat -g ses level, and try to find out what that page is and how it is being used.

There is a big clue in the pgflags output. 0x80e means that it is a page from a big chunk and that it is a blob bitmap page.

The waiter and owner columns correspond to the first column in the onstat -u output.

Disk usage

onstat -d

onstat -d lists disk usage for dbspaces and chunks.

Listing 22. Output for onstat -d
 onstat -d
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 00:39:25 
-- 3812 Kbytes

Dbspaces
address       number   flags    fchunk   nchunks  pgsize   flags    owner	    name
10aedee78     1        0x40001  1        1        2048     N  B     informix rootdbs
1 active, 2047 maximum

Chunks
address   chunk/dbs offset size  free   bpages  flags  pathname
10aedf028 1     1   0      15000 1238   PO-B           /testing/prod/1110FC1B5/SERVER
                                                       /chunks/rootchunk
1 active, 32766 maximum

Note: The values in the "size" and "free" columns for DBspace chunks are displayed in terms of "pgsize" of the DBspace to which they belong.

Expanded chunk capacity mode: always.

Table 7. Highlights of the chunk output
ColumnDescription
SizeThe size of the chunk in online pages
FreeThe number of free online pages in that chunk

onstat -D

onstat -d is closely coupled with the onstat -D command.

Listing 23. Output for onstat -D
$ onstat -D
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 00:42:00 
-- 38912 Kbytes

Dbspaces
address      number   flags     fchunk   nchunks  pgsize   flags   owner     name
10aedee78    1        0x40001   1        1        2048     N  B    informix  rootdbs
1 active, 2047 maximum

Chunks
address      chunk/dbs  offset    page Rd  page Wr  pathname
10aedf028    1     1    0         964      492      /testing/prod/1110FC1B5/SERVER/
					      chunks/rootchunk
1 active, 32766 maximum

Note: The values in the "page Rd" and "page Wr" columns for DBspace chunks are displayed in terms of system base page size.

Expanded chunk capacity mode: always.

Instead of the size of the chunks, what you see are the amounts of pages read and written per chunk.

I/O at a chunk level can be monitored with this command run repetitively. It is not as fine or as granulated as looking at I/O at the partition level (onstat -P). But it is a good way to look at stuff in a high level.

onstat -g iof

The onstat -g iof option displays the number of reads from each chunk and the number of writes to each chunk. If one chunk has a disproportionate amount of I/O activity against it, this chunk might be a system bottleneck. This option is useful for monitoring the distribution of I/O requests against the different fragments of a fragmented table.

Listing 24. Output for onstat -g iof
$ onstat -g iof

IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 01:21:55 
-- 38912 Kbytes

AIO global files:
gfd pathname         bytes read     page reads  bytes write    page writes io/s
3   rootchunk        2697216        1317        2418688        1181        224.8
op type     count          avg. time
seeks       0              N/A
reads       904            0.0010
writes      454            0.0112
kaio_reads  0              N/A
kaio_writes 0              N/A

onstat -g iob

The onstat -g iob option prints a summary of big buffer use.

onstat -g iov

The onstat -g iov option shows asynchronous I/O statistics for each virtual processor.

onstat -g ioa

The onstat -g ioa combines the onstat -g iob, the onstat -g iof, and the onstat -g iov in one output.

Troubleshooting disk issues

Troubleshooting at the disk level is mostly done in the context of performance. Usually, there is some process, such as a load or batch process that usually takes a certain amount of time but, for some reason, is not.

Say a user calls in, is trying to update several million rows of a table, and wants to know why it's taking so long.

One way to approach it is to look at things at the chunk level.

Listing 25. onstat -Dr snippet
IBM Informix Dynamic Server Version 11.10.FB5TL -- On-Line -- Up 06:23:43 -- 389
12 Kbytes

Dbspaces
address      number   flags      fchunk   nchunks  pgsize   flags   owner     name
10aeded78    1        0x40001    1        1        2048     N B     informix  rootdbs
.
.
10aedee88    2        0x40001    12       1        2048     N B     informix  datadbs
.
.
10aedee98    3        0x40001    17       1        2048     N B     informix  idxdbs
 1 active, 2047 maximum

Chunks
address      chunk/dbs  offset     page Rd  page Wr   pathname
10aedf028    12     2   0          157000   42790     /dev/chunks/datachunk
.
.
.
10aeee038	    17	  3   0	        223414   1324123  /dev/chunks/indexdbschunk
 1 active, 32766 maximum

NOTE: The values in the "page Rd" and "page Wr" columns for DBspace chunks
      are displayed in terms of system base  page size.

Expanded chunk capacity mode: always

Note: Due to space constraints, I am just including the relevant chunk information


Chunks
address          chunk/dbs  offset     page Rd  page Wr  pathname
10aedf028        12     2    0         157000     42790  /dev/chunksdatadbschunk
.
.
10aeee038	 17	3    0	 	223414 	 1324123  /dev/chunks/indexdbschunk
.
.
Chunks
address          chunk/dbs  offset     page Rd  page Wr  pathname
10aedf028        12     2    0         157103    50320  /dev/chunksdatadbschunk
.
.
.
10aeee038	 17	3    0	 	343413 	 1924131  /dev/chunks/indexdbschunk
.
.
address          chunk/dbs  offset     page Rd  page Wr  pathname
10aedf028        12     2    0         157195     51020  /dev/chunksdatadbschunk
.
.
.
10aeee038	 17	3    0	 	386616 	2242207  /dev/chunks/indexdbschunk

So, in looking at the output, you see that even though the datadbs dbspace is not filling very fast, the idxdbs is extremely busy.

Why would the idxdbs be busy? Well, a simple dbschema could be run at that point that would verify if the user has indexes on that table. A simple solution then is to disable indexes and re-enable them after the mass update.


The sysmaster database

The sysmaster database, sometimes referred to as the system monitoring interface (SMI), is a special database designed with both real tables and pseudo-tables, and can be used to gather information about the state of many components of your IDS system. You can query these tables to identify processing bottlenecks, determine resource usage, track session or database server activity, and so on.

The database server creates the sysmaster database when it initializes disk space. The database server creates the database with unbuffered logging. You cannot drop the database or any of the tables in it through the normal drop database commands. To rebuild the sysmaster database, you should contact technical support to lead you through the $INFORMiXDIR/etc/buildsmi utility.

Let's start by looking at the syslocks table in the sysmaster database and see how it can help troubleshoot locking problems.

Example: Syslocks

The syslocks table tracks locks and is analogous to the output from onstat -k.

Listing 26 is from a sample line of onstat -k output:

Listing 26. Output from onstat -k
>
Locks
address   wtlist   owner      lklist     type     tblsnum rowid   key#/bsiz
10a166f68 0        10afa7008    10a166ce8  HDR+X   10015f   100        0

There is a fair amount of data here, but it involves some work to get meaningful information.

Let's look at the same locks by executing the command in Listing 27 in sysmaster. (Note that the "where" clause is omitted.)

Listing 27. Querying the syslocks table in sysmaster
Select * from syslocks
dbsname  stores_demo
tabname  customer
rowidlk  256
keynum   0
type     X
owner    28
waiter

The first thing to notice is that instead of a cryptic partnum, there is an actual database name and a table name. This is more meaningful for us. The other thing to notice is that instead of the address for the owner given in the onstat -k output, we are actually given a session id. So, you can run an onstat-g ses, -g sql, -g stm to find out what the user is doing that has locked that resource.

One thing to note is that the results from querying the sysmaster database is usually given in decimal format, whereas onstat output may be given in hexadecimal format. This can cause some confusion if you are trying to track down a problem using both modalities.

There are many pseudo-tables that are usually intuitively named to correspond with the resource they are tracking.

Table 8. Other pseudo-tables
TableCorresponding onstat
sysconfigonstat -c
syschunksonstat -d (chunks part)
sysdbspacesonstat -d (dbspaces part)
syslogsonstat -l (logical logs part)
sysprofile onstat -p
sysptntab onstat -t
sysusers onstat -u

The sysadmin database

The scheduler is defined and driven by tasks, and the sysadmin database contains the six tables that contain and organize scheduler task information. By default, only user informix is granted access to the sysadmin database; other users may be granted access to sysadmin. For detailed information about the scheduler, see the IBM Informix Administrator's Guide. (Note: The scheduler is also covered in part 2 of this tutorial series.)

The sysadmin database, which contains tables is a logged database. You use the task properties (not configuration parameters) to define the information that the scheduler collects and the statements the scheduler executes.

The sysadmin database also contains:

  • The built-in task() function
  • The built-in admin() function
  • The command_history table, which contains information about the commands that the Administration API ran

IMPORTANT:
Do not drop or attempt to alter the sysadmin database; several other database server components use it.

PH_TASK

The PH_TASK table contains information about how and when a task will be updated.

Table 9. PH_TASK table description
ColumnDescription
tk_idSequential task ID
tk_nameTask name. A unique index on this column ensures that no two names are the same.
tk_descriptionDescription about this task
tk_typeType of task:
  • TASK: Executes a task that does not collect data
  • SENSOR: A task that collects data
  • STARTUP SENSOR: Runs only when the server starts
  • STARTUP MONITOR: Runs only when the server starts
tk_sequenceCurrent data collection number. System updated; do not modify.
tk_ownerOwner's thread ID. System updated; do not modify.
tk_result_tableResult table name. Note: The tk_result_table column is used only by sensors and the content matches the table created in tk_create. When the tk_delete interval is exceeded, data is deleted from tk_result_table.
tk_createThe CREATE TABLE statement to execute. Note: The tk_create column is used only by sensors and, as necessary, is created to contain any data a sensor might store.
tk_executeThe SQL object to execute
tk_deleteDeletes data older than this interval
tk_start_timeStarting time of this task
tk_stop_timeTime of day this task should stop running
tk_frequencyHow often this task runs
tk_attributesFlags. System updated; do not modify.
tk_groupFlags> Group name references ph_group(group_name)
tk_exec_num Number of times to execute this task
tk_exec_time Total time spent executing this task. System updated; do not modify.
tk_enabled Specifies whether or not the task is enabled. If the value of tk_enabled equals FALSE, the task is not scheduled for execution. Number of times to execute this task.
tk_priority Job priority, on a scale of 0-5. If there are several jobs to execute simultaneously, the job with the highest priority executes first. The default is zero.

PH_RUN

The PH_RUN table contains information about how and when each scheduler task ran

Table 10. PH-RUN column descriptions
ColumnDescription
run_idSequential ID generated during execution
run_task_idID of the scheduler task executed out of the PH_TASK table
run_task_seqData collector sequence number
run_retcode Return code or SQL code from the UDR or SQL statement
run_time Time this scheduler task was executed
run_duration Time it took to execute this job (in seconds)
run_ztimeTime when onstat -z was last run
run_btimeTime when server was started
run_mtimeTime the task was executed
Table 11. A sample row
ColumnValue
run_id8
run_task_id1
run_task_seq1
run_retcode0
run_time 2007-04-09 11:46:46
run_duration0.004943
run_ztime1176137133
run_btime1176137133
run_mtime1176137206

The time this was executed was April 9 at 11:46. It is run once a day, and there is a row for each and every run. The return code 0 shows that it was successful.

PH_GROUP

The PH_GROUP table contains names about the scheduling group names.

Table 12. PH_GROUP table description
ColumnDescription
grouop_idGroup ID
group_nameUnique name of the group
group_descriptionDescription of the group

There are several different groups: MISC, DISK, NETWORK, MEMORY, CPU, TABLES, INDEXES, SERVER, USER, BACKUP, and PERFORMANCE.

PH_ALERT

The PH_ALERT table contains information about errors, warnings, or informational messages, for the scheduler.

Table 13. PH_ALERT table description
ColumnDescription
IDAlert ID
alert_run_idInvocation of a scheduler task that created the alert
alert_task_seqIdentifies which invocation of the scheduler task created the alert
alert_typeInformational, warning, or error
alert_colorGreen, yellow, or red
alert_timeTime the alert was generated
alert_stateIndicates what state the object is in currently:
  • NEW: The alert was newly added, and no other action has occurred on this alert
  • IGNORED: The alert was acknowledged by the DBA, and no action was taken
  • ACKNOWLEDGED: The alert has been acknowledged by the DBA.
  • ADDRESSED: The alert has been addressed by the DBA.
alert_state_changedThe last time the state was changed
alert_object_typeThe type of object:
  • SERVER
  • DATABASE
  • TABLE
  • INDEX
  • DBSPACE
  • CHUNK
  • USER
  • SQL_STATEMENT
  • MISC
alert_object_nameThe name of the object
alert_messageMessage
alert_actionCorrective action. This is an SQL script that can be executed by the user or tool, or it will be NULL if no action is available. This script must comply with all multi-statement prepare rules.
alert_action_dbsName of the database to use when executing the alert_action

PH_THRESHOLD

The PH_THRESHOLD table holds information about the thresholds before tasks are called by the scheduler.

Table 14. PH_THRESHOLD table description
ColumnDescription
idAlert ID
task_nameScheduler task name associated with the threshold
nameName of the threshold
ValueValue of the threshold
Value_typeThe data type of the value column:
  • STRINGZ
  • NUMERIC
  • NUMERIC,MAX,MIN

Concurrency

When using a multiple thread, or multiple process architecture, concurrency is important. Accessing the data in a single-threaded manner causes bottlenecks, as processes or threads have to wait for resources. This can slow processing dramatically. It is more efficient to have multiple applications connecting in parallel to the database.

Using WITH CONCURRENT TRANSACTION

The WITH CONCURRENT TRANSACTION clause allows you to switch to a different connection while a transaction is still active in the current connection. To switch to another database while a transaction still active you must have the WITH CONCURRENT TRANSACTION clause. Without this clause, you cannot switch to a different connection if a transaction is active; the CONNECT or SET CONNECTION statement will fail and return an error. The current transaction continues to be active in the active connection.

The WITH CONCURRENT TRANSACTION clause supports the concept of multiple concurrent transactions. This way, each connection can have its own transaction. The COMMIT WORK and ROLLBACK WORK statements affect only the current connection.

Global transactions, in which one transaction spans multiple databases over multiple connections, is not supported by the WITH CONCURRENT TRANSACTION clause. The COMMIT WORK and ROLLBACK WORK statements do not act on databases across multiple connections.

Concurrency and locks

There is always the possibility that, while one client is modifying data, another client will read or try to modify the same data. Two or more clients trying to access the data are said to be accessing it concurrently.

A multiuser database system requires a high level of concurrency. Concurrency can lead to a variety of problems if there is not some sort of methodology to control access to that data. A client could read data that has been modified or possibly deleted, or an update could be lost in the shuffle.

The database server imposes a system of locks to guard against these eventualities. A lock is a claim, or reservation, that a client can place on data. As long as the data is locked, no other client can modify it. Depending on the SET LOCK MODE setting, if another client requests the data, the database server either makes the other client wait or tells the client the resource is not available, generating an error.

To control the effect that locks have on your data access, use a combination of SQL statements: SET LOCK MODE, and either SET ISOLATION or SET TRANSACTION.

Committed read with last committed isolation level

If the isolation level is COMMITTED READ (and SET LOCK MODE is not set to WAIT), locks which are held by other sessions can cause SQL operations to fail if the current session cannot acquire a lock. Another situation that even SET LOCK MODE to WAIT will not help is a deadlock. (A deadlock occurs when two users are holding locks that the other wants) The LAST COMMITTED keyword option to the SET ISOLATION COMMITTED READ statement of SQL reduces the risk of locking conflicts. This syntax instructs the server to return the most recently committed version of the rows, even if another concurrent session holds an exclusive lock. You can use the LAST COMMITTED keyword option for both B-tree and functional indexes, logged tables, and tables that do not have page-level locking or exclusive locks.

USELASTCOMMITTED ONCONFIG PARAMETER

USELASTCOMMITTED specifies the isolation level for which the LAST COMMITTED feature of the COMMITTED READ isolation level is implicitly in effect.

Range of values:

  • None - No isolation level identified
  • 'Committed Read' - All transactions from a committed read isolation level
  • 'Dirty Read' - All transactions from a dirty read isolation level
  • All - Both committed read and dirty read isolation levels

As an ONCONFIG parameter, changing its value will only take effect in the effect of reinitializing shared memory.

Using onmode to affect isolation level

When the database server is in online mode, you can use the onmode -wm and onmode -wf options to change the values of the USELASTCOMMITTED configuration parameter.

To specify a new value for either configuration parameter for the current session, use this syntax:

onmode -wm USELASTCOMMITTED=value

To change the value of either configuration parameter in the ONCONFIG file, use this syntax:

onmode -wf USELASTCOMMITTED=value

SET ENVIRONMENT

The SET ENVIRONMENT SQL statement can specify options at runtime that affect subsequent queries submitted within the same routine. This is an extension to the ANSI/ISO standard for SQL.

The settings for USELASTCOMMITTED correspond to the onconfig parameters:

  • None - No isolation level identified
  • 'Committed Read' - All transactions from a committed read isolation level
  • 'Dirty Read' - All transactions from a dirty read isolation level
  • All -- Both committed read and dirty read isolation levels

Conclusion

This has been a very brief overview of how to approach troubleshooting in IDS.

The onstat utility has been covered in some detail. onstat options to look at memory usage, session information, disk usage, and locks have been demonstrated.

The sysmaster database has been introduced. The syslocks table has been examined in detail, as well as introducing such tables as sysconfig, syschunks, syslogs, sysprofile, sysdbspaces, sysptntab, and sysusers.

The sysadmin database was introduced also. This database is tied to the scheduler, which is covered in part 2 of the series.

Lastly, this tutorial included some mention of the functionality of lock concurrency and how to adjust it. This would be utilized in an environment with multiple databases (and database servers) involved in a production architecture

Part 4 of the tutorial series teaches you about performance tuning.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=229141
ArticleTitle=System Administration Certification exam 918 for IBM Informix Dynamic Server 11 prep, Part 3: Troubleshooting
publish-date=05242007