Db2

Hi Experts Team,

 I am looking for information and expert ideas how to to delete the the data in Batches, capture and update the deleted Batch information saying Batch 1 completed Batch 2 completed and so on keeping the history data which all processed and deleted  mark as success in Temp table and during failure job should rerun from failure batches..

We are getting the data which needs to get deleted in file which we are loading in the temp file and do the join from source table and perform the delete with commit count

This was the logic I was thinking for Batch deletion not sure this was the correct way or you have a better suggestion..
I know this can be done easily with ETL and Informatica and front tool.. But here We need to write some hand  code logic script

I will add 3 column in Temp table SED_ID(Auto generated), STATUS = 'F'  Char(1) -  (Default Set to F)  and STATUS_DATE (Time stamp) (Default set to time stamp)

Temp table where condition will change to following - for the  Batch of 100

where STATUS = 'F' and seq_id >=0 and SEQID <= 100

once the deletion get completed,  after that update will perform in temp table with above condition which will mark 1 batch of seq_id >=0 and SEQID <= 100 Status Column to C

and next batch will start from seq_id > 100 and SEQID <= 200 so on..

Please let me know you have better suggestion to complete this kind of activity...

------------------------------
PRASHANT SHIVMATH
------------------------------

#Db2

Original Message:
Sent: 5/26/2020 8:54:00 AM
From: PRASHANT SHIVMATH
Subject: Bulk Deletion with Batch

Hi Experts Team,

 I am looking for information and expert ideas how to to delete the the data in Batches, capture and update the deleted Batch information saying Batch 1 completed Batch 2 completed and so on keeping the history data which all processed and deleted  mark as success in Temp table and during failure job should rerun from failure batches..

We are getting the data which needs to get deleted in file which we are loading in the temp file and do the join from source table and perform the delete with commit count

This was the logic I was thinking for Batch deletion not sure this was the correct way or you have a better suggestion..
I know this can be done easily with ETL and Informatica and front tool.. But here We need to write some hand  code logic script

I will add 3 column in Temp table SED_ID(Auto generated), STATUS = 'F'  Char(1) -  (Default Set to F)  and STATUS_DATE (Time stamp) (Default set to time stamp)

Temp table where condition will change to following - for the  Batch of 100

where STATUS = 'F' and seq_id >=0 and SEQID <= 100

once the deletion get completed,  after that update will perform in temp table with above condition which will mark 1 batch of seq_id >=0 and SEQID <= 100 Status Column to C

and next batch will start from seq_id > 100 and SEQID <= 200 so on..

Please let me know you have better suggestion to complete this kind of activity...

------------------------------
PRASHANT SHIVMATH
------------------------------

#Db2

Original Message:
Sent: 5/27/2020 2:31:00 AM
From: Markus Schaub
Subject: RE: Bulk Deletion with Batch

Hi Prashant Shivmath

 

If you are using a later version of Db2 you can use the FETCH FIRST N ROWS clause of the delete until you get SQLCODE +100.

 

Regarding history you could you temporal tables or if this does not fit your requirements you can SELECT from a DELETE statement (data-change-table-reference).

 

 

 

Markus Schaub

*****JuliusBaer Disclaimer***** This e-mail is for the intended recipient only and may contain confidential or privileged information. If you have received this e-mail by mistake, please contact us immediately and completely delete it (and any attachments) and do not forward it or inform any other person of its contents. If you send us messages by e-mail, we take this as your authorisation to correspond with you by e-mail, however, we reserve the right not to execute orders and instructions transmitted by e-mail at any time and without further explanation. If you do not wish to receive any further e-mail correspondence please let us know. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, amended, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. Neither the Julius Baer Group nor the sender accept liability for any errors or omissions in the content of this message which arise as a result of its e-mail transmission. Please note that all e-mail communications to and from the Julius Baer Group may be monitored. This communication is for informational purposes only. It is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction.

Original Message:
Sent: 5/26/2020 8:54:00 AM
From: PRASHANT SHIVMATH
Subject: Bulk Deletion with Batch

Hi Experts Team,

 I am looking for information and expert ideas how to to delete the the data in Batches, capture and update the deleted Batch information saying Batch 1 completed Batch 2 completed and so on keeping the history data which all processed and deleted  mark as success in Temp table and during failure job should rerun from failure batches..

We are getting the data which needs to get deleted in file which we are loading in the temp file and do the join from source table and perform the delete with commit count

This was the logic I was thinking for Batch deletion not sure this was the correct way or you have a better suggestion..
I know this can be done easily with ETL and Informatica and front tool.. But here We need to write some hand  code logic script

