ADSM-L

Re: SQL timestamp not working when upgraded to 4.2.2 for summary tabl e

2002-08-15 14:58:53
Subject: Re: SQL timestamp not working when upgraded to 4.2.2 for summary tabl e
From: PAC Brion Arnaud <Arnaud.Brion AT PANALPINA DOT COM>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Thu, 15 Aug 2002 17:34:11 +0200
Hi Denis,

Just for information : I tested your query on my system (4.2.2.15) and
it worked like a charm (except I had to modify "Node Name" to
"Node_Name")

Did you apply the latest PTF's to get 4.2.2.15 ? Maybe it could help ...
Good luck anyway !

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
| Arnaud Brion, Panalpina Management Ltd., IT Group     |
| Viaduktstrasse 42, P.O. Box, 4002 Basel - Switzerland |
| Phone: +41 61 226 19 78 / Fax: +41 61 226 17 01       | 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=



-----Original Message-----
From: L'Huillier, Denis [mailto:DLHuilli AT EXCHANGE.ML DOT COM] 
Sent: Thursday, 15 August, 2002 15:39
To: ADSM-L AT VM.MARIST DOT EDU
Subject: SQL timestamp not working when upgraded to 4.2.2 for summary
tabl e


Hello -
Since I upgraded our 4.1 server to 4.2.2 my sql query against the
summary table no longer works. Has anyone run into this problem before?
Here's the query...

/* --- Query Summary Table ---- */
/* ---   Run as a macro   ----- */
select cast(entity as varchar(12)) as "Node Name", \ cast(activity as
varchar(10)) as Type, \ cast(number as varchar(8)) "Filespace", \
cast(failed as varchar(3)) "Stg", \ cast(affected as decimal(7,0)) as
files, \ cast(bytes/1024/1024 as decimal(12,4)) as "Phy_MB", \
cast(bytes/1024/1024 as decimal(12,4)) as "Log_MB" \ from summary where
end_time>=timestamp(current_date -1 days, '09:00:00') \ and
end_time<=timestamp(current_date, '08:59:59') \ and (activity='BACKUP'
or activity='ARCHIVE') \ order by "Node Name"

Here's the output from a 4.1 server:

Node Name     TYPE        Filespace  Stg      FILES          Phy_MB
Log_MB
------------  ----------  ---------  ---  ---------  --------------
--------------
CENKRS        BACKUP      649        0           26          0.0222
0.0222
CENNTNFS      BACKUP      648        0           15          0.0072
0.0072
RSCH-AS1-P    BACKUP      615        0           90          7.3412
7.3412
RSCH-DB2-P    BACKUP      614        0           43          5.6337
5.6337
RSCH-DB3-P    BACKUP      608        1            0          0.0000
0.0000
RSCH-DB3-P    BACKUP      616        0          114       1477.5513
1477.5513
RSCH-FS1-P    BACKUP      611        1            0          0.0000
0.0000
RSCH-FS1-P    BACKUP      618        0           97         10.3834
10.3834
RSCH-WS5-P    BACKUP      667        0           29          2.5706
2.5706
RSCH-WS6-P    BACKUP      666        0           35          5.4812
5.4812
TPRSCHHOME01  BACKUP      624        2            0          0.0000
0.0000
TPRSCHHOME01  BACKUP      627        0         2467      16412.1675
16412.1675
TPRSCHHOME02  BACKUP      634        1            0          0.0000
0.0000
TPRSCHHOME02  BACKUP      637        0         3552      19135.1409
19135.1409

Here's the output from a 4.2 server:

Node Name     TYPE        Filespace  Stg      FILES          Phy_MB
Log_MB
------------  ----------  ---------  ---  ---------  --------------
--------------
REMEDY2W      BACKUP      3896       0           64          0.0000
0.0000

I only get one line back.. There should be one for each node (about 100
nodes on this server)

Now, for any of you who are wondering..  'Filespace' and 'Stg' are
columns put in just as place holders. We were using the 'q occu' to
generate charge back info.  I needed to generate an sql query would look
Just like the q occu (same columns) so the data could be fed into an
existing program which handled charge Back to the clients.

Regards,

Denis L. L'Huillier
212-647-2168