Create ISD job for a web service with RESTFUL JSON format

Dedicated to DataStage and DataStage TX editions featuring IBM<sup>®</sup> Service-Oriented Architectures.

Moderators: chulett, rschirm

yijuanl
Participant
Posts: 23
Joined: Fri Jun 24, 2005 8:32 am

Create ISD job for a web service with RESTFUL JSON format

Post by yijuanl »

Hi
My goal is to create a web service as an ISD job with a heirarchy structure data in RESTFUL JSON format.
Here is my design.
Oracle Stage to query Master data having Lookup stage to get detail data -> Join Stage -> XML output to get a heirarchy structure data -> what stage ??? to call a java class which converts XML to JSON -> What stage ??? to read JSON file and output it to -> ISD output

Is there any better design to achieve my goal?

Thank you very much in advance!
Jane Liu
yl
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

Use the JSON option in the REST binding ---- it will deliver JSON directly. It won't be deeply hierarchical ---- just a single repeating node in JSON for your final output link to the ISDoutput Stage.

You'll have it working in minutes.

Doing custom deeper hierarchicak JSON in the current release will require a lot more effort and a different binding.

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
yijuanl
Participant
Posts: 23
Joined: Fri Jun 24, 2005 8:32 am

Post by yijuanl »

The web service with the tree structure data will be used in Gadget for our company's web site. Soi it has to be the restful JSON format for each attribute.

In XML output stage, I define the output as one output file mydata.xml in "Option" tab. But after run, there are many mydata.xml gets generated like mydata_1.xml, mydata_2.xml .... How can I only get one output data file?

Thank you very much!
Jane
yl
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

Why are you using the xml stage? Just send the output link to the ISDoutput stage and then pick one of the REST bindings in ISD and choose JSON (or xml if that's what you really need inside the payload).

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
yijuanl
Participant
Posts: 23
Joined: Fri Jun 24, 2005 8:32 am

Post by yijuanl »

Without XML stage, I will get a relational structure data. I need a heirarchy structure data.
I try to transfer the xml data from xml output stage to a sequential file using one big size column, but my data is really big like 800,000 bytes exceed the max 131072.
Please advice.
Thanks!
Jane
yl
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

Best practice for your success:

a) build the Job and publish it as REST binding with either JSON or XML format. ...both are VERY different formats, albeit similar from a tagging perspective.

b) share the xsd, or share the JSON output with the callers.

c) THAT becomes the standard. Don't fall into the trap of trying to bend your ISD Job to match someone else's specification of the payload. Let the data dictate the payload.

...the difference? 5 minutes of additional development time vs a far more complex Job, a far more complex Web services binding, a far more complex QA and testing scenario, etc. ...not to mention description, documentation and future maintenance time.

If you want to go that detailed route with all the caveats noted, I'll describe how to do it, but I would STRONGLY discourage it.

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
yijuanl
Participant
Posts: 23
Joined: Fri Jun 24, 2005 8:32 am

Post by yijuanl »

I try to follow your advice.

1) Master-detail data from Oracle stage -> XML output(Hierarchy data) -> ISD output.

2) deploy it as Web service in ISD selecting JSON format.

However I got messed up data.

Do you have any document to show step by step on how to do "Topology II job"?

Thank you!
yl
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

My point here is to allow ISD to dictate the structure....it will be in perfect JSON format, or XML if that is what you choose....and in the Job, REMOVE the xmlOutput Stage....

...send the link DIRECTLY to the ISDOutput. You will get a simple hierarchy, albeit a nearly flat one.

...the receiver of the payload will easily be able to format it otherwise if needed.

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

To get it to be an "always on" Job instead of a regular batch Job with only ISDoutput, send your input parameters up thru an ISDinput Stage instead of (what I am guessing you are doing at the moment) as Job Parameters.

...and then perform a lookup with a multi-row return to bring back as many rows as will meet the WHERE clause for your lookup, with the value on the input link coming from the ISDinput Stage, and consequently, from the java client who will have sent up the desired WHERE clause value via SOAP or on the URL if this is REST.

...and as noted in the prior post, send the data directly from the lookup to ISDoutput.

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
yijuanl
Participant
Posts: 23
Joined: Fri Jun 24, 2005 8:32 am

Post by yijuanl »

If I remove the xml output stage, the Database stage gives me relational data, but I need the hierarchy data as Web Service. How can I achieve this?
Thank you very much! -Jane
yl
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

Don't allow a non ctitical requirement dictate your development. Encourage (or just tell) the receivers of the payload that they should accept the "flat" xml or json that is delivered directly by the binding --- the job and its binding will be far simpler and you will have done the team, the organization, and future maintenance people a big favor. As noted earlier, this is the recommended approach.

Short of that, try this:

Use your xmloutputStage, with the output going to one single large column. I will assume you already know how to do that. Adjust your xmloutput stage so that it builds exactly what you expect to have "on the wire".

Now choose the "Text over http" binding. This is a "pass thru" binding --- it simply sends back in http whatever you put into the output link.

Of course --- you lose any nice support for calling mechanisms also --- if you want parameter input you will also have to do that yourself. The input link will receive "whatever" the caller puts into the http payload. Usually xml works well. You will have an xml input stage at the start of your job to pull out whatever variable values thgey send you, probably to pass to your lookup as I described above.

The job is exponentially harder to develop, requiring deeper skills....but it is do-able. Spend a lot of time reading up on the text over http binding and how to call it.

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
yijuanl
Participant
Posts: 23
Joined: Fri Jun 24, 2005 8:32 am

Post by yijuanl »

I already provided the receiver with the flat json Web service, But they insisted on the hierarchy data. Is it legitimate to say that Data stage and ISD can not be used to create a hierarchy structure data RESTful Web Service as JSON format?
Thank you very much!
-Jane
yl
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

It can be done, but not "out of the box". ETL tools are inherently based on a relational engine --- rows and columns --- so the "automated" functions generate fairly simple "flat" json and xml. So I'll say it again --- the value of using DataStage here is the use of your skills and the easy managed access to the source. Keep it simple and have them use the xml " as is"....it's just rows anyway....they should be able to manipulate it further with less difficultly than you will have doing this manually....

You can manage the hierarchy manually for this, but it's a lot more work.

Use one giant column from ISDinput, into xmlInput to parse whatever xml the sender wants to include in their http payload and one giant column going into ISDoutput to send back whatever xml you build in xmlOutput that preceeds it.
The Text over http binding says "don't touch this payload" and ISD just passes it thru.

It's possible that the stylesheet option may help you on the output of the rest binding, if you are skilled in xslt --- but that may depend on what client you are using. I generally only use that when I have a browser as the REST client and want fancy formatting for an xml response.

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
yijuanl
Participant
Posts: 23
Joined: Fri Jun 24, 2005 8:32 am

Post by yijuanl »

I would really appreciate if you could shed some light on how to do this .
Jane
yl
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

? I just outlined the details.....start with database stage to xmlOutput to isdoutput --- and then use the text over http binding .....
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
Post Reply