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

CLOB Column data to External File using File-Reference Var


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

New User


Joined: 22 May 2007
Posts: 5
Location: Mumbai

PostPosted: Tue Aug 21, 2018 7:22 pm
Reply with quote

Hello All,

I have a requirement to insert a Sequential file to CLOB column in a DB2 table and retrieve it back into an external Sequential file again for later processing.

I tried using a COBOL program and File-Reference variables. Both Insert and select are working fine.

Below is the issue:
Input file Sample records
ABCD123ABCD123
PQRS456PQRS456
TUVW789TUVW789

Resulting Output file. (All input records combined into a single record)
ABCD123ABCD123PQRS456PQRS456TUVW789TUVW789

This is not correct.
Expected Output file should be same as Input
ABCD123ABCD123
PQRS456PQRS456
TUVW789TUVW789


Can anyone please suggest what could be going wrong here or what I might be missing.

Please let me know if you need more information.

Thanks,
Lalit.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10770
Location: italy

PostPosted: Tue Aug 21, 2018 8:00 pm
Reply with quote

DB2 knows/does nothing about a CLOB content, it is just a string of consecutive bytes
Back to top
View user's profile Send private message
lalitmehta12

New User


Joined: 22 May 2007
Posts: 5
Location: Mumbai

PostPosted: Tue Aug 21, 2018 8:12 pm
Reply with quote

Thanks Enrico

I agree with you.

I am not sure why but at present when I do the extract using file-reference variables via a COBOL program, all the records in the original input file are merged into a single record when unloaded into the output file.


Am I missing anything during the insert or during the select query?
Back to top
View user's profile Send private message
lalitmehta12

New User


Joined: 22 May 2007
Posts: 5
Location: Mumbai

PostPosted: Tue Aug 21, 2018 8:14 pm
Reply with quote

1 more important information

Input file length is 133
DB2 creates the output file by default of length around 27k.

Is the Input file length causing this problem
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2638
Location: NYC,USA

PostPosted: Wed Aug 22, 2018 2:15 am
Reply with quote

Use DFSORT SPLICE or RESIZE to 133 post DB2 unload to get into multiple records.

You are inserting one row with all the records from the data set so how do you expect it to save it in the same manner as data set has? If you do those many inserts as number of records in the file then you could get back exactly as you have it in the input data set.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Wed Aug 22, 2018 11:32 am
Reply with quote

Why not keep the original file and use that for later processing as it must be the same as the original file.

So who came up with this scatterbrained req ???
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1348
Location: Israel

PostPosted: Wed Aug 22, 2018 5:28 pm
Reply with quote

You didn't say how you restore the sequential file from the CLOB column.
If you are using the same/another COBOL program, you could, during your "backup", add a separator between each record, and during the "restore", find the separator and split the records back:
Quote:
Input file Sample records
ABCD123ABCD123
PQRS456PQRS456
TUVW789TUVW789

Contents of column. (All input records combined into a single record)
ABCD123ABCD123$$PQRS456PQRS456$$TUVW789TUVW789

Output file same as Input:
ABCD123ABCD123
PQRS456PQRS456
TUVW789TUVW789

Do your COBOL program do other process, or is it just used to copy the file ?
Back to top
View user's profile Send private message
lalitmehta12

New User


Joined: 22 May 2007
Posts: 5
Location: Mumbai

PostPosted: Thu Aug 23, 2018 4:40 pm
Reply with quote

Hi expat,

I agree the requirement is weird. But the application is supposed to generate thousands of formatted reports and send to multiple destinations whenever requested. It is costly to generate report for every destination. So we intend to generate the reports once, store them into CLOB columns and send them as it is to all the destinations.

Hi Marso,

Thanks for the suggestion.
I am working to write program to use CLOB Locators.

The question is DB2 has the provision to move the data directly from a PS file to CLOB column and from CLOB to PS file using File reference variables. This does not requires the application program to allocate any memory. This seems to be an ideal and efficient solution for my requirement. I am trying to understand what did I miss that its not working as expected.

I referred the redbook which has this example to load and unload PS files into CLOB columns and I did exactly as given.

Thanks.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Thu Aug 23, 2018 5:45 pm
Reply with quote

Which red book? There are many and several relating to DB2.
Did you refer to the "Application Programming and SQL Guide"?
Back to top
View user's profile Send private message
lalitmehta12

New User


Joined: 22 May 2007
Posts: 5
Location: Mumbai

PostPosted: Thu Aug 23, 2018 6:18 pm
Reply with quote

Hi Nic,

IBM Redbook.
www.redbooks.ibm.com/abstracts/sg247270.html?Open

Yes. I checked "Application Programming and SQL Guide"
Page 752 "Ways to manipulate LOB data". It has material for File-Reference variables as well

It also has some examples which I am not able to find in my system. I will check with DBAs where they have stored these sample programs.

Thanks.
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 rexx pgm to create multiple ps file IBM Tools 7
No new posts Remove commas from specific positions... DFSORT/ICETOOL 10
No new posts Uploading from desktop, a CSV file, s... DB2 2
No new posts Get two files from one file with diff... DFSORT/ICETOOL 10
No new posts View GDG data which is copied from a ... JCL & VSAM 2
Search our Forums:

Back to Top