ODBC Update and Delete Problem
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
ODBC Update and Delete Problem
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.
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.
Shivaranjani
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
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.
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? ...
Shivaranjani
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
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
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 ...
Shivaranjani
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
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. ...
Shivaranjani
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
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?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That's where you are wrong. The report says that eight rows were sent to the database.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...
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
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...
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?
Shivaranjani