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_Processor_Time", "WRITETIME", "MAX_
%_Total_Processor_Time", "MIN_%_Total_Processor_Time", "AVG_
%_Total_Privileged_Time" , "AVG_%_Total_User_Time",
"AVG_Context_Switches/Sec", "AVG_Total_Interrupts/Sec"
from "NT_System_HV"
where "Server_Name" = 'Primary:ITM6VM:NT' and "WRITETIME" between
'0000000000000000' and'9999999999999999'

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]

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]

On Jun 20, 9:31*pm, Tonkuma <[e-mail]:

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 1082106400000000.

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

pls provide the query . thanks

[Tonkuma]

data type of "WRITETIME" is 16 character.

data is store like that 1082106400000000.

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(miliseconds? microseconds? or
other?) of the duration?

[Lennart]

On Jun 23, 8:34 am, Irfan Mughal <[e-mail]:
[...]

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

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') +
(1082106400000000 / 1000000) seconds (current_timestamp - 24 hours)

/Lennart

[...]

[The Boss]

Lennart wrote:

On Jun 23, 8:34 am, Irfan Mughal <[e-mail]:
[...]

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

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') +
(1082106400000000 / 1000000) seconds (current_timestamp - 24 hours)

/Lennart

[...]

It is a typical format for Tivoli, I guess, and definitely not "microseconds
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: 1061024164012000

To: 20061024:1640:12000

'20' || substr("Linux_CPU"."Timestamp",2,6) || ':' ||
substr("Linux_CPU"."Timestamp",8,4) || ':' ||
substr("Linux_CPU"."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: 1061024164012000

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

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

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

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

HTH

--
Jeroen