Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Problem in deleting multiple rows from a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Problem in deleting multiple rows from a table
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    Post subject: Re: Problem in deleting multiple rows from a table
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    Post subject: Reply to: Problem in deleting multiple rows from a table
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    Post subject:
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    Post subject: Reply to: Problem in deleting multiple rows from a table
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    Post subject: Reply to: Problem in deleting multiple rows from a table
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    Post subject:
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    Post subject:
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    Post subject: Reply to: Problem in deleting multiple rows from a table
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    Post subject: Reply to: Problem in deleting multiple rows from a table
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    Post subject: Reply to: Problem in deleting multiple rows from a table
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    Post subject: Reply to: Problem in deleting multiple rows from a table
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: 6970
Location: porcelain throne

PostPosted: Wed Jan 30, 2008 3:22 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts TBSARG - Deleting the search criteria... ISPFHerc TSO/ISPF 1 Thu Dec 14, 2017 8:18 am
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Can we combine the rows with same key... V S Amarendra Reddy DB2 12 Fri Dec 01, 2017 10:29 pm
No new posts Having a problem FTP'ng to the MF pahiker All Other Mainframe Topics 2 Fri Nov 10, 2017 8:20 pm
No new posts Formatting multiple records to a sing... Vignesh Sid SYNCSORT 3 Tue Nov 07, 2017 12:22 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us