lock escalation

Jose Antonio

lock escalation
Hi group!!

We are thinking of disabling lock scalation in our system. How have you managed this issue in your installation?

Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem parameter) to 10000 as a job will
abort reaching this value.

Thank you very much!!!!

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Caja Mediterraneo
Arquitectura y Seguridad TI
Administración Base Datos
__________________________




Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Walter Janißen

AW: lock escalation
(in response to Jose Antonio)
Hi Jose

I am also thinking about that, because I also don't like lock escalation. Who knows, how often a lock escalation fails, but other applications are suspended, because they have to wait for the lock escalation to fail? We are running with the option of lock escalation since the first DB2 release, so I fear to change anything, because jobs can abend, which currently work fine.

I don't know how big your tables are, but I feel 10,000 much too less. What are 10,000 rows or pages for big tables: nothing.

Do you have jobs in place using DSNTEP2 doing corrections on production tables? Or do you always write a program to do that?

It's difficult to consider all what can happen, if you change these settings in that way.

regards
Walter Janissen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 08:52
An: [login to unmask email]
Betreff: [DB2-L] lock escalation

Hi group!!

We are thinking of disabling lock scalation in our system. How have you managed this issue in your installation?

Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem parameter) to 10000 as a job will abort reaching this value.

Thank you very much!!!!

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Caja Mediterraneo
Arquitectura y Seguridad TI
Administración Base Datos
__________________________




Este correo electronico es confidencial. Si lo ha recibido por error, por favor contacte con el remitente y destruya su contenido. Toda la informacion relativa a la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact the sender and delete it from your system. All information about Personal Data Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Jose Antonio

Re: lock escalation
(in response to Walter Janißen)
Hi Walter Janissen!

We have a bimonthly report summarizing the number of times a job has a lock scalation,
so it's limited the impact. Also at table space level NUMLKTS es 1000 so we think if we
increase the value to 10000 we are not having very much jobs arriving that value.

We really have different jobs typology.

What is your NUMLKTS value?

Regards

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Administración Base Datos
__________________________


-----Mensaje original-----
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Walter Janißen
Enviado el: viernes, 14 de enero de 2011 10:37
Para: [login to unmask email]
Asunto: [DB2-L] AW: lock escalation

Hi Jose

I am also thinking about that, because I also don't like lock escalation. Who knows, how often a lock escalation fails, but other applications are suspended, because they have to wait for the lock escalation to fail? We are running with the option of lock escalation since the first DB2 release, so I fear to change anything, because jobs can abend, which currently work fine.

I don't know how big your tables are, but I feel 10,000 much too less. What are 10,000 rows or pages for big tables: nothing.

Do you have jobs in place using DSNTEP2 doing corrections on production tables? Or do you always write a program to do that?

It's difficult to consider all what can happen, if you change these settings in that way.

regards
Walter Janissen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 08:52
An: [login to unmask email]
Betreff: [DB2-L] lock escalation

Hi group!!

We are thinking of disabling lock scalation in our system. How have you managed this issue in your installation?

Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem parameter) to 10000 as a job will abort reaching this value.

Thank you very much!!!!

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Caja Mediterraneo
Arquitectura y Seguridad TI
Administración Base Datos
__________________________




Este correo electronico es confidencial. Si lo ha recibido por error, por favor contacte con el remitente y destruya su contenido. Toda la informacion relativa a la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact the sender and delete it from your system. All information about Personal Data Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Walter Janißen

AW: lock escalation
(in response to Jose Antonio)
Hi

o.k. you have a report summarizing the number of lock escalations, but you have no idea, how often a lock escalation gets a timeout.

Regarding NUMLKTS in our installation: We are currently going with 5000, which I feel is far too low. I am in discussion with the system guys to increase it and I have confirmation to increase it to 20,000, which in my opinion is still too low. They are afraid, that they need to much memory, because there can potentially be many users approaching that limit.


Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
Tel.: +49(0)211/477-2928
Fax: +49(0)211/477-2615
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 12:11
An: [login to unmask email]
Betreff: Re: [DB2-L] lock escalation

Hi Walter Janissen!

We have a bimonthly report summarizing the number of times a job has a lock scalation, so it's limited the impact. Also at table space level NUMLKTS es 1000 so we think if we increase the value to 10000 we are not having very much jobs arriving that value.

We really have different jobs typology.

What is your NUMLKTS value?

Regards

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Administración Base Datos
__________________________


-----Mensaje original-----
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Walter Janißen Enviado el: viernes, 14 de enero de 2011 10:37
Para: [login to unmask email]
Asunto: [DB2-L] AW: lock escalation

Hi Jose

I am also thinking about that, because I also don't like lock escalation. Who knows, how often a lock escalation fails, but other applications are suspended, because they have to wait for the lock escalation to fail? We are running with the option of lock escalation since the first DB2 release, so I fear to change anything, because jobs can abend, which currently work fine.

I don't know how big your tables are, but I feel 10,000 much too less. What are 10,000 rows or pages for big tables: nothing.

Do you have jobs in place using DSNTEP2 doing corrections on production tables? Or do you always write a program to do that?

It's difficult to consider all what can happen, if you change these settings in that way.

regards
Walter Janissen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 08:52
An: [login to unmask email]
Betreff: [DB2-L] lock escalation

Hi group!!

We are thinking of disabling lock scalation in our system. How have you managed this issue in your installation?

Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem parameter) to 10000 as a job will abort reaching this value.

Thank you very much!!!!

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Caja Mediterraneo
Arquitectura y Seguridad TI
Administración Base Datos
__________________________




Este correo electronico es confidencial. Si lo ha recibido por error, por favor contacte con el remitente y destruya su contenido. Toda la informacion relativa a la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact the sender and delete it from your system. All information about Personal Data Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Jose Antonio

Re: lock escalation
(in response to Walter Janißen)
Hi!

Is it important how often a lock escalation gets a timeout?! As to whether setting NUMLKTS to zero and to set NUMLKUS to 10000? I'm afraid of this last number. I'm not really sure if it's going to be enough...

Regards.

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43

Administración Base Datos

__________________________



-----Mensaje original-----
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Walter Janißen
Enviado el: viernes, 14 de enero de 2011 12:40
Para: [login to unmask email]
Asunto: [DB2-L] AW: lock escalation

Hi

o.k. you have a report summarizing the number of lock escalations, but you have no idea, how often a lock escalation gets a timeout.

