SQWareCentral:db2

From Wiki_dbSQWare
Revision as of 22:53, 17 December 2012 by BatchUser (talk | contribs) (Updated by BatchUser)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

auto

sqwdb2_AdddbSQWareProfile.ksh

Action script $gvsqw_RootexpdbCentral/action/db2/auto/sqwdb2_AdddbSQWareProfile.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/db2/auto_cust/sqwdb2_AdddbSQWareProfile.ksh.
Add dbSQWare profile

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
lvsqw_Instance=$(echo "$lvsqw_Instance"|cut -d':' -f1)
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
if [ -r ~/.profile ]
then
	if [ \$(grep -c 'gvsqw_Db2Bin' ~/.profile) -eq 0 ]
	then
		cat <<EOFCAT >>~/.profile

#dbSQWare
export gvsqw_Env=''
export gvsqw_Db2Bin=\\\$HOME/SQWareProduction/db2/bin
lvsqw_IsTerminal=\\\$(tty 2>&1 >/dev/null;echo \\\$?)
if [ "\\\$lvsqw_IsTerminal" = "0" ] && [ -r \\\$gvsqw_Db2Bin/../etc/.profile_confort ]
then
	. \\\$gvsqw_Db2Bin/../etc/.profile_confort
fi

EOFCAT
	fi
elif [ -r ~/.bash_profile ]
then
	if [ \$(grep -c 'gvsqw_Db2Bin' ~/.bash_profile) -eq 0 ]
	then
		cat <<EOFCAT >>~/.bash_profile

#dbSQWare
export gvsqw_Env=''
export gvsqw_Db2Bin=\\\$HOME/SQWareProduction/db2/bin
lvsqw_IsTerminal=\\\$(tty 2>&1 >/dev/null;echo \\\$?)
if [ "\\\$lvsqw_IsTerminal" = "0" ] && [ -r \\\$gvsqw_Db2Bin/../etc/.profile_confort ]
then
	. \\\$gvsqw_Db2Bin/../etc/.profile_confort
fi

EOFCAT
	fi
fi
EOFSSH
done <$gvsqw_TmpFile.lst

}


sqwdb2_DeplScripts.ksh

Action script $gvsqw_RootexpdbCentral/action/db2/auto/sqwdb2_DeplScripts.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/db2/auto_cust/sqwdb2_DeplScripts.ksh.
SQWareProduction deployment for Db2

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
mkdir -p $gvsqw_RootSQWareProductionDb2/$gvsqw_VersDeplSQWareProductionDb2
cd $gvsqw_RootSQWareProductionDb2
rm -f $gvsqw_VersSQWareProductionDb2
ln -s $gvsqw_VersDeplSQWareProductionDb2 $gvsqw_VersSQWareProductionDb2
EOFSSH
rsync -aHv $gvsqw_RsyncPathDb2 --delete $gvsqw_RootSQWareProduction/db2/$gvsqw_VersDeplSQWareProductionDb2 $lvsqw_UserName@$lvsqw_HostName:$gvsqw_RootSQWareProductionDb2/.
done <$gvsqw_TmpFile.lst
}


sqwdb2_GatherIndicatorsDist.ksh

Action script $gvsqw_RootexpdbCentral/action/db2/auto/sqwdb2_GatherIndicatorsDist.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/db2/auto_cust/sqwdb2_GatherIndicatorsDist.ksh.
Db2 gather indicators in distant mode

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh $gvsqw_SshOptions $gvsqw_DistSQWareProdUser_db2 <<EOFSSH &
\$gvsqw_Db2Bin/sqwdb2_GatherIndicators.ksh -I "$lvsqw_Instance" -Dist
EOFSSH
sleep 1
done <$gvsqw_TmpFile.lst
jobs
wait
}


sqwdb2_GatherIndicators.ksh

Action script $gvsqw_RootexpdbCentral/action/db2/auto/sqwdb2_GatherIndicators.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/db2/auto_cust/sqwdb2_GatherIndicators.ksh.
Db2 gather indicators

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH &
. \$gvsqw_Db2Bin/../tools/sqwdb2_SetEnv.ksh $lvsqw_Instance
\$gvsqw_Db2Bin/sqwdb2_RunJob.ksh -I $lvsqw_Instance -A IndicDba
EOFSSH
sleep 1
done <$gvsqw_TmpFile.lst
jobs
wait
}


sqwdb2_Gen_dbSQWare.ksh

