HPL data migration

Is it possible to use HPL to do a table-to-table migration? I need to fragment an 86GB table. I want to rename the current table X to X_old, create a new table X (fragmented) and then use HPL to migrate the data from X_old to X. Is this possible or do I need to Unload/Load the data? If it is possible, how do I set it up in the ipload utility? I've used HPL in the past, but not extensively. Thanks in advance, Wayne Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc.

Set up an unload job to pipe (2x#cpus) set up a load job from pipe (the same ones) and run. Unload to pipes:use mknod -p to create these. Your device elements look like: 'cat > /tmp/pipe.1' Load from pipes:Your device elements look like 'cat /tmp/pipe.1' I do not recommend this approach for a repeatable production process. You can 'lose' a pipe and not get all of your data. I prefer to unload to pipe with a gzip stuck on the end: (gzip - > file.gz) and then load from those gzip files. gunzip -c file.gz > /tmp/pipe.1 That way you have an intermediate file on disk. Unloading to gzip is about 7x faster than unload to disk. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Zablatzky, .... Sent: Saturday, January 07, 2006 12:12 PM To: ids@iiug.org Subject: HPL data migration [6180] Is it possible to use HPL to do a table-to-table migration? I need to fragment an 86GB table. I want to rename the current table X to X_old, create a new table X (fragmented) and then use HPL to migrate the data from X_old to X. Is this possible or do I need to Unload/Load the data? If it is possible, how do I set it up in the ipload utility? I've used HPL in the past, but not extensively. Thanks in advance, Wayne Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks for the fast reply, but that still involves a 'disk' file. Scratch space is hard to come by. I have been testing with: insert into X select * from X_old. This takes ~6 hours to complete. I've unloaded this table a few years ago with HPL and it took ~2 1/2 hours. Another 2 1/2 to load. Much more data now. I was kind of hoping that I could use the HPL as a direct copy utility. Wayne -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jack Parker Sent: Saturday, January 07, 2006 12:25 PM To: ids@iiug.org Subject: RE: HPL data migration [6181] Set up an unload job to pipe (2x#cpus) set up a load job from pipe (the same ones) and run. Unload to pipes:use mknod -p to create these. Your device elements look like: 'cat > /tmp/pipe.1' Load from pipes:Your device elements look like 'cat /tmp/pipe.1' I do not recommend this approach for a repeatable production process. You can 'lose' a pipe and not get all of your data. I prefer to unload to pipe with a gzip stuck on the end: (gzip - > file.gz) and then load from those gzip files. gunzip -c file.gz > /tmp/pipe.1 That way you have an intermediate file on disk. Unloading to gzip is about 7x faster than unload to disk. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Zablatzky, .... Sent: Saturday, January 07, 2006 12:12 PM To: ids@iiug.org Subject: HPL data migration [6180] Is it possible to use HPL to do a table-to-table migration? I need to fragment an 86GB table. I want to rename the current table X to X_old, create a new table X (fragmented) and then use HPL to migrate the data from X_old to X. Is this possible or do I need to Unload/Load the data? If it is possible, how do I set it up in the ipload utility? I've used HPL in the past, but not extensively. Thanks in advance, Wayne Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc.

Hi, Maybe you can use the "alter fragment on table" statement to change the fragmentation in place. Just a thought. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Zablatzky, .... Sent: Saturday, January 07, 2006 9:56 PM To: ids@iiug.org Subject: RE: HPL data migration [6182] Thanks for the fast reply, but that still involves a 'disk' file. Scratch space is hard to come by. I have been testing with: insert into X select * from X_old. This takes ~6 hours to complete. I've unloaded this table a few years ago with HPL and it took ~2 1/2 hours. Another 2 1/2 to load. Much more data now. I was kind of hoping that I could use the HPL as a direct copy utility. Wayne -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jack Parker Sent: Saturday, January 07, 2006 12:25 PM To: ids@iiug.org Subject: RE: HPL data migration [6181] Set up an unload job to pipe (2x#cpus) set up a load job from pipe (the same ones) and run. Unload to pipes:use mknod -p to create these. Your device elements look like: 'cat > /tmp/pipe.1' Load from pipes:Your device elements look like 'cat /tmp/pipe.1' I do not recommend this approach for a repeatable production process. You can 'lose' a pipe and not get all of your data. I prefer to unload to pipe with a gzip stuck on the end: (gzip - > file.gz) and then load from those gzip files. gunzip -c file.gz > /tmp/pipe.1 That way you have an intermediate file on disk. Unloading to gzip is about 7x faster than unload to disk. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Zablatzky, .... Sent: Saturday, January 07, 2006 12:12 PM To: ids@iiug.org Subject: HPL data migration [6180] Is it possible to use HPL to do a table-to-table migration? I need to fragment an 86GB table. I want to rename the current table X to X_old, create a new table X (fragmented) and then use HPL to migrate the data from X_old to X. Is this possible or do I need to Unload/Load the data? If it is possible, how do I set it up in the ipload utility? I've used HPL in the past, but not extensively. Thanks in advance, Wayne Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Yes, you can use HPL as a direct copy utility. First your output and input should be defined as pipes. There, you are obligated to put a command. If you are in a unix environment you can put just "cat". Then, to retrive the data and load it in a single instruction just execute: onpload -p proj_name -j job_unload_name -fu | onpload -p proj_name -j job_load_name -fl and that's it. I hope this helps. Regards, Víctor Fabián Miramontes DBA Proyecto SAP Cencosud S.A. TE: 54-11-4733-1000 int. 4158 >>> Wayne.Zablatzky@ubs.com 07/01/06 17:55 >>> Thanks for the fast reply, but that still involves a 'disk' file. Scratch space is hard to come by. I have been testing with: insert into X select * from X_old. This takes ~6 hours to complete. I've unloaded this table a few years ago with HPL and it took ~2 1/2 hours. Another 2 1/2 to load. Much more data now. I was kind of hoping that I could use the HPL as a direct copy utility. Wayne -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jack Parker Sent: Saturday, January 07, 2006 12:25 PM To: ids@iiug.org Subject: RE: HPL data migration [6181] Set up an unload job to pipe (2x#cpus) set up a load job from pipe (the same ones) and run. Unload to pipes:use mknod -p to create these. Your device elements look like: 'cat > /tmp/pipe.1' Load from pipes:Your device elements look like 'cat /tmp/pipe.1' I do not recommend this approach for a repeatable production process. You can 'lose' a pipe and not get all of your data. I prefer to unload to pipe with a gzip stuck on the end: (gzip - > file.gz) and then load from those gzip files. gunzip -c file.gz > /tmp/pipe.1 That way you have an intermediate file on disk. Unloading to gzip is about 7x faster than unload to disk. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Zablatzky, .... Sent: Saturday, January 07, 2006 12:12 PM To: ids@iiug.org Subject: HPL data migration [6180] Is it possible to use HPL to do a table-to-table migration? I need to fragment an 86GB table. I want to rename the current table X to X_old, create a new table X (fragmented) and then use HPL to migrate the data from X_old to X. Is this possible or do I need to Unload/Load the data? If it is possible, how do I set it up in the ipload utility? I've used HPL in the past, but not extensively. Thanks in advance, Wayne Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

By all means you can, but be sure to check the results, ensure that your record count before is the same as your record count afterwards. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Zablatzky, .... Sent: Saturday, January 07, 2006 3:56 PM To: ids@iiug.org Subject: RE: HPL data migration [6182] Thanks for the fast reply, but that still involves a 'disk' file. Scratch space is hard to come by. I have been testing with: insert into X select * from X_old. This takes ~6 hours to complete. I've unloaded this table a few years ago with HPL and it took ~2 1/2 hours. Another 2 1/2 to load. Much more data now. I was kind of hoping that I could use the HPL as a direct copy utility. Wayne -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jack Parker Sent: Saturday, January 07, 2006 12:25 PM To: ids@iiug.org Subject: RE: HPL data migration [6181] Set up an unload job to pipe (2x#cpus) set up a load job from pipe (the same ones) and run. Unload to pipes:use mknod -p to create these. Your device elements look like: 'cat > /tmp/pipe.1' Load from pipes:Your device elements look like 'cat /tmp/pipe.1' I do not recommend this approach for a repeatable production process. You can 'lose' a pipe and not get all of your data. I prefer to unload to pipe with a gzip stuck on the end: (gzip - > file.gz) and then load from those gzip files. gunzip -c file.gz > /tmp/pipe.1 That way you have an intermediate file on disk. Unloading to gzip is about 7x faster than unload to disk. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Zablatzky, .... Sent: Saturday, January 07, 2006 12:12 PM To: ids@iiug.org Subject: HPL data migration [6180] Is it possible to use HPL to do a table-to-table migration? I need to fragment an 86GB table. I want to rename the current table X to X_old, create a new table X (fragmented) and then use HPL to migrate the data from X_old to X. Is this possible or do I need to Unload/Load the data? If it is possible, how do I set it up in the ipload utility? I've used HPL in the past, but not extensively. Thanks in advance, Wayne Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Victor, I'll give that a try..... Thanks, Wayne -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Victor Mira.... Sent: Saturday, January 07, 2006 4:19 PM To: ids@iiug.org Subject: RE: HPL data migration [6184] Yes, you can use HPL as a direct copy utility. First your output and input should be defined as pipes. There, you are obligated to put a command. If you are in a unix environment you can put just "cat". Then, to retrive the data and load it in a single instruction just execute: onpload -p proj_name -j job_unload_name -fu | onpload -p proj_name -j job_load_name -fl and that's it. I hope this helps. Regards, Víctor Fabián Miramontes DBA Proyecto SAP Cencosud S.A. TE: 54-11-4733-1000 int. 4158 >>> Wayne.Zablatzky@ubs.com 07/01/06 17:55 >>> Thanks for the fast reply, but that still involves a 'disk' file. Scratch space is hard to come by. I have been testing with: insert into X select * from X_old. This takes ~6 hours to complete. I've unloaded this table a few years ago with HPL and it took ~2 1/2 hours. Another 2 1/2 to load. Much more data now. I was kind of hoping that I could use the HPL as a direct copy utility. Wayne -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jack Parker Sent: Saturday, January 07, 2006 12:25 PM To: ids@iiug.org Subject: RE: HPL data migration [6181] Set up an unload job to pipe (2x#cpus) set up a load job from pipe (the same ones) and run. Unload to pipes:use mknod -p to create these. Your device elements look like: 'cat > /tmp/pipe.1' Load from pipes:Your device elements look like 'cat /tmp/pipe.1' I do not recommend this approach for a repeatable production process. You can 'lose' a pipe and not get all of your data. I prefer to unload to pipe with a gzip stuck on the end: (gzip - > file.gz) and then load from those gzip files. gunzip -c file.gz > /tmp/pipe.1 That way you have an intermediate file on disk. Unloading to gzip is about 7x faster than unload to disk. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Zablatzky, .... Sent: Saturday, January 07, 2006 12:12 PM To: ids@iiug.org Subject: HPL data migration [6180] Is it possible to use HPL to do a table-to-table migration? I need to fragment an 86GB table. I want to rename the current table X to X_old, create a new table X (fragmented) and then use HPL to migrate the data from X_old to X. Is this possible or do I need to Unload/Load the data? If it is possible, how do I set it up in the ipload utility? I've used HPL in the past, but not extensively. Thanks in advance, Wayne Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc.

You can do it using pipes. Also, look into my dbcopy utility. It is not as fast as HPL, but is faster than INSERT INTO...SELECT FROM ... Dbcopy is in the utils2_ak package in the IIUG Software Repository. You could also refragment the table in place. Art S. Kagel ----- Original Message ----- From: .... Zablatzky <ids@iiug.org> At: 1/ 7 12:11 Is it possible to use HPL to do a table-to-table migration? I need to fragment an 86GB table. I want to rename the current table X to X_old, create a new table X (fragmented) and then use HPL to migrate the data from X_old to X. Is this possible or do I need to Unload/Load the data? If it is possible, how do I set it up in the ipload utility? I've used HPL in the past, but not extensively. Thanks in advance, Wayne Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Since Informix gave us the chance of creating raw tables I've been using HPL less and less for this kind of tasks. The steps would be - Create a target raw table. - Set pdqpriority high enough to have parallel threads. - Do a insert into select from - Alter table to type (standard). - then Create indexes if needed - Take an archive before turning it to production. I've not used Art Kagel program, but in all my tests speed this solution was equivalent to an express load in HPL. No buffer is used in the load, so no long transaction is involved either. To me it works like a charm and scripting it is straightforward. Hospitably yours, Walter Milan DBA -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART KAGEL, .... Sent: Monday, January 09, 2006 8:51 AM To: ids@iiug.org Subject: Re: HPL data migration [6188] You can do it using pipes. Also, look into my dbcopy utility. It is not as fast as HPL, but is faster than INSERT INTO...SELECT FROM ... Dbcopy is in the utils2_ak package in the IIUG Software Repository. You could also refragment the table in place. Art S. Kagel ----- Original Message ----- From: .... Zablatzky <ids@iiug.org> At: 1/ 7 12:11 Is it possible to use HPL to do a table-to-table migration? I need to fragment an 86GB table. I want to rename the current table X to X_old, create a new table X (fragmented) and then use HPL to migrate the data from X_old to X. Is this possible or do I need to Unload/Load the data? If it is possible, how do I set it up in the ipload utility? I've used HPL in the past, but not extensively. Thanks in advance, Wayne Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

I did the PIPE test and it worked wonderfully. It took 31 minutes to fragment the ~86GB table (sure beats 6 hours). I still need to add 13 indexes back, update statistics and take the necessary Level 0 backup, which was a given. Thanks for all your help, Wayne -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Walter Milan Sent: Monday, January 09, 2006 12:12 PM To: ids@iiug.org Subject: RE: HPL data migration [6191] Since Informix gave us the chance of creating raw tables I've been using HPL less and less for this kind of tasks. The steps would be - Create a target raw table. - Set pdqpriority high enough to have parallel threads. - Do a insert into select from - Alter table to type (standard). - then Create indexes if needed - Take an archive before turning it to production. I've not used Art Kagel program, but in all my tests speed this solution was equivalent to an express load in HPL. No buffer is used in the load, so no long transaction is involved either. To me it works like a charm and scripting it is straightforward. Hospitably yours, Walter Milan DBA -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART KAGEL, .... Sent: Monday, January 09, 2006 8:51 AM To: ids@iiug.org Subject: Re: HPL data migration [6188] You can do it using pipes. Also, look into my dbcopy utility. It is not as fast as HPL, but is faster than INSERT INTO...SELECT FROM ... Dbcopy is in the utils2_ak package in the IIUG Software Repository. You could also refragment the table in place. Art S. Kagel ----- Original Message ----- From: .... Zablatzky <ids@iiug.org> At: 1/ 7 12:11 Is it possible to use HPL to do a table-to-table migration? I need to fragment an 86GB table. I want to rename the current table X to X_old, create a new table X (fragmented) and then use HPL to migrate the data from X_old to X. Is this possible or do I need to Unload/Load the data? If it is possible, how do I set it up in the ipload utility? I've used HPL in the past, but not extensively. Thanks in advance, Wayne Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc.

Sorry for not getting back sooner. It's been on of those weeks. The named pipes worked like a charm. It took 31 minutes to migrate the data portion of the table. Indexes too another 6 hours to rebuild. The required level 0 after the HPL was not an issue since we (DBAs) required a pre/post level 0 anyway. Thanks for all you help, Wayne -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART KAGEL, .... Sent: Monday, January 09, 2006 9:51 AM To: ids@iiug.org Subject: Re: HPL data migration [6188] You can do it using pipes. Also, look into my dbcopy utility. It is not as fast as HPL, but is faster than INSERT INTO...SELECT FROM ... Dbcopy is in the utils2_ak package in the IIUG Software Repository. You could also refragment the table in place. Art S. Kagel ----- Original Message ----- From: .... Zablatzky <ids@iiug.org> At: 1/ 7 12:11 Is it possible to use HPL to do a table-to-table migration? I need to fragment an 86GB table. I want to rename the current table X to X_old, create a new table X (fragmented) and then use HPL to migrate the data from X_old to X. Is this possible or do I need to Unload/Load the data? If it is possible, how do I set it up in the ipload utility? I've used HPL in the past, but not extensively. Thanks in advance, Wayne Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you. UBS Financial Services Inc. UBS International Inc.