Unloading LOBs on DB2 v7.1 on OS/390

[Rom Marshall]

Hi.

Once a LOB size goes beyond 32KB in size, I'm not able to use the LOAD
utility to unload the data off the tables for DB2 7.1 on OS/390.
Instead, the documentation refers to a sample C++ program --
DSN710.SDSNSAMP(DSN8DLPL) to use for this purpose.

I'm wondering if anyone out there has a COBOL equivalent to this as I do
not have a C++ compiler?

Would I be able to use the same program to load LOBs larger than 32KB?

Lastly, how do you generally manage LOBs that are greater than 32KB in size?

Any response appreciated.

Thanks and best regards.

Rom Marshall

[EDWARD LIPSON]

We are DB2 6 and 7. We limit CLOBs to 31K because of this. Third party
software says it will support it (BMC has a V2 product as part of it's
DB2 Admin Solution Set - I forget the name but they just did a webex on it
- supports up to 32 MB). I think the real issue in the program is the
OS file record size limits. Hard to get over 32K, so you have to take it
apart and put it together.
RM> Hi.

RM> Once a LOB size goes beyond 32KB in size, I'm not able to use the LOAD
RM> utility to unload the data off the tables for DB2 7.1 on OS/390.
RM> Instead, the documentation refers to a sample C++ program --
RM> DSN710.SDSNSAMP(DSN8DLPL) to use for this purpose.

RM> I'm wondering if anyone out there has a COBOL equivalent to this as I do
RM> not have a C++ compiler?

RM> Would I be able to use the same program to load LOBs larger than 32KB?

RM> Lastly, how do you generally manage LOBs that are greater than 32KB in size?

RM> Any response appreciated.

RM> Thanks and best regards.

RM> Rom Marshall

Edward Lipson via Relaynet.org Moondog
ed***********@moondog.com el*****@bankofny.com
---
þ MM 1.1 #0361 þ MONEY TALKS ... all mine ever says is GOODBYE!

[Blair Adamache]

If your text is limited to 32K, you'd be better off using VARCHAR on a
32K page.

EDWARD LIPSON wrote:

We are DB2 6 and 7. We limit CLOBs to 31K because of this. Third party
software says it will support it (BMC has a V2 product as part of it's
DB2 Admin Solution Set - I forget the name but they just did a webex on it
- supports up to 32 MB). I think the real issue in the program is the
OS file record size limits. Hard to get over 32K, so you have to take it
apart and put it together.
RM> Hi.

RM> Once a LOB size goes beyond 32KB in size, I'm not able to use the LOAD
RM> utility to unload the data off the tables for DB2 7.1 on OS/390.
RM> Instead, the documentation refers to a sample C++ program --
RM> DSN710.SDSNSAMP(DSN8DLPL) to use for this purpose.

RM> I'm wondering if anyone out there has a COBOL equivalent to this as I do
RM> not have a C++ compiler?

RM> Would I be able to use the same program to load LOBs larger than 32KB?

RM> Lastly, how do you generally manage LOBs that are greater than 32KB in size?

RM> Any response appreciated.

RM> Thanks and best regards.

RM> Rom Marshall

Edward Lipson via Relaynet.org Moondog
ed***********@moondog.com el*****@bankofny.com
---
þ MM 1.1 #0361 þ MONEY TALKS ... all mine ever says is GOODBYE!

[PM \(pm3iinc-nospam\)]

> I think the real issue in the program is the

OS file record size limits. Hard to get over 32K, so you have to take it
apart and put it together.

I've done RECFM=U cobol file processing in the past so 32k is not
allways the limit for logical records.

I think that the 32k limit is because the cobol BL and BLL cells can
(could?) only point/reference
adresses in the 32k following the base address. /offset

Logically, LOBS are 1 record.

PM

[Rom Marshall]

Unfortunately, the application I am using -- WBIFN -- has defined the
BLOBs to be 100MB in size. But they have not provided any mechanism for
backing up/restoring the data. Instead, the manual says to refer to the
DB2 manuals for the appropriate mechanism. Hah!

