move data between 2 instance

Hi All, We have 2 databases sitting on same machine. One is our production database, another one is our archive database. We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" ) to move the old data to archive database. It was running well before. The original version is V7.31UD10. However after I migrate both database to new version V115UC3. We encounter the problem. Normally this archive procedure would only takes around 1 hour to finished, but now it runs very very slow. Seems to me it will take more several days to finish. I have to revert the archive database to old version IDS7.31UD10, then re-run the procedure. Every thing backs to normal. Anybody has idea why and what causing this problem? Thanks for any help. Denny ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message électronique pourrait contenir des informations privilégiées et confidentielles. Si vous n'en êtes pas le récipiendaire prévu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le présent message. Si vous l'avez reçu par erreur, veuillez prévenir l'expéditeur par courriel, puis effacer ce message et en détruire toute copie. Le courrier électronique n'est pas garanti sécuritaire ni exempt d'erreurs. Les messages pourraient être interceptés, corrompus, égarés, retardés ou contaminés par des virus. L'expéditeur n'est pas responsable de ces risques.

2009/3/23 Guo, Denny <DGuo@livingstonintl.com>: > Hi All, > > We have 2 databases sitting on same machine. One is our production database, > another one is our archive database. > We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" ) > to move the old data to archive database. It was running well before. > The original version is V7.31UD10. However after I migrate both database to > new version V115UC3. We encounter the problem. Normally this archive procedure > would only takes around 1 hour to finished, but now it runs very very slow. > Seems to me it will take more several days to finish. > I have to revert the archive database to old version IDS7.31UD10, then re-run > the procedure. Every thing backs to normal. > Anybody has idea why and what causing this problem? > > Thanks for any help. > > Denny > > ******************************************* > > The information contained in this e-mail message may > contain privileged and confidential information. > If you are not the intended recipient, you are > hereby notified that any review, dissemination, > distribution or duplication of this communication > is strictly prohibited. If you have received this > message in error, please notify the sender by return > e-mail, delete this message and destroy any copies. > Internet e- mail is not guaranteed to be secure or > error-free. Messages could be intercepted, corrupted, > lost, arrive late or contain viruses. > The sender will not be liable for > these risks. > > ******************************************* > Ce message électronique pourrait contenir des informations > privilégiées et confidentielles. Si vous n'en êtes pas le > récipiendaire prévu, nous vous signalons qu'il est strictement > interdit d'examiner, de diffuser, de distribuer et de reproduire le > présent message. Si vous l'avez reçu par erreur, veuillez prévenir > l'expéditeur par courriel, puis effacer ce message et en détruire > toute copie. Le courrier électronique n'est pas garanti sécuritaire > ni exempt d'erreurs. Les messages pourraient être interceptés, > corrompus, égarés, retardés ou contaminés par des virus. > L'expéditeur n'est pas responsable de ces risques. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Denny Did you follow the update process to the letter, including UPDATE STATISTICS 'exactly' as recommended I have found the later you go with IDS versions the more sensitive they are to stats being updated precisely as specified (at least once), near enough is not good enough. What do query plans show? Keith

Hi Keith, Yes, I do follow the upgrade procedure. Run update statistics, run oncheck to make sure DB is OK. Thanks, Denny -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Keith Simmons Sent: Monday, March 23, 2009 11:14 AM To: ids@iiug.org Subject: Re: move data between 2 instance [15281] 2009/3/23 Guo, Denny <DGuo@livingstonintl.com>: > Hi All, > > We have 2 databases sitting on same machine. One is our production database, > another one is our archive database. > We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" ) > to move the old data to archive database. It was running well before. > The original version is V7.31UD10. However after I migrate both database to > new version V115UC3. We encounter the problem. Normally this archive procedure > would only takes around 1 hour to finished, but now it runs very very slow. > Seems to me it will take more several days to finish. > I have to revert the archive database to old version IDS7.31UD10, then re-run > the procedure. Every thing backs to normal. > Anybody has idea why and what causing this problem? > > Thanks for any help. > > Denny > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Denny Did you follow the update process to the letter, including UPDATE STATISTICS 'exactly' as recommended I have found the later you go with IDS versions the more sensitive they are to stats being updated precisely as specified (at least once), near enough is not good enough. What do query plans show? Keith ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message électronique pourrait contenir des informations privilégiées et confidentielles. Si vous n'en êtes pas le récipiendaire prévu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le présent message. Si vous l'avez reçu par erreur, veuillez prévenir l'expéditeur par courriel, puis effacer ce message et en détruire toute copie. Le courrier électronique n'est pas garanti sécuritaire ni exempt d'erreurs. Les messages pourraient être interceptés, corrompus, égarés, retardés ou contaminés par des virus. L'expéditeur n'est pas responsable de ces risques.

Definitely update statistics -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Guo, Denny Sent: 23 March 2009 05:20 PM To: ids@iiug.org Subject: RE: move data between 2 instance [15283] Hi Keith, Yes, I do follow the upgrade procedure. Run update statistics, run oncheck to make sure DB is OK. Thanks, Denny -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Keith Simmons Sent: Monday, March 23, 2009 11:14 AM To: ids@iiug.org Subject: Re: move data between 2 instance [15281] 2009/3/23 Guo, Denny <DGuo@livingstonintl.com>: > Hi All, > > We have 2 databases sitting on same machine. One is our production database, > another one is our archive database. > We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" ) > to move the old data to archive database. It was running well before. > The original version is V7.31running well before. >UD10. However after I migrate both database to > new version V115UC3. We encounter the problem. Normally this archive procedure > would only takes around 1 hour to fve procedure >inished, but now it runs very very slow. > Seems to me it will take more several days to finish. > I have to rwill take more several days to finish. >evert the archive database to old version IDS7.31UD10, then re-run > the procedure. Every thing backs to norma re-run >l. > Anybody has idea why and what causing this problem >? > > Thanks for any help. > > Denny > ****** > > Thanks for any help. > > Denny >************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Denny Didnse in the discussion forum. > > you follow the update process to the letter, including UPDATE STATISTICS 'exactly' as recommended I have found the later you go with IDS versions the more sensitive they are to stats being updated precisely as specified (at least once), near enough is not good enough. What do query plans show? Keith ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message électronique pourrait contenir des informations privilégiées et confidentielles. Si vous n'en êtes pas le récipiendaire prévu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le présent message. Si vous l'avez reçu par erreur, veuillez prévenir l'expéditeur par courriel, puis effacer ce message et en détruire toute copie. Le courrier électronique n'est pas garanti sécuritaire ni exempt d'erreurs. Les messages pourraient être interceptés, corrompus, égarés, retardés ou contaminés par des virus. L'expéditeur n'est pas responsable de ces risques. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ================== Please read our Email Disclaimer : http://www.thefuelgroup.com/disclaimer.html

Perhaps with the new version (11.5?) it's "automagically" doing update stats on the target/archive box while the data is being loaded, slowing it down? Don't have any IDS 11.1 or 11.5 so just guessing. Bob ----- Original Message ----- From: "Denny Guo" <DGuo@livingstonintl.com> To: ids@iiug.org Sent: Monday, March 23, 2009 11:06:25 AM GMT -05:00 US/Canada Eastern Subject: move data between 2 instance [15280] Hi All, We have 2 databases sitting on same machine. One is our production database, another one is our archive database. We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" ) to move the old data to archive database. It was running well before. The original version is V7.31UD10. However after I migrate both database to new version V115UC3. We encounter the problem. Normally this archive procedure would only takes around 1 hour to finished, but now it runs very very slow. Seems to me it will take more several days to finish. I have to revert the archive database to old version IDS7.31UD10, then re-run the procedure. Every thing backs to normal. Anybody has idea why and what causing this problem? Thanks for any help. Denny *******************************************

After an upgrade in place, you MUST MUST MUST run: - update statistics LOW DROP DISTRIBUTIONS; for all tables to get rid of data distributions on disk that may be in an incorrect format as the format changes even between minor versions - absolutely the format changed from 7.31 to 11.50 at least three times that I know about. - Rerun the normal suite of update statistics commands to restore the correct distributions - Finally recompile every stored procedure by running UPDATE STATISTICS FOR PROCEDURE ...; for every procedure on the system. After that see how this function performs. Note that my dostats utility can take care of this for you automatically and my dbcopy utility is a bit faster copying between servers than SELECT FROM ... INSERT INTO ... statement that your procedure uses when you enable the -F option. Both utilities are included in the package utils2_ak which you can download from the Oninit web site (www.oninit.com/utilities) or the IIUG Software Repository (www.iiug.org/software). Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Mon, Mar 23, 2009 at 11:06 AM, Guo, Denny <DGuo@livingstonintl.com>wrote: > Hi All, > > We have 2 databases sitting on same machine. One is our production > database, > another one is our archive database. > We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" > ) > to move the old data to archive database. It was running well before. > The original version is V7.31UD10. However after I migrate both database to > new version V115UC3. We encounter the problem. Normally this archive > procedure > would only takes around 1 hour to finished, but now it runs very very slow. > Seems to me it will take more several days to finish. > I have to revert the archive database to old version IDS7.31UD10, then > re-run > the procedure. Every thing backs to normal. > Anybody has idea why and what causing this problem? > > Thanks for any help. > > Denny > > ******************************************* > > The information contained in this e-mail message may > contain privileged and confidential information. > If you are not the intended recipient, you are > hereby notified that any review, dissemination, > distribution or duplication of this communication > is strictly prohibited. If you have received this > message in error, please notify the sender by return > e-mail, delete this message and destroy any copies. > Internet e- mail is not guaranteed to be secure or > error-free. Messages could be intercepted, corrupted, > lost, arrive late or contain viruses. > The sender will not be liable for > these risks. > > ******************************************* > Ce message électronique pourrait contenir des informations > privilégiées et confidentielles. Si vous n'en êtes pas le > récipiendaire prévu, nous vous signalons qu'il est strictement > interdit d'examiner, de diffuser, de distribuer et de reproduire le > présent message. Si vous l'avez reçu par erreur, veuillez prévenir > l'expéditeur par courriel, puis effacer ce message et en détruire > toute copie. Le courrier électronique n'est pas garanti sécuritaire > ni exempt d'erreurs. Les messages pourraient être interceptés, > corrompus, égarés, retardés ou contaminés par des virus. > L'expéditeur n'est pas responsable de ces risques. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016364ee01ca3dc160465cb7e26

