DB2 People Soft Compare issue

Srinivas Gopala

DB2 People Soft Compare issue
Hi,

The user is running compare reports in People Soft that is accessing DB2 V9 Database.
Consistently we are getting the below error :

- DSNILMCL RESOURCE UNAVAILABLE 776
CORRELATION-ID=pside.exe
CONNECTION-ID=SERVER
LUW-ID=AC15670A.G1BE.C5334BCE40ED=6
REASON 00C90096
TYPE 00000304
NAME PSHRDMO .PTPRJWK .X'0009C9' '.X'0A'


I have Altered the Tablespaces to have lock size Table or Tablespace. In addition I have reset NUMLKUS to 0.
Earlier NUMLKUS was 10000 and then 30000. Out of desperation I have set it to 0.

We have a Z800 Dual CPU System. The memory allocated to this LPAR that only runs DB2 is 3.2 GB.

But still this error persists.

Regards,
Srinivas G


**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

_____________________________________________________________________

* 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

Jeffrey Schade

Re: DB2 People Soft Compare issue
(in response to Srinivas Gopala)
Hi Srinivas, The error you are getting is because the application is obtaining more locks than are allowed for a single user. You must either increase the NUMLKUS zparm value to allow more locks for a single user or have the application perform more frequent commits to release locks held. We have also, in some cases, had the application code a LOCK TABLE which eliminates all concurrent access but does not acquire more than one lock for the entire table.



00C90096

Explanation:

The page, row or LOB lock on the page or subpage identified by NAME in message DSNT500I or DSNT501I caused the total number of page, row or LOB locks concurrently held to reach the installation maximum number of page, row or LOB locks (NUMLKUS) allowed for a single agent in the system.



Jeff

_________________________________________
Jeffrey Schade

Systems Consultant, Technology Engineering


ISO
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738

FAX: (201) 748-1500
[login to unmask email] <mailto:[login to unmask email]>

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Monday, December 07, 2009 9:58 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 People Soft Compare issue



Hi,



The user is running compare reports in People Soft that is accessing DB2 V9 Database.

Consistently we are getting the below error :



- DSNILMCL RESOURCE UNAVAILABLE 776

CORRELATION-ID=pside.exe

CONNECTION-ID=SERVER

LUW-ID=AC15670A.G1BE.C5334BCE40ED=6

REASON 00C90096

TYPE 00000304

NAME PSHRDMO .PTPRJWK .X'0009C9' '.X'0A'





I have Altered the Tablespaces to have lock size Table or Tablespace. In addition I have reset NUMLKUS to 0.

Earlier NUMLKUS was 10000 and then 30000. Out of desperation I have set it to 0.



We have a Z800 Dual CPU System. The memory allocated to this LPAR that only runs DB2 is 3.2 GB.



But still this error persists.



Regards,

Srinivas G



**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***



________________________________

< http://www.idug.org >

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 >

This email is intended for the recipient only. If you are not the intended recipient please disregard, and do not use the information for any purpose.

Michael Turner

Re: DB2 People Soft Compare issue
(in response to Jeffrey Schade)
Hi Srinivas

The message indicates that row level locking is being used (Type 304), so your alters of locksize to tablespace may not have worked if you are still seeing Type 304 on the errors. Also, the tablespace may be defined with LOCKMAX n (i.e. not SYSTEM). In this case the NUMLKUS is overridden by the tablespace specification.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44.
----- Original Message -----
From: SrinivasG
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Monday, December 07, 2009 2:57 PM
Subject: [DB2-L] DB2 People Soft Compare issue


Hi,



The user is running compare reports in People Soft that is accessing DB2 V9 Database.

Consistently we are getting the below error :



- DSNILMCL RESOURCE UNAVAILABLE 776

CORRELATION-ID=pside.exe

CONNECTION-ID=SERVER

LUW-ID=AC15670A.G1BE.C5334BCE40ED=6

REASON 00C90096

TYPE 00000304

NAME PSHRDMO .PTPRJWK .X'0009C9' '.X'0A'