Action script $gvsqw_RootexpdbCentral/action/db2/auto/sqwdb2_Gen_dbSQWare.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/db2/auto_cust/sqwdb2_Gen_dbSQWare.ksh.
generate dbSQWare files

Action: 

lfsqw_Action()
{
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
lvsqw_Instance=$(echo "$lvsqw_Instance"|cut -d':' -f1)
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName $lvsqw_Instance${c_normal}"
ssh $gvsqw_SshOptions $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
. \$gvsqw_Db2Bin/../tools/sqwdb2_SetEnv.ksh $lvsqw_Instance
\$gvsqw_Db2Bin/../tools/sqwdb2_GenerateCreateInstance.ksh -dbsOnly <<EOF
y
EOF
EOFSSH
done <$gvsqw_TmpFile.lst
}


sqwdb2_GenSshKeysInstance.ksh

Action script $gvsqw_RootexpdbCentral/action/db2/auto/sqwdb2_GenSshKeysInstance.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/db2/auto_cust/sqwdb2_GenSshKeysInstance.ksh.
ssh keys deployment for Db2

Action: 

lfsqw_Action()
{

echo "#########################################################################"
echo "#Create/Retrieve key for SQWareCentral"
mkdir -p $HOME/tmp
if [ -r $HOME/.ssh/id_dsa.pub ]
then
	echo "Id DSA found:"
	ls -l $HOME/.ssh/id_dsa.pub
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_dsa.pub)
elif [ -r $HOME/.ssh/id_rsa.pub ]
then
	echo "Id RSA found:"
	ls -l $HOME/.ssh/id_rsa.pub
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
else
	ssh-keygen -t rsa -N '' -f $HOME/.ssh/id_rsa
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
fi
#Retrieve additionnal keys if found (your windows key for exemple)
if [ -r $HOME/.ssh/id_dbSQWare.pub ]
then
	echo "Id dbSQWare found:"
	ls -l $HOME/.ssh/id_dbSQWare.pub
	lvsqw_AdditionnalKey=$(cat $HOME/.ssh/id_dbSQWare.pub)
else
	lvsqw_AdditionnalKey=""
fi
chmod go-w $HOME
touch $HOME/.ssh/authorized_keys
chmod 700 $HOME/.ssh
chmod 600 $HOME/.ssh/authorized_keys
echo "${c_menu}#########################################################################"
echo "#create key if not found and put SQWareCentral key for distant users"
echo "Enter password when necessary${c_normal}"
while read lvsqw_HostName lvsqw_UserName lvsqw_Instance
do
echo ""
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
ssh -o StrictHostKeyChecking=no $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
mkdir -p \$HOME/tmp
if [ -r \$HOME/.ssh/id_dsa.pub ]
then
	echo "Id DSA found:"
	ls -l \$HOME/.ssh/id_dsa.pub
	cat \$HOME/.ssh/id_dsa.pub
elif [ -r \$HOME/.ssh/id_rsa.pub ]
then
	echo "Id RSA found:"
	ls -l \$HOME/.ssh/id_rsa.pub
	cat \$HOME/.ssh/id_rsa.pub
else
	ssh-keygen -t rsa -N '' -f \$HOME/.ssh/id_rsa
	cat \$HOME/.ssh/id_rsa.pub
fi
chmod go-w \$HOME
echo "$lvsqw_CentralKey" >>\$HOME/.ssh/authorized_keys
echo "$lvsqw_AdditionnalKey" >>\$HOME/.ssh/authorized_keys
chmod 700 \$HOME/.ssh
chmod 600 \$HOME/.ssh/authorized_keys
EOFSSH

done <$gvsqw_TmpFile.lst
}


sqwdb2_GenSshKeys.ksh

Action script $gvsqw_RootexpdbCentral/action/db2/auto/sqwdb2_GenSshKeys.ksh:
Can be replaced by $gvsqw_RootexpdbCentral/action/db2/auto_cust/sqwdb2_GenSshKeys.ksh.
ssh keys deployment for Db2

Action: 

lfsqw_Action()
{

echo "#########################################################################"
echo "#Create/Retrieve key for SQWareCentral"
mkdir -p $HOME/tmp
if [ -r $HOME/.ssh/id_dsa.pub ]
then
	echo "Id DSA found:"
	ls -l $HOME/.ssh/id_dsa.pub
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_dsa.pub)
elif [ -r $HOME/.ssh/id_rsa.pub ]
then
	echo "Id RSA found:"
	ls -l $HOME/.ssh/id_rsa.pub
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
else
	ssh-keygen -t rsa -N '' -f $HOME/.ssh/id_rsa
	lvsqw_CentralKey=$(cat $HOME/.ssh/id_rsa.pub)