Thanks Art, I will try your method. The only thing I did not run is "update statistics LOW DROP DISTRIBUTIONS". I do run normal suite update statistics and re-compile the procedure. Regards, Denny -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Monday, March 23, 2009 12:10 PM To: ids@iiug.org Subject: Re: move data between 2 instance [15289] After an upgrade in place, you MUST MUST MUST run: - update statistics LOW DROP DISTRIBUTIONS; for all tables to get rid of data distributions on disk that may be in an incorrect format as the format changes even between minor versions - absolutely the format changed from 7.31 to 11.50 at least three times that I know about. - Rerun the normal suite of update statistics commands to restore the correct distributions - Finally recompile every stored procedure by running UPDATE STATISTICS FOR PROCEDURE ...; for every procedure on the system. After that see how this function performs. Note that my dostats utility can take care of this for you automatically and my dbcopy utility is a bit faster copying between servers than SELECT FROM ... INSERT INTO ... statement that your procedure uses when you enable the -F option. Both utilities are included in the package utils2_ak which you can download from the Oninit web site (www.oninit.com/utilities) or the IIUG Software Repository (www.iiug.org/software). Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Mon, Mar 23, 2009 at 11:06 AM, Guo, Denny <DGuo@livingstonintl.com>wrote: > Hi All, > > We have 2 databases sitting on same machine. One is our production > database, > another one is our archive database. > We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" > ) > to move the old data to archive database. It was running well before. > The original version is V7.31UD10. However after I migrate both database to > new version V115UC3. We encounter the problem. Normally this archive > procedure > would only takes around 1 hour to finished, but now it runs very very slow. > Seems to me it will take more several days to finish. > I have to revert the archive database to old version IDS7.31UD10, then > re-run > the procedure. Every thing backs to normal. > Anybody has idea why and what causing this problem? > > Thanks for any help. > > Denny > ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message électronique pourrait contenir des informations privilégiées et confidentielles. Si vous n'en êtes pas le récipiendaire prévu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le présent message. Si vous l'avez reçu par erreur, veuillez prévenir l'expéditeur par courriel, puis effacer ce message et en détruire toute copie. Le courrier électronique n'est pas garanti sécuritaire ni exempt d'erreurs. Les messages pourraient être interceptés, corrompus, égarés, retardés ou contaminés par des virus. L'expéditeur n'est pas responsable de ces risques.

Hmmmmm. You don't say? Bob ----- Original Message ----- From: "Denny Guo" <DGuo@livingstonintl.com> To: ids@iiug.org Sent: Monday, March 23, 2009 12:29:12 PM GMT -05:00 US/Canada Eastern Subject: RE: move data between 2 instance [15290] Thanks Art, I will try your method. The only thing I did not run is "update statistics LOW DROP DISTRIBUTIONS". I do run normal suite update statistics and re-compile the procedure. Regards, Denny -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Monday, March 23, 2009 12:10 PM To: ids@iiug.org Subject: Re: move data between 2 instance [15289] After an upgrade in place, you MUST MUST MUST run: - update statistics LOW DROP DISTRIBUTIONS; for all tables to get rid of data distributions on disk that may be in an incorrect format as the format changes even between minor versions - absolutely the format changed from 7.31 to 11.50 at least three times that I know about. - Rerun the normal suite of update statistics commands to restore the correct distributions - Finally recompile every stored procedure by running UPDATE STATISTICS FOR PROCEDURE ...; for every procedure on the system. After that see how this function performs. Note that my dostats utility can take care of this for you automatically and my dbcopy utility is a bit faster copying between servers than SELECT FROM ... INSERT INTO ... statement that your procedure uses when you enable the -F option. Both utilities are included in the package utils2_ak which you can download from the Oninit web site (www.oninit.com/utilities) or the IIUG Software Repository (www.iiug.org/software). Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Mon, Mar 23, 2009 at 11:06 AM, Guo, Denny <DGuo@livingstonintl.com>wrote: > Hi All, > > We have 2 databases sitting on same machine. One is our production > database, > another one is our archive database. > We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" > ) > to move the old data to archive database. It was running well before. > The original version is V7.31UD10. However after I migrate both database to > new version V115UC3. We encounter the problem. Normally this archive > procedure > would only takes around 1 hour to finished, but now it runs very very slow. > Seems to me it will take more several days to finish. > I have to revert the archive database to old version IDS7.31UD10, then > re-run > the procedure. Every thing backs to normal. > Anybody has idea why and what causing this problem? > > Thanks for any help. > > Denny > ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message électronique pourrait contenir des informations privilégiées et confidentielles. Si vous n'en êtes pas le récipiendaire prévu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le présent message. Si vous l'avez reçu par erreur, veuillez prévenir l'expéditeur par courriel, puis effacer ce message et en détruire toute copie. Le courrier électronique n'est pas garanti sécuritaire ni exempt d'erreurs. Les messages pourraient être interceptés, corrompus, égarés, retardés ou contaminés par des virus. L'expéditeur n'est pas responsable de ces risques. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Denny is still speaking Klingon. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Guo, > Denny > Sent: Monday, March 23, 2009 11:29 AM > To: ids@iiug.org > Subject: RE: move data between 2 instance [15290] > > VGhhbmtzIEFydCwgDQoNCkkgd2lsbCB0cnkgeW91ciBtZXRob2QuDQpUaGUgb25seSB0aGlu Zy > BJ > IGRpZCBub3QgcnVuIGlzICJ1cGRhdGUgc3RhdGlzdGljcyBMT1cgRFJPUCBESVNUUklCVVRJ T0 > 5T > Ii4NCkkgZG8gcnVuIG5vcm1hbCBzdWl0ZSB1cGRhdGUgc3RhdGlzdGljcyBhbmQgcmUtY29t cG > ls Big snip.

Art ... your link doesn't work ..... gets a "not found" on /utilities .... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 03/23/2009 12:10 PM Subject: Re: move data between 2 instance [15289] Sent by: ids-bounces@iiug.org After an upgrade in place, you MUST MUST MUST run: - update statistics LOW DROP DISTRIBUTIONS; for all tables to get rid of data distributions on disk that may be in an incorrect format as the format changes even between minor versions - absolutely the format changed from 7.31 to 11.50 at least three times that I know about. - Rerun the normal suite of update statistics commands to restore the correct distributions - Finally recompile every stored procedure by running UPDATE STATISTICS FOR PROCEDURE ...; for every procedure on the system. After that see how this function performs. Note that my dostats utility can take care of this for you automatically and my dbcopy utility is a bit faster copying between servers than SELECT FROM ... INSERT INTO ... statement that your procedure uses when you enable the -F option. Both utilities are included in the package utils2_ak which you can download from the Oninit web site (www.oninit.com/utilities) or the IIUG Software Repository (www.iiug.org/software). Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Mon, Mar 23, 2009 at 11:06 AM, Guo, Denny <DGuo@livingstonintl.com>wrote: > Hi All, > > We have 2 databases sitting on same machine. One is our production > database, > another one is our archive database. > We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" > ) > to move the old data to archive database. It was running well before. > The original version is V7.31UD10. However after I migrate both database to > new version V115UC3. We encounter the problem. Normally this archive > procedure > would only takes around 1 hour to finished, but now it runs very very slow. > Seems to me it will take more several days to finish. > I have to revert the archive database to old version IDS7.31UD10, then > re-run > the procedure. Every thing backs to normal. > Anybody has idea why and what causing this problem? > > Thanks for any help. > > Denny > > ******************************************* > > The information contained in this e-mail message may > contain privileged and confidential information. > If you are not the intended recipient, you are > hereby notified that any review, dissemination, > distribution or duplication of this communication > is strictly prohibited. If you have received this > message in error, please notify the sender by return > e-mail, delete this message and destroy any copies. > Internet e- mail is not guaranteed to be secure or > error-free. Messages could be intercepted, corrupted, > lost, arrive late or contain viruses. > The sender will not be liable for > these risks. > > ******************************************* > Ce message électronique pourrait contenir des informations > privilégiées et confidentielles. Si vous n'en êtes pas le > récipiendaire prévu, nous vous signalons qu'il est strictement > interdit d'examiner, de diffuser, de distribuer et de reproduire le > présent message. Si vous l'avez reçu par erreur, veuillez prévenir > l'expéditeur par courriel, puis effacer ce message et en détruire > toute copie. Le courrier électronique n'est pas garanti sécuritaire > ni exempt d'erreurs. Les messages pourraient être interceptés, > corrompus, égarés, retardés ou contaminés par des virus. > L'expéditeur n'est pas responsable de ces risques. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016364ee01ca3dc160465cb7e26 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Should be www.oninit.com/utils Sorry. Run dostats once with the -L option to DROP DISTRIBUTIONS then again without -L to rebuild the new ones and recompile stored routines. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Mon, Mar 23, 2009 at 2:14 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Art ... your link doesn't work ..... gets a "not found" on /utilities .... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: > "Art Kagel" <art.kagel@gmail.com> > To: > ids@iiug.org > Date: > 03/23/2009 12:10 PM > Subject: > Re: move data between 2 instance [15289] > Sent by: > ids-bounces@iiug.org > > After an upgrade in place, you MUST MUST MUST run: > > - update statistics LOW DROP DISTRIBUTIONS; for all tables to get rid of > > data distributions on disk that may be in an incorrect format as the > format > > changes even between minor versions - absolutely the format changed from > > 7.31 to 11.50 at least three times that I know about. > > - Rerun the normal suite of update statistics commands to restore the > > correct distributions > > - Finally recompile every stored procedure by running UPDATE STATISTICS > > FOR PROCEDURE ...; for every procedure on the system. > > After that see how this function performs. Note that my dostats utility > can > take care of this for you automatically and my dbcopy utility is a bit > faster copying between servers than SELECT FROM ... INSERT INTO ... > statement that your procedure uses when you enable the -F option. > > Both utilities are included in the package utils2_ak which you can > download > from the Oninit web site (www.oninit.com/utilities) or the IIUG Software > Repository (www.iiug.org/software). > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Oninit, the IIUG, nor any other > organization > with which I am associated either explicitly or implicitly. 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 Mon, Mar 23, 2009 at 11:06 AM, Guo, Denny > <DGuo@livingstonintl.com>wrote: > > > Hi All, > > > > We have 2 databases sitting on same machine. One is our production > > database, > > another one is our archive database. > > We have one simple procedure ( "select from PRODUCTION insert into > ARCHIVE" > > ) > > to move the old data to archive database. It was running well before. > > The original version is V7.31UD10. However after I migrate both database > to > > new version V115UC3. We encounter the problem. Normally this archive > > procedure > > would only takes around 1 hour to finished, but now it runs very very > slow. > > Seems to me it will take more several days to finish. > > I have to revert the archive database to old version IDS7.31UD10, then > > re-run > > the procedure. Every thing backs to normal. > > Anybody has idea why and what causing this problem? > > > > Thanks for any help. > > > > Denny > > > > ******************************************* > > > > The information contained in this e-mail message may > > contain privileged and confidential information. > > If you are not the intended recipient, you are > > hereby notified that any review, dissemination, > > distribution or duplication of this communication > > is strictly prohibited. If you have received this > > message in error, please notify the sender by return > > e-mail, delete this message and destroy any copies. > > Internet e- mail is not guaranteed to be secure or > > error-free. Messages could be intercepted, corrupted, > > lost, arrive late or contain viruses. > > The sender will not be liable for > > these risks. > > > > ******************************************* > > Ce message électronique pourrait contenir des informations > > privilégiées et confidentielles. Si vous n'en êtes pas le > > récipiendaire prévu, nous vous signalons qu'il est strictement > > interdit d'examiner, de diffuser, de distribuer et de reproduire le > > présent message. Si vous l'avez reçu par erreur, veuillez prévenir > > l'expéditeur par courriel, puis effacer ce message et en détruire > > toute copie. Le courrier électronique n'est pas garanti sécuritaire > > ni exempt d'erreurs. Les messages pourraient être interceptés, > > corrompus, égarés, retardés ou contaminés par des virus. > > L'expéditeur n'est pas responsable de ces risques. > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --0016364ee01ca3dc160465cb7e26 > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016360e3b3c55f1300465cd67d4