Regarding NUMLKTS in our installation: We are currently going with 5000, which I feel is far too low. I am in discussion with the system guys to increase it and I have confirmation to increase it to 20,000, which in my opinion is still too low. They are afraid, that they need to much memory, because there can potentially be many users approaching that limit.


Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
Tel.: +49(0)211/477-2928
Fax: +49(0)211/477-2615
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 12:11
An: [login to unmask email]
Betreff: Re: [DB2-L] lock escalation

Hi Walter Janissen!

We have a bimonthly report summarizing the number of times a job has a lock scalation, so it's limited the impact. Also at table space level NUMLKTS es 1000 so we think if we increase the value to 10000 we are not having very much jobs arriving that value.

We really have different jobs typology.

What is your NUMLKTS value?

Regards

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Administración Base Datos
__________________________


-----Mensaje original-----
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Walter Janißen Enviado el: viernes, 14 de enero de 2011 10:37
Para: [login to unmask email]
Asunto: [DB2-L] AW: lock escalation

Hi Jose

I am also thinking about that, because I also don't like lock escalation. Who knows, how often a lock escalation fails, but other applications are suspended, because they have to wait for the lock escalation to fail? We are running with the option of lock escalation since the first DB2 release, so I fear to change anything, because jobs can abend, which currently work fine.

I don't know how big your tables are, but I feel 10,000 much too less. What are 10,000 rows or pages for big tables: nothing.

Do you have jobs in place using DSNTEP2 doing corrections on production tables? Or do you always write a program to do that?

It's difficult to consider all what can happen, if you change these settings in that way.

regards
Walter Janissen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 08:52
An: [login to unmask email]
Betreff: [DB2-L] lock escalation

Hi group!!

We are thinking of disabling lock scalation in our system. How have you managed this issue in your installation?

Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem parameter) to 10000 as a job will abort reaching this value.

Thank you very much!!!!

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Caja Mediterraneo
Arquitectura y Seguridad TI
Administración Base Datos
__________________________




Este correo electronico es confidencial. Si lo ha recibido por error, por favor contacte con el remitente y destruya su contenido. Toda la informacion relativa a la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact the sender and delete it from your system. All information about Personal Data Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Walter Janißen

AW: lock escalation
(in response to Jose Antonio)
Hi

My intention to avoid lock escalation is, that programs trying to escalate can suspend many others, until they themself get their timeout. These programs suspend others for nothing and if they needn't escalate, maybe nobody suffers. So for me, it would be interesting to know, if many lock escalation attempts timeout.

What is your reason to disable lock escalation? Are there a lot of applications suffering from a timeout, because there was a lock escalation?

regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
Tel.: +49(0)211/477-2928
Fax: +49(0)211/477-2615
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 13:13
An: [login to unmask email]
Betreff: Re: [DB2-L] lock escalation

Hi!

Is it important how often a lock escalation gets a timeout?! As to whether setting NUMLKTS to zero and to set NUMLKUS to 10000? I'm afraid of this last number. I'm not really sure if it's going to be enough...

Regards.

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43

Administración Base Datos

__________________________



-----Mensaje original-----
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Walter Janißen Enviado el: viernes, 14 de enero de 2011 12:40
Para: [login to unmask email]
Asunto: [DB2-L] AW: lock escalation

Hi

o.k. you have a report summarizing the number of lock escalations, but you have no idea, how often a lock escalation gets a timeout.

Regarding NUMLKTS in our installation: We are currently going with 5000, which I feel is far too low. I am in discussion with the system guys to increase it and I have confirmation to increase it to 20,000, which in my opinion is still too low. They are afraid, that they need to much memory, because there can potentially be many users approaching that limit.


Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
Tel.: +49(0)211/477-2928
Fax: +49(0)211/477-2615
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 12:11
An: [login to unmask email]
Betreff: Re: [DB2-L] lock escalation

Hi Walter Janissen!

We have a bimonthly report summarizing the number of times a job has a lock scalation, so it's limited the impact. Also at table space level NUMLKTS es 1000 so we think if we increase the value to 10000 we are not having very much jobs arriving that value.

We really have different jobs typology.

What is your NUMLKTS value?

Regards

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Administración Base Datos
__________________________


-----Mensaje original-----
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Walter Janißen Enviado el: viernes, 14 de enero de 2011 10:37
Para: [login to unmask email]
Asunto: [DB2-L] AW: lock escalation

Hi Jose

I am also thinking about that, because I also don't like lock escalation. Who knows, how often a lock escalation fails, but other applications are suspended, because they have to wait for the lock escalation to fail? We are running with the option of lock escalation since the first DB2 release, so I fear to change anything, because jobs can abend, which currently work fine.

I don't know how big your tables are, but I feel 10,000 much too less. What are 10,000 rows or pages for big tables: nothing.

Do you have jobs in place using DSNTEP2 doing corrections on production tables? Or do you always write a program to do that?

It's difficult to consider all what can happen, if you change these settings in that way.

regards
Walter Janissen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 08:52
An: [login to unmask email]
Betreff: [DB2-L] lock escalation

Hi group!!

We are thinking of disabling lock scalation in our system. How have you managed this issue in your installation?

Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem parameter) to 10000 as a job will abort reaching this value.

Thank you very much!!!!

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Caja Mediterraneo
Arquitectura y Seguridad TI
Administración Base Datos
__________________________




Este correo electronico es confidencial. Si lo ha recibido por error, por favor contacte con el remitente y destruya su contenido. Toda la informacion relativa a la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact the sender and delete it from your system. All information about Personal Data Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Dave Nance

Re: AW: lock escalation
(in response to Walter Janißen)
Jose,  
   I am a big believer in not allowing escalation. If a batch job wants to
process tons of data, then they should issue a periodic commit. If they do not
commit their work at some interval, then the job will abend when they reach max
number of locks, this way I attempt to ensure they are not locking eveyone else
out of a table.
    I have, also, pushed for a generic routine to perform these commits. This
routine would read the commit frequency for a job each time it performs a commit
from a control table, so that we can change the frequency on the fly, in case it
is timing out other processes(lower the UOW) or it is taking too many
commits(raise the UOW). This process would, also, record any restart logic, if
it existed for the job running, such as last commited key value.
   I do, believe, you have to have another look at the zparms you want to change
