DSXchange

Hello all. I just wanted to see if anyone else has had any experience and pointers when working with DataStage and SalesForce.com.

Here is the scenario:
We have an instance of SalesForce.com that we're wanting to get data from and push data to on a nightly basis. Our current job design consists of the following: An Oracle stage that reads in the columns requested from SFDC, which is passed to an Java Transform stage. In that stage, the jar files are invoked, in which they extract the column names and create a "select" statement to extract the data from SFDC. The java Transform stage then links to another Oracle Stage or a sequential file where the selected data is output.

We have been successful in extracting data from SFDC, but when it comes to larger volumns, we have issues, like the job just hanging. It seems to be related to the number of columns times the number of records returned.

We've tried numerous different scenarios with different number of columns and more rows. We think it may be memory related, but not sure.

First of all.....yes, I know there is a purchasable SFDC adapter for DataStage, but we have written one in Java for the client.

Has anyone else experienced this sort of pain?

Thanks.

When ODBC protocol is used there are default maxima for the number of columns and size of rows that can be transferred.

These can be overridden either globally or for a particular data source name by editing the uvodbc.config file and setting MAXFETCHCOLS and MAXFETCHBUFF parameters. The MAXFETCHBUFF parameter is given in bytes.

There are so many things that this could be.....from a problem in your java class to some memory issue somewhere....or maybe another Stage and it just "looks" like it is an issue in your class.....hard to tell. A very interesting test would be to see if it hangs consistently also in a Server Job. Your java class should work just as well in Server as in EE....if nothing else, it might offer different errors or symptoms for debugging, as often error messages and behavior for the same problem are surfaced slightly different between the two.

Ernie

Thanks for your input guys. I'll try the suggestions.

Thanks,
Jason

jatayl wrote:Hello all. I just wanted to see if anyone else has had any experience and pointers when working with DataStage and SalesForce.com.

Here is the scenario:
We have an instance of SalesForce.com that we're wanting to get data from and push data to on a nightly basis. Our current job design consists of the following: An Oracle stage that reads in the columns requested from SFDC, which is passed to an Java Transform stage. In that stage, the jar files are invoked, in which they extract the column names and create a "select" statement to extract the data from SFDC. The java Transform stage then links to another Oracle Stage or a sequential file where the selected data is output.

We have been successful in extracting data from SFDC, but when it comes to larger volumns, we have issues, like the job just hanging. It seems to be related to the number of columns times the number of records returned.

We've tried numerous different scenarios with different number of columns and more rows. We think it may be memory related, but not sure.

First of all.....yes, I know there is a purchasable SFDC adapter for DataStage, but we have written one in Java for the client.

Has anyone else experienced this sort of pain?

Thanks.

I am currently working on integrating SFDC with a regular DB but we are have Salesforce PACK for daily/weekly batches. Of course we have so many issues with the PACK.

Well, we finally found something that worked. I changed all the stages after the java adapter to run sequentially, instead of the default(parallel). I'm not sure why, but I'm thinking it has to do with memory usage.

Regardless, it's working now.

Thanks to all.

Jason