Now that works ... Thanks .... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 03/23/2009 02:27 PM Subject: Re: move data between 2 instance [15295] Sent by: ids-bounces@iiug.org Should be www.oninit.com/utils Sorry. Run dostats once with the -L option to DROP DISTRIBUTIONS then again without -L to rebuild the new ones and recompile stored routines. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Mon, Mar 23, 2009 at 2:14 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Art ... your link doesn't work ..... gets a "not found" on /utilities .... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: > "Art Kagel" <art.kagel@gmail.com> > To: > ids@iiug.org > Date: > 03/23/2009 12:10 PM > Subject: > Re: move data between 2 instance [15289] > Sent by: > ids-bounces@iiug.org > > After an upgrade in place, you MUST MUST MUST run: > > - update statistics LOW DROP DISTRIBUTIONS; for all tables to get rid of > > data distributions on disk that may be in an incorrect format as the > format > > changes even between minor versions - absolutely the format changed from > > 7.31 to 11.50 at least three times that I know about. > > - Rerun the normal suite of update statistics commands to restore the > > correct distributions > > - Finally recompile every stored procedure by running UPDATE STATISTICS > > FOR PROCEDURE ...; for every procedure on the system. > > After that see how this function performs. Note that my dostats utility > can > take care of this for you automatically and my dbcopy utility is a bit > faster copying between servers than SELECT FROM ... INSERT INTO ... > statement that your procedure uses when you enable the -F option. > > Both utilities are included in the package utils2_ak which you can > download > from the Oninit web site (www.oninit.com/utilities) or the IIUG Software > Repository (www.iiug.org/software). > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Oninit, the IIUG, nor any other > organization > with which I am associated either explicitly or implicitly. 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 Mon, Mar 23, 2009 at 11:06 AM, Guo, Denny > <DGuo@livingstonintl.com>wrote: > > > Hi All, > > > > We have 2 databases sitting on same machine. One is our production > > database, > > another one is our archive database. > > We have one simple procedure ( "select from PRODUCTION insert into > ARCHIVE" > > ) > > to move the old data to archive database. It was running well before. > > The original version is V7.31UD10. However after I migrate both database > to > > new version V115UC3. We encounter the problem. Normally this archive > > procedure > > would only takes around 1 hour to finished, but now it runs very very > slow. > > Seems to me it will take more several days to finish. > > I have to revert the archive database to old version IDS7.31UD10, then > > re-run > > the procedure. Every thing backs to normal. > > Anybody has idea why and what causing this problem? > > > > Thanks for any help. > > > > Denny > > > > ******************************************* > > > > The information contained in this e-mail message may > > contain privileged and confidential information. > > If you are not the intended recipient, you are > > hereby notified that any review, dissemination, > > distribution or duplication of this communication > > is strictly prohibited. If you have received this > > message in error, please notify the sender by return > > e-mail, delete this message and destroy any copies. > > Internet e- mail is not guaranteed to be secure or > > error-free. Messages could be intercepted, corrupted, > > lost, arrive late or contain viruses. > > The sender will not be liable for > > these risks. > > > > ******************************************* > > Ce message électronique pourrait contenir des informations > > privilégiées et confidentielles. Si vous n'en êtes pas le > > récipiendaire prévu, nous vous signalons qu'il est strictement > > interdit d'examiner, de diffuser, de distribuer et de reproduire le > > présent message. Si vous l'avez reçu par erreur, veuillez prévenir > > l'expéditeur par courriel, puis effacer ce message et en détruire > > toute copie. Le courrier électronique n'est pas garanti sécuritaire > > ni exempt d'erreurs. Les messages pourraient être interceptés, > > corrompus, égarés, retardés ou contaminés par des virus. > > L'expéditeur n'est pas responsable de ces risques. > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --0016364ee01ca3dc160465cb7e26 > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016360e3b3c55f1300465cd67d4 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi All, I am still facing the same issue after try drop distributions and rebuild distributions. For sure I have re-compiled the procedure. Any idea? Thanks, Denny ________________________________________ From: ids-bounces@iiug.org [ids-bounces@iiug.org] On Behalf Of Art Kagel [art.kagel@gmail.com] Sent: Monday, March 23, 2009 12:10 PM To: ids@iiug.org Subject: Re: move data between 2 instance [15289] After an upgrade in place, you MUST MUST MUST run: - update statistics LOW DROP DISTRIBUTIONS; for all tables to get rid of data distributions on disk that may be in an incorrect format as the format changes even between minor versions - absolutely the format changed from 7.31 to 11.50 at least three times that I know about. - Rerun the normal suite of update statistics commands to restore the correct distributions - Finally recompile every stored procedure by running UPDATE STATISTICS FOR PROCEDURE ...; for every procedure on the system. After that see how this function performs. Note that my dostats utility can take care of this for you automatically and my dbcopy utility is a bit faster copying between servers than SELECT FROM ... INSERT INTO ... statement that your procedure uses when you enable the -F option. Both utilities are included in the package utils2_ak which you can download from the Oninit web site (www.oninit.com/utilities) or the IIUG Software Repository (www.iiug.org/software). Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Mon, Mar 23, 2009 at 11:06 AM, Guo, Denny <DGuo@livingstonintl.com>wrote: > Hi All, > > We have 2 databases sitting on same machine. One is our production > database, > another one is our archive database. > We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" > ) > to move the old data to archive database. It was running well before. > The original version is V7.31UD10. However after I migrate both database to > new version V115UC3. We encounter the problem. Normally this archive > procedure > would only takes around 1 hour to finished, but now it runs very very slow. > Seems to me it will take more several days to finish. > I have to revert the archive database to old version IDS7.31UD10, then > re-run > the procedure. Every thing backs to normal. > Anybody has idea why and what causing this problem? > > Thanks for any help. > > Denny > > ******************************************* > > The information contained in this e-mail message may > contain privileged and confidential information. > If you are not the intended recipient, you are > hereby notified that any review, dissemination, > distribution or duplication of this communication > is strictly prohibited. If you have received this > message in error, please notify the sender by return > e-mail, delete this message and destroy any copies. > Internet e- mail is not guaranteed to be secure or > error-free. Messages could be intercepted, corrupted, > lost, arrive late or contain viruses. > The sender will not be liable for > these risks. > > ******************************************* > Ce message 'lectronique pourrait contenir des informations > privil'gi'es et confidentielles. Si vous n'en 'tes pas le > r'cipiendaire pr'vu, nous vous signalons qu'il est strictement > interdit d'examiner, de diffuser, de distribuer et de reproduire le > pr'sent message. Si vous l'avez re'u par erreur, veuillez pr'venir > l'exp'diteur par courriel, puis effacer ce message et en d'truire > toute copie. Le courrier 'lectronique n'est pas garanti s'curitaire > ni exempt d'erreurs. Les messages pourraient 'tre intercept's, > corrompus, 'gar's, retard's ou contamin's par des virus. > L'exp'diteur n'est pas responsable de ces risques. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016364ee01ca3dc160465cb7e26 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************* The information contained in this e-mail message may contain privileged and confidential information. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you have received this message in error, please notify the sender by return e-mail, delete this message and destroy any copies. Internet e- mail is not guaranteed to be secure or error-free. Messages could be intercepted, corrupted, lost, arrive late or contain viruses. The sender will not be liable for these risks. ******************************************* Ce message électronique pourrait contenir des informations privilégiées et confidentielles. Si vous n'en êtes pas le récipiendaire prévu, nous vous signalons q

