DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
rrcreddy4
Participant



Joined: 29 Jan 2005
Posts: 21

Points: 186

Post Posted: Sat Jan 29, 2005 10:36 pm Reply with quote    Back to top    

DataStage® Release: 6x
Job Type: Server
OS: Unix
Hi All,

I am new to DS. I have created a new job in DS 6 which selects the data from an Oracle table(The select statement has UNION ALL) and then writes to a sequential file and then goes through mapping process before I insert the same data in to an Oracle table on the different database. The target table has a function based unique constraint. I get an error during the load job which reads from the sequential file and then loads in to an oracle table through a transformer. The error message says, Abnormal End, Job failed and then I also get DSD.SEQClose error. The job has almost a million rows. Also while inserting I am using Insert or Update exisiting rows clause. When I run for lesser rows I do not have an issue.

Please suggest me if anyone has gone through this....

RC
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 51954
Location: Las Vegas
Points: 282080

Post Posted: Sun Jan 30, 2005 3:14 pm Reply with quote    Back to top    

Welcome aboard! Check the job log file for warning or error messages. For example, by default a DataStage job will abort if 50 warnings have been logged (you set the limit when requesting th ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Sydney and Melbourne
Rate this response:  
Not yet rated
rrcreddy4
Participant



Joined: 29 Jan 2005
Posts: 21

Points: 186

Post Posted: Sun Jan 30, 2005 4:12 pm Reply with quote    Back to top    

Thanks. I am using Oracle9i Stage for loading the data from already created sequential file in to an oracle table using the Insert or update clause we have on Oracle9i stage. When I load data for less records everything is fine. The issue is when I load more records.

I don't see any errors in the log file, When I am watching the job using DS Director, I see this abnormal error and then see DSD.SEQclose in BASIC program failure.

Please let me know.

RC
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 39169
Location: Denver, CO
Points: 200003

Post Posted: Sun Jan 30, 2005 5:17 pm Reply with quote    Back to top    

When the job aborts, try Resetting the job from the Director. It's the button with the two left-facing arrows like rewind on a VCR. When it does the reset, it may put new information about the pro ...

_________________
-craig

If you try and take a cat apart to see how it works, the first thing you have on your hands is a non-working cat. -- Douglas Adams
Rate this response:  
Not yet rated
rrcreddy4
Participant



Joined: 29 Jan 2005
Posts: 21

Points: 186

Post Posted: Sun Jan 30, 2005 8:28 pm Reply with quote    Back to top    

Hi, Thanks for the reply. I do get the "Resetting Previous Job ..." message in the log.In the I do see a message saying "BASIC" error, DSD.SEQClose error. I can send you an exact error message tomorrow.

Please let me know.

Thanks again
RC
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 39169
Location: Denver, CO
Points: 200003

Post Posted: Sun Jan 30, 2005 9:58 pm Reply with quote    Back to top    

Make sure to look for the "previous run" message, not anything about the actual resetting. If it's there, double-click on it to read the detail. There will also be a 'Copy' button that will automatica ...

_________________
-craig

If you try and take a cat apart to see how it works, the first thing you have on your hands is a non-working cat. -- Douglas Adams
Rate this response:  
Not yet rated
kcbland

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 15 Jan 2003
Posts: 5209
Location: Lutz, FL
Points: 39192

Post Posted: Sun Jan 30, 2005 10:32 pm Reply with quote    Back to top    

Please provide a text sketch of your job, something like this: Code: OCI--->xfm--->SEQ--->xfm--->OCI. The way you described your job design left me scratching my head. I ...

_________________
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rate this response:  
Not yet rated
rrcreddy4
Participant



Joined: 29 Jan 2005
Posts: 21

Points: 186

Post Posted: Mon Jan 31, 2005 9:46 am Reply with quote    Back to top    

Hi,

You are right, We have standard way of using DS. We split the jobs in to SRC, Look Up, XClean,Xmap,Xkey,Load.

In my case, I skipped Xkey. I extracted data from Oracle using OCI---Transformer---Seq file and then did cleansing,Xmap and then started doing Load, using SEQ------Transformer-----OCI.

I do have a trigger on the table to update update_user_id and update_date.

Initially I was committing at the end. After experiencing the issue, I have started committing after every 10K rows. Still I got the error. To answer afetr how many rows it blows, that is not fixed, when I run for 6 months data(1M rows) it blows after 600K rows. But the same job when I run for less than 200K rows I am fine initially. Again to explain in detail, I am running this job for a span of 12 months, If I run for 2 months period I am fine for 3 to 4 rounds and after that even that starts erroring. Then I get down to 10 days I am fine.

Regarding table space etc. am I supposed to get Oracle error for that rather than abnormal error.

Please suggest me. I will post the exact error message soon.

RC
Rate this response:  
Not yet rated
kcbland

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 15 Jan 2003
Posts: 5209
Location: Lutz, FL
Points: 39192

Post Posted: Mon Jan 31, 2005 9:58 am Reply with quote    Back to top    

rrcreddy4 wrote: I do have a trigger on the table to update update_user_id and update_date. Every row loaded hits this trigger, can it be disabled on this table and you do the work in the ...

_________________
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rate this response:  
Not yet rated
rrcreddy4
Participant



Joined: 29 Jan 2005
Posts: 21

Points: 186

Post Posted: Tue Feb 01, 2005 9:45 am Reply with quote    Back to top    

Hi Here are the error messages I am getting . These are with Previous run title.

Project:smqa (whseqa)
Job name:LdTouchpointFactDICatalogs
Event #:127
Timestamp:2/1/2005 10:41:10 AM
Event type:Info
User:qdssmqa
Message:
From previous run
DataStage Job 237 Phantom 18248
jobnotify: Unknown error
DataStage Phantom Finished.
[18253] DSD.StageRun LdTouchpointFactDICatalogs. LdTouchpointFactDICatalogs.xfm 1 0/100 - core dumped.

Project:smqa (whseqa)
Job name:LdTouchpointFactDICatalogs
Event #:128
Timestamp:2/1/2005 10:41:10 AM
Event type:Info
User:qdssmqa
Message:
From previous run
DataStage Job 237 Phantom 18253
Abnormal termination of DataStage.
Fault type is 10. Layer type is BASIC run machine.
Fault occurred in BASIC program DSD.SEQClose at address 8

I am running on Oracle 9i client. Please let me know. Again I am new to this DS.

RC
Rate this response:  
Not yet rated
kcbland

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 15 Jan 2003
Posts: 5209
Location: Lutz, FL
Points: 39192

Post Posted: Tue Feb 01, 2005 9:59 am Reply with quote    Back to top    

Are you using any row buffering or IPC stages?

_________________
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rate this response:  
Not yet rated
scottr
Participant



Joined: 02 Dec 2004
Posts: 51

Points: 477

Post Posted: Tue Feb 01, 2005 10:01 am Reply with quote    Back to top    

Hi Bland what's this "database rollback segment issues, snapshot too old"

error,one of my jobs are aborting giving this message and if i reset it and run again it just runs fine without failing..

thanks
Rate this response:  
Not yet rated
rrcreddy4
Participant



Joined: 29 Jan 2005
Posts: 21

Points: 186

Post Posted: Tue Feb 01, 2005 10:09 am Reply with quote    Back to top    

I think we are using row buffering.

RC
Rate this response:  
Not yet rated
rrcreddy4
Participant



Joined: 29 Jan 2005
Posts: 21

Points: 186

Post Posted: Tue Feb 01, 2005 10:43 am Reply with quote    Back to top    

Hi

I ran with commit after every 30000 rows and the total rows almost 1M. And i got the below error

From previous run
DataStage Job 237 Phantom 29511
Abnormal termination of DataStage.
Fault type is 10. Layer type is BASIC run machine.
Fault occurred in BASIC program *DataStage*DSR_LOADSTRING at address 6ec.
Rate this response:  
Not yet rated
kcbland

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 15 Jan 2003
Posts: 5209
Location: Lutz, FL
Points: 39192

Post Posted: Tue Feb 01, 2005 11:18 am Reply with quote    Back to top    

rrcreddy4 wrote:
I think we are using row buffering.

RC


See my previous comments about this sometimes being flaky. Turn it OFF and try your job.

_________________
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours