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
 
CLOB Column data to External File using File-Reference Var

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: CLOB Column data to External File using File-Reference Var
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

Senior Member


Joined: 14 Mar 2007
Posts: 10543
Location: italy

PostPosted: Tue Aug 21, 2018 8:00 pm    Post subject: Reply to: CLOB Column data to External File using File-Reference Var
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    Post subject: Reply to: CLOB Column data to External File using File-Reference Var
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    Post subject: Reply to: CLOB Column data to External File using File-Reference Var
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

Senior Member


Joined: 21 Sep 2010
Posts: 2064
Location: NY,USA

PostPosted: Wed Aug 22, 2018 2:15 am    Post subject:
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: 8747
Location: Welsh Wales

PostPosted: Wed Aug 22, 2018 11:32 am    Post subject:
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: 1303
Location: Israel

PostPosted: Wed Aug 22, 2018 5:28 pm    Post subject: Re: Reply to: CLOB Column data to External File using File-Reference Var
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    Post subject: Reply to: CLOB Column data to External File using File-Reference Var
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: 2126
Location: UK

PostPosted: Thu Aug 23, 2018 5:45 pm    Post subject: Reply to: CLOB Column data to External File using File-Reference Var
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    Post subject: Reply to: CLOB Column data to External File using File-Reference Var
Reply with quote

Hi Nic,

IBM Redbook.
http://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    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 Transferring file to Mainframes Shinoj Vijayakumar IBM Tools 3 Thu Dec 06, 2018 2:35 pm
No new posts Convert VBA file to FB! Vignesh Sid SYNCSORT 5 Mon Dec 03, 2018 1:27 pm
No new posts To get the count of PDS data sets whi... Vignesh Sid JCL & VSAM 4 Fri Nov 30, 2018 8:38 pm
No new posts Another 39 File Status on VB file socker_dad COBOL Programming 5 Tue Nov 27, 2018 6:11 am
No new posts How to handle different file length b... javen777 DFSORT/ICETOOL 15 Mon Nov 26, 2018 12:13 pm

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