ADSM-L

Re: Select Statement Syntax

2006-10-05 16:32:02
Subject: Re: Select Statement Syntax
From: Andrew Raibeck <storman AT US.IBM DOT COM>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Thu, 5 Oct 2006 14:23:06 -0600
Hi Joni,

What I am driving at is, why not just do this:

  where (node_name like 'NAS%' or node_name like 'SERVER%') and \
        BACKUP_START>=BACKUP_END

instead of this, which seems labored:

  where (node_name like 'NAS%' or node_name like 'SERVER%') and \
        date(BACKUP_START)>=date(BACKUP_END) and \
        time(BACKUP_START)>=time(BACKUP_END)

You also might want to do a test for null, so maybe:

  where (node_name like 'NAS%' or node_name like 'SERVER%') and \
          (BACKUP_START is not null and BACKUP_END is null or \
           BACKUP_START>=BACKUP_END)

Note that the above assumes that the backup was attempted but failed,
versus the backup never being attempted. To include the latter, you can
just reduce the above "where" cause slightly:

  where (node_name like 'NAS%' or node_name like 'SERVER%') and \
          (BACKUP_END is null or BACKUP_START>=BACKUP_END)

For your other query, you can do date differences like this:

select ... where backup_end < current_timestamp - 5 days

For example (and I leave it to you to pad the column names with blanks):

select node_name, \
       filespace_name, \
       backup_end \
  from filespaces \
  where backup_end is null or \
        backup_end < current_timestamp - 5 days

Regards,

Andy

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
10:15:18 AM:

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