How to load LOB tablespaces using DSN1COPY?

In the past, I've tried to load tablespaces with LOB columns using the regular IBM Load, BMC Load Plus & CA Fast Load. None of these tools support LOB tablespaces. You get into a series of errors.

The only utility that has ever worked for me is DSN1COPY. It's a pain to use DSN1COPY though. You need several things to go right for this to work. Let me try to explain as simple as possible.

In this example, I'm copying data from an imagecopy dataset into a table in a TEST subsystem. The first thing you need to remember is that, you need to know the DBID of the database, PSID of the tablespace and OBID of the table for both the source and target subsystems.

These are the steps:

1. STOP the base and LOB tablespaces and indexes for the LOB table on the target subsystem

2. Run DSN1COPY with the right parameters. I use PARM='PAGESIZE(32K),LOB,FULLCOPY,OBIDXLAT,RESET'. Supply the imagecopy dataset of the table on the source subsystem for SYSUT1 and supply the DSNDBC linear dataset of the table on the target subsystem for SYSUT2.

3. START the base and LOB tablespaces and indexes for the LOB table on the target subsystem

4. Rebuild the indexes

5. Run runstats

If the DSN1COPY step abends with "DSN1992I VSAM PUT ERROR, RPLERREG = 008, RPLERRCD = 028", check JESMSGLG. If you see a message like this "IEC070I 204-220,jobname,stepname,SYSUT2,6BF7,DBD115& IEC070I DB2CATx.DSNDBC.dbname.tsname.I0001.A001", it means you do not have enough space in that dataset. Make sure, you assign multiple datasets with suffix A001, A002, etc. in the target subsystem, it needs to accommodate all data from the source.

If you see a message like this "IEC070I 209-220,jobname,stepname,SYSUT2,89B5,DBD235,& IEC070I DB2CATx.DSNDBC.dbname.tsname.I0001.A001,", that means no space is available in the DASD volume. To fix this problem, make the dataset multi volume so that it can extend space into multiple volumes.

HTH !!

How to find DBID, PSID, OBID of a tablespace

If you dropped a table and you took imagecopy BEFORE dropping it, then you can recover the data to a newly recreated table if you know the DBID, PSID, OBID of the dropped table. To find these values, use DSN1COPY with the image copy dataset and use FULLCOPY parm and specify 0001 for the source DBID, PSID & OBID. When you run it first, it will give the correct DBID. Replace 0001 with this DBID, you'll get the PSID. Replace 0001 with this PSID and you'll get the OBID.

For an existing table, to obtain the DBID and PSID, run the following query:

SELECT DBNAME, DBID, NAME, PSID
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME = "database-name"
AND NAME = "table_space-name"

To obtain the table OBID, run this query:

SELECT NAME, OBID
FROM SYSIBM.SYSTABLES
WHERE TSNAME = "table_space-name"
AND CREATOR = "creator-name"