fi
#Retrieve additionnal keys if found (your windows key for exemple)
if [ -r $HOME/.ssh/id_dbSQWare.pub ]
then
	echo "Id dbSQWare found:"
	ls -l $HOME/.ssh/id_dbSQWare.pub
	lvsqw_AdditionnalKey=$(cat $HOME/.ssh/id_dbSQWare.pub)
else
	lvsqw_AdditionnalKey=""
fi
chmod go-w $HOME
touch $HOME/.ssh/authorized_keys
chmod 700 $HOME/.ssh
chmod 600 $HOME/.ssh/authorized_keys
echo "${c_menu}#########################################################################"
echo "#create key if not found and put SQWareCentral key for distant users"
echo "Enter password when necessary${c_normal}"
while read lvsqw_HostName lvsqw_UserName
do
echo ""
echo "${c_info}$lvsqw_UserName@$lvsqw_HostName${c_normal}"
ssh -o StrictHostKeyChecking=no $lvsqw_UserName@$lvsqw_HostName <<EOFSSH
mkdir -p \$HOME/tmp
if [ -r \$HOME/.ssh/id_dsa.pub ]
then
	echo "Id DSA found:"
	ls -l \$HOME/.ssh/id_dsa.pub
	cat \$HOME/.ssh/id_dsa.pub
elif [ -r \$HOME/.ssh/id_rsa.pub ]
then
	echo "Id RSA found:"
	ls -l \$HOME/.ssh/id_rsa.pub
	cat \$HOME/.ssh/id_rsa.pub
else
	ssh-keygen -t rsa -N '' -f \$HOME/.ssh/id_rsa
	cat \$HOME/.ssh/id_rsa.pub
fi
chmod go-w \$HOME
echo "$lvsqw_CentralKey" >>\$HOME/.ssh/authorized_keys
echo "$lvsqw_AdditionnalKey" >>\$HOME/.ssh/authorized_keys
chmod 700 \$HOME/.ssh
chmod 600 \$HOME/.ssh/authorized_keys
EOFSSH

done <$gvsqw_TmpFile.lst
}




etc

CrontabRef.cfg

Configuration file $gvsqw_RootexpdbCentral/action/db2/etc/CrontabRef.cfg:
It contains the operating variables of SQWareCentral for db2.
If you want to overload default variables,
put in $gvsqw_RootexpdbCentral/action/db2/etc_cust/CrontabRef.cfg
your modifications (only variables you want to modify).

File content: 







Variable explanation:










sqwc_DbRefVar.cfg


Configuration file $gvsqw_RootexpdbCentral/action/db2/etc/sqwc_DbRefVar.cfg:

It contains the operating variables of SQWareCentral for db2.

If you want to overload default variables,

put in $gvsqw_RootexpdbCentral/action/db2/etc_cust/sqwc_DbRefVar.cfg

your modifications (only variables you want to modify).



File content:



export gvsqw_DbRefType_db2='db2'
export gvsqw_DbRefPort_db2='NotUsedForDb2'
export gvsqw_DbRefInstance_db2='InstanceNameForDb2(Alias for connexion)'
export gvsqw_DbRefDatabase_db2='DatabaseInstanceNameForDb2(schema of SQWareRepository'
export gvsqw_DbRefUser_db2='ConnexionUserForDb2'
export gvsqw_DbRefPass_db2='ConnexionPassForDb2'







Variable explanation:



gvsqw_DbRefDatabase_db2 => 
gvsqw_DbRefInstance_db2 => 
gvsqw_DbRefPass_db2 => 
gvsqw_DbRefPort_db2 => 
gvsqw_DbRefType_db2 => 
gvsqw_DbRefUser_db2 => 








sqwc_GlobalVar.cfg


Configuration file $gvsqw_RootexpdbCentral/action/db2/etc/sqwc_GlobalVar.cfg:

It contains the operating variables of SQWareCentral for db2.

If you want to overload default variables,

put in $gvsqw_RootexpdbCentral/action/db2/etc_cust/sqwc_GlobalVar.cfg

your modifications (only variables you want to modify).



File content:



export gvsqw_Mail_db2='default@dbsqware.com'
export gvsqw_MailMsg_db2='TrtDB2Auto'
export gvsqw_Db2ErrorMsg='^SQL[0-9][0-9]*'
export gvsqw_Db2ErrorMsgWarning='^SQL[0-9][0-9]*W'
export gvsqw_UserUniqueDb2='dbsqware'
export gvsqw_RootSQWareProductionDb2="~$gvsqw_UserUniqueDb2/SQWareProduction/db2"
export gvsqw_VersSQWareProductionDb2='prod'
export gvsqw_VersDeplSQWareProductionDb2='v3.1'
export gvsqw_DistSQWareProdUser_db2="$gvsqw_UserUniqueDb2@$gvsqw_Hostname"







Variable explanation:



gvsqw_Db2ErrorMsg => 
gvsqw_Db2ErrorMsgWarning => 
gvsqw_DistSQWareProdUser_db2 => 
gvsqw_Mail_db2 => 
gvsqw_MailMsg_db2 => 
gvsqw_RootSQWareProductionDb2 => 
gvsqw_UserUniqueDb2 => 
gvsqw_VersDeplSQWareProductionDb2 => 
gvsqw_VersSQWareProductionDb2 => 








.profile_confort


File to put aliases $gvsqw_RootexpdbCentral/action/db2/etc/.profile_confort:

Contains confort aliases of SQWareCentral for RDBMS db2.



Alias explanation:



	  act_db2 => 'cd $gvsqw_RootexpdbCentral/action/db2/prod;ls -al'
	  etc_db2 => 'cd $gvsqw_RootexpdbCentral/action/db2/etc;ls -al'
	  lst_db2 => 'cd $gvsqw_RootexpdbCentral/action/db2/lst;ls -al'
	  auto_db2 => 'cd $gvsqw_RootexpdbCentral/action/db2/auto;ls -al'
	  cetc_db2 => 'cd $gvsqw_RootexpdbCentral/action/db2/etc_cust;ls -al'
	  clst_db2 => 'cd $gvsqw_RootexpdbCentral/action/db2/lst_cust;ls -al'
	  cauto_db2 => 'cd $gvsqw_RootexpdbCentral/action/db2/auto_cust;ls -al'
	  depl_db2 => '$gvsqw_RootexpdbCentral/menu/sqwc_MenuAction.ksh db2 sqwdb2_DeplScripts.ksh sqwdb2_GenLstUnique.ksh'
	  repind_db2 => '$gvsqw_RootexpdbCentral/bin/sqwc_TrtAuto.ksh db2 sqwdb2_GatherIndicators.ksh sqwdb2_GenLstRepind.ksh NOLOG'
	  repind_db2_dist => '$gvsqw_RootexpdbCentral/bin/sqwc_TrtAuto.ksh db2 sqwdb2_GatherIndicatorsDist.ksh sqwdb2_GenLstRepindDist.ksh NOLOG'
	  gen_db2 => '$gvsqw_RootexpdbCentral/action/db2/lst/sqwc_GenRefDb2.ksh'
	  menu_db2 => '$gvsqw_RootexpdbCentral/menu/sqwc_MenuAction.ksh db2'











lst


sqwc_Extract_CMDB_Db2.ksh


List generator $gvsqw_RootexpdbCentral/action/db2/lst/sqwc_Extract_CMDB_Db2.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/db2/lst_cust/sqwc_Extract_CMDB_Db2.ksh.




Query used:



select 'DB2;'||ref.db2_sid||';'||coalesce(hst.host_name,'N.A')||';'||
from tsqw_Repository ref left outer join tsqw_GenHisto hist
        on (ref.db2_sid = hist.db2_sid and hist.month = trunc(current timestamp, 'MONTH'))
order by 1








sqwc_GenRefDb2.ksh


List generator $gvsqw_RootexpdbCentral/action/db2/lst/sqwc_GenRefDb2.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/db2/lst_cust/sqwc_GenRefDb2.ksh.




Query used:



select cast(virt_host_name as char(30)) as Virthost,
from tsqw_Repository
select cast(virt_host_name as char(30)) as Virthost,
from tsqw_RepositoryOther
order by 6,2,1,3,4








sqwc_GenRefDbDb2.ksh


List generator $gvsqw_RootexpdbCentral/action/db2/lst/sqwc_GenRefDbDb2.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/db2/lst_cust/sqwc_GenRefDbDb2.ksh.




Query used:



