DB2 Unload utility - R.I. on tables with LOB columns

Kapil Mathur

DB2 Unload utility - R.I. on tables with LOB columns
Hi Listers,

I am in a DB2 V8 NFM on z/OS environment.
There are 2 tables participating in a referential-integrity relationship with the
small parent-table and a large child-table (with the child-table having 400,000
rows with a 2MB LOB column).

I need to unload "referentially intact" data from the parent table and the child
table, (including LOB data in the LOB tablespace) ... prior to reloading the
unloaded files into another DB2 subsystem ..

Is there a faster way to accomplish this other than first putting both tables in
read-only status and using DB2 UNLOAD utility to unload regular data to a
sequential file and with the BLOBF clause to unload lob data to a PDSE file
(this entire operation currently takes 7 to 8 hours of read-only outage on both
tables every time it is done)?

Thank you in advance for any assistance ..

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Roger Miller

Re: DB2 Unload utility - R.I. on tables with LOB columns
(in response to Kapil Mathur)
How about read only time to run DSN1COPY on the table spaces? Then get
back to work. Create new tables and translate the OBIDs and DBIDs, then
unload from here or just use the technique on the new subsystem, clearing
out the LRSN / RBA values.

Roger Miller, DB2 for z/OS

On Thu, 4 Dec 2008 19:01:04 +0000, Kapil Mathur
<[login to unmask email]> wrote:

>Hi Listers,
>
>I am in a DB2 V8 NFM on z/OS environment.
>There are 2 tables participating in a referential-integrity relationship with the
>small parent-table and a large child-table (with the child-table having 400,000
>rows with a 2MB LOB column).
>
>I need to unload "referentially intact" data from the parent table and the child
>table, (including LOB data in the LOB tablespace) ... prior to reloading the
>unloaded files into another DB2 subsystem ..
>
>Is there a faster way to accomplish this other than first putting both tables in
>read-only status and using DB2 UNLOAD utility to unload regular data to a
>sequential file and with the BLOBF clause to unload lob data to a PDSE file
>(this entire operation currently takes 7 to 8 hours of read-only outage on
both
>tables every time it is done)?
>
>Thank you in advance for any assistance ..
>
>

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Deb Ghosh

Re: DB2 Unload utility - R.I. on tables with LOB columns
(in response to Roger Miller)

You may want to use VSAM NSR pool using SMB buffering technique while using DSN1COPY, I have seen some dramatic improvement in DSN1COPY. You can invoke SMB by providing parms in the JCL.> Date: Fri, 5 Dec 2008 18:43:26 +0000> From: [login to unmask email]> Subject: Re: [DB2-L] DB2 Unload utility - R.I. on tables with LOB columns> To: [login to unmask email]> > How about read only time to run DSN1COPY on the table spaces? Then get > back to work. Create new tables and translate the OBIDs and DBIDs, then > unload from here or just use the technique on the new subsystem, clearing > out the LRSN / RBA values.> > Roger Miller, DB2 for z/OS> > On Thu, 4 Dec 2008 19:01:04 +0000, Kapil Mathur > <[login to unmask email]> wrote:> > >Hi Listers,> >> >I am in a DB2 V8 NFM on z/OS environment.> >There are 2 tables participating in a referential-integrity relationship with the> >small parent-table and a large child-table (with the child-table having 400,000> >rows with a 2MB LOB column).> >> >I need to unload "referentially intact" data from the parent table and the child> >table, (including LOB data in the LOB tablespace) ... prior to reloading the> >unloaded files into another DB2 subsystem ..> >> >Is there a faster way to accomplish this other than first putting both tables in> >read-only status and using DB2 UNLOAD utility to unload regular data to a> >sequential file and with the BLOBF clause to unload lob data to a PDSE file> >(this entire operation currently takes 7 to 8 hours of read-only outage on > both> >tables every time it is done)?> >> >Thank you in advance for any assistance ..> >> >> > ______________________________________________________________________> > * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *> ______________________________________________________________________> > > > The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
_________________________________________________________________
You live life online. So we put Windows on the web.
http://clk.atdmt.com/MRT/go/127032869/direct/01/
______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms