Hi All,
As per my biz requirement, I need to drop the partition from our PROD table based on the retention period.
Hence I have written the script and called the drop partition procedure within the script.
The procedure is working fine and droped the partition from the table when I execute alone but when I call the procdure from the script itself, paritions are not dropped and throwing error. I really don't have any idea what is the reason for records are not dropped from the script.
Kindly provide your update on this.
Scriptname:
drop_part_w56.sh
#!/bin/ksh
#Batchdate=20170616
#retn=$2
freq='W'
instance=DPHK0
dbuser=etl
dbpass=dummy
path=/CBS/HK/Temp
BatchDate=20170616
logfile=STATUS_w286_tmp_tab_$BatchDate.log
rm -f $path/$logfile
set +x
db2 connect to DPHK0 user etl using dummy
db2cmd=CALL HPPDWSG0.PARTITION_CLEANUP_PROC('SG','$BatchDate','W',?)";
echo ${db2cmd}
db2 +c -tv "${db2cmd}" >> $path/$logfile
db2 terminate
Error:
Database Connection Information
Database server = DB2/AIX64 9.7.8
SQL authorization ID = ETL
Local database alias = DPHK0
/pdw/Singapore/simco/sit/pdwds_drop_part_w286.sh[38]: CALL HPPDWSG0.PARTITION_CLEANUP_PROC('SG','20170616','W',?): not found
DB20000I The TERMINATE command completed successfully.
Did you compare the working directory when "executing alone" to when "calling from script"? And the PATH variable? You might want to use absolute paths.
Thanks for your input. Could you plz share some example.
However, I used the procedure script alone it was successful. Please see the details below.
/pd/SG/uat/EBBS> db2 connect to DPHK0 user etl
Enter current password for etl:
Database Connection Information
Database server = DB2/AIX64 9.7.8
SQL authorization ID = ETL
Local database alias = DPHK0
/pd/SG/uat/EBBS> db2 "CALL HPPDWSG0.PARTITION_CLEANUP_PROC('SG', '20170616','W', ?)";
Value of output parameters
--------------------------
Parameter Name : RESULT_STS
Parameter Value : ALTER TABLE HPPDWSG0.CASA_ACCT_WK DETACH PARTITION P20170303 INTO HPPDWSG0.CAS_ACCT_WK_P20170303_DROP
Not sure what you mean by "share some example". What is the working directory (e.g. output of pwd command) in either case? From the error msg in the first and the command prompt in your second post one could infer they are different; that migt be the reason the script is not found.