pajama

When executing SQL like below:

Example:
unload to x.unl select tabname, "" from systables where tabname = "tab1" 

The output file contains:
tab1|\ |

It is expected behavior as NULL cannot be represented by "", because IBM Informix considers this an empty string internally.

Use one of the following workarounds to get || instead of |\ | :

1) SELECT NULL::CHAR FROM table


or

2) SELECT CAST(NULL AS CHAR) FROM table



This simply defines the data type of the result set to return to the client.

Example:

unload to tab1.unl select tabname,CAST(NULL AS CHAR) from systables where tabid = 99;

1 row(s) unloaded.

> !cat t1.unl
VERSION||

or

> unload to t1.unl select tabname,NULL::CHAR from systables where tabid = 99;

1 row(s) unloaded.

> !cat tab1.unl

During the Informix instance start you will see following types of error messages in the instance message log and the instance will not come online:

13:23:26  Assert Failed: No Exception Handler
13:23:26  IBM Informix Dynamic Server Version 11.70.FC7W2
13:23:26   Who: Thread(134, kaio, 0, 16)
  File: mtex.c Line: 490
13:23:26   Results: Exception Caught. Type: MT_EX_OS, Context: mem
13:23:26   Action: Please notify IBM Informix Technical Support.
13:23:26   See Also: /tmp/af.46e0eae, shmem.46e0eae.0

The af.<xxxxxx> file will contain an empty stack trace of the kaio thread; the si_signo value of 11 will indicate the SEGV signal received by the oninit process:

13:23:26  Stack for thread: 134 kaio

 base: 0x0000000146f02000
  len:   69632
   pc: 0x0000000101103350
  tos: 0x00000001019ff751
state: running
   vp: 22

 ucontext: 0x0000000101a00a20
  siginfo: 0x0000000101a00d00
           si_signo: 11  si_code: 1   si_errno: 0 si_pid: 0 si_uid: 0
           si_addr: 0x0000000001057d68 si_value: 0x0000000000000000  si_fd: 0

One of the possible causes of the problem is following Oracle Solaris defect:

15815250 : SUNBT7197575 AUTOMOUNTD FAILS TO MOUNT LDAP USERS HOME DIR on SOLARIS 10 BRANDED

Contact the Oracle Solaris support and ask them for following fix:

IDR-fix idr781.1 for Solaris 11.1.8.4

INTRODUCTION

This document describes a technique that can help you rebuild a table based on the data that might be still accessible. It uses the concept of the ROWIDs and the UNLOAD command.

Attention: This technique only saves rows that are still accessible. Damaged rows are lost.

Note: The technique will not work for fragmented tables, because they do not have rowids.

1. Use the UNLOAD command to extract the accessible rows from the table. This command will fail when the first corrupted row is encountered:

UNLOAD TO file1 SELECT rowid, * FROM table ;

file1  
File name where the data is going to the stored

table
Name of the source table

2. Open file1 and take note of the last ROWID that was unloaded successfully:

last_rowid = Last ROWID in file1

3. Add two units to the last_rowid. You will use this value in the next step:

next_rowid = last_rowid + 2

4. Run the UNLOAD command again, this time to retrieve the remaining rows of the table using the next_rowid as the filtering argument:

UNLOAD TO file2
SELECT rowid, * FROM table where rowid >= next_rowid

Note: If UNLOAD fails without producing any data, skip another rowid by increasing next_rowid. You might have to repeat steps 2, 3 and 4 many times depending on the quantity of corrupted rows. There are some scenarios where this technique can be very time-consuming.

5. After unloading all possible rows, repeat steps 1 and 2, this time without selecting the rowid in the column list of the SELECT statement.

UNLOAD TO file1 SELECT * FROM table ;
UNLOAD TO file2
SELECT * FROM table where rowid >= next_rowid

6. Recreate the table using the data that was just unloaded.

LOAD FROM file1 INSERT INTO table;
LOAD FROM file2 INSERT INTO table;

1. oncheck reported an error in the table customer. Run the first UNLOAD command until it fails.

UNLOAD TO file1 SELECT rowid, * FROM customer;

2. Suppose the last rowid found in the last record on file1 is decimal 1038:

last_rowid = 1038

3. Add two units to last_rowid :

next_rowid = (last_rowid + 2) = 1040

4. Using this calculation, restart the unload on rowid 1040:

UNLOAD TO file2
SELECT rowid, * FROM customer where rowid >= 1040;

5. Repeat this process until you scan the whole table. Assuming that the second UNLOAD command finished without errors, the next step is to run the same commands again without the rowid.

UNLOAD TO file1 SELECT * FROM customer;
UNLOAD TO file2
SELECT * FROM customer where rowid >=1040;

6. Recreate the table and load the data using file1 and file2 as input files:

LOAD from file1 insert into customer;
LOAD from file2 insert into customer;

Messages similar to the following will be displayed in the Message.log on Primary HDR server.

R: Needed to send a ping message but failed. 1
12:31:25 Logical Log 358 Complete, timestamp: 0x20c592c5.
12:31:27 DR: Needed to send a ping message but failed. 1
12:32:28 DR: Needed to send a ping message but failed. 1
12:33:11 Logical Log 359 Complete, timestamp: 0x20ca1170.
12:33:28 DR: Needed to send a ping message but failed. 1
12:33:59 DR: Needed to send a ping message but failed. 1
12:34:29 DR: Needed to send a ping message but failed. 1
12:35:06 Logi

This is strictly a new Warning message developed and released in IDS 11.70.xC7.

This message is displayed when the HDR buffer starts to backup and fill. This is usually evident of a very Busy system and no cause for alarm.

Monitor these Warnings. If the numeric value in the Warning message reaches a value of 3 or greater, possible Ping Time-out errors could start to occur.

Certain env variables set

determine if any of the following environment variables are set and unset them

• IFX_AUTOFREE
• IFX_DEFERRED_PREPARE
• OPTOFC

ls -l shows that the chunks are in the raw devices which are created as block devices by mistake.

ls -l
brw-rw---- 1 root system 22, 1 Aug 4 2012 rootchk
brw-rw---- 1 root system 22, 1 Aug 4 2012 llogchk
brw-rw---- 1 root system 22, 1 Aug 4 2012 plogchk
brw-rw---- 1 root system 22, 1 Aug 4 2012 datachk


The raw devices should be character devices.
crw-rw---- 1 root system 22, 1 Aug 4 2012 rootchk
crw-rw---- 1 root system 22, 1 Aug 4 2012 llogchk
crw-rw---- 1 root system 22, 1 Aug 4 2012 plogchk
crw-rw---- 1 root system 22, 1 Aug 4 2012 datachk

Change the devices to character devices with relative OS commands and restart Informix Server. Then KAIO will be enabled correctly.

To find the SQL Queries:

SELECT FIRST 10 * FROM sysmaster:syssqltrace  WHERE sql_runtime > 0  ORDER BY sql_runtime DESC; 

Then you can use the sql_id to query the sqltrace_iter tables to get the SQL statement iterators info. 

Syntax:
select * from sysmaster:syssqltrace_iter where sql_id = <sid> 

Example:
> select * from syssqltrace_iter where sql_id = 4; 

sql_id             4 
sql_address        504403159339495528 
sql_itr_address    504403159339512048 
sql_itr_id         1 
sql_itr_left       0 
sql_itr_right      0 
sql_itr_cost       9 
sql_itr_estrows    100 
sql_itr_numrows    1 
sql_itr_type       1 
sql_itr_misc       65672 
sql_itr_info       Seq Scan 
sql_itr_time       4.16157768e-05 
sql_itr_partnum    38 
sql_itr_sender     0 
sql_itr_nxtsender  0

Errors -252/-113 are returned:

252: Cannot get system information for table.

113: ISAM error: the file is locked.

Isolation level Committed Read or Last Committed is used and there is an exclusive lock on the table

Using 'onstat -g sql' check the isolation level that sessions are using:

> onstat -g sql

IBM Informix Dynamic Server Version 11.70.FC3 -- on-Line -- Up 00:52:17 -- 182532 Kbytes

Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
39 - stores_demo CR Not Wait 0 0 9.24 Off
27 sysadmin DR Wait 5 0 0 - Off
26 sysadmin DR Wait 5 0 0 - Off
25 sysadmin DR Wait 5 0 0 - Off

In the above example session #39 is using an isolation level of Committed Read (CR).

