Problem with LOBVALA setting for (large) XML document construction

Philip Nelson

Problem with LOBVALA setting for (large) XML document construction
Folks,

I'm trying to convert an XML construction SQL. This SQL has been in
production on DB2 for LUW for about 6 years, using data that we crossload
from DB2 for z/OS (at that time we didn't have a DB2 for z/OS version with
pureXML).

We are experiencing -

UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE: REASON 00C900D1.
TYPE OF RESOURCE 00000907.

This appears to be a problem with DSNZPARM LOBVALA, which on our
development system is set to 2048 (KB I believe).

On DB2 for LUW, the XML that we produce comes to about 125 MB in size.

Would the value of LOBVALA have to match this ? Anything else we'd need
to change if we are increasing LOBVALA (any other parameters related) ?

Why is it LOBVALA that is being exceeded, not XMLVALA (which on our system
is set to 2097152) ? Is this just because we are testing the SQL from
Data Studio? At the end of our test we'd want to make this a view and
then use the view with an UNLOAD to produce a file.

Thanks

Phil

Marcus Davage

OT: Problem with LOBVALA setting for (large) XML document construction
(in response to Philip Nelson)
Classification: Public

LOBVALA? Isn’t that where Norse LOBs go when they die?

Regards,
Marcus Davage CEng CITP MBCS
IBM Certified DB2 Database Administrator
LinkedIn http://uk.linkedin.com/in/marcusdavage | IDUG http://www.idug.org/p/us/sn/uid=24500 | Twitter http://twitter.com/spufidoo | About.me http://about.me/spufidoo
From: Philip Nelson [mailto:[login to unmask email]
Sent: 05 February 2018 14:52
To: [login to unmask email]
Subject: [DB2-L] - Problem with LOBVALA setting for (large) XML document construction

-- This email has reached the Bank via an external source --

Folks,

I'm trying to convert an XML construction SQL. This SQL has been in production on DB2 for LUW for about 6 years, using data that we crossload from DB2 for z/OS (at that time we didn't have a DB2 for z/OS version with pureXML).

We are experiencing -

UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE: REASON 00C900D1. TYPE OF RESOURCE 00000907.

This appears to be a problem with DSNZPARM LOBVALA, which on our development system is set to 2048 (KB I believe).

On DB2 for LUW, the XML that we produce comes to about 125 MB in size.

Would the value of LOBVALA have to match this ? Anything else we'd need to change if we are increasing LOBVALA (any other parameters related) ?

Why is it LOBVALA that is being exceeded, not XMLVALA (which on our system is set to 2097152) ? Is this just because we are testing the SQL from Data Studio? At the end of our test we'd want to make this a view and then use the view with an UNLOAD to produce a file.

Thanks

Phil

-----End Original Message-----


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

Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500.

Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801.

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Halifax is a division of Bank of Scotland plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

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.

Raymond Bell

Problem with LOBVALA setting for (large) XML document construction
(in response to Marcus Davage)
Only if they die in battle, Marcus.

That joke certainly deserves to die howwibwy, howwibwy…

Raymond Bell
Db2
Hosting Services, Technology
Royal Bank of Scotland Group
3rd Floor Regents House
40-42 Islington High Street
London N1 8XL
Mob: +44 (0) 7894 608214
Email: [login to unmask email]<mailto:[login to unmask email]>

