Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need information on DB2 COMMIT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Wed Jun 11, 2014 7:34 pm    Post subject: Need information on DB2 COMMIT
Reply with quote

HI,

My requirement is to delete all the phone numbers for the empids with the phone numbers having country code ABC or abc.

here in the table phone number is declared as character.

so i have written a delete program to delete the ph numbers
which is having 3 million records.

I used control card to get input as date from the job.
please verify and let me know the code looks ok to proceed
further or anyting to be changed.

300**************************************************************
015400* DCLGENS *
015500***************************************************************
015600 EXEC SQL
015700 INCLUDE SQLCA
015800 END-EXEC.
015900
015901 EXEC SQL
015902 INCLUDE TABLE1
015903 END-EXEC.
015904
015905 EXEC SQL
015906 DECLARE table1-CURSOR CURSOR WITH HOLD FOR
015908 SELECT TABLE1_EMP_NAME
015909 ,TABLE1_EMP_NBR_C
015910 ,TABLE1_EMP-ADDRSS
015920 ,TABLE1-EMP_ID
015930 ,TABLE1_EMP_SCANNED_Y
015940 ,TABLE1_EMP_SCANNED_M
015950 ,TABLE1_EMP_UPD_S
015960 TABLE1_PHONE_X
015970 FROM table1_EMP
016000 WHERE UPPER(TABLE1_PHONE_X) = :LIT-ABC
016010 AND EMP_SCANNED_Y BETWEEN :WS-SCAN-N
016020 AND :WS-SCAN-S WITH UR
016100 END-EXEC.
019100 EJECT
019700 LINKAGE SECTION.
020100 PROCEDURE DIVISION.
020200
020587 00000-MAINLINE.
021000 PERFORM 10000-INITIAL
021100 PERFORM 31000-OPEN-CURSOR
021110 PERFORM 32000-FETCH-CURSOR UNTIL END-OF-VIN
021200 PERFORM 30000-PROCESS-VINS UNTIL END-OF-VIN
021210 PERFORM 34000-CLOSE-CURSOR
021300 PERFORM 40000-WRAP-UP
021301 CLOSE DELETE-FILE
021310
021400 GOBACK
021500 .
021600 EJECT
021700
021800 10000-INITIAL.
002240 OPEN OUTPUT DELETE-FILE
022510 PERFORM 20000-READ-WS-INPUT-REC
022700 .
022800 EJECT
022900 20000-READ-WS-INPUT-REC.
023000*****************************************************************
023010** READ CONTROL CARD *
023020*****************************************************************
023070 OPEN INPUT CHECKPT-IN
023080
023090 READ CHECKPT-IN INTO WS-INPUT-RECORD
023091 AT END
023092 MOVE "Y" TO EOF-VIN-SW.
023093
023094 MOVE WS-SCANNED-M TO WS-SCAN-N
023095 MOVE WS-SCANNED-Y TO WS-SCAN-S
023100
023101 CLOSE CHECKPT-IN
023102 .
023110 30000-PROCESS-VINS.
023700 PERFORM 32000-FETCH-CURSOR
024000 .
024100 EJECT
024240 31000-OPEN-CURSOR.
024250 EXEC SQL
024260 OPEN TABLE1-CURSOR
024270 END-EXEC.
024280
024290 EVALUATE SQLCODE
024291 WHEN ZEROES
024295 CONTINUE
024296 WHEN OTHER
024297 MOVE LIT-OPEN TO DB2-ABEND-FUNCTION
024298 MOVE SQLCODE TO DB2-ABEND-SQLCODE
024299 MOVE LIT-TABLE1 TO DB2-ABEND-TABLE
024300 MOVE DB2-ABEND-MSG TO ABEND-MSG
024301 PERFORM 99999-ABNORMAL-END-OF-JOB
024302 END-EVALUATE
024303 .
024307 32000-FETCH-CURSOR.
024309 EXEC SQL
024310 FETCH TABLE1-CURSOR
024311 INTO TABLE1-EMP-NAME
015909 ,TABLE1-EMP-NBR
015910 ,TABLE1-EMP-ADDRSS
015920 ,TABLE1-EMP-ID
015930 ,TABLE1-EMP-SCANNED-Y
015940 ,TABLE1-EMP-SCANNED-M
015950 ,TABLE1-EMP-UPD-S
015960 TABLE1-PHONE-X
024326 END-EXEC.
024330 EVALUATE SQLCODE
024331 WHEN ZEROES
024332 WRITE DELETE-RECORD FROM TABLE1-EMP-NAME
024333 PERFORM 33000-DELETE-TABLE1-ID
024341 WHEN PLUS-100
024342 MOVE LIT-Y TO EOF-NO-SW
024344 WHEN OTHER
024345 MOVE LIT-FETCH TO DB2-ABEND-FUNCTION
024346 MOVE SQLCODE TO DB2-ABEND-SQLCODE
024347 MOVE LIT-TABLE1 TO DB2-ABEND-TABLE
024348 MOVE DB2-ABEND-MSG TO ABEND-MSG
024349 PERFORM 99999-ABNORMAL-END-OF-JOB
024350
024351 END-EVALUATE
024354 .
024358 34000-CLOSE-CURSOR.
024359 EXEC SQL
024360 CLOSE TABLE1-CURSOR
024361 END-EXEC
024362
024363 EVALUATE SQLCODE
024364 WHEN ZERO
024365 CONTINUE
024366 WHEN OTHER
024367 MOVE LIT-CLOSE TO DB2-ABEND-FUNCTION
024368 MOVE SQLCODE TO DB2-ABEND-SQLCODE
024369 MOVE LIT-TABLE1 TO DB2-ABEND-TABLE
024370 MOVE DB2-ABEND-MSG TO ABEND-MSG
024371 PERFORM 99999-ABNORMAL-END-OF-JOB
024372 END-EVALUATE
025213 .
025220 33000-DELETE-TABLE1-ID.
026200******************************************************************
026300* DELETE THE PHS *
026400******************************************************************
026700 EXEC SQL
026800 DELETE FROM TABLE1-CURSOR
026801 WHERE TABLE1_EMP_NAME = :TABLE1-EMP-NAME
026810 AND TABLE1_EMP_NBR = :TABLE1-EMP-NBR
026811 AND TABLE1_EMP_ADDRSS = :TABLE1-EMP-ADDRSS
026812 AND TABLE1_EMP_ID = :TABLE1-EMP-ID
026815 AND UPPER(TABLE1-PHONE-X) = :LIT-ABC
027300 END-EXEC
027500 MOVE SQLCODE TO WS-SQLCODE
027600 EVALUATE TRUE
027700 WHEN SQL-SUCCESSFUL
027800 ADD 1 TO WS-TABLE1-DELETE-CNT
027810 WHEN SQL-NOT-FOUND
027820 MOVE LIT-N TO NO-SW
027900 WHEN OTHER
027901 MOVE LIT-CLOSE TO DB2-ABEND-FUNCTION
027902 MOVE SQLCODE TO DB2-ABEND-SQLCODE
027903 MOVE LIT-TABLE1 TO DB2-ABEND-TABLE
027904 MOVE DB2-ABEND-MSG TO ABEND-MSG
027905 PERFORM 99999-ABNORMAL-END-OF-JOB
028200
028300 END-EVALUATE
028400 .
028500 EJECT
028800 37000-COMMIT.
029500 EXEC SQL
029600 COMMIT
029700 END-EXEC
029800
029910 MOVE SQLCODE TO WS-SQLCODE
030000 IF SQL-SUCCESSFUL
030100 CONTINUE
030200 ELSE
030300 DISPLAY "DB2 COMMIT ERROR"
030400 END-IF
030500 .
030600 EJECT
030700
030800 40000-WRAP-UP.
031400 PERFORM 37000-COMMIT.
031500 DISPLAY "COMMIT"
031700 EJECT
034000
034100 99999-ABNORMAL-END-OF-JOB.
034500
034600 IF AVOID-COREDUMP
034700 MOVE LIT-99909 TO SAR-NUMBER
034900 ELSE
035000 CALL "COREDUMP"
035100 END-IF
035200 .
035300 EJECT
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Jun 11, 2014 7:56 pm    Post subject:
Reply with quote