Hi All, I am still facing the issue. I have followed Art's suggestion to drop distributions and re-build distributions. I also re-compiled the procedure. Seems there is no improvement. Any ideas? Thanks, Denny After an upgrade in place, you MUST MUST MUST run: - update statistics LOW DROP DISTRIBUTIONS; for all tables to get rid of data distributions on disk that may be in an incorrect format as the format changes even between minor versions - absolutely the format changed from 7.31 to 11.50 at least three times that I know about. - Rerun the normal suite of update statistics commands to restore the correct distributions - Finally recompile every stored procedure by running UPDATE STATISTICS FOR PROCEDURE ...; for every procedure on the system. After that see how this function performs. Note that my dostats utility can take care of this for you automatically and my dbcopy utility is a bit faster copying between servers than SELECT FROM ... INSERT INTO ... statement that your procedure uses when you enable the -F option. Both utilities are included in the package utils2_ak which you can download from the Oninit web site (www.oninit.com/utilities) or the IIUG Software Repository (www.iiug.org/software). Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Mon, Mar 23, 2009 at 11:06 AM, Guo, Denny <DGuo@livingstonintl.com>wrote: > Hi All, > > We have 2 databases sitting on same machine. One is our production > database, > another one is our archive database. > We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" > ) > to move the old data to archive database. It was running well before. > The original version is V7.31UD10. However after I migrate both database to > new version V115UC3. We encounter the problem. Normally this archive > procedure > would only takes around 1 hour to finished, but now it runs very very slow. > Seems to me it will take more several days to finish. > I have to revert the archive database to old version IDS7.31UD10, then > re-run > the procedure. Every thing backs to normal. > Anybody has idea why and what causing this problem? > > Thanks for any help. > > Denny

Try adjusting OPTCOMPIND. I noticed a significant difference in the way this parameter is utilized starting with IDS 11.1.

