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

LOB type data size calculation

Hi. I have tested how to calculate the data size of an Informix Large Object (LOB) type.

I know that there is no function that can process LOB type data, so I installed and used the blade manager's bundled module as shown below.

1. First, create the test database stores_demo as shown below.

$ dbaccessdemo -log -dbspace datadbs1

2. Install the module for processing LOB type data in blademgr as shown below.

$ blademgr

ol_informix1210_1>show databases

Databases on server:

        stores_demo

        sysadmin

        sysuser

ol_informix1210_1>list stores_demo

DataBlade modules registered in database stores_demo:

         TimeSeries.6.00.FC7          ifxrltree.2.00

            spatial.8.22.FC2

ol_informix1210_1>show modules

16 DataBlade modules installed on server ol_informix1210_1:

                LLD.1.20.FC2                Node.2.0 c

           TSAFuncs.1.00.FC1       TSPIndex.1.00.FC1

           TSPMatch.2.00.FC1     TimeSeries.6.00.FC7

               binaryudt.1.0                bts.3.10

                excompat.1.0         ifxbuiltins.1.1

               ifxregex.1.00          ifxrltree.2.00

                 mqblade.2.0        spatial.8.22.FC2

                sts.2.00.FC1            wfs.1.00.FC1

A 'c' indicates DataBlade module has client files.

If a module does not show up, check the prepare log.

ol_informix1210_1>register excompat.1.0 stores_demo

Register module excompat.1.0 into database stores_demo? [Y/n]y

Registering DataBlade module... (may take a while).

DataBlade excompat.1.0 was successfully registered in database stores_demo.

3. After installing the above procedure, you can see that dbms related functions and procedures have been created.

$ dbschema -d stores_demo -f all | egrep 'create function.*dbms_|create procedure.*dbms'

create function "informix".dbms_lob_getlength (blob)

create function "informix".dbms_lob_getlength (clob)

create function "informix".dbms_lob_compare (blob,blob,integer default 2147483647,integer default 1,integer default 1)

create function "informix".dbms_lob_substr (clob,integer default 32767,integer default 1)

create function "informix".dbms_lob_instr (clob,lvarchar,integer default 1,integer default 1)

create function "informix".dbms_lob_new_clob (lvarchar)

create function "informix".dbms_lob_compare (clob,clob,integer default 2147483647,integer default 1,integer default 1)

create function "informix".dbms_random_jrand48 (inout bigint)

create procedure "informix".dbms_output_enable (integer default 20000)

create procedure "informix".dbms_output_disable ()

create procedure "informix".dbms_output_put (lvarchar)

...

4. Check the size before entering the sample image file of the Informix demo program.

$ ls -l demo/esqlc/cn*.gif

-rw-r--r--    1 informix informix      15736 Apr 19 18:08 demo/esqlc/cn_10001.gif

-rw-r--r--    1 informix informix      21454 Apr 19 18:08 demo/esqlc/cn_10027.gif

-rw-r--r--    1 informix informix      11751 Apr 19 18:08 demo/esqlc/cn_10031.gif

-rw-r--r--    1 informix informix      25610 Apr 19 18:08 demo/esqlc/cn_10046.gif

-rw-r--r--    1 informix informix       7937 Apr 19 18:08 demo/esqlc/cn_10049.gif

5. Create the table to be entered and calculate the LOB data size.

$ dbaccess stores_demo -

> create table test (filename varchar(30), img blob);

> insert into test values ('cn_10001.gif', filetoblob('./demo/esqlc/cn_10001.gif','client'));

> insert into test values ('cn_10027.gif', filetoblob('./demo/esqlc/cn_10027.gif','client'));

> insert into test values ('cn_10031.gif', filetoblob('./demo/esqlc/cn_10031.gif','client'));

> insert into test values ('cn_10046.gif', filetoblob('./demo/esqlc/cn_10046.gif','client'));

> insert into test values ('cn_10049.gif', filetoblob('./demo/esqlc/cn_10049.gif','client'));

> select filename, dbms_lob_getlength (img) from test;

filename                       (ｅxpression)

cn_10001.gif                          15736

cn_10027.gif                          21454

cn_10031.gif                          11751

cn_10046.gif                          25610

cn_10049.gif                           7937

5 row(s) retrieved.

The Knowledge Center does not document DBMS_LOB_LENGTH, so there seems to be confusion.

Hope to see you.


** Reference
http://www-01.ibm.com/support/docview.wss?uid=swg21670983
https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.dbext.doc/ids_r0055123.htm

How to change regular expression pattern using SUBSTR, LPAD

Hi. In the following article, I wrote about REGEX_REPLACE, the regular expression pattern change function of Informix.

However, since the REGEX_REPLACE function is only supported in Informix version 12, so the questioner asked again how to do it before version 12.



Thank you for your comment.
The query in Oracle that Manon's uploaded is applied.
I can not apply Informix 12 version.
Is it possible to use Informix without using regex_replace?




I have tested this query again with a query that was rewritten by the Database Sarang Net 'Manon', using basic string functions.




