Informix crash due to UPDATE after VARCHAR column size reduction

Hi. I am posting a problem with a system using Informix 12.10FC12 or later.

After this problem occurs, the Informix process will not start and is a very fatal defect.

The conditions under which the problem may occur are listed below.
1. Add a VARCHAR column to the table.
2. Reduce the size of the added VARCHAR column.

If you execute an UPDATE statement in the above situation, the Informix process will crash with an error.

The process is not always down, and it may or may not go down depending on the length of the data stored in the table.

The following is an online log message when I reproduce process crashes in Informix 12.10 and 14.10.


## 12.10.FC12W1
11:20:01  Assert Failed: Buffer modified in inconsistent chunk.
11:20:01  IBM Informix Dynamic Server Version 12.10.FC12W1WE
11:20:01   Who: Session(87, informix@pilma01, 39452766, 700000020593858)
                Thread(222, sqlexec, 700000020553068, 10)
                File: rsdebug.c Line: 1047
11:20:01   Results: Chunk 13 is being taken OFFLINE.
11:20:01   Action: Restore space containing this chunk from the archive.
11:20:01  stack trace for pid 36175890 written to /work2/ifx1210fc12w1we/tmp/af.4c6a350
11:20:02   See Also: /work2/ifx1210fc12w1we/tmp/af.4c6a350, shmem.4c6a350.0
11:20:10  Buffer modified in inconsistent chunk.
11:20:11  Assert Failed: INFORMIX-OnLine Must ABORT
        Critical media failure.
11:20:11  IBM Informix Dynamic Server Version 12.10.FC12W1WE
11:20:11   Who: Session(87, informix@pilma01, 39452766, 700000020593858)
                Thread(222, sqlexec, 700000020553068, 10)
                File: rsmirror.c Line: 2080
11:20:11  stack trace for pid 36175890 written to /work2/ifx1210fc12w1we/tmp/af.4c6a350
11:20:12   See Also: /work2/ifx1210fc12w1we/tmp/af.4c6a350
11:20:19  Thread ID 222 will NOT be suspended because
          it is in a critical section.
11:20:19   See Also: /work2/ifx1210fc12w1we/tmp/af.4c6a350
11:20:19  rsmirror.c, line 2080, thread 222, proc id 36175890, INFORMIX-OnLine Must ABORT
        Critical media failure..
11:20:19  Fatal error in ADM VP at mt_fn.c:14593
11:20:19  Unexpected virtual processor termination: pid = 36175890, exit status = 0x1.
11:20:19  PANIC: Attempting to bring system down


## 14.10.FC1DE
12:46:54  Assert Failed: Buffer modified in inconsistent chunk.
12:46:54  IBM Informix Dynamic Server Version 14.10.FC1DE
12:46:54   Who: Session(46, informix@ifxdb1, 62804, 0x4526fbc8)
                Thread(55, sqlexec, 4522d8c8, 1)
                File: rsdebug.c Line: 908
12:46:54   Results: Chunk 1 is being taken OFFLINE.
12:46:54   Action: Restore space containing this chunk from the archive.
12:46:54  stack trace for pid 62571 written to /opt/IBM/Informix_Software_Bundle/tmp/af.41fb7ad
12:46:54   See Also: /opt/IBM/Informix_Software_Bundle/tmp/af.41fb7ad, shmem.41fb7ad.0
12:46:57  Buffer modified in inconsistent chunk.
12:46:58  Assert Failed: INFORMIX-OnLine Must ABORT
        Critical media failure.
12:46:58  IBM Informix Dynamic Server Version 14.10.FC1DE
12:46:58   Who: Session(46, informix@ifxdb1, 62804, 0x4526fbc8)
                Thread(55, sqlexec, 4522d8c8, 1)
                File: rsmirror.c Line: 2062
12:46:58  stack trace for pid 62571 written to /opt/IBM/Informix_Software_Bundle/tmp/af.41fb7ad
12:46:58   See Also: /opt/IBM/Informix_Software_Bundle/tmp/af.41fb7ad
12:47:02  Thread ID 55 will NOT be suspended because
          it is in a critical section.
12:47:02   See Also: /opt/IBM/Informix_Software_Bundle/tmp/af.41fb7ad
12:47:02  Starting crash time check of:
12:47:02  1. memory block headers
12:47:02  2. stacks
12:47:02  Crash time checking found no problems
12:47:02  rsmirror.c, line 2062, thread 55, proc id 62571, INFORMIX-OnLine Must ABORT
        Critical media failure..
12:47:02  The Master Daemon Died
12:47:02  PANIC: Attempting to bring system down

I requested technical support from IBM for the above system failure.

The problem was that there were already cases from other customers, and there were no specific scenarios to reproduce.

Below is a link to the document that describes the problem.

http://www-01.ibm.com/support/docview.wss?crawler=1&uid=swg1IT27997

At the time of the failure, when I looked at the contents of the assert failure file in the online message log, I could see the stack trace of that user thread.

0x00000001000af9cc (oninit)afstack

0x00000001000aeb5c (oninit)afhandler

0x00000001000af038 (oninit)affail_interface

0x00000001001b8844 (oninit)buffcheck

0x00000001002371a0 (oninit)buffput

0x0000000100b88640 (oninit)ckpgversion

0x0000000100b87af4 (oninit)rewrecord

0x0000000100b870ec (oninit)rsrewrec

0x000000010071ab00 (oninit)fmrewrec

0x00000001008382a0 (oninit)aud_sqisrewrec

0x0000000100d40a90 (oninit)doupdate

0x0000000100d3ff2c (oninit)chkrowcons

0x000000010114ea04 (oninit)dodmlrow

0x0000000101150eac (oninit)dodelupd

0x000000010083ee30 (oninit)aud_dodelupd

0x0000000100d1ec24 (oninit)excommand

0x00000001008c8590 (oninit)sq_execute

0x00000001008103ac (oninit)sqmain

0x00000001014d6898 (oninit)listen_verify

0x00000001014d530c (oninit)spawn_thread

0x0000000101482ae0 (oninit)th_init_initgls

0x00000001018f86e0 (oninit)startup

I found a stack trace that is very similar to the one described for defect IT27997. The ckpgversion function part of the stack trace seems to be the problem.

And since version 12.10.FC12, it is known that the in-place alter function has been improved. I think that this improvement has caused the defect.

In the presentation of IIUG2018, Jeff McMahon and Nick Geib published What's New in Informix, which shows that the type that changes between VARCHAR and VARCHAR (smaller or larger) size is made by in-place alteration.

Below is a script that can reproduce an instance crash based on the information described in IT27997 above.

It is easy to reproduce the test data by making a fixed length field of 60 characters.

drop table test;

create table test (a varchar(60));

load from test.unl insert into test;

alter table test add b int;

alter table test add c varchar(5);

alter table test add d varchar(5);

alter table test add e varchar(5);

alter table test modify c varchar(1);

alter table test modify d varchar(1);

alter table test modify e varchar(1);

update test set a=' qui officia deserunt mollit anim id est laborum.Lorem ip';

This issue occurs with versions 12.10.FC12 and 12.10.FC12W1, so if you are using that version, you will not have a problem if you request a patch version from IBM or downgrade to version 12.10.FC11 or below.

I hope that you can use Informix with stability.

Initializing the sysadmin database

Starting with Informix 11, there is a sysadmin database for database administration.

Because the sysadmin database contains Informix state information, it is recommended that you periodically delete the data if the monitoring data is accumulated in the database, or place the sysadmin database in a separate dbspace.

To regenerate the sysadmin database, you can use a script in the directory where Informix is ​​installed or the admin / task procedure in the sysadmin database.


1. Manually rebuild the sysadmin database

According to IBM Technote, there is a script in $INFORMIXDIR/etc/sysadmin to regenerate the sysadmin database.

Execute the following commands in order.


cd $INFORMIXDIR/etc/sysadmin;
dbaccess - db_uninstall.sql;
dbaccess - db_create.sql;
dbaccess sysadmin db_install.sql;
dbaccess sysadmin sch_tasks.sql;
dbaccess sysadmin sch_aus.sql;
dbaccess sysadmin sch_sqlcap.sql;
dbaccess sysadmin start.sql;


Looking at the contents of the db_create.sql file, there is a syntax for creating a database.


CREATE DATABASE sysadmin WITH LOG;


You can also modify the contents of the file to specify a dbspace with this syntax.


