I am deleting multiple rows from a db2 test table. There are already around 20 rows in the DB2 table, And when I try to edit some few rows as per my requirement in Sql and when i run the job the few rows which i edited has to get deleted, but every time i run the job only one row at a time gets deleted. Could anyone let me know why could this be.
But when I add new rows apart from the existing 20 rows i am able to delete multiple rows when i run the job.
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
kumar1234 wrote:
Hi All,
I am deleting multiple rows from a db2 test table. There are already around 20 rows in the DB2 table, And when I try to edit some few rows as per my requirement in Sql and when i run the job the few rows which i edited has to get deleted, but every time i run the job only one row at a time gets deleted. Could anyone let me know why could this be.
But when I add new rows apart from the existing 20 rows i am able to delete multiple rows when i run the job.
CLAIM_NO ,
CLAIM_INFO ,
DRAFT_PREFIX ,
LOGICAL_DRAFT_NUM ,
FILLER_1 ,
NAME_TYPE ,
CUST_NAME ,
DATE_OF_BIRTH ,
ADDRESS ,
CITY ,
STATE ,
ZIP ,
COUNTRY ,
SSN
FROM KCLMO122.DEY_DATA
WHERE PROCESS_DATE < CURRENT DATE - 3 MONTHS
AND PROCESS_FLAG = 'P'
END-EXEC.
IF SQLCODE = +0
CONTINUE
ELSE
IF SQLCODE = +100
DISPLAY 'SQLCODE = ', SQLCODE
DISPLAY '*** NO ROWS FOUND ***'
MOVE 4 TO RETURN-CODE
GO TO 2000-EXIT
END-IF.
MOVE CLAIM-NO TO WS-CLAIM-NO.
2500-DELETE-PARA.
EXEC SQL
DELETE
FROM KCLMO122.DEY_DATA
WHERE PROCESS_DATE < CURRENT DATE - 3 MONTHS
AND PROCESS_FLAG = 'P'
AND CLAIM_NO = :WS-CLAIM-NO
END-EXEC.
IF SQLCODE = +0
DISPLAY 'SQLCODE = ' SQLCODE
DISPLAY '*** DELETE SUCCESSFUL ***'
MOVE 4 TO RETURN-CODE
GO TO 2000-EXIT
END-IF.
2000-EXIT.
EXIT.
2500-EXIT.
EXIT.
3000-CLOSE.
EXEC SQL CLOSE DELEROWS END-EXEC.
3000-EXIT.
EXIT.
Please Let me know if I need to make any changes to it that will solve my problem.
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
You are not looping so you would only delete the rows with the CLAIM-NO that you receive on your one and only fetch. 2500-DELETE-PARA should come after 2000-exit and the GO TO 2000-EXIT in 2500-DELETE-PARA should be GO TO 2500-EXIT . The way it is now you are executing that paragraph twice, once as part of PERFORM 2000-COPY-ROWS THRU 2000-exit and then PERFORM 2500-DELETE-PARA THRU 2500-EXIT.
I am still not able to delete multiple rows from the table, can anyone look at the code and let me know if there is any problem in the code, it is a small code.
Joined: 17 Dec 2007 Posts: 33 Location: pune,INDIA
Using 'WHERE CURRENT OF' does not do any harm rather it makes the process a lot more easier. By saying that of course you have to delare the cursor like
Code:
EXEC SQL
DECLARE CURSOR-name CURSOR FOR
SELECT COL1
,COL2
,COL3
FROM TABLENAME
WHERE condition
FOR UPDATE OF COL1
END-EXEC.
Don't you think deleting some records from a table is an UPDATE operation on the same table? I appreaciate Ashwin's post, and probably thats why i mentioned 'otherwise' in my previous post to let the O/P to try a different solution if he wishes. Also the O/P's code is a lot more disorganized and not upto standard of readability.
.jpg)