I will add 3 column in Temp table SED_ID(Auto generated), STATUS = 'F'  Char(1) -  (Default Set to F)  and STATUS_DATE (Time stamp) (Default set to time stamp)

Temp table where condition will change to following - for the  Batch of 100

where STATUS = 'F' and seq_id >=0 and SEQID <= 100

once the deletion get completed,  after that update will perform in temp table with above condition which will mark 1 batch of seq_id >=0 and SEQID <= 100 Status Column to C

and next batch will start from seq_id > 100 and SEQID <= 200 so on..

Please let me know you have better suggestion to complete this kind of activity...

------------------------------
PRASHANT SHIVMATH
------------------------------

#Db2

Hi Prashant,

I recommend to use compound sql, deleting over a cursor (with batchsize to be defined and inserting status in status table). Where clause can be changed to e.g. have batches to be executed in parallel by separating by you mentioned seq_ID. same sample code (put in a file and execute file with "db2 -vtsf <myfile>" or within a SQL Editor like DBeaver or Squirrel) - ";--" is to escape the ";" :

########​
########​

Feel free to ask if you have any concerns.

------------------------------
Markus Fraune
------------------------------

Original Message:
Sent: Thu May 28, 2020 03:36 AM
From: PRASHANT SHIVMATH
Subject: Bulk Deletion with Batch

Hi Markus,

Thanks for the update.. I will take a look. Do you have any reference code logic which you used in the project..

Kind Regards

Prashant Shivmath
Mobile : +91-9892691478
Email : prashant.shivmath@in.ibm.com


"Markus Schaub via IBM Community" ---27-05-2020 21:53:57---Hi Prashant Shivmath

Original Message:
Sent: 5/27/2020 2:31:00 AM
From: Markus Schaub
Subject: RE: Bulk Deletion with Batch

Hi Prashant Shivmath

 

If you are using a later version of Db2 you can use the FETCH FIRST N ROWS clause of the delete until you get SQLCODE +100.

 

Regarding history you could you temporal tables or if this does not fit your requirements you can SELECT from a DELETE statement (data-change-table-reference).

 

 

 

Markus Schaub

*****JuliusBaer Disclaimer***** This e-mail is for the intended recipient only and may contain confidential or privileged information. If you have received this e-mail by mistake, please contact us immediately and completely delete it (and any attachments) and do not forward it or inform any other person of its contents. If you send us messages by e-mail, we take this as your authorisation to correspond with you by e-mail, however, we reserve the right not to execute orders and instructions transmitted by e-mail at any time and without further explanation. If you do not wish to receive any further e-mail correspondence please let us know. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, amended, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. Neither the Julius Baer Group nor the sender accept liability for any errors or omissions in the content of this message which arise as a result of its e-mail transmission. Please note that all e-mail communications to and from the Julius Baer Group may be monitored. This communication is for informational purposes only. It is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction.

Original Message:
Sent: 5/26/2020 8:54:00 AM
From: PRASHANT SHIVMATH
Subject: Bulk Deletion with Batch

Hi Experts Team,

 I am looking for information and expert ideas how to to delete the the data in Batches, capture and update the deleted Batch information saying Batch 1 completed Batch 2 completed and so on keeping the history data which all processed and deleted  mark as success in Temp table and during failure job should rerun from failure batches..

We are getting the data which needs to get deleted in file which we are loading in the temp file and do the join from source table and perform the delete with commit count

This was the logic I was thinking for Batch deletion not sure this was the correct way or you have a better suggestion..
I know this can be done easily with ETL and Informatica and front tool.. But here We need to write some hand  code logic script

I will add 3 column in Temp table SED_ID(Auto generated), STATUS = 'F'  Char(1) -  (Default Set to F)  and STATUS_DATE (Time stamp) (Default set to time stamp)

Temp table where condition will change to following - for the  Batch of 100

where STATUS = 'F' and seq_id >=0 and SEQID <= 100

once the deletion get completed,  after that update will perform in temp table with above condition which will mark 1 batch of seq_id >=0 and SEQID <= 100 Status Column to C

and next batch will start from seq_id > 100 and SEQID <= 200 so on..

Please let me know you have better suggestion to complete this kind of activity...

------------------------------
PRASHANT SHIVMATH
------------------------------

#Db2

Original Message:
Sent: 5/29/2020 3:27:00 AM
From: Markus Fraune
Subject: RE: Bulk Deletion with Batch

Hi Prashant,

