Error retrieving results from server

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
bhaskarjha
Participant
Posts: 67
Joined: Thu Apr 06, 2006 7:13 am

Error retrieving results from server

Post by bhaskarjha »

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
Bhaskar Jha
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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...
-craig

"You can never have too many knives" -- Logan Nine Fingers
bhaskarjha
Participant
Posts: 67
Joined: Thu Apr 06, 2006 7:13 am

Post by bhaskarjha »

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.
Bhaskar Jha
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

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?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
bhaskarjha
Participant
Posts: 67
Joined: Thu Apr 06, 2006 7:13 am

Post by bhaskarjha »

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.
Bhaskar Jha
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
splayer
Charter Member
Charter Member
Posts: 502
Joined: Mon Apr 12, 2004 5:01 pm

Post by splayer »

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?
tagnihotri
Participant
Posts: 83
Joined: Sat Oct 28, 2006 6:25 am

Post by tagnihotri »

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!
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply