More LOB lock issues

Denise M Gantz

More LOB lock issues
I have a 25-partition base table and 25 LOB auxiliary tables. We were
requested to do an unload of data from our production system using specific
data parameters to create a load file for a test system.

//SYSTSIN DD *
DSN SYSTEM(D2P1)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') -
LIB('D2P1.DSNLOAD')
END
/*
//SYSIN DD *
SELECT *
FROM PROD.MSG A
WHERE A.MSG_ID IN
(SELECT MSG_ID
FROM PROD.MSG_SEARCH
WHERE CRT_TS >=timestamp('2009-10-19-00.00.00'))

When attempting to run the unload job, I encountered NUMLKUS abends

DSNT493I SQL ERROR DURING SQL STATEMENT FETCH , TABLE
PROD.MSG
DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION
CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90096,
TYPE OF
RESOURCE 00000F01, AND RESOURCE NAME
011B.02D9.48C58543C272321A2904010
DSNT418I SQLSTATE = 57011 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNXRFN SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = -131 0 42 -1 0 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'FFFFFF7D' X'00000000' X'0000002A'
X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION
ACF0C038 ACF2 LOGONID ATTRIBUTES HAVE REPLACED DEFAULT
USER ATTRIBUTES
READY
DSN SYSTEM(D2P1)
DSN
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') LIB
('D2P1.DSNLOAD')
DSN
END
READY
END

These LOBs were originally created with LOCKSIZE LOB. To get past my
lock issue, I changed the LOCKSIZE to TABLESPACE to get my unload job
to run. I then changed the LOCKSIZE back to LOB. This seems to have
created a new problem.
There is a batch job that runs every night against these same tablespaces. It
is a simple extract program; it opens a cursor, fetches a row, and writes it
out to a flat file. This job has run for two years with no issues; after I did the
LOCKSIZE change, it got this same LOB lock error; the only way we could
get it to run was to again change the LOCKSIZE on the LOB tablespaces
back to TABLESPACE. The job used to run in about 2 hours; now since
setting LOCKSIZE TABLESPACE it is running 4-5 hours, which is affecting
the batch cycle window.
Any ideas why making the change to the LOCKSIZE would cause this? I
would have expected that when I changed LOCKSIZE back to LOB, which
was what the tablespaces were created with and have been set at for two
years, that nothing in the processing would have changed. Why would the
batch job now be getting this LOB lock error? Would something have
happened "behind the scenes" when the locksize was changed that would
now be causing the LOB tablespaces to behave differently?

Denise Gantz
HP Enterprise Services
[login to unmask email]

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Roy Boxwell

Re: More LOB lock issues
(in response to Denise M Gantz)
I see no-one has had a go at answering this....If I recall correctly when
you go from TS back to LOB you must REBIND any applications...(It might
auto-rebind) so it could well be that either

a) You should rebind to get performance back
or
b) A rebind killed your performance

Time to check the EXPLAIN data...


Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert




Denise Gantz <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
01.12.2009 19:45
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] More LOB lock issues






I have a 25-partition base table and 25 LOB auxiliary tables. We were
requested to do an unload of data from our production system using
specific
data parameters to create a load file for a test system.

//SYSTSIN DD *
DSN SYSTEM(D2P1)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') -
LIB('D2P1.DSNLOAD')
END
/*
//SYSIN DD *
SELECT *
FROM PROD.MSG A
WHERE A.MSG_ID IN
(SELECT MSG_ID
FROM PROD.MSG_SEARCH
WHERE CRT_TS >=timestamp('2009-10-19-00.00.00'))

When attempting to run the unload job, I encountered NUMLKUS abends

DSNT493I SQL ERROR DURING SQL STATEMENT FETCH , TABLE
PROD.MSG
DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION
CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90096,
TYPE OF
RESOURCE 00000F01, AND RESOURCE NAME
011B.02D9.48C58543C272321A2904010
DSNT418I SQLSTATE = 57011 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNXRFN SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = -131 0 42 -1 0 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'FFFFFF7D' X'00000000' X'0000002A'
X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION
ACF0C038 ACF2 LOGONID ATTRIBUTES HAVE REPLACED DEFAULT
USER ATTRIBUTES
READY
DSN SYSTEM(D2P1)
DSN
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') LIB
('D2P1.DSNLOAD')
DSN
END
READY
END

These LOBs were originally created with LOCKSIZE LOB. To get past my
lock issue, I changed the LOCKSIZE to TABLESPACE to get my unload job
to run. I then changed the LOCKSIZE back to LOB. This seems to have
created a new problem.
There is a batch job that runs every night against these same tablespaces.
It
is a simple extract program; it opens a cursor, fetches a row, and writes
it
out to a flat file. This job has run for two years with no issues; after I
did the
LOCKSIZE change, it got this same LOB lock error; the only way we could
get it to run was to again change the LOCKSIZE on the LOB tablespaces
back to TABLESPACE. The job used to run in about 2 hours; now since
setting LOCKSIZE TABLESPACE it is running 4-5 hours, which is affecting
the batch cycle window.
Any ideas why making the change to the LOCKSIZE would cause this? I
would have expected that when I changed LOCKSIZE back to LOB, which
was what the tablespaces were created with and have been set at for two
years, that nothing in the processing would have changed. Why would the
batch job now be getting this LOB lock error? Would something have
happened "behind the scenes" when the locksize was changed that would
now be causing the LOB tablespaces to behave differently?

Denise Gantz
HP Enterprise Services
[login to unmask email]

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Robert Catterall

Re: More LOB lock issues
(in response to Roy Boxwell)
I don't think that the program will be auto-rebound as a result of an ALTER
TABLESPACE LOCKSIZE LOB -- I'm pretty sure that you'll have to manually
rebind the batch program to get it to use LOB- versus TABLESPACE-level
locking.

When you ALTER to a larger lock size (e.g., LOB to TABLESPACE), a static SQL
statement targeting a table in the altered tablespace will use that larger
lock size the next time it is executed. When you ALTER to a smaller lock
size (e.g., TABLESPACE to LOB), a static SQL statement targeting a table in
the altered tablespace will continue to use the old, larger lock size until
the package associated with the statement is rebound.

Robert


On Wed, Dec 2, 2009 at 3:47 AM, Roy Boxwell <[login to unmask email]> wrote:

>
> I see no-one has had a go at answering this....If I recall correctly when
> you go from TS back to LOB you must REBIND any applications...(It might
> auto-rebind) so it could well be that either
>
> a) You should rebind to get performance back
> or
> b) A rebind killed your performance
>
> Time to check the EXPLAIN data...
>
> *
> Roy Boxwell*
> SOFTWARE ENGINEERING GMBH
> -Product Development-*
> Robert-Stolz-Straße 5
> 40470 Düsseldorf/Germany
> Tel. +49 (0)211 96149-675
> Fax +49 (0)211 96149-32
> Email: [login to unmask email]
> http://www.seg.de
>
> Software Engineering GmbH
> Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Siegfried Fürst, Gerhard Schubert
> *
>
>
> *Denise Gantz <[login to unmask email]>*
> Gesendet von: IDUG DB2-L <[login to unmask email]>
>
> 01.12.2009 19:45
> Bitte antworten an
> IDUG DB2-L <[login to unmask email]>
>
> An
> [login to unmask email]
> Kopie
> Thema
> [DB2-L] More LOB lock issues
>
>
>
>
> I have a 25-partition base table and 25 LOB auxiliary tables. We were
> requested to do an unload of data from our production system using specific
>
> data parameters to create a load file for a test system.
>
> //SYSTSIN DD *
> DSN SYSTEM(D2P1)
> RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') -
> LIB('D2P1.DSNLOAD')
> END
> /*
> //SYSIN DD *
> SELECT *
> FROM PROD.MSG A
> WHERE A.MSG_ID IN
> (SELECT MSG_ID
> FROM PROD.MSG_SEARCH
> WHERE CRT_TS >=timestamp('2009-10-19-00.00.00'))
>
> When attempting to run the unload job, I encountered NUMLKUS abends
>
> DSNT493I SQL ERROR DURING SQL STATEMENT FETCH , TABLE
> PROD.MSG
> DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION
> CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90096,
> TYPE OF
> RESOURCE 00000F01, AND RESOURCE NAME
> 011B.02D9.48C58543C272321A2904010
> DSNT418I SQLSTATE = 57011 SQLSTATE RETURN
> CODE
> DSNT415I SQLERRP = DSNXRFN SQL PROCEDURE DETECTING
> ERROR
> DSNT416I SQLERRD = -131 0 42 -1 0 0 SQL DIAGNOSTIC
> INFORMATION
> DSNT416I SQLERRD = X'FFFFFF7D' X'00000000' X'0000002A'
> X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
> INFORMATION
>
> ACF0C038 ACF2 LOGONID ATTRIBUTES HAVE REPLACED DEFAULT
> USER ATTRIBUTES
> READY
>
> DSN SYSTEM(D2P1)
>
> DSN
>
> RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') LIB
> ('D2P1.DSNLOAD')
> DSN
>
> END
>
> READY
>
> END
>
> These LOBs were originally created with LOCKSIZE LOB. To get past my
> lock issue, I changed the LOCKSIZE to TABLESPACE to get my unload job
> to run. I then changed the LOCKSIZE back to LOB. This seems to have
> created a new problem.
> There is a batch job that runs every night against these same tablespaces.
> It
> is a simple extract program; it opens a cursor, fetches a row, and writes
> it
> out to a flat file. This job has run for two years with no issues; after I
> did the
> LOCKSIZE change, it got this same LOB lock error; the only way we could
> get it to run was to again change the LOCKSIZE on the LOB tablespaces
> back to TABLESPACE. The job used to run in about 2 hours; now since
> setting LOCKSIZE TABLESPACE it is running 4-5 hours, which is affecting
> the batch cycle window.
> Any ideas why making the change to the LOCKSIZE would cause this? I
> would have expected that when I changed LOCKSIZE back to LOB, which
> was what the tablespaces were created with and have been set at for two
> years, that nothing in the processing would have changed. Why would the
> batch job now be getting this LOB lock error? Would something have
> happened "behind the scenes" when the locksize was changed that would
> now be causing the LOB tablespaces to behave differently?
>
> Denise Gantz
> HP Enterprise Services
> [login to unmask email]
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/db2-videos.html has hundreds of video presentations!
> Did you miss out on attending an IDUG conference?
> Many of the presentations were recorded and are available on our website!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
> the home of IDUG's DB2-L
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L