I recommend to use compound sql, deleting over a cursor (with batchsize to be defined and inserting status in status table). Where clause can be changed to e.g. have batches to be executed in parallel by separating by you mentioned seq_ID. same sample code (put in a file and execute file with "db2 -vtsf <myfile>" or within a SQL Editor like DBeaver or Squirrel) - ";--" is to escape the ";" :

########​

BEGIN

DECLARE batchsize integer;--

DECLARE l_count INTEGER;--

SET batchsize = 10000;--

insert into schema.status values (CURRENT TIMESTAMP , 'Delete Old schema.table_to_be_deleted: Starting Delete');--

commit;--

SET l_count = 0;--

FOR row AS c CURSOR WITH HOLD FOR

select

*

from

schema.table_to_be_deleted

where

insert_time_column < current timestamp - 2 years

FOR UPDATE

DO

SET l_count = l_count + 1;--

delete from schema.table_to_be_deleted WHERE CURRENT OF c;--

IF (MOD (l_count, batchsize) = 0)

THEN

commit;--

insert into schema.status values (CURRENT TIMESTAMP , 'Delete Old schema.table_to_be_deleted: Count ' || l_count);--

commit;--

END IF;--

END FOR;--

commit;--

insert into schema.status values (CURRENT TIMESTAMP , 'Delete Old schema.table_to_be_deleted: FINISH - Deleted Rows: ' || l_count);--

commit;--

END!

########​

Feel free to ask if you have any concerns.

------------------------------
Markus Fraune
------------------------------

Original Message:
Sent: Thu May 28, 2020 03:36 AM
From: PRASHANT SHIVMATH
Subject: Bulk Deletion with Batch

Hi Markus,

Thanks for the update.. I will take a look. Do you have any reference code logic which you used in the project..

Kind Regards

Prashant Shivmath
Mobile : +91-9892691478
Email : prashant.shivmath@in.ibm.com


"Markus Schaub via IBM Community" ---27-05-2020 21:53:57---Hi Prashant Shivmath

Original Message:
Sent: 5/27/2020 2:31:00 AM
From: Markus Schaub
Subject: RE: Bulk Deletion with Batch

Hi Prashant Shivmath

 

If you are using a later version of Db2 you can use the FETCH FIRST N ROWS clause of the delete until you get SQLCODE +100.

 

Regarding history you could you temporal tables or if this does not fit your requirements you can SELECT from a DELETE statement (data-change-table-reference).

 

 

 

Markus Schaub

*****JuliusBaer Disclaimer***** This e-mail is for the intended recipient only and may contain confidential or privileged information. If you have received this e-mail by mistake, please contact us immediately and completely delete it (and any attachments) and do not forward it or inform any other person of its contents. If you send us messages by e-mail, we take this as your authorisation to correspond with you by e-mail, however, we reserve the right not to execute orders and instructions transmitted by e-mail at any time and without further explanation. If you do not wish to receive any further e-mail correspondence please let us know. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, amended, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. Neither the Julius Baer Group nor the sender accept liability for any errors or omissions in the content of this message which arise as a result of its e-mail transmission. Please note that all e-mail communications to and from the Julius Baer Group may be monitored. This communication is for informational purposes only. It is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction.

Original Message:
Sent: 5/26/2020 8:54:00 AM
From: PRASHANT SHIVMATH
Subject: Bulk Deletion with Batch

Hi Experts Team,

 I am looking for information and expert ideas how to to delete the the data in Batches, capture and update the deleted Batch information saying Batch 1 completed Batch 2 completed and so on keeping the history data which all processed and deleted  mark as success in Temp table and during failure job should rerun from failure batches..

We are getting the data which needs to get deleted in file which we are loading in the temp file and do the join from source table and perform the delete with commit count

This was the logic I was thinking for Batch deletion not sure this was the correct way or you have a better suggestion..
I know this can be done easily with ETL and Informatica and front tool.. But here We need to write some hand  code logic script

I will add 3 column in Temp table SED_ID(Auto generated), STATUS = 'F'  Char(1) -  (Default Set to F)  and STATUS_DATE (Time stamp) (Default set to time stamp)

Temp table where condition will change to following - for the  Batch of 100

where STATUS = 'F' and seq_id >=0 and SEQID <= 100

once the deletion get completed,  after that update will perform in temp table with above condition which will mark 1 batch of seq_id >=0 and SEQID <= 100 Status Column to C

and next batch will start from seq_id > 100 and SEQID <= 200 so on..

Please let me know you have better suggestion to complete this kind of activity...

------------------------------
PRASHANT SHIVMATH
------------------------------

#Db2