ODBC Update and Delete Problem

G SHIVARANJANI · Post by **G SHIVARANJANI** » Thu Mar 06, 2008 6:40 am

We are trying to update a table called Savedreport in infodev datasource of progress DB (Application DB Which supports SQL 92) USING ETL.

1) We need the query to be :

UPDATE
PUB.Savedreport
SET
dmr_limbo_date = Saveddate

where acno = ORCHESTRATE.Acno

Issue: This query updates only acno=1 (where ORCHESTRATE.acno passes 1,2,3 and many more acno present in savedreport) but it do not update rest of the acno

2) We tried to hard code,

UPDATE
PUB.Savedreport
SET
dmr_limbo_date = Saveddate

where acno IN( 1,2,3)

It updates all 1,2,3

Please suggest a solution.This happens the same way for deletion also

Is it a commit problem with progress application ...
As it is firing the query once only but not every time ORCHESTRATE gets a value.

ArndW · Post by **ArndW** » Thu Mar 06, 2008 6:46 am

Put a reject link on your ODBC output to see what error might be occurring on your update.

G SHIVARANJANI · Post by **G SHIVARANJANI** » Thu Mar 06, 2008 9:33 am

Putting a reject link makes the log to show a different count of number of records getting updated.....but no record is geting rejected in that case

ArndW wrote:Put a reject link on your ODBC output to see what error might be occurring on your update. ...

ArndW · Post by **ArndW** » Thu Mar 06, 2008 10:20 am

Does that mean after you put a reject link *some* of the acno=2 or 3 or 4's are being updated?

G SHIVARANJANI · Post by **G SHIVARANJANI** » Thu Mar 06, 2008 11:46 pm

Yes all of the records passed to orchestrate are getting updated,I could see in the Table.

I.e if i send 2,3,4,5 into ORCHESTRATE then all of them are getting updated which could be justified lookin into the table....BUT the Log in the director shows

Number of RECORDS UPDATED as more than 4.
Note:2,3,4,5 being unique in the table.

ArndW wrote:Does that mean after you put a reject link *some* of the acno=2 or 3 or 4's are being updated? ...

G SHIVARANJANI · Post by **G SHIVARANJANI** » Thu Mar 06, 2008 11:47 pm

NOTE: No records are getting rejected

ArndW wrote:Does that mean after you put a reject link *some* of the acno=2 or 3 or 4's are being updated? ...

ray.wurlod · Post by **ray.wurlod** » Thu Mar 06, 2008 11:51 pm

Updates are never rejected. You can issue a statement against the database such as UPDATE table SET col1=value1,col2=value2 WHERE 1=0 and, although no rows will be affected, none is rejected in any sense. DataStage plays by the same rules.

G SHIVARANJANI · Post by **G SHIVARANJANI** » Fri Mar 07, 2008 12:02 am

If i send records with acno (2,3,4,5,6) through ORCHESTRATE, and the number of records in the table are 2,3,4,5 only then record with acno 6 goes into reject link leaving
2,3,4,5 as updated...BUT THE LOG SHOW

RECORDS UPDATE:10
RECORDS REJECTED:1

ray.wurlod wrote:Updates are never rejected. You can issue a statement against the database such as UPDATE table SET col1=value1,col2=value2 WHERE 1=0 and, although no rows will be affected, none is rejected i ...

ray.wurlod · Post by **ray.wurlod** » Fri Mar 07, 2008 1:09 am

The log reports the number of rows sent along the link. What happens to them is no concern of DataStage's, except to detect errors. That is, DataStage does NOT report the number of rows affected.

G SHIVARANJANI · Post by **G SHIVARANJANI** » Fri Mar 07, 2008 1:12 am

You mean the Log report has insignificant information.Which should not be considered as one of an issue

ray.wurlod wrote:The log reports the number of rows sent along the link. What happens to them is no concern of DataStage's, except to detect errors. That is, DataStage does NOT report the number of rows affected. ...

ray.wurlod · Post by **ray.wurlod** » Fri Mar 07, 2008 1:17 am

The DataStage log reports what DataStage did.

G SHIVARANJANI · Post by **G SHIVARANJANI** » Fri Mar 07, 2008 3:10 am

The report says It has updated 8 records intead of 4 records, but when seen in the database it shows that it has updated 4 records only...

even if the key on which it has been updated is unique.

ray.wurlod wrote:The DataStage log reports what DataStage did. ...

ArndW · Post by **ArndW** » Fri Mar 07, 2008 3:16 am

If you replace your output ODBC with a sequential file, you will see that 8 records get sent down that link. The same applies to ODBC, but for some reason not all 8 records are doing what you expect in the database. Can you try to execute these 8 statements manually and separately to the database and see if the updates takes place?

ray.wurlod · Post by **ray.wurlod** » Fri Mar 07, 2008 3:47 am

G SHIVARANJANI wrote:The report says It has updated 8 records intead of 4 records, but when seen in the database it shows that it has updated 4 records only...

That's where you are wrong. The report says that eight rows were sent to the database.

Whether or not these succeeded in updating existing rows is not what is being reported - this is managed through error handling. What is being reported is the link row count.

G SHIVARANJANI · Post by **G SHIVARANJANI** » Fri Mar 07, 2008 6:12 am

When i try to update the records manualluy into the data base it updates well....
It updates for the records for which the update is asked for ....but not double time...

ArndW wrote:If you replace your output ODBC with a sequential file, you will see that 8 records get sent down that link. The same applies to ODBC, but for some reason not all 8 records are doing what you expect in the database. Can you try to execute these 8 statements manually and separately to the database and see if the updates takes place?