Use of LOBs

Bob Pingston

Use of LOBs
As a systems guy, design is not one of my strengths. So I post to the
list for advice.
We are thinking of using a LOB as a "scratch pad" for an application that
works with large amounts of data. Its not that the volume is large, its
the "record size" that is large. In the size of about 1.2MB in length.
Currently this is a VSAM application, but it is being altered to use DB2.
Any suggestions are welcome.

---------------------------------------------------------------------------------
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: Use of LOBs
(in response to Bob Pingston)
Just some hints I lernt working with developers for a CLOB application:

1) Don't use the maximun size of the object, but use a 'mean' size and
calculate how many objects (in %) are away from it. I discovered that no
object reached the maximum forecasted length, and only 1% where more 100k
(when the predicted size was 250KB and more). This has a big impact on the
size of VSAM for LOB object if you choose the wrong page size based on
max object size. As you know you've to COPY/RECOVER LOB tablespace to
increase size (in V7).

2) Check with developers what application do, ie how is the rate of LOB
update as LOB are not updated but recreated. This has a big impact on
reorgs AND space increase(SEE LOB reed book) . I discovered that LOG YES
isn't necessary as LOB objects are saved in sequential files. It's easier
to back up the file than LOB tables, but of course it depends on
application design.

3) Reorg (up to now) aren't an issue, they are fast but if something goes
wrong you could have a lot of work to check that all is OK (even if LOG
YES is mandatory for reorgs...)

Just some thoughts

Max Scarpa




Bob Pingston <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
18/01/2007 23.09
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[DB2-L] Use of LOBs






As a systems guy, design is not one of my strengths. So I post to the
list for advice.
We are thinking of using a LOB as a "scratch pad" for an application that
works with large amounts of data. Its not that the volume is large, its
the "record size" that is large. In the size of about 1.2MB in length.
Currently this is a VSAM application, but it is being altered to use DB2.
Any suggestions are welcome.

---------------------------------------------------------------------------------
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

Walter Jani&#223;en

Re: Use of LOBs
(in response to Max Scarpa)
Bob

I must admit, that I have no experience with lobs, but I would not
recommend lobs for a scratch pad area. I think update and deletes are
difficult with lobs with respect to reusing space. I would split the data
in several rows, e.g. with a counter per key, and store those rows in a
table with 8K, 16K or 32K row-length.

---------------------------------------------------------------------------------
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: Use of LOBs
(in response to Walter Janißen)

Walter

My experience with LOBs is pretty recent, but I've found that LOB in my
case (CLOB as they are XML objects) isn't so bad if you have a good reorg
strategy.

We receive XML via MQ, insert them in a LOB table and then we process them
and delete them. Reorg for compacting space is a matter of seconds as
you've few or no LOB inside (remember LOG YES is mandatory up to V9 where
you'll have SHRLEVEL REFERENCE) . If you have problems with space a
IC/RECOVER is quickly done in the maintenance windows...if any..... As I
wrote before, I saw that a good page size choice is an important keypont.


That's for my application but YMMV

Max Scarpa
Certified mainframe Swiss Knife




Walter Janißen
<walter.janissen@
VICTORIA.DE> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> Re: [DB2-L] Use of LOBs


19/01/2007 16.46


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






Bob

I must admit, that I have no experience with lobs, but I would not
recommend lobs for a scratch pad area. I think update and deletes are
difficult with lobs with respect to reusing space. I would split the data
in several rows, e.g. with a counter per key, and store those rows in a
table with 8K, 16K or 32K row-length.

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

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