CLOB Column data to External File using File-Reference Var

lalitmehta12 · New User Joined: 22 May 2007 Posts: 5 Location: Mumbai

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.

enrico-sorichetti · Posted: Tue Aug 21, 2018 8:00 pm

DB2 knows/does nothing about a CLOB content, it is just a string of consecutive bytes

lalitmehta12 · New User Joined: 22 May 2007 Posts: 5 Location: Mumbai

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?

lalitmehta12 · New User Joined: 22 May 2007 Posts: 5 Location: Mumbai

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

Rohit Umarjikar · Posted: Wed Aug 22, 2018 2:15 am

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.

expat · Posted: Wed Aug 22, 2018 11:32 am

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 ???

Marso · Posted: Wed Aug 22, 2018 5:28 pm

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:

lalitmehta12 · New User Joined: 22 May 2007 Posts: 5 Location: Mumbai

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.

Nic Clouston · Posted: Thu Aug 23, 2018 5:45 pm

Which red book? There are many and several relating to DB2.
Did you refer to the "Application Programming and SQL Guide"?

lalitmehta12 · New User Joined: 22 May 2007 Posts: 5 Location: Mumbai

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.