Update statistics

Hi Gurus, When the system is running "update statistics", will it affect insert, update? We have some issue recently. We start to process business transaction at 6:00AM, when there is "update statistics" job running, some insert been rejected. The return code is "144","211". Any ideas? AIX: 4.3.3 Informix: 7.31UD3 Thanks, 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 electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .

There will be occassional transient locks as the update stats update the syscolumns and sysindexes records for the table(s) in the query/insert/update/delete. Your application should have declared 'SET LOCK MODE TO WAIT <n>;" where <n> is some small number of seconds. This prevents transient momentary locks from disrupting the job's processing. Art S. Kagel ----- Original Message ----- From: Denny Guo <ids@iiug.org> At: 7/19 9:10:04 Hi Gurus, When the system is running "update statistics", will it affect insert, update? We have some issue recently. We start to process business transaction at 6:00AM, when there is "update statistics" job running, some insert been rejected. The return code is "144","211". Any ideas? AIX: 4.3.3 Informix: 7.31UD3 Thanks, 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 electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks Art, That confirms my suspicion. I have another question, I try to duplicate the error on our development machine, but failed. When the "update statistics" is running, I try to process our business transaction, it works. No error shows up. The environment is same. That totally confused me. Do you have any idea? Denny -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART KAGEL, .... Sent: July 19, 2006 9:14 AM To: ids@iiug.org Subject: Re: Update statistics [7128] There will be occassional transient locks as the update stats update the syscolumns and sysindexes records for the table(s) in the query/insert/update/delete. Your application should have declared 'SET LOCK MODE TO WAIT <n>;" where <n> is some small number of seconds. This prevents transient momentary locks from disrupting the job's processing. Art S. Kagel ----- Original Message ----- From: Denny Guo <ids@iiug.org> At: 7/19 9:10:04 Hi Gurus, When the system is running "update statistics", will it affect insert, update? We have some issue recently. We start to process business transaction at 6:00AM, when there is "update statistics" job running, some insert been rejected. The return code is "144","211". Any ideas? AIX: 4.3.3 Informix: 7.31UD3 Thanks, 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 electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* 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 electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .

As I said, the locks are transient, they only last a fraction of a second. Chances are your job could have run in parallel with the update stats for years before it ever hit the -211/-144 problem, perhaps it has? Trying to replicate it is extremely difficult. SET LOCK MODE TO WAIT though is the fix. Art S. Kagel ----- Original Message ----- From: Denny Guo <ids@iiug.org> At: 7/19 9:32:46 Thanks Art, That confirms my suspicion. I have another question, I try to duplicate the error on our development machine, but failed. When the "update statistics" is running, I try to process our business transaction, it works. No error shows up. The environment is same. That totally confused me. Do you have any idea? Denny -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART KAGEL, .... Sent: July 19, 2006 9:14 AM To: ids@iiug.org Subject: Re: Update statistics [7128] There will be occassional transient locks as the update stats update the syscolumns and sysindexes records for the table(s) in the query/insert/update/delete. Your application should have declared 'SET LOCK MODE TO WAIT <n>;" where <n> is some small number of seconds. This prevents transient momentary locks from disrupting the job's processing. Art S. Kagel ----- Original Message ----- From: Denny Guo <ids@iiug.org> At: 7/19 9:10:04 Hi Gurus, When the system is running "update statistics", will it affect insert, update? We have some issue recently. We start to process business transaction at 6:00AM, when there is "update statistics" job running, some insert been rejected. The return code is "144","211". Any ideas? AIX: 4.3.3 Informix: 7.31UD3 Thanks, 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 electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* 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 electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks Art. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART KAGEL, .... Sent: July 19, 2006 9:32 AM To: ids@iiug.org Subject: RE: Update statistics [7130] As I said, the locks are transient, they only last a fraction of a second. Chances are your job could have run in parallel with the update stats for years before it ever hit the -211/-144 problem, perhaps it has? Trying to replicate it is extremely difficult. SET LOCK MODE TO WAIT though is the fix. Art S. Kagel ----- Original Message ----- From: Denny Guo <ids@iiug.org> At: 7/19 9:32:46 Thanks Art, That confirms my suspicion. I have another question, I try to duplicate the error on our development machine, but failed. When the "update statistics" is running, I try to process our business transaction, it works. No error shows up. The environment is same. That totally confused me. Do you have any idea? Denny -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART KAGEL, .... Sent: July 19, 2006 9:14 AM To: ids@iiug.org Subject: Re: Update statistics [7128] There will be occassional transient locks as the update stats update the syscolumns and sysindexes records for the table(s) in the query/insert/update/delete. Your application should have declared 'SET LOCK MODE TO WAIT <n>;" where <n> is some small number of seconds. This prevents transient momentary locks from disrupting the job's processing. Art S. Kagel ----- Original Message ----- From: Denny Guo <ids@iiug.org> At: 7/19 9:10:04 Hi Gurus, When the system is running "update statistics", will it affect insert, update? We have some issue recently. We start to process business transaction at 6:00AM, when there is "update statistics" job running, some insert been rejected. The return code is "144","211". Any ideas? AIX: 4.3.3 Informix: 7.31UD3 Thanks, 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 electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* 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 electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques . ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* 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 electronique pourrait contenir des informations privilegiees et confidentielles. Si vous n'en etes pas le recipiendaire prevu, nous vous signalons qu'il est strictement interdit d'examiner, de diffuser, de distribuer et de reproduire le present message. Si vous l'avez recu par erreur, veuillez prevenir l'expediteur par courriel, puis effacer ce message et en detruire toute copie. Le courrier electronique n'est pas garanti securitaire ni exempt d'erreurs. Les messages pourraient etre interceptes, corrompus, egares, retardes ou contamines par des virus. L'exp'editeur n'est pas responsable de ces risques .