- 论坛徽章:
- 11
|
如何查看数据库逻辑日志的记录?
没有很直接的方法!
你可以用onlog去测试,在ids的管理员手册上有关于逻辑日志解释的!
onlog --
==========================
Usage: onlog [-l] [-q] [-b] [-d <tape device>;] [-n <log file number>;]
[-u <user name>;] [-t <TBLspace number>;] [-x <transaction number>;]
-l Display maximum information about each log record
-q Do not display program header
-b Display information about logged BLOB pages (-d option only)
-d Read from tape device
-n Display the specified log(s)
-u Display the specified user(s)
-t Display the specified TBLspace(s)
-x Display the specified transaction(s)
#!/usr/bin/ksh
###############################################################################
#
# Module: onlog1.sh.sh
# Author: Peter R. Schmidt
# Description: Persue the Infomrix logical log
#
# Change Log
#
# Date Name Description.................
# 11/22/00 Peter R. Schmidt Start Program
#
###############################################################################
TMP1=onlog1.tmp1
TMP2=onlog1.tmp2
TMP3=onlog1.tmp3
EXCLUDE="BEGIN|COMMIT|CHALLOC|CKPOINT|BEGCOM|CHFREE"
HEADING1="addr len type xid id link"
rm -f $TMP1
rm -f $TMP2
rm -f $TMP3
echo "The current logical log is:"
onstat -l | grep "U\-\-\-C\-"
LAST=`onstat -l | grep "U\-\-\-C\-" | cut -c26-36`
LAST=`expr $LAST + 0`
echo
echo "The last logical log number is: $LAST"
echo
echo "Enter logical log id to scan, or press <Enter>; to use default of $LAST"
read answer
if [ "${answer}x" = "x" ]
then
LOGID=$LAST
else
LOGID=${answer}
fi
if [ $LOGNAME = informix ]
then
#OPT_LONG="-l"
OPT_LONG=""
else
OPT_LONG=""
fi
while true
do
echo
echo "Enter the name of the Informix table you are interested in (or 'ALL' for all tables):"
read TABLENAME
if [ "${TABLENAME}x" = "x" ]
then
TABLENAME=all
echo "Default to ALL tables"
fi
TABLENAME=`echo $TABLENAME | tr "[A-Z]" "[a-z]"` # Convert to lower case
if [ $TABLENAME = all ]
then
OPT_TABLE=""
SELECT_ROW=0
echo
echo "Unload list of available tables from the database for later reference..."
dbaccess sysmaster <<-EOF
unload to $TMP3
select dbsname, tabname, partnum
from systabnames
order by dbsname, tabname;
EOF
break
fi
if [ "${OSADBNAME}x" = "x" ]
then
echo
echo "Enter Database Name"
read DATABASE
else
DATABASE=$OSADBNAME
fi
echo
echo "Database is: $DATABASE"
dbaccess sysmaster <<-EOF
unload to $TMP1
select partnum from systabnames where dbsname = "$DATABASE" and tabname = "$TABLENAME";
EOF
CNT=`cat $TMP1 | wc -l`
if [ -s $TMP1 -a $CNT = 1 ]
then
PARTNUM=`cut -d"|" -f1 $TMP1`
echo
echo "Table $TABLE partnum = $PARTNUM"
OPT_TABLE="-t $PARTNUM"
echo
echo "Enter specific row ID to select (or <enter>; for all rows)."
read SELECT_ROW
if [ "${SELECT_ROW}x" = "x" ]
then
SELECT_ROW=0
fi
break
else
echo
echo "Invalid table name - please try again!"
continue
fi
done
rm -f $TMP1
echo
echo " ress <Enter>; to continue"
read answer
while true
do
echo
echo "Scanning logical log: $LOGID"
echo
onlog -n $LOGID $OPT_TABLE $OPT_LONG | egrep -v "^$" | grep -v "$HEADING1" | \
awk ' \
{
if (NR == 1) {
if (tablename == "all" {
while ((getline line < tablelistfile) >; 0) {
split (line,a,"|"
a_dbsname=a[1]
a_tablename=a[2]
a_partnum=a[3] "" # Append null to force value to be a string
value=(a_dbsname ":" a_tablename)
# printf "Debug: dbsname=%s, tablename=%s, partnum=%s\n", a_dbsname, a_tablename, a_partnum
# printf "Debug: value=%s\n", value
part_num_array[a_partnum] = value
}
close(tablelistfile)
# For debugging - list entire contects of array
# for (x in part_num_array) {
# printf "key=%s, value=%s\n",x, part_num_array[x]
# }
}
}
}
/^INFORMIX-OnLine Logical Log display/ { next }
/^Software Serial Number/ { next }
/^Copyright \(C\)/ { next }
/^log number:/ { next }
/^Program failed/ {
print "Error: onlog failed to run\n"
exit
}
/BEGIN/ {
BEGIN_ID=$1
BEGIN_DATE=$7
BEGIN_TIME=$8
USER_ID=$10
LAST_ID=BEGIN_ID
next
}
#-------------------------------------------------------------------------------
/HUPDAT/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_ROWID=$8
D_FORWARD_PTR=$9
D_OLD_SLOT=$10
D_NEW_SLOT=$11
D_NUM_PIECES=$12
D_DESC="Home row update"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/BTSHUFFL/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_ROWID=$8
D_LOGICAL_PAGE=$9
D_KEYNUM=$10
D_KEYLEN=$11
D_DESC="Shuffle B-Tree node"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/BTMERGE/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_ROWID=$8
D_LOGICAL_PAGE=$9
D_KEYNUM=$10
D_KEYLEN=$11
D_DESC="Merge B-Tree node"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/ADDITEM/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_ROWID=$8
D_LOGICAL_PAGE=$9
D_KEYNUM=$10
D_KEYLEN=$11
D_DESC="Add item to index"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/PTEXTEND/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_LAST_LOGICAL=$8
D_FIRST_LOGICAL=$8
D_DESC="Extend partition"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/BTSPLIT/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_ROWID=$8
D_PARENT_LOGICAL=$9
D_LEFTLOGICAL=$10
D_RIGHTLOGICAL=$11
D_INFINITY=$12
D_ROOTLEFT=$13
D_MIDSPLIT=$14
D_KEYNUM=$15
D_KEYLEN=$16
D_DESC="Split B-tree node"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/UNIQID/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_UNIQUE_ID=$8
D_DESC="New serial ID"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/CINDEX/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_DESC="Create index"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/BLDCL/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_DESC="Build tblspace"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/PERASE/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_DESC=" re-erase old file"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/ERASE/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_DESC="Drop tblspace"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/HDELETE/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_DESC="Home row delete"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/CLR/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=""
D_DESC=" art of a rollback"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/ROLLBACK/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=""
D_DESC="Rollback work"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/DELITEM/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_DESC="Delete item from index"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
/HINSERT/ {
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_ROWID=$8
D_SLOTNUM=$9
D_DESC="Home row insert"
print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID)
LAST_ID=D_ADDR
next
}
#-------------------------------------------------------------------------------
# skip certain types
#-------------------------------------------------------------------------------
/COMMIT|CHALLOC|CKPOINT|BEGCOM|CHFREE/ { next }
#-------------------------------------------------------------------------------
# Default logic for unregistered types
#-------------------------------------------------------------------------------
{
D_ADDR=$1
D_LEN=$2
D_TYPE=$3
D_XID=$4
D_ID=$5
D_LINK=$6
D_TABLENAME=hex_partnum2tablename($7)
D_ROWID=$8
D_DESC = "UNREGISTERED TYPE"
DECIMAL_ROWID = hex2dec(D_ROWID) # Convert rowid from hex to decimal
STR_ROWID = sprintf ("%d", DECIMAL_ROWID) # Convert to string
if (select_row == 0 || select_row == STR_ROWID) {
if (D_LINK == LAST_ID) {
printf "%-8s ID=%-5s PARENT=%-6s ROW=%-5s %s %s\n", D_TYPE, D_ADDR, D_LINK, STR_ROWID, D_TABLENAME, D_DESC
} else {
printf "%-8s ID=%-5s PARENT=%-6s ROW=%-5s %s %s NEW TRANSACTION\n", D_TYPE, D_ADDR, D_LINK, STR_ROWID, D_TABLENAME, D_DESC
}
}
}
################################################################################
# Display an item - version 1
#
function print_item1(D_TYPE, D_TABLENAME, D_ADDR, D_LINK, D_ROWID, D_DESC, LAST_ID) {
DECIMAL_ROWID = hex2dec(D_ROWID) # Convert rowid from hex to decimal
STR_ROWID = sprintf ("%d", DECIMAL_ROWID) # Convert to string
if (select_row == 0 || select_row == STR_ROWID) {
if (D_LINK == LAST_ID) {
printf "%-8s ID=%-5s PARENT=%-6s ROW=%-8s %s %s %s %s %s\n", D_TYPE, D_ADDR, D_LINK, STR_ROWID, BEGIN_DATE, BEGIN_TIME, USER_ID, D_TABLENAME, D_DESC
} else {
printf "%-8s ID=%-5s PARENT=%-6s ROW=%-8s %s %s %s %s %s NEW TRANSACTION\n", D_TYPE, D_ADDR, D_LINK, STR_ROWID, BEGIN_DATE, BEGIN_TIME, USER_ID, D_TABLENAME, D_DESC
}
}
}
################################################################################
# Convert a hex partition number into a Informix tablename string
#
function hex_partnum2tablename(HEX_PARTNUM) {
if (tablename != "all" {
return tablename
}
DEC_INT_PARTNUM=hex2dec(HEX_PARTNUM)
# printf "Convert %s to %s\n", HEX_PARTNUM, DEC_INT_PARTNUM
STR_PARTNUM = sprintf ("%d", DEC_INT_PARTNUM)
# printf "STR_PARTNUM=%s\n", DEC_STR_PARTNUM
L_TABLENAME=part_num_array[STR_PARTNUM]
# printf "L_TABLENAME=%s\n", L_TABLENAME
if (L_TABLENAME == "" {
L_TABLENAME = " ARTNUM " STR_PARTNUM
}
return L_TABLENAME
}
################################################################################
# returns decimal value of hex string S, or -1 on invalid input
#
function hex2dec(s,n,i,j,h) {
s = tolower(s)
n = length(s)
for (i=1; i<=n; i++) {
j = index("0123456789abcdef", substr(s, i, 1))
if (j == 0)
return -1;
h = h*16 + j-1
}
return h
}
################################################################################
# returns hexstring representation of decimal N
#
function dec2hex(n,hex) {
do {
#hex = substr("0123456789abcdef", n%16+1, 1) hex
hex = substr("0123456789ABCDEF", n%16+1, 1) hex
n = int(n/16)
} while (n)
return hex
}
################################################################################
' tablename=$TABLENAME tablelistfile=$TMP3 select_row=$SELECT_ROW | pg
PREVID=`expr $LOGID \- 1`
NEXTID=`expr $LOGID + 1`
while true
do
echo
echo "Enter < >;revious, <N>;ext (or <Enter> for next, <S>; for same log (or Q to quit)"
read answer
if [ "${answer}x" = "x" ]
then
LOGID=$NEXTID
break
fi
case $answer in
P|p) LOGID=$PREVID; break;;
N|n) LOGID=$NEXTID; break;;
S|s) break;;
*) LOGID=0; break;;
esac
done
if [ $LOGID = 0 ]
then
break
fi
done
rm -f $TMP3 |
|