Stage executing even when constraint is false

[sbass1]

Hi,

My job design looks like:

Code: Select all

DRS Stage --> xfm -- <Insert> --> DRS1
               |
               |
          <Initialize>
               |
               V
              DRS2

DRS1 and DRS2 are the same table.

DRS1's constraint is: JobParm = 0

DRS2's constraint is: JobParm = 2

i.e. they are mutually exclusive.

The DRS2 stage does a "truncate then insert rows". There are no input rows linked to this stage, all values are hard coded.

However, this table truncation is occuring regardless of the constraint. Even when JobParm = 0 and link stats show 1 row added (and no rows on the Initialize link), "Show Data" returns an empty table.

1. Is this just "how it works", i.e. the truncation occurs regardless of the constraint on the data that will then be inserted?

2. Is there a way I can conditionally truncate then insert rows (i.e. initialize my table with one row) based on a job parm value?

Thanks,
Scott

[chulett]

Yes, that's how it works - the truncate is issued when the stage initializes just after connecting but before any rows flow, much like anything in the "before sql" tab. It doesn't wait for the first row, so if you "truncate and insert" and send it no rows to insert, you end up with an empty table. As you've seen.

So... 'conditionally' truncate? Meaning your design was meant to only truncate the table if there are actually rows to insert as well? Leave existing records alone in the case when the job processed zero records. I assume so or you'd just stick with a single link set to 'truncate then insert'.

You could use two jobs for this, basically what you've got there split up and without the (then unnecessary) constraints. A conditional check, say in a Sequence job, to verify you have records to load and then trigger the truncate job (or even routine) before an unconditional run of the load. However, it seems to me if you're going to do that conditional check then you might as well just use a single job with an 'truncate then insert' link and only run the job if there are actually records to be loaded.

[ray.wurlod]

Every active stage has three phases of execution: "startup", "per row" and "shutdown".

Operations like "truncate" or "clear" occur during the startup phase - you wouldn't really want them done per row, would you?!!

The usual solution is two jobs, as Craig noted.

[sbass1]

I'm looking for conditional code / stage execution based on the value of a parameter.

Every active stage has three phases of execution: "startup", "per row" and "shutdown".

So what I want is no "startup", "per row" and "shutdown" at all if job parm is false.

I guess this can't be done in DS, and I need two jobs???

[chulett]

As noted, you are always going to get the "startup" and "shutdown" phases executed, regardless of constraints on the link or your desire to control everything with a parameter. So while not necessarily meaning you "need" two jobs, you could use two for this or simply build your conditional logic (to run the load job or not) into the controlling sequence or whatever you are using for job control.