Delete Script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Delete Script
# 1  
Old 08-26-2013
Delete Script

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.

Code:
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

Code:
 
#!/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

Last edited by Don Cragun; 08-26-2013 at 11:11 PM.. Reason: Add more CODE tags and remove confusing FONT settings.
# 2  
Old 08-26-2013
with this sample:

Code:
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  
Old 08-27-2013
Deleting from second table with keycolumn as a parameter

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  
Old 08-27-2013
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)
Code:
 
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

Last edited by Prasad Rao; 08-27-2013 at 10:54 AM..
This User Gave Thanks to Prasad Rao For This Post:
# 5  
Old 08-27-2013
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:-

Code:
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:-


Code:
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:-

Code:
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:-
Code:
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.
Moderator's Comments:
Mod Comment Please use CODE tags; not font changes. Not using CODE tags hides spacing in sample code, input, and output. Please help us help you by using CODE tags.

Last edited by Don Cragun; 08-27-2013 at 09:02 PM.. Reason: Add CODE tags; remove font tags.
# 6  
Old 08-28-2013
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.

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

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

SFTP Shell Script Get & Delete && Upload & Delete

Hi All, Do you have any sample script, - auto get file from SFTP remote server and delete file in remove server after downloaded. - only download specify filename - auto upload file from local to SFTP remote server and delete local folder file after uploaded - only upload specify filename ... (3 Replies)
Discussion started by: weesiong
3 Replies

2. Shell Programming and Scripting

Script needed to delete to the list of files in a directory based on last created & delete them

Hi My directory structure is as below. dir1, dir2, dir3 I have the list of files to be deleted in the below path as below. /staging/retain_for_2years/Cleanup/log $ ls -lrt total 0 drwxr-xr-x 2 nobody nobody 256 Mar 01 16:15 01-MAR-2015_SPDBS2 drwxr-xr-x 2 root ... (2 Replies)
Discussion started by: prasadn
2 Replies

3. Shell Programming and Scripting

Files delete script

Friends, I had written a small script to delete files from deletefiles.txt file. However, I want to add one more piece to script, so as to check if the file(file abc) which was already deleted earlier exists in 'deletefiles.txt' file, script should comment out that "file abc doesnt exist". Can... (5 Replies)
Discussion started by: fop4658
5 Replies

4. Red Hat

Need Script to ZIP/SAVE & then DELETE Log file & DELETE ZIPS older than 12 months

ENVIROMENT Linux: Fedora Core release 1 (Yarrow) iPlanet: iPlanet-WebServer-Enterprise/6.0SP1 Log Path: /usr/iplanet/servers/https-company/logs I have iPlanet log rotation enabled rotating files on a daily basis. The rotated logs are NOT compressed & are taking up too much space. I... (7 Replies)
Discussion started by: zachs
7 Replies

5. UNIX for Dummies Questions & Answers

script to delete old files

Hi, I want to delete files that are older than 60 days.... i need to execute the script in 7 differnt folders.... i can run the script in crontab to regularly check.... I am struck @ finding out how the file is 60 days old or not... Can u please help me on this? Thanks, NithZ (6 Replies)
Discussion started by: Nithz
6 Replies

6. Shell Programming and Scripting

Script to delete old directories

Hi, I have a requirement like, I need to create the directory with date and time stap (i.e YYYYMMDDHMS) every day end need to delete the old directories which is 12 months old. I have tested with following script cd /export/home/sbeeravo/; find . -type d -mtime +365 -exec rm -rf {} \; ... (2 Replies)
Discussion started by: ShirishaReddy
2 Replies

7. Linux

Delete script

Hi I need a script that will remove the contents out of a common named folder on our server and all its contents. We have folder called temp which resides within several different folders and i need to delete the contents at a set time in the night thanks Treds (7 Replies)
Discussion started by: treds
7 Replies

8. Shell Programming and Scripting

script to delete one digit.

Hi User, I have a text file with a lot of customer records (over 10,000). Each record contained one field called "charge" and it must start with some space (each record may have 15 space, 17 spece, 9 space,etc, then start with <charge> and end with </charge>, in between is a value. How can I... (3 Replies)
Discussion started by: happyv
3 Replies

9. Shell Programming and Scripting

perl script to check if empty files are created and delete them and run a shell script

I have a local linux machine in which the files are dumped by a remote ubuntu server. If the process in remote server has any problem then empty files are created in local machine. Is there any way using perl script to check if the empty files are being created and delete them and then run a shell... (2 Replies)
Discussion started by: hussa1n
2 Replies

10. Shell Programming and Scripting

Please (Delete script)

Hello everybody. Actually I would like to ask you who can I make delete commend by shell script to delete some files from anywhere in my director and send it to specific place but whit the PATH because if want to restore it will return to the original place which I delete it from (As recycle pan... (7 Replies)
Discussion started by: falm
7 Replies
Login or Register to Ask a Question