ADSM-L

Re: Select Statement Syntax

2006-10-05 15:53:31
Subject: Re: Select Statement Syntax
From: Joni Moyer <joni.moyer AT HIGHMARK DOT COM>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Thu, 5 Oct 2006 13:15:18 -0400
Hi Andy,

I pretty much want to see the following:

NODE_NAME                 FILESPACE_NAME                      BACKUP_START
          BACKUP_END
---------------------     -------------------------------
------------------     ------------------
NAS_SERVER_2              /                                   2006-10-04
19:00       2006-10-03 19:01
NAS_SERVER_2              /.etc_common                        2006-10-04
19:00       2006-10-03 21:19
NAS_SERVER_2              /apache                             2006-10-04
19:00       2006-10-03 21:08

This points me to the fact that on 10/4 a backup started, but the last
successful backup ended on the day before, so something must've gone
wrong...

I've been really struggling with getting a good report for my NAS NDMP
backups.  I tried a long time ago to join 2 tables so that I received a
good report, but it would never complete and always timed out.

I'd also like to be able to get a listing of any filesystems that haven't
backed up in over 5 days, but that's another battle.

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 Raibeck" <storman AT US.IBM DOT COM>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
10/05/2006 01:03 PM
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






Remember your operator precedence: items are ANDed first, then ORed, so
without strategically placed parentheses around the ORed portion of your
SELECT statement, you will get the wrong results.

Also, why do you need to break down BACKUP_START and BACKUP_END into
comparison of the individual date and time components?

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: storman AT us.ibm DOT com

IBM Tivoli Storage Manager support web page:
http://www.ibm.com/software/sysmgmt/products/support/IBMTivoliStorageManager.html


The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.

"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 10/05/2006
09:48:21 AM:

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

<Prev in Thread] Current Thread [Next in Thread>