DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
and
Participant



Joined: 05 Oct 2017
Posts: 14

Points: 160

Post Posted: Thu Jan 18, 2018 5:35 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
Hi all!

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

Code:
PROCEDURE TEST1(p_in1 in varchar2, p_out1 out clob)


I have some trouble with output from the stored procedure stage Sad

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

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42532
Location: Denver, CO
Points: 218864

Post Posted: Thu Jan 18, 2018 7:35 am Reply with quote    Back to top    

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

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
asorrell
Site Admin

Group memberships:
Premium Members, DSXchange Team, Inner Circle, Server to Parallel Transition Group

Joined: 04 Apr 2003
Posts: 1669
Location: Colleyville, Texas
Points: 22716

Post Posted: Thu Jan 18, 2018 5:36 pm Reply with quote    Back to top    

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 - 2017
Rate this response:  
Not yet rated
and
Participant



Joined: 05 Oct 2017
Posts: 14

Points: 160

Post Posted: Thu Jan 18, 2018 11:38 pm Reply with quote    Back to top    

thanks chulett and asorrell

Quote:
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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42532
Location: Denver, CO
Points: 218864

Post Posted: Fri Jan 19, 2018 7:26 am Reply with quote    Back to top    

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

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
and
Participant



Joined: 05 Oct 2017
Posts: 14

Points: 160

Post Posted: Sat Jan 20, 2018 1:12 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42532
Location: Denver, CO
Points: 218864

Post Posted: Sat Jan 20, 2018 10:34 am Reply with quote    Back to top    

Not to be funny, but... and?

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
asorrell
Site Admin

Group memberships:
Premium Members, DSXchange Team, Inner Circle, Server to Parallel Transition Group

Joined: 04 Apr 2003
Posts: 1669
Location: Colleyville, Texas
Points: 22716

Post Posted: Tue Jan 23, 2018 3:18 pm Reply with quote    Back to top    

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.wss?uid=swg21416107

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 - 2017
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42532
Location: Denver, CO
Points: 218864

Post Posted: Tue Jan 23, 2018 3:57 pm Reply with quote    Back to top    

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

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
and
Participant



Joined: 05 Oct 2017
Posts: 14

Points: 160

Post Posted: Wed Jan 31, 2018 3:55 am Reply with quote    Back to top    

asorrell, chulett
thanks

i tried following
- created sample table

Code:
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:
<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:
<test>
<x>
x as value
</x>
</test>
Peek_6,0: X:<test>
<y>
y as value
</y>
</test>


please help me in assembling xml
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54198
Location: Sydney, Australia
Points: 293951

Post Posted: Thu Feb 01, 2018 12:57 am Reply with quote    Back to top    

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.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
and
Participant



Joined: 05 Oct 2017
Posts: 14

Points: 160

Post Posted: Thu Feb 01, 2018 9:22 am Reply with quote    Back to top    

thanks for note

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

please compare
Code:
<test>
<x>
</test>
<test>
x as value
</test>
<test>
</x>
</test>


and expectation
Quote:
<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 )
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42532
Location: Denver, CO
Points: 218864

Post Posted: Thu Feb 01, 2018 10:31 am Reply with quote    Back to top    

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

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
and
Participant



Joined: 05 Oct 2017
Posts: 14

Points: 160

Post Posted: Thu Feb 01, 2018 10:45 am Reply with quote    Back to top    

>> to chop it up into chunks
it in dbms

Code:
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 ?
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42532
Location: Denver, CO
Points: 218864

Post Posted: Thu Feb 01, 2018 1:22 pm Reply with quote    Back to top    

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

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours