I have a process that runs everyday, lots of short jobs usually back
to back, loading data into tables. This process has been around since
our business was tiny, but over the past few years we hacve grown alot
and now from time to time I have seen the following error messages.
Each time I see the error its always it seems like its when my data
loads are larger than normal and the process is very long running (12+
hours) and that seems to be the only thing in common during the
errors.

I know from experieince that I can usually just resubmit the same
exact job and data and its likely to run fine on the second try. I
know you dont know my code and table structures, but whats your knee
jerk opinion of whats going on? I am a sysadmin and not a programmer,
but my opinion is that the application lacks any lock maangement logic
and the fact that it runs as well as it does normally might be the
product of good luck/good timing?

-211
__________________________________________________ ________
Cannot read system catalog catalog-table.
The database server refers to the tables of the system catalog while
it processes most statements. When it cannot read one of these
important tables, a serious error results. Check the accompanying ISAM
error code for more information. The effect of the error depends on
the statement that is being executed and the particular table, as
follows:
* CREATE TABLE statement, systabauth not read; the table is
created, but PUBLIC is not granted authorization as it normally is.
* DROP TABLE statement, systables not read; no action taken.
* DROP TABLE statement, sysviews not read; the table is dropped
but any views that depended on the table were not automatically
dropped.
* DROP VIEW statement, sysviews not read; no action taken.
* DROP INDEX statement, sysindexes or systables not read; no
action taken.
* DROP SYNONYM statement, systables or syssynonyms not read; no
action taken.
* DROP DATABASE statement, systables not read; no action taken.
* START DATABASE statement, systables not read; no action taken.
* DATABASE statement, systables or sysusers not read; the
database was not selected (no current database; for subsequent
operations, see error -349).
Other statements may be partially complete before the error is
detected. Roll back the current transaction and then investigate the
cause of the error. Use the bcheck or secheck utility (tbcheck with
INFORMIX-OnLine or oncheck with INFORMIX-OnLine Dynamic Server) to
check and repair indexes. If necessary, restore the database
from backup and logical-log tapes.


-144
__________________________________________________ ________
ISAM error: key value locked.
The current operation inserts a row with a certain primary key value
or updates a row with a certain primary key value, but a transaction
that has not yet been committed has deleted that key value from the
index. This error occurs only when the lock mode is set to NOT WAIT.
Treat it the same as error -107 (record is locked). Roll back the
current transaction, and re-execute it after a delay. Then, if the
other transaction was committed, the lock no longer exists. If it was
rolled back, the key exists, and this operation receives a
duplicate-key error.

sumGirl wrote:
> I have a process that runs everyday, lots of short jobs usually back
> to back, loading data into tables. This process has been around since
> our business was tiny, but over the past few years we hacve grown alot
> and now from time to time I have seen the following error messages.
> Each time I see the error its always it seems like its when my data
> loads are larger than normal and the process is very long running (12+
> hours) and that seems to be the only thing in common during the
> errors.
>
> I know from experieince that I can usually just resubmit the same
> exact job and data and its likely to run fine on the second try. I
> know you dont know my code and table structures, but whats your knee
> jerk opinion of whats going on? I am a sysadmin and not a programmer,
> but my opinion is that the application lacks any lock maangement logic
> and the fact that it runs as well as it does normally might be the
> product of good luck/good timing?
>
> -211
> __________________________________________________ ________
> Cannot read system catalog catalog-table.
....

>
> -144
> __________________________________________________ ________
> ISAM error: key value locked.
....

What is the O/S?
What is the version of IDS?
Are there drop / create tables / indexes?
Are there update statistics?

Can you repro in a test environment?
You could trap the error and see what sql is actually running (onmode -I)

On Thu, 15 Jul 2004 11:27:59 -0400, sumGirl wrote:

> I have a process that runs everyday, lots of short jobs usually back to
> back, loading data into tables. This process has been around since our
> business was tiny, but over the past few years we hacve grown alot and now
> from time to time I have seen the following error messages. Each time I see
> the error its always it seems like its when my data loads are larger than
> normal and the process is very long running (12+ hours) and that seems to be
> the only thing in common during the errors.
>
> I know from experieince that I can usually just resubmit the same exact job
> and data and its likely to run fine on the second try. I know you dont know
> my code and table structures, but whats your knee jerk opinion of whats
> going on? I am a sysadmin and not a programmer, but my opinion is that the
> application lacks any lock maangement logic and the fact that it runs as
> well as it does normally might be the product of good luck/good timing?
<SNIP>

The -349 & -211 errors are related. Some process is creating or dropping
objects (tables, indexes, etc) and another process wants to lookup something
while the system catalog is locked.

The -107 & -144 are also related. Records are being updated, deleted,
inserted and another query is trying to access the locked record.

SET LOCK MODE TO WAIT is the solution to all of these. The locks that are
being described are normally transitory, lasting fractions of a second. Lock
mode WAIT will wait for the locks to clear and only return a timeout error if
the WAIT period expires. When you update load gets larger there's just more
chance of interferring with other apps, so you do not always see the problem.

Art S. Kagel

Thanks Art, I kind of thought so too. Its an uphill battle for me
because I keep getting this "we never had to do that before" attitude,
but I think what we are seeing now are growing pains due to the
increase in business and work for the database. Its very frustrating,
this is the first place I have ever worked were the developers didnt
want to build lock and error handling into there code!

New info! We accidentally recreated the exact error messages with the
job in questionon a test system. At the time of error we had a process
outside of this job running some update statistics statements for a
variety of tables...what do you think about that being a possible
cause of this kind of blocking?

I know we still need to use lock wait hints as well, just wondering.

This could be the cause of your 211 errors.

emebohw@netscape.net (sumGirl) wrote in message news:<a5e13cff.0407190526.6aa5d513@posting.google. com>...
> New info! We accidentally recreated the exact error messages with the
> job in questionon a test system. At the time of error we had a process
> outside of this job running some update statistics statements for a
> variety of tables...what do you think about that being a possible
> cause of this kind of blocking?
>
> I know we still need to use lock wait hints as well, just wondering.