Options to Migrate LOB data from DB2 z/OS to Oracle

Saurabh Tripathi

Options to Migrate LOB data from DB2 z/OS to Oracle

Hi Everyone,

I am trying to find some text on how to migrate LOB data from DB2 z/OS to Oracle. I know DB2/Mainframe lovers may not like the thought of migrating from Mainframe :), but stil...

 

If I use IBM Unload, I will get the LOB as members in PDS, and other records from base table in SYSREC. Now to load data in an Oracle table(which will not have the ROWID) I tried using sqlloader, but somehow its not working. I even changed the refeence to LOB datasets in SYSREC to actual absolute path on system where I am executing the LOAD in oracle.

Any thought on this?

Steen Rasmussen

Options to Migrate LOB data from DB2 z/OS to Oracle
(in response to Saurabh Tripathi)
Why don’t you use VARIABLE output format to avoid the old PDS-member method ?

Steen

From: Saurabh Tripathi [mailto:[login to unmask email]
Sent: Thursday, August 03, 2017 12:48 PM
To: [login to unmask email]
Subject: [DB2-L] - Options to Migrate LOB data from DB2 z/OS to Oracle

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Hi Everyone,

I am trying to find some text on how to migrate LOB data from DB2 z/OS to Oracle. I know DB2/Mainframe lovers may not like the thought of migrating from Mainframe :), but stil...



If I use IBM Unload, I will get the LOB as members in PDS, and other records from base table in SYSREC. Now to load data in an Oracle table(which will not have the ROWID) I tried using sqlloader, but somehow its not working. I even changed the refeence to LOB datasets in SYSREC to actual absolute path on system where I am executing the LOAD in oracle.

Any thought on this?

-----End Original Message-----

Saurabh Tripathi

RE: Options to Migrate LOB data from DB2 z/OS to Oracle
(in response to Steen Rasmussen)

Thanks Steen. I tried BMC UNLOAD with that option, but it did not go well. When I tried to load the unloaded data in Oracle, I was getting many errors. Strangely lots of NULL values appeared while loading. Can you help me with a template to try out? Or just point to relevant document. Thanks again.

Steen Rasmussen

Options to Migrate LOB data from DB2 z/OS to Oracle
(in response to Saurabh Tripathi)
I cant help you with the BMC issue – but I can with CA Fast Unload or IBM Unload in this case, and my IBM unload of BLOB/CLOB works just fine:

TEMPLATE UNLDDSN DSN 'STEEN.DSNDB06.TID133.DATA'
SPACE CYL
UNLOAD
FROM TABLE SYSIBM.SYSPACKSTMT
HEADER NONE
LIMIT ALL
( "CONTOKEN"
POSITION(00001 ) CHAR ( 8)
, "SEQNO"
POSITION(00009 ) INTEGER
, "STMTNO"
POSITION(00013 ) SMALLINT
, "SECTNO"
POSITION(00015 ) SMALLINT
, "BINDERROR"
POSITION(00017 ) CHAR ( 1)
, "IBMREQD"
POSITION(00018 ) CHAR ( 1)
, "ISOLATION"
POSITION(00019 ) CHAR ( 1)
, "STATUS"
POSITION(00020 ) CHAR ( 1)
, "ACCESSPATH"
POSITION(00021 ) CHAR ( 1)
, "STMTNOI"
POSITION(00022 ) INTEGER
, "SECTNOI"
POSITION(00026 ) INTEGER
, "EXPLAINABLE"
POSITION(00030 ) CHAR ( 1)
, "QUERYNO"
POSITION(00031 ) INTEGER
, "STMT_ID"
POSITION(00035 ) BIGINT
, "EXPANSION_REASON"
POSITION(00043 ) CHAR ( 2)
, "LOCATION"
POSITION(00045 ) VARCHAR
, "COLLID"
POSITION( * ) VARCHAR
, "NAME"
POSITION( * ) VARCHAR
, "VERSION"
POSITION( * ) VARCHAR
, "STMT"
POSITION( * ) VARCHAR
, "ROWID"
POSITION( * ) ROWID
, "STATEMENT"
POSITION( * ) CLOB
, "STMTBLOB"
POSITION( * ) BLOB
)
NOPAD
SPANNED YES
UNLDDN UNLDDSN
UNICODE
SHRLEVEL REFERENCE
DSNU252I 216 13:31:51.29 DSNUUNLD - UNLOAD PHASE STATISTICS - NUMBER OF
RECORDS UNLOADED=1679352 FOR TABLESPACE
DSNDB06.SYSTSPKS
DSNU250I 216 13:31:51.29 DSNUUNLD - UNLOAD PHASE COMPLETE, ELAPSED
TIME=00:02:18
DSNU010I 216 13:31:51.33 DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST
RETURN CODE=0

From: Saurabh Tripathi [mailto:[login to unmask email]
Sent: Thursday, August 03, 2017 10:15 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Options to Migrate LOB data from DB2 z/OS to Oracle

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Thanks Steen. I tried BMC UNLOAD with that option, but it did not go well. When I tried to load the unloaded data in Oracle, I was getting many errors. Strangely lots of NULL values appeared while loading. Can you help me with a template to try out? Or just point to relevant document. Thanks again.

-----End Original Message-----

David Baldon

Options to Migrate LOB data from DB2 z/OS to Oracle
(in response to Saurabh Tripathi)
If you didn’t open an issue with BMC Support I recommend you do so.

Without seeing the output from your attempts to load things into Oracle it’s really difficult to say what might be going on and suggest suitable solutions.

…David

From: Saurabh Tripathi [mailto:[login to unmask email]
Sent: Thursday, August 3, 2017 10:15 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Options to Migrate LOB data from DB2 z/OS to Oracle


Thanks Steen. I tried BMC UNLOAD with that option, but it did not go well. When I tried to load the unloaded data in Oracle, I was getting many errors. Strangely lots of NULL values appeared while loading. Can you help me with a template to try out? Or just point to relevant document. Thanks again.

-----End Original Message-----

Javier Estrada Benavides

RE: Options to Migrate LOB data from DB2 z/OS to Oracle
(in response to David Baldon)

Hi:

  How big are your LOB tables? If they're big enough, you won't be able to unload it to a PDS or a zFS because you'll run out of space and it will be extremely hard to handle, however, if they're small enough, a simple IBM Data Studio unload can give it on many formats (as I believe your Oracle side would prefer).

 

Regards,

Javier Estrada Benavides, Mexico

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Database Administrator - DB2 11 DBA for z/OS

Saurabh Tripathi

RE: Options to Migrate LOB data from DB2 z/OS to Oracle
(in response to Javier Estrada Benavides)

Thanks Javier. I will try this option. Is there a limitation on unloading large LOB tables using Data studio? 

Saurabh Tripathi

RE: Options to Migrate LOB data from DB2 z/OS to Oracle
(in response to Steen Rasmussen)

Hi Steen,

 

Many thanks for sharing the template. The problem with SPANNED YES is it will work only with IBM LOAD. I still tried with Oracle load, but as expected, it did not work. Also what will be the default delimiter in case SPANNED YES is used? We cannot have DELIMITED parameter for SPANNED YES.