Oracle update SQL problem

[Neil C]

I am trying to execute an update statement in an ODBC stage, and get a message 'SQLExecute failed...'.

The update statement is:

UPDATE P.PF003T00 SET SOURCE_SYSTEM = ?, CUST_NBR = ? WHERE ( RTRIM(NVL(FIELD1,'~~~~')) = ? AND RTRIM(NVL(FIELD2,'~~~~')) = ? AND RTRIM(NVL(FIELD3,'~~~~')) = ?)

The full message returned by DataStage is:

PaymentFilterDataDictAutoCSIMigrateTest.xDataDictUpdate1.updPF003T00: DSD.BCIPut call to SQLExecute failed.
SQL statement:UPDATE P.PF003T00 SET SOURCE_SYSTEM = ?, CUST_NBR = ? WHERE ( RTRIM(NVL(FIELD1,'~~~~')) = ? AND RTRIM(NVL(FIELD2,'~~~~')) = ? AND RTRIM(NVL(FIELD3,'~~~~')) = ?)
0 Rows affected.

FIELD1 = "5397815 "
FIELD2 = "DIRECT CREDI"
FIELD3 = "20030116 "
SOURCE_SYSTEM = "Arbor"
CUST_NBR = 5397816

The record as specified by FIELD1, FIELD2, FIELD3 does exist (I could handle a no record found condition...). The record is read be the previous stage. I had also tried the lookup read and the update statement in the same stage with the same result.

There is an index defined on these three fields, as well as one on SOURCE_SYSTEM, CUST_NBR.

I have tried a number of combinations of commits and quote options (" and ') to no avail. A simmilar update to this is used successfully in another job.

Any ideas why I cannot do this?

[ray.wurlod]

Try SET SOURCE_SYSTEM = '?' and so on.

This column (based on the data at the bottom is a character string, and so should be quoted. Similarly, the result of RTRIM() is a character string, so parameter markers for these must be quoted. However, CUST_NBR is not a character string so its parameter marker is not quoted.

Code: Select all

UPDATE P.PF003T00 SET SOURCE_SYSTEM = '?', CUST_NBR = ? WHERE ( RTRIM(NVL(FIELD1,'~~~~')) = '?' AND RTRIM(NVL(FIELD2,'~~~~')) = '?' AND RTRIM(NVL(FIELD3,'~~~~')) = '?') 

Also, have you marked columns FIELD1, FIELD2 and FIELD3 as key columns in your job?

[Neil C]

Hi Ray,
thanks for the hints. I tried quoting the ? as indicated, but this did not work. I have tried with and without specifying Key=Yes for the FIELD1 etc.

I have tried reordering the column names in the record description so that SOURCE_SYSTEM, CUST_NBR are first, and FIELD1 etc are first (clutching at straws here..). This also did not change the result. The table is defined with SOURCE_SYSTEM, CUST_NBR, FIELD1, FIELD2, FIELD3, etc etc fields in that order. I can't see that that should matter.

[ray.wurlod]

Try switching back to generated SQL (yes, I know you lose the RTRIM and NVL functions, so preserve them first, if only by copying to the Clipboard).
See what DataStage generates.
Can you spot anything from that?
Columns specified as key go into the WHERE clause in the generated SQL, while columns not specified as keys go into the VALUES clause (if INSERT) or the SET clause (if UPDATE).
Then switch back to user-defined SQL, and add RTRIM and NVL functions to the generated SQL.
Good luck!

[Neil C]

OKay...
That generated

UPDATE P.PF003T00 SET FIELD1 = ?, FIELD2 = ?, FIELD3 = ?, SOURCE_SYSTEM = ?, CUST_NBR = ? WHERE (FIELD1 = ? AND FIELD2 = ? AND FIELD3 = ?);

without the quoted variable.

So even though the FIELDx colums were defined as indexed, it wants to update them. No problems, I can live with that, and it works. An explain via PLSql Developer indicates correct use of the index.

Changing this SQL to include the TRIM(NVL code (to cope with null values in these key fields - not my SQL but I have been assured that it works) gets my origional error message back again. An explain of this code indicates that it is doing a table space scan, due to the use of the functions, I would guess. I can understand this, as this is what DB2 would do as well.

So does the fact that the update will scan the whole table cause the error in DataStage? Or does Oracle not allow this, and DS is not reporting the Oracle error message correctly?

I am ignoring, for the moment, the dissasterous effect that a TS scan will have on performance.

I have acheived what I wanted by doing a DELETE/INSET rather than an UPDATE, but this caused its own problems. I could not do the operation in the one stage, as it crashed because of locks held by itself (the FIELD1, FIELD2, FIELD3 index is unique).

Separating it into two stages, DELETE then INSERT works, but this introduces a possable source of error, where the DELETE stage has committed its changes, and the INSERT stage has not yet done its thing. I could look at introducing restart logic somehow, but the job itself is really quite simple, and should be kept simple, IMHO.

[Neil C]

Problem solved. And I have now learnt about the Update action option of "Replace existing rows completly". Null values are not a problem with this option, as far as I can see.

[Neil C]

Hmm. Spoke too soon. On setting one field to NULLs, I now get the message

"ORA-00001: unique constraint (P.PF003_TEXTKEY_INDEX) violated
"

So this would indicate that the initial DELETE statement could not delete the record. There was no message returned to indicate that this was the case.

Generated SQL is:

DELETE FROM P.PF003T00 WHERE (FIELD1 = ? AND FIELD2 = ? AND FIELD3 = ?);
INSERT INTO P.PF003T00(SOURCE_SYSTEM, CUST_NBR, FIELD1, FIELD2, FIELD3, FIELD4_DESC, NBR_PYMT_MATCHED, DATE_UPDATED, MESSAGE) VALUES (?,?,?,?,?,?,?,?,?);

[chulett]

So this would indicate that the initial DELETE statement could not delete the record.

Of course not. Nothing is ever equal to NULL, not even another NULL. You have to add more code in your statement if you needed to handle nulls, which would be difficult with the positional parameters this stage uses. 'Named' ones, like OCI uses, would make this easier:

Code: Select all

WHERE (Field1 = :1 OR (Field1 IS NULL AND :1 IS NULL))

Something along those lines. Not sure how you'd do that with those darn question mark parameter markers.