Maximun locks request reached... 
Author Message
 Maximun locks request reached...

As a programmer, is there any way to prevent of reach the maximun number
of lock requets and get a SqlCode -912? My process is a PL/I DB2 program
running on MVS.
The last run this process lasted 4 times more than usual; after it began
to run, nobody could connect to the database. In a previous run the
process has canceled with SqlCode -912.

I don't know if, as a programmer, is there any action I could perform to
avoid it.
What should I tell to the DB2 Administrator?

Thanks in advanced,
Marcelo Narvaja



Sun, 17 Sep 2000 03:00:00 GMT  
 Maximun locks request reached...

you could include the sql statement lock table in share mode or lock table
in exclusive mode before the rest of sql in your program

Quote:

> As a programmer, is there any way to prevent of reach the maximun number
> of lock requets and get a SqlCode -912? My process is a PL/I DB2 program
> running on MVS.
> The last run this process lasted 4 times more than usual; after it began
> to run, nobody could connect to the database. In a previous run the
> process has canceled with SqlCode -912.

> I don't know if, as a programmer, is there any action I could perform to
> avoid it.
> What should I tell to the DB2 Administrator?

> Thanks in advanced,
> Marcelo Narvaja



Sun, 17 Sep 2000 03:00:00 GMT  
 Maximun locks request reached...

Locking quanties are set at the subsystem level.
dsnzparms: numlkts -- max lock per tablespace before lock escation. default=1k..
dsnzparm: numlkus -- max locks per user/thread. default=10k

1) commit more frequently, access less table in a single unit of work.
2) change a lock size row to lock size any on tablespace, increase seg size
3) on index, reduce subpages, or user type 2 ix
4) lock table before accessing

Quote:

> As a programmer, is there any way to prevent of reach the maximun number
> of lock requets and get a SqlCode -912? My process is a PL/I DB2 program
> running on MVS.
> The last run this process lasted 4 times more than usual; after it began
> to run, nobody could connect to the database. In a previous run the
> process has canceled with SqlCode -912.

> I don't know if, as a programmer, is there any action I could perform to
> avoid it.
> What should I tell to the DB2 Administrator?

> Thanks in advanced,
> Marcelo Narvaja



Sun, 17 Sep 2000 03:00:00 GMT  
 Maximun locks request reached...

Here is what you can do :
1. COMMIT frequently, don't wait for the implicit COMMIT at the end of the
job.
2. Don't use RR isolation if you can avoid it ( which in most cases can be
avoided using RS isolation ).
3. Check with your DBA if row level locking is defined for the table ? If
yes, can page level be used instead ?

Hope it helps.

Khalid



Quote:
> As a programmer, is there any way to prevent of reach the maximun number
> of lock requets and get a SqlCode -912? My process is a PL/I DB2 program
> running on MVS.
> The last run this process lasted 4 times more than usual; after it began
> to run, nobody could connect to the database. In a previous run the
> process has canceled with SqlCode -912.

> I don't know if, as a programmer, is there any action I could perform to
> avoid it.
> What should I tell to the DB2 Administrator?

> Thanks in advanced,
> Marcelo Narvaja



Mon, 18 Sep 2000 03:00:00 GMT  
 Maximun locks request reached...

Quote:

> Here is what you can do :
> ...
> 2. Don't use RR isolation if you can avoid it ( which in most cases can be
> avoided using RS isolation ).
> ...

> Hope it helps.

I think that will help a lot!

By the way, what I only know about RR and RS isolation, is some setting
in a DB2 installation panel, isn't it? I mean is it a task for the DBA?

Thanks in advanced,
Marcelo Narvaja



Tue, 19 Sep 2000 03:00:00 GMT  
 Maximun locks request reached...

The default will be at what isolation level your plan/package is bound with.  You
can override this on a select stmt by adding a "with cs" or "with rs" to the end
of the stmt.

Quote:


> > Here is what you can do :
> > ...
> > 2. Don't use RR isolation if you can avoid it ( which in most cases can be
> > avoided using RS isolation ).
> > ...

> > Hope it helps.

> I think that will help a lot!

> By the way, what I only know about RR and RS isolation, is some setting
> in a DB2 installation panel, isn't it? I mean is it a task for the DBA?

> Thanks in advanced,
> Marcelo Narvaja



Thu, 21 Sep 2000 04:00:00 GMT  
 Maximun locks request reached...

No, it's not for DBA anymore. It's a bind parameter ISOLATION which applies
to whole PLAN or PACKAGE but it can be included in the SQL statement to
make it specific to that statement.


Quote:

> I think that will help a lot!

> By the way, what I only know about RR and RS isolation, is some setting
> in a DB2 installation panel, isn't it? I mean is it a task for the DBA?

> Thanks in advanced,
> Marcelo Narvaja



Mon, 25 Sep 2000 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. num lock, caps lock, scroll lock lights

2. 2nd request - Application locks up when minimized

3. Feature request: VISA serial locking

4. Num Lock and Caps Lock

5. DBFNTX/1035 Lock failure - type lock

6. Page Lock vs. Record Lock

7. a traffic light program with lock's (num loack Caps loack and scroll lock)

8. COBOL record locks - find out who has the record lock

9. Record Locking - Who has locked it

10. global threading.Lock not locking correctly?

11. how to on/off num lock, caps lock, scroll lock ???

12. How to reach PPS?

 

 
Powered by phpBB® Forum Software