though. In particular, have a look at NUMLKTS. This is how many locks are
allowed on the tablespace. You control escalation at the tablespace level with
the keyword LOCKMAX 0.
   If, you change it as you specified, there is no maximum number of locks on
the tablespace and you can lock every single row/page in the tablespace. If you
do this keep in mind the effect that could have on your IRLM and the effect on
concurrent threads. Typically, NUMLKTS should be less than your setting of
NUMLKUS, as a typical thread may be accessing multiple tablespaces in a unit of
work.
 
David Nance


-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 08:52
An: [login to unmask email]
Betreff: [DB2-L] lock escalation

Hi group!!

We are thinking of disabling lock scalation in our system. How have you managed
this issue in your installation?

Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem parameter) to
zero and LOCKS PER USER field (NUMLKUS subsystem parameter) to 10000 as a job
will abort reaching this value.

Thank you very much!!!!

__________________________
José A Morcillo Valenciano
Tfno.:      +34 965 90 51 43
Caja Mediterraneo
Arquitectura y Seguridad TI
Administración Base Datos
__________________________




_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

[login to unmask email]

Re: AW: lock escalation
(in response to Dave Nance)
I absolutely agree with Dave. I am a fan of disabling lock escalation in
most cases.
My logic is a (batch) program which asks for more than thousands of locks
is abnormal
and there must be some problems. So I have to suspend or abend it, before
OLTP or
anyone else be impacted. That 00C90096 does.

In our shop, all application tablespaces are LOCKMAX 0, and we set
NUMLKUS=100000.
It works for years. Rarely, a few "normal" programs will hit it, then DBA
enlarge
NUMLKUS, compile ZPARM, SET SYSPARM to bypass. And the most important is,
tell
developers to change application logic or raise commit frenquency, that's
all.

Finally, YMMV.


William Huang
Senior Manager, Dept. System
ICBC Data Center(Shanghai)
Mail:[login to unmask email]




Dave Nance <[login to unmask email]>
发件人: IDUG DB2-L <[login to unmask email]>
2011-01-15 00:46
请答复 给
IDUG DB2-L <[login to unmask email]>


收件人
[login to unmask email]
抄送

主题
Re: [DB2-L] AW: lock escalation






Jose,
I am a big believer in not allowing escalation. If a batch job wants to
process tons of data, then they should issue a periodic commit. If they do
not commit their work at some interval, then the job will abend when they
reach max number of locks, this way I attempt to ensure they are not
locking eveyone else out of a table.
I have, also, pushed for a generic routine to perform these commits.
This routine would read the commit frequency for a job each time it
performs a commit from a control table, so that we can change the
frequency on the fly, in case it is timing out other processes(lower the
UOW) or it is taking too many commits(raise the UOW). This process would,
also, record any restart logic, if it existed for the job running, such as
last commited key value.
I do, believe, you have to have another look at the zparms you want to
change though. In particular, have a look at NUMLKTS. This is how many
locks are allowed on the tablespace. You control escalation at the
tablespace level with the keyword LOCKMAX 0.
If, you change it as you specified, there is no maximum number of locks
on the tablespace and you can lock every single row/page in the
tablespace. If you do this keep in mind the effect that could have on your
IRLM and the effect on concurrent threads. Typically, NUMLKTS should be
less than your setting of NUMLKUS, as a typical thread may be accessing
multiple tablespaces in a unit of work.

David Nance


-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 08:52
An: [login to unmask email]
Betreff: [DB2-L] lock escalation

Hi group!!

We are thinking of disabling lock scalation in our system. How have you
managed this issue in your installation?

Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem
parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem parameter)
to 10000 as a job will abort reaching this value.

Thank you very much!!!!

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Caja Mediterraneo
Arquitectura y Seguridad TI
Administración Base Datos
__________________________







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 * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Max Scarpa

Re: AW: lock escalation
(in response to hhuang@DCCSH.ICBC.COM.CN)
I agree

In all but last company where I worked we didn't allow lock escalation as
we preferred to 'tune' application and to understand why it was escalating
instead of permitting it.
This usually turned out as a bad access path or awful commit policy
(mainly in batch jobs) or application/program issue and in almost all
cases the problem of having a 00C90096 error was the minor problem as we
had a lot of benefits after tuning it. The problem is that in test and in
prod, as usual, things are different and in many cases you use/have few
data in your test tables.

In last company I monitored lock escalation and even if sometimes it's
difficult to understand which program did it, we were able to detect the
most problematic and to tune them. Which number must be used as NUMLKUS
strogly depend on your shop. but we used values ranging from 10000 to
50000 and more in one case.

My personal opinion is to disable lock escalation as it's can be seen as a
application issue in most cases, but it depends on company's policy.BTW
there are some papers dealing with this topic.

Just an opinion

Max Scarpa


> From: [login to unmask email]
> To: [login to unmask email]
> Date: 17/01/2011 07.02
> Subject: Re: [DB2-L] AW: lock escalation
> Sent by: IDUG DB2-L <[login to unmask email]>
>
>
> I absolutely agree with Dave. I am a fan of disabling lock
> escalation in most cases.
> My logic is a (batch) program which asks for more than thousands of
> locks is abnormal
> and there must be some problems. So I have to suspend or abend it,
> before OLTP or
> anyone else be impacted. That 00C90096 does.
>
> In our shop, all application tablespaces are LOCKMAX 0, and we set
> NUMLKUS=100000.
> It works for years. Rarely, a few "normal" programs will hit it,
> then DBA enlarge
> NUMLKUS, compile ZPARM, SET SYSPARM to bypass. And the most
> important is, tell
> developers to change application logic or raise commit frenquency,
that's all.
>
> Finally, YMMV.
>
>
> William Huang
> Senior Manager, Dept. System
> ICBC Data Center(Shanghai)
> Mail:[login to unmask email]
>
>

