Wrong value used when doing an insert

[Tobbi]

Hi!

I'm getting strange results in one of my jobs. It feels like sometimes (not always) when I do an insert, the value of ONE column is cached or something similar so when I do the next insert to row the value from my previous insert is used for this particular column.

The problem is that there doesn't seem to be any logic to this. If I reset the data and re-run the job, different rows are affected and sometimes no rows are affected thus I cannot recreate the exact same scenario even though I'm using the same data.

Ideas?


As this is a bit thin I'll try to explain in more detail what I'm doing.

I'm populating a User Life Cycle table and for each new, incoming user I do a lookup in my target table if I've already added this user. I have two tables, STG_ULC and ULC (short names). Before my problems start I have all the necessary data to allocate dimension keys etc. for all the incoming users in the STG_ULC table.

The job is Called: LoadULCDimensions

And it basically does three things:
I) Allocates each users dimension keys
II) Extract locally genereated USER_ID for this user (if encountered before)
II) Extracts the CURRENT_ROW for each user from the ULC table (if it exists) and inserts into the ULC

NB: I'm only using one STG table for this, not moving between STG tables.

Allocating the dimensions works fine
Allocating the USER_ID works fine
** Inserting the CURRENT_ROW doesn't **

I find it difficult to explain, but I'll try to illustrate the problem, assume we start with the two following row:

ROWTYPE: INCOMING - SRC_USER_ID: 12345 - ULC_USER_ID: NULL
ROWTYPE: INCOMING - SRC_USER_ID: 22222 - ULC_USER_ID: NULL

I run my job and the results should look like this:

ROWTYPE: INCOMING - SRC_USER_ID: 12345 - ULC_USER_ID: 101
ROWTYPE: EXISTING - SRC_USER_ID: 12345 - ULC_USER_ID: 101
ROWTYPE: INCOMING - SRC_USER_ID: 22222 - ULC_USER_ID: 102
ROWTYPE: EXISTING - SRC_USER_ID: 22222 - ULC_USER_ID: 102

But what I do get is:

ROWTYPE: INCOMING - SRC_USER_ID: 12345 - ULC_USER_ID: 101
ROWTYPE: EXISTING - SRC_USER_ID: 12345 - ULC_USER_ID: 101
ROWTYPE: EXISTING - SRC_USER_ID: 22222 - ULC_USER_ID: 101
ROWTYPE: INCOMING - SRC_USER_ID: 22222 - ULC_USER_ID: 102

Now, for each incoming row I only do ONE insert and I do a match on the SRC_USER_ID (and no other column). Further, for all INSERTS I do, I also do a write to a sequencial file (for debugging). In the sequencial file the ULC_USER_ID's are always correct, for each SRC_USER_ID.

Now, if a clear up everything I get one of two resuts:
a) different SRC_USER_ID and ULC_USER_ID are messed up
b) everything is fine

Further more, directly after I allocate the ULC_USER_ID I do a write (both to the DB and seq.file) so there is no more logic.

Anyone encountered something similiar?
There must be something I'm doing wrong but I've been staring blindly on this for almost 2 days straight...

Any comments appreciated.
Thanks
Tobbi

[MukundShastri]

Are you using any stage variables in the transformer.
It would be easier to debug if you can send the job export to my email shastrimukund@rediffmail.com.

Mukund

[Tobbi]

Yes, I'm using staging variables, partly to decide if a row should be inserted and partly to decide if I can use an existing USER_ID or if I need to create a new one.

/Tobbi

[chulett]

Or simply post the Stage variable derivations here... that would help.

[Tobbi]

Alraight, don't think it will help but here you are:

USEREXISTS
IF IsNull(hLoadUserLifeCycle.ID) THEN 0 ELSE 1

Desc: used in the CONSTRAINT only - if I find a user, I insert that users current row into my staging table.

ENTRYOK
IF IsNull(IDimensionsAllocated.CUSTOMER_TYPE_ID) OR (IsNull(IDimensionsAllocated.SALES_ORDER_DATE_ID)) OR (IsNull(IDimensionsAllocated.ACTIVATION_DATE_ID)) THEN 0 ELSE 1

Desc: the criteria if a users should be inserted into the user lifecycle.

The staging variables aren't used as "output" to either the Database nor the Sequencial file.

/Tobbi