Can CLP output be timestamped?

[Carl Castrianni]

When running files containing many sql statements using the db2
command line processor, I`d like to have the output show the time that
each sql statement was executed (so I know when they ran).

Something like:
10:05:00 Select count(*) from .....
10:05:30 Select count(*) from .....

Is there a way to do this? I know db2batch can do this (with SET
TIMESTAMP) but I`d rather use db2 clp.
Thanks!

[Jan M. Nelken]

Carl Castrianni wrote:

When running files containing many sql statements using the db2
command line processor, I`d like to have the output show the time that
each sql statement was executed (so I know when they ran).

Something like:
10:05:00 Select count(*) from .....
10:05:30 Select count(*) from .....

Is there a way to do this? I know db2batch can do this (with SET
TIMESTAMP) but I`d rather use db2 clp.
Thanks!

Can you use simply script like this:

connect to sample;
values current timestamp;
select count(*) from org;
values current timestamp;
select count(*) from staff;
values current timestamp;
connect reset;

Jan M. Nelken

[gogotank]

Yes this it possible.
The most convinient way to do this (for me) is
using the pipe symbol ( | ).
You just run the script from cmdline and redirect (pipe)
its output to some program like tee, etc...

If you running windows you can use my output filter script (.vbs):
save it as .vbs file
'================================================= =========================================
'= outputfilter.vbs
'= special script to capture output from db2 command processor.
'= it can be used as a general output filter (with any console
programs)
'= USAGE (in .cmd file):
'=
'= %comspec% /c script.cmd 2>&1 ^| cscript //nologo outputfilter.vbs
/logfile:script.log
'= output will be echoed to screen and (optionally)
'= saved in script%ISODATE%.log
'================================================= =========================================
Option Explicit
Dim WshShell, StdIn, StdOut, str, outf, outfilename, fso

Set WshShell = WScript.CreateObject("WScript.Shell")

'Set sv-se locale for ISO date/time formatting, example: 2005-03-23
09:13:37
SetLocale "sv-se"

Set StdIn = WScript.StdIn
Set StdOut = WScript.StdOut

set fso = CreateObject("Scripting.FileSystemObject")

outfilename = Cstr(wscript.arguments.named("logfile"))

if len(outfilename)>0 then
set outf = fso.CreateTextFile(outfilename)
else
set outf = nothing
end if

On error resume next
Do While Not StdIn.AtEndOfStream
'replace is needed to remove vbCr symbol
str = Replace(StdIn.ReadLine,vbCr,vbNullstring)
Echo str
Loop

Sub Echo(sMessage)
Dim sMsg
sMsg = Date & " " & Time & " " & sMessage
stdout.writeline sMsg
CheckError "Write to StdOut"
if not outf is nothing then
outf.writeline sMsg
CheckError "Write to output file"
end if
End Sub
Sub CheckError(sMessage)
Dim errNum,errDesc,errSource, errMessage

if err.number<>0 then
errNum = err.Number
errDesc = err.description
errSource = err.source
errMessage = "Error occured. " & sMessage & _
"#" & errNum & "(0x" & errnum & "), " & errDesc & _
", source " & errSource

on error goto 0
WshShell.LogEvent 1,errMessage
Wscript.echo errMessage
end if
End Sub

[Gert van der Kooij]

In article <qj********************************@4ax.com>, Carl
Castrianni (fl***********@comcast.net) says...

When running files containing many sql statements using the db2
command line processor, I`d like to have the output show the time that
each sql statement was executed (so I know when they ran).

Something like:
10:05:00 Select count(*) from .....
10:05:30 Select count(*) from .....

Is there a way to do this? I know db2batch can do this (with SET
TIMESTAMP) but I`d rather use db2 clp.
Thanks!

db2 select current time, count(*) from ....

[Carl Castrianni]

Thank you very much for the vbs script. That does what I needed.

I didn't want to have to edit all of my sql files to accomplish this.

Much thanks!
On 6 Jul 2005 23:22:07 -0700, "gogotank" <go******@yandex.ru> wrote:

Yes this it possible.
The most convinient way to do this (for me) is
using the pipe symbol ( | ).
You just run the script from cmdline and redirect (pipe)
its output to some program like tee, etc...

If you running windows you can use my output filter script (.vbs):
save it as .vbs file
'================================================ ==========================================
'= outputfilter.vbs
'= special script to capture output from db2 command processor.
'= it can be used as a general output filter (with any console
programs)
'= USAGE (in .cmd file):
'=
'= %comspec% /c script.cmd 2>&1 ^| cscript //nologo outputfilter.vbs
/logfile:script.log
'= output will be echoed to screen and (optionally)
'= saved in script%ISODATE%.log
'================================================ ==========================================
Option Explicit
Dim WshShell, StdIn, StdOut, str, outf, outfilename, fso

Set WshShell = WScript.CreateObject("WScript.Shell")

'Set sv-se locale for ISO date/time formatting, example: 2005-03-23
09:13:37
SetLocale "sv-se"

Set StdIn = WScript.StdIn
Set StdOut = WScript.StdOut

set fso = CreateObject("Scripting.FileSystemObject")

outfilename = Cstr(wscript.arguments.named("logfile"))

if len(outfilename)>0 then
set outf = fso.CreateTextFile(outfilename)
else
set outf = nothing
end if

On error resume next
Do While Not StdIn.AtEndOfStream
'replace is needed to remove vbCr symbol
str = Replace(StdIn.ReadLine,vbCr,vbNullstring)
Echo str
Loop

Sub Echo(sMessage)
Dim sMsg
sMsg = Date & " " & Time & " " & sMessage
stdout.writeline sMsg
CheckError "Write to StdOut"
if not outf is nothing then
outf.writeline sMsg
CheckError "Write to output file"
end if
End Sub
Sub CheckError(sMessage)
Dim errNum,errDesc,errSource, errMessage

if err.number<>0 then
errNum = err.Number
errDesc = err.description
errSource = err.source
errMessage = "Error occured. " & sMessage & _
"#" & errNum & "(0x" & errnum & "), " & errDesc & _
", source " & errSource

on error goto 0
WshShell.LogEvent 1,errMessage
Wscript.echo errMessage
end if
End Sub

[gogotank]

You welcome.
Actually i found that i'm using different script.
Is much smaller and include error detection.
It is usable for interactive script execution

'db2outputfilter.vbs
Option Explicit
SetLocale "sv-se"

Dim StdIn, StdOut, str, bWasError,sMsgId

Set StdIn = WScript.StdIn
Set StdOut = WScript.StdOut

Do While Not StdIn.AtEndOfStream
'str = StdIn.ReadLine
str = Replace(StdIn.ReadLine,vbCr,vbNullstring)
if left(str,3)="SQL" then
sMsgId = Trim(Left(str,9))
if Right(sMsgId,1) = "N" then
bWasError = TRUE
MsgBox str, vbOkOnly + vbCritical, "Db2 error"
end if
end if

StdOut.WriteLine Date & " " & Time & " " & str
Loop
If bWasError then
wscript.quit 1
end if