>
> Dave Nance <[login to unmask email]>
> 发件人: IDUG DB2-L <[login to unmask email]>
> 2011-01-15 00:46
>
> 请答复 给
> IDUG DB2-L <[login to unmask email]>
>
> 收件人
>
> [login to unmask email]
>
> 抄送
>
> 主题
>
> Re: [DB2-L] AW: lock escalation
>
>
>
>
> Jose,
> I am a big believer in not allowing escalation. If a batch job
> wants to process tons of data, then they should issue a periodic
> commit. If they do not commit their work at some interval, then the
> job will abend when they reach max number of locks, this way I
> attempt to ensure they are not locking eveyone else out of a table.
> I have, also, pushed for a generic routine to perform these
> commits. This routine would read the commit frequency for a job each
> time it performs a commit from a control table, so that we can
> change the frequency on the fly, in case it is timing out other
> processes(lower the UOW) or it is taking too many commits(raise the
> UOW). This process would, also, record any restart logic, if it
> existed for the job running, such as last commited key value.
> I do, believe, you have to have another look at the zparms you
> want to change though. In particular, have a look at NUMLKTS. This
> is how many locks are allowed on the tablespace. You control
> escalation at the tablespace level with the keyword LOCKMAX 0.
> If, you change it as you specified, there is no maximum number of
> locks on the tablespace and you can lock every single row/page in
> the tablespace. If you do this keep in mind the effect that could
> have on your IRLM and the effect on concurrent threads. Typically,
> NUMLKTS should be less than your setting of NUMLKUS, as a typical
> thread may be accessing multiple tablespaces in a unit of work.
>
> David Nance
>
>
> -----Ursprüngliche Nachricht-----
> Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
> Gesendet: Freitag, 14. Januar 2011 08:52
> An: [login to unmask email]
> Betreff: [DB2-L] lock escalation
>
> Hi group!!
>
> We are thinking of disabling lock scalation in our system. How have
> you managed this issue in your installation?
>
> Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem
> parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem
> parameter) to 10000 as a job will abort reaching this value.
>
> Thank you very much!!!!
>
> __________________________
> José A Morcillo Valenciano
> Tfno.: +34 965 90 51 43
> Caja Mediterraneo
> Arquitectura y Seguridad TI
> Administración Base Datos
> __________________________
>
>
>
>
>

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

> [image removed]
> 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 * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Jose Antonio

Re: AW: lock escalation
(in response to Max Scarpa)
Hi Max!

Do you know where I can find these papers?

Thanks!
Jos¨¦




________________________________
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Max Scarpa
Enviado el: lunes, 17 de enero de 2011 8:47
Para: [login to unmask email]
Asunto: Re: [DB2-L] AW: lock escalation

I agree

In all but last company where I worked we didn't allow lock escalation as we preferred to 'tune' application and to understand why it was escalating instead of permitting it.
This usually turned out as a bad access path or awful commit policy (mainly in batch jobs) or application/program issue and in almost all cases the problem of having a 00C90096 error was the minor problem as we had a lot of benefits after tuning it. The problem is that in test and in prod, as usual, things are different and in many cases you use/have few data in your test tables.

In last company I monitored lock escalation and even if sometimes it's difficult to understand which program did it, we were able to detect the most problematic and to tune them. Which number must be used as NUMLKUS strogly depend on your shop. but we used values ranging from 10000 to 50000 and more in one case.

My personal opinion is to disable lock escalation as it's can be seen as a application issue in most cases, but it depends on company's policy.BTW there are some papers dealing with this topic.

Just an opinion

Max Scarpa


> From: [login to unmask email]
> To: [login to unmask email]
> Date: 17/01/2011 07.02
> Subject: Re: [DB2-L] AW: lock escalation
> Sent by: IDUG DB2-L <[login to unmask email]>
>
>
> I absolutely agree with Dave. I am a fan of disabling lock
> escalation in most cases.
> My logic is a (batch) program which asks for more than thousands of
> locks is abnormal
> and there must be some problems. So I have to suspend or abend it,
> before OLTP or
> anyone else be impacted. That 00C90096 does.
>
> In our shop, all application tablespaces are LOCKMAX 0, and we set
> NUMLKUS=100000.
> It works for years. Rarely, a few "normal" programs will hit it,
> then DBA enlarge
> NUMLKUS, compile ZPARM, SET SYSPARM to bypass. And the most
> important is, tell
> developers to change application logic or raise commit frenquency, that's all.
>
> Finally, YMMV.
>
>
> William Huang
> Senior Manager, Dept. System
> ICBC Data Center(Shanghai)
> Mail:[login to unmask email]
>
>

>
> Dave Nance <[login to unmask email]>
> ·¢¼þÈË: IDUG DB2-L <[login to unmask email]>
> 2011-01-15 00:46
>
> Çë´ð¸´ ¸ø
> IDUG DB2-L <[login to unmask email]>
>
> ÊÕ¼þÈË
>
> [login to unmask email]
>
> ³­ËÍ
>
> Ö÷Ìâ
>
> Re: [DB2-L] AW: lock escalation
>
>
>
>
> Jose,
> I am a big believer in not allowing escalation. If a batch job
> wants to process tons of data, then they should issue a periodic
> commit. If they do not commit their work at some interval, then the
> job will abend when they reach max number of locks, this way I
> attempt to ensure they are not locking eveyone else out of a table.
> I have, also, pushed for a generic routine to perform these
> commits. This routine would read the commit frequency for a job each
> time it performs a commit from a control table, so that we can
> change the frequency on the fly, in case it is timing out other
> processes(lower the UOW) or it is taking too many commits(raise the
> UOW). This process would, also, record any restart logic, if it
> existed for the job running, such as last commited key value.
> I do, believe, you have to have another look at the zparms you
> want to change though. In particular, have a look at NUMLKTS. This
> is how many locks are allowed on the tablespace. You control
> escalation at the tablespace level with the keyword LOCKMAX 0.
> If, you change it as you specified, there is no maximum number of
> locks on the tablespace and you can lock every single row/page in
> the tablespace. If you do this keep in mind the effect that could
> have on your IRLM and the effect on concurrent threads. Typically,
> NUMLKTS should be less than your setting of NUMLKUS, as a typical
> thread may be accessing multiple tablespaces in a unit of work.
>
> David Nance
>
>
> -----Urspr¨¹ngliche Nachricht-----
> Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
> Gesendet: Freitag, 14. Januar 2011 08:52
> An: [login to unmask email]
> Betreff: [DB2-L] lock escalation
>
> Hi group!!
>
> We are thinking of disabling lock scalation in our system. How have
> you managed this issue in your installation?
>
> Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem
> parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem
> parameter) to 10000 as a job will abort reaching this value.
>
> Thank you very much!!!!
>
> __________________________
> Jos¨¦ A Morcillo Valenciano
> Tfno.: +34 965 90 51 43
> Caja Mediterraneo
> Arquitectura y Seguridad TI
> Administraci¨®n Base Datos
> __________________________
>
>
>
>
>

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

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