2. Using the procedures in the sysadmin database to move the sysadmin database to another dbspace

You can regenerate the sysadmin database using the admin / task procedure in the sysadmin database.


execute function task ("reset sysadmin", "admindbs");


If you do not specify a dbspace name, it is created in rootdbs. When regeneration is complete, the scheduler will restart automatically.



** Reference
https://www-01.ibm.com/support/docview.wss?uid=swg21266296
https://www-01.ibm.com/support/docview.wss?uid=swg21420189

hextoraw

writing in progress..

Moving BLOB Data between MySQL and Informix

I have migrated a table containing MySQL's Binary Large Object field to Informix.
System information for migration testing is shown below.

MySQL: 5.0.89, AIX 6.1
Informix: 11.50.FC9, AIX 6.1

Creating Sample Tables and Data (MySQL)

echo "create table blotest(a int, b blob)" | mysql -u root -p1nfra test

echo "insert into blobtest values (2,load_file('/home/mysql/logo.gif'))" | mysql -u user -ppasswd test

Initially, the data was extracted with the --hex-blob and --tab options of the mysqldump utility, but when extracted as a delimiter, it was not extracted as a hex.
So I extracted it with the OUTFILE statement as shown below.

echo "SELECT a,hex(b) INTO OUTFILE '/home/mysql/blobtest.txt'  FIELDS TERMINATED BY '|'      LINES TERMINATED BY '|\n'     FROM blobtest;" | mysql -u user -ppasswd test

As mentioned above, when extracting data using the OUTFILE statement in MySQL, the NULL value is extracted as '\ N', so it needs to be processed.

And since Informix's delimiter file has delimiters at the end of the record, we've set LINES TERMINTAED BY to '|\n'.

In MySQL, the alphabet of the result of the hex function is printed in uppercase, while Informix is ​​printed in lowercase, but there is no problem loading the data.


Create table to enter BLOB data in Informix (Informix)

echo "create table blobtest (a byte)" | dbaccess stores_demo

echo "create table blobtest1 (a blob)" | dbaccess stores_demo

When inputting to BLOB type in Informix, text and image data should be separated, so I executed the process of inputting image data first in BYTE type, then in BLOB format and then input again.

echo "load from /home/mysql/blobtest.txt insert into blobtest" | dbaccess stores_Demo

echo "insert into blobtest1 select b::blob from blobtest" | dbaccess stores_demo

After checking with the GUI tool, I was able to confirm that the image was well entered.


source:
How to migrate large blob table from mysql to postgresql?
http://dba.stackexchange.com/questions/4211/how-to-migrate-large-blob-table-from-mysql-to-postgresql

Migrate a database from MySQL to IBM Informix Innovator-C Edition, Part 1: Comparing MySQL to IBM Informix Innovator-C Edition
http://www.ibm.com/developerworks/data/library/techarticle/dm-1102mysqltoinnovatorc/

Migrate a database from MySQL to IBM Informix Innovator-C Edition, Part 2: Step-by-step walk-through of the migration process
http://www.ibm.com/developerworks/data/tutorials/dm-1102mysqltoinnovatorc2/index.html

how to terminate global transactions?

This is a post about the workaround when Global Transaction is not terminated gracefully. We will introduce the case in detail.

On my Informix instance I have two global transactions holding some locks. See onstat -G and onstat -x output below.
How can I get rid of them?
Also after a server reboot they are still there.
onmode -Z <address> or onmode -H <address> do not work (see below).

IBM Informix Dynamic Server Version 11.50.UC7IE on Solaris SPARC 10.

Global Transaction Identifiers
address flags isol timeout fID gtl bql data
56656bd8 -LX-G LC 0 131075 30 28 312D2D35336530656466663A613139383.....
56656e28 -LX-G LC 0 131075 30 28 312D2D35336530656466663A613139383.....
2 active, 128 total

Transactions

                                                                          est.

address  flags userthread locks  begin_logpos      current logpos isol    rb_time  retrys coord

56655018 A---- 56623018   0      -                 -              COMMIT  -        0

56655268 A---- 56623638   0      -                 -              COMMIT  -        0

566554b8 A---- 56623c58   0      -                 -              COMMIT  -        0

56655708 A---- 56624278   0      -                 -              COMMIT  -        0

56655958 A---- 56624898   0      -                 -              COMMIT  -        0

