DB2 Script for Compiled Procedures from yesterday

It is always good to keep a track or log of all the activities that were going on in the DB server .

So What is the idea

Keep a track of all the compiled procedures from the last day

• A Copy of  log on the server and
• Mail the daily report to your Id with Cronjob utility

So that when somebody in your team or your manager ask you anytime this question you need not login the server and execute the query , You  have it in your inbox everyday.

This script help's you to maintain the log of all the proc's compiled for the day ,

	#!/bin/sh
	if [ /home/db2inst1/sqllib/db2profile ]; then
	. /home/db2inst1/sqllib/db2profile
	fi
	echo “Procedures Compiled for the DB-NAME on `date +%Y-%m-%d`”
	#Change the DB_NAME to your respective database
	/home/db2inst1/sqllib/bin/db2 connect to DB_NAME> /dev/null
	/home/db2inst1/sqllib/bin/
	db2 "select substr(PROCSCHEMA,1,5)||'.'||substr(PROCNAME,1,40),CREATE_TIME from SYSCAT.PROCEDURES where date(CREATE_TIME) > date('`date +%m/%d/%Y -d "1 days ago"`') with UR" 2>&1 | tee /home/ftpusr/backup/AWDRT_Proc_log.out
	/home/db2inst1/sqllib/bin/db2 commit work > /dev/null
	/home/db2inst1/sqllib/bin/db2 connect reset > /dev/null

view raw ProcsComplied.sh hosted with ❤ by GitHub