pajama

Two new onstat commands introduced in 12.10.xC2 to view the dependencies between blocking and waiting threads. A running threads take ownership of various objects and resources; for example, buffers, locks, mutexes, decision support memory etc. Contention for these resources among hundreds or thousands of threads can result in chains of dependencies.

Now, you can use the 'onstat -g bth' command to display the dependencies between blocking and waiting threads. Next, use the 'onstat -g BTH' command to display session and stack information for the blocking threads.

Here is a scenario where these onstat commands can be helpful. It possible a thread that is blocked waiting to enter a critical section might own a row lock for which another thread is waiting. The second thread might be blocking a third thread that is waiting in the MGM query queue. Usually, the duration of such contention is short and user never notice any problem. However, if a thread is blocked long enough, you might need to identify the source of the contention. The 'onstat -g bth' command discovers the chains of dependency and displays blocker threads followed by waiting threads, in order.

The following is a sample output of the 'onstat -g bth' command where multiple threads are waiting on resources.

Highest level blocker(s)
 tid      name                 session
 48       sqlexec              26

Threads waiting on resources
 tid      name                 blocking resource              blocker
 49       sqlexec              MGM                            48
 13       readahead_0          Condition (ReadAhead)           -
 50       sqlexec              Lock (0x4411e578)              49
 51       sqlexec              Lock (0x4411e578)              49
 52       sqlexec              Lock (0x4411e578)              49
 53       sqlexec              Lock (0x4411e578)              49
 57       bf_priosweep()       Condition (bp_cond)             -
 58       scan_1.0             Condition (await_MC1)           -
 59       scan_1.0             Condition (await_MC1)           -

In the above example, four threads are waiting for a lock that is owned by thread 49. However, that's not the actual problem. The thread 49 is waiting for MGM resources that are owned by thread 48. So, originally problem started with the thread 48. Next, run the 'onstat -g BTH' command to see the session and stack information of thread 48. Following is the example for 'onstat -g BTH' output:

Stack for thread: 48 sqlexec
 base: 0x00000000461a3000
  len:   69632
   pc: 0x00000000017b32c3
  tos: 0x00000000461b2e30
state: ready
   vp: 1

0x00000000017b32c3 (oninit) yield_processor_svp
0x00000000017bca6c (oninit) mt_wait
0x00000000019d4e5c (oninit) net_buf_get
0x00000000019585bf (oninit) recvsocket
0x00000000019d1759 (oninit) tlRecv
0x00000000019ce62d (oninit) slSQIrecv
0x00000000019c43ed (oninit) pfRecv
0x00000000019b2580 (oninit) asfRecv
0x000000000193db2a (oninit) ASF_Call
0x0000000000c855dd (oninit) asf_recv
0x0000000000c8573c (oninit) _iread
0x0000000000c835cc (oninit) _igetint
0x0000000000c72a9e (oninit) sqmain
0x000000000194bb38 (oninit) listen_verify
0x000000000194ab8a (oninit) spawn_thread
0x0000000001817de3 (oninit) th_init_initgls
0x00000000017d3135 (oninit) startup



Highest level blocker(s)
 tid      name                 session
 48       sqlexec              26      

session          effective                  #RSAM    total   used   dynamic
id      user     user  tty   pid   hostname threads  memory  memory explain
26      informix -     45    31041 darwin   2        212992  186568 off

Program :
/usr/informix/bin/dbaccess

tid      name     rstcb            flags    curstk   status
48       sqlexec  448bc5e8         ---P---  4560     ready-
58       scan_1.0 448bb478         Y------  896      cond wait  await_MC1 -

Memory pools    count 2
name         class addr          totalsize  freesize   #allocfrag #freefrag
26           V     45fcc040      208896     25616      189        16        
26*O0        V     462ad040      4096       808        1          1        

name           free      used           name           free       used      
overhead       0         6576           mtmisc         0          72        
resident       0         72             scb            0          240      
opentable      0         7608           filetable      0          1376      
log            0         33072          temprec        0          17744    
blob           0         856            keys           0          176      
ralloc         0         55344          gentcb         0          2240      
ostcb          0         2992           sqscb          0          21280    
sql            0         11880          xchg_desc      0          1528      
xchg_port      0         1144           xchg_packet    0          440      
xchg_group     0         104            xchg_priv      0          336      
hashfiletab    0         1144           osenv          0          2520      
sqtcb          0         15872          fragman        0          1024      
shmblklist     0         416            sqlj           0          72        
rsam_seqscan   0         368            

sqscb info
scb              sqscb          optofc   pdqpriority optcompind  directives
4499c1c0         461c1028       0        100         2           1        

Sess       SQL          Current       Iso Lock       SQL  ISAM F.E.
Id         Stmt type    Database      Lvl Mode       ERR  ERR  Vers  Explain    
26         SELECT       stores        CR  Not Wait   0    0    9.24  Off        

Current statement name : unlcur

Current SQL statement (5) :
  select * from systables,syscolumns,sysfragments

Last parsed SQL statement :
  select * from systables,syscolumns,sysfragments

