View previous topic :: View next topic
|
Author |
Message |
Vishwamurthy
New User
Joined: 11 Mar 2008 Posts: 57 Location: India
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
DSNTIAUL should do the trick. Do you have BMC tools for DB2 available - that is another place to look for. |
|
Back to top |
|
|
Vishwamurthy
New User
Joined: 11 Mar 2008 Posts: 57 Location: India
|
|
|
|
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 |
|
Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Viswa,
DSNTIAUL with LOBFILE option.
Sushanth |
|
Back to top |
|
|
vimalravi83
New User
Joined: 28 Feb 2010 Posts: 21 Location: Bloomington, IL
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
vimalravi83
New User
Joined: 28 Feb 2010 Posts: 21 Location: Bloomington, IL
|
|
|
|
That's cool.. Sushant. Thanks a lot. I will try this out.
Does PARMS('SQL,2,LOBFILE(DSN8UNLD)') have importance? |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
vimalravi83
New User
Joined: 28 Feb 2010 Posts: 21 Location: Bloomington, IL
|
|
|
|
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.
|
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
|