DSXchange

Hi,

One of my jobs updates the oracle table with input data. Job is running successfully but whenever it is not finding a match it is giving warning also. Can we not avoid these kind of warnings?

Regards,
Nripendra

Nripendra,

as you rightly wish to do, the warnings you are getting should be removed. In order to do so you will have to tell us what the warnings are and in which stage they are occurring.

If it is not able to locate an update, use a reference lookup and reject the rows. It is better to identify and remove from the stream than to let them returned as error.

The people before me were very kind to you.

Geting a warning is (beside some negligable bugs in DS) a sign for an suboptimal coding. With a concrete warning you are then able to optimize your coding.

Wolfgang

i'm posting the sample warning message:

jbGenerateKeyForAccountCopy.HR.CustNameMapping.LnkAddress: DSD.BCIPut call to SQLExecute failed.
SQL statement:UPDATE COMET_TEST.ADDRESS SET NAME = ? WHERE (GSFA_ID = ?)
0 Rows affected.
GSFA_ID = "HRC3140642200"
NAME = "ZABA TURIZAM DOO"

Regards,
Nripendra

As mentioned before, all it ways is that it was not able to locate the row with the key in the table.

Use reference lookup to reject them.

Alternatively, if your design is to be altered to 'insert or update rows', then look into it.

but in other job operation is "insert new or update existing one". This job is also giving the same type of warnings.
Primary keys in the tables are different from the keys which i'm using to update the tables. can this be a reason for warnings?

-Nripendra Chand

If you attempt to insert, you need to define ALL the primary keys as you cannot have any duplicates of them.

Similarly, an update without all primary keys may result in updating multiple rows.

so is there no foolproof way to avaoid the warnings if the operation is "insert new or update existing". I'm using the multiple keys for this operation which have uniqueness property.

-Nripendra Chand

If you rejects those records in transformer then you can avoid these warnings. Stop them from going to the DB stage.

Hi,
you managed to confuse me wih your last post Nripendra Chand,
If you use insert or update then there is no way you'll get this message (if it isyourload method)

in case you use only update you can, as mentioned, prepare or perform a lookup verifying the record exists and only then update it (use constraint to filter rows for update not in the table you try to update).
this way no warning will pop up.

IHTH,

One simple way to make this error go away is to switch from ODBC to an OCI stage.

Oracle doesn't generate a warning if an update 'affects' zero rows and the OCI stage doesn't report it - it's an artifact of using ODBC.

But then, these error messages are there for a purpose. The message clearly says that you are attempting to alter a record value and you are not successful in performing that. So you need to have a design that caters for this failure to be traced and recorded....not hidden and forgotten.

Obviously, hence the little winky guy.

However, I have encountered people that don't care if an update doesn't actually update anything. If you consider it a failure, as previously noted several times, you need to create a reference hash and do a lookup check ahead of time, validating your inserts and your updates before actually shipping them off to the database.

Ziggacclly.