Unanswered: What is the efficient way to transfer data from one database to another
What is the most efficient way to unload and load data from one database to another where both database at in different server.
For small amounts of records, I always use unload and load but what is the faster way and will reduce database performance if I want to unload the whole table of data? If using unload method, I will take hours to unload.
1) If both servers have the same OS and compatible IDS versions ( meaning more or less same versions, admitting some slightly different versions ), you can use the couple onunload / onload with will unload your IFMX datapages in binary format ( not spending CPU time "thinking" ), and load those binary IFMX pages on the target server.
This is the fastest and simplest way if conditions are met.
2) if you are happy enough to have IDS >= 11.50 xC6, you are entitled to use EXTERNAL tables built on your ascii unloaded files. you won't unload faster because you will unload the same way you use to, but load time will be way shorter!
In fact, EXTERNAL TABLES can replace in most cases a complex ETL, they are delivered with IDS starting with 11.50 xC6, are very simple to use and extremely powerful.
This technology has been taken from informix XPS, the Datawarehouse version of Informix Dynamic server, and included into the regular version of IDS.
you can see an example very similar to what you want to do in this article, written in french, but google translate does great things.
3) HPL ( the parallel loader ) can also do the job if your archicture is really mutithreaded, but it is somewhat complex to set up, and is often beaten by using the external tables...