Re: Moving data from one server to another

Krishnakn There are many approaches you can use. Here are 2 more I can think of: 1) Your approach (1). In 4GL at least, you could prepare a statement such as "UNLOAD TO " || filename || "SELECT * FROM tablename WHERE...". There is a trick to it, since this is not true SQL. Altenatively you could simply use system() (or RUN in 4GL) to do this. 2) Your approach (2), also possible in ESQL/C or 4GL provided you set up commectivity between the 2 instances. But probably not very fast. 3) The above 2 approaches will not take care of resizing your production tables after the delete and will not release space freed. For that I would suggest unloading the tables to two sets of files, one that you want to keep active and the other that you want to archive, and recreate all the tables in production with the correct extent sizes based on the number and size of your records, and loading the data back from the keep active set of files. You can load the data in the archive set into your archive database on a different server. 4) Since you know the criteria that you will use to archive the records, you could define a fragmentation strategy for tables you need to archive and unlink specific fragments from your production database, and link them to your archive database. I have never tried this, so I dont have details, but it is all clearly laid out in the admin manual. If you do it correctly, this is probably the best and quickest method. There are some very informative posts by Art S Kagel on this subject in the NG archives, so you could perhaps refer to these. HTH Sujit krishnakn@my-dejanews.com on 04/08/99 08:20:54 AM Please respond to krishnakn@my-dejanews.com To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: Moving data from one server to another I'm trying to develop some kind of archival application. Like, when there is too much data in production db, not being used at all, the application should move those data from production db to arhival db. Both the db can be in different servers. What approach do U think I must use to move the data???? Approach 1: Move to txt file [using unload] and ftp and load the data from txt file to archival db tables[using load command]. But I cant use these commands in Stored Procedure nor in ESQL-C. Approach 2: INSERT into archive_db@server:tablename SELECT * FROM productiondb.table ...... Or Is there anyother way of doing this ?????????? Please help me out ......... -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own