How Best to Unload BLOB Data for Archive

[login to unmask email]

How Best to Unload BLOB Data for Archive
Dear Esteemed List,

I am supporting an application that has BLOB data, thusly:

TBNAME COL_NAME COLNO COLTYPE LENGTH
---------+---------+---------+---------+---------+---------+------
ZLLBLOB I_DOC_REF 1 INTEGER 4
ZLLBLOB R_ZLLBLOB 2 ROWID 17
ZLLBLOB X_DOCUMENT 3 BLOB 4

ZLLBLOBA AUXID 1 VARCHAR 17
ZLLBLOBA AUXVER 2 SMALLINT 2
ZLLBLOBA AUXVALUE 3 BLOB 4

At the moment, things are, space-wise, getting out of hand:

- Enter "/" to select action Tracks %Used XT
Device
---------------------------------------------------------------------
DB2P.DSNDBD.Z561.ZLLBLOB.I0001.A001 6000 ? 1 3390
DB2P.DSNDBD.Z561.ZLLBLOBA.I0001.A001 87360 ? 27 3390 <===
DB2P.DSNDBD.Z561.ZLLBLOBA.I0001.A002 48000 ? 15 3390 <===
DB2P.DSNDBD.Z561.ZLLBLOBX.I0001.A001 1500 ? 1 3390
DB2P.DSNDBD.Z561.ZLLBLOB1.I0001.A001 750 ? 1 3390
DB2P.DSNDBD.Z561.ZLLBLOB2.I0001.A001 750 ? 1 3390

As can be seen, the ZLLBLOBA tablespace holds the BLOB data, and it's
Large and Growing. Stats from the application indicate that no BLOB
column exceeds 75,000 bytes, with the vast majority of rows below 32KB.

Question: What is the "easiest" way to archive this stuff, so that it
could be restored if requested? UNLOAD utility? (If so, to what kind of
dataset, and with what DCB attributes)? An application (Cobol or
Extrieve) ?

Your ideas, as always, are most welcome.

Lock Lyon
Compuware Corp

---------------------------------------------------------------------------------
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: How Best to Unload BLOB Data for Archive
(in response to LL581@DAIMLERCHRYSLER.COM)
Lock,
There are two problems with unloading blobs to make space in the
dataset.
1. The space is not reused until the AUX table is reorged. (only free
space at the end is reused).
2. Many 'unloads' do not support a record lenth of over 32K (this is a
moving target).

The best suggestion I have found is
1. Create a shadow table with the same structure as the original.
2. Do a INSERT INTO newtable SELECT etc.
3. Delete the 'archived' rows from the original.
4. Reorg the original esp the AUX table space.

Hope this helps
Av

_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Tuesday, December 21, 2004 1:26 PM
To: [login to unmask email]
Subject: How Best to Unload BLOB Data for Archive



Dear Esteemed List,

I am supporting an application that has BLOB data, thusly:

TBNAME COL_NAME COLNO COLTYPE LENGTH
---------+---------+---------+---------+---------+---------+------
ZLLBLOB I_DOC_REF 1 INTEGER 4
ZLLBLOB R_ZLLBLOB 2 ROWID 17
ZLLBLOB X_DOCUMENT 3 BLOB 4

ZLLBLOBA AUXID 1 VARCHAR 17
ZLLBLOBA AUXVER 2 SMALLINT 2
ZLLBLOBA AUXVALUE 3 BLOB 4

At the moment, things are, space-wise, getting out of hand:

- Enter "/" to select action Tracks %Used XT
Device
---------------------------------------------------------------------
DB2P.DSNDBD.Z561.ZLLBLOB.I0001.A001 6000 ? 1
3390
DB2P.DSNDBD.Z561.ZLLBLOBA.I0001.A001 87360 ? 27
3390 <===
DB2P.DSNDBD.Z561.ZLLBLOBA.I0001.A002 48000 ? 15
3390 <===
DB2P.DSNDBD.Z561.ZLLBLOBX.I0001.A001 1500 ? 1
3390
DB2P.DSNDBD.Z561.ZLLBLOB1.I0001.A001 750 ? 1
3390
DB2P.DSNDBD.Z561.ZLLBLOB2.I0001.A001 750 ? 1
3390

