PROC_LOCK
# -----------------------------------------------------------------------------------------------#
# Detalhe: lock table user #
# Autor : Claudemar Martins de Sa #
# Data : 14/04/2012 #
# Fonte : http://www.vivaolinux.com.br/script/Menu-tarefas-com-SSH #
# -----------------------------------------------------------------------------------------------#
TABELAS_EM_LOCK () {
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n " "
echo -n " ***** INFORMIX BASIC SERVICES ***** "
echo -n " "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo " $(hostname) - $(whoami)"
echo
echo " Code Function "
echo " ---- ------------------------------------------- "
echo " 1 Tabelas em Lock - Simples "
echo " 2 Tabelas em Lock - Completo "
echo " 3 Tabelas em Lock - Resumido "
echo " 4 Tabelas em Lock - Banco "
echo " 5 Tabelas em Lock - Banco e Usuario "
echo " 6 Usuarios com Maiores Locks "
echo " 0 Sair da Aplicacao "
echo " ---- ------------------------------------------- "
echo
echo -n " Digite um Codigo - "
read OPCAO3
case $OPCAO3 in
1) TABELAS_LOCK_SIMPLES ;;
2) TABELAS_LOCK_COMPLETO ;;
3) TABELAS_LOCKS_RESUMIDO ;;
4) TABELAS_LOCK_COMPLETO_POR_BANCO ;;
5) TABELAS_LOCK_COMPLETO_POR_USUARIO ;;
6) USUARIOS_COM_MAIORES_LOCKS ;;
0) clear ; exit ;;
*) Opcao Invalida ; echo ; TABELAS_EM_LOCK ;;
esac
}
# ======================================================================================================
# ======================================================================================================
USUARIOS_COM_MAIORES_LOCKS ()
{
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n " "
echo -n " ***** INFORMIX BASIC SERVICES ***** "
echo -n " "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo
echo "Threads address flags sessid user tty wait tout locks nreads nwrites"
echo "------- -------- ------- -------- -------- -------- ---------------- ---- ----- -------- -------"
echo "$(onstat -u | sort -nrk 8 |head -n 29)"
echo
echo
echo -n "Pressione qualquer tecla para continuar..."
read MSG
TABELAS_EM_LOCK
}
# ======================================================================================================
# ======================================================================================================
TABELAS_LOCK_SIMPLES ()
{
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n " "
echo -n " ***** INFORMIX BASIC SERVICES ***** "
echo -n " "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo
echo "Processos que Estao Aguardando Algum Lock"
echo
echo "select dbsname, b.tabname, rowidr, keynum, e.txt type, d.sid owner, g.username ownername, f.sid waiter, h.username waitname from syslcktab a, systabnames b, systxptab c, sysrstcb d, sysscblst g, flags_text e, sysrstcb f , sysscblst h where a.partnum = b.partnum and a.owner = c.address and c.owner = d.address and a.wtlist = f.address and d.sid = g.sid and e.tabname = 'syslcktab' and e.flags = a.type and f.sid = h.sid into temp A;
select dbsname, tabname, type[1,4], owner, ownername, waitname from A;
drop table A;"|dbaccess sysmaster
echo -n "Pressione qualquer tecla para continuar..."
echo
read MSG
clear
TABELAS_EM_LOCK
}
# ======================================================================================================
# ======================================================================================================
# ======================================================================================================
TABELAS_LOCK_COMPLETO ()
{
LOG1="/tmp/1234_pode_deletar_1234_0.log"
LOG2="/tmp/1234_pode_deletar_1234_1.log"
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n " "
echo -n " ***** INFORMIX BASIC SERVICES ***** "
echo -n " "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo
echo " +---------------------------------------------------------+"
echo " | |"
echo " | aguarde alguns segundos ... |"
echo " | |"
echo " +---------------------------------------------------------+"
echo
dbaccess sysmaster > /dev/null <<+
unload to $LOG1
select owner, username, hostname, dbsname, tabname, type
from sysmaster:syssessions s, sysmaster:syslocks l
where sid = owner --- AND (dbsname = 'nfeletronica') -- OR username = 'nfeletronica')
AND tabname not like "sys%";
+
cat $LOG1 | awk -F "|" '{
t_iso=$1
if ($1 == "|")
{ t_iso=" " }
printf("| %-9s | %-20s | %-14s | %-14s | %-40s | \n",$1,$2,$3,$4,$5)}' > $LOG2
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n " "
echo -n " ***** INFORMIX BASIC SERVICES ***** "
echo -n " "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo
echo "+-----------+----------------------+----------------+----------------+------------------------------------------+"
echo "| Processo | User Name | Hostname | dbsname | table/Index Name |"
echo "+-----------+----------------------+----------------+----------------+------------------------------------------+"
cat $LOG2 | more
echo "+-----------+----------------------+----------------+----------------+------------------------------------------+"
rm $LOG1
rm $LOG2
echo
echo -n "Pressione qualquer tecla para sair... "
read MSG
clear
TABELAS_EM_LOCK
}
# ======================================================================================================
# ======================================================================================================
# ======================================================================================================
TABELAS_LOCKS_RESUMIDO ()
{
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n " "
echo -n " ***** INFORMIX BASIC SERVICES ***** "
echo -n " "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo
#################################################################################
# Utility to Print lock status
# Written By - Prasad Mahale
# Date - 05/04/1999
# Usage - locks.sh
# Friends, This utility may not be 100% correct. Feel free to modify this
# according to your requirement.
# If you find anything wrong in the logic of script or you have better version
# of this utility or just even to send comments or suggestions, mail me at
# pmahale@yahoo.com.
# For sample output of this script, visit www.geocities.com/pmahale/inf/tools
#
#################################################################################
dbaccess sysmaster 2> /dev/null <<+
unload to locks.out
select a.dbsname, a.tabname, a.rowidlk, a.type, a.owner, b.username
from syslocks a, syssessions b
where a.owner = b.sid
order by a.owner;
+
cat locks.out | awk -F "|" '
{
t_llevel=$3
if (length($3)<7)
{ t_llevel="ROW" }
if ($3 == "0")
{ t_llevel="TABLE" }
if (substr($3,length($3)-1,2) == "00")
{ t_llevel="PAGE" }
if (length($3)>6)
{ t_llevel="IDX KEY" }
t_ltype=$4
if ($4 == "B")
{ t_ltype="BYTES" }
if ($4 == "S")
{ t_ltype="SHRED" }
if ($4 == "X")
{ t_ltype="EXCLV" }
if ($4 == "I")
{ t_ltype="INTNT" }
if ($4 == "U")
{ t_ltype="UPDAT" }
if ($4 == "IX")
{ t_ltype="INT-EX" }
if ($4 == "IS")
{ t_ltype="INT-SHR" }
if ($4 == "SIX")
{ t_ltype="SHR-INT-EX" }
print($5"|"$6"|"$1"|"$2"|"t_llevel"|"t_ltype"|")}' > locks1.out
dbaccess sysmaster 2> /dev/null <<+
create temp table t_lloocckk (session integer, usernm char(15), db_name
char(15)
, tb_name char(20),level char(10), type char(15));
load from locks1.out insert into t_lloocckk;
unload to locks.out
select FIRST 15 session, usernm, db_name, tb_name, level, type, count(*)
from t_lloocckk
group by 1,2,3,4,5,6
order by 7 desc;
drop table t_lloocckk;
+
echo "Lock usage report Total locks in use" `
onstat -k | tail -2 | grep active | awk '{print $1, "out of", $3}'`
echo "-------------------------------------------------------------------------------"
echo "SESSION OWNER DATABASE TABLE LEVEL TYPE LOCKS"
echo "-------------------------------------------------------------------------------"
cat locks.out | awk -F "|" '{
printf("%8d %-13s %-10s %-18s %-7s %-10s %-6d\n",$1,$2,$3,$4,$5,$6,$7)
}'
rm locks.out
rm locks1.out
echo
################################################################################
# Utility to Print sessions waiting for the lock
# Written By - Prasad Mahale
# Date - 05/02/1999
# Usage - userlock.sh
# Friends, This utility may not be 100% correct. Feel free to modify this
# according to your requirement.
# If you find anything wrong in the logic of script or you have better version
# of this utility or just even to send comments or suggestions, mail me at
# pmahale@yahoo.com.
# For sample output of this script, visit www.geocities.com/pmahale/inf/tools
#
################################################################################
dbaccess sysmaster 2>/dev/null <<+
unload to userlock.out
select a.us_name, a.us_sid,
c.us_name, c.us_sid
from sysuserthreads a, syslocktab b, sysuserthreads c
where b.lk_addr = a.us_lkwait
and b.lk_owner= c.us_txp;
+
echo "Lista de usuarios que estao aguardando algum lock"
echo "-------------------------------------------------------------------------"
echo "USER SESSION USER SESSION"
echo "-------------------------------------------------------------------------"
cat userlock.out | awk -F "|" '{
printf("%-10s %10s waiting to release lock from %-10s %10s\n",$1,$2,$3,$4)
}'
rm userlock.out
echo
echo
echo
echo -n "Pressione qualquer tecla para continuar..."
read MSG
TABELAS_EM_LOCK
}
# ======================================================================================================
# ======================================================================================================
TABELAS_LOCK_COMPLETO_POR_BANCO ()
{
LOG1="/tmp/1234_pode_deletar_1234_0.log"
LOG2="/tmp/1234_pode_deletar_1234_1.log"
clear
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n " "
echo -n " ***** INFORMIX BASIC SERVICES ***** "
echo -n " "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo " BACKUP DE TABELAS "
echo
echo
echo -n "Digite o NOME DO BANCO DE DADOS .....: "
read DATABASE
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n " "
echo -n " ***** INFORMIX BASIC SERVICES ***** "
echo -n " "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo
echo " +---------------------------------------------------------+"
echo " | |"
echo " | aguarde alguns segundos ... |"
echo " | |"
echo " +---------------------------------------------------------+"
echo
dbaccess sysmaster > /dev/null <<+
unload to $LOG1
select owner, username, hostname, dbsname, tabname, type
from sysmaster:syssessions s, sysmaster:syslocks l
where sid = owner AND (dbsname = '${DATABASE}') -- OR username = 'nfeletronica')
AND tabname not like "sys%";
+
cat $LOG1 | awk -F "|" '{
t_iso=$1
if ($1 == "|")
{ t_iso=" " }
printf("| %-9s | %-20s | %-14s | %-14s | %-40s | \n",$1,$2,$3,$4,$5)}' > $LOG2
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n " "
echo -n " ***** INFORMIX BASIC SERVICES ***** "
echo -n " "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo
echo "+-----------+----------------------+----------------+----------------+------------------------------------------+"
echo "| Processo | User Name | Hostname | dbsname | table/Index Name |"
echo "+-----------+----------------------+----------------+----------------+------------------------------------------+"
cat $LOG2 | more
echo "+-----------+----------------------+----------------+----------------+------------------------------------------+"
rm $LOG1
rm $LOG2
echo
echo -n "Pressione qualquer tecla para sair... "
read MSG
clear
TABELAS_EM_LOCK
}
# ======================================================================================================
# ======================================================================================================
# ======================================================================================================
TABELAS_LOCK_COMPLETO_POR_USUARIO ()
{
LOG1="/tmp/1234_pode_deletar_1234_0.log"
LOG2="/tmp/1234_pode_deletar_1234_1.log"
clear
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n " "
echo -n " ***** INFORMIX BASIC SERVICES ***** "
echo -n " "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo " BACKUP DE TABELAS "
echo
echo
echo -n "Digite o NOME DO BANCO DE DADOS .....: "
read DATABASE
echo -n "Digite o NOME DO USUARIO ............: "
read USER
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n " "
echo -n " ***** INFORMIX BASIC SERVICES ***** "
echo -n " "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo
echo " +---------------------------------------------------------+"
echo " | |"
echo " | aguarde alguns segundos ... |"
echo " | |"
echo " +---------------------------------------------------------+"
echo
dbaccess sysmaster > /dev/null <<+
unload to $LOG1
select owner, username, hostname, dbsname, tabname, type
from sysmaster:syssessions s, sysmaster:syslocks l
where sid = owner AND (dbsname = '${DATABASE}') -- OR username = '${USER}')
AND tabname not like "sys%";
+
cat $LOG1 | awk -F "|" '{
t_iso=$1
if ($1 == "|")
{ t_iso=" " }
printf("| %-9s | %-20s | %-14s | %-14s | %-40s | \n",$1,$2,$3,$4,$5)}' > $LOG2
clear
echo "-------------------------------------------------------------------------------------------------"
echo -n
echo -n "$(date "+%d/%m/%Y")"
echo -n " "
echo -n " ***** INFORMIX BASIC SERVICES ***** "
echo -n " "
echo "$(date "+%T")"
echo "-------------------------------------------------------------------------------------------------"
echo
echo "+-----------+----------------------+----------------+----------------+------------------------------------------+"
echo "| Processo | User Name | Hostname | dbsname | table/Index Name |"
echo "+-----------+----------------------+----------------+----------------+------------------------------------------+"
cat $LOG2 | more
echo "+-----------+----------------------+----------------+----------------+------------------------------------------+"
rm $LOG1
rm $LOG2
echo
echo -n "Pressione qualquer tecla para sair... "
read MSG
clear
TABELAS_EM_LOCK
}
# ======================================================================================================
# Nao remover a linha abaixo
# ======================================================================================================
TABELAS_EM_LOCK
0 comentários:
Enviar um comentário