Hello Everyone,
I finally came up with a mixture of everyone's suggestions so that I now
have good output:
select node_name as "NODE_NAME ",filespace_name as
"FILESPACE_NAME ",substr(char(backup_start),1,16) as
backup_start,substr(char(backup_end),1,16) as backup_end from filespaces
where node_name like 'NAS%' or node_name like 'SERVER%'
Which gave me:
NODE_NAME FILESPACE_NAME BACKUP_START
BACKUP_END
--------------------- -------------------------------
------------------ ------------------
NAS_SERVER_2 / 2006-10-04
19:00 2006-10-04 19:01
NAS_SERVER_2 /.etc_common 2006-10-04
19:00 2006-10-04 21:19
NAS_SERVER_2 /apache 2006-10-04
19:00 2006-10-04 21:08
NAS_SERVER_2 /clearcase_prod 2006-10-04
19:00 2006-10-04 20:16
NAS_SERVER_2 /clearcase_test 2006-10-04
19:00 2006-10-04 20:03
NAS_SERVER_2 /edi_test 2006-10-04
19:00 2006-10-04 20:24
NAS_SERVER_2 /home2 2006-10-04
19:00 2006-10-04 20:28
NAS_SERVER_2 /home2/home2dir1 2006-10-04
19:00 2006-10-04 21:02
NAS_SERVER_2 /home2/home2dir2 2006-10-04
19:00 2006-10-04 21:00
NAS_SERVER_2 /home2/home2dir3 2006-10-04
19:00 2006-10-04 21:17
NAS_SERVER_2 /home2/home2dir4 2006-10-04
19:00 2006-10-04 21:17
NAS_SERVER_2 /nas_mp2 2006-10-04
19:00 2006-10-04 20:23
My next challenge is to figure out how to find any filespaces where the
backup_start date/time is > the backup_end date/time. It is hard for me
to find failed NDMP backups and this is one of the ways in which I can see
which filespaces failed/missed. I have tried the following statement:
select node_name as "NODE_NAME ",filespace_name as
"FILESPACE_NAME ",substr(char(backup_start),1,16) as
backup_start,substr(char(backup_end),1,16) as backup_end from filespaces
where node_name like 'NAS%' or node_name like 'SERVER%' and
date(BACKUP_START)>=date(BACKUP_END) and
time(BACKUP_START)>=time(BACKUP_END)
But this gives me incorrect output. Does anyone know what I am doing
wrong? I've even just tried the "and backup_start > backup_end and I
still get incorrect data. Any ideas?
Thanks again!
********************************
Joni Moyer
Highmark
Storage Systems, Senior Systems Programmer
Phone Number: (717)302-9966
Fax: (717) 302-9826
joni.moyer AT highmark DOT com
********************************
"Andrew Carlson" <naclosagc AT GMAIL DOT COM>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
10/05/2006 10:45 AM
Please respond to
"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
To
ADSM-L AT VM.MARIST DOT EDU
cc
Subject
Re: Select Statement Syntax
I went through this same problem. Never got a satisfactory answer,
totally. It behaves differently depending on if you do it from a
terminal,
or disconnected, like from cron. Also it depends on the setting of
sqldisplaymode. Try the command `set sqldisplaymode wide` and see if that
makes it look like you want.
On 10/5/06, Joni Moyer <joni.moyer AT highmark DOT com> wrote:
>
> Hello Everyone,
>
> I have been trying to figure out how to have the syntax of the below
> output so that it will all go on 1 line, but so far I have been very
> unsuccessful.
>
> Here is the statement I am issuing:
>
> select node_name,filespace_name,backup_start,backup_end from filespaces
> where node_name like 'NAS%' or node_name like 'SERVER%'
>
> And here is the output:
>
> NODE_NAME FILESPACE_NAME BACKUP_START
> BACKUP_END
> ------------------ ------------------ ------------------
> ------------------
> NAS_SERVER_2_OFFS- / 2006-10-04
> 2006-10-04
> ITE 21:20:32.000000
> 21:23:03.000000
> NAS_SERVER_2_OFFS- /.etc_common 2006-10-04
> 2006-10-05
> ITE 21:20:32.000000
> 02:34:44.000000
> NAS_SERVER_2_OFFS- /apache 2006-10-04
> 2006-10-05
> ITE 21:20:32.000000
> 02:21:08.000000
> NAS_SERVER_2_OFFS- /clearcase_prod 2006-10-04
> 2006-10-04
> ITE 21:20:32.000000
> 22:36:12.000000
>
> As you can see the node_name column wraps. I tried to do a left justify
> and I also tried to make it 20 characters wide, but I guess I am doing
> something wrong because I continue to get the same output.
>
> I was also wondering if there is a way to change the backup_start to go
on
> 1 line as well and to make it include the date and the time to be just
> hh:mm:ss?
>
> Thank you in advance for any pointers you might have!
>
> ********************************
> Joni Moyer
> Highmark
> Storage Systems, Senior Systems Programmer
> Phone Number: (717)302-9966
> Fax: (717) 302-9826
> joni.moyer AT highmark DOT com
> ********************************
>
--
Andy Carlson
---------------------------------------------------------------------------
Gamecube:$150,PSO:$50,Broadband Adapter: $35, Hunters License:
$8.95/month,
The feeling of seeing the red box with the item you want in it:Priceless.
|