As can be seen, the ZLLBLOBA tablespace holds the BLOB data, and it's
Large and Growing. Stats from the application indicate that no BLOB
column exceeds 75,000 bytes, with the vast majority of rows below 32KB.

Question: What is the "easiest" way to archive this stuff, so that it
could be restored if requested? UNLOAD utility? (If so, to what kind
of dataset, and with what DCB attributes)? An application (Cobol or
Extrieve) ?

Your ideas, as always, are most welcome.

Lock Lyon
Compuware Corp
------------------------------------------------------------------------
--------- 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
--------------------------------------------------------

NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited.


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

Phil Grainger

Re: How Best to Unload BLOB Data for Archive
(in response to Avram Friedman)
You could use CAs Fast Unload to unload the data safe in the knowledge
you can then use Fast Load to put it back again if needed. Of course,
you'd have to write some SQL as well to delete the rows after you have
unloaded them.

Also, schedule a reorg after the unload/delete to reclaim dead space (I
guess you have tried reorging the LOB tablespace???)

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]



_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: 21 December 2004 18:26
To: [login to unmask email]
Subject: How Best to Unload BLOB Data for Archive



Dear Esteemed List,

I am supporting an application that has BLOB data, thusly:

TBNAME COL_NAME COLNO COLTYPE LENGTH
---------+---------+---------+---------+---------+---------+------
ZLLBLOB I_DOC_REF 1 INTEGER 4
ZLLBLOB R_ZLLBLOB 2 ROWID 17
ZLLBLOB X_DOCUMENT 3 BLOB 4

ZLLBLOBA AUXID 1 VARCHAR 17
ZLLBLOBA AUXVER 2 SMALLINT 2
ZLLBLOBA AUXVALUE 3 BLOB 4

At the moment, things are, space-wise, getting out of hand:

- Enter "/" to select action Tracks %Used XT
Device
---------------------------------------------------------------------
DB2P.DSNDBD.Z561.ZLLBLOB.I0001.A001 6000 ? 1
3390
DB2P.DSNDBD.Z561.ZLLBLOBA.I0001.A001 87360 ? 27
3390 <===
DB2P.DSNDBD.Z561.ZLLBLOBA.I0001.A002 48000 ? 15
3390 <===
DB2P.DSNDBD.Z561.ZLLBLOBX.I0001.A001 1500 ? 1
3390
DB2P.DSNDBD.Z561.ZLLBLOB1.I0001.A001 750 ? 1
3390
DB2P.DSNDBD.Z561.ZLLBLOB2.I0001.A001 750 ? 1
3390

As can be seen, the ZLLBLOBA tablespace holds the BLOB data, and it's
Large and Growing. Stats from the application indicate that no BLOB
column exceeds 75,000 bytes, with the vast majority of rows below 32KB.

Question: What is the "easiest" way to archive this stuff, so that it
could be restored if requested? UNLOAD utility? (If so, to what kind
of dataset, and with what DCB attributes)? An application (Cobol or
Extrieve) ?

Your ideas, as always, are most welcome.

Lock Lyon
Compuware Corp
------------------------------------------------------------------------
--------- 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

Roland Schiradin

Re: How Best to Unload BLOB Data for Archive
(in response to Phil Grainger)
Peanuts:-))

Enter "/" to select action Tracks %Used XT Device
--------------------------------------------------------------------
DB2PDB.DSNDBD.DALP0MW0.SMWTPJL1.I0001.A001 87360 ? 1 3390
DB2PDB.DSNDBD.DALP0MW0.SMWTPJL1.I0001.A002 87360 ? 5 3390
DB2PDB.DSNDBD.DALP0MW0.SMWTPJL1.I0001.A003 87360 ? 4 3390
DB2PDB.DSNDBD.DALP0MW0.SMWTPJL1.I0001.A004 87360 ? 4 3390
DB2PDB.DSNDBD.DALP0MW0.SMWTPJL1.I0001.A005 87360 ? 4 3390


and another LOB tablespace

