Need to Publish 20,000 rows through ISD output stage SOAP
-
- Participant
- Posts: 19
- Joined: Thu Mar 06, 2014 12:41 pm
Need to Publish 20,000 rows through ISD output stage SOAP
Hi,
I have a DataStage job which reads data from DB2 connector stage and outputs through ISD output stage. I have configured this job through ISD on the information server to publish the result through SOAP over HTTP protocol.I am able test this web service when the output from the DataStage job is 20 or 25 rows but when i increase to fetch all rows then the job aborts saying time out etc. Please help me how can i publish all 20,000 records?
All your help is highly appreciated.
I have a DataStage job which reads data from DB2 connector stage and outputs through ISD output stage. I have configured this job through ISD on the information server to publish the result through SOAP over HTTP protocol.I am able test this web service when the output from the DataStage job is 20 or 25 rows but when i increase to fetch all rows then the job aborts saying time out etc. Please help me how can i publish all 20,000 records?
All your help is highly appreciated.
chandrakothapalli
-
- Participant
- Posts: 19
- Joined: Thu Mar 06, 2014 12:41 pm
Just because you want to send all 20,000 records at once to the service doesn't mean it can handle that many / an XML that large. Check with the service owner / author first to see if that is even possible.
Otherwise you may need to loop through the rows and send them individually or in smaller (like your 20 to 25 record) groups.
Otherwise you may need to loop through the rows and send them individually or in smaller (like your 20 to 25 record) groups.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 19
- Joined: Thu Mar 06, 2014 12:41 pm
Hi Chulett,
this what I am doing. I have designed the datastage job as defined in my earlier post. Then I have deployed the service through Information services director. I am testing that service through SOAP UI. So, its me who is the author rite and I dont know what is the limitation here. Is there a limitation on using this service on the DS, I mean In general ppl use these kind of jobs just for very small amount of data. In case if I have to publish all 20000 rows what is work around design please.
this what I am doing. I have designed the datastage job as defined in my earlier post. Then I have deployed the service through Information services director. I am testing that service through SOAP UI. So, its me who is the author rite and I dont know what is the limitation here. Is there a limitation on using this service on the DS, I mean In general ppl use these kind of jobs just for very small amount of data. In case if I have to publish all 20000 rows what is work around design please.
chandrakothapalli
Sorry, for some reason I thought you had a job that pulled rows from DB2 and sent the XML to another service. Going far too fast today.
Show us the abort / timeout error messages. You need to determine what part of your design is the culprit. It's been far too long but I believe as a test you can remove the ISD output stage so that it simply writes to disk instead and see if you still have the issue.
Show us the abort / timeout error messages. You need to determine what part of your design is the culprit. It's been far too long but I believe as a test you can remove the ISD output stage so that it simply writes to disk instead and see if you still have the issue.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 19
- Joined: Thu Mar 06, 2014 12:41 pm
Please find the error messages below:
ISD_Output_2,0: PutRow Failed: dspipe_wait(19524): Writer timed out waiting for Reader to connect.
ISD_Output_2,0: The runLocally() of the operator failed.
ISD_Output_2,0: Operator terminated abnormally: runLocally() did not return APT_StatusOk.
so basically I understand it has a time out error and on the ISD output I have increased the time out to 2000 seconds, biut that didn't helped.
my job design is :
db2 stage(1000 rows,348 columns) ---> Transformer ---> ISD output.
ISD_Output_2,0: PutRow Failed: dspipe_wait(19524): Writer timed out waiting for Reader to connect.
ISD_Output_2,0: The runLocally() of the operator failed.
ISD_Output_2,0: Operator terminated abnormally: runLocally() did not return APT_StatusOk.
so basically I understand it has a time out error and on the ISD output I have increased the time out to 2000 seconds, biut that didn't helped.
my job design is :
db2 stage(1000 rows,348 columns) ---> Transformer ---> ISD output.
chandrakothapalli
First try Craig's suggestion and prove you get complete, correct output to a Sequential File stage instead of the ISD Output stage.
You may be running into the 8.x bug where the ISD stage's buffer size setting is actually a hard limit (not the expected behavior). The default is 512 KB. Try doubling the buffer size, recompiling, and redeploying, then testing it again. Lather. Rinse. Repeat.
I returned 50 thousand rows as a test last year on version 8.7 (no fix pack) by increasing the buffer size to get past the bug. I'm sure the upper limit is much higher.
One bad thing about XML is how incredibly inefficient it is. If you look at a typical SOAP payload, you may find that 90% to 95% of the payload size is consumed by the XML opening and closing tags. That's a lot of overhead. Take that times 345 columns 20 thousand records and your web service consumer's round trip is likely to be measured minutes instead of milliseconds.
You may be running into the 8.x bug where the ISD stage's buffer size setting is actually a hard limit (not the expected behavior). The default is 512 KB. Try doubling the buffer size, recompiling, and redeploying, then testing it again. Lather. Rinse. Repeat.
I returned 50 thousand rows as a test last year on version 8.7 (no fix pack) by increasing the buffer size to get past the bug. I'm sure the upper limit is much higher.
One bad thing about XML is how incredibly inefficient it is. If you look at a typical SOAP payload, you may find that 90% to 95% of the payload size is consumed by the XML opening and closing tags. That's a lot of overhead. Take that times 345 columns 20 thousand records and your web service consumer's round trip is likely to be measured minutes instead of milliseconds.
Choose a job you love, and you will never have to work a day in your life. - Confucius
-
- Participant
- Posts: 19
- Joined: Thu Mar 06, 2014 12:41 pm
Hi,
I tried the Sequential file as output and i Can get all the 345 columns and 20000 rows. Its only when the ISD stage is added i get the above mentioned error.
Actually I am running on v9.1.2 and on windows platform. I can try increasing the buffer size and will post the result. Thank you.
I tried the Sequential file as output and i Can get all the 345 columns and 20000 rows. Its only when the ISD stage is added i get the above mentioned error.
Actually I am running on v9.1.2 and on windows platform. I can try increasing the buffer size and will post the result. Thank you.
chandrakothapalli
-
- Participant
- Posts: 19
- Joined: Thu Mar 06, 2014 12:41 pm
I haven't reported to the IBM tech support but I have tested this job both on server and parallel and on both the jobs I get the same error:
IIS-DSEE-RTIO-00005 ISD_Output_2,0: PutRow Failed: dspipe_wait(22176): Writer timed out waiting for Reader to connect.
I have the buffer size to be 1024 and the time out to be 300.
This time i have limited to 1000 rows and 348 columns.
IIS-DSEE-RTIO-00005 ISD_Output_2,0: PutRow Failed: dspipe_wait(22176): Writer timed out waiting for Reader to connect.
I have the buffer size to be 1024 and the time out to be 300.
This time i have limited to 1000 rows and 348 columns.
chandrakothapalli
Yuk. 20k rows in a SOAP based service? It can be done, but its not a best practice for any SOAP based scenario. Regular SOAP has never been a reliable transport mechanism for a "lot" of data. The major vendors (for example, review the calls available for SF.com) put a "batch" parameter inside of most of their calls for exactly this reason...you decide the size of your batch and the host sends "that" many rows. It is then predictable and manageable across any environment or scenario.
I'm not surprised it hangs. It might work in some configurations and not others.
Only other possibility is if, by chance, there is a particular row or record containing some bad data that is causing your symptoms and making it "look like" a volume issue.
Ernie
I'm not surprised it hangs. It might work in some configurations and not others.
Only other possibility is if, by chance, there is a particular row or record containing some bad data that is causing your symptoms and making it "look like" a volume issue.
Ernie
Ernie Ostic
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>