56655ba8 A---- 56624eb8   0      -                 -              COMMIT  -        0

56655df8 A---- 566254d8   0      -                 -              COMMIT  -        0

56656048 A---- 56625af8   0      -                 -              COMMIT  -        0

56656298 A---- 56626118   0      -                 -              COMMIT  -        0

566564e8 A---- 56626738   0      -                 -              COMMIT  -        0

56656738 A---- 56626d58   0      -                 -              COMMIT  -        0

56656988 A---- 56627378   0      -                 -              COMMIT  -        0

56656bd8 -LX-G 0          2      logid 303         303:0x64b2622c COMMIT  2:58     0

56656e28 -LX-G 0          2      logid 303         303:0x64b2922c COMMIT  2:58     0

56657078 A---- 56627fb8   0      -                 -              COMMIT  -        0

566572c8 A---- 56627998   0      -                 -              COMMIT  -        0

56657e58 A---- 56629218   0      -                 -              COMMIT  -        0

Transaction with flags value of '-LX-G' does not have userthread information as in the example above. These transactions are called orphaned transactions, and the userthread has been dropped from the transaction. If these transactions stop using the Logical Log, they may enter the Long Transaction state.


> onmode -Z 1449487912
onmode: Cannot kill transaction 0x56656e28.
Only I-STAR subordinates that are PREPARE'd or HEURISTICally ABORT'd
may be heuristically completed.

> onmode -H 1449487912
onmode -H may only be used to kill heuristically completed transactions.


This is the result of running onmode with address in decimal.
You can also run it in the same format as onmode -Z (or -H) 0x56656e28.

If you continue reading the post, you will find:

Try onmode -l until you have forced a long transaction, this usually
help us. We have requested a way to get rid of these, but it seems
that the view of  IBM that the TM should handle this i all cases.
Problem is that the TM fails sometimes, and then you are stuck.

Regards

Ulf

In the case of Frank, who asked the first question, log switching occurred naturally and caused a long transaction. This depends on the LTXHWM configuration variable setting, which defaults to 70. If there are 10 Logical Logs, if a transaction does not complete during 7 log switches, a long transaction is usually triggered.

Today, the transactions were rolled back by the server:

23:42:09  Aborting Long Transaction: tx: 0x56656bd8 no user info due to XA or distributed (2-phase commit) transaction
23:42:09  Aborting Long Transaction: tx: 0x56656e28 no user info due to XA or distributed (2-phase commit) transaction
23:42:13  Checkpoint Completed:  duration was 4 seconds.
23:42:13  Thu Jul  1 - loguniq 306, logpos 0x3fff33b4, timestamp: 0xdedfaa19 Interval: 5616

23:42:13  Maximum server connections 15
23:42:13  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 1, Plog used 83432, Llog used 182271

23:42:13  Session completed abnormally. Rolling back tx id 24, flags 0x108463b
23:42:13  Session completed abnormally. Rolling back tx id 32, flags 0x108463b
23:42:13  Long Transaction 0x56656e28 Aborted. Rollback Duration: 0 Seconds
23:42:14  Long Transaction 0x56656bd8 Aborted. Rollback Duration: 0 Seconds

If onmode -l causes manual log switching or if the log accumulates and exceeds the LTXHWM limit, it will be processed as above. Generally, this does not affect instances or other transactions when processed as above.

It is difficult to find official documentation for onmode -H. It seems to apply only to heuristic transactions (onmode -H may only be used to kill heuristically completed transactions).

In my experience it is used when the value of flags in onstat -G is:

700000379440490  -TH-G 0                0      108002:0xaf7a018  108072:0xd068     DIRTY   26:56    0

70000038ad89b48  -TH-G 0                0      107898:0x4524018  107968:0x6a018    COMMIT  0:00     0

70000038c7ccfa0  -TH-G 0                0      107993:0x14781050 108063:0x9404     DIRTY   32:58    0

70000038c7e1160  -TH-G 0                0      108002:0x2dea018  108072:0x27678    DIRTY   27:10    0

70000038d69e178  -TH-G 0                0      108002:0x39e018   108072:0xe118     DIRTY   27:14    0