I have Altered the Tablespaces to have lock size Table or Tablespace. In addition I have reset NUMLKUS to 0.

Earlier NUMLKUS was 10000 and then 30000. Out of desperation I have set it to 0.



We have a Z800 Dual CPU System. The memory allocated to this LPAR that only runs DB2 is 3.2 GB.



But still this error persists.



Regards,

Srinivas G



**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***


------------------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_____________________________________________________________________

* 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

Michael Turner

Re: DB2 People Soft Compare issue
(in response to Michael Turner)
Oops. LOCKMAX overrides NUMLKTS not NUMLKUS. I hate Mondays!

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44.
----- Original Message -----
From: Mike Turner
To: IDUG DB2-L
Sent: Monday, December 07, 2009 4:23 PM
Subject: Re: [DB2-L] DB2 People Soft Compare issue


Hi Srinivas

The message indicates that row level locking is being used (Type 304), so your alters of locksize to tablespace may not have worked if you are still seeing Type 304 on the errors. Also, the tablespace may be defined with LOCKMAX n (i.e. not SYSTEM). In this case the NUMLKUS is overridden by the tablespace specification.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44.
----- Original Message -----
From: SrinivasG
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Monday, December 07, 2009 2:57 PM
Subject: [DB2-L] DB2 People Soft Compare issue


Hi,



The user is running compare reports in People Soft that is accessing DB2 V9 Database.

Consistently we are getting the below error :



- DSNILMCL RESOURCE UNAVAILABLE 776

CORRELATION-ID=pside.exe

CONNECTION-ID=SERVER

LUW-ID=AC15670A.G1BE.C5334BCE40ED=6

REASON 00C90096

TYPE 00000304

NAME PSHRDMO .PTPRJWK .X'0009C9' '.X'0A'





I have Altered the Tablespaces to have lock size Table or Tablespace. In addition I have reset NUMLKUS to 0.

Earlier NUMLKUS was 10000 and then 30000. Out of desperation I have set it to 0.



We have a Z800 Dual CPU System. The memory allocated to this LPAR that only runs DB2 is 3.2 GB.



But still this error persists.



Regards,

Srinivas G



**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***


----------------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_____________________________________________________________________

* 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

Robert Catterall

Re: DB2 People Soft Compare issue
(in response to Michael Turner)
If the PeopleSoft process is using dynamic SQL, and if dynamic statement
caching is being utilized, is it possible that the ALTER TABLESPACE LOCKSIZE
change has had no effect because the statements in question are cached and
have not been re-prepared since the execution of the ALTER TABLESPACE
statement? If so, you could try invalidating the cached statements by way of
a RUNSTATS job with UPDATE NONE and REPORT NO. Then, when the statements are
re-prepared, the new LOCKSIZE specification should be honored.

Also, does the compare involve just SELECT statements against the target
objects? If so, why are so many locks being held? Was the associated package
bound with an isolation level of repeatable read?

Robert


On Mon, Dec 7, 2009 at 9:57 AM, SrinivasG <[login to unmask email]> wrote:

> Hi,
>
>
>
> The user is running compare reports in People Soft that is accessing DB2 V9
> Database.
>
> Consistently we are getting the below error :
>
>
>
> - DSNILMCL RESOURCE UNAVAILABLE 776
>
> CORRELATION-ID=pside.exe
>
> CONNECTION-ID=SERVER
>
> LUW-ID=AC15670A.G1BE.C5334BCE40ED=6
>
> REASON 00C90096
>
> TYPE 00000304
>
> NAME PSHRDMO .PTPRJWK .X'0009C9' '.X'0A'
>
>
>
>
>
> I have Altered the Tablespaces to have lock size Table or Tablespace. In
> addition I have reset NUMLKUS to 0.
>
> Earlier NUMLKUS was 10000 and then 30000. Out of desperation I have set it
> to 0.
>
>
>
> We have a Z800 Dual CPU System. The memory allocated to this LPAR that only
> runs DB2 is 3.2 GB.
>
>
>
> But still this error persists.
>
>
>
> Regards,
>
> Srinivas G
>
>
>
> **************** CAUTION - Disclaimer *****************
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
> for the use of the addressee(s). If you are not the intended recipient, please
> notify the sender by e-mail and delete the original message. Further, you are not
> to copy, disclose, or distribute this e-mail or its contents to any other person and
> any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
> every reasonable precaution to minimize this risk, but is not liable for any damage
> you may sustain as a result of any virus in this e-mail. You should carry out your
> own virus checks before opening the e-mail or attachment. Infosys reserves the
> right to monitor and review the content of all messages sent to or from this e-mail
> address. Messages sent to or from this e-mail address may be stored on the
> Infosys e-mail system.
> ***INFOSYS******** End of Disclaimer ********INFOSYS***
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org >
>
> 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-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

Srinivas Gopala

Re: DB2 People Soft Compare issue
(in response to Robert Catterall)
Hi Mike,

I set LOCKMAX to System for this Tablespace and the compare is working !!
I will keep monitoring.

Thanks to all who replied.

Regards,
Srinivas G

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mike Turner
Sent: Monday, December 07, 2009 9:59 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 People Soft Compare issue

Oops. LOCKMAX overrides NUMLKTS not NUMLKUS. I hate Mondays!

Regards
Mike Turner
Email: [login to unmask email]<mailto:[login to unmask email]>
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk < http://www.michael-turner.ltd.uk >
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44.
----- Original Message -----
From: Mike Turner<mailto:[login to unmask email]>
To: IDUG DB2-L<mailto:[login to unmask email]>
Sent: Monday, December 07, 2009 4:23 PM
Subject: Re: [DB2-L] DB2 People Soft Compare issue

Hi Srinivas

The message indicates that row level locking is being used (Type 304), so your alters of locksize to tablespace may not have worked if you are still seeing Type 304 on the errors. Also, the tablespace may be defined with LOCKMAX n (i.e. not SYSTEM). In this case the NUMLKUS is overridden by the tablespace specification.

Regards
Mike Turner
Email: [login to unmask email]<mailto:[login to unmask email]>
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk < http://www.michael-turner.ltd.uk >
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44.
----- Original Message -----
From: SrinivasG<mailto:[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]<mailto:[login to unmask email]>
Sent: Monday, December 07, 2009 2:57 PM
Subject: [DB2-L] DB2 People Soft Compare issue

Hi,

The user is running compare reports in People Soft that is accessing DB2 V9 Database.
Consistently we are getting the below error :

- DSNILMCL RESOURCE UNAVAILABLE 776
CORRELATION-ID=pside.exe
CONNECTION-ID=SERVER
LUW-ID=AC15670A.G1BE.C5334BCE40ED=6
REASON 00C90096
TYPE 00000304
NAME PSHRDMO .PTPRJWK .X'0009C9' '.X'0A'


I have Altered the Tablespaces to have lock size Table or Tablespace. In addition I have reset NUMLKUS to 0.
Earlier NUMLKUS was 10000 and then 30000. Out of desperation I have set it to 0.

We have a Z800 Dual CPU System. The memory allocated to this LPAR that only runs DB2 is 3.2 GB.

But still this error persists.

Regards,
Srinivas G


**************** CAUTION - Disclaimer *****************

This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely

for the use of the addressee(s). If you are not the intended recipient, please

notify the sender by e-mail and delete the original message. Further, you are not

to copy, disclose, or distribute this e-mail or its contents to any other person and

any such actions are unlawful. This e-mail may contain viruses. Infosys has taken

every reasonable precaution to minimize this risk, but is not liable for any damage

you may sustain as a result of any virus in this e-mail. You should carry out your

own virus checks before opening the e-mail or attachment. Infosys reserves the

right to monitor and review the content of all messages sent to or from this e-mail

address. Messages sent to or from this e-mail address may be stored on the

Infosys e-mail system.

***INFOSYS******** End of Disclaimer ********INFOSYS***


________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

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 >

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

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 >

_____________________________________________________________________

* 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