I have configured only basic Oracle character functions.
Please fix it for Informix.

WITH t AS
(
SELECT '문자001' v FROM dual
UNION ALL SELECT '문자열01'    FROM dual
UNION ALL SELECT '01문자001'   FROM dual
UNION ALL SELECT '121문자열01' FROM dual
UNION ALL SELECT '문자002'     FROM dual
UNION ALL SELECT '문자003'     FROM dual
UNION ALL SELECT '문자1'       FROM dual
UNION ALL SELECT '문자'        FROM dual
UNION ALL SELECT '문자11A03'   FROM dual
)
SELECT v
     , SUBSTR(v, 1, x-y) || LPAD(SUBSTR(v, x-y+1, 4), 4, '0') z
  FROM (SELECT v
             , x
             , CASE WHEN SUBSTR(v, x-0, 1) BETWEEN '0' AND '9' THEN 1
             + CASE WHEN SUBSTR(v, x-1, 1) BETWEEN '0' AND '9' THEN 1
             + CASE WHEN SUBSTR(v, x-2, 1) BETWEEN '0' AND '9' THEN 1
             + CASE WHEN SUBSTR(v, x-3, 1) BETWEEN '0' AND '9' THEN 1
               ELSE 0 END ELSE 0 END ELSE 0 END ELSE 0 END AS y
          FROM (SELECT v, LENGTH(v) x FROM t) a
        ) a
;



It worked well with Informix 11 version.

Note that the WITH clause can not be used in Informix.

It's really cool. I am grateful to Manon for giving me instructions on how to write the query.

Regular expression pattern change function REGEX_REPLACE

Hi. Informix has a MATCHES keyword that you can search using regular expressions in conditional terms.

Oracle's REGEXP_REPLACE function, which changes the pattern retrieved by regular expressions, is supported in version 12.

I found out about the pattern change in the Database Sarang Net and tested it in the Informix environment.

Below are the posts you have posted.

The column data

'문자001'

'문자열01'

'01문자001'

'121문자열01'

'문자002'

'문자003'

In this way ... I have a column that contains multiple columns.

Only the digits after 001 should be numbered with 0001

So the result is

'문자0001'

문자열0001'

01문자0001'

121문자열0001'

'문자0002'

'문자0003'

This number should be represented in 4 digits in common.

I think we should separate the letters and digits (the serial numbers on the back) and then pad the back numbers to 0000.

Is this possible with Oracle and Informix?

Source: Database LoveNet (http://database.sarang.net/?criteria=oracle&subcrit=qna&inc=read&aid=41526)

Thanks to Manon, who is a stronghold of the Database Sarang Net, he posted the following query in the reply, and it was easy to test it.

WITH t AS

(

SELECT 'character 001' v FROM dual

UNION ALL SELECT 'string 01' FROM dual

UNION ALL SELECT '01 Character 001 'FROM dual

UNION ALL SELECT '121 String 01' FROM dual

UNION ALL SELECT 'character 002' FROM dual

UNION ALL SELECT 'character 003' FROM dual

)

- Oracle regular expressions -

SELECT v

     , REGEXP_REPLACE (REGEXP_REPLACE (v

       , '([0-9] +) $', '000 \ 1'

       , '0 + ([0-9] {4}) $', '\ 1'

       AS x

  FROM t

;

I've done the following in dbaccess. I cast it to varchar (20) to make it look better.

- Temp table creation and data entry -

create temp table t (v varchar(20));

insert into t values ('문자001');

insert into t values ('문자열01');

insert into t values ('01문자001');

insert into t values ('121문자열01');

insert into t values ('문자002');

insert into t values ('문자003');

- Result of using REGEX_REPLACE function -

SELECT v::varchar(20)

       AS v

       , REGEX_REPLACE(REGEX_REPLACE(v

       , '([0-9]+)$', '000\1')

       , '0+([0-9]{4})$', '\1')::varchar(20)

       AS x

  FROM t

;

v                    x

문자001              문자0001

문자열01             문자열0001

01문자001            01문자0001

121문자열01          121문자열0001

문자002              문자0002

문자003              문자0003

6 row (s) retrieved.

REGEX_REPLACE is an extension of Informix that installs the function the first time it is called.

It is generated in the form of an external function. If it is executed without being installed, it seems to be installed automatically.

You can check the Informix message logs and database schema for relevant information.

$ onstat -m

...

15:39:53 Logical Log 420 Complete, timestamp: 0xe75412.

15:40:09 INFO (autoregexe 13) (EXECUTE FUNCTION sysbldprepare ('ifxregex. *', 'Create'))

$ dbschema -d kscdb -f all

...

create function "informix" .regex_replace (lvarchar, lvarchar, lvarchar, integer default null, integer default null, integer default null)

returns lvarchar

with (parallelizable, handlesnulls, not variant, percall_cost = 1000)

external name '$ INFORMIXDIR / extend / ifxregex.1.00 / ifxregex.bld (ifxregex_replace_char)' language c;

Please note that it may be useful to use.

References

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.dbext.doc/ids_dbxt_559.htm