[Introducing IBM(r) DB2(r) 10 for z/OS ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

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 >

Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Jose Antonio

Re: lock escalation
(in response to Jose Antonio)
Hi Walter!

We have suffered from a job lock escalation during online window this is why we
want to disable it.

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43

Administración Base Datos

__________________________


-----Mensaje original-----
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Walter Janißen
Enviado el: viernes, 14 de enero de 2011 16:06
Para: [login to unmask email]
Asunto: [DB2-L] AW: lock escalation

Hi

My intention to avoid lock escalation is, that programs trying to escalate can suspend many others, until they themself get their timeout. These programs suspend others for nothing and if they needn't escalate, maybe nobody suffers. So for me, it would be interesting to know, if many lock escalation attempts timeout.

What is your reason to disable lock escalation? Are there a lot of applications suffering from a timeout, because there was a lock escalation?

regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
Tel.: +49(0)211/477-2928
Fax: +49(0)211/477-2615
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 13:13
An: [login to unmask email]
Betreff: Re: [DB2-L] lock escalation

Hi!

Is it important how often a lock escalation gets a timeout?! As to whether setting NUMLKTS to zero and to set NUMLKUS to 10000? I'm afraid of this last number. I'm not really sure if it's going to be enough...

Regards.

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43

Administración Base Datos

__________________________



-----Mensaje original-----
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Walter Janißen Enviado el: viernes, 14 de enero de 2011 12:40
Para: [login to unmask email]
Asunto: [DB2-L] AW: lock escalation

Hi

o.k. you have a report summarizing the number of lock escalations, but you have no idea, how often a lock escalation gets a timeout.

Regarding NUMLKTS in our installation: We are currently going with 5000, which I feel is far too low. I am in discussion with the system guys to increase it and I have confirmation to increase it to 20,000, which in my opinion is still too low. They are afraid, that they need to much memory, because there can potentially be many users approaching that limit.


Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
Tel.: +49(0)211/477-2928
Fax: +49(0)211/477-2615
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 12:11
An: [login to unmask email]
Betreff: Re: [DB2-L] lock escalation

Hi Walter Janissen!

We have a bimonthly report summarizing the number of times a job has a lock scalation, so it's limited the impact. Also at table space level NUMLKTS es 1000 so we think if we increase the value to 10000 we are not having very much jobs arriving that value.

We really have different jobs typology.

What is your NUMLKTS value?

Regards

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Administración Base Datos
__________________________


-----Mensaje original-----
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Walter Janißen Enviado el: viernes, 14 de enero de 2011 10:37
Para: [login to unmask email]
Asunto: [DB2-L] AW: lock escalation

Hi Jose

I am also thinking about that, because I also don't like lock escalation. Who knows, how often a lock escalation fails, but other applications are suspended, because they have to wait for the lock escalation to fail? We are running with the option of lock escalation since the first DB2 release, so I fear to change anything, because jobs can abend, which currently work fine.

I don't know how big your tables are, but I feel 10,000 much too less. What are 10,000 rows or pages for big tables: nothing.

Do you have jobs in place using DSNTEP2 doing corrections on production tables? Or do you always write a program to do that?

It's difficult to consider all what can happen, if you change these settings in that way.

regards
Walter Janissen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 08:52
An: [login to unmask email]
Betreff: [DB2-L] lock escalation

Hi group!!

We are thinking of disabling lock scalation in our system. How have you managed this issue in your installation?

Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem parameter) to 10000 as a job will abort reaching this value.

Thank you very much!!!!

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Caja Mediterraneo
Arquitectura y Seguridad TI
Administración Base Datos
__________________________




Este correo electronico es confidencial. Si lo ha recibido por error, por favor contacte con el remitente y destruya su contenido. Toda la informacion relativa a la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact the sender and delete it from your system. All information about Personal Data Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Randy Ebersole

Lock Escalation
(in response to Jose Antonio)
To All, I feel that disabling lock escalation is asking for bigger
problems. Dave mentions IRLM and that is our primary concern here. IF for
some reason there are several batch jobs running and additional
transactional work, the amount of concurrent locks held could be too much
for the IRLM. IF the IRLM would run out of storage to manage concurrent
locks, the results are going to be felt system wide, not just for a single
job or transaction. Is that really worth not managing the application
locking workload ?

Also, another potential impact is that if effective commit logic is not in
place, then the potential for system wide rollback impact comes into play.
And one last area of concern would be if you are in Data Sharing and the
potential impact on the lock structure and coupling facility workload.

So managing lock escalation with the proper settings and also effective
commit logic has a fairly far reaching impact and should be managed to
provide a more robust DB2 environment.


Randy Ebersole
Certified IT Specialist
North American Lab Services
DB2 Information Management, IBM Software Group
[login to unmask email]
Cell Phone - 717-571-1062


