pajama

INTRODUCTION

Most customers using Informix products are used to having an option which gives them the version of the product being used. This is usually done at the command line using the "-V" option or using a special utility.

For example: esql -V would give the version of esql or the utility ifx_getversion can be used to display the version of the ClientSDK.

It is also possible to obtain the version of the JDBC driver contained in the ifxjdbc.jar file from the command line.


BEFORE YOU BEGIN

Make sure you have set the CLASSPATH variable correctly to include the location of ifxjdbc.jar ( or ifxjdbc-g.jar ) and that your PATH has the JDK in it.


STEPS

Execute the following

java com.informix.jdbc.Version

% java com.informix.jdbc.Version 
IBM Informix JDBC Driver Version 2.21.JC5

To obtain a list of the tables in a particular chunk, below sql can be used:
select dbinfo( "DBSPACE" , pe_partnum ) dbspace,

dbsname[1,12],

tabname,

pe_chunk,

pe_offset start,

pe_size size

from sysptnext, outer systabnames

where pe_partnum = partnum

and pe_chunk = <chunk number>

order by start, dbsname, tabname;

Replace chunk number with the number of the chunk for which you want to list the table names.

Lock requests showing up on a primary server that do not map to sessions on the primary.

To associate an open transaction on the primary with a session on the SDS node, we first start with 'onstat -k' from the primary, we see some locks that are causing problems:

  Locks
address    wtlist  owner      lklist    type     tblsnum  rowid    key#/bsiz
443139e8   0       4b7d0bf0   0         HDR+S    100002   205         0    
44313b68   0       4b7d99c8   4451cae8  HDR+X    1001c8   100         0
4444c168   0       4b7d24f8   0             S    100002   204         0     
4444c7e8   0       4b7d99c8   4451c868  HDR+S    100002   206         0     
444b43e8   0       4b7d2d50   0         HDR+S    100002   204         0     
4451c668   0       4b7d35a8   0             S    100002   204         0     
4451c868   0       4b7d99c8   0         HDR+X    649      3           0     
4451cae8   0       4b7d99c8   4444c7e8  HDR+IX   1001c8   0           0    
 8 active, 20000 total, 16384 hash buckets, 0 lock table overflows

The owner '4b7d99c8' has been identified as a lock on a table we need access to, or maybe while running an onstat –x, you see a open transaction that looks older than it should:

$ onstat –x
Transactions
                                                                     est.   
address  flags userthread locks begin_logpos   current logpos  isol  rb_time
4b811af0 A-B-- 4b7d99c8   4     2500:0x1014018 2578:0x1014080 
... 

To find the session, on the primary, use the owner from ‘onstat -k’ and run:

$ onstat -g ath|grep 4b7d99c8
 tid     tcb      rstcb    prty status                vp-class       name
 1770    4d0e9568 4b7d99c8 1    sleeping secs: 1       5cpu         proxyTh

...make note of the 'tid' value, then run:

$ onstat -g proxy all
IBM Informix Dynamic Server Version 11.50.F       -- on-Line
Secondary  Proxy      Reference Transaction  Hot Row   
Node       ID         Count     Count        Total     
sds2       1609       0         1            0         

TID      Flags      Proxy  Source   Proxy    Current  sqlerrno iserrno 
                    ID     SessID   TxnID    Seq                       
1770     0x00008224 1609   22       3        2        0        0       

...here we see the Proxy ID of 1609, and the Source SessID of 3, and the ProxyTxnID of 2.

On the SDS node, using the ‘Source SessId’ from the ‘onstat –g proxy all’ output, run the following:

$ onstat -g sql 22
IBM Informix Dynamic Server Version 11.50.F       -- Updatable (SDS) -- Up 06:08:18 – 443096 Kbytes

Sess       SQL          Current     Iso Lock       SQL  ISAM F.E.
Id         Stmt type    Database    Lvl Mode       ERR  ERR  Vers Explain
22         -            pwhite      DR  Not Wait   0    0    9.24  Off

Last parsed SQL statement :
  insert into tab1 values(0,"Howdy") { commit work; }

You can also find similar information using the ‘ProxyID’ and the ‘ProxyTxnID’ from the ‘onstat –g proxy all’ output from the primary:

$ onstat -g proxy 1609 3