• Large buffers, which are used for large read and write I/O operations
• Sort-space pools
• Active thread-control blocks, stacks, and heaps
• User-session data
• Caches for SQL statements, data-dictionary information, and user-defined routines
• A global pool for network-interface message buffers and other information

INTRODUCTION

You can use a SQL statement to obtain information regarding each of the most recent UPDATE STATISTICS HIGH and UPDATE STATISTICS MEDIUM commands that ran against a table.

The type of information you can obtain from the most recent update statistics is the following:

• The date on which it ran
• The mode used (high or medium)
• The column(s) it ran against
• The resolution used
• The confidence level used

SELECT UNIQUE tabname,colname,constructed,mode, 
       resolution,confidence 
FROM  systable s t, syscolumns c, sysdistrib d 
WHERE d.tabid = c.tabi d  AND d.colno = c.colno A ND 
       d.tabid = t.tabid AND 
       t.tabname=' tablename ' 
ORDER BY 1,2;

tablename
The name of the table you want the distribution information for.

Note: The confidence level for distributions run in high mode is irrelevant so it should always be set to zero.

tabname      state 
colname      code 
constructed  07/08/2003 
mode         H 
resolution   0.5 
confidence   0.00 

tabname      state 
colname      sname 
constructed  07/08/2003 
mode         M 
resolution   2.500000000000 
confidence   0.94999999

No rows found is returned.

INTRODUCTION

This article shows a SQL statement which can be executed to retrieve when and for which columns the distributions were last built. However, if a column does not have a distribution it will not appear in the query output.

STEPS

select systables.tabname, syscolumns.colname, 
      sysdistrib.constructed,mode
from sysdistrib,systables,syscolumns
where systables.tabid > 99
and systables.tabid = syscolumns.tabid
and sysdistrib.tabid=systables.tabid
and sysdistrib.colno = syscolumns.colno
group by 1,2,3,4
order by tabname,colname;

Assuming that 

UPDATE STATISTICS HIGH for table customer (customer_num) 

 
tabname      customer 
colname      customer_num 
constructed  12/15/2006 
mode         H 

System generated names for primary and foreign key indexes and constraints.

There are times that you may want to specify the name of the index associated with a foreign key constraint or a primary key constraint rather than having one generated by the engine. You may, for instance, want to use the index name as part of an optimizer directive to be used on the same table in more than one instance.

To specify a name for the index associated with a primary key constraint or a foreign key constraint do the following:

1. Create the table without including the PRIMARY KEY or FOREIGN KEY clause. 

2. Create a unique index on the columns used by the primary key or foreign key you want to create. This index will be the one that will be used by your primary key or foreign key constraint. 


Example: 

If you are going to create a primary key on the columns col1 and col2 of a table named tab2 and you want the index to be named ixtab2 you would use this statement to create the index: 

CREATE UNIQUE INDEX ixtab2 on tab2(col1,col2);


3. Alter the table to add the primary key or foreign key constraint. The engine will detect that a unique index already exists on the columns and use that index automatically.


Example: 

Continuing the previous example, the following ALTER TABLE statement would create primary key constraint that uses the index ixtab2: 

ALTER TABLE tab2 ADD CONSTRAINT PRIMARY KEY(col1,col2);

Your INFORMIXDIR will have a subdirectory with the name "license" or "ids_license." Find the file in the license subdirectory that is appropriate for the language you use. Open the file and search for the program edition statement. For example, in the English language file, a search on "Edition" yields a line like the following:

Program Name: IBM Informix Ultimate Edition V11.70

In this case you have a fixpak of IBM Informix Server 11.70 installed and it is Ultimate Edition.

INTRODUCTION

IBM® Informix® Dynamic Server (IDS) version 9.x introduced the ability to collect information for all active SQL sessions by using the command onstat -g sql 0. This functionality can be replicated for IDS version 7.31 databases by using the following awk script.

STEPS

Create an awk script that contains the following text:

BEGIN {system(":> onstat_g_sql_0")}
{
if ($NF > 0) {
my_string= onstat -g sql " $1 "  >> onstat_g_sql_0; echo \"----------------\" >> onstat_g_sql_0"
system(sprintf(my_string))
}
}
END {}

IBM Informix Dynamic Server Version 7.31.UD8     -- on-Line -- Up 00:10:54 -- 8912 Kbytes
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers
13    SELECT         informix_log       CR  Not Wait   0    0    7.31
11    -              informix_log       CR  Not Wait   0    0    7.31

Sample output from awk script

IBM Informix Dynamic Server Version 7.31.UD8     -- on-Line -- Up 00:10:20 -- 8912 Kbytes
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers
13    SELECT         informix_log       CR  Not Wait   0    0    7.31
Current statement name : slctcur
Current SQL statement :
  select * from test2
Last parsed SQL statement :
  select * from test2
----------------
IBM Informix Dynamic Server Version 7.31.UD8     -- on-Line -- Up 00:10:20 -- 8912 Kbytes
Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers
11    -              informix_log       CR  Not Wait   0    0    7.31
Last parsed SQL statement :
  UPDATE   test2 set str1="bbb" where key=0
----------------