Need information on DB2 COMMIT

sivasaras · New User Joined: 29 Sep 2007 Posts: 93 Location: chenna/i-

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

Pandora-Box · Posted: Wed Jun 11, 2014 7:56 pm

Did you compile this code and did you make a test run for the same?

sivasaras · New User Joined: 29 Sep 2007 Posts: 93 Location: chenna/i-

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

Craq Giegerich · Posted: Wed Jun 11, 2014 10:10 pm

sivasaras · New User Joined: 29 Sep 2007 Posts: 93 Location: chenna/i-

i need to delete only the names which are having phone numbers as ABC or abc only.

Marso · Posted: Thu Jun 12, 2014 6:21 pm

If you are deleting the row you just FETCHed, you should use

sivasaras · New User Joined: 29 Sep 2007 Posts: 93 Location: chenna/i-

my doubt is how to give that commit every 1000 rows in my program and the above delete

Pandora-Box · Posted: Thu Jun 12, 2014 7:24 pm

For every fetch you make have a counter

Check if the counter is >=1000

Then commit

Chetan Kumar · New User Joined: 03 Dec 2012 Posts: 46 Location: India

Hi, Please post DB2 related queries in DB2 section.

Rohit Umarjikar · Posted: Fri Jun 13, 2014 8:28 pm

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