Informix

 View Only

 using ER to clone a table with different column type

Jump to  Best Answer
Jacobo BC's profile image
Jacobo BC posted Tue January 20, 2026 12:49 PM

Is it possible to use ER to loopback clone a table but altering it so you can change a serial into int8 or serial8?

I'm against a brick wall. I managed to define the replication (not trivial, clasic -c er seems to reject different data types) but then it doesn't convert the rows automaticaly.

sale has a field serial and sale_new serial8, i've tried serial to int8 with same result.

cdr define replicate repl_sale -C always -S row -A -R "P carsales@group_happy_1:informix.sale" "select * from informix.sale" "R carsales@g_loopback:informix.sale_new" "select * from informix.sale_new"new"
Interpreting this replicate as a master replicate.
Verification of carsales@group_happy_1:informix.sale started 
Verification of carsales@group_happy_1:informix.sale is successful
Verification of carsales@g_loopback:informix.sale_new started 
Verification of carsales@g_loopback:informix.sale_new is successful
1-happynode1-informix-~>
1-happynode1-informix-~>cdr start replicate repl_sale
1-happynode1-informix-~>cdr check replicate -r repl_sale -m group_happy_1 -a -R 
Jan 20 2026 18:38:29 ------   Table scan for repl_sale start  --------
Node                  Rows     Extra   Missing  Mismatch Processed 
---------------- --------- --------- --------- --------- ---------
group_happy_1            4         0         0         0         4
g_loopback               0         0         4         0         0
 
Error in cmpCol
SQL types do not match for sale_id(103) sale_id(117)
command failed -- Source and Target do not have the same data type (202)

Andreas Legner's profile image
Andreas Legner IBM Champion posted Wed January 21, 2026 06:22 AM  Best Answer

Hi Jacobo,

for some reason the system only allows me another answer to your initial question (rather than a reply to your latest post - please read it in that context.).

Some further testing on my part showed that "cdr sync repl" suffers from that same "SQL types do not match" problem, BUT apparently only if a PK scan of both tables is involved (to match rows and/or determine extra/missing rows).  Such PK scan would be skipped, and also a more efficient replication mechanism chosen, if the intention is to pump a table's content to a target table and the target table is completely empty.

So if your goal is to copy an existing table's content into a new table with deviating (larger) column types within the same server instance (or database even), your approach using loopback ER indeed would be viable and offer nearly zero downtime.

In any case, for bulk transfer, "cdr sync" is preferrable over "cdr check --repair".

HTH,
 Andreas

Art Kagel's profile image
Art Kagel IBM Champion posted Tue January 20, 2026 12:57 PM

I believe that, as you discovered, it is not possible to replicate to a different data type using ER. 

However, you can easily make the copy using simple insert, update, and delete triggers on the source table to handle new rows and changes to existing rows, Then, once at least one row has been inserted into the source table and copied to the target, you can use INSERT INTO <target> SELECT * FROM  <source> WHERE key_col < <lowest serial value inserted by the trigger>;

Or, if the table is very large, you can use my dbcopy or dbmove utility from my utils2_ak package to make the copy and avoid long transaction rollbacks.

Art

Andreas Legner's profile image
Andreas Legner IBM Champion posted Tue January 20, 2026 01:22 PM

Hi Jacobo,

this indeed is a bit of a complex situation and question, and with your local loopback scenario you managed to hit the lighter version of complex ;-)

As your example shows, the replicate definition is not rejected (it would between two separate servers - there the data types strictly must match at replicate definition time, and you'd have to go through a few extra hoops to get replication across differing types).

What's not so obvious from your post is that, after starting this replicate, replication of new rows does work - at least in my quick test, using SQL for testing the result.

What I can confirm from my testing is that "cdr check" indeed fails on this type mismatch (and I don't even get to see this table detailing node, rows, extra and so forth).

What I haven't explored yet what difference it makes that the type differences are on (a) primary key column(s).  I take it that these serial/serial8 are your tables' PKs?

Lastly, what I'd recommend, quite independently from ER, is BIGSERIAL instead of SERIAL8.

So far for now,

 Andreas

Jacobo BC's profile image
Jacobo BC posted Tue January 20, 2026 01:44 PM

Thanks Art and Andreas! You're always there to help. I still need to learn how can I quote someones reply. Andreas, yes, they are the primary key. I'll go old route of duplicating it with triggers.

Related Content