xLOBs and daily management

Max Scarpa

xLOBs and daily management
Esteemed listers

First of all best wishes for a new & happy 2006, may this year bring a
zilliard of dollars to all listers and tons of high-pressure and
high-temperature carbon, better know as diamond, to all DB2-L ladies.
Or whatever that can make you happy......

We have a project of implementing a new application with CLOBs and BLOBs
(and XML) and for this reason I'm reading redbook and some papers. The
redbook 'Large objects with DB2 for z/OS and OS/390' is good and provides a
good picture for table(spaces) and indexes management as BP,reorg etc.

What I'd like to know if there are other issue in daily management,
strange situation or problem occurred managing xLOBs. For example as
suggested I'm planning to use LOG NO for tables but of course this could
generate some problems in recovering a tablespace. Or as there's ROWID
involved if there are issues moving data from prod to test. And so on.

Any hint is appreciated. Thank you in advance & best regards


Max Scarpa

Certified ' Run away before they catch you !' DB2 sysprog
Certified 'Watch your step' warning strip

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

Avram Friedman

Re: xLOBs and daily management
(in response to Max Scarpa)
Hi Max
Happy 2006 to you as well.
You are reading RedBooks? I thought you just collected them.

Some of the problems I have experienced with LOBs and BLOBs are
1. Many retrieval functions / utilities do not work. Which ones are a moving target and is highly dependent on which vendor is hyping the the quality of support at the second but the basic problem is its a trick for standard access methods to support physical blocks greater than 32K. Things that work by physical block like IBM's copy and recovery tend to work, things that work by logical row or column like load and unload tend to fail. Mind you the reality and hype change here on a daily basis.

2. LOG vs no LOG of LOBS. One consideration here is space considerations for the AUX data set. If you do log lobs there is the log volume. If you don't log them your AUX data set doubles in size as the before and after image is maintained there so choose your poison.

3. Do you ever plan on doing any deletes or updates to existing lobs. If so be aware freed space is never reused in the AUX space. Do you think I said never reused until reorged? I did not there is no such thing as reorging an AUX space, The space is never re used unless you try to fool mother nature.
The drill for fooling mother nature goes something like
Copy to a shadow structure via SQL INSERT from
(cant use unload / load because they don't work)
DROP and recreate target
COPY from shadow to live via SQL INSERT from
Did some one say RUN STATS, BINDS, Authorizations, RI etc?

Welcome to the wonderful world of LOBs and
be aware some people say "Size does count"







Max Scarpa <[login to unmask email]> wrote:
Esteemed listers

First of all best wishes for a new & happy 2006, may this year bring a
zilliard of dollars to all listers and tons of high-pressure and
high-temperature carbon, better know as diamond, to all DB2-L ladies.
Or whatever that can make you happy......

We have a project of implementing a new application with CLOBs and BLOBs
(and XML) and for this reason I'm reading redbook and some papers. The
redbook 'Large objects with DB2 for z/OS and OS/390' is good and provides a
good picture for table(spaces) and indexes management as BP,reorg etc.

What I'd like to know if there are other issue in daily management,
strange situation or problem occurred managing xLOBs. For example as
suggested I'm planning to use LOG NO for tables but of course this could
generate some problems in recovering a tablespace. Or as there's ROWID
involved if there are issues moving data from prod to test. And so on.

Any hint is appreciated. Thank you in advance & best regards


Max Scarpa

Certified ' Run away before they catch you !' DB2 sysprog
Certified 'Watch your step' warning strip

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




Avram Friedman
(877)311-0480 Voice Mail
[login to unmask email]
Http://www.IBMsysProg.com




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

James Rohal

Re: xLOBs and daily management
(in response to Avram Friedman)
Max,

The only issues I've had is dealing with space. You can reorg the LOB's
(shrlevel none) after deleting old data, but it will not redefine the
dataset to eliminate extents. You will see the space available as
freespace in a listcat however.


If you want to have the datasets recreated to change the space allocations,
the process I use:

- set the status to 'UT'
- image copy the LOB
- alter the priqty and secqty
- recover to the image copy
- rebuild the index
- run the check utility on the LOB and Base tables
- set the status back to 'RW' and runstat both the LOB and Base tables




James Rohal
DB2 Data Base Administrator
Office Phone: 614-331-8113
Fax: 614-331-5860
Cell phone: 614-374-2793
Email: [login to unmask email]



Max Scarpa
<[login to unmask email]
> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> [DB2-L] xLOBs and daily management


12/30/2005 06:59
AM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-L.ORG>






Esteemed listers

First of all best wishes for a new & happy 2006, may this year bring a
zilliard of dollars to all listers and tons of high-pressure and
high-temperature carbon, better know as diamond, to all DB2-L ladies.
Or whatever that can make you happy......

We have a project of implementing a new application with CLOBs and BLOBs
(and XML) and for this reason I'm reading redbook and some papers. The
redbook 'Large objects with DB2 for z/OS and OS/390' is good and provides a
good picture for table(spaces) and indexes management as BP,reorg etc.

What I'd like to know if there are other issue in daily management,
strange situation or problem occurred managing xLOBs. For example as
suggested I'm planning to use LOG NO for tables but of course this could
generate some problems in recovering a tablespace. Or as there's ROWID
involved if there are issues moving data from prod to test. And so on.

Any hint is appreciated. Thank you in advance & best regards


Max Scarpa

Certified ' Run away before they catch you !' DB2 sysprog
Certified 'Watch your step' warning strip

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

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

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

Myron Miller

Re: xLOBs and daily management
(in response to James Rohal)
Max,
I'd fully concur with Avram and Jim's comments. Space management and the use
of utilities is a real problem with LOBS/BLOBS. Space issues have caused my
client many issues with them.

Personally I feel that IBM has put some features into DB2 without fully
functional utilities to support the features. I think it's another case where
they wanted to trumpet that they had support for some features without fully
understanding all the real production issues for that feature. But this is
strictly my own personal opinion.

Myron

--- Avram Friedman <[login to unmask email]> wrote:

> Hi Max
> Happy 2006 to you as well.
> You are reading RedBooks? I thought you just collected them.
>
> Some of the problems I have experienced with LOBs and BLOBs are
> 1. Many retrieval functions / utilities do not work. Which ones are a
> moving target and is highly dependent on which vendor is hyping the the
> quality of support at the second but the basic problem is its a trick for
> standard access methods to support physical blocks greater than 32K. Things
> that work by physical block like IBM's copy and recovery tend to work, things
> that work by logical row or column like load and unload tend to fail. Mind
> you the reality and hype change here on a daily basis.
>
> 2. LOG vs no LOG of LOBS. One consideration here is space considerations
> for the AUX data set. If you do log lobs there is the log volume. If you
> don't log them your AUX data set doubles in size as the before and after
> image is maintained there so choose your poison.
>
> 3. Do you ever plan on doing any deletes or updates to existing lobs. If
> so be aware freed space is never reused in the AUX space. Do you think I
> said never reused until reorged? I did not there is no such thing as
> reorging an AUX space, The space is never re used unless you try to fool
> mother nature.
> The drill for fooling mother nature goes something like
> Copy to a shadow structure via SQL INSERT from
> (cant use unload / load because they don't work)
> DROP and recreate target
> COPY from shadow to live via SQL INSERT from
> Did some one say RUN STATS, BINDS, Authorizations, RI etc?
>
> Welcome to the wonderful world of LOBs and
> be aware some people say "Size does count"
>
>
>
>
>
>
>
> Max Scarpa <[login to unmask email]> wrote:
> Esteemed listers
>
> First of all best wishes for a new & happy 2006, may this year bring a
> zilliard of dollars to all listers and tons of high-pressure and
> high-temperature carbon, better know as diamond, to all DB2-L ladies.
> Or whatever that can make you happy......
>
> We have a project of implementing a new application with CLOBs and BLOBs
> (and XML) and for this reason I'm reading redbook and some papers. The
> redbook 'Large objects with DB2 for z/OS and OS/390' is good and provides a
> good picture for table(spaces) and indexes management as BP,reorg etc.
>
> What I'd like to know if there are other issue in daily management,
> strange situation or problem occurred managing xLOBs. For example as
> suggested I'm planning to use LOG NO for tables but of course this could
> generate some problems in recovering a tablespace. Or as there's ROWID
> involved if there are issues moving data from prod to test. And so on.
>
> Any hint is appreciated. Thank you in advance & best regards
>
>
> Max Scarpa
>
> Certified ' Run away before they catch you !' DB2 sysprog
> Certified 'Watch your step' warning strip
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
> "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
> The IDUG List Admins can be reached at [login to unmask email] Find
> out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>
>
>
>
> Avram Friedman
> (877)311-0480 Voice Mail
> [login to unmask email]
> Http://www.IBMsysProg.com
>
>
>
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
> "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
> The IDUG List Admins can be reached at [login to unmask email] Find
> out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>

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

Max Scarpa

Re: xLOBs and daily management
(in response to Myron Miller)
Hi Avram, Jim and Myron

Thank you for replies.

As I never stop learning, I've to read redbooks and papers :-))) . I did
some test in the past with xLOBs but I hadn't a real application using it
so it was a mere exercise with few rows.

I was awared of problems after reorgs with space, I read some posts in
DB2-L and agree with Avram that size matters. And yes I agree from my (very
limited) tests that this feature seems to me not yet 'mature' and fully
supported and its usage is not so user-friendly as in other platforms.
Even some chat with some IBMers gave me this impression. For this reason I
posted my request to hear other listers' experience, if any.

Our application will start slowly, but probably it'll 'explode' in the next
future so I'd like to be prepared for the worst cases......if
possible.....but is it possible ?

Thank you again & best regards

Max Scarpa
Certified 'If I eat so many chocolates I'll become a BLOB as well'

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

Isaac Yassin

Re: xLOBs and daily management
(in response to Max Scarpa)
Hi,

Another venue :-)

We had a CLOB system ("had" is the correct time:-) )
After being hit by all the "good" mentioned before benefits we decided that we'll convert it to VARCHAR(32000) and use "sequence"
numbers for managing the order of the rows.
We had to change some programs, but it was a good decision for us. YMMV.
We had objects up to 200KB with the majority in the range 40-80KB.
I did not test it for bigger objects.


Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Max Scarpa
Sent: Friday, December 30, 2005 5:31 PM
To: [login to unmask email]
Subject: Re: [DB2-L] xLOBs and daily management

Hi Avram, Jim and Myron

Thank you for replies.

As I never stop learning, I've to read redbooks and papers :-))) . I did some test in the past with xLOBs but I hadn't a real
application using it so it was a mere exercise with few rows.

I was awared of problems after reorgs with space, I read some posts in DB2-L and agree with Avram that size matters. And yes I
agree from my (very
limited) tests that this feature seems to me not yet 'mature' and fully supported and its usage is not so user-friendly as in other
platforms.
Even some chat with some IBMers gave me this impression. For this reason I posted my request to hear other listers' experience, if
any.

Our application will start slowly, but probably it'll 'explode' in the next future so I'd like to be prepared for the worst
cases......if possible.....but is it possible ?

Thank you again & best regards

Max Scarpa
Certified 'If I eat so many chocolates I'll become a BLOB as well'

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

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

Max Scarpa

Re: xLOBs and daily management
(in response to Isaac Yassin)
Hi ISaac
This application will manage object up to 1 Mb as CLOB and XML each so 32k
is not enough, we have to use xLOBs.

But I'm afraid it'll be a hard project, for space requested 'in primis' and
for managing object. BTW how big are dataspace where xLOBs are used ? Just
curious.

Happy new year 2006

Max Scarpa

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

Isaac Yassin

Re: xLOBs and daily management
(in response to Max Scarpa)
Hi Max,

Happy new year 2006 - that's near 2GB :-)))

Currently we don't have the @#$@ LOBZ, so I don't have number.
You'll have to rely on locators as much as you can and really move around those BIG objects as last resort.
We were happy to ditch them, but we were lucky.
Watch for I/O and paging.

Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Max Scarpa
Sent: Saturday, December 31, 2005 11:36 AM
To: [login to unmask email]
Subject: Re: [DB2-L] xLOBs and daily management

Hi ISaac
This application will manage object up to 1 Mb as CLOB and XML each so 32k is not enough, we have to use xLOBs.

But I'm afraid it'll be a hard project, for space requested 'in primis' and for managing object. BTW how big are dataspace where
xLOBs are used ? Just curious.

Happy new year 2006

Max Scarpa

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

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