MVSFORUMS.com

[Arun]

Hi,
One of our production maintenance job has a sysin that has both the queries below. I'm just confused. Why should they have both of the queries in a single sysin ? I don't see any point in having the first query, as the final outcome of the job with/without the first query is the same.
In that case, why they need to select all the records once, they do a commit and then again select one by one and delete it. Thay can very well have only the second query.

SELECT *
FROM BILLG_ACCT_DTD A
WHERE A.TM_PD_TYP_INST_NBR = 551
AND A.DT_TYPE_CD = 'DISCONNECT'
AND A.DT_STATUS_CD = 'AC'
AND EXISTS
( SELECT *
FROM LGCY_BILLG_ACCT B
WHERE B.BILLG_ACCT_ID = A.BILLG_ACCT_ID
AND B.LGCY_BLACT_END_DT = '9999-12-31')
;

COMMIT ;

DELETE
FROM BILLG_ACCT_DTD A
WHERE A.TM_PD_TYP_INST_NBR = 551
AND A.DT_TYPE_CD = 'DISCONNECT'
AND A.DT_STATUS_CD = 'AC'
AND EXISTS
( SELECT *
FROM LGCY_BILLG_ACCT B
WHERE B.BILLG_ACCT_ID = A.BILLG_ACCT_ID
AND B.LGCY_BLACT_END_DT = '9999-12-31')
;

COMMIT ;



am i missing out something ?? Do u feel any importance/difference it makes by the way they are written ??

[Bithead]

It looks to me like you are creating a backup, or at least a report, of the data (step 1) before you delete it (step 2). It is always a good idea to commit after the select to release any locks.

[Arun]

Thank You Bithead, for you kind reply .

[Mukunda]

Arun
You might also be interested in Pranav Sampath's article (DB2 Magazine)
http://www.db2mag.com/db_area/archives/2002/q4/sampat.shtml 8)