|------------>
| From: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|DB2-L automatic digest system <[login to unmask email]> |
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| To: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|[login to unmask email] |
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Date: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|01/18/2011 01:00 AM |
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Subject: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|DB2-L Digest - 17 Jan 2011 to 18 Jan 2011 (#2011-17) |
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Sent by: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|IDUG DB2-L <[login to unmask email]> |
>--------------------------------------------------------------------------------------------------------------------------------------------------|





There are 13 messages totaling 3058 lines in this issue.

Topics of the day:

1. AW: lock escalation (3)
2. DB2 for z/OS V8 - package name
3. DB2 for z/OS Efficiency rating (3)
4. lock escalation
5. The REAL World" with Susan Gausden And Terry Mason, Directors,
Brooklands
Technology Limited, UK' -Friday January 21, 2011 10:00 AM - 11:00 AM
CST
6. Dallas DB2 Forum meeting next week
7. To the DB2 folk in the Houston area (2)
8. [AD] Webinar from Cogito - EZ-Tracer Live Demo and EZ-Index Analyzer &
EZ-XOP

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

Date: Mon, 17 Jan 2011 14:01:47 +0800
From: [login to unmask email]
Subject: Re: AW: lock escalation

I absolutely agree with Dave. I am a fan of disabling lock escalation in
most cases.
My logic is a (batch) program which asks for more than thousands of locks
is abnormal
and there must be some problems. So I have to suspend or abend it, before
OLTP or
anyone else be impacted. That 00C90096 does.

In our shop, all application tablespaces are LOCKMAX 0, and we set
NUMLKUS=100000.
It works for years. Rarely, a few "normal" programs will hit it, then DBA
enlarge
NUMLKUS, compile ZPARM, SET SYSPARM to bypass. And the most important is,
tell
developers to change application logic or raise commit frenquency, that's
all.

Finally, YMMV.


William Huang
Senior Manager, Dept. System
ICBC Data Center(Shanghai)
Mail:[login to unmask email]




Dave Nance <[login to unmask email]>
发件人: IDUG DB2-L <[login to unmask email]>
2011-01-15 00:46
请答复 给
IDUG DB2-L <[login to unmask email]>


收件人
[login to unmask email]
抄送

主题
Re: [DB2-L] AW: lock escalation






Jose,
I am a big believer in not allowing escalation. If a batch job wants to
process tons of data, then they should issue a periodic commit. If they do
not commit their work at some interval, then the job will abend when they
reach max number of locks, this way I attempt to ensure they are not
locking eveyone else out of a table.
I have, also, pushed for a generic routine to perform these commits.
This routine would read the commit frequency for a job each time it
performs a commit from a control table, so that we can change the
frequency on the fly, in case it is timing out other processes(lower the
UOW) or it is taking too many commits(raise the UOW). This process would,
also, record any restart logic, if it existed for the job running, such as
last commited key value.
I do, believe, you have to have another look at the zparms you want to
change though. In particular, have a look at NUMLKTS. This is how many
locks are allowed on the tablespace. You control escalation at the
tablespace level with the keyword LOCKMAX 0.
If, you change it as you specified, there is no maximum number of locks
on the tablespace and you can lock every single row/page in the
tablespace. If you do this keep in mind the effect that could have on your
IRLM and the effect on concurrent threads. Typically, NUMLKTS should be
less than your setting of NUMLKUS, as a typical thread may be accessing
multiple tablespaces in a unit of work.

David Nance


-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 08:52
An: [login to unmask email]
Betreff: [DB2-L] lock escalation

Hi group!!

We are thinking of disabling lock scalation in our system. How have you
managed this issue in your installation?

Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem
parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem parameter)
to 10000 as a job will abort reaching this value.

Thank you very much!!!!

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Caja Mediterraneo
Arquitectura y Seguridad TI
Administración Base Datos
__________________________







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 * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it!
*
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A

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

Date: Mon, 17 Jan 2011 08:47:22 +0100
From: Max Scarpa <[login to unmask email]>
Subject: Re: AW: lock escalation

I agree

In all but last company where I worked we didn't allow lock escalation as
we preferred to 'tune' application and to understand why it was escalating
instead of permitting it.
This usually turned out as a bad access path or awful commit policy
(mainly in batch jobs) or application/program issue and in almost all
cases the problem of having a 00C90096 error was the minor problem as we
had a lot of benefits after tuning it. The problem is that in test and in
prod, as usual, things are different and in many cases you use/have few
data in your test tables.

In last company I monitored lock escalation and even if sometimes it's
difficult to understand which program did it, we were able to detect the
most problematic and to tune them. Which number must be used as NUMLKUS
strogly depend on your shop. but we used values ranging from 10000 to
50000 and more in one case.

My personal opinion is to disable lock escalation as it's can be seen as a
application issue in most cases, but it depends on company's policy.BTW
there are some papers dealing with this topic.

Just an opinion

Max Scarpa


> From: [login to unmask email]
> To: [login to unmask email]
> Date: 17/01/2011 07.02
> Subject: Re: [DB2-L] AW: lock escalation
> Sent by: IDUG DB2-L <[login to unmask email]>
>
>
> I absolutely agree with Dave. I am a fan of disabling lock
> escalation in most cases.
> My logic is a (batch) program which asks for more than thousands of
> locks is abnormal
> and there must be some problems. So I have to suspend or abend it,
> before OLTP or
> anyone else be impacted. That 00C90096 does.
>
> In our shop, all application tablespaces are LOCKMAX 0, and we set
> NUMLKUS=100000.
> It works for years. Rarely, a few "normal" programs will hit it,
> then DBA enlarge
> NUMLKUS, compile ZPARM, SET SYSPARM to bypass. And the most
> important is, tell
> developers to change application logic or raise commit frenquency,
that's all.
>
> Finally, YMMV.
>
>
> William Huang
> Senior Manager, Dept. System
> ICBC Data Center(Shanghai)
> Mail:[login to unmask email]
>
>

>
> Dave Nance <[login to unmask email]>
> 发件人: IDUG DB2-L <[login to unmask email]>
> 2011-01-15 00:46
>
> 请答复 给
> IDUG DB2-L <[login to unmask email]>
>
> 收件人
>
> [login to unmask email]
>
> 抄送
>
> 主题
>
> Re: [DB2-L] AW: lock escalation
>
>
>
>
> Jose,
> I am a big believer in not allowing escalation. If a batch job
> wants to process tons of data, then they should issue a periodic
> commit. If they do not commit their work at some interval, then the
> job will abend when they reach max number of locks, this way I
> attempt to ensure they are not locking eveyone else out of a table.
> I have, also, pushed for a generic routine to perform these
> commits. This routine would read the commit frequency for a job each
> time it performs a commit from a control table, so that we can
> change the frequency on the fly, in case it is timing out other
> processes(lower the UOW) or it is taking too many commits(raise the
> UOW). This process would, also, record any restart logic, if it
> existed for the job running, such as last commited key value.
> I do, believe, you have to have another look at the zparms you
> want to change though. In particular, have a look at NUMLKTS. This
> is how many locks are allowed on the tablespace. You control
> escalation at the tablespace level with the keyword LOCKMAX 0.
> If, you change it as you specified, there is no maximum number of
> locks on the tablespace and you can lock every single row/page in
> the tablespace. If you do this keep in mind the effect that could
> have on your IRLM and the effect on concurrent threads. Typically,
> NUMLKTS should be less than your setting of NUMLKUS, as a typical
> thread may be accessing multiple tablespaces in a unit of work.
>
> David Nance
>
>
> -----Ursprüngliche Nachricht-----
> Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
> Gesendet: Freitag, 14. Januar 2011 08:52
> An: [login to unmask email]
> Betreff: [DB2-L] lock escalation
>
> Hi group!!
>
> We are thinking of disabling lock scalation in our system. How have
> you managed this issue in your installation?
>
> Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem
> parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem
> parameter) to 10000 as a job will abort reaching this value.
>
> Thank you very much!!!!
>
> __________________________
> José A Morcillo Valenciano
> Tfno.: +34 965 90 51 43
> Caja Mediterraneo
> Arquitectura y Seguridad TI
> Administración Base Datos
> __________________________
>
>
>
>
>

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

> [image removed]
> 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 * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it!
*
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi

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

Date: Mon, 17 Jan 2011 10:27:19 +0100
From: Roy Boxwell <[login to unmask email]>
Subject: Re: DB2 for z/OS V8 - package name

You will enter a "world of pain" trying to do all that! A while ago some
other lister wrote up how they have written a universal connector and then
call the correct environment from within thus allowing mixed environment
calls with the same program. (Or you can wait for DB2 10 and the Universal
call stub) Of course if run with the same RLI and IMS has no complaints (I
havent worked with IMS for *years*) then you can have different versions
but the load libs must be correctly matched and of course the correct
versions bound.
On a personal note I *hate* versioning - All it does it create tons of
junk packages in the catalog that no-one remembers to manually FREE which
all have DB2 dependancies that then cause automatic and semi-automatic
rebinds which locks the catalog just as you are doing a production spufi
ALTER....and then it all goes pear shaped.... Yep I really really hate
versioning! If IBM removed it tomorrow I would buy 'em all a beer!


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: Gerhard Schubert



Jim McAlpine <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
14.01.2011 16:38
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
Re: [DB2-L] DB2 for z/OS V8 - package name






On Fri, Jan 14, 2011 at 3:26 PM, Roy Boxwell <[login to unmask email]> wrote:

The VERSION is my best friday afternoon guess...shortly before I depart
the office, take my blue pill, quaff my beer and eat my chips....

Roy Boxwell


OK then, supplementary question. If I want to compile, link and bind the
same program to a CICS load library and an IMS load library and execute
them both against the same DB2 subsystem can I use a different package
version to accomplish this. Or should I use some other method.

Jim McAlpine



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 * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it!
*
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

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

Date: Mon, 17 Jan 2011 11:38:29 -0000
From: "Davage, Marcus" <[login to unmask email]>
Subject: DB2 for z/OS Efficiency rating

Has anyone ever come up with an overall DB2 for z/OS performance
efficiency rating?



What would it involve? Wait time? Getpages? Package statement cost? CPU
cost? Buffer pool usage? Thread usage?



Marcus

Lloyds TSB Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN.
Registered in England and Wales, number 2065. Telephone: 020 7626 1500.
Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ.
Registered in Scotland, number 327000. Telephone: 0870 600 5000

Lloyds TSB Scotland plc. Registered Office: Henry Duncan House, 120 George
Street, Edinburgh EH2 4LH. Registered in Scotland, number 95237. Telephone:
0131 225 4555.
Cheltenham & Gloucester plc. Registered Office: Barnett Way, Gloucester GL4
3RL. Registered in England and Wales, number 2299428. Telephone: 01452
372372.

Lloyds TSB Bank plc, Lloyds TSB Scotland plc, Bank of Scotland plc and
Cheltenham & Gloucester plc are authorised and regulated by the Financial
Services Authority.
Halifax is a division of Bank of Scotland plc. Cheltenham & Gloucester
Savings is a division of Lloyds TSB Bank plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in
Scotland, number 218813. Telephone: 0870 600 5000

Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ.
Registered in Scotland, number 95000. Telephone: 0131 225 4555

This e-mail (including any attachments) is private and confidential and may
contain privileged material. If you have received this e-mail in error,
please notify the sender and delete it (including any attachments)
immediately. You must not copy, distribute, disclose or use any of the
information in it or any attachments.

Telephone calls may be monitored or recorded.

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it!
*
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

Date: Mon, 17 Jan 2011 12:47:26 +0000
From: Jose Antonio <[login to unmask email]>
Subject: Re: AW: lock escalation

Hi Max!

Do you know where I can find these papers?

Thanks!
José




________________________________
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Max Scarpa
Enviado el: lunes, 17 de enero de 2011 8:47
Para: [login to unmask email]
Asunto: Re: [DB2-L] AW: lock escalation

I agree

In all but last company where I worked we didn't allow lock escalation as
we preferred to 'tune' application and to understand why it was escalating
instead of permitting it.
This usually turned out as a bad access path or awful commit policy (mainly
in batch jobs) or application/program issue and in almost all cases the
problem of having a 00C90096 error was the minor problem as we had a lot
of benefits after tuning it. The problem is that in test and in prod, as
usual, things are different and in many cases you use/have few data in your
test tables.

In last company I monitored lock escalation and even if sometimes it's
difficult to understand which program did it, we were able to detect the
most problematic and to tune them. Which number must be used as NUMLKUS
strogly depend on your shop. but we used values ranging from 10000 to 50000
and more in one case.

My personal opinion is to disable lock escalation as it's can be seen as a
application issue in most cases, but it depends on company's policy.BTW
there are some papers dealing with this topic.

Just an opinion

Max Scarpa


> From: [login to unmask email]
> To: [login to unmask email]
> Date: 17/01/2011 07.02
> Subject: Re: [DB2-L] AW: lock escalation
> Sent by: IDUG DB2-L <[login to unmask email]>
>
>
> I absolutely agree with Dave. I am a fan of disabling lock
> escalation in most cases.
> My logic is a (batch) program which asks for more than thousands of
> locks is abnormal
> and there must be some problems. So I have to suspend or abend it,
> before OLTP or
> anyone else be impacted. That 00C90096 does.
>
> In our shop, all application tablespaces are LOCKMAX 0, and we set
> NUMLKUS=100000.
> It works for years. Rarely, a few "normal" programs will hit it,
> then DBA enlarge
> NUMLKUS, compile ZPARM, SET SYSPARM to bypass. And the most
> important is, tell
> developers to change application logic or raise commit frenquency, that's
all.
>
> Finally, YMMV.
>
>
> William Huang
> Senior Manager, Dept. System
> ICBC Data Center(Shanghai)
> Mail:[login to unmask email]
>
>

