NUMLKUS Exceeded for simple Unload

[login to unmask email]

NUMLKUS Exceeded for simple Unload
Dear List:

In a DB2 for z/OS V7 environment I executed DSNTIAUL (Bound with
ISOLATION(CS)) in order to unload rows from a table containing a BLOB
column. My input statement is:

SELECT DOC_REF, DOCUMENT
FROM Z.ZLLBLOB;

Where the table is defined as:

COL_NAME COLNO COLTYPE LENGTH
---------+---------+---------+---------+-----
DOC_REF 1 INTEGER 4
ZLLBLOB 2 ROWID 17
DOCUMENT 3 BLOB 4

Upon execution, DSNTIAUL begins to unload lots (millions) of rows.
However, it eventually ends with a -904, thus:

DSNT501I !DT11 DSNILMCL RESOURCE UNAVAILABLE 714
CORRELATION-ID=<JOBNAME>
CONNECTION-ID=BATCH
LUW-ID=*
REASON 00C90096
TYPE 00000F01
NAME 0ECF.0059.1E0092EBE5EDD9092604015BFE84.00001

And the "00C90096" means: Exceeded NUMLKUS.

We've got NUMLKUS set at 15,000, so I have some difficulty believing that
I'm taking so many locks. Yes, I can fix this by specifying "WITH UR",
but I'm still puzzled about the locking bevavior.

Any ideas, anyone?

Lock Lyon
Compuware Corp

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: NUMLKUS Exceeded for simple Unload
(in response to LL581@DAIMLERCHRYSLER.COM)
Lockwood,

I think that's scary. Check two things:
1) Does your base table have LOCKSIZE(PAGE)?
2) Do you have an online monitor such as Omegamon, CA-INSIGHT, or BMC
MAINVIEW/DB2 which will allow you to examine what locks the process holds?
Could the BLOB be holding locks without escalating? It's supposed to be
a more "primitive" kind of tablespace structure, as far as I can tell from
what I've read about it?

We have a multimillion-row BLOB table and would be interested in what you
find out. Please let the list know how this comes out.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]






[login to unmask email]
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/08/2004 12:04 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: [DB2-L] NUMLKUS Exceeded for simple Unload



Dear List:

In a DB2 for z/OS V7 environment I executed DSNTIAUL (Bound with
ISOLATION(CS)) in order to unload rows from a table containing a BLOB
column. My input statement is:

SELECT DOC_REF, DOCUMENT
FROM Z.ZLLBLOB;

Where the table is defined as:

COL_NAME COLNO COLTYPE LENGTH
---------+---------+---------+---------+-----
DOC_REF 1 INTEGER 4
ZLLBLOB 2 ROWID 17
DOCUMENT 3 BLOB 4

Upon execution, DSNTIAUL begins to unload lots (millions) of rows.
However, it eventually ends with a -904, thus:

DSNT501I !DT11 DSNILMCL RESOURCE UNAVAILABLE 714
CORRELATION-ID=<JOBNAME>
CONNECTION-ID=BATCH
LUW-ID=*
REASON 00C90096
TYPE 00000F01
NAME 0ECF.0059.1E0092EBE5EDD9092604015BFE84.00001

And the "00C90096" means: Exceeded NUMLKUS.

We've got NUMLKUS set at 15,000, so I have some difficulty believing that
I'm taking so many locks. Yes, I can fix this by specifying "WITH UR",
but I'm still puzzled about the locking bevavior.

Any ideas, anyone?

Lock Lyon
Compuware Corp
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Robert Tilkes

Re: NUMLKUS Exceeded for simple Unload
(in response to Philip Sevetson)
Lock Lyon,

ENV - DB2 7.1 for z/OS

I have unload tables will LOB columns. We are not able to us the process
that you are trying to use because the LOB column is greater than 32 K. We
have a program that has been written in house that will unload and load.
Our LOB value is 3 M. The program contains commits and we have never
experienced the problem you are having. I do not know if this will help.
The other question is if you are on ver 7 have you tried the IBM unload
utility?

