IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Problem in deleting multiple rows from a table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Fri Jan 25, 2008 7:04 pm
Reply with quote

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.

Thanks,
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Jan 25, 2008 7:08 pm
Reply with quote

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.

Thanks,


Show us the code you are using.
Back to top
View user's profile Send private message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Fri Jan 25, 2008 7:41 pm
Reply with quote

Please find the code what I am using

IDENTIFICATION DIVISION.
PROGRAM-ID. A5768890.
AUTHOR. QWDWD.
INSTALLATION. SEGSGRHTHTTHH.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.

01 WORKING-STORAGE-BEGINS.
05 FILLER PIC X(44) VALUE
'PROGRAM A5768890 WORKING-STORAGE STARTS HERE'.

EXEC SQL INCLUDE SQLCA END-EXEC.

EXEC SQL INCLUDE CDSCFC1 END-EXEC.

01 WS-CURRENT-DATE PIC X(10).
01 WS-DATE-CONV.
05 WS-YYYY PIC 9(04).
05 WS-MM PIC 9(02).
05 WS-DD PIC 9(02).

01 WS-DELETE-REC.
05 WS-CLAIM-NO PIC X(09) VALUE SPACES.


01 WORKING-STORAGE-ENDS.
05 FILLER PIC X(42) VALUE
'PROGRAM A5768890 WORKING-STORAGE ENDS HERE'.

PROCEDURE DIVISION.

DISPLAY '**** PROGRAM A5768890 START ****'.

PERFORM 1000-DECLARE-AND-OPEN THRU 1000-EXIT.

PERFORM 2000-COPY-ROWS THRU 2000-EXIT.

PERFORM 2500-DELETE-PARA THRU 2500-EXIT.

PERFORM 3000-CLOSE THRU 3000-EXIT.

DISPLAY '**** PROGRAM A5768890 END ****'.

GOBACK.


1000-DECLARE-AND-OPEN.

EXEC SQL DECLARE DELEROWS CURSOR FOR
SELECT

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.

EXEC SQL OPEN DELEROWS END-EXEC.
1000-EXIT.
EXIT.

2000-COPY-ROWS.
EXEC SQL FETCH DELEROWS
INTO
:CLAIM-NO ,
:CLAIM-INFO ,
:DRAFT-PREFIX ,
:LOGICAL-DRAFT-NUM ,
:FILLER-1 ,
:NAME-TYPE ,
:CUST-NAME ,
:DATE-OF-BIRTH ,
:ADDRESS ,
:CITY ,
:STATE ,
:ZIP ,
:COUNTRY ,
:SSN
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.

Thanks,
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Jan 25, 2008 8:27 pm
Reply with quote

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.
Back to top
View user's profile Send private message
rag swain

New User


Joined: 17 Dec 2007
Posts: 33
Location: pune,INDIA

PostPosted: Fri Jan 25, 2008 11:19 pm
Reply with quote

Kumar1234,

Please use [code] instead of directly pasting it to look like mainframe centric the way we get used to.
Back to top
View user's profile Send private message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Mon Jan 28, 2008 2:21 pm
Reply with quote

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.

01 WORKING-STORAGE-BEGINS.
05 FILLER PIC X(44) VALUE
'PROGRAM BCLM0132 WORKING-STORAGE STARTS HERE'.

EXEC SQL INCLUDE SQLCA END-EXEC.

EXEC SQL INCLUDE CLMJHJKL END-EXEC.

01 WS-CURRENT-DATE PIC X(10).
01 WS-DATE-CONV.
05 WS-YYYY PIC 9(04).
05 WS-MM PIC 9(02).
05 WS-DD PIC 9(02).
01 WS-END-OF-RECS PIC X(1) VALUE 'N'.

01 WS-DELETE-REC.
05 WS-CLAIM-NO PIC X(09) VALUE SPACES.

01 WORKING-STORAGE-ENDS.
05 FILLER PIC X(42) VALUE
'PROGRAM BCLM0132 WORKING-STORAGE ENDS HERE'.
PROCEDURE DIVISION.

DISPLAY '**** PROGRAM BCLM0132 START ****'.

PERFORM 1000-DECLARE-AND-OPEN THRU 1000-EXIT

IF RETURN-CODE = 0
PERFORM 2000-COPY-ROWS THRU 2000-EXIT
UNTIL WS-END-OF-RECS = 'Y'
END-IF.

PERFORM 3000-CLOSE THRU 3000-EXIT.

DISPLAY '**** PROGRAM BCLM0132 END ****'.

GOBACK.


1000-DECLARE-AND-OPEN.

EXEC SQL DECLARE DELEROWS CURSOR FOR
SELECT
CUST ,
REGOFF ,
APPLICATION ,
CLAIM_NO ,
CLAIM_INFO ,
DRAFT_PREFIX ,
LOGICAL_DRAFT_NUM ,
FILLER_1 ,
NAME_TYPE ,
CUST_NAME ,
DATE_OF_BIRTH ,
ADDRESS_1 ,
ADDRESS_2 ,
CITY ,
STATE ,
ZIP ,
COUNTRY ,
PASSPORT ,
SSN
FROM BOKDMM01.CAC_DATA
WHERE PROCESS_DATE < CURRENT DATE - 3 MONTHS
AND PROCESS_FLAG = 'P'
END-EXEC.

