Problem with data transfer between tables.

Hi all, I'm using IDS 9.4HC7 on HP-UX 11i. I'm facing the following problem while trying to transfer data from one table to another. I have to transfer all the contents (250 million rows) of a table to another one with the same dbschema but different name: >cat procedure.sh DIR=/var/tmp mkfifo -m 777 $DIR/unloadfile.unl echo "unload to $DIR/unloadfile.unl select * from table_A;" | dbaccess db & dbload -d db -c $DIR/dbload_cf -l $DIR/dbload.errorlog -n 10000 > $DIR/dbload.log & > cat dbload_cf FILE “/var/tmp/unloadfile.unl” DELIMITER “|” 22; INSERT INTO table_B; The procedure finishes after 20 hours without any errors on log files but at the end the table_B has only 147 million rows (table_A has 150 million rows). Is there any potential explenation for this? Please let me know if you need more information.

I have seen this sort of thing happen when using pipes, for some reason one of the pipes (in your case the pipe) goes out or does not keep up and the data or portions of the data from that pipe is lost. This is the primary reason that I like to actually land the data on disk and check it before reloading it. I would use HPL instead for such a job, your run time will look more like 2 hours than 20 hours. See the Load FAQ for a primer. http://www.artentech.com/downloads.htm cheers j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of KOSTAS ZERZELOPOULOS Sent: Wednesday, February 06, 2008 7:33 AM To: ids@iiug.org Subject: Problem with data transfer between tables. [11175] Hi all, I'm using IDS 9.4HC7 on HP-UX 11i. I'm facing the following problem while trying to transfer data from one table to another. I have to transfer all the contents (250 million rows) of a table to another one with the same dbschema but different name: >cat procedure.sh DIR=/var/tmp mkfifo -m 777 $DIR/unloadfile.unl echo "unload to $DIR/unloadfile.unl select * from table_A;" | dbaccess db & dbload -d db -c $DIR/dbload_cf -l $DIR/dbload.errorlog -n 10000 > $DIR/dbload.log & > cat dbload_cf FILE /var/tmp/unloadfile.unl DELIMITER | 22; INSERT INTO table_B; The procedure finishes after 20 hours without any errors on log files but at the end the table_B has only 147 million rows (table_A has 150 million rows). Is there any potential explenation for this? Please let me know if you need more information. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks for the reply Jack. Pipe file could be the problem. But I tried the same procedure 5 times up to now (testing before real implementation) and all of them were succesful. HPL can't be used since there is not enough disk space to store the unloaded data even if they are compressed (40 Gig). Anyway I will try once againg with pipe file transfer.

KOSTAS ZERZELOPOULOS wrote: > Thanks for the reply Jack. > Pipe file could be the problem. But I tried the same procedure 5 times up to > now (testing before real implementation) and all of them were succesful. > HPL can't be used since there is not enough disk space to store the unloaded > data even if they are compressed (40 Gig). > Anyway I will try once againg with pipe file transfer. Kostas, Just download my package, utils2_ak, from the IIUG Software Repository and compile the package or at least the dbcopy.ec utility. Dbcopy can copy data between tables at least as fast as you can load it with dbload and with mostly the same features including: - Configurable partial transactions. - Configurable internal protocols (the -F option enables FETCH ARRAY data retrieval which improves the speed significantly). - Configurable PUT cursor cache size. - Rows in error output to a log file in UNLOAD/LOAD/DBLOAD format. - Optionally ignore first N errors. - Optionally ignore duplicate row/key errors without logging them. No pipes, no problem. Only one caviat to remember, if the source and target tables are in databases in the same server instance, you have to use a TCP or strpip connection as the hostname for at least one of the connections (dbcopy uses separate connections for the source table reads and target table writes - much faster than using a single connection) as you can only have one shared memory per process. Art S. Kagel ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========