PDF

System Administration Certification exam 918
for IBM Informix Dynamic Server 11 prep, Part 3:
Troubleshooting
Joseph W. Baric, Jr. ([email protected])
Advanced Support Engineer
IBM
24 May 2007
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.
View more content in this series
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
© Copyright IBM Corporation 2007
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Trademarks
Page 1 of 32
developerWorks®
ibm.com/developerWorks/
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
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 2 of 32
ibm.com/developerWorks/
developerWorks®
• 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
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.
onstat
Memory usage
There are several commands that track memory usage:
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 3 of 32
developerWorks®
ibm.com/developerWorks/
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
tpcpool
V
seqpool
V
pnlpool
V
sbtlist
V
dstpool
V
sqcrypto
V
ampool
V
Blkpool Summary:
name
class
mt
V
global
V
10ad28040
10b034040
10b06a040
10b037040
10ae10040
10b033040
10b21b040
10b061040
8192
40960
4096
77824
20480
8192
4096
8192
2488
4776
768
4344
7232
3320
504
3088
addr
10ad2b450
10ad26290
size
1527808
0
#blks
21
0
5
32
2
69
4
2
2
22
3
3
1
5
3
2
1
1
Table 1 describes the information provided in the pool summary.
Table 1. Pool summary description
Name
Descriptoin
Name
Pool name
Class
Shared memory class (R=Resident, V=Virtual)
Addr
Pool header memory address
size
Total 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
Name
Descriptoin
Name
Pool name
Class
Shared memory class (R=Resident, V=Virtual)
Addr
Pool header memory address
size
Total size of the pool in bytes
#blksize
Number of blocks in the pool
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.
onstat -g ses
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 4 of 32
ibm.com/developerWorks/
developerWorks®
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
id
user
35
informix
18
informix
17
informix
16
informix
5
informix
3
informix
2
informix
35 informix -
tty
-
pid
0
0
0
0
0
0
0
0
-
#RSAM
threads
0
1
1
1
0
0
0
81920
hostname
0
total
memory
12288
425984
434176
282624
12288
16384
12288
76360
used
memory
11592
338128
337408
227560
11592
13176
11592
off
dynamic
explain
off
off
off
off
off
off
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
id
35
user
tty
informix 53
tid
59
name
sqlexec
pid
15046
rstcb
10afaa7d0
Memory pools
count 2
name
class addr
35
V
10bf99040
35*O0
V
10c072040
name
overhead
opentable
log
keys
gentcb
sqscb
rdahead
osenv
sqtcb
sapi
sqscb info
scb
10bde6400
free
0
0
0
0
0
0
0
0
0
0
#RSAM
hostname threads
ryleh
1
flags
Y--P---
dynamic
explain
off
#allocfrag #freefrag
110
5
1
1
name
scb
filetable
temprec
ralloc
ostcb
sql
hashfiletab
buft_buffer
fragman
optofc
0
used
memory
76360
status
cond wait(sm_read)
totalsize freesize
77824
4720
4096
840
used
6512
2568
12096
800
1640
18880
160
2880
3216
64
sqscb
10bf63028
curstk
7791
total
memory
81920
free
0
0
0
0
0
0
0
0
0
used
144
496
1696
18992
2864
7
552
2168
488
pdqpriority sqlstats optcompind
0
0
2
directives
1
Sess
Id
SQL
Stmt type
Current
Database
Iso Lock
Lvl Mode
SQL
ERR
ISAM F.E.
ERR Vers Explain
35
SELECT
sysmaster
CR
0
0
Not Wait
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
9.24 Off
Page 5 of 32
developerWorks®
ibm.com/developerWorks/
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 sharedmemory 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
Output
Description
addr
Memory address of the pool fragment
size
Size, in bytes, of the pool fragment
memid
Memory 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.
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 6 of 32
ibm.com/developerWorks/
developerWorks®
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
Output
Description
addr
Memory address of the pool fragment
size
Size, 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
id
35
18
17
.
.
.
2
2301
8220
1704
430
1991
user
informix
informix
informix
tty
-
pid
0
0
0
hostname
-
#RSAM
threads
0
1
1
informix
bad_app
bad_app
bad_app
bad_app
bad_app
-
0
0
0
0
0
0
-
0
1
1
1
1
1
total
memory
12288
425984
434176
12288
3203072
3194880
3203072
19169280
3203072
used
memory
11592
338128
337408
11592
16384
16384
16384
16384
16384
dynamic
explain
off
off
off
off
off
off
off
off
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.
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 7 of 32
developerWorks®
ibm.com/developerWorks/
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
6/
6
1
8
Client Type
sqlexec
srvinfx
onspace
onlog
onparam
oncheck
onload
onunload
onmonitor
dr_accept
cdraccept
ontape
srvstat
asfecho
listener
crsamexec
onutil
safe
drdaexec
smx
Totals
Calls
yes
yes
yes
yes
yes
yes
yes
yes
yes
yes
no
yes
yes
yes
yes
yes
yes
yes
no
yes
write
8
Accepted
1
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1
q-free q-limits
0/
0 135/ 10
Rejected
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
q-exceed alloc/max
0/
0
1/
1
Read
7
0
0
0
0
0
0
0
0
0
0
0
0
0
1
0
0
0
0
0
8
Write
8
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
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
6/
6
1
8
write
8
Network mailbox information:
box
netscb thread name
5
10b239928 tlitcppoll
6
10b250928 tlitcplst
q-free q-limits
0/
0 135/ 10
max received
10
4
10
0
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
q-exceed alloc/max
0/
0
1/
1
in box
0
0
max in box full signal
2
0 yes
0
0 no
Page 8 of 32
ibm.com/developerWorks/
developerWorks®
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
6/
6
1
8
write
8
q-free q-limits
0/
0 135/ 10
q-exceed alloc/max
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
6/
6
1
8
write
8
q-free q-limits
0/
0 135/ 10
Individual thread network information (basic):
netscb type
thread name
sid
fd poll
reads
10b4bf368 tlitcp sqlexec
4
2
5
10c10ccd0 tlitcp unknown
17
0
0
10b270790 tlitcp unknown
16
0
0
10b3abd18 tlitcp unknown
15
0
0
10b250928 tlitcp tlitcplst
3
1
5
10b239928 tlitcp tlitcppoll
2
0
5
q-exceed alloc/max
0/
0
1/
1
writes q-nrm q-pvt q-exp
8 0/ 1 1/1 0/ 0
0 0/ 0 0/ 0 0/ 0
0 0/ 0 0/0 0/ 0
0 0/ 0 0/ 0 0/ 0
0 0/ 0 0/0 0/ 0
0 0/ 0 0/0/ 0
8
0
0
0
1
7
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.
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 9 of 32
developerWorks®
ibm.com/developerWorks/
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
6/
7
15
936
write
714
q-free
1/
1
q-limits
135/ 10
Individual thread network information (basic):
netscb type
thread name
sid
fd poll
vt q-exp
10b434b88 tlitcp unknown
19
0
0
0 0/ 0
10b294848 tlitcp unknown
18
0
0
0 0/ 0
10b270ae0 tlitcp unknown
17
0
0
0 0/ 0
10b274928 tlitcp tlitcplst
4
2
5
0 0/ 0
10b250928 tlitcp tlitcplst
3
1
5
0 0/ 0
10b239928 tlitcp tlitcppoll
2
0
5
0 0/ 0
10b239928 tlitcp tlitcppoll
2
0
5
0 0/ 0
Individual thread network information (times):
netscb thread name
sid
open
10b434b88
19 09:54:00
10b294848
18 09:54:00
10b270ae0
17 09:54:00
q-exceed alloc/max
0/
0
1/
1
reads
read
writes q-nrm q-p
0
0
0/ 0
0/
0
0
0/ 0
0/
0
0
0/ 0
0/
0
0
0/ 0
0/
15
0
0/ 0
0/
936
0
0/ 0
0/
936
0
0/ 0
0/
write address
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
gnal
5
10b239928 tlitcppoll
gnal
5
10b239928 tlitcppoll
yes
6
10b250928 tlitcplst
no
7
10b274928 tlitcplst
no
Client Type
sqlexec
srvinfx
onspace
onlog
onparam
oncheck
onload
onunload
onmonitor
dr_accept
cdraccept
Calls
yes
yes
yes
yes
yes
yes
yes
yes
yes
yes
no
Accepted
15
0
0
0
0
0
0
0
0
0
0
max received
in box
max in box full si
10
49
0
4
0
10
49
0
4
0
10
0
0
0
0
10
0
0
0
0
Rejected
0
0
0
0
0
0
0
0
0
0
0
Read
921
0
0
0
0
0
0
0
0
0
0
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Write
714
0
0
0
0
0
0
0
0
0
0
Page 10 of 32
ibm.com/developerWorks/
ontape
srvstat
asfecho
listener
onunload
onmonitor
dr_accept
cdraccept
ontape
srvstat
asfecho
listener
crsamexec
onutil
safe
drdaexec
smx
Totals
yes
yes
yes
yes
yes
yes
yes
no
yes
yes
yes
yes
yes
yes
yes
no
yes
developerWorks®
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
15
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
15
0
0
0
0
0
0
0
15
0
0
0
0
0
936
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
714
No MaxConnect instances connected
IO statistics for each MaxConnect instance:
IMCid
header
data
partial
blocked
reads
reads
reads
reads
-
data
writes
-
partial
writes
-
blocked
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.
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 11 of 32
developerWorks®
ibm.com/developerWorks/
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
id
18
17
16
15
4
3
2
user
informix
informix
informix
informix
informix
informix
informix
tty
53
-
pid
0
0
0
0
25457
0
0
#RSAM
threads
0
1
1
1
1
0
0
hostname
ryleh
-
total
memory
12288
303104
307200
278528
98304
16384
12288
used
memory
11592
260672
265480
227640
91928
13176
11592
dynamic
explain
off
off
off
off
off
off
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
id
35
user
tty
informix 53
tid
59
name
sqlexec
pid
15046
rstcb
10afaa7d0
Memory pools
count 2
name
class addr
35
V
10bf99040
35*O0
V
10c072040
name
overhead
opentable
log
keys
gentcb
sqscb
rdahead
osenv
sqtcb
sapi
sqscb info
scb
10bde6400
Sess
Id
SQL
Stmt type
free
0
0
0
0
0
0
0
0
0
0
#RSAM
hostname threads
ryleh
1
flags
Y--P---
Current
Database
name
scb
filetable
temprec
ralloc
ostcb
sql
hashfiletab
buft_buffer
fragman
optofc
0
used
memory
76360
#allocfrag #freefrag
110
5
1
1
free
0
0
0
0
0
0
0
0
0
used
144
496
1696
18992
2864
7
552
2168
488
pdqpriority sqlstats optcompind
0
0
2
Iso Lock
Lvl Mode
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
dynamic
explain
off
status
cond wait(sm_read)
totalsize freesize
77824
4720
4096
840
used
6512
2568
12096
800
1640
18880
160
2880
3216
64
sqscb
10bf63028
curstk
7791
total
memory
81920
SQL
ERR
directives
1
ISAM F.E.
ERR Vers Explain
Page 12 of 32
ibm.com/developerWorks/
35
SELECT
developerWorks®
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
id
2257
user
tty
bad_user
tid
2070
name
sqlexec
pid
15098
rstcb
10afaa7d0
Memory pools
count 2
name
class addr
35
V
10bf99040
35*O0
V
10c072040
name
overhead
opentable
log
keys
gentcb
sqscb
rdahead
osenv
sqtcb
sapi
free
0
0
0
0
0
0
0
0
0
0
used
6512
2568
12096
800
1640
18880
160
2880
3216
64
#RSAM
hostname threads
ryleh
1
flags
Y--P---
curstk
7791
total
memory
122288
dynamic
explain
off
status
cond wait(sm_read)
totalsize freesize
77824
4720
4096
840
name
scb
filetable
temprec
ralloc
ostcb
sql
hashfiletab
buft_buffer
fragman
used
memory
121592
#allocfrag #freefrag
110
5
1
1
free
0
0
0
0
0
0
0
0
0
used
144
496
1696
18992
2864
7
552
2168
488
sqscb info
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 13 of 32
developerWorks®
scb
10bde6400
ibm.com/developerWorks/
sqscb
10bf63028
optofc
0
pdqpriority sqlstats optcompind
0
0
2
directives
1
Sess
Id
SQL
Stmt type
Current
Database
Iso Lock
Lvl Mode
SQL
ERR
ISAM F.E.
ERR Vers Explain
35
INSERT
sysmaster
CR
0
0
Not Wait
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
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 14 of 32
ibm.com/developerWorks/
developerWorks®
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
933
961
101742
99.08
173
441
6221
97.22
isamtot
19993
open
1533
start
2346
read
6077
gp_read
0
gp_write
0
ovlock
0
ovuserthread ovbuff
0
0
write
685
gp_rewrt
0
gp_del
0
usercpu
3.55
bufwaits lokwaits lockreqs deadlks
240
0
9130
0
ixda-RA
38
idx-RA
5
da-RA
162
rewrite
37
delete
346
gp_alloc
0
syscpu
0.52
dltouts
0
gp_free
0
commit
807 0
numckpts
2
rollbk
gp_curs
0
flushes
2
ckpwaits compress seq scans
0
385
137 0
RA-pgsused lchwaits
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
0
LRU Writes
0
Chunk Writes
98
address
flusher state
10afa5820
0
I
states: Exit Idle Chunk Lru
data
0
# LRU
0
Chunk
1
Wakeups Idle Time
1239
1237.670
If Fg writes is increasing, then tuning is warranted.
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.
onstat -R
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
#
0
1
buffer LRU queue pairs
f/m
pair total
% of
F
624
100.0%
m
0.0%
length
624
0
priority levels
LOW
HIGH
624
0
0
0
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 15 of 32
developerWorks®
ibm.com/developerWorks/
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
Column
Description
Buffer pool page size
The 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/m
Identifies 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
length
Tracks the length of the queue measured in
buffers
% of
Shows 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 total
Provides the total number of buffers in this LRU
queue
priority levels
Displays 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:
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 16 of 32
ibm.com/developerWorks/
developerWorks®
Table 6. Interpreting the output
Column
Description
dirty
The total number of buffers that have been
modified in all LRU queues
queued
The total number of buffers in LRU queues
total
The total number of buffers
hash buckets
The number of hash buckets
buffer size
The size of each buffer
start clean
The value of LRU_MAX_DIRTY
stop at
The value of LRU_MIN_DIRTY
priority downgrades
The number of LRU queues downgraded to a
lower priority
priority upgrades
The 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
19993
open
1533
start
2346
read
6077
gp_read
0
gp_write
0
gp_rewrt
0
ovlock
0
ovuserthread ovbuff
0
0
write
685
gp_del
0
bufwaits lokwaits lockreqs deadlks
2400
0
9130
0
usercpu
3.53
dltouts
0
rewrite
37
gp_alloc
0
syscpu
0.52
delete
346
gp_free
0
numckpts
2
commit
807 0
rollbk
gp_curs
0
flushes
2
ckpwaits compress seq scans
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
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 17 of 32
developerWorks®
isamtot
19993
open
1533
ibm.com/developerWorks/
start
2346
read
6077
gp_read
0
gp_write
0
gp_rewrt
0
ovlock
0
ovuserthread ovbuff
0
0
write
685
gp_del
0
usercpu
3.55
bufwaits lokwaits lockreqs deadlks
2706
0
9130
0
dltouts
0
rewrite
37
gp_alloc
0
syscpu
0.54
delete
346
gp_free
0
numckpts
2
commit
807 0
rollbk
gp_curs
0
flushes
2
ckpwaits compress seq scans
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
19993
open
1533
start
2346
read
6077
gp_read
0
gp_write
0
gp_rewrt
0
ovlock
0
ovuserthread ovbuff
0
0
write
685
gp_del
0
usercpu
3.57
bufwaits lokwaits lockreqs deadlks
3002
0
9130
0
ixda-RA
38
idx-RA
5
da-RA
162
dltouts
0
rewrite
37
gp_alloc
0
syscpu
0.54
delete
346
gp_free
0
numckpts
2
commit
807 0
rollbk
gp_curs
0
flushes
2
ckpwaits compress seq scans
0
385
137 0
RA-pgsused lchwaits
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.
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 18 of 32
ibm.com/developerWorks/
developerWorks®
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
10a21ede0
0
pgflgs scount
waiter
80e
90
10afa5028
10afa5820
10afa6028
.
.
.
10afaa028
flags pagenum
103 11:5893
memaddr
10a627800
nslots
9
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
10aedee78
1
0x40001
1 active, 2047 maximum
fchunk
1
Chunks
address
chunk/dbs offset size free
10aedf028 1
1
0
15000 1238
nchunks
1
bpages
PO-B
pgsize
2048
flags
flags
N B
owner
name
informix rootdbs
pathname
/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.
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 19 of 32
developerWorks®
ibm.com/developerWorks/
Table 7. Highlights of the chunk output
Column
Description
Size
The size of the chunk in online pages
Free
The 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
10aedee78
1
0x40001
1 active, 2047 maximum
Chunks
address
10aedf028
chunk/dbs offset
1
1
0
chunks/rootchunk
1 active, 32766 maximum
fchunk
1
page Rd
964
nchunks
1
pgsize
2048
page Wr
492
flags
N B
owner
informix
name
rootdbs
pathname
/testing/prod/1110FC1B5/SERVER/
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.
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 20 of 32
ibm.com/developerWorks/
developerWorks®
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
3
rootchunk
op type
count
seeks
0
reads
904
writes
454
kaio_reads 0
kaio_writes 0
bytes read
page reads
2697216
1317
avg. time
N/A
0.0010
0.0112
N/A
N/A
bytes write
2418688
page writes io/s
1181
224.8
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
10aeded78
1
0x40001
.
.
10aedee88
2
0x40001
.
.
10aedee98
3
0x40001
1 active, 2047 maximum
fchunk
1
nchunks
1
pgsize
2048
flags
N B
owner
informix
name
rootdbs
12
1
2048
N B
informix
datadbs
17
1
2048
N B
informix
idxdbs
Chunks
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 21 of 32
developerWorks®
ibm.com/developerWorks/
address
chunk/dbs offset
10aedf028
12
2
0
.
.
.
10aeee038
17
3
0
1 active, 32766 maximum
page Rd
157000
223414
page Wr
42790
1324123
pathname
/dev/chunks/datachunk
/dev/chunks/indexdbschunk
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
10aedf028
.
.
10aeee038
.
.
Chunks
address
10aedf028
.
.
.
10aeee038
.
.
address
10aedf028
.
.
.
10aeee038
chunk/dbs
12
2
17 3
0
223414
chunk/dbs
12
2
17 3
0
0
1324123
page Rd
157000
386616
page Rd
157103
1924131
offset
0
2242207
page Wr
42790
pathname
/dev/chunksdatadbschunk
/dev/chunks/indexdbschunk
offset
0
343413
chunk/dbs
12
2
17 3
offset
0
page Wr pathname
50320 /dev/chunksdatadbschunk
/dev/chunks/indexdbschunk
page Rd
157195
page Wr
51020
pathname
/dev/chunksdatadbschunk
/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 reenable 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.
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 22 of 32
ibm.com/developerWorks/
developerWorks®
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
10a166f68 0
owner
10afa7008
lklist
type
10a166ce8 HDR+X
tblsnum rowid
10015f
100
key#/bsiz
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 *
dbsname
tabname
rowidlk
keynum
type
owner
waiter
from syslocks
stores_demo
customer
256
0
X
28
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
Table
Corresponding onstat
sysconfig
onstat -c
syschunks
onstat -d (chunks part)
sysdbspaces
onstat -d (dbspaces part)
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 23 of 32
developerWorks®
ibm.com/developerWorks/
syslogs
onstat -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
Column
Description
tk_id
Sequential task ID
tk_name
Task name. A unique index on this column
ensures that no two names are the same.
tk_description
Description about this task
tk_type
Type 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_sequence
Current data collection number. System updated;
do not modify.
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 24 of 32
ibm.com/developerWorks/
developerWorks®
tk_owner
Owner's thread ID. System updated; do not
modify.
tk_result_table
Result 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_create
The 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_execute
The SQL object to execute
tk_delete
Deletes data older than this interval
tk_start_time
Starting time of this task
tk_stop_time
Time of day this task should stop running
tk_frequency
How often this task runs
tk_attributes
Flags. System updated; do not modify.
tk_group
Flags> 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
Column
Description
run_id
Sequential ID generated during execution
run_task_id
ID of the scheduler task executed out of the
PH_TASK table
run_task_seq
Data 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_ztime
Time when onstat -z was last run
run_btime
Time when server was started
run_mtime
Time the task was executed
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 25 of 32
developerWorks®
ibm.com/developerWorks/
Table 11. A sample row
Column
Value
run_id
8
run_task_id
1
run_task_seq
1
run_retcode
0
run_time
2007-04-09 11:46:46
run_duration
0.004943
run_ztime
1176137133
run_btime
1176137133
run_mtime
1176137206
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
Column
Description
grouop_id
Group ID
group_name
Unique name of the group
group_description
Description 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
Column
Description
ID
Alert ID
alert_run_id
Invocation of a scheduler task that created the
alert
alert_task_seq
Identifies which invocation of the scheduler task
created the alert
alert_type
Informational, warning, or error
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 26 of 32
ibm.com/developerWorks/
developerWorks®
alert_color
Green, yellow, or red
alert_time
Time the alert was generated
alert_state
Indicates 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_changed
The last time the state was changed
alert_object_type
The type of object:
• SERVER
• DATABASE
• TABLE
• INDEX
• DBSPACE
• CHUNK
• USER
• SQL_STATEMENT
• MISC
alert_object_name
The name of the object
alert_message
Message
alert_action
Corrective 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_dbs
Name 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
Column
Description
id
Alert ID
task_name
Scheduler task name associated with the
threshold
name
Name of the threshold
Value
Value of the threshold
Value_type
The 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
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 27 of 32
developerWorks®
ibm.com/developerWorks/
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.
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 28 of 32
ibm.com/developerWorks/
developerWorks®
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
COMMITTED READ isolation level is implicitly in effect.
the LAST COMMITTED feature of the
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.
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 29 of 32
developerWorks®
ibm.com/developerWorks/
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.
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 30 of 32
ibm.com/developerWorks/
developerWorks®
Resources
Learn
• developerWorks Informix zone: Read articles and tutorials and connect to other resources to
expand your Informix skills.
• IBM Informix Dynamic Server Information Center: Learn more about Informix.
• IBM Informix Dynamic Server 11 BETA Information Center: Learn more about IDS 11.
• developerWorks IDS Experts blog: Find technical notes on Informix Dynamic Server by a
worldwide team of Development and Technical Support engineers.
• IBM Information Management certification page: Learn more about resources for IDS
certification.
• developerWorks Information Management zone: Learn more about Information Management.
Find technical documentation, how-to articles, education, downloads, product information,
and more.
• Stay current with developerWorks technical events and webcasts.
• Technology bookstore: Browse for books on these and other technical topics.
Get products and technologies
• Informix Dynamic Server Enterprise Edition V10.0: Download a free trial version.
• Informix Dynamic Server 11: Download the free trial version to work along with this tutorial.
• IBM product evaluation versions: Download and get your hands on application development
tools and middleware products from Information Management, Lotus®, Rational®, Tivoli®,
and WebSphere®.
Discuss
• Participate in the discussion forum for this content.
• Check out developerWorks blogs and get involved in the developerWorks community.
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 31 of 32
developerWorks®
ibm.com/developerWorks/
About the author
Joseph W. Baric, Jr.
Joe 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 [email protected].
© Copyright IBM Corporation 2007
(www.ibm.com/legal/copytrade.shtml)
Trademarks
(www.ibm.com/developerworks/ibm/trademarks/)
System Administration Certification exam 918 for IBM Informix
Dynamic Server 11 prep, Part 3: Troubleshooting
Page 32 of 32