Do you use shared memory connections or socket connections to the version 7 engine? Make sure you are using socket. Thanks! Kate Tomchik [ kate@iiug.org ] www.iiug.org International Informix Users Group Board of Directors A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratliffe ---------- Original Message ----------- From: "Guo, Denny" <DGuo@livingstonintl.com> To: ids@iiug.org Sent: Tue, 24 Mar 2009 07:41:40 -0400 (EDT) Subject: RE: move data between 2 instance [15312] > Hi All, > > I am still facing the same issue after try drop distributions and > rebuild distributions. For sure I have re-compiled the procedure. > > Any idea? > > Thanks, > Denny > ________________________________________ > From: ids-bounces@iiug.org [ids-bounces@iiug.org] On Behalf Of Art > Kagel [art.kagel@gmail.com] Sent: Monday, March 23, 2009 12:10 PM > To: ids@iiug.org Subject: Re: move data between 2 instance [15289] > > After an upgrade in place, you MUST MUST MUST run: > > - update statistics LOW DROP DISTRIBUTIONS; for all tables to get > rid of > > data distributions on disk that may be in an incorrect format as the > format > > changes even between minor versions - absolutely the format changed > from > > 7.31 to 11.50 at least three times that I know about. > > - Rerun the normal suite of update statistics commands to restore the > > correct distributions > > - Finally recompile every stored procedure by running UPDATE > STATISTICS > > FOR PROCEDURE ...; for every procedure on the system. > > After that see how this function performs. Note that my dostats > utility can take care of this for you automatically and my dbcopy > utility is a bit faster copying between servers than SELECT FROM > ... INSERT INTO ... statement that your procedure uses when you > enable the -F option. > > Both utilities are included in the package utils2_ak which you can > download from the Oninit web site (www.oninit.com/utilities) or the > IIUG Software Repository (www.iiug.org/software). > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and do not reflect on my employer, Oninit, the IIUG, nor > any other organization with which I am associated either explicitly > or implicitly. 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 Mon, Mar 23, 2009 at 11:06 AM, Guo, Denny > <DGuo@livingstonintl.com>wrote: > > > Hi All, > > > > We have 2 databases sitting on same machine. One is our production > > database, > > another one is our archive database. > > We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" > > ) > > to move the old data to archive database. It was running well before. > > The original version is V7.31UD10. However after I migrate both database to > > new version V115UC3. We encounter the problem. Normally this archive > > procedure > > would only takes around 1 hour to finished, but now it runs very very slow. > > Seems to me it will take more several days to finish. > > I have to revert the archive database to old version IDS7.31UD10, then > > re-run > > the procedure. Every thing backs to normal. > > Anybody has idea why and what causing this problem? > > > > Thanks for any help. > > > > Denny > > > > ******************************************* > > > > The information contained in this e-mail message may > > contain privileged and confidential information. > > If you are not the intended recipient, you are > > hereby notified that any review, dissemination, > > distribution or duplication of this communication > > is strictly prohibited. If you have received this > > message in error, please notify the sender by return > > e-mail, delete this message and destroy any copies. > > Internet e- mail is not guaranteed to be secure or > > error-free. Messages could be intercepted, corrupted, > > lost, arrive late or contain viruses. > > The sender will not be liable for > > these risks. > > > > ******************************************* > > Ce message 'lectronique pourrait contenir des informations > > privil'gi'es et confidentielles. Si vous n'en 'tes pas le > > r'cipiendaire pr'vu, nous vous signalons qu'il est strictement > > interdit d'examiner, de diffuser, de distribuer et de reproduire le > > pr'sent message. Si vous l'avez re'u par erreur, veuillez pr'venir > > l'exp'diteur par courriel, puis effacer ce message et en d'truire > > toute copie. Le courrier 'lectronique n'est pas garanti s'curitaire > > ni exempt d'erreurs. Les messages pourraient 'tre intercept's, > > corrompus, 'gar's, retard's ou contamin's par des virus. > > L'exp'diteur n'est pas responsable de ces risques. > > > > > > > > > ***************************************************************************** ** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --0016364ee01ca3dc160465cb7e26 > > ***************************************************************************** ** Forum Note: Use "Reply" to post a response in the discussion forum. > > ******************************************* > > The information contained in this e-mail message may > contain privileged and confidential information. > If you are not the intended recipient, you are > hereby notified that any review, dissemination, > distribution or duplication of this communication > is strictly prohibited. If you have received this > message in error, please notify the sender by return > e-mail, delete this message and destroy any copies. > Internet e- mail is not guaranteed to be secure or > error-free. Messages could be intercepted, corrupted, > lost, arrive late or contain viruses. > The sender will not be liable for > these risks. > > ******************************************* > Ce message électronique pourrait contenir des informations > privilégiées et confidentielles. Si vous n'en êtes pas le > récipiendaire prévu, nous vous signalons qu'il est strictement > interdit d'examiner, de diffuser, de distribuer et de reproduire le > présent message. Si vous l'avez reçu par erreur, veuillez prévenir > l'expéditeur par courriel, puis effacer ce message et en détruire > toute copie. Le courrier électronique n'est pas garanti sécuritaire > ni exempt d'erreurs. Les messages pourraient être interceptés, > corrompus, égarés, retardés ou contaminés par des virus. > L'expéditeur n'est pas responsable de ces risques. Šî˜Ú-yK EêeÊÚ) > ¢ËZ­ë)¢{ Š{ayجrë,Š‰ß¢»¦ ------- End of Original Message -------