Did you compile this code and did you make a test run for the same?
Back to top
View user's profile Send private message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Wed Jun 11, 2014 8:39 pm    Post subject:
Reply with quote

yes i compiled and tested the code in test it is working fine, my doubt is whether this should affect the table, my commit is ok or anywhere else i need to use it since record count is huge
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Wed Jun 11, 2014 10:10 pm    Post subject: Re: Need information on DB2 COMMIT
Reply with quote

sivasaras wrote:
HI,

My requirement is to delete all the phone numbers for the empids with the phone numbers having country code ABC or abc.


Delete all the rows with the county code (name, address ,emp_id, etc) or just erase the phone number?
Back to top
View user's profile Send private message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Thu Jun 12, 2014 2:41 pm    Post subject:
Reply with quote

i need to delete only the names which are having phone numbers as ABC or abc only.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Thu Jun 12, 2014 6:21 pm    Post subject: Reply to: Need information on DB2 COMMIT
Reply with quote

If you are deleting the row you just FETCHed, you should use
Code:
DELETE FROM table1_EMP
WHERE CURRENT OF table1-CURSOR


You also could count the deleted rows and COMMIT every, say, 1000 rows.
Back to top
View user's profile Send private message
sivasaras

New User


Joined: 29 Sep 2007
Posts: 90
Location: chenna/i-

PostPosted: Thu Jun 12, 2014 6:26 pm    Post subject:
Reply with quote

my doubt is how to give that commit every 1000 rows in my program and the above delete
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jun 12, 2014 7:24 pm    Post subject:
Reply with quote

For every fetch you make have a counter

Check if the counter is >=1000

Then commit
Back to top
View user's profile Send private message
Chetan Kumar

New User


Joined: 03 Dec 2012
Posts: 35
Location: India

PostPosted: Fri Jun 13, 2014 10:32 am    Post subject:
Reply with quote

Hi, Please post DB2 related queries in DB2 section.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Jun 13, 2014 8:28 pm    Post subject:
Reply with quote

Please add Code tags , program can be simplified further, you may wants to have below logic added for Commit.


Code:
DELETE FROM TABLE1-CURSOR
  WHERE TABLE1_EMP_NAME = :TABLE1-EMP-NAME
     AND TABLE1_EMP_NBR = :TABLE1-EMP-NBR
     AND TABLE1_EMP_ADDRSS = :TABLE1-EMP-ADDRSS
     AND TABLE1_EMP_ID = :TABLE1-EMP-ID
     AND UPPER(TABLE1-PHONE-X) = :LIT-ABC
 END-EXEC

MOVE SQLCODE TO WS-SQLCODE

 EVALUATE TRUE
    WHEN SQL-SUCCESSFUL
             IF  WS-TABLE1-DELETE-CNT = 1000
                  << Issue a Commit >>>
                   Check SQLCODE
                    IF Zero
                       Initialize WS-TABLE1-DELETE-CNT
                    ELSE
                       Error
                    END-IF
             ELSE
                    ADD 1 TO WS-TABLE1-DELETE-CNT
             END-IF


However instead of program check if you can perform this by DB2 utilities and in your shop there must be an existing program which does the similar type of processing so you need to use that as a template and fit your entire logic accordingly instead of re inventing the wheel ..
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 -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Obtaining file information pahiker COBOL Programming 25 Tue Sep 27, 2016 7:25 pm
No new posts Need information on COBOL MOVE CORRES... subratarec COBOL Programming 10 Tue Jun 28, 2016 10:38 pm
No new posts Passing information through JCL as in... Pia Baruah JCL & VSAM 5 Wed May 25, 2016 2:22 pm
No new posts Extracting Information from DD Statem... Charles Wolters All Other Mainframe Topics 7 Wed Apr 13, 2016 10:21 pm
This topic is locked: you cannot edit posts or make replies. Job Offer: Information systems consu... Express Mainframe Jobs 0 Thu Mar 10, 2016 7:18 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us