Data loading quesiton

Previouly customer use raw table and external table to do the data loading. It take about 6 minutes for 56452664 row count, the user table has 2 indexes. Now in the HDR enviroment, we can't use raw table, and I did the data loading again, it take about 1 hour and 50 minutes using external table. below are the scripts. create external table ext$tabname sameas $tabname using （ datafiles ('DISK:$dir/data$tabname%r(1..15).unl')', format 'informix', ESCAPE, deluxe; rejectfile "$dir/data/$tabname.rej", maxerrors 100 ); set pdqpriority 30;insert into $tabname select * from ext$tabname; do anybody know any quickly method to do the data loading in HDR env? longer time means the business being stopped more longer. thanks.

Partitioning the table (and possibly the input file) would be an option? HDR is based on the logs. As such we cannot do fast appends and that means lower processing. We managed to workaround this for ER. It would be great to have it on HDR also... Regards. On Tue, Mar 22, 2016 at 3:19 PM, CHUAN LU <luchuan@cn.ibm.com> wrote: > Previouly customer use raw table and external table to do the data > loading. It > take about 6 minutes for 56452664 row count, the user table has 2 indexes. > > Now in the HDR enviroment, we can't use raw table, and I did the data > loading > again, it take about 1 hour and 50 minutes using external table. > > below are the scripts. > > create external table ext$tabname sameas $tabname > using > &#65288; > > datafiles ('DISK:$dir/data$tabname%r(1..15).unl')', > > format 'informix', > > ESCAPE, > > deluxe; > > rejectfile "$dir/data/$tabname.rej", > > maxerrors 100 > > ); > set pdqpriority 30;> insert into $tabname select * from ext$tabname; > > do anybody know any quickly method to do the data loading in HDR env? > longer > time means the business being stopped more longer. > > thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f2a0cee40bd052ea4d69a

One option might be to temporarily convert the secondary into an ER node, load up the data through RAW, convert the table back to standard, and let the two servers sync, then convert the secondary back to HDR. Another, break replication, load the data through RAW mode, convert the table back to standard, reestablish HDR using ifxclone or a restore. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Mar 22, 2016 at 11:26 AM, Fernando Nunes <domusonline@gmail.com> wrote: > Partitioning the table (and possibly the input file) would be an option? > HDR is based on the logs. As such we cannot do fast appends and that means > lower processing. > We managed to workaround this for ER. It would be great to have it on HDR > also... > Regards. > > On Tue, Mar 22, 2016 at 3:19 PM, CHUAN LU <luchuan@cn.ibm.com> wrote: > > > Previouly customer use raw table and external table to do the data > > loading. It > > take about 6 minutes for 56452664 row count, the user table has 2 > indexes. > > > > Now in the HDR enviroment, we can't use raw table, and I did the data > > loading > > again, it take about 1 hour and 50 minutes using external table. > > > > below are the scripts. > > > > create external table ext$tabname sameas $tabname > > using > > &#65288; > > > > datafiles ('DISK:$dir/data$tabname%r(1..15).unl')', > > > > format 'informix', > > > > ESCAPE, > > > > deluxe; > > > > rejectfile "$dir/data/$tabname.rej", > > > > maxerrors 100 > > > > ); > > set pdqpriority 30;> > insert into $tabname select * from ext$tabname; > > > > do anybody know any quickly method to do the data loading in HDR env? > > longer > > time means the business being stopped more longer. > > > > thanks. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a113f2a0cee40bd052ea4d69a > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113ee4fc61f375052ea4eaee

Would you be able to disable HDR, load and reinitialize HDR? 1. Stop HDR 2. Load table using raw and external 3. Convert table from raw to standard 4. Reinitialize HDR Downside: You are without HDR for the duration of the table load and while HDR reinitializes Extra work of reinitializing HDR each time you want to load Upside: Business only has to stop for 6 minutes while the table is loaded, business can continue while HDR is reinitialized Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of CHUAN LU Sent: Tuesday, March 22, 2016 10:19 AM To: ids@iiug.org Subject: Data loading quesiton [36818] Previouly customer use raw table and external table to do the data loading. It take about 6 minutes for 56452664 row count, the user table has 2 indexes. Now in the HDR enviroment, we can't use raw table, and I did the data loading again, it take about 1 hour and 50 minutes using external table. below are the scripts. create external table ext$tabname sameas $tabname using &#65288; datafiles ('DISK:$dir/data$tabname%r(1..15).unl')', format 'informix', ESCAPE, deluxe; rejectfile "$dir/data/$tabname.rej", maxerrors 100 ); set pdqpriority 30;insert into $tabname select * from ext$tabname; do anybody know any quickly method to do the data loading in HDR env? longer time means the business being stopped more longer. thanks. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Before making any suggestions, I'd like to know the following... 1) What is the load time if the load is done using buffered logging into a normal table without HDR? 2) What is the version of Informix? In more recent releases the interface between the logging and HDR was modified a bit, resulting in less of a impact of HDR transfer of the logs to the secondary. If the answer to question 1 is that the load time is acceptable, then I would suggesting setting HDR to use DRINTERVAL set to 0 and then use near sync mode. DRINTERVAL 0HDR_TXN_SCOPE NEAR_SYNC 3) If the load time with #2 is still not acceptable, but the the answer to question #1 is that it is acceptable, then I would consider temporally converting the HDR pair into an RSS pair during the load and then promoting the RSS pair back into an HDR pair once the RSS secondary has caught up. You'd need to let RSS run totally async and also make sure you had enough logs to avoid a log wrap. These two conversions can be done while online. RSS_FLOW_CONTROL -1 Madison Pruet Retired and Loving it On Tuesday, March 22, 2016 10:47 AM, Andrew Ford <andrew@informix-dba.com> wrote: Would you be able to disable HDR, load and reinitialize HDR? 1. Stop HDR 2. Load table using raw and external 3. Convert table from raw to standard 4. Reinitialize HDR Downside: You are without HDR for the duration of the table load and while HDR reinitializes Extra work of reinitializing HDR each time you want to load Upside: Business only has to stop for 6 minutes while the table is loaded, business can continue while HDR is reinitialized Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of CHUAN LU Sent: Tuesday, March 22, 2016 10:19 AM To: ids@iiug.org Subject: Data loading quesiton [36818] Previouly customer use raw table and external table to do the data loading. It take about 6 minutes for 56452664 row count, the user table has 2 indexes. Now in the HDR enviroment, we can't use raw table, and I did the data loading again, it take about 1 hour and 50 minutes using external table. below are the scripts. create external table ext$tabname sameas $tabname using datafiles ('DISK:$dir/data$tabname%r(1..15).unl')', format 'informix', ESCAPE, deluxe; rejectfile "$dir/data/$tabname.rej", maxerrors 100 ); set pdqpriority 30;insert into $tabname select * from ext$tabname; do anybody know any quickly method to do the data loading in HDR env? longer time means the business being stopped more longer. thanks. **************************************************************************** *** 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 will figure out the loading time with standard table without HDR on this thursday or friday. My Ids VERSION is 12.10FC6. currently DRINTERVAL is 0,HDR is near_sync; and I will do the testing according your suggestion, convert it to RSS before loading ,and conver it back to HDR after the loading.

the customer will run the read only application on HDR, so reinitilize the HDR is not workable , the read data is about 60TB, ifxclone take about 9 hours.