DB2v10.1 for Z/OS.How to unload table that contains lob column.

Oleg Dayneko

DB2v10.1 for Z/OS.How to unload table that contains lob column.

Hi. I try to unload table that contains CLOB column.

This is my job for example:

 //UNLOADJ JOB REGION=0K
//UNLD1 EXEC DSNUPROC,SYSTEM=TST1,
// UID=''
//SYSPUNCH DD DSN=DB2LOD.V3.CNTL.LOB,
// SPACE=(TRK,(5,5),RLSE),
// UNIT=SYSDA,
// DISP=(,CATLG,DELETE)
//SYSREC DD DSN=DB2LOD.V3.UNLD.LOB,
// DISP=(,CATLG,DELETE),
// DCB=(BLKSIZE=8192),
// SPACE=(TRK,(20000,20000),RLSE),
// UNIT=SYSDA
//SYSIN DD *
TEMPLATE LOBFRV DSN 'DB2LOD.V4.CLOB'
DSNTYPE(PDS) UNIT(SYSDA) VOLUMES(SORTDS)

UNLOAD TABLESPACE BOR1.T#DETAI2
FROM TABLE
BOR1.AUDIT_DETAIL
(EVENT_ID VARCHAR(64),
DETAIL_ID INTEGER,
SERVER_CUID VARCHAR(64),
DETAIL_TYPE_ID INTEGER,
DETAIL_TEXT VARCHAR(255) CLOBF LOBFRV
)  

DETAIL_TEXT column is CLOB(4 M) column when I get DDL from BOR1.AUDIT_DETAIL table.

When I run this job, he work more than 1 day(very slowly) and then ended with error. In this job the weakness

is TEMPLATE LOBFRV with DSNTYPE(PDS). The maximum size is 65565 Tracks only and PDS dataset

not support multi volume features. Maybe have other way to unload big table and faster unload?

And I can instead of  type VARCHAR(255) put other type?
 

 

Edited By:
Oleg Dayneko[Organization Members] @ Dec 05, 2017 - 05:16 PM (GMT+3)

Michael Arlebrandt

DB2v10.1 for Z/OS.How to unload table that contains lob column.
(in response to Oleg Dayneko)
Hi Oleg,

You can use SPANNED YES to unload data in spanned format and the clob data will be stored to the same sysrec as the base table,
By that you can use extended format and multivolume and it’s much faster than using CLOB to a PDS

Example:

//UNLD001 EXEC DSNUPROC,SYSTEM=D2VT,
// UID='XL100101',UTPROC='',COND=(4,LT)
//SYSREC DD DSN=V070674.DBCLONE.UNL.N001.S001.SYSR,
// DISP=(NEW,CATLG),DATACLAS=PSEXB, Extended eXtra Big at our shop ☺
// MGMTCLAS=DEL20
//SYSPUNCH DD DSN=V070674.DBCLONE.UNL.N001.S001.SYSP,
// DISP=(NEW,CATLG),DATACLAS=PSEN,
// MGMTCLAS=DEL20
//SYSIN DD *