DB2PDB.DSNDBD.DALP0CT0.SCTBDOL1.I0001.A001 87360 ? 2 3390
DB2PDB.DSNDBD.DALP0CT0.SCTBDOL1.I0001.A002 87360 ? 4 3390
DB2PDB.DSNDBD.DALP0CT0.SCTBDOL1.I0001.A003 87360 ? 7 3390
DB2PDB.DSNDBD.DALP0CT0.SCTBDOL1.I0001.A004 87360 ? 4 3390
DB2PDB.DSNDBD.DALP0CT0.SCTBDOL1.I0001.A005 87360 ? 4 3390
DB2PDB.DSNDBD.DALP0CT0.SCTBDOL1.I0001.A006 87360 ? 6 3390
DB2PDB.DSNDBD.DALP0CT0.SCTBDOL1.I0001.A007 87360 ? 5 3390
DB2PDB.DSNDBD.DALP0CT0.SCTBDOL1.I0001.A008 87360 ? 4 3390
DB2PDB.DSNDBD.DALP0CT0.SCTBDOL1.I0001.A009 87360 ? 5 3390
DB2PDB.DSNDBD.DALP0CT0.SCTBDOL1.I0001.A010 87360 ? 4 3390
DB2PDB.DSNDBD.DALP0CT0.SCTBDOL1.I0001.A011 43680 ? 2 3390


All of them are LOG YES because of DML (Updates/Deletes/Insert)

REORG doesn't release any extent even you delete all rows. Recover works just fine. Unload will not work because >32K.
Almost daily online fullcopy. We had a lot of issues with IBM during LOB corruption. For V7 I recommend a high maintenance level (RSU0411)
in order to handle corrupted lob's if any.

Sometime we create a shadow table and use INSERT INTO shadow-lob SELECT * FROM orig-lob where.
Drop orig-lob, recreate and INSERT INTO orig-lob SELECT * FROM shadow-lob to get rid of some "empty" extents.

I believe it's the best to write an application if you want to archive some data. Just remember an extent will never released.


Roland

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of [login to unmask email]
Sent: Tuesday, December 21, 2004 7:26 PM
To: [login to unmask email]
Subject: How Best to Unload BLOB Data for Archive



Dear Esteemed List,

I am supporting an application that has BLOB data, thusly:

TBNAME COL_NAME COLNO COLTYPE LENGTH
---------+---------+---------+---------+---------+---------+------
ZLLBLOB I_DOC_REF 1 INTEGER 4
ZLLBLOB R_ZLLBLOB 2 ROWID 17
ZLLBLOB X_DOCUMENT 3 BLOB 4

ZLLBLOBA AUXID 1 VARCHAR 17
ZLLBLOBA AUXVER 2 SMALLINT 2
ZLLBLOBA AUXVALUE 3 BLOB 4

At the moment, things are, space-wise, getting out of hand:

- Enter "/" to select action Tracks %Used XT Device
---------------------------------------------------------------------
DB2P.DSNDBD.Z561.ZLLBLOB.I0001.A001 6000 ? 1 3390
DB2P.DSNDBD.Z561.ZLLBLOBA.I0001.A001 87360 ? 27 3390 <===
DB2P.DSNDBD.Z561.ZLLBLOBA.I0001.A002 48000 ? 15 3390 <===
DB2P.DSNDBD.Z561.ZLLBLOBX.I0001.A001 1500 ? 1 3390
DB2P.DSNDBD.Z561.ZLLBLOB1.I0001.A001 750 ? 1 3390
DB2P.DSNDBD.Z561.ZLLBLOB2.I0001.A001 750 ? 1 3390

As can be seen, the ZLLBLOBA tablespace holds the BLOB data, and it's Large and Growing. Stats from the application indicate that no BLOB column exceeds 75,000 bytes, with the vast majority of rows below 32KB.

Question: What is the "easiest" way to archive this stuff, so that it could be restored if requested? UNLOAD utility? (If so, to what kind of dataset, and with what DCB attributes)? An application (Cobol or Extrieve) ?

Your ideas, as always, are most welcome.

Lock Lyon
Compuware Corp --------------------------------------------------------------------------------- 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