IBM Informix Dynamic Server Version 11.50.F       -- Updatable (SDS) -- Up 06:07:33 -- 443096 Kbytes
Sequence Operation rowid    Table                          sqlerrno
Number   Type               Name                                   
1        *Insert   0        pwhite:informix.tab1           0  

Conclusion:
Using this method, you can track down user 'pwhite' and find out why he is still causing Informix problems?

PROBLEM

A java application that uses Informix JDBC Driver to deal with BLOBs runs out of memory. Informix JDBC Driver uses the java File.deleteOnExit which causes the OutOfMemory problem in the JVM heap.

Example of error java.lang.OutOfMemoryError

The heap will have a lot of blocks that contain file names that have names like ifxb_123456 or ifxb_123456890. The shorter file names in JDBC drivers upwards from 2.21.JC6 and 3.00.JC1 and the longer filenames in earlier versions. 


CAUSE

Reason for this is a known bug regarding File.deleteOnExit() calls:

http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4813777
http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4513817


SOLUTION

To workaround this set the environment variable LOBCACHE to -1.


EXTERNAL REFERENCES

To read more about the environment variable LOBCACHE see the manual "IBM Informix JDBC Driver Programmer’s Guide".

http://www-306.ibm.com/software/data/informix/pubs/library/

When attempting to load data from a database created with ISO-8859-1 locale into a database using UTF-8 locale, reject files are created for rows containing unrecognizable characters even though CLIENT_LOCALE, DB_LOCALE and SERVER_LOCALE have all been set accordingly.

It is possible that setting CLIENT_LOCALE, DB_LOCALE and SERVER_LOCALE will still leave some characters unrecognizable to a database created with the UTF-8 locale.

Most of the time 8859-1 will load into a UTF-8 database without error, however if you have already attempted to load the data and failed, complete the following steps:

First, set the environment variables CLIENT_LOCALE, DB_LOCALE and SERVER_LOCALE so they have the appropriate settings. After migrating the old data onto the UTF-8 server, use a conversion process such as the iconv API to ensure all data will be accepted into the new database.

For example, once you have migrated the data file, you could run the command:

iconv -f ISO-8859-1 -t UTF-8 {filename} > {pipe, file}

Once the file has been converted, you can direct it to a new file or a pipe before loading it into the destination table.

The partition number (partnum) contains information about a table.

It is 4-byte hexadecimal value (0x12345678) that is composite of two values, the most significant 3 digits indicates which dbspace contains the table (tblspace) and the least 5 significant digits indicates the logical page number that contains information about the table.

It means that 0x123 indicates which dbspace contains the table and 0x45678 indicates a logical page number that keeps information about the table.

For example, if the partnum of table customer is 0x0050015f, it means that dbspace 5 (0x005) contains the table and the logical page 0x0015f keeps information about the customer table.

Note 1: The 'tblsnum' in 'onstat -k' and 'tblnum' in 'onstat -t/-T' corresponds to the part number (partnum) of the table.

Note 2: The value of partnum stored in systables and reported in oncheck -pt is a decimal value, you need to convert it to hexadecimal in order to get the information of which dbspace contains the table and the logical page that keeps information of the table.

You may run into locking issues when running oncheck -pT tablename on a high use table. You can run a query using the sysmaster database instead to avoid encountering locking issues.
The following select statement from the sysmaster database will provide free page information similar information to oncheck -pt:

select count(pb_bitmap), pb_bitmap
  from sysptnbit p,systabnames t
 where t.tabname = "tablename"
   and t.dbsname = "databasename"
   and p.pb_partnum = t.partnum
 group by pb_bitmap;

This select will not place any locks on the base table tablename . The query produces output similar to:

         (count)   pb_bitmap

            2841          12
             196           0
              26           4
               1           8

4 row(s) retrieved.

The pb_bitmap column is the decimal value of the bitmap for each page in the table. The values mean the following:

pb_bitmap Description
========= =========
  0       Unused
  4       Data Page with room for another row
  8       Index Page
 12       Full Data Page

Note: You could use the query to help estimate the number of pages still available within the table. You should estimate on being able to use only 30% of the pages with the value of '4', meaning 'a data page with room for another row'.

Using the example data: The engine would need another extent after using the remaining space in the partially used pages:
(26 * 30%) = 7.8 pages