SQWareCentral:db2

From Wiki_dbSQWare
Revision as of 23:53, 17 December 2012 by BatchUser (talk | contribs) (Updated by BatchUser)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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"