Useful WCS Script Examples

WebSphere Commerce - Some useful script

1. Script to find Applications connection count (Script File Name - db_connections.sh)

RUNDAY=`date`
OUTPUT_DATE=`date '+%d%m%y'`
export RUNDAY
export OUTPUT_DATE

echo `date` >> /home/dbuser/DB_CONNECTIONS/DB_DATABASENAME_CONN$OUTPUT_DATE.txt;
db2 list applications for database <DATABASENAME> | wc -l  >> /home/dbuser/DB_CONNECTIONS/DB_dbuser_CONN$OUTPUT_DATE.txt;
echo "##############################################" >> /home/dbuser/DB_CONNECTIONS/DB_dbuser_CONN$OUTPUT_DATE.txt;
echo "##############################################" >> /home/dbuser/DB_CONNECTIONS/DB_dbuser_CONN$OUTPUT_DATE.txt;
------------------------------------------------------------------------------------------------------------------------
2. Getting the DATA from DATABASE using script

#!/bin/bash
cd /home/wcuser/SolutionsOfIssues/
rm -f OrderTotalMismatchIssue.sh.2017*
lockfile=/home/wcuser/SolutionsOfIssues/$(basename $0).lock
LOG=/home/wcuser/SolutionsOfIssues/$(basename $0).$(date +%Y%m%d%H%M%S)
# Open STDOUT and STDERR as $LOG
CSV="/home/wcuser/SolutionsOfIssues/OutputFile.csv"
exec 1>$LOG 2>&1
(
flock -x -w 10 200 || exit 1

rm -f $CSV

cmd="EXPORT TO $CSV OF DEL MODIFIED BY NOCHARDEL select e.order_id, count(*) from edppayinst ep, edporder e where e.edporder_id=ep.edporder_id and ep.mARKFORDELETE=0 and ep.PAYMENTSYSTEM != 'StoreCredit' and e.order_id in (select orders_id from orders where status='P' ) group by e.order_id having count(*)>1 order by e.order_id"

db2 <<EOF
connect to <databaseName>  user <dbuser> using <dbpassword>
$cmd
quit
EOF
exit $?
)  200>$lockfile
exit $?

------------------------------------------------------------------------------------------------------------------------
3. Getting the DATA from DB into CSV File

#!/bin/bash
if [ -f ~/.bash_profile ]; then
        . ~/.bash_profile
fi

lockfile=/tmp/$(basename $0).lock
LOG=/tmp/$(basename $0).$(date +%Y%m%d%H%M%S)
CSV="/home/dbuser/CatalogData/catentry.csv"
CSV="/tmp/catentry.csv"
# Open STDOUT and STDERR as $LOG
exec 1>$LOG 2>&1

(
flock -x -w 10 200 || exit 1
rm -f $CSV
cmd="EXPORT TO $CSV OF DEL MODIFIED BY NOCHARDEL COLDEL| \
select partnumber as DUMMY_ARTICLE, catentry_id from catentry where markfordelete=0 and buyable=1"

db2 <<EOF
connect to <dbname>  user <dbusername> using <dbpassword>
$cmd
quit
EOF
exit $?
)  200>$lockfile
sed -i 1i"Partnumber|Catentry_id" $CSV #header of file
exit $?

-----------------------------------------------------------------------------------------------------------------------

4. Files Backup using Script

#!/bin/bash
if [ -f ~/.bash_profile ]; then
        . ~/.bash_profile
fi

aa=$(date +%Y%m%d%H%M%S)
lockfile=/tmp/$(basename $0).lock
LOG=/tmp/$(basename $0).${aa}

# Open STDOUT and STDERR as $LOG
exec 1>$LOG 2>&1
(
        flock -x -w 10 200 || exit 1
        # Rename file with time stamp
        cp -f /home/wcuser/Dataload/12_Inventory_DOM/non_ATP_inventory_for_esite.csv /home/wcuser/Dataload/12_Inventory_DOM/non_ATP_inventory_for_esite_${aa}.csv
cd /opt/IBM/WebSphere/CommerceServer70/bin
        ./dataload.sh /home/wcuser/Dataload/12_Inventory_DOM/wc-dataload-for-esite.xml
        if [ $? -ne 0 ]
        then
                echo "Error: Inventory dataload failed"
                exit 1
        fi

)  200>$lockfile
exit $?
-----------------------------------------------------------------------------------------------------------------------
5. Get the space information using script

df -kh > 10.10.10.1_disk-space.txt
cat /root/10.10.10.1_disk-space.txt |  mailx -s "Disk space- 10.10.10.1" ramaswamy.karra@abc.com
-----------------------------------------------------------------------------------------------------------------------
6.  DB Backup Script
#!/bin/bash

. $HOME/sqllib/db2profile

BACKUPPATH=/BKP/DB_BKP/

#############Performing an Online Backup of the Database ########################

db2 -v "backup database <DBName> online to $BACKUPPATH compress  include logs"

########################End of "online_backup_script.sh"#############################

-----------------------------------------------------------------------------------------------------------------------
7. Removing Archive logs more than 40 days
#! /bin/bash

. $HOME/sqllib/db2profile

# Remove old archive files from database, files older than 40 days will be deleted # Look for local databases

ARCHIVELOC="/db2/archival_logs/db2inst/RILPDB/NODE0*"

#Remove all archive logs greater than 40 days

find $ARCHIVELOC -name '*.LOG' -ctime +40 -exec rm {} \;

-----------------------------------------------------------------------------------------------------------------------