By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,611 Members | 1,711 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,611 IT Pros & Developers. It's quick & easy.

Can CLP output be timestamped?

P: n/a
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!
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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 ....
Nov 12 '05 #4

P: n/a
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


Nov 12 '05 #5

P: n/a
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

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.