Cannot read from sysprocplan
An app hit "-211 Cannot read system catalog (sysprocplan)" with ISAM -154 Lock Timeout Expired (and later 244/107 record is locked), while the same SELECT ran fine from dbaccess on the server. One responder suggested SET ISOLATION TO DIRTY READ and UPDATE STATISTICS HIGH on the involved tables. The poster then noticed all logical logs were full and unbacked-up; after backing them up the application worked again. Another contributor explained why: with logs full, transactions stall while holding their locks, so freeing a log lets them finish and release locks — i.e. the log-full condition, not a catalog problem, was the cause. Advice was also given to set LTAPEDEV=/dev/null and no_log.sh on a dev box.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, we are seeing an error through an application when it tries to do a select
on the database, but if we go on the server and use dbaccess, the select
statement works. Anybody know anything about these errors?
12/08-09:22:26.211 [01] <002:0001:> (01) ==> inf_exec_stmt: (-211,-154) Cannot
read system catalog (sysprocplan).
12/08-09:22:26.211 [01] <002:0001:> (02) ==> ISAM error: Lock Timeout Expired
1) try "set isolation to dirty read;" before your select2) try 1) first, if
not working, try "update statistics high for table <your tablename>"let us
know how it goes... Let's go GreenThis email contains 100% recycled electrons.
From: BENJI LONG <ruggedmouse@hotmail.com>
To: ids@iiug.org
Sent: Friday, December 8, 2017 9:13 AM
Subject: Cannot read from sysprocplan [40343]
Hi, we are seeing an error through an application when it tries to do a select
on the database, but if we go on the server and use dbaccess, the select
statement works. Anybody know anything about these errors?
12/08-09:22:26.211 [01] <002:0001:> (01) ==> inf_exec_stmt: (-211,-154) Cannot
read system catalog (sysprocplan).
12/08-09:22:26.211 [01] <002:0001:> (02) ==> ISAM error: Lock Timeout Expired
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Ok, I will look into this and try. I did notice that every log was full and not backed up. Would that cause that issue? I just backed them up, and waiting on the developer to give it a try from the application. This is on a dev box that I haven't been on yet. I will have to setup automatic log backups...but back to this issue first.
Developer says it is working now after I backed up the logs, so I'm going to sit tight and hope that was the reason. :)
Logical logs are getting full is a separate issue.For development environment,
and if you have no reason to do point-in-time restore which you usually don't,
you should handle llog by changing 2 things:1) LTAPEDEV=/dev/null (onmode -wf
LTAPEDEV=/dev/null)2) ALARMPROGRAM=$INFORMIXDIR/etc/no_log.sh (onmode -wf
ALARMPROGRAM=$INFORMIXDIR/etc/no_log.sh)
There may be no need to bounce the engine.
Let's go GreenThis email contains 100% recycled electrons.
From: BENJI LONG <ruggedmouse@hotmail.com>
To: ids@iiug.org
Sent: Friday, December 8, 2017 9:52 AM
Subject: Re: Cannot read from sysprocplan [40345]
Ok, I will look into this and try.
I did notice that every log was full and not backed up. Would that cause that
issue? I just backed them up, and waiting on the developer to give it a try
from the application. This is on a dev box that I haven't been on yet. I will
have to setup automatic log backups...but back to this issue first.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi, I was just reading up on set isolation to dirty read. From what I'm reading, this looks like it is a deadlock, so I see what you are saying that this fix could help, but it seems like it would be a work around to get the select to work, but you wouldn't run that every time you did a select would you? What does the updating statistics job do to the table that would allow it to do a select? I also see online that these statistics could be the issue but I don't know much about this.
Here is another error they were seeing:
244: Could not do a physical-order read to fetch next row.
107: ISAM error: record is locked.
Error in line 1
I do not know the answer maybe someone does, since I do not know exactly what
you guys were doing besides knowing that a "select" from the Windows side
failed while from dbaccess worked. But based on others' past encountering with
similar errors you have, that was what they did to resolve. Also I do not
believe logical log was the issue.
Let's go GreenThis email contains 100% recycled electrons.
From: BENJI LONG <ruggedmouse@hotmail.com>
To: ids@iiug.org
Sent: Friday, December 8, 2017 11:09 AM
Subject: Re: Cannot read from sysprocplan [40350]
Hi, I was just reading up on set isolation to dirty read. From what I'm
reading, this looks like it is a deadlock, so I see what you are saying that
this fix could help, but it seems like it would be a work around to get the
select to work, but you wouldn't run that every time you did a select would
you?
What does the updating statistics job do to the table that would allow it to
do a select? I also see online that these statistics could be the issue but I
don't know much about this.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Well, did you do the update statistics on related tables?
Let's go GreenThis email contains 100% recycled electrons.
From: BENJI LONG <ruggedmouse@hotmail.com>
To: ids@iiug.org
Sent: Friday, December 8, 2017 11:40 AM
Subject: Re: Cannot read from sysprocplan [40351]
Here is another error they were seeing:
244: Could not do a physical-order read to fetch next row.
107: ISAM error: record is locked.
Error in line 1
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
I haven't done anything yet. I was trying to figure out what updating the statistics would actually do to fix the issue.
I'm going to run the update statistics and set up a job to do that weekly. Hopefully that is the issue, but I can't find out why it is. Still looking.
When logs are full, any ongoing transaction comes to a halt, can neither proceed nor rollback, just has to wait ... while keeping all the locks it happens to hold. As soon as you free up the next log. log, by backing it up, your open transactions can carry on, eventually finish and free their locks. That's probably what you've seen, not a problem in itself. From: "BENJI LONG" <ruggedmouse@hotmail.com> To: ids@iiug.org Date: 12/08/2017 04:04 PM Subject: Re: Cannot read from sysprocplan [40346] Sent by: ids-bounces@iiug.org Developer says it is working now after I backed up the logs, so I'm going to sit tight and hope that was the reason. :) ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.