I wrote a utility that scans a replication log file from Sybase, it searches for "MISSING","FAILED", and "ILLEGAL." It also scans the file and dumps to three text files where Insert,Update, and Delete statements occur and parses the table name. Next, it loads a text file containing just table names and counts 1 at a time per output file to make up the totals. Just to load the 81MB log files takes 10 minutes on NT (with notepad). I have all this happening below in less than 5 minutes. Any suggestions for improvements?
Thanks,
Brad
[This message has been edited by Bradley Callis (edited June 04, 2000).]
Thanks,
Brad
Code:
FUNCTION PBMAIN()
DIM t AS SINGLE
DIM a$
DIM x$
DIM f$
DIM i$
DIM m$
DIM d$
DIM u$
DIM count AS DOUBLE
DIM fcount AS DOUBLE
DIM icount AS DOUBLE
DIM mcount AS DOUBLE
DIM ucount AS DOUBLE
DIM dcount AS DOUBLE
DIM incount AS DOUBLE
count = 0
fcount = 0
icount = 0
mcount = 0
ucount = 0
dcount = 0
incount = 0
t = TIMER
u$ = "UPDATE"
d$ = "DELETE FROM"
x$ = "INSERT INTO"
f$ = "FAILED"
i$ = "MISSING"
m$ = "ILLEGAL"
OPEN "c:\temp\qatarmsg.log" FOR INPUT AS #1 LEN = 16384
OPEN "c:\temp\insert.tmp" FOR OUTPUT AS #2 LEN = 16384
OPEN "c:\temp\delete.tmp" FOR OUTPUT AS #3 LEN = 16384
OPEN "c:\temp\update.tmp" FOR OUTPUT AS #4 LEN = 16384
DO WHILE NOT EOF(1)
INPUT #1, a$
'REGEXPR x$ IN a$ TO where&, length&
'IF length& THEN
IF INSTR(1,a$,x$)>0 THEN
'count = count + 1
WRITE# 2, MID$(a$,12,INSTR(1,a$,CHR$(40)) -12) & CHR$(33)
END IF
IF INSTR(1,a$,d$)>0 THEN
'count = count + 1
WRITE# 3, MID$(a$,12,INSTR(1,a$,CHR$(40)) -12) & CHR$(33)
END IF
IF INSTR(1,a$,u$)>0 THEN
'count = count + 1
WRITE# 4, MID$(a$,7,INSTR(1,a$,CHR$(40)) -7) & CHR$(33)
END IF
IF INSTR(1,a$,f$)>0 THEN
fcount = fcount + 1
END IF
IF INSTR(1,a$,m$)>0 THEN
mcount = mcount + 1
END IF
IF INSTR(1,a$,i$)>0 THEN
icount = icount + 1
END IF
LOOP
CLOSE 1
CLOSE 2
CLOSE 3
CLOSE 4
OPEN "c:\temp\tables.txt" FOR INPUT AS #1 LEN = 16384
OPEN "c:\temp\rep.log" FOR OUTPUT AS #2 LEN = 16384
WRITE# 2, "Replication Log for " & DATE$
WRITE# 2, "-----------------------------------------------------"
WRITE# 2, ""
WRITE# 2, "Failed= " & STR$(fcount) & " Missing= " & STR$(mcount) & " Illegal= " & STR$(icount)
WRITE# 2, ""
WRITE# 2, "-----------------------------------------------------"
WRITE# 2, ""
WRITE# 2, "Tables with deleted items"
WRITE# 2, "-----------------------------------------------------"
DO WHILE NOT EOF(1)
INPUT #1, a$
a$ = REMOVE$(a$,"'")
a$ = a$ & CHR$(33)
OPEN "c:\temp\delete.tmp" FOR INPUT AS #3 LEN = 16384
DO WHILE NOT EOF(3)
INPUT #3, d$
IF INSTR(1,d$,a$)>0 THEN
dcount = dcount + 1
END IF
LOOP
IF dcount > 1 THEN
WRITE# 2, REMOVE$(a$,CHR$(33)) & STR$(dcount)
END IF
dcount = 0
CLOSE 3
LOOP
CLOSE 1
OPEN "c:\temp\tables.txt" FOR INPUT AS #1 LEN = 16384
WRITE# 2, ""
WRITE# 2, "Tables with inserted items"
WRITE# 2, "-----------------------------------------------------"
DO WHILE NOT EOF(1)
INPUT #1, a$
a$ = REMOVE$(a$,"'")
a$ = a$ & CHR$(33)
OPEN "c:\temp\insert.tmp" FOR INPUT AS #3 LEN = 16384
DO WHILE NOT EOF(3)
INPUT #3, d$
IF INSTR(1,d$,a$)>0 THEN
icount = icount + 1
END IF
LOOP
IF icount > 1 THEN
WRITE# 2, REMOVE$(a$,CHR$(33)) & STR$(icount)
END IF
icount = 0
CLOSE 3
LOOP
CLOSE 1
OPEN "c:\temp\tables.txt" FOR INPUT AS #1 LEN = 16384
WRITE# 2, ""
WRITE# 2, "Tables with updated items"
WRITE# 2, "-----------------------------------------------------"
DO WHILE NOT EOF(1)
INPUT #1, a$
a$ = REMOVE$(a$,"'")
a$ = a$ & CHR$(33)
OPEN "c:\temp\update.tmp" FOR INPUT AS #3 LEN = 16384
DO WHILE NOT EOF(3)
INPUT #3, d$
IF INSTR(1,d$,a$)>0 THEN
ucount = ucount + 1
END IF
LOOP
IF ucount > 1 THEN
WRITE# 2, REMOVE$(a$,CHR$(33)) & STR$(ucount)
END IF
ucount = 0
CLOSE 3
LOOP
CLOSE 1
CLOSE 2
t = TIMER - t
END FUNCTION
Maybe a more effective way to handle this would be to use SQL Tools to write a program that reads the transaction logs and creates a new database. Then you could use all of your existing reporting tools to analyze the transactions logs. (I bet you could even sell a few copies of a program like that.)
Comment