select 'DB2;'||ref.db2_sid||';'||vol.database_name||';'||ref.env||';'||
from tsqw_Repository ref, tsqw_GenHisto hist, tsqw_VolInstance inst, tsqw_VolDb vol
where hist.month = trunc(current timestamp, 'MONTH')
    and hist.db2_sid = ref.db2_sid
    and hist.db2_sid = vol.db2_sid
    and hist.gather_date = vol.gather_date
    and hist.db2_sid = inst.db2_sid
    and hist.gather_date = inst.gather_date
order by 1








sqwdb2_GenLstInstanceDist.ksh


List generator $gvsqw_RootexpdbCentral/action/db2/lst/sqwdb2_GenLstInstanceDist.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/db2/lst_cust/sqwdb2_GenLstInstanceDist.ksh.

Generation of the list of Db2 instances for distant connection (tsqw_Repository)



Query used:



select virt_host_name, username, db2_sid
from tsqw_Repository
where status  in ('DIST','MIX')
order by virt_host_name, username








sqwdb2_GenLstInstance.ksh


List generator $gvsqw_RootexpdbCentral/action/db2/lst/sqwdb2_GenLstInstance.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/db2/lst_cust/sqwdb2_GenLstInstance.ksh.

Generation of the list of Db2 instances (tsqw_Repository)



Query used:



select virt_host_name, username, db2_sid
from tsqw_Repository
where status  in ('ON','MIX')
order by virt_host_name, username








sqwdb2_GenLstInstanceNew.ksh


List generator $gvsqw_RootexpdbCentral/action/db2/lst/sqwdb2_GenLstInstanceNew.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/db2/lst_cust/sqwdb2_GenLstInstanceNew.ksh.

Generation of the list of Db2 instances (tsqw_Repository) status 'NEW'



Query used:



select virt_host_name, username, db2_sid
from tsqw_Repository
where status  = 'NEW'
order by virt_host_name, username








sqwdb2_GenLstRepindDist.ksh


List generator $gvsqw_RootexpdbCentral/action/db2/lst/sqwdb2_GenLstRepindDist.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/db2/lst_cust/sqwdb2_GenLstRepindDist.ksh.

Generation of the list for reprise of indicators for distant connection (tsqw_Repository/tsqw_GenHisto)



Query used:



select virt_host_name, username, db2_sid
from tsqw_Repository ref
where ref.status in ('DIST','MIX')
and not exists ( select 1 from tsqw_GenHisto inf
where inf.gather_date = current date
and ref.db2_sid = inf.db2_sid)








sqwdb2_GenLstRepind.ksh


List generator $gvsqw_RootexpdbCentral/action/db2/lst/sqwdb2_GenLstRepind.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/db2/lst_cust/sqwdb2_GenLstRepind.ksh.

Generation of the list for reprise of indicators (tsqw_Repository/tsqw_GenHisto)



Query used:



select virt_host_name, username, db2_sid
from tsqw_Repository ref
where ref.status in ('ON','MIX')
and not exists ( select 1 from tsqw_GenHisto inf
where inf.gather_date = current date
and ref.db2_sid = inf.db2_sid)








sqwdb2_GenLstUnique.ksh


List generator $gvsqw_RootexpdbCentral/action/db2/lst/sqwdb2_GenLstUnique.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/db2/lst_cust/sqwdb2_GenLstUnique.ksh.

Generation of the user unique list (tsqw_Repository)



Query used:



select distinct host_name, ' $gvsqw_UserUniqueDb2'
from $gvsqw_DbRefDatabase_db2.tsqw_Repository
where status  in ('ON','MIX')
select distinct host_name, ' $gvsqw_UserUniqueDb2'
from $gvsqw_DbRefDatabase_db2.tsqw_RepositoryOther
order by host_name








sqwdb2_GenLstUniqueNew.ksh


List generator $gvsqw_RootexpdbCentral/action/db2/lst/sqwdb2_GenLstUniqueNew.ksh:

Can be replaced by $gvsqw_RootexpdbCentral/action/db2/lst_cust/sqwdb2_GenLstUniqueNew.ksh.

Generation of the user unique list (tsqw_Repository) statut 'NEW'



Query used:



select distinct host_name, '$gvsqw_UserUniqueDb2'
from tsqw_Repository
where status = 'NEW'
order by host_name
















					

						Retrieved from "https://wiki.dbsqware.com/index.php?title=SQWareCentral:db2&oldid=3530"