Informix

Copying data from a source instance table on one host to a target instance table on a different host

By Jon Ritson posted 20 days ago

  
In the fine manual, an example is provided to copy data from a table in one instance to a table in another instance on the same host:

Copying data from one instance to another using the PIPE option - IBM Documentation

However, in general, when doing data movement across instances this will be to an instance on another host and SPEED is required.

Below is a description of the environment used in this example, along with 3 small files demonstrating a simple approach to facilitate this using pipes and ssh

Source host jj-site1-a
Source instance jj_site1_a_tcp
Target host jj-site2-a
Target instance jj_site2_a_tcp

informix has passwordless access across the hosts
Multiple pipes are used for SPEED
The target table is created as "RAW" for SPEED with the last action altering it to TYPE (STANDARD)

The files all exist on jj-site1-a and are run on jj-site1-a

1. The following script cleans up any remaining pipes, create source and target pipes, then joins the source pipes to the target pipes leaving them running in the background (BEWARE of these background processes on both source and target hosts)
[informix@jj-site1-a rem_pipes]$ cat setup_pipes.sh 
rm /home/informix/rem_pipes/jj_src_pipe[12]; mkfifo /home/informix/rem_pipes/jj_src_pipe1; mkfifo /home/informix/rem_pipes/jj_src_pipe2;
ssh jj-site2-a "rm /home/informix/rem_pipes/jj_tgt_pipe[12]; mkfifo /home/informix/rem_pipes/jj_tgt_pipe1; mkfifo /home/informix/rem_pipes/jj_tgt_pipe2"
cat /home/informix/rem_pipes/jj_src_pipe1 | ssh jj-site2-a "cat - > /home/informix/rem_pipes/jj_tgt_pipe1" &
cat /home/informix/rem_pipes/jj_src_pipe2 | ssh jj-site2-a "cat - > /home/informix/rem_pipes/jj_tgt_pipe2" &
[informix@jj-site1-a rem_pipes]$ ​

2. The following (obviously contrived) creates the source database and table, loads some data, creates the source external table, creates the target database and table and finally the target external table
informix@jj-site1-a rem_pipes]$ cat setup_src_tgt.sql
connect to "@jj_site1_a_tcp";
drop database jj_pipes;
create database jj_pipes in dbspace2k_1 with buffered log;
create table tab1_source (col1 serial, col2 char(20));
insert into tab1_source select 0,"Just some data" from systables;
create external table tab1_source_ext SAMEAS tab1_source
USING (DATAFILES (
	'PIPE:/home/informix/rem_pipes/jj_src_pipe1',
	'PIPE:/home/informix/rem_pipes/jj_src_pipe2'
	));

connect to "@jj_site2_a_tcp";
drop database jj_pipes;
create database jj_pipes in dbspace2k_1 with buffered log;
create raw table tab1_target (col1 serial, col2 char(20));
create external table tab1_target_ext SAMEAS tab1_target
USING (DATAFILES (
	'PIPE:/home/informix/rem_pipes/jj_tgt_pipe1',
	'PIPE:/home/informix/rem_pipes/jj_tgt_pipe2'
	));​

3. Finally, the following performs the INSERT into the target table from the external target table first and runs in the background (waiting on the PIPES) and once the INSERT has completed alters the target table to TYPE STANDARD; then the source INSERT into the external source table is run (putting INTO the pipes so allowing the FIRST INSERT to progress)
[informix@jj-site1-a rem_pipes]$ cat transfer_src_tgt.sh 
echo 'connect to "jj_pipes@jj_site2_a_tcp"; insert into tab1_target select * from tab1_target_ext; alter table tab1_target type (standard);' | dbaccess - - &
echo 'connect to "jj_pipes@jj_site1_a_tcp"; insert into tab1_source_ext select * from tab1_source;' | dbaccess - -​
 
ALWAYS be aware of "dangling cat pipe" commands when testing and kill them off!!

The following is a demonstration of running the above
[informix@jj-site1-a rem_pipes]$ ./setup_pipes.sh 
[informix@jj-site1-a rem_pipes]$ dbaccess - setup_src_tgt.sql 

Connected.
Database dropped.
Database created.
Table created.
71 row(s) inserted.
Table created.
Disconnected.
Connected.
Database dropped.
Database created.
Table created.
Table created.
Disconnected.
[informix@jj-site1-a rem_pipes]$ ./transfer_src_tgt.sh 
Connected.
71 row(s) inserted.
Disconnected.
[informix@jj-site1-a rem_pipes]$ 
Connected.
71 row(s) inserted.
Table altered.
Disconnected.​


Hopefully interesting and possibly useful.

JJ

0 comments
8 views

Permalink