Then check the table for exclusive locks. To do so, first get the partnum of the table. This can be done by running the 'oncheck -pt <dbname:tabname>' command:

> oncheck -pt stores_demo:customer

TBLspace Report for stores_demo:informix.customer

Physical Address 2:678
Creation date 08/05/2011 15:55:41
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 134
Number of special columns 0
Number of keys 0
Number of extents 1
Current serial value 129
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8
Next extent size 8
Number of pages allocated 8
Number of pages used 3
Number of data pages 2
Number of rows 28
Partition partnum 1049059
Partition lockid 1049059
<...>

Next, convert the partnum value into HEX: 1049059 = 0x1001e3

Finally, grep the partnum value (without the leading 0x) from 'onstat -k' output:

> onstat -k | grep 1001e3
4ebef028 0 4b85c418 4ebeed28 HDR+X 1001e3 102 0 D
4ebef328 0 4b85c418 4ebef228 HDR+X 1001e3 103 0 D
4ebef528 0 4b85c418 4ebef428 HDR+X 1001e3 104 0 D
4ebef728 0 4b85c418 4ebef628 HDR+X 1001e3 105 0 D
4ebef828 0 4b85c418 4ebeefa8 HDR+X 1001e3 106 0 D
4ebefa28 0 4b85c418 4ebef9a8 HDR+X 1001e3 107 0 D
4ebefb28 0 4b85c418 4ebefaa8 HDR+X 1001e3 108 0 D
4ebefd28 0 4b85c418 4ebefca8 HDR+X 1001e3 109 0 D
4ebeff28 0 4b85c418 4ebefea8 HDR+X 1001e3 10a 0 D
4ebf0128 0 4b85c418 4ebf00a8 HDR+X 1001e3 10b 0 D
4ebf0328 0 4b85c418 4ebf02a8 HDR+X 1001e3 10c 0 D
4ebf0528 0 4b85c418 4ebf04a8 HDR+X 1001e3 10d 0 D
4ebf0728 0 4b85c418 4ebf06a8 HDR+X 1001e3 10e 0 D
4ebf0928 0 4b85c418 4ebf08a8 HDR+X 1001e3 201 0 D
4ebf0b28 0 4b85c418 4ebf0aa8 HDR+X 1001e3 202 0 D
4ebf0ca8 0 4b85c418 4ebf0c28 HDR+X 1001e3 203 0 D
4ebf0ea8 0 4b85c418 4ebf0e28 HDR+X 1001e3 204 0 D
4ebf10a8 0 4b85c418 4ebf1028 HDR+X 1001e3 205 0 D
4ebf12a8 0 4b85c418 4ebf1228 HDR+X 1001e3 206 0 D
4ebf1428 0 4b85c418 4ebf13a8 HDR+X 1001e3 207 0 D
4ebf1628 0 4b85c418 4ebf15a8 HDR+X 1001e3 208 0 D
4ebf1828 0 4b85c418 4ebf17a8 HDR+X 1001e3 209 0 D
4ebf1a28 0 4b85c418 4ebf19a8 HDR+X 1001e3 20a 0 D
4ebf1c28 0 4b85c418 4ebf1ba8 HDR+X 1001e3 20b 0 D
4ebf1e28 0 4b85c418 4ebf1da8 HDR+X 1001e3 20c 0 D
4ebf2028 0 4b85c418 4ebf1fa8 HDR+X 1001e3 20d 0 D
4ebf2228 0 4b85c418 4ebf21a8 HDR+X 1001e3 20e 0 D
4ebfeda8 0 4b85c418 4ebfee28 HDR+X 1001e3 101 0 D
4ebfee28 0 4b85c418 44452788 HDR+IX 1001e3 0 0

In the above example, the table has an intent-exclusive lock on the whole table (HDR+IX) and some exclusive locks on its rows (HDR+X).

Set the isolation level to Dirty Read at the session level before running the query:

> echo "set isolation to dirty read; select count(*) from customer;" | dbaccess stores_demo

Database selected.

Isolation level set.


(count(*))

28

1 row(s) retrieved.

Database closed.

Another option is to set lock mode to wait in order to allow the query to wait until the lock is released:

> echo "set lock mode to wait; select count(*) from customer;" | dbaccess stores_demo