DSXchange

Hi,
I am trying to load a table from a view, the view contains 16 lacs record. It runs fine till 13 lacs record but after that I am getting error "Error retrieving results from server." Is this because of number of records? Please help me in resolving this problem.
Thanks,
Bhaskar

I doubt the problem is from the number of records, but you haven't given us much to go on. How about more details about your job? What database are you accessing via what stage? Is there any error number associated with this message?

And can you - from the DataStage server, not your client - run the same sql outside of DataStage and retrieve all records? And I don't mean just get the first set of records back but retrieve all 16 lacs of data...

Hello Chulett,
Design of job is

Source View---> Transformer ---> Target table

I am using DB2 as target database. I am getting one more warning "The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007" but I am able to view all the data from view.

Are you getting this warning for all the records? I mean is your director's log filled with warnings? You need to get the date or timestamp in the correct format. What is your source? Is your source DB2 as well?

This warning ( The syntax of the string representation of a datetime value is incorrect. ) is coming for all the records, though I have checked the data which got inserted in the target table & they are ok. But I think some tuning should be done for view as it tooks very much time to run the query. The view is also being created in DB2 & target table is also in DB2.

Try to run the view at the command line and collect its contents in a file. Then do a wc -l on the file to check the number of records. If it is what you are getting via DataStage then the sql for creating the view is too huge to accomodate that amount of records in the database memory. For that you need to contact the dba. If not, then i would suggest you try to tackle the warning message first. Maybe that has something to do with it.

chulett, how would you go about doing that? That is, how would you go about executing the sql from the Datastage server? Let's say it is a UNIX box. You go to the DSEngine folder and then execute the script? Through something like a shell script?

The syntax of the string representation of a datetime value is incorrect. Quick solution may not be the best one is to read and write your date collumn changing their data type to char. This should supress the warning.

Secondly as already pointed I belive you should extract the records in a seq file first (do a count to ensure all data has landed well, if not shout for dba's). Then in another job load it to the db2 source (you can use udb load utilities for that), this should exell your performance too!

splayer wrote:chulett, how would you go about doing that? That is, how would you go about executing the sql from the Datastage server? Let's say it is a UNIX box. You go to the DSEngine folder and then execute the script? Through something like a shell script?

Took me a minute to realize what the 'that' was we were talking about as this topic isn't exactly fresh in my mind.

I meant via a utility like sqlplus for Oracle. However, since the OP didn't mention what database they were working with until later, couldn't offer any specific advice.