>
> Dave Nance <[login to unmask email]>
> 发件人: IDUG DB2-L <[login to unmask email]>
> 2011-01-15 00:46
>
> 请答复 给
> IDUG DB2-L <[login to unmask email]>
>
> 收件人
>
> [login to unmask email]
>
> 抄送
>
> 主题
>
> Re: [DB2-L] AW: lock escalation
>
>
>
>
> Jose,
> I am a big believer in not allowing escalation. If a batch job
> wants to process tons of data, then they should issue a periodic
> commit. If they do not commit their work at some interval, then the
> job will abend when they reach max number of locks, this way I
> attempt to ensure they are not locking eveyone else out of a table.
> I have, also, pushed for a generic routine to perform these
> commits. This routine would read the commit frequency for a job each
> time it performs a commit from a control table, so that we can
> change the frequency on the fly, in case it is timing out other
> processes(lower the UOW) or it is taking too many commits(raise the
> UOW). This process would, also, record any restart logic, if it
> existed for the job running, such as last commited key value.
> I do, believe, you have to have another look at the zparms you
> want to change though. In particular, have a look at NUMLKTS. This
> is how many locks are allowed on the tablespace. You control
> escalation at the tablespace level with the keyword LOCKMAX 0.
> If, you change it as you specified, there is no maximum number of
> locks on the tablespace and you can lock every single row/page in
> the tablespace. If you do this keep in mind the effect that could
> have on your IRLM and the effect on concurrent threads. Typically,
> NUMLKTS should be less than your setting of NUMLKUS, as a typical
> thread may be accessing multiple tablespaces in a unit of work.
>
> David Nance
>
>
> -----Ursprüngliche Nachricht-----
> Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
> Gesendet: Freitag, 14. Januar 2011 08:52
> An: [login to unmask email]
> Betreff: [DB2-L] lock escalation
>
> Hi group!!
>
> We are thinking of disabling lock scalation in our system. How have
> you managed this issue in your installation?
>
> Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem
> parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem
> parameter) to 10000 as a job will abort reaching this value.
>
> Thank you very much!!!!
>
> __________________________
> José A Morcillo Valenciano
> Tfno.: +34 965 90 51 43
> Caja Mediterraneo
> Arquitectura y Seguridad TI
> Administración Base Datos
> __________________________
>
>
>
>
>

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

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

[Introducing IBM(r) DB2(r) 10 for z/OS]<
http://www-01.ibm.com/software/data/db2/zos/db2-10/>

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>

Este correo electronico es confidencial. Si lo ha recibido por error, por
favor
contacte con el remitente y destruya su contenido. Toda la informacion
relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion
en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error,
please contact
the sender and delete it from your system. All information about Personal
Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer
section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it!
*
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB

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

Date: Mon, 17 Jan 2011 12:50:21 +0000
From: Jose Antonio <[login to unmask email]>
Subject: Re: lock escalation

Hi Walter!

We have suffered from a job lock escalation during online window this is
why we
want to disable it.

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43

Administración Base Datos

__________________________


-----Mensaje original-----
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Walter Janißen
Enviado el: viernes, 14 de enero de 2011 16:06
Para: [login to unmask email]
Asunto: [DB2-L] AW: lock escalation

Hi

My intention to avoid lock escalation is, that programs trying to escalate
can suspend many others, until they themself get their timeout. These
programs suspend others for nothing and if they needn't escalate, maybe
nobody suffers. So for me, it would be interesting to know, if many lock
escalation attempts timeout.

What is your reason to disable lock escalation? Are there a lot of
applications suffering from a timeout, because there was a lock escalation?

regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
Tel.: +49(0)211/477-2928
Fax: +49(0)211/477-2615
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 13:13
An: [login to unmask email]
Betreff: Re: [DB2-L] lock escalation

Hi!

Is it important how often a lock escalation gets a timeout?! As to whether
setting NUMLKTS to zero and to set NUMLKUS to 10000? I'm afraid of this
last number. I'm not really sure if it's going to be enough...

Regards.

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43

Administración Base Datos

__________________________



-----Mensaje original-----
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Walter Janißen
Enviado el: viernes, 14 de enero de 2011 12:40
Para: [login to unmask email]
Asunto: [DB2-L] AW: lock escalation

Hi

o.k. you have a report summarizing the number of lock escalations, but you
have no idea, how often a lock escalation gets a timeout.

Regarding NUMLKTS in our installation: We are currently going with 5000,
which I feel is far too low. I am in discussion with the system guys to
increase it and I have confirmation to increase it to 20,000, which in my
opinion is still too low. They are afraid, that they need to much memory,
because there can potentially be many users approaching that limit.


Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
Tel.: +49(0)211/477-2928
Fax: +49(0)211/477-2615
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr.
Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 12:11
An: [login to unmask email]
Betreff: Re: [DB2-L] lock escalation

Hi Walter Janissen!

We have a bimonthly report summarizing the number of times a job has a lock
scalation, so it's limited the impact. Also at table space level NUMLKTS es
1000 so we think if we increase the value to 10000 we are not having very
much jobs arriving that value.

We really have different jobs typology.

What is your NUMLKTS value?

Regards

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Administración Base Datos
__________________________


-----Mensaje original-----
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Walter Janißen
Enviado el: viernes, 14 de enero de 2011 10:37
Para: [login to unmask email]
Asunto: [DB2-L] AW: lock escalation

Hi Jose

I am also thinking about that, because I also don't like lock escalation.
Who knows, how often a lock escalation fails, but other applications are
suspended, because they have to wait for the lock escalation to fail? We
are running with the option of lock escalation since the first DB2 release,
so I fear to change anything, because jobs can abend, which currently work
fine.

I don't know how big your tables are, but I feel 10,000 much too less. What
are 10,000 rows or pages for big tables: nothing.

Do you have jobs in place using DSNTEP2 doing corrections on production
tables? Or do you always write a program to do that?

It's difficult to consider all what can happen, if you change these
settings in that way.

regards
Walter Janissen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Systeme Laufzeitarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr.
Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Jose Antonio
Gesendet: Freitag, 14. Januar 2011 08:52
An: [login to unmask email]
Betreff: [DB2-L] lock escalation

Hi group!!

We are thinking of disabling lock scalation in our system. How have you
managed this issue in your installation?

Our idea is to set LOCKS PER TABLE(SPACE) field (NUMLKTS subsystem
parameter) to zero and LOCKS PER USER field (NUMLKUS subsystem parameter)
to 10000 as a job will abort reaching this value.

Thank you very much!!!!

__________________________
José A Morcillo Valenciano
Tfno.: +34 965 90 51 43
Caja Mediterraneo
Arquitectura y Seguridad TI
Administración Base Datos
__________________________




_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

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