My idea: - Can you manually run query of that procedure (select from PRODUCTION insert into ARCHIVE) in dbaccess ? Is it slow too ? if no, there's something wrong in the procedure - Do you migrate from the old machine/disk (upgrade) or you migrate by unload/load into new machine/disk ? If new machine , check disk layout, I/O. If old machine , check DB configuration because from 7 to 11. There 're many new parameter. - How about indexes that serve your query (if the SQL has where clause) , Some index lost/disabled between migration or not ? Regards, Jakkrit A. ________________________________ From: "Guo, Denny" <DGuo@livingstonintl.com> To: ids@iiug.org Sent: Tuesday, March 24, 2009 6:50:21 PM Subject: Re: move data between 2 instance [15313] Hi All, I am still facing the issue. I have followed Art's suggestion to drop distributions and re-build distributions. I also re-compiled the procedure. Seems there is no improvement. Any ideas? Thanks, Denny After an upgrade in place, you MUST MUST MUST run: - update statistics LOW DROP DISTRIBUTIONS; for all tables to get rid of data distributions on disk that may be in an incorrect format as the format changes even between minor versions - absolutely the format changed from 7.31 to 11.50 at least three times that I know about. - Rerun the normal suite of update statistics commands to restore the correct distributions - Finally recompile every stored procedure by running UPDATE STATISTICS FOR PROCEDURE ...; for every procedure on the system. After that see how this function performs. Note that my dostats utility can take care of this for you automatically and my dbcopy utility is a bit faster copying between servers than SELECT FROM ... INSERT INTO ... statement that your procedure uses when you enable the -F option. Both utilities are included in the package utils2_ak which you can download from the Oninit web site (www.oninit.com/utilities) or the IIUG Software Repository (www.iiug.org/software). Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Mon, Mar 23, 2009 at 11:06 AM, Guo, Denny <DGuo@livingstonintl.com>wrote: > Hi All, > > We have 2 databases sitting on same machine. One is our production > database, > another one is our archive database. > We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" > ) > to move the old data to archive database. It was running well before. > The original version is V7.31UD10. However after I migrate both database to > new version V115UC3. We encounter the problem. Normally this archive > procedure > would only takes around 1 hour to finished, but now it runs very very slow. > Seems to me it will take more several days to finish. > I have to revert the archive database to old version IDS7.31UD10, then > re-run > the procedure. Every thing backs to normal. > Anybody has idea why and what causing this problem? > > Thanks for any help. > > Denny ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Denny The tables belonging to ARCHIVE database have indexes created ? Constraints ? Remenber , if you're insertind new rows in any table, sure the indexes must be split after some new rows inserted ... Maybe the "reorg" of indexes are taking the operation slowlly ... Think about. If there are indexes, drop them and execute the process. After that, if you need recreate the indexes/constraints. Best regards R Ferronato > To: ids@iiug.org > From: jakkritakeng@yahoo.com > Subject: Re: move data between 2 instance [15328] > Date: Wed, 25 Mar 2009 03:38:19 -0400 > > My idea: > - Can you manually run query of that procedure (select from PRODUCTION insert > into ARCHIVE) in dbaccess ? > Is it slow too ? if no, there's something wrong in the procedure > - Do you migrate from the old machine/disk (upgrade) or you migrate by > unload/load into new machine/disk ? > > If new machine , check disk layout, I/O. > > If old machine , check DB configuration because from 7 to 11. There 're many > new parameter. > - How about indexes that serve your query (if the SQL has where clause) , Some > index lost/disabled between migration or not ? > > Regards, > Jakkrit A. > > ________________________________ > From: "Guo, Denny" <DGuo@livingstonintl.com> > To: ids@iiug.org > Sent: Tuesday, March 24, 2009 6:50:21 PM > Subject: Re: move data between 2 instance [15313] > > Hi All, > > I am still facing the issue. > I have followed Art's suggestion to drop distributions and re-build > distributions. > I also re-compiled the procedure. Seems there is no improvement. > > Any ideas? > > Thanks, > Denny > > After an upgrade in place, you MUST MUST MUST run: > > - update statistics LOW DROP DISTRIBUTIONS; for all tables to get rid of > > data distributions on disk that may be in an incorrect format as the format > > changes even between minor versions - absolutely the format changed from > > 7.31 to 11.50 at least three times that I know about. > > - Rerun the normal suite of update statistics commands to restore the > > correct distributions > > - Finally recompile every stored procedure by running UPDATE STATISTICS > > FOR PROCEDURE ...; for every procedure on the system. > > After that see how this function performs. Note that my dostats utility can > take care of this for you automatically and my dbcopy utility is a bit > faster copying between servers than SELECT FROM ... INSERT INTO ... > statement that your procedure uses when you enable the -F option. > > Both utilities are included in the package utils2_ak which you can download > from the Oninit web site (www.oninit.com/utilities) or the IIUG Software > Repository (www.iiug.org/software). > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. 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 Mon, Mar 23, 2009 at 11:06 AM, Guo, Denny <DGuo@livingstonintl.com>wrote: > > > Hi All, > > > > We have 2 databases sitting on same machine. One is our production > > database, > > another one is our archive database. > > We have one simple procedure ( "select from PRODUCTION insert into ARCHIVE" > > ) > > to move the old data to archive database. It was running well before. > > The original version is V7.31UD10. However after I migrate both database to > > new version V115UC3. We encounter the problem. Normally this archive > > procedure > > would only takes around 1 hour to finished, but now it runs very very slow. > > Seems to me it will take more several days to finish. > > I have to revert the archive database to old version IDS7.31UD10, then > > re-run > > the procedure. Every thing backs to normal. > > Anybody has idea why and what causing this problem? > > > > Thanks for any help. > > > > Denny > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx