DB2 query hours difference

[Irfan Mughal]

i need ur help.

i want to have CPU utilization of last 24 hrs. for time being i want
to have result from 1 server.

select "AVG_%_Total_Pr ocessor_Time", "WRITETIME" , "MAX_
%_Total_Process or_Time", "MIN_%_Total_Pr ocessor_Time", "AVG_
%_Total_Privile ged_Time" , "AVG_%_Total_Us er_Time",
"AVG_Context_Sw itches/Sec", "AVG_Total_Inte rrupts/Sec"
from "NT_System_ HV"
where "Server_Nam e" = 'Primary:ITM6VM :NT' and "WRITETIME" between
'00000000000000 00' and'99999999999 99999'

query working fine till where server name but i am confuse how to
utilize and get result from between clause and get last 24 hrs result.

i want this query for BIRT report design and itm6.2.


pls tell me how to achieve the result.

[Tonkuma]

Re: DB2 query hours difference

What is the data type(and format, if data type is not TIMESTAMP) of
"WRITETIME" ?

If the data type is TIMESTAMP, this might be an answer:
"WRITETIME" between CURRENT TIMESTAMP - 24 HOURs
and CURRENT TIMESTAMP

By the way, waht is "BIRT report design and itm6.2"?

[Irfan Mughal]

Re: DB2 query hours difference

On Jun 20, 9:31 pm, Tonkuma <tonk...@fiberb it.netwrote:

What is the data type(and format, if data type is not TIMESTAMP) of
"WRITETIME" ?
>
If the data type is TIMESTAMP, this might be an answer:
"WRITETIME" between CURRENT TIMESTAMP - 24 HOURs
                and CURRENT TIMESTAMP
>
By the way, waht is "BIRT report design and itm6.2"?

thanks for ur reply . data type of "WRITETIME" is 16 character. data
is store like that 108210640000000 0.

BIRT is open source tool for report designing. and itm is ibm product
for system health monitoring.

pls provide the query . thanks

[Tonkuma]

Re: DB2 query hours difference

data type of "WRITETIME" is 16 character.

data is store like that 108210640000000 0.

How to interprit this 16 character string?
It looks like not including yyyy,mm,dd, so on.
It may be time duration.
If so, from when and what is a timeunit(milise conds? microseconds? or
other?) of the duration?

[Lennart]

Re: DB2 query hours difference

On Jun 23, 8:34 am, Irfan Mughal <iffi....@gmail .comwrote:
[...]

thanks for ur reply . data type of "WRITETIME" is 16 character. data
is store like that 108210640000000 0.
>

What is it, microseconds since 1970-01-01 00:00:00 UTC? In that case,
how about:

select ... from ... where timestamp('1970-01-01-00.00.00.0') +
(10821064000000 00 / 1000000) seconds (current_timest amp - 24 hours)

/Lennart

[...]

[The Boss]

Re: DB2 query hours difference

Lennart wrote:

On Jun 23, 8:34 am, Irfan Mughal <iffi....@gmail .comwrote:
[...]

>thanks for ur reply . data type of "WRITETIME" is 16 character. data
>is store like that 108210640000000 0.
>>

>
What is it, microseconds since 1970-01-01 00:00:00 UTC? In that case,
how about:
>
select ... from ... where timestamp('1970-01-01-00.00.00.0') +
(10821064000000 00 / 1000000) seconds (current_timest amp - 24 hours)
>
/Lennart
>
[...]

It is a typical format for Tivoli, I guess, and definitely not "microsecon ds
since ..."
The example given by OP seems to be not valid, however (see below).

I found the following Redbook "Tivoli Management Services Warehouse and
Reporting":

http://www.redbooks.ibm.com/redbooks/pdfs/sg247290.pdf

which should be useful for OP. Be aware, though, it is a 9Mb download.
Here is some relevant snippets form a chapter on using Crystal Reports:

------------------------------
[Page 459:]
7.2.5 Report creation: Disk Usage
In this section, we create a sample report titled Disk Usage.
<description of some steps from the Crystal Reports wizard>
------------------------------

------------------------------
[Page 461, step 3 from the wizard:]
3. Select the grouping. In this case, we want the data to be grouped by
System_Name, by LAT_Mount_Point , and then by WRITETIME, as shown in Figure
7-36.
We also have to convert the WRITETIME field to a readable format. We do this
later when we finish this wizard.
------------------------------

------------------------------
[Page 463, step 5 from the wizard:]
5. At this point, we create an SQL Expression Field to convert WRITETIME to
a readable format.
To convert WRITETIME field, see "Converting TIMESTAMP field" on page 448.
------------------------------

------------------------------
[Page 449/450, the relevant part of above mentioned paragraph:]
3. Select SQL Expression Fields and enter an SQL that:
a. Converts the string field TIMESTAMP into a human-readable format string

You can use the following sample syntax to convert the TIMESTAMP field into
a readable format. This syntax translate the TIMESTAMP field to a
human-readable format:

From: 106102416401200 0

To: 20061024:1640:1 2000

'20' || substr("Linux_C PU"."Timestamp" ,2,6) || ':' ||
substr("Linux_C PU"."Timestamp" ,8,4) || ':' ||
substr("Linux_C PU"."Timestamp" ,12,5)

b. Converts the string field TIMESTAMP into a datetime

You can use the following sample syntax to convert the TIMESTAMP field into
datetime format. This syntax translate the TIMESTAMP field to datetime
format:

From: 106102416401200 0

To: 20061024164012 (System default: "10/24/2006 4:40:12PM")

timestamp('20' || substr("Linux_C PU"."Timestamp" ,2,6) ||
substr("Linux_C PU"."Timestamp" ,8,6) )

<...>
------------------------------

The format therefor seems to be CYYMMDDHHMMSSmm m
(C=Century-bit: 0=19, 1=20)

HTH

--
Jeroen