Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Move LOB/CLOB column data to PS

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Move LOB/CLOB column data to PS
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

Senior Member


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

PostPosted: Tue Jan 19, 2010 5:42 pm    Post subject:
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    Post subject: Reply to: Move LOB/CLOB column data to PS
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: 8165
Location: East Dubuque, Illinois, USA

PostPosted: Tue Jan 19, 2010 5:54 pm    Post subject:
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: 1013
Location: India

PostPosted: Tue Jan 19, 2010 6:57 pm    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Mon Mar 01, 2010 3:02 pm    Post subject:
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    Post subject: Reply to: Move LOB/CLOB column data to PS
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: 1013
Location: India

PostPosted: Tue Mar 02, 2010 9:16 am    Post subject:
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    Post subject: Reply to: Move LOB/CLOB column data to PS
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: 1013
Location: India

PostPosted: Wed Mar 03, 2010 12:17 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm
No new posts opening a dataset after reading it fr... arunsoods DFSORT/ICETOOL 5 Wed Oct 04, 2017 3:54 pm
This topic is locked: you cannot edit posts or make replies. PS file data should be passed as symb... d_sarlie JCL & VSAM 15 Tue Oct 03, 2017 5:18 am
No new posts File Aid tool to compare numeric data balaji81_k Compuware & Other Tools 2 Tue Sep 26, 2017 3:35 am
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us