I'm finding it more than a little puzzling that DB2 provides the
facility to create such large LOBs but do not provide a utility to back
it up. But then, the world's full of puzzles ...

One suggestion I got was to use the DSN1COPY facility. But this raises
the possibility that I may not be able to restore the data when I move
my DB2 to a different version eventually.

Hence the search for an unloading/loading mechanism.

Rom Marshall

Blair Adamache wrote:

If your text is limited to 32K, you'd be better off using VARCHAR on a
32K page.

EDWARD LIPSON wrote:

We are DB2 6 and 7. We limit CLOBs to 31K because of this. Third party
software says it will support it (BMC has a V2 product as part of it's
DB2 Admin Solution Set - I forget the name but they just did a webex
on it - supports up to 32 MB). I think the real issue in the program
is the OS file record size limits. Hard to get over 32K, so you have
to take it apart and put it together.
RM> Hi.

RM> Once a LOB size goes beyond 32KB in size, I'm not able to use the LOAD
RM> utility to unload the data off the tables for DB2 7.1 on OS/390.
RM> Instead, the documentation refers to a sample C++ program --
RM> DSN710.SDSNSAMP(DSN8DLPL) to use for this purpose.

RM> I'm wondering if anyone out there has a COBOL equivalent to this as I do
RM> not have a C++ compiler?

RM> Would I be able to use the same program to load LOBs larger than 32KB?

RM> Lastly, how do you generally manage LOBs that are greater than 32KB in size?

RM> Any response appreciated.

RM> Thanks and best regards.

RM> Rom Marshall

Edward Lipson via Relaynet.org Moondog
ed***********@moondog.com el*****@bankofny.com
---
þ MM 1.1 #0361 þ MONEY TALKS ... all mine ever says is GOODBYE!

[Blair Adamache]

Can you cast them to varchar(31K) when unloading:

db2 select varchar(col1,31000) from testclob

Rom Marshall wrote:

Unfortunately, the application I am using -- WBIFN -- has defined the
BLOBs to be 100MB in size. But they have not provided any mechanism for
backing up/restoring the data. Instead, the manual says to refer to the
DB2 manuals for the appropriate mechanism. Hah!

I'm finding it more than a little puzzling that DB2 provides the
facility to create such large LOBs but do not provide a utility to back
it up. But then, the world's full of puzzles ...

One suggestion I got was to use the DSN1COPY facility. But this raises
the possibility that I may not be able to restore the data when I move
my DB2 to a different version eventually.

Hence the search for an unloading/loading mechanism.

Rom Marshall

Blair Adamache wrote:

If your text is limited to 32K, you'd be better off using VARCHAR on a
32K page.

EDWARD LIPSON wrote:

We are DB2 6 and 7. We limit CLOBs to 31K because of this. Third
party software says it will support it (BMC has a V2 product as part
of it's DB2 Admin Solution Set - I forget the name but they just did
a webex on it - supports up to 32 MB). I think the real issue in the
program is the OS file record size limits. Hard to get over 32K, so
you have to take it apart and put it together.
RM> Hi.

RM> Once a LOB size goes beyond 32KB in size, I'm not able to use the
LOAD
RM> utility to unload the data off the tables for DB2 7.1 on OS/390.
RM> Instead, the documentation refers to a sample C++ program --
RM> DSN710.SDSNSAMP(DSN8DLPL) to use for this purpose.

RM> I'm wondering if anyone out there has a COBOL equivalent to this
as I do
RM> not have a C++ compiler?

RM> Would I be able to use the same program to load LOBs larger than
32KB?

RM> Lastly, how do you generally manage LOBs that are greater than
32KB in size?

RM> Any response appreciated.

RM> Thanks and best regards.

RM> Rom Marshall

Edward Lipson via Relaynet.org Moondog
ed***********@moondog.com el*****@bankofny.com
---
þ MM 1.1 #0361 þ MONEY TALKS ... all mine ever says is GOODBYE!

[Rom Marshall]

Most of the BLOBs are <32K but there are those that are larger in size.
I would end up unloading truncated data by doing that.

Blair Adamache wrote:

Can you cast them to varchar(31K) when unloading:

db2 select varchar(col1,31000) from testclob

Rom Marshall wrote:

Unfortunately, the application I am using -- WBIFN -- has defined the
BLOBs to be 100MB in size. But they have not provided any mechanism
for backing up/restoring the data. Instead, the manual says to refer
to the DB2 manuals for the appropriate mechanism. Hah!

I'm finding it more than a little puzzling that DB2 provides the
facility to create such large LOBs but do not provide a utility to
back it up. But then, the world's full of puzzles ...

One suggestion I got was to use the DSN1COPY facility. But this
raises the possibility that I may not be able to restore the data when
I move my DB2 to a different version eventually.

Hence the search for an unloading/loading mechanism.

Rom Marshall

Blair Adamache wrote:

If your text is limited to 32K, you'd be better off using VARCHAR on
a 32K page.

EDWARD LIPSON wrote:

We are DB2 6 and 7. We limit CLOBs to 31K because of this. Third
party software says it will support it (BMC has a V2 product as part
of it's DB2 Admin Solution Set - I forget the name but they just did
a webex on it - supports up to 32 MB). I think the real issue in the
program is the OS file record size limits. Hard to get over 32K, so
you have to take it apart and put it together.
RM> Hi.

RM> Once a LOB size goes beyond 32KB in size, I'm not able to use
the LOAD
RM> utility to unload the data off the tables for DB2 7.1 on OS/390.
RM> Instead, the documentation refers to a sample C++ program --
RM> DSN710.SDSNSAMP(DSN8DLPL) to use for this purpose.

RM> I'm wondering if anyone out there has a COBOL equivalent to this
as I do
RM> not have a C++ compiler?

RM> Would I be able to use the same program to load LOBs larger than
32KB?

RM> Lastly, how do you generally manage LOBs that are greater than
32KB in size?

RM> Any response appreciated.

RM> Thanks and best regards.

RM> Rom Marshall

Edward Lipson via Relaynet.org Moondog
ed***********@moondog.com el*****@bankofny.com
---
þ MM 1.1 #0361 þ MONEY TALKS ... all mine ever says is GOODBYE!

[Heinz Buehler]

> I'm finding it more than a little puzzling that DB2 provides the

facility to create such large LOBs but do not provide a utility to back
it up. But then, the world's full of puzzles ...

Rom
you can use DB2 COPY utility to back up your LOB table spaces,
and RECOVER to recover them.
Note that you must COPY the table space set in
SHRLEVEL REFERENCE when the LOB table space is defined without
logging, and that you must recover the entire table space set
in case you do a point-in-time recovery.

Instead of unloading, you may consider to copy the LOB data
to a clone of the table using SQL INSERT INTO ... SELECT FROM.
This works also for large LOBs. This way you can at least put
your LOB data somewhere safe if you have to drop the
table spaces for some reason. When you use LOBs in DB2, you will
most probably run into data corruption problems, the DB2 software
still is very wobbly in this area. The SQL method will allow to
at least pick out those rows that are still readable and put them
into a new table space.

regards,
Heinz Buehler

[Rom Marshall]

Heinz, hi.

You've given me some ideas for short term data protection -- thanks for
that thought.

I looked back and realised that I didn't explain why I seemed bent on
using LOAD. The biggest reason is that we may need to recover the data
up to 7 years from now -- the local (Singaporean) monetary authority
requires us to keep customer information for a period that long.

If, for some reason, we have to restore 6 year old data, we would
probably face a problem if there are data file incompatibilities between
the version of DB2 at the time we took the COPY and the version at the
time we attempt to do a RECOVER.

A DSN1COPY is just as problematic for the same reason.

LOAD bypasses this problem by extracting out the data.

On the open platforms (AIX, Windows, etc), one other way around would be
to use EXPORT in IXF format. This does not exist on the OS/390
environment, unfortunately.

I have one idea lurking at the back of my mind, and that's to catalog
the database on the OS/390 to an AIX/Windows box and perform a backup
from there. I don't know if this would work, though, as I have not
tried such a thing yet. Does anyone know? And, if I do this, will I
lose any data -- e.g. authentication or privilege settings?

Rom Marshall

Heinz Buehler wrote:

I'm finding it more than a little puzzling that DB2 provides the
facility to create such large LOBs but do not provide a utility to back
it up. But then, the world's full of puzzles ...

Rom
you can use DB2 COPY utility to back up your LOB table spaces,
and RECOVER to recover them.
Note that you must COPY the table space set in
SHRLEVEL REFERENCE when the LOB table space is defined without
logging, and that you must recover the entire table space set
in case you do a point-in-time recovery.

Instead of unloading, you may consider to copy the LOB data
to a clone of the table using SQL INSERT INTO ... SELECT FROM.
This works also for large LOBs. This way you can at least put
your LOB data somewhere safe if you have to drop the
table spaces for some reason. When you use LOBs in DB2, you will
most probably run into data corruption problems, the DB2 software
still is very wobbly in this area. The SQL method will allow to
at least pick out those rows that are still readable and put them
into a new table space.

regards,
Heinz Buehler

[Blair Adamache]

Partial answer - could you get the short ones using the length function:

select varchar(col1,31000) from testclob where length(col1)<32728

Rom Marshall wrote:

Most of the BLOBs are <32K but there are those that are larger in size.
I would end up unloading truncated data by doing that.

Blair Adamache wrote:

Can you cast them to varchar(31K) when unloading:

db2 select varchar(col1,31000) from testclob

Rom Marshall wrote:

Unfortunately, the application I am using -- WBIFN -- has defined the
BLOBs to be 100MB in size. But they have not provided any mechanism
for backing up/restoring the data. Instead, the manual says to refer
to the DB2 manuals for the appropriate mechanism. Hah!

I'm finding it more than a little puzzling that DB2 provides the
facility to create such large LOBs but do not provide a utility to
back it up. But then, the world's full of puzzles ...

One suggestion I got was to use the DSN1COPY facility. But this
raises the possibility that I may not be able to restore the data
when I move my DB2 to a different version eventually.

Hence the search for an unloading/loading mechanism.

Rom Marshall

Blair Adamache wrote:

If your text is limited to 32K, you'd be better off using VARCHAR on
a 32K page.

EDWARD LIPSON wrote:

> We are DB2 6 and 7. We limit CLOBs to 31K because of this. Third
> party software says it will support it (BMC has a V2 product as
> part of it's DB2 Admin Solution Set - I forget the name but they
> just did a webex on it - supports up to 32 MB). I think the real
> issue in the program is the OS file record size limits. Hard to get
> over 32K, so you have to take it apart and put it together.
>
>
> RM> Hi.
>
> RM> Once a LOB size goes beyond 32KB in size, I'm not able to use
> the LOAD
> RM> utility to unload the data off the tables for DB2 7.1 on OS/390.
> RM> Instead, the documentation refers to a sample C++ program --
> RM> DSN710.SDSNSAMP(DSN8DLPL) to use for this purpose.
>
> RM> I'm wondering if anyone out there has a COBOL equivalent to
> this as I do
> RM> not have a C++ compiler?
>
> RM> Would I be able to use the same program to load LOBs larger
> than 32KB?
>
> RM> Lastly, how do you generally manage LOBs that are greater than
> 32KB in size?
>
> RM> Any response appreciated.
>
> RM> Thanks and best regards.
>
> RM> Rom Marshall
>
> Edward Lipson via Relaynet.org Moondog
> ed***********@moondog.com el*****@bankofny.com
> ---
> þ MM 1.1 #0361 þ MONEY TALKS ... all mine ever says is GOODBYE!