UNLOAD TABLESPACE DMIARLT1.SMIARL04
SPANNED YES
FROM TABLE "mikes"."TEST_TABLE_04"
( NO
INTEGER
, DATE
CHAR
, TIME
CHAR
, TIMESTAMP
TIMESTAMP EXTERNAL
, TEXT
CLOB -- note for spanned format the lob column(s) need to be the last columns
)
/*

Best regards
Michael Arlebrandt
HCL Sweden

From: Oleg Dayneko [mailto:[login to unmask email]
Sent: den 5 december 2017 3:15
To: [login to unmask email]
Subject: [DB2-L] - DB2v10.1 for Z/OS.How to unload table that contains lob column.


Hi. I try to unload table that contains CLOB column.

This is my job for example:

//UNLOADJ JOB REGION=0K
//UNLD1 EXEC DSNUPROC,SYSTEM=TST1,
// UID=''
//SYSPUNCH DD DSN=DB2LOD.V3.CNTL.LOB,
// SPACE=(TRK,(5,5),RLSE),
// UNIT=SYSDA,
// DISP=(,CATLG,DELETE)
//SYSREC DD DSN=DB2LOD.V3.UNLD.LOB,
// DISP=(,CATLG,DELETE),
// DCB=(BLKSIZE=8192),
// SPACE=(TRK,(20000,20000),RLSE),
// UNIT=SYSDA
//SYSIN DD *
TEMPLATE LOBFRV DSN 'DB2LOD.V4.CLOB'
DSNTYPE(PDS) UNIT(SYSDA) VOLUMES(SORTDS)

UNLOAD TABLESPACE BOR1.T#DETAI2
FROM TABLE
BOR1.AUDIT_DETAIL
(EVENT_ID VARCHAR(64),
DETAIL_ID INTEGER,
SERVER_CUID VARCHAR(64),
DETAIL_TYPE_ID INTEGER,
DETAIL_TEXT VARCHAR(255) CLOBF LOBFRV
)

DETAIL_TEXT column is CLOB(4 M) column when I get DDL from BOR1.AUDIT_DETAIL table.

When I run this job, he work more than 1 day(very slowly) and then ended with error. In this job i think, that weakness

is TEMPLATE LOBFRV with DSNTYPE(PDS). The maximum size is 65565 Tracks only and PDS dataset

not support multi volume features. Maybe have other way to unload big table and faster unload?

And I can instead of type VARCHAR(255) put other type?




-----End Original Message-----
::DISCLAIMER:: ---------------------------------------------------------------------------------------------------------------------------------------------------- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates. Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of authorized representative of HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any email and/or attachments, please check them for viruses and other defects. ----------------------------------------------------------------------------------------------------------------------------------------------------

Michael Arlebrandt

DB2v10.1 for Z/OS.How to unload table that contains lob column.
(in response to Michael Arlebrandt)
Sorry, but SPANNED YES I believe came in v11 and you’re v10 ☹

Back in v10 we preferred to unload LOB to HFS instead of PDS/PDSE.

/Michael

From: Michael Arlebrandt [mailto:[login to unmask email]
Sent: den 5 december 2017 3:27
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2v10.1 for Z/OS.How to unload table that contains lob column.

Hi Oleg,

You can use SPANNED YES to unload data in spanned format and the clob data will be stored to the same sysrec as the base table,
By that you can use extended format and multivolume and it’s much faster than using CLOB to a PDS

Example:

//UNLD001 EXEC DSNUPROC,SYSTEM=D2VT,
// UID='XL100101',UTPROC='',COND=(4,LT)
//SYSREC DD DSN=V070674.DBCLONE.UNL.N001.S001.SYSR,
// DISP=(NEW,CATLG),DATACLAS=PSEXB, Extended eXtra Big at our shop ☺
// MGMTCLAS=DEL20
//SYSPUNCH DD DSN=V070674.DBCLONE.UNL.N001.S001.SYSP,
// DISP=(NEW,CATLG),DATACLAS=PSEN,
// MGMTCLAS=DEL20
//SYSIN DD *

UNLOAD TABLESPACE DMIARLT1.SMIARL04
SPANNED YES
FROM TABLE "mikes"."TEST_TABLE_04"
( NO
INTEGER
, DATE
CHAR
, TIME
CHAR
, TIMESTAMP
TIMESTAMP EXTERNAL
, TEXT
CLOB -- note for spanned format the lob column(s) need to be the last columns
)
/*

Best regards
Michael Arlebrandt
HCL Sweden

From: Oleg Dayneko [mailto:[login to unmask email]
Sent: den 5 december 2017 3:15
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - DB2v10.1 for Z/OS.How to unload table that contains lob column.


Hi. I try to unload table that contains CLOB column.

This is my job for example:

//UNLOADJ JOB REGION=0K
//UNLD1 EXEC DSNUPROC,SYSTEM=TST1,
// UID=''
//SYSPUNCH DD DSN=DB2LOD.V3.CNTL.LOB,
// SPACE=(TRK,(5,5),RLSE),
// UNIT=SYSDA,
// DISP=(,CATLG,DELETE)
//SYSREC DD DSN=DB2LOD.V3.UNLD.LOB,
// DISP=(,CATLG,DELETE),
// DCB=(BLKSIZE=8192),
// SPACE=(TRK,(20000,20000),RLSE),
// UNIT=SYSDA
//SYSIN DD *
TEMPLATE LOBFRV DSN 'DB2LOD.V4.CLOB'
DSNTYPE(PDS) UNIT(SYSDA) VOLUMES(SORTDS)

UNLOAD TABLESPACE BOR1.T#DETAI2
FROM TABLE
BOR1.AUDIT_DETAIL
(EVENT_ID VARCHAR(64),
DETAIL_ID INTEGER,
SERVER_CUID VARCHAR(64),
DETAIL_TYPE_ID INTEGER,
DETAIL_TEXT VARCHAR(255) CLOBF LOBFRV
)

DETAIL_TEXT column is CLOB(4 M) column when I get DDL from BOR1.AUDIT_DETAIL table.

When I run this job, he work more than 1 day(very slowly) and then ended with error. In this job i think, that weakness

is TEMPLATE LOBFRV with DSNTYPE(PDS). The maximum size is 65565 Tracks only and PDS dataset

not support multi volume features. Maybe have other way to unload big table and faster unload?

And I can instead of type VARCHAR(255) put other type?




-----End Original Message-----
::DISCLAIMER:: ---------------------------------------------------------------------------------------------------------------------------------------------------- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates. Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of authorized representative of HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any email and/or attachments, please check them for viruses and other defects. ----------------------------------------------------------------------------------------------------------------------------------------------------
-----End Original Message-----

Oleg Dayneko

RE: DB2v10.1 for Z/OS.How to unload table that contains lob column.
(in response to Michael Arlebrandt)

Thank you. I try to create big HFS and unload lob into OMVS. Later I say about result.

Robert Plata

DB2v10.1 for Z/OS.How to unload table that contains lob column.
(in response to Michael Arlebrandt)
We are currently on V10 and SPANNED YES is available. We use it.

HTH

Robert

From: Michael Arlebrandt [mailto:[login to unmask email]
Sent: Tuesday, December 05, 2017 6:32 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2v10.1 for Z/OS.How to unload table that contains lob column.

Sorry, but SPANNED YES I believe came in v11 and you’re v10 ☹

Back in v10 we preferred to unload LOB to HFS instead of PDS/PDSE.

/Michael

From: Michael Arlebrandt [mailto:[login to unmask email]
Sent: den 5 december 2017 3:27
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: DB2v10.1 for Z/OS.How to unload table that contains lob column.

Hi Oleg,

You can use SPANNED YES to unload data in spanned format and the clob data will be stored to the same sysrec as the base table,
By that you can use extended format and multivolume and it’s much faster than using CLOB to a PDS

Example:

//UNLD001 EXEC DSNUPROC,SYSTEM=D2VT,
// UID='XL100101',UTPROC='',COND=(4,LT)
//SYSREC DD DSN=V070674.DBCLONE.UNL.N001.S001.SYSR,
// DISP=(NEW,CATLG),DATACLAS=PSEXB, Extended eXtra Big at our shop ☺
// MGMTCLAS=DEL20
//SYSPUNCH DD DSN=V070674.DBCLONE.UNL.N001.S001.SYSP,
// DISP=(NEW,CATLG),DATACLAS=PSEN,
// MGMTCLAS=DEL20
//SYSIN DD *

UNLOAD TABLESPACE DMIARLT1.SMIARL04
SPANNED YES
FROM TABLE "mikes"."TEST_TABLE_04"
( NO
INTEGER
, DATE
CHAR
, TIME
CHAR
, TIMESTAMP
TIMESTAMP EXTERNAL
, TEXT
CLOB -- note for spanned format the lob column(s) need to be the last columns
)
/*

Best regards
Michael Arlebrandt
HCL Sweden

From: Oleg Dayneko [mailto:[login to unmask email]
Sent: den 5 december 2017 3:15
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - DB2v10.1 for Z/OS.How to unload table that contains lob column.


Hi. I try to unload table that contains CLOB column.

This is my job for example:

//UNLOADJ JOB REGION=0K
//UNLD1 EXEC DSNUPROC,SYSTEM=TST1,
// UID=''
//SYSPUNCH DD DSN=DB2LOD.V3.CNTL.LOB,
// SPACE=(TRK,(5,5),RLSE),
// UNIT=SYSDA,
// DISP=(,CATLG,DELETE)
//SYSREC DD DSN=DB2LOD.V3.UNLD.LOB,
// DISP=(,CATLG,DELETE),
// DCB=(BLKSIZE=8192),
// SPACE=(TRK,(20000,20000),RLSE),
// UNIT=SYSDA
//SYSIN DD *
TEMPLATE LOBFRV DSN 'DB2LOD.V4.CLOB'
DSNTYPE(PDS) UNIT(SYSDA) VOLUMES(SORTDS)

UNLOAD TABLESPACE BOR1.T#DETAI2
FROM TABLE
BOR1.AUDIT_DETAIL
(EVENT_ID VARCHAR(64),
DETAIL_ID INTEGER,
SERVER_CUID VARCHAR(64),
DETAIL_TYPE_ID INTEGER,
DETAIL_TEXT VARCHAR(255) CLOBF LOBFRV
)

DETAIL_TEXT column is CLOB(4 M) column when I get DDL from BOR1.AUDIT_DETAIL table.

When I run this job, he work more than 1 day(very slowly) and then ended with error. In this job i think, that weakness

is TEMPLATE LOBFRV with DSNTYPE(PDS). The maximum size is 65565 Tracks only and PDS dataset

not support multi volume features. Maybe have other way to unload big table and faster unload?

And I can instead of type VARCHAR(255) put other type?




-----End Original Message-----
::DISCLAIMER:: ---------------------------------------------------------------------------------------------------------------------------------------------------- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates. Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of authorized representative of HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any email and/or attachments, please check them for viruses and other defects. ----------------------------------------------------------------------------------------------------------------------------------------------------
-----End Original Message-----

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

Javier Estrada Benavides

RE: DB2v10.1 for Z/OS.How to unload table that contains lob column.
(in response to Robert Plata)

Hi Oleg,

  How did it go with the HFS?

When unloading big tables with LOB columns I always choose to unload to a zFS because of the time it takes (on this machine, if I unload to a PDS and it takes more than 4 hours, it usually takes only 30 mins when unloading to a zFS).

 

For that, I also use a zFS allocated in a prefix that has a data class with extended addressability and multi volume capabilities.

 

Regards,

Javier Estrada Benavides, Mexico

IBM Certified System Administrator - DB2 11 for z/OS

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

Oleg Dayneko

RE: DB2v10.1 for Z/OS.How to unload table that contains lob column.
(in response to Michael Arlebrandt)

I succesfull unload then load this table usage UNLOAD utility with option SPANNED YES.

The elapsed time is 4 hours(On PDS take 12 hours then occurred  logical overflow tracks in data set).

Thanks and kind regards.