The content of this email is confidential unless stated otherwise.
[cid:[login to unmask email]

From: Davage, Marcus (ITS Database Services - DB2) [mailto:[login to unmask email]
Sent: 05 February 2018 15:16
To: [login to unmask email]
Subject: [DB2-L] - OT: Problem with LOBVALA setting for (large) XML document construction


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************

Classification: Public
LOBVALA? Isn’t that where Norse LOBs go when they die?

Regards,
Marcus Davage CEng CITP MBCS
IBM Certified DB2 Database Administrator
LinkedIn http://uk.linkedin.com/in/marcusdavage | IDUG http://www.idug.org/p/us/sn/uid=24500 | Twitter http://twitter.com/spufidoo | About.me http://about.me/spufidoo
From: Philip Nelson [mailto:[login to unmask email]
Sent: 05 February 2018 14:52
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Problem with LOBVALA setting for (large) XML document construction

-- This email has reached the Bank via an external source --

Folks,

I'm trying to convert an XML construction SQL. This SQL has been in production on DB2 for LUW for about 6 years, using data that we crossload from DB2 for z/OS (at that time we didn't have a DB2 for z/OS version with pureXML).

We are experiencing -

UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE: REASON 00C900D1. TYPE OF RESOURCE 00000907.

This appears to be a problem with DSNZPARM LOBVALA, which on our development system is set to 2048 (KB I believe).

On DB2 for LUW, the XML that we produce comes to about 125 MB in size.

Would the value of LOBVALA have to match this ? Anything else we'd need to change if we are increasing LOBVALA (any other parameters related) ?

Why is it LOBVALA that is being exceeded, not XMLVALA (which on our system is set to 2097152) ? Is this just because we are testing the SQL from Data Studio? At the end of our test we'd want to make this a view and then use the view with an UNLOAD to produce a file.

Thanks

Phil

-----End Original Message-----


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

Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500.

Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801.

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Halifax is a division of Bank of Scotland plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

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.

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com
Attachments

  • image001.png (6.7k)

Joe Geller

RE: Problem with LOBVALA setting for (large) XML document construction
(in response to Philip Nelson)

Phil,

The default for XMLVALA is much higher than for LOBVALA (200MB vs 10MB as of V10) which could be why you hit LOBVALA first.  I'm not sure exactly how the two parameters are used for XML. I do know that starting in V11 one or both were deprecated.  They limit how big a LOB or XML a process can handle, but with larger memories today that is an unnecessary limitation.  So I would make them both big enough for any XML you might be using.

Here are a couple of links related to them.  The first is an article (on the IDUG Content Blog) by Jane Man.  The second is a forum thread.

http://www.idug.org/p/bl/et/blogid=477&blogaid=483

http://www.idug.org/p/fo/et/thread=43109

Joe

In Reply to Philip Nelson:

Folks,

I'm trying to convert an XML construction SQL. This SQL has been in
production on DB2 for LUW for about 6 years, using data that we crossload
from DB2 for z/OS (at that time we didn't have a DB2 for z/OS version with
pureXML).

We are experiencing -

UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE: REASON 00C900D1.
TYPE OF RESOURCE 00000907.

This appears to be a problem with DSNZPARM LOBVALA, which on our
development system is set to 2048 (KB I believe).

On DB2 for LUW, the XML that we produce comes to about 125 MB in size.

Would the value of LOBVALA have to match this ? Anything else we'd need
to change if we are increasing LOBVALA (any other parameters related) ?

Why is it LOBVALA that is being exceeded, not XMLVALA (which on our system
is set to 2097152) ? Is this just because we are testing the SQL from
Data Studio? At the end of our test we'd want to make this a view and
then use the view with an UNLOAD to produce a file.

Thanks

Phil

Philip Nelson

Problem with LOBVALA setting for (large) XML document construction
(in response to Joe Geller)
Thanks Joe. Very useful.

We're on V11 CM at the moment - who knows what will happen when we get the
NFM ...

It does look like I need an uplift on these values. I'll maybe reach out
to Jane for more guidance, as she does seem to be the expert on all things
XML on z/OS.

Phil

On 5 February 2018 at 15:33, Joe Geller <[login to unmask email]> wrote:

> Phil,
>
> The default for XMLVALA is much higher than for LOBVALA (200MB vs 10MB as
> of V10) which could be why you hit LOBVALA first. I'm not sure exactly how
> the two parameters are used for XML. I do know that starting in V11 one or
> both were deprecated. They limit how big a LOB or XML a process can
> handle, but with larger memories today that is an unnecessary limitation.
> So I would make them both big enough for any XML you might be using.
>
> Here are a couple of links related to them. The first is an article (on
> the IDUG Content Blog) by Jane Man. The second is a forum thread.
>
> http://www.idug.org/p/bl/et/blogid=477&blogaid=483
>
> http://www.idug.org/p/fo/et/thread=43109
>
> Joe
>
> In Reply to Philip Nelson:
>
> Folks,
>
> I'm trying to convert an XML construction SQL. This SQL has been in
> production on DB2 for LUW for about 6 years, using data that we crossload
> from DB2 for z/OS (at that time we didn't have a DB2 for z/OS version with
> pureXML).
>
> We are experiencing -
>
> UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE: REASON 00C900D1.
> TYPE OF RESOURCE 00000907.
>
> This appears to be a problem with DSNZPARM LOBVALA, which on our
> development system is set to 2048 (KB I believe).
>
> On DB2 for LUW, the XML that we produce comes to about 125 MB in size.
>
> Would the value of LOBVALA have to match this ? Anything else we'd need
> to change if we are increasing LOBVALA (any other parameters related) ?
>
> Why is it LOBVALA that is being exceeded, not XMLVALA (which on our system
> is set to 2097152) ? Is this just because we are testing the SQL from
> Data Studio? At the end of our test we'd want to make this a view and
> then use the view with an UNLOAD to produce a file.
>
> Thanks
>
> Phil
>
>
> -----End Original Message-----
>

Raymond Bell

Problem with LOBVALA setting for (large) XML document construction
(in response to Philip Nelson)
OK, so semi-seriously, at a previous site I have a vague recollection of hitting LOBVALA in one or two Q-Rep subscriptions. At least one sub included a table with a LOB column and occasionally a 10MB+ bad boy would come across the pipe. Or at least try to. I think Db2 baulked before MQ did. Tried to convince the indentured Sysprog to increase the limit but seeing as That’s How We’ve Always Set It I got no joy.

PS: Joe, LOBVALA does indeed look to have been deprecated in V11. Woo-hoo! No need for that site to wait for their sysprog to retire; just wait for V11 NFM to come along. Hmmm… about the same time frame either way, knowing that place.

Cheers,


Raymond

Raymond Bell
Db2
Hosting Services, Technology
Royal Bank of Scotland Group
3rd Floor Regents House
40-42 Islington High Street
London N1 8XL
Mob: +44 (0) 7894 608214
Email: [login to unmask email]<mailto:[login to unmask email]>

The content of this email is confidential unless stated otherwise.
[cid:[login to unmask email]

From: Philip Nelson [mailto:[login to unmask email]
Sent: 05 February 2018 15:49
To: [login to unmask email]
Subject: [DB2-L] - RE: Problem with LOBVALA setting for (large) XML document construction


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************

Thanks Joe. Very useful.

We're on V11 CM at the moment - who knows what will happen when we get the NFM ...

It does look like I need an uplift on these values. I'll maybe reach out to Jane for more guidance, as she does seem to be the expert on all things XML on z/OS.

Phil

On 5 February 2018 at 15:33, Joe Geller <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Phil,

The default for XMLVALA is much higher than for LOBVALA (200MB vs 10MB as of V10) which could be why you hit LOBVALA first. I'm not sure exactly how the two parameters are used for XML. I do know that starting in V11 one or both were deprecated. They limit how big a LOB or XML a process can handle, but with larger memories today that is an unnecessary limitation. So I would make them both big enough for any XML you might be using.

Here are a couple of links related to them. The first is an article (on the IDUG Content Blog) by Jane Man. The second is a forum thread.

http://www.idug.org/p/bl/et/blogid=477&blogaid=483

http://www.idug.org/p/fo/et/thread=43109

Joe

In Reply to Philip Nelson:
Folks,

I'm trying to convert an XML construction SQL. This SQL has been in
production on DB2 for LUW for about 6 years, using data that we crossload
from DB2 for z/OS (at that time we didn't have a DB2 for z/OS version with
pureXML).

We are experiencing -

UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE: REASON 00C900D1.
TYPE OF RESOURCE 00000907.

This appears to be a problem with DSNZPARM LOBVALA, which on our
development system is set to 2048 (KB I believe).

On DB2 for LUW, the XML that we produce comes to about 125 MB in size.

Would the value of LOBVALA have to match this ? Anything else we'd need
to change if we are increasing LOBVALA (any other parameters related) ?

Why is it LOBVALA that is being exceeded, not XMLVALA (which on our system
is set to 2097152) ? Is this just because we are testing the SQL from
Data Studio? At the end of our test we'd want to make this a view and
then use the view with an UNLOAD to produce a file.

Thanks

Phil

-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (“RBS” or “us”) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you give your consent to the monitoring of your e-mail communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com
Attachments

  • image001.png (6.7k)