Error # 40002
User encountered error 40002 (table dropped/altered/renamed) randomly on Informix 11.70. Expert explained this typically occurs when table statistics are updated, indexes changed, or tables altered while prepared statements or stored procedures reference them. Recommended solution: disable Auto-Update Statistics (AUS) and use dostats_ng.ec utility instead, which recompiles stored procedures after updating statistics.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, We are running ERP system (written in Visual Basic) on IBM-AIX (P560)[AIX version 6.1] using informix 11.70. To conect to informix database we use DataDirect ODBC (ver 4.10.0030) Lately we are getting (randomly - not everyday) table dropped message Error Number: 40002 Error Description: S100: [DataDirect][ODBC Informix driver]{Informix]Tab;e (informix.dpr) has been dropped, altered or renamed Error Source: MSRDO20.DLL The only way to fix this is to restart the database using onmonitor. Taking database offline & starting again We have been told by the EPR vendor that it is a INFORMIX problem. I am not sure what to do? Has anyone encountered this message? What is the cause?
This is usually caused by updating a table's data distributions, altering the table, or adding or dropping an index while an application has a prepared statement defined or if a session executes a stored procedure that refers to the modified table that has not been recompiled since the changes. Informix 11.xx has a task that runs nightly that automatically updates the data distributions for tables. Only tables whose stats are stale are actually updated, so the table causing this error is probably not updated every night, but only periodically depending on the activity level. That task does not recompile stored procedures after updating the data distributions for tables which can sometimes cause one of the first few tasks to execute an affected procedure to get an error while the procedure is auto-recompiling itself due to the first access to it since the changes. So, my advice, turn off AUS (Auto-Update Statistics) and use my dostats_ng.ec utility to perform this task nightly or weekly as it DOES recompile all procs when it finishes the tables. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Nov 12, 2012 at 8:11 PM, SANJAY SHAH <shah@hcma.com.au> wrote: > Hi, > > We are running ERP system (written in Visual Basic) on IBM-AIX (P560)[AIX > version 6.1] using informix 11.70. To conect to informix database we use > DataDirect ODBC (ver 4.10.0030) > > Lately we are getting (randomly - not everyday) table dropped message > > Error Number: 40002 > Error Description: S100: [DataDirect][ODBC Informix driver]{Informix]Tab;e > (informix.dpr) has been dropped, altered or renamed > Error Source: MSRDO20.DLL > > The only way to fix this is to restart the database using onmonitor. Taking > database offline & starting again > > We have been told by the EPR vendor that it is a INFORMIX problem. > > I am not sure what to do? Has anyone encountered this message? What is the > cause? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93406d7df7c1104ce57d389
Also set AUTO_REPREPARE 1 in your $ONCONFIG file. Regards, Doug Lawry
Especially if you want your engine to crash often :-) Cheers Paul Paul Watson Oninit www.oninit.com +1 913 387 7529 On Nov 13, 2012, at 5:29, "DOUG LAWRY" <douglawry@hotmail.com> wrote: > Also set AUTO_REPREPARE 1 in your $ONCONFIG file.=20 >=20 > Regards,=20 > Doug Lawry=20 >=20 >=20 > **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20
Works for me, Paul. Client APIs may need updating, though.
Takes our engine out :-) Paul Watson Oninit www.oninit.com +1 913 387 7529 On Nov 13, 2012, at 9:04, "DOUG LAWRY" <douglawry@hotmail.com> wrote: > Works for me, Paul. Client APIs may need updating, though. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.