No data from Link Collector to Oracle Stage

Suttond · Post by **Suttond** » Tue Jan 20, 2004 2:24 pm

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.

raju_chvr · Post by **raju_chvr** » Tue Jan 20, 2004 2:47 pm

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.

Suttond · Post by **Suttond** » Tue Jan 20, 2004 2:55 pm

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

raju_chvr · Post by **raju_chvr** » Tue Jan 20, 2004 3:06 pm

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 ?

Suttond · Post by **Suttond** » Tue Jan 20, 2004 3:06 pm

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?

shawn_ramsey · Post by **shawn_ramsey** » Tue Jan 20, 2004 3:26 pm

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?

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.

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.

raju_chvr · Post by **raju_chvr** » Tue Jan 20, 2004 3:29 pm

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 ?

chulett · Post by **chulett** » Tue Jan 20, 2004 3:38 pm

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.

shawn_ramsey · Post by **shawn_ramsey** » Tue Jan 20, 2004 4:56 pm

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 ?

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.

chulett · Post by **chulett** » Tue Jan 20, 2004 5:22 pm

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.

shawn_ramsey · Post by **shawn_ramsey** » Tue Jan 20, 2004 5:44 pm

The odd thing is that it worked perfectly finr is 6. Go figure

kcbland · Post by **kcbland** » Tue Jan 20, 2004 6:14 pm

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.

vmcburney · Post by **vmcburney** » Tue Jan 20, 2004 6:36 pm

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.

Teej · Post by **Teej** » Wed Jan 21, 2004 7:40 am

vmcburney wrote:I am hoping the upcoming 7.0.1 makes it more robust.

Oh, if it is anything compared with 6.0.2, you will experience a new set of problems while your old problems are solved.

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.

Suttond · Post by **Suttond** » Wed Jan 21, 2004 2:27 pm

Thanks everyone. Putting the transform stage in works fine and I see the reason to have it - for capturing rejects.

DSXchange

No data from Link Collector to Oracle Stage

No data from Link Collector to Oracle Stage

Re: No data from Link Collector to Oracle Stage