In <9811099106.AA910660151 AT ccmail.adp.wisc DOT edu>, on 11/09/98
at 07:07 PM, Eric LEWIS <eric.lewis AT CCMAIL.ADP.WISC DOT EDU> said:
> Hi: I am just learning how to use the Select SQL and am encountering
> some problems. I do plan to purchase one or more of the references
> Andy recommended in September but I have been making do with IBM
> Database 2 Version 3 DB2 SQL Reference first edition December 1993.
> I want to produce a list of nodes with filespaces that have not been
> backed up in over 30 days.
>select node_name,filespace_name,backup_end,current_date-date(backup_end)
>from filespaces where (current_date-date(backup_end)>(30 days))
(snip...)
> Many thanks in advance.
> Eric Lewis UW-Madison
> eric.lewis AT doit.wisc DOT edu
I took your first query and reworked it. This works --
--- cut after this line ---
/* */
/* */
/* macro file to select filespaces that haven't */
/* completed a backup in more than 30 days */
/* */
set sqldatetimeformat i
set sqldisplaymode w
set sqlmathmode r
commit
select left(node_name,16) as node_name, -
left(filespace_name,32) as file_space, -
backup_end, -
current_date - date(backup_end) as days_delinquent -
from filespaces -
where (days(current_date) - days(backup_end) >= 31) -
> e:\adsmcv3\sqllists\stalebkups
--- cut before this line ---
You can avoid the unintended redirection by using '>=' instead of '>'. I
You can avoid the unintended redirection by using '>=' instead of '>'. I
always redirect the sql output; you never know how big it might be.
Save this file as 'select.mac' in the directory you run dsmadmc in. then
just say -- macro select.mac
--
-----------------------------------------------------------
-----------------------------------------------------------
Bill Colwell
Bill Colwell
C. S. Draper Lab
Cambridge, Ma.
bcolwell AT draper DOT com
-----------------------------------------------------------
=========================================================================
|