Hi,

We have 4 servers that run the same services to call stored procedures to validate and insert data to tables, these services run every 10 second by cron.  Sometimes we get this error "Could not position within a file via an index", believe it happen because table lock, same process tries to update same table. Our table mode already set row locks and our store procedures for updating table have set lock wait 10, what else we can do to fix this issue. The current version IDS 14.10FC8 runs on VMware Server Red hat Linux 8.

Please advice

Thank You

------------------------------
MOHD FADZIL JUSOH
------------------------------

What your quoting, "Could not position within a file via an index", is the SQL error message.

It normally comes with an associated ISAM error code/message which indicates the actual lower level reason for the SQL error and which, in this case, likely is some sort of locking error.  Without knowing what precisely that is, it's hard to say what's going on.

------------------------------
Andreas Legner
------------------------------

Hi,

1. Ensure transactions are as short as possible i.e. the app does not do BEGIN WORK, lock rows and wait for sometime that is not the database.

2. This can be because a lock on an index key is the same as locking all rows with the same values in the indexed columns.

Ensure that the table has a serial or bigserial column and add that to the end of each non-uniqure index or unqiue index with nulls in a column so each index key only covers 1 row.

Regards,
David.

Regards,

David.

------------------------------
David Williams
------------------------------

High Mohd,

I would also try it with "dirty read" and then measure the runtime of the individual calls. Maybe it is because of access from another process and the "lock mode wait 10" is not enough. in case of need, you could switch on the "trace on" for the procedure. So a little research work - but it should work in any case finally as you describe it.

Good Luck,

Henri

------------------------------
Henri Cujass
leolo IT, CTO
Germany
IBM Champion 2021 2022 2023
IIUG Board Member
------------------------------

On onconfig file have parameter for USELASTCOMMITTED

 USELASTCOMMITTED - Controls the committed read isolation level.
#                    Acceptable values are:
#                    - "NONE" Waits on a lock
#                    - "DIRTY READ" Uses the last committed value in
#                      place of a dirty read
#                    - "COMMITTED READ" Uses the last committed value
#                      in place of a committed read
#                    - "ALL" Uses the last committed value in place
#                      of all isolation levels that support the last
#                      committed option

Currently we used default value "NONE" , If we change it to "ALL", it will help to solve this problem? OR which option best to used. Our database is in Buffer mode, our java code has set begin work and commit for all process.

Thank You

------------------------------
MOHD FADZIL JUSOH
------------------------------

Original Message:
Sent: Tue March 07, 2023 02:06 AM
From: Henri Cujass
Subject: Table Lock

High Mohd,

I would also try it with "dirty read" and then measure the runtime of the individual calls. Maybe it is because of access from another process and the "lock mode wait 10" is not enough. in case of need, you could switch on the "trace on" for the procedure. So a little research work - but it should work in any case finally as you describe it.

Good Luck,

Henri

------------------------------
Henri Cujass
leolo IT, CTO
Germany
IBM Champion 2021 2022 2023
IIUG Board Member
------------------------------