Merge Command eg

 MERGE INTO LIQUIDITY.REP_STG_PRODUCT_DATA STG USING
          (Select VALID_TO ,PRODUCT FROM LIQUIDITY.REP_PRODUCT_DATA SCD WHERE SCD.VALID_TO > ? AND SCD.VALID_FROM <=?  ) ad
          on STG.PRODUCT =ad.PRODUCT
 WHEN MATCHED
  THEN
  UPDATE SET (STG.PROCESSED,STG.VALID_TO)=('D',ad.VALID_TO)

  ELSE IGNORE;
  --


MERGE INTO LMFR_REPORT.REF_PRODUCT_DATA  R
 USING
          (SELECT    ms.VALUE2 , fp.SK_FP
          FROM LMFR_REPORT.R_REF_FUNDING_POOL fp
                    inner join LIQUIDITY.LIST_MEMBER_SNAPSHOT ms
                    on ms.LIST_ID = 43 and ms.VALUE1 = fp.SECURITY_POOL and ms.DATA_DATE = '2013-01-31'
) ad
       
                 on ad.SK_FP = R.SK_FP   AND R.VALID_FROM <= '2013-01-31'  AND R.VALID_TO > '2013-01-31'
       AND R.FED_CATEGORY != ms.VALUE2
                  AND ((R.FED_CATEGORY = 'A-II' AND VALUE2='A-I')
                  OR R.FED_CATEGORY IN ('B','D'))
                   AND R.PRODUCT_S_P_LT_RATING IS NULL
                   AND R.PRODUCT_MOODY_LT_RATING IS NULL
                   AND R.PRODUCT_S_P_LT_RATING IS NULL
                   AND R.PRODUCT_HIERARCHY_LEVEL_2 in ( 'DO NOT POST', 'Government')
 WHEN MATCHED
  THEN
  UPDATE SET (R.FED_CATEGORY, R.BASEL_CATEGORY)=(AD.VALUE2, CASE WHEN AD.VALUE2= 'A-I' THEN '0% risk - issued by sovereigns'
WHEN AD.VALUE2='A-II' THEN '20% risk - issued by sovereigns' ELSE 'greater 20% risk - issued by sovereigns' END )
ELSE IGNORE;

Shell Script tips

====================
How to get execution time bw 2 statements in a shell script:
====================
t1=$( perl -e "print time")
sleep 10
t2=$( perl -e "print time")
timeConsumed=$(($t2-t1))

echo "time taken : $timeConsumed"



====================
find and delete files older then 10 days
====================
find subdebt*.dat.Z -mtime +00 -exec ls -lrt  {} \;
find *.* -mtime +10 -exec rm -f {} \;

====================

use of awk command

====================

tabname=ENH_BAAC

rows_deleted=10323244

enh_age=56

echo TABLE_NAME ROWS_DELETED  ENH_AGE | awk '{printf "%-30s %-20s %-20s \n",$1,$2,$3 }' >test

echo $tabname $rows_deleted $enh_age | awk '{printf "%-30s %-20s %-20s \n",$1,$2,$3 }' >>test

tabname=ENH_LETTERS_OF_CREDIT

rows_deleted=0

enh_age=98

echo $tabname $rows_deleted $enh_age | awk '{printf "%-30s %-20s %-20s \n",$1,$2,$3 }' >>test

cat test

====================

Extension of file

====================

ls  /v/region/na/appl/corptsy/lmfr/data/qa/sourced/brm_repo/History/20120730.20120803.174759.dat.Z | read file

echo ${file##*.}

file="thisfile.yogesh.txt"

echo "filename: ${file%.*}"

echo "extension: ${file##*.}"

====================

For loop

====================

for  i in 2 3 4 5 6

do

  export DB2NODE=$i

  db2 terminate

  db3 connect to nypd_lmfr

  db2 load query table liquidity.ETL_UK_MORtGAGES

done

====================

If Else Stmt

====================

PREV_TABNAME=dummy1

if [ "${PREV_TABNAME}" != "dummy" ]; then

    echo $PREV_TABNAME

else 

    echo "incor"

fi

====================

Cut Command: to get 4th col with delimeter as |

====================

cut -d "|" -f-4 test

How to get explain plan

Below qry will give you explain plan for a qry:
Make sure the "Org_SL" file has qry and has semi colon at the end.

db2expln -d DBNAME -f Org_SL -z \; -t -g -o Org_SL.out

Below qry will give you explain plan for a procedure.
db2expln -d DBNAME -package P6472415 -schema SCHEMANAME -g -o PROC.out

How to delete old partitions from Range partition table

Below qry will give you all the partitions older then certain date. this can be used to drop partitions

SELECT TABSCHEMA,TABNAME,DATAPARTITIONNAME,LOWVALUE,DAYNAME(LOWVALUE) DAY_NAME,DAYOFWEEK(LOWVALUE)DAY_OF_WEEK ,
       TRIM(CHAR(DAY(LOWVALUE)))||'_'||monthname(LOWVALUE) MONTH_ENDS
FROM
(
select TABSCHEMA,TABNAME,DATAPARTITIONNAME, DATE(REPLACE(LOWVALUE,'''','')) AS LOWVALUE from syscat.datapartitions
where tabname ='TABNAME'
AND LOWVALUE NOT IN ('MINVALUE','MAXVALUE')
)DT
WHERE LOWVALUE < DATE(date('2012-08-01') - 90 days)
AND DAYOFWEEK(LOWVALUE) != 6
with ur;

-- Qry to drop parition leaving weekend and monthend.
select
        'db2ts detach table partition -dbname NYPD_LMFR  -schema '||dp.TABSCHEMA||' -name '||TABNAME||' -partition '||DATAPARTITIONNAME||' -cleanup drop -cmrs batch -mailto lmfr_dba'
from
    syscat.datapartitions  dp

where
    dp.HIGHVALUE != 'MAXVALUE'
    AND TABNAME='HUB_PENDING_PART'
    AND  date(REPLACE(HIGHVALUE,'''',''))  < current date -3 months
--Month end
AND (date(REPLACE(HIGHVALUE,'''','')) != date(REPLACE(HIGHVALUE,'''','')) + 1 days - day(date(REPLACE(HIGHVALUE,'''',''))) days
--Weekend
 OR DAYNAME(date(REPLACE(HIGHVALUE,'''','')) )!='Friday')
with ur    ;