EXEC SQL OPEN DELEROWS END-EXEC.
1000-EXIT.
EXIT.

2000-COPY-ROWS.
EXEC SQL FETCH DELEROWS
INTO
:CUST ,
:REGOFF ,
:APPLICATION ,
:CLAIM-NO ,
:CLAIM-INFO ,
:DRAFT-PREFIX ,
:LOGICAL-DRAFT-NUM ,
:FILLER-1 ,
:NAME-TYPE ,
:CUST-NAME ,
:DATE-OF-BIRTH ,
:ADDRESS-1 ,
:ADDRESS-2 ,
:CITY ,
:STATE ,
:ZIP ,
:COUNTRY ,
:PASSPORT ,
:SSN
END-EXEC.

IF SQLCODE = +0
PERFORM 2500-DELETE-PARA THRU 2500-EXIT.
MOVE CLAIM-NO TO WS-CLAIM-NO.
IF SQLCODE = +100
MOVE 'Y' TO WS-END-OF-RECS
END-IF.
2000-EXIT.
EXIT.
2500-DELETE-PARA.
EXEC SQL
DELETE
FROM BOKDMM01.CAC_DATA
WHERE CLAIM_NO = :WS-CLAIM-NO
END-EXEC.

IF SQLCODE = +0
DISPLAY 'SQLCODE = ' SQLCODE
DISPLAY '*** DELETE SUCCESSFUL ***'
MOVE 4 TO RETURN-CODE
GO TO 2500-EXIT
END-IF.

2500-EXIT.
EXIT.

3000-CLOSE.

EXEC SQL CLOSE DELEROWS END-EXEC.
3000-EXIT.
EXIT.



thanks.
Back to top
View user's profile Send private message
ashwinreddy

Active User


Joined: 16 Sep 2004
Posts: 106
Location: Hyderabad

PostPosted: Mon Jan 28, 2008 2:44 pm
Reply with quote

Hi Kumar,

Change your code as

Move the Claim-no before performing the delete processing as

IF SQLCODE = +0

MOVE CLAIM-NO TO WS-CLAIM-NO.

PERFORM 2500-DELETE-PARA THRU 2500-EXIT. (Perform this para until WS-END-OF-RECS = 'Y')

IF SQLCODE = +100
MOVE 'Y' TO WS-END-OF-RECS

ELSE

Perfom Fetch Para again and Move the Claim ID to WS-CLAIM-NO.
Now call the delete processing

END-IF.


Its just a Pseduo code, as i can't write entire program.

Please correct me if i am wrong.

Cheers
Ashwin
Back to top
View user's profile Send private message
ashwinreddy

Active User


Joined: 16 Sep 2004
Posts: 106
Location: Hyderabad

PostPosted: Mon Jan 28, 2008 2:52 pm
Reply with quote

Hi Kumar,

Please re-visit your code, looks need to be work-out a lot.

My Suggestion might have confused you, Please let me know if you have any questions.

Cheers
Ashwin
Back to top
View user's profile Send private message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Mon Jan 28, 2008 3:09 pm
Reply with quote

Thanks for your input Ashwin. I will work on this and let you know If I have any questions.

Thanks,
Kumar.
Back to top
View user's profile Send private message
rag swain

New User


Joined: 17 Dec 2007
Posts: 33
Location: pune,INDIA

PostPosted: Tue Jan 29, 2008 8:25 pm
Reply with quote

Otherwise try using WHERE CURRENT OF while deleting the rows in a loop.

[code]

EXEC SQL
DELETE FROM TABLENAME
WHERE CURRENT OF CURSOR-NAME
END-EXEC.

[code]
Back to top
View user's profile Send private message
nikyojin

New User


Joined: 05 Oct 2005
Posts: 94

PostPosted: Tue Jan 29, 2008 10:09 pm
Reply with quote

Hi Kumar1234

Quote:
EXEC SQL
DELETE FROM TABLENAME
WHERE CURRENT OF CURSOR-NAME
END-EXEC.


The above expression can't be used since it has been mentioned
Code:
FOR SELECT
on the cursor.

Change the condition to
Code:
FOR UPDATE


Please try ashwin's suggestions and let us know if you have any queries.

Thanks,
Nikhil S. Thayyil
Back to top
View user's profile Send private message
rag swain

New User


Joined: 17 Dec 2007
Posts: 33
Location: pune,INDIA

PostPosted: Tue Jan 29, 2008 10:30 pm
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jan 30, 2008 3:22 am
Reply with quote

IF RETURN-CODE = 0


where is RETURN-CODE being set?

go to exit is bad programming practice.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Load new table with Old unload - DB2 DB2 6
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
Search our Forums:

Back to Top