No data from Link Collector to Oracle Stage
Moderators: chulett, rschirm, roy
No data from Link Collector to Oracle Stage
Output to Link Collector is two links from the same Transformer stage. Link Collector has one link to Oracle8i stage.
From transformer, first link passed 700 rows and second link passed two rows.
Received many warning messages from Oracle due to a date field being too small. In fact, all fields contained zero (0). See below:
PlanSignoffMerge..Link_Collector_14: The value of the row is: PART_NUMBER = 0 PLAN_ALT = 0 REVISION_NUMBER = 0 SIGNOFF_SEQUENCE_NUMBER = 0 GROUP_NAME = 0 IN_QUEUE_DATE = 0 ACTUAL_START_DATE = 0 ACTUAL_FINISH_DATE = 0 ACCEPT_REJECT_CODE = 0 COMMENTS = 0 REJECT_REASON_CODE = 0 REVIEW_COUNT = 0 REJECT_COUNT = 0
Oracle table is empty. No data was processed.
Q1: If Link_Collector stage is writing the warning message, is this really an Oracle warning message? Wouldn't the Oracle warning message come from the Oracle8i stage?
Q2: What is happening to the data as it passes thru the Collector.
Note: When a transformation stage is put between Collector and Oracle, all goes well.
From transformer, first link passed 700 rows and second link passed two rows.
Received many warning messages from Oracle due to a date field being too small. In fact, all fields contained zero (0). See below:
PlanSignoffMerge..Link_Collector_14: The value of the row is: PART_NUMBER = 0 PLAN_ALT = 0 REVISION_NUMBER = 0 SIGNOFF_SEQUENCE_NUMBER = 0 GROUP_NAME = 0 IN_QUEUE_DATE = 0 ACTUAL_START_DATE = 0 ACTUAL_FINISH_DATE = 0 ACCEPT_REJECT_CODE = 0 COMMENTS = 0 REJECT_REASON_CODE = 0 REVIEW_COUNT = 0 REJECT_COUNT = 0
Oracle table is empty. No data was processed.
Q1: If Link_Collector stage is writing the warning message, is this really an Oracle warning message? Wouldn't the Oracle warning message come from the Oracle8i stage?
Q2: What is happening to the data as it passes thru the Collector.
Note: When a transformation stage is put between Collector and Oracle, all goes well.
Re: No data from Link Collector to Oracle Stage
First of all Suttond I am confused with your question.
Q1)If I am not wrong you are trying to write into ORACLE and you are getting warnings. To decide which stage is throwing the error you can get either from logs or I would suggest write the output to Sequential file to zero on error. Then if this executes well then ur issue is writing into ORACLE.
Q2: What is happening to the data as it passes thru the Collector.
A) It collects the data from 2 of your sources(again If am not wrong!) using the Collection algorithm and writes it to the Output.
May be you can give us the pictorial representation of your job.
Q1)If I am not wrong you are trying to write into ORACLE and you are getting warnings. To decide which stage is throwing the error you can get either from logs or I would suggest write the output to Sequential file to zero on error. Then if this executes well then ur issue is writing into ORACLE.
Q2: What is happening to the data as it passes thru the Collector.
A) It collects the data from 2 of your sources(again If am not wrong!) using the Collection algorithm and writes it to the Output.
May be you can give us the pictorial representation of your job.
Sorry, I was in the log, but I only provided you with one warning message from the log. Here are two warning messages, that will more completely show where my confusion comes from. How does the Link_Collector stage know about an Oracle error message?
PlanSignoffMerge..Link_Collector_14: ORA-01840: input value not long enough for date format
PlanSignoffMerge..Link_Collector_14: The value of the row is: PART_NUMBER = 0 PLAN_ALT = 0 REVISION_NUMBER = 0 SIGNOFF_SEQUENCE_NUMBER = 0 GROUP_NAME = 0 IN_QUEUE_DATE = 0 ACTUAL_START_DATE = 0 ACTUAL_FINISH_DATE = 0 ACCEPT_REJECT_CODE = 0 COMMENTS = 0 REJECT_REASON_CODE = 0 REVIEW_COUNT = 0 REJECT_COUNT = 0
PlanSignoffMerge..Link_Collector_14: ORA-01840: input value not long enough for date format
PlanSignoffMerge..Link_Collector_14: The value of the row is: PART_NUMBER = 0 PLAN_ALT = 0 REVISION_NUMBER = 0 SIGNOFF_SEQUENCE_NUMBER = 0 GROUP_NAME = 0 IN_QUEUE_DATE = 0 ACTUAL_START_DATE = 0 ACTUAL_FINISH_DATE = 0 ACCEPT_REJECT_CODE = 0 COMMENTS = 0 REJECT_REASON_CODE = 0 REVIEW_COUNT = 0 REJECT_COUNT = 0
DataStage is showing you the information from LinkCollector which you are trying to insert the stage followed by this one. Actually the second message is showing the data which is not accepted by ORACLE.
Are you trying to change any in Transformation stage or is it direct mappings for every column ?
Are you trying to change any in Transformation stage or is it direct mappings for every column ?
-
- Participant
- Posts: 145
- Joined: Fri May 02, 2003 9:59 am
- Location: Seattle, Washington. USA
So this happens in Oracle also? We have seen the same issue in SQL Server and have a trouble ticket on it even though I am not quite sure where it is in the resolution process. I spent several days chasing this down.Suttond wrote:Here is a gross pictorial view.
TransformLink1-700rows->Link Collector-->Oracle Stage
TransformLink2- 2rows-->
To be more specific, why does every field in every row contain zeros after passing through the collector?
The work around that we came up with that seemed to fix the issue is to add a transformer stage after the collector that just replicates the columns.
Shawn Ramsey
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
Sure - Ascential's eService website here, provided you have an active support contract. Once you log in, you can do a Knowledge Search to look for any existing ECASEs that match your criteria.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 145
- Joined: Fri May 02, 2003 9:59 am
- Location: Seattle, Washington. USA
I spent several days chasing this one down. I thought is was just an issue with the SQL Server bulk loader since the only other thing that I tried it with was the Flat File stage and it did not have an issue. The funny thing is that if you run it in trace mode and look at the output link from the collector the data looks OK.raju_chvr wrote:Oh !!. I didn't know about that.
I wish there is a place where we can go the submitted Trouble tickets for every version so that we will not spend too much time on them before we realise that it is bug in the system, or is does on already exist ?
Shawn Ramsey
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
There seem to be several issues related to linking two passive stages directly together, which is why I tend to avoid it. I've gotten into the habit of putting an active stage (typically a transform) between them, even if 'all it does' is pass the columns thru unchanged.
Overkill, perhaps. I'm not aware of any issues with doing things this way, whereas you've seen one of the odd things that can happen when you don't.![Sad :(](./images/smilies/icon_sad.gif)
Overkill, perhaps. I'm not aware of any issues with doing things this way, whereas you've seen one of the odd things that can happen when you don't.
![Sad :(](./images/smilies/icon_sad.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 145
- Joined: Fri May 02, 2003 9:59 am
- Location: Seattle, Washington. USA
Well, the lesson is to always have a transformer prior to a passive stage. Anytime you have a job like seq --> oci you miss the opportunity to have a reject link to capture rejecting rows. In addition, a reject link allows you to set a threshold number of rejected rows before the job aborts (I like 1). Since you don't have this, then the rejecting rows from the Oracle stage have no where to go. As for the log, never trust that junk, because it's usually a bad message, wrong data (as seen), or partial data (some character limit on the length of the message).
So, rule to follow, always have a transformer between two passive stages. You gain ability for derivation, column re-ordering, column renaming, reject link output, and column reduction.
So, rule to follow, always have a transformer between two passive stages. You gain ability for derivation, column re-ordering, column renaming, reject link output, and column reduction.
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
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
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
I'm not surprised that it worked in version 6 and stopped working in version 7! There have been an increasing number of posts about problems in 7 with any jobs with link collectors, whether it's hanging jobs, mutex timeouts or abnormal terminations. I am hoping the upcoming 7.0.1 makes it more robust.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Oh, if it is anything compared with 6.0.2, you will experience a new set of problems while your old problems are solved.vmcburney wrote:I am hoping the upcoming 7.0.1 makes it more robust.
For example, we found out this week that a simple change of a column as Non-Nullable to Nullable within a Transform stage would cause the OSH code to be missing a line of code copying from input to output. Optimization, maybe, but the very next stage have a Tsort set up. Non-Nullable field passed through being requested to sort -null first equals to a set of fatal errors.
Fun stuff. Gives your management heart attacks.
-T.J.
Developer of DataStage Parallel Engine (Orchestrate).