Bob




[login to unmask email]
OM T
To: [login to unmask email]
Sent by: DB2 Data Base cc:
Discussion List
<[login to unmask email]> bcc:
Subject: NUMLKUS
Exceeded for simple Unload



12/08/2004 11:04 AM
Please respond to DB2
Database Discussion
list at IDUG







Dear List:

In a DB2 for z/OS V7 environment I executed DSNTIAUL (Bound with
ISOLATION(CS)) in order to unload rows from a table containing a BLOB
column. My input statement is:

SELECT DOC_REF, DOCUMENT
FROM Z.ZLLBLOB;

Where the table is defined as:

COL_NAME COLNO COLTYPE LENGTH
---------+---------+---------+---------+-----
DOC_REF 1 INTEGER 4
ZLLBLOB 2 ROWID 17
DOCUMENT 3 BLOB 4

Upon execution, DSNTIAUL begins to unload lots (millions) of rows. However,
it eventually ends with a -904, thus:

DSNT501I !DT11 DSNILMCL RESOURCE UNAVAILABLE 714
CORRELATION-ID=<JOBNAME>
CONNECTION-ID=BATCH
LUW-ID=*
REASON 00C90096
TYPE 00000F01
NAME 0ECF.0059.1E0092EBE5EDD9092604015BFE84.00001

And the "00C90096" means: Exceeded NUMLKUS.

We've got NUMLKUS set at 15,000, so I have some difficulty believing that
I'm taking so many locks. Yes, I can fix this by specifying "WITH UR",
but I'm still puzzled about the locking bevavior.

Any ideas, anyone?

Lock Lyon
Compuware Corp
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Avram Friedman

Re: NUMLKUS Exceeded for simple Unload
(in response to Robert Tilkes)
Just a guess, but I would take a look at the following APAR


General search


Item PQ92576


<http://www-306.ibm.com/ibmlink/link2/ast/astPage.jsp?applJsp=astTrack.j
sp&items=PQ92576> Track
<http://www-306.ibm.com/ibmlink/link2/ast/astPage.jsp?applJsp=astTrack.j
sp&items=PQ92576>





APAR Identifier ...... PQ92576
<http://www-306.ibm.com/ibmlink/link2/sis/sisPage.jsp?applJsp=documentBr
owse.jsp&navItem=sis.jsp&docNumber=PQ92576> Last Changed ........
04/10/05
LOB LOCKS NOT RELEASED RC00C90092


Symptom ...... IN INCORROUT Status ........... CLOSED PER
Severity ................... 3 Date Closed ......... 04/09/10
Component .......... 5740XYR00 Duplicate of ........
Reported Release ......... 710 Fixed Release ............ 999
Component Name 5740 IBM DATABA Special Notice
Current Target Date ..04/11/12 Flags
SCP ...................
Platform ............

Status Detail: SHIPMENT - Packaged solution is available for
shipment.

PE PTF List:

PTF List:
Release 710 : UQ92789
<http://www-306.ibm.com/ibmlink/link2/sis/sisPage.jsp?applJsp=documentBr
owse.jsp&navItem=sis.jsp&docNumber=UQ92789> available 04/09/28 (F409 )
Release 810 : UQ92790
<http://www-306.ibm.com/ibmlink/link2/sis/sisPage.jsp?applJsp=documentBr
owse.jsp&navItem=sis.jsp&docNumber=UQ92790> available 04/09/28 (F409 )


Parent APAR:
Child APAR list:


ERROR DESCRIPTION:
Lob locks not released causing RC00C90092.


LOCAL FIX:


PROBLEM SUMMARY:
****************************************************************
* USERS AFFECTED: All DB2 users. *
****************************************************************
* PROBLEM DESCRIPTION: From IRLM address space: *
* DXR175E IDP1001 IRLM IS UNABLE TO *
* OBTAIN STORAGE - ECSA. *
* *
* From DB2 (MSTR) address space: *
* -DBP1 DSNTCPRE RESOURCE UNAVAILABLE *
* CORRELATION-ID=SSCP1 *
* CONNECTION-ID=DB2CALL *
* LUW-ID=* *
* REASON 00C90092 *
* TYPE 00000905 *
* NAME IRLM *
****************************************************************
* RECOMMENDATION: *
****************************************************************
When a LOB table space was defined as LOCKMAX 0 for no LOB
lock escalation, as the user application fetches the LOB data
and commits after each fetch, DB2 issued an IRLM resource
unavailable error due to incorrect count of LOB locks during
COMMIT.

The problem does not exist if the user application only fetches
the LOB data without a COMMIT. The LOB locks were counted
correctly in this case.


PROBLEM CONCLUSION:
DB2 is fixed to count the LOB locks correctly at COMMIT.
This will cause the number of locks allowed per user to
be checked correctly and DB2 will issue the following message
when the number of locks allowed per user is reached.
DSNT408I SQLCODE = -904,
ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE.
REASON 00C90096, TYPE OF RESOURCE 00000F01, AND RESOURCE NAME
.




_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Wednesday, December 08, 2004 12:05 PM
To: [login to unmask email]
Subject: NUMLKUS Exceeded for simple Unload



Dear List:

In a DB2 for z/OS V7 environment I executed DSNTIAUL (Bound with
ISOLATION(CS)) in order to unload rows from a table containing a BLOB
column. My input statement is:

SELECT DOC_REF, DOCUMENT
FROM Z.ZLLBLOB;

Where the table is defined as:

COL_NAME COLNO COLTYPE LENGTH
---------+---------+---------+---------+-----
DOC_REF 1 INTEGER 4
ZLLBLOB 2 ROWID 17
DOCUMENT 3 BLOB 4

Upon execution, DSNTIAUL begins to unload lots (millions) of rows.
However, it eventually ends with a -904, thus:

DSNT501I !DT11 DSNILMCL RESOURCE UNAVAILABLE 714
CORRELATION-ID=<JOBNAME>
CONNECTION-ID=BATCH
LUW-ID=*
REASON 00C90096
TYPE 00000F01
NAME 0ECF.0059.1E0092EBE5EDD9092604015BFE84.00001

And the "00C90096" means: Exceeded NUMLKUS.

We've got NUMLKUS set at 15,000, so I have some difficulty believing
that I'm taking so many locks. Yes, I can fix this by specifying "WITH
UR", but I'm still puzzled about the locking bevavior.

Any ideas, anyone?

Lock Lyon
Compuware Corp
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
--------------------------------------------------------

NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Infodemic B.V. Douwe van Sluis

Re: NUMLKUS Exceeded for simple Unload
(in response to Avram Friedman)
We see the same thing happening here.

Our work-around is to escalate to tablespace locking. Or we set temporary
LOCKSIZE to TABLESPACE.

Even WITH UR does not help, DB2 has to take a 'sort of mass-delete lock' on
the LOB.

Have fun with LOBs ;-)

Vriendelijke groet,
Douwe van Sluis, Infodemic B.V.


-----Oorspronkelijk bericht-----
Van: DB2 Data Base Discussion List [mailto:[login to unmask email] Namens
[login to unmask email]
Verzonden: woensdag 8 december 2004 18:05
Aan: [login to unmask email]
Onderwerp: NUMLKUS Exceeded for simple Unload




Dear List:

In a DB2 for z/OS V7 environment I executed DSNTIAUL (Bound with
ISOLATION(CS)) in order to unload rows from a table containing a BLOB
column. My input statement is:

SELECT DOC_REF, DOCUMENT
FROM Z.ZLLBLOB;

Where the table is defined as:

COL_NAME COLNO COLTYPE LENGTH
---------+---------+---------+---------+-----
DOC_REF 1 INTEGER 4
ZLLBLOB 2 ROWID 17
DOCUMENT 3 BLOB 4

Upon execution, DSNTIAUL begins to unload lots (millions)
ofrows.However,iteventuallyendswitha-904, thus:

DSNT501I !DT11 DSNILMCL RESOURCE UNAVAILABLE 714
CORRELATION-ID=<JOBNAME>
CONNECTION-ID=BATCH
LUW-ID=*
REASON 00C90096
TYPE 00000F01
NAME 0ECF.0059.1E0092EBE5EDD9092604015BFE84.00001

And the "00C90096" means: Exceeded NUMLKUS.

We've got NUMLKUS set at 15,000, so I have some difficulty believing that
I'm taking so many locks. Yes, I can fix this by specifying "WITH UR", but
I'm still puzzled about the locking bevavior.

Any ideas, anyone?

Lock Lyon
Compuware Corp
----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Marcel Harleman

Re: NUMLKUS Exceeded for simple Unload
(in response to Infodemic B.V. Douwe van Sluis)
Hi,

have a look at the DB2 Administration Guide, chapter 30, paragraph on
LOB locks. The following is the text on LOB lock duration:

(....)
Locks on LOBs are taken when they are needed and are usually released
at commit. However, if that LOB value is assigned to a LOB locator,
the S lock remains until the application commits.
If the application uses HOLD LOCATOR, the LOB lock is not freed until
the first commit operation after a FREE LOCATOR statement is issued,
or until the thread is deallocated.
A note about held cursors: If a cursor is defined WITH HOLD, LOB locks
are held through commit operations.
(....)

DSNTIAUL does not commit. In combination with the LOCKMAX 0 which is
the default for LOCKSIZE LOB you don't get lock-escalation to prevent
you from hitting the ceiling of NUMLKTUS.

Suggestion: change the LOCKMAX on the LOB tablespace to a value that
will cause lock-escalation to occur. Or change the LOCKSIZE for un
unload to tablespace. Or write a program that unloads the data issuing
a couple of commits. Or is it possible to do a LOCK TABLESPACE via the
SQL-option of DSNTIAUL?

HTH

Marcel.

>Dear List:
>
>In a DB2 for z/OS V7 environment I executed DSNTIAUL (Bound with
>ISOLATION(CS)) in order to unload rows from a table containing a BLOB
>column. My input statement is:
>
> SELECT DOC_REF, DOCUMENT
> FROM Z.ZLLBLOB;
>
>Where the table is defined as:
>
>COL_NAME COLNO COLTYPE LENGTH
>---------+---------+---------+---------+-----
>DOC_REF 1 INTEGER 4
>ZLLBLOB 2 ROWID 17
>DOCUMENT 3 BLOB 4
>
>Upon execution, DSNTIAUL begins to unload lots (millions) of rows.
>However, it eventually ends with a -904, thus:
>
>DSNT501I !DT11 DSNILMCL RESOURCE UNAVAILABLE 714
> CORRELATION-ID=<JOBNAME>
> CONNECTION-ID=BATCH
> LUW-ID=*
> REASON 00C90096
> TYPE 00000F01
> NAME 0ECF.0059.1E0092EBE5EDD9092604015BFE84.00001
>
>And the "00C90096" means: Exceeded NUMLKUS.
>
>We've got NUMLKUS set at 15,000, so I have some difficulty believing that
>I'm taking so many locks. Yes, I can fix this by specifying "WITH UR",
>but I'm still puzzled about the locking bevavior.
>
>Any ideas, anyone?
>
>Lock Lyon
>Compuware Corp
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Robin Willoughby

Re: NUMLKUS Exceeded for simple Unload
(in response to Marcel Harleman)
Hi,
we hit a similar problem and basically do as Marcel suggests - here's a bit
from one of our UNLOAD jobs

//SYSTSIN DD *
DSN SYSTEM(DSN2)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB61) -
PARMS('SQL') - <---- NB: PARMS('SQL')
LIB('DSN2.RUNLIB.LOAD')
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
LOCK TABLE
XXX.A_BASE_TABLE <---- base table
IN SHARE MODE;
LOCK TABLE
XXX.LOB_DATA <-- Lob table
IN SHARE MODE;
SELECT * FROM
XXX.A_BASE_TABLE;
/*

cheers,

Robin



On Thu, 9 Dec 2004 06:42:42 +0100, Marcel Harleman
<[login to unmask email]> wrote:

>Hi,
>
>have a look at the DB2 Administration Guide, chapter 30, paragraph on
>LOB locks. The following is the text on LOB lock duration:
>
>(....)
>Locks on LOBs are taken when they are needed and are usually released
>at commit. However, if that LOB value is assigned to a LOB locator,
>the S lock remains until the application commits.
>If the application uses HOLD LOCATOR, the LOB lock is not freed until
>the first commit operation after a FREE LOCATOR statement is issued,
>or until the thread is deallocated.
>A note about held cursors: If a cursor is defined WITH HOLD, LOB locks
>are held through commit operations.
>(....)
>
>DSNTIAUL does not commit. In combination with the LOCKMAX 0 which is
>the default for LOCKSIZE LOB you don't get lock-escalation to prevent
>you from hitting the ceiling of NUMLKTUS.
>
>Suggestion: change the LOCKMAX on the LOB tablespace to a value that
>will cause lock-escalation to occur. Or change the LOCKSIZE for un
>unload to tablespace. Or write a program that unloads the data issuing
>a couple of commits. Or is it possible to do a LOCK TABLESPACE via the
>SQL-option of DSNTIAUL?
>
>HTH
>
>Marcel.
>
>>Dear List:
>>
>>In a DB2 for z/OS V7 environment I executed DSNTIAUL (Bound with
>>ISOLATION(CS)) in order to unload rows from a table containing a BLOB
>>column. My input statement is:
>>
>> SELECT DOC_REF, DOCUMENT
>> FROM Z.ZLLBLOB;
>>
>>Where the table is defined as:
>>
>>COL_NAME COLNO COLTYPE LENGTH
>>---------+---------+---------+---------+-----
>>DOC_REF 1 INTEGER 4
>>ZLLBLOB 2 ROWID 17
>>DOCUMENT 3 BLOB 4
>>
>>Upon execution, DSNTIAUL begins to unload lots (millions) of rows.
>>However, it eventually ends with a -904, thus:
>>
>>DSNT501I !DT11 DSNILMCL RESOURCE UNAVAILABLE 714
>> CORRELATION-ID=<JOBNAME>
>> CONNECTION-ID=BATCH
>> LUW-ID=*
>> REASON 00C90096
>> TYPE 00000F01
>> NAME 0ECF.0059.1E0092EBE5EDD9092604015BFE84.00001
>>
>>And the "00C90096" means: Exceeded NUMLKUS.
>>
>>We've got NUMLKUS set at 15,000, so I have some difficulty believing that
>>I'm taking so many locks. Yes, I can fix this by specifying "WITH UR",
>>but I'm still puzzled about the locking bevavior.
>>
>>Any ideas, anyone?
>>
>>Lock Lyon
>>Compuware Corp
>>
>>--------------------------------------------------------------------------
-------
>>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

laura miller

Re: NUMLKUS Exceeded for simple Unload
(in response to Robin Willoughby)
I will be out of the office until Friday December 10. I will respond
to your message as soon as possible when I return. If you need
immediate assistance, please contact either Bill Boyd or the Help Desk.



>>> DB2-L 12/09/04 04:41 >>>

Hi,
we hit a similar problem and basically do as Marcel suggests - here's a
bit
from one of our UNLOAD jobs

//SYSTSIN DD *
DSN SYSTEM(DSN2)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB61) -
PARMS('SQL') - <---- NB: PARMS('SQL')
LIB('DSN2.RUNLIB.LOAD')
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
LOCK TABLE
XXX.A_BASE_TABLE <---- base table
IN SHARE MODE;
LOCK TABLE
XXX.LOB_DATA <-- Lob table
IN SHARE MODE;
SELECT * FROM
XXX.A_BASE_TABLE;
/*

cheers,

Robin



On Thu, 9 Dec 2004 06:42:42 +0100, Marcel Harleman
<[login to unmask email]> wrote:

>Hi,
>
>have a look at the DB2 Administration Guide, chapter 30, paragraph on
>LOB locks. The following is the text on LOB lock duration:
>
>(....)
>Locks on LOBs are taken when they are needed and are usually released
>at commit. However, if that LOB value is assigned to a LOB locator,
>the S lock remains until the application commits.
>If the application uses HOLD LOCATOR, the LOB lock is not freed until
>the first commit operation after a FREE LOCATOR statement is issued,
>or until the thread is deallocated.
>A note about held cursors: If a cursor is defined WITH HOLD, LOB locks
>are held through commit operations.
>(....)
>
>DSNTIAUL does not commit. In combination with the LOCKMAX 0 which is
>the default for LOCKSIZE LOB you don't get lock-escalation to prevent
>you from hitting the ceiling of NUMLKTUS.
>
>Suggestion: change the LOCKMAX on the LOB tablespace to a value that
>will cause lock-escalation to occur. Or change the LOCKSIZE for un
>unload to tablespace. Or write a program that unloads the data issuing
>a couple of commits. Or is it possible to do a LOCK TABLESPACE via the
>SQL-option of DSNTIAUL?
>
>HTH
>
>Marcel.
>
>>Dear List:
>>
>>In a DB2 for z/OS V7 environment I executed DSNTIAUL (Bound with
>>ISOLATION(CS)) in order to unload rows from a table containing a BLOB
>>column. My input statement is:
>>
>> SELECT DOC_REF, DOCUMENT
>> FROM Z.ZLLBLOB;
>>
>>Where the table is defined as:
>>
>>COL_NAME COLNO COLTYPE LENGTH
>>---------+---------+---------+---------+-----
>>DOC_REF 1 INTEGER 4
>>ZLLBLOB 2 ROWID 17
>>DOCUMENT 3 BLOB 4
>>
>>Upon execution, DSNTIAUL begins to unload lots (millions) of rows.
>>However, it eventually ends with a -904, thus:
>>
>>DSNT501I !DT11 DSNILMCL RESOURCE UNAVAILABLE 714
>> CORRELATION-ID=<JOBNAME>
>> CONNECTION-ID=BATCH
>> LUW-ID=*
>> REASON 00C90096
>> TYPE 00000F01
>> NAME 0ECF.0059.1E0092EBE5EDD9092604015BFE84.00001
>>
>>And the "00C90096" means: Exceeded NUMLKUS.
>>
>>We've got NUMLKUS set at 15,000, so I have some difficulty believing
that
>>I'm taking so many locks. Yes, I can fix this by specifying "WITH
UR",
>>but I'm still puzzled about the locking bevavior.
>>
>>Any ideas, anyone?
>>
>>Lock Lyon
>>Compuware Corp
>>
>>--------------------------------------------------------------------------
-------
>>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: NUMLKUS Exceeded for simple Unload
(in response to laura miller)
Robin,

Your solution is interesting, and I've tried it. One snag is that to do a
Lock Table on an auxiliary table one must have DBADM, SYSADM, or SYSCTRL
authority (or be the database owner). I assume that your code snippet is
from a production job that has the appropriate authority.

I believe that we'll move ahead with this solution. Thanks to all for
your help!

Lock Lyon





Robin WIlloughby <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/09/2004 04:41 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: NUMLKUS Exceeded for simple Unload






Hi,
we hit a similar problem and basically do as Marcel suggests - here's a
bit
from one of our UNLOAD jobs

//SYSTSIN DD *
DSN SYSTEM(DSN2)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB61) -
PARMS('SQL') - <---- NB: PARMS('SQL')
LIB('DSN2.RUNLIB.LOAD')
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
LOCK TABLE
XXX.A_BASE_TABLE <---- base table
IN SHARE MODE;
LOCK TABLE
XXX.LOB_DATA <-- Lob table
IN SHARE MODE;
SELECT * FROM
XXX.A_BASE_TABLE;
/*

cheers,

Robin




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm