Thanks Scott. Looks like someone in development overlooked the q_ses_stats
script in the scripts.smp file for the new time calculation function. Here
is the updated version - it works on my 6.2.1.0 lab server, but I would
appreciate a quick check on your part as my lab does not have much of any
workload. Perhaps you can test it on your TSM server Interestingly, on one
of my customers' it blows up on 6.1.3.1 and 6.2.1.0 on the timestampdiff
function being absent. I would appreciate it if someone can shed some light
on this problem.
Here is the new script:
/* -----------------------------------------*/
/* Script Name: Q_SES_STATS */
/* Description: Display connection time */
/* and send and receive */
/* throughput for active */
/* sessions */
/* Parameter: none */
/* Example: run q_ses_stats */
/* -----------------------------------------*/
select session_id as "Session", -
client_name as "Client", state as "State", -
timestampdiff(2,char(current_timestamp-start_time)) as "Elapsed Time",-
(cast(bytes_sent as decimal(18,0)) / -
cast(timestampdiff(2,current_timestamp-start_time) as -
decimal(18,0))) "Bytes sent/second",-
(cast(bytes_received as decimal(18,0)) / -
cast(timestampdiff(2,current_timestamp-start_time) as -
decimal(18,0))) "Bytes received/second" -
from sessions where -
cast(timestampdiff(2,(current_timestamp-start_time)) -
as decimal) > 0
Here is the error regarding the timestampdiff function:
tsm: TSM01>ru q_ses_stats_v6
Session established with server TSM01: Windows
Server Version 6, Release 1, Level 3.1
Server date/time: 07/06/2010 10:11:19 Last access: 07/06/2010 10:10:44
ANR0162W Supplemental database diagnostic information: -1:42884:-440
([IBM][CLI Driver][DB2/NT64] SQL0440N No authorized routine named
"TIMESTAMPDIFF" of type "FUNCTION" having compatible arguments was found.
SQLSTATE=42884
).
ANR0516E SQL processing for statement select session_id as "Session" ,
client_name as "Client" , state as "State" , timestampdiff ( 2 , char (
current_timestamp-start_time ) ) as "Elapsed Time" , ( cast ( bytes_sent as
decimal ( 18 , 0 ) ) / cast ( timestampdiff ( 2 ,
current_timestamp-start_time
) as decimal ( 18 , 0 ) ) ) "Bytes sent/second" , ( cast ( bytes_received as
decimal ( 18 , 0 ) ) / cast ( timestampdiff ( 2 ,
current_timestamp-start_time
) as decimal ( 18 , 0 ) ) ) "Bytes received/second" from sessions where cast
(
timestampdiff ( 2 , ( current_timestamp-start_time ) ) as decimal ) > 0
failed.
ANR0162W Supplemental database diagnostic information: -1:42884:-440
([IBM][CLI Driver][DB2/NT64] SQL0440N No authorized routine named
"TIMESTAMPDIFF" of type "FUNCTION" having compatible arguments was found.
SQLSTATE=42884
).
ANR0516E SQL processing for statement select session_id as "Session" ,
client_name as "Client" , state as "State" , timestampdiff ( 2 , char (
current_timestamp-start_time ) ) as "Elapsed Time" , ( cast ( bytes_sent as
decimal ( 18 , 0 ) ) / cast ( timestampdiff ( 2 ,
current_timestamp-start_time
) as decimal ( 18 , 0 ) ) ) "Bytes sent/second" , ( cast ( bytes_received as
decimal ( 18 , 0 ) ) / cast ( timestampdiff ( 2 ,
current_timestamp-start_time
) as decimal ( 18 , 0 ) ) ) "Bytes received/second" from sessions where cast
(
timestampdiff ( 2 , ( current_timestamp-start_time ) ) as decimal ) > 0
failed.
ANR1463E RUN: Command script Q_SES_STATS_V6 completed in error.
ANS8001I Return code 4.
tsm: TSM01>
Joerg Pohlmann
250-585-3711
-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
mccambly
Sent: Monday, July 05, 2010 13:57
To: ADSM-L AT VM.MARIST DOT EDU
Subject: [ADSM-L] sample script q_ses_stats on v6 servers produces
unbelievabl
Hi Joerg,
You are running into the problem that the old syntax of (time1-time2)seconds
works but doesn't produce the same results in V6 as in V5.
In V5 it gave the timestamp difference in seconds (perfect) but in V6 it
just gives the difference of the 'seconds portion' of the two timestamps.
IBM did not explain/correct this in their migration documentation until much
later after the V6.1 release.
So now to get the same results you must use the db2 "timestampdiff()"
function like this "timestampdiff(2,char(timestamp1-timestamp2))" where 2 is
the argument to return seconds. google for the full syntax.
Scott.
J. Pohlmann wrote:
> Has anyone experienced and found a fix for the sample script that measures
> session speeds. The sample script q_ses_stats is identical for v5 and v6
and
> runs fine on v 5 and v6 - except that on v6.1 and v6.2 it produces
> unbelievable numbers (100s of MB/sec received or sent).
>
>
>
> Joerg Pohlmann
>
> 250-585-3711
+----------------------------------------------------------------------
|This was sent by mccambly AT unopsys DOT com via Backup Central.
|Forward SPAM to abuse AT backupcentral DOT com.
+----------------------------------------------------------------------
|