If the flags value is in the '-TH-G' state, the transaction will not be dropped beyond the LTXHWM limit. In this case, you can inadvertently remove it with the -H option, but you should be careful to use it because there is no description of the -H option on the IBM Knowledge Center or onmode command.

source:

http://www.iiug.org/forums/ids/index.cgi/noframes/read/2975

https://groups.google.com/forum/?hl=en&fromgroups=#!topic/comp.databases.informix/egoEcVkTmbY

https://www.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.admin.doc/ids_admin_1066.htm

Checking the number of multiple table records with a single query

There is a query that uses Oracle's getxmltype function to get the number of records in each table.

I have tested it for Informix.

First of all, here's an example of using the getxmltype function in Oracle

SELECT table_name

     , num_rows -- information about the number of table records in the catalog table

     , TO_NUMBER(

       dbms_xmlgen.getxmltype('SELECT COUNT(*) c FROM ' || table_name).Extract('//text()')

       ) num_rows2 -- Number of records by count function

  FROM user_tables

;

Executing the above query from the SCOTT account, which is sample data, shows the following results.

TABLE_NAME                       NUM_ROWS  NUM_ROWS2

------------------------------ ---------- ----------

DEPT                                    4          4

EMP                                    14         14

BONUS                                   0          0

SALGRADE                                5          5

TB_DF_STAT                             24         24

To use XML functions in Informix, the idsxmlvp class Virtual Processor must be running.

This can be set dynamically with the onconfig VPCLASS setting or the onmode -wm / -wf command.



In the Informix sample database stores_demo, we created the following query using the xml function:

The functions used are extractvalue, genxmlquery. These are built-in functions.

select tabname,

       nrows::int num_rows,

       extractvalue(genxmlquery('set','SELECT count(*) c FROM '||tabname),'/set/row/c')::int num_rows2 

from   systables where tabid > 99 and tabtype = 'T';

tabname     customer

num_rows    28

num_rows2   28

tabname     orders

num_rows    23

num_rows2   23

tabname     manufact

num_rows    9

num_rows2   9

tabname     stock

num_rows    74

num_rows2   74

tabname     items

num_rows    67

num_rows2   67

It's convenient because you can verify the number of cases with a single query. It seems to be good to use according to the purpose.

In addition to using the COUNT function, there seems to be a way to use it.

If the number of records is too large, the system may be overloaded due to high resource consumption such as memory and disk.


Reference:
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.xml.doc/ids_xpconfig.htm
http://www.gurubee.net/article/55486
http://www.gurubee.net/article/61164

Enabling parallelism and fragmentation in IBM Informix Growth Edition

Since Informix 11.50.xC7 and later versions of Growth Edition, parallel processing and partitioning functionality is physically restricted.
However, in versions below 11.50.xC6, there were no physical limitations on the functions of the Workgroup Edition, and there were no restrictions on the use of licenses.
Below is a description of how you can continue to use these features when upgrading the Informix version in the above cases.
Using this feature without an IBM license agreement may cause cost problems due to breach of contract.

For reference, Growth Edition is a term that refers to the previous Workgroup Edition, and it has a different name depending on the version.
11.50.xC1 to 11.50.xC6: Workgroup Edition
11.50.xC7 to 11.50.xC9: Growth Edition
11.70: Growth Edition
12.10: Workgroup Edition

The release notes for the 11.50.xC7 version of IBM Informix provide an introduction to enabling parallel processing and fragmentation.

Enabling parallelism and fragmentation in IBM Informix Growth Edition

ImportantThese features are limited to customers who were explicitly granted the rights to use them. Customers who have Informix Dynamic Server Workgroup Edition entitlements before February 10, 2009 have been grandfathered to use these features. See the IBM Withdrawal Announcement 909-049 for specific terms and conditions: http://www-01.ibm.com/common/ssi/rep_ca/9/897/ENUS909-049/ENUS909-049.PDF.

By default, the following parallelism and fragmentation features are not enabled:

• parallel query
• parallel index build
• parallel load
• parallel backup
• parallel restore
• parallel sort
• high performance loader
• table fragmentation
• index fragmentation

If you are entitled to use these features, you can enable them. Add the GE_ALLOW_PARALLEL configuration parameter to your onconfig file and set the value for this configuration parameter to 1.

Reference
https://www.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.relnotes.doc/ifx_1150xc7/ids_win_release_notes_11.50.html