So I've been working on a Proof of Concept with the new Informix Warehouse Accelerator. Part of that is getting data from source systems , and often those source systems are on another Database system. When doing work with that you inevitably use an ETL tool of some sort, and the customer I'm working with uses IBM Datatastage.
I'm using an older version of Datastage, and the ODBC driver is slow. SO I was looking for a quicker way to load, while at the same time not taking up any space, except inside the database. So I wanted to share the method used:
- on your Datastage server, create a pipe
- Write your datastage job to write the output of the job to the pipe.
- On your informix server, create a pipe
- Get your pipe2 to write to pipe2
ssh informix@dsserver 'cat /tmp/pipe1' > pipe2
- Create your external table with the pipe
create external table et_<table_name> sameas <table_name>
- Kick off your datastage job
- Load the external table
insert into <table_name> select * from et_<table_name>