Delete Script

Shell Programming and Scripting
1

I 'm trying to write a unix script with delete statement on DB2 tables based on the date.The issue I have is that I need to delete the records on 2 tables but only one table have date field and based on another key I have to delete the related records.

Table 1:-
DateID
KeyColumn
Field 1
Field 2
Table 2:-
KeyColumn
Field 2
Field 3

This is the delete statement I can execute to delete from Table 1 but not sure how to delete the related records from Table 2 using Unix script

 
#!/bin/ksh
echo "  " >> ${LOG};
  echo "End of Job" >> ${LOG};
  echo "${SCRIPTNUM} - ${TTL} - ${STS}" >> ${LOG};
  date >> ${LOG};
  echo "${SCRIPTNUM} - ${TTL} - ${STS}" >> ${EOJ};
  date >> ${EOJ};
  if [ "${STS}" != "Completed successfully" ];then
    echo "See ${EOJ} for job status information" >> ${LOG}
    echo "See ${LOG} for more information" >> ${EOJ}
      fi ;
  echo " " >> ${EOJ};
}
 
#--------------------------------------------------------------------------#
#--  Connecting to DWH                                        --#
#--------------------------------------------------------------------------#
sqlrc=`db2 -ec +o "connect to ${ALIAS} user ${FDW_UID} using ${FDW_PWD}"`
if [ "${sqlrc}" = "0" ];then
  echo "connect to DWH tables ${ALIAS} successful" >> ${LOG}
else
  echo "Connect to DWH tables ${ALIAS} failed, SQLCODE = ${sqlrc}" >> ${LOG}
  closelog
  exit 16
fi
 
delete from table1 where date_id < currentdate - 6 months

#--------------------------------------------------------------------------#
#--  Reset Database Connection on Successful Completion of Job         --#
#--------------------------------------------------------------------------#
echo "Reset database connection" >> ${LOG}
db2 connect reset >> ${LOG}
echo " " >> ${LOG}
STS="Completed successfully"
exit 0

Anyhelp is greatly appreciated

2

with this sample:

Table 1:-
DateID
KeyColumn
Field 1
Field 2

Table 2:-
KeyColumn
Field 2
Field 3

from DateID, I can find out the keyColumn, then is keyColumn unique that I can use to identify which record need be deleted?

3

I can find out the common records but I need to pass these records as parameters when deleting from second table.And I can expect more than several 1000 records at a time. So using unix script how will I be able to pass these 1000 records in unix script?

4

You may delete the records in Table2 first where the common columns in two Tables are matched and condition on date field of first table is satisfied.

(I'm not sure with the DB2 would accept exactly the same, but u can try something like this)

 
delete table2 from table1, table2 
where 
table1.KeyColumn = table2.KeyColumn
table1.Field 2 = table2.Field 2
table1.date_id < currentdate - 6 months

After this table1 records may be deleted with the date_id condition

1 Like
5

I guess that will solve the issue.

Thank you

---------- Post updated at 05:05 PM ---------- Previous update was at 09:16 AM ----------

I found another issue which I didn't realize earlier.

  • I have to first identify the records greater than 6 months in table1.
  • Based on keycolumn value if there are any other records with a different date id in Table 1 then no deletion is required in Table2.
  • Incase if there are no records in Table 1 on a different date based on the keycolumn value then the related columns in table 2 based on keycolumn value should be deleted Joining on the key column.

Scenario 1
Table 1 Values:-

ROWNO          DateID      KeyColumn  Field 1    Field 2  
1                  2/25/2013           1          ABC          XYZ 
2                  8/26/2014           2          DEF          XYZ 
3                  8/25/2014           1         ABC           XYZ 
4                  8/26/2015           3         DEF           XYZ

Table 2:-

ROWNO             KeyColumn       Field 3       Field 4  
1                           1               ABCC         XXYZ 
2                           2               DDEF          XYZ 
3                           3               ABBC          XYZZ 
4                           4                DEFF         XYYZ

Since Keycolumn 1 have rows on both 2/25 & 8/25 in Table 1 there is no delete required in Table 2.

Scenario 2

Table 1 Values:-

ROWNO          DateID      KeyColumn  Field 1    Field 2  
1                  2/25/2013           1          ABC          XYZ 
2                  8/26/2014           2          DEF          XYZ 
3                  8/25/2014           4         ABC           XYZ 
4                  8/26/2015           3         DEF           XYZ

Table 2:-

ROWNO             KeyColumn  Field 1    Field 2  
1                            1               ABCC          XXYZ 
2                            2               DDEF          XYZ 
3                           3                ABBC           XYZZ
4                           4              DEFF           XYYZ

Since Keycolumn 1 have only one row on 2/25 in Table 1 the corresponding record in Table2 ROWNO 1 should be deleted.

Any help is greatly appreciated.

6

Though this is not about Unix shell scripting, here is my proposed solution.
It's completely a logic that you have to work using the sql functions available.
You can do it by taking max value of date in a sub quey.

 
delete from table2  
where KeyColumn in
(select KeyColumn from table1
group by KeyColumn  having max(DateId)< currentdate - 6 months)