Stored Procedure stage with Longvarchar (CLOB)

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

and
Participant
Posts: 14
Joined: Thu Oct 05, 2017 5:36 am

Stored Procedure stage with Longvarchar (CLOB)

Post by and »

Hi all!

I have job with a Stored Procedure stage and Oracle procedure like

Code: Select all

PROCEDURE TEST1(p_in1 in varchar2, p_out1 out clob)
I have some trouble with output from the stored procedure stage :(

If I set type for output in stored procedure stage as "varchar" the job works.

But I need to return "Clob" (or "xmltype") so I changed type of output to "longvarchar" and this does not work and error is:

Code: Select all

Stored_Procedure_17,0: Error: Datatype not presently supported
Is it a limitation of the Stored Procedure stage ? Or is it a bug ?

Thanks!
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Based on the message, I'd go with "limitation". Best to open up a support case if you want an official answer and to see what, if any, options you have.
-craig

"You can never have too many knives" -- Logan Nine Fingers
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

It isn't supported. I heard they are adding better support for CLOB in 11.7 (mainly for Hadoop), but don't know if it would apply in this case. Customer services should be able to confirm if its in 11.7 or not. However, that doesn't really fix your current issue.

I encountered this once and had to slice up the XML CLOB into manageable "chunks", send the pieces, and then reassemble them afterwards. That wasn't a preferred solution, but it worked.
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
and
Participant
Posts: 14
Joined: Thu Oct 05, 2017 5:36 am

Post by and »

thanks chulett and asorrell
I encountered this once and had to slice up the XML CLOB into manageable "chunks", send the pieces, and then reassemble them afterwards. That wasn't a preferred solution, but it worked.
nice note

can you please give me some hints on it

did you slice up XML/CLOB by dbms and return "chunks" to datastage and assemble ?


thanks
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Yes, that's exactly what he means and something I've done before as well.
-craig

"You can never have too many knives" -- Logan Nine Fingers
and
Participant
Posts: 14
Joined: Thu Oct 05, 2017 5:36 am

Post by and »

i have to shed some light to my previous reply

if dbms stored procedure use only one row with clob value to return then it's ok to slice the clob up

what if dbms stored procedure use cursor to return many rows ( my case ) and so in datastage you'll have many "chunks" of many row's clob and so you have to identify needed "chunks" to assemble into clob/LongVarChar and in this case it's tricky
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Not to be funny, but... and?
-craig

"You can never have too many knives" -- Logan Nine Fingers
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

Yes - you might have to establish some sort of key structure / row count in order to reassemble the chunks.

Just realize - if the resulting data record including the re-assembled CLOB exceeds 128K, then it can't be done in DataStage. On the release you are on, 131,072 bytes is the hard limit on record size.

http://www-01.ibm.com/support/docview.w ... wg21416107

If it is a CLOB is it a conglomeration of some sort that can be split out on a logical sub-record basis?
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Andy - they are on 11.x and your support link is for some of the original releases. Is that hard limit still actually an issue?
-craig

"You can never have too many knives" -- Logan Nine Fingers
and
Participant
Posts: 14
Joined: Thu Oct 05, 2017 5:36 am

Post by and »

asorrell, chulett
thanks

i tried following
- created sample table

Code: Select all

x_control    x_ret
   1         <x>
   1         x as value
   1         </x>
   2         <y>
   2         y as value
   2         </y>
- create oracle dbms procedure with cursor based on above table
- in datastage add stored procedure stage
- after stored procedure stage was added xml output

for output column
use trigger column - x_control

and result

Code: Select all

<test>
<x>
</test>
<test>
x as value
</test>
<test>
</x>
</test>
Peek_6,0: X:<test>
<y>
</test>
<test>
y as value
</test>
<test>
</y>
</test>
but expected something like

Code: Select all

<test>
<x>
x as value
</x>
</test>
Peek_6,0: X:<test>
<y>
y as value
</y>
</test>
please help me in assembling xml
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

There are plenty of XML prettifiers out there; it's not the task of an ETL tool. The XML produced is valid, and that's all that's important. Format is largely irrelevant in XML.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
and
Participant
Posts: 14
Joined: Thu Oct 05, 2017 5:36 am

Post by and »

thanks for note

but there are some moments:
-- i'm talking not about pretty printing
-- it's not well-formed xml

please compare

Code: Select all

<test> 
<x> 
</test> 
<test> 
x as value 
</test> 
<test> 
</x> 
</test>
and expectation
<test>
<x>
x as value
</x>
</test>
in last case xml contains one root

and imho it have to be a result of xml output stage ( use trigger column - x_control and sample data in previous reply )
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Color me a little lost here. I thought you already had XML in the CLOB and just needed to chop it up into chunks and then reassemble them in the job. If that's true, why involve an XML Output stage? If that's not true, have we moved on to a different topic?
-craig

"You can never have too many knives" -- Logan Nine Fingers
and
Participant
Posts: 14
Joined: Thu Oct 05, 2017 5:36 am

Post by and »

>> to chop it up into chunks
it in dbms

Code: Select all

x_control    x_ret 
   1         <x> 
   1         x as value 
   1         </x> 
   2         <y> 
   2         y as value 
   2         </y>
it is returned by stored procedure stage with cursor

>> and then reassemble them in the job
in job, yes
xml output stage with use trigger column - x_control

may be i wrong about xml output to reassemble and may be i need to use something else ?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

First off, there's no reason to quote everything you are replying to, there's a simple Reply to topic link at the top and bottom of every page. Quote stuff when it's relevant / needed. Saves me having to clean up things. :wink:

So, it's not already well-formed, complete XML stored in its entirety as a single CLOB? Instead it is just the 'core' data elements which have already been cut up and stored as multiple records in a relational table? Meaning you then need to not only put the records back together but also wrap it to make it 'well-formed' as well. If so, we seem to have moved past the original discussion which was a bit more... straight-forward.

What does the "x_control" column represent? I see that they mark each tag but I don't see that being especially useful here. Can you explain how you are attempting to use them, please? I'm assuming Andy meant something more like a simple ascending "chunk number" so that you could properly "assemble" / concatenate back together the long string that is a piece of well-formed XML. And those chunks would literally be chopped up string pieces, all cut to the same size, useless on their own, simply so they fit out the door.

Just want to make sure we're all on the same page with regards to the problem you are trying to solve.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply