Page 1 of 2
ODBC Update and Delete Problem
Posted: Thu Mar 06, 2008 6:40 am
by G SHIVARANJANI
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.
Posted: Thu Mar 06, 2008 6:46 am
by ArndW
Put a reject link on your ODBC output to see what error might be occurring on your update.
Posted: Thu Mar 06, 2008 9:33 am
by G SHIVARANJANI
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. ...
Posted: Thu Mar 06, 2008 10:20 am
by ArndW
Does that mean after you put a reject link *some* of the acno=2 or 3 or 4's are being updated?
Posted: Thu Mar 06, 2008 11:46 pm
by G SHIVARANJANI
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? ...
Posted: Thu Mar 06, 2008 11:47 pm
by G SHIVARANJANI
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? ...
Posted: Thu Mar 06, 2008 11:51 pm
by ray.wurlod
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.
Posted: Fri Mar 07, 2008 12:02 am
by G SHIVARANJANI
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 ...
Posted: Fri Mar 07, 2008 1:09 am
by ray.wurlod
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.
Posted: Fri Mar 07, 2008 1:12 am
by G SHIVARANJANI
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. ...
Posted: Fri Mar 07, 2008 1:17 am
by ray.wurlod
The DataStage log reports what DataStage did.
Posted: Fri Mar 07, 2008 3:10 am
by G SHIVARANJANI
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. ...
Posted: Fri Mar 07, 2008 3:16 am
by ArndW
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?
Posted: Fri Mar 07, 2008 3:47 am
by ray.wurlod
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.
Posted: Fri Mar 07, 2008 6:12 am
by G SHIVARANJANI
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?