IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Move LOB/CLOB column data to PS


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Vishwamurthy

New User


Joined: 11 Mar 2008
Posts: 57
Location: India

PostPosted: Tue Jan 19, 2010 12:57 pm
Reply with quote

Hi,
Is there any way to move data from a table to file, when the data in the table contains a CLOB/LOB column (length > 131000)

Any solution or pointers will be appreciated
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Tue Jan 19, 2010 5:42 pm
Reply with quote

DSNTIAUL should do the trick. Do you have BMC tools for DB2 available - that is another place to look for.
Back to top
View user's profile Send private message
Vishwamurthy

New User


Joined: 11 Mar 2008
Posts: 57
Location: India

PostPosted: Tue Jan 19, 2010 5:49 pm
Reply with quote

Thanks Anuj!
The CLOB field is of length > 130100 (obviously > 32KB) Maximum record size in a PS can not exceed 32KB rite.. We have BMC available!! Actually, the CLOB data comes from one table.. We have to copy it to a file.. Load to another table (with corresponding CLOB field) from this file..

How do we handle this?

Thanks icon_smile.gif
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Tue Jan 19, 2010 5:54 pm
Reply with quote

Quote:
Maximum record size in a PS can not exceed 32KB rite.
You're behind the times -- this is not always true.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Jan 19, 2010 6:57 pm
Reply with quote

Viswa,

DSNTIAUL with LOBFILE option.

Sushanth
Back to top
View user's profile Send private message
vimalravi83

New User


Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL

PostPosted: Sun Feb 28, 2010 2:41 pm
Reply with quote

BMCUNLOAD has restriction to unload LOB data to a PS dataset. It will unload LOB field, only if it is null or space.I am facing this problem now.
Sushanth,
Can you please give me one example for DSBTIAUL with LOBFILE option? I would like to try one.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Mon Mar 01, 2010 3:02 pm
Reply with quote

Vimal,

In DB2 V9 Application Programming & SQL guide --> Search for this "Example of using DSNTIAUL to unload LOB data"

You will find below
Code:
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN  DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB91) -
PARMS('SQL,2,LOBFILE(DSN8UNLD)') -
LIB('DSN910.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
//          UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
//          VOL=SER=SCR03,RECFM=FB
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
//       UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
//       VOL=SER=SCR03,RECFM=FB
//SYSIN DD *
SELECT * FROM DSN8910.EMP_PHOTO_RESUME;


Give it a read, you may find interesting things.

Thank You,
Sushanth
Back to top
View user's profile Send private message
vimalravi83

New User


Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL

PostPosted: Tue Mar 02, 2010 5:29 am
Reply with quote

That's cool.. icon_kidra.gif Sushant. Thanks a lot. I will try this out.
Does PARMS('SQL,2,LOBFILE(DSN8UNLD)') have importance?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Mar 02, 2010 9:16 am
Reply with quote

Vimal,

Quote:
Does PARMS('SQL,2,LOBFILE(DSN8UNLD)') have importance?

Yes or May be No. Actually, it depends on your requirement & LOB size, for vishwa its a Yes.
Read page 1078 in the manual which i have refered above.

Sushanth
Back to top
View user's profile Send private message
vimalravi83

New User


Joined: 28 Feb 2010
Posts: 21
Location: Bloomington, IL

PostPosted: Wed Mar 03, 2010 12:12 pm
Reply with quote

Hi Sushant,
I was successfully able to unload all the data from table including LOB data into a flat file. Now if I want to load those rows back, which utility can i use. I tried BMCLOAD with the same SYSREC file, but it failed.Seems to be BMC don't like the format of SYSREC file created by DSNTIAUL utlity.
icon_rolleyes.gif
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Mar 03, 2010 12:17 pm
Reply with quote

Hi Vimal,

Quote:
Seems to be BMC don't like the format of SYSREC file created by DSNTIAUL utlity

I wouldn't say that, without looking into BMC load manuals. We use CA tools, so i can't do much help here.

For IBM, you have use LOAD utility, book will be DB2 V(your version)Utility Reference.

Sushanth
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts How to load to DB2 with column level ... DB2 6
No new posts SCOPE PENDING option -check data DB2 2
No new posts RC query -Time column CA Products 3
No new posts Remote Unload of CLOB Columns DB2 6
Search our Forums:

Back to Top