Table Lookup Problem in Server Job

[Seyed]

Hi all,
I have a problem with a Server job. My source table, target table and lookup tables are in Oracle. I wanted to read the data in the source table and then find a matching data in the lookup table. If the lookup was successful, I would insert data in the target table. If lookup failed, I would write the content of source row to a rejected file on the Windows 2003 Server. It looks like when I run this Server job, the data in the source table that finds a match in the lookup table is successfully written to the target table. However, the generated log consists of warnings for attempting to insert null value in a not null key column. I have verified that the warnings are from rows that were written to the rejected file on the Windows Server.

Any ideas why the Server job writes the rejected data to the rejected file but also tries to insert same rows to the target table? I have an open PMR with IBM Support but a resolution has not been recommended yet.

Thank you for your input,


Seyed

[chulett]

Without posting your actual full error messages, I'm assuming what you are seeing are the records first being written to the database where they are rejected for the reason you noted and which are then written to the reject link.

[Seyed]

Hi Craig,
Thank you for your input. Assuming that attempts are made to first insert the rejected data into the Oracle table and then to the rejected file, what would be the solution? Below is an example of the error messages being generated:

Code: Select all

Project:AgencyWideReports (sabcdef)
Job name:Load_TWRKR_DIV_FCT2
Event #:928
Timestamp:6/7/2011 12:20:20 PM
Event type:Warning
User:U999888
Message:
Load_TWRKR_DIV_FCT2..Transformer_2.DSLink7: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO U999888.TWRKR_DIV_FCT(TWRKR_DIV_FCT_ID, WRKR_DMSN_ID, DIV_DMSN_ID, RPT_PER_END_DT, WRKR_USER_ID, CREATE_DT, CREATE_USER, CREATE_PGM, UPDATE_DT, UPDATE_USER, UPDATE_PGM, END_DT_PART1, END_DT_PART2, END_DT_PART3) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?) 
SQLSTATE=23000, DBMS.CODE=1400
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-01400: cannot insert NULL into ("U999888"."TWRKR_DIV_FCT"."WRKR_DMSN_ID")

TWRKR_DIV_FCT_ID = 49
WRKR_DMSN_ID = NULL
DIV_DMSN_ID = NULL
RPT_PER_END_DT = "2011-06-07 12:20:16"
WRKR_USER_ID = NULL
CREATE_DT = "2011-06-07 12:20:16"
CREATE_USER = "DATASTGE"
CREATE_PGM = "Load_TWRKR_DIV_FCT"
UPDATE_DT = "2011-06-07 12:20:16"
UPDATE_USER = "DATASTGE"
UPDATE_PGM = "Load_TWRKR_DIV_FCT"

Thank you,


Seyed

[chulett]

Be proactive rather than reactive - make sure your required fields are non-null before sending the row off to the database.

[chulett]

Assuming that attempts are made to first insert the rejected data into the Oracle table and then to the rejected file

ps. Not an assumption... How It Works.

[Seyed]

Assuming that attempts are made to first insert the rejected data into the Oracle table and then to the rejected file

ps. Not an assumption... How It Works.

That is good to know. Thanks, by the way, I checked and verified that neither the WRKR_DMSN_ID nor DIV_DMSN_ID columns in the lookup table contain null values.

Thanks again,


Seyed

[Seyed]

Assuming that attempts are made to first insert the rejected data into the Oracle table and then to the rejected file

ps. Not an assumption... How It Works.

Hi Craig,
I resolved this problem by adding the following constraints in the Transform Stage:

Code: Select all

DSLink1.CL_USR_NM = DSLink5.WRKR_USER_ID

Now, when I run this job, it runs fine and all my links turn green and only one entry in the log is yellow with type=Reject containing reject count. What gets me is that in my other DS job that contained a lookup table, I didn't have to add any constraints in the Transform Stage and it worked fine.

Thank you as always,

Seyed

[chulett]

To me, that would imply that you've never had a lookup fail before, something hard to believe. That or your looked up fields weren't targeting required fields in a database.