Re: [ADSM-L] Nested SQL?

Loon, EJ van - SPLXM wrote:
>
> Hi TSM-ers!
> We use the following SQL statement to report to our Oracle department if
> their delete obsolete jobs are running ok for all nodes:
>
> select node_name, filespace_name, ll_name, state, object_id,
> date(backup_date) from backups where ((days(current_date) -
> days(backup_date) >= 30)) and state= 'ACTIVE_VERSION' and
> node_name='<nodename>'
>
> Since backups are kept for 14 days, no objects should be returned.
> We use TSMOR to report this on a weekly basis for all their nodes. If
> there are objects older than 30 days, TSM lists them as an exception,
> along with the amount of obsolete objects.
> The Oracle departments now asks me if the report could be enhanced with
> amount of obsolete objects per node per database. I know the database
> SID is used in the first 5 characters of the backup object name
> (ll_name), but if I want to put that in one SQL query, I think I have to
> create a nested SQL statement.
> Does anybody know if nested SQL is supported by TSM?

Eric,

If I'm understanding your DBAs' request correctly, you shouldn't need to
do a nested query anyway, but just include "count(*) as one of the fields
you're selecting.  You'll need to add "group by" clauses for the static
fields you're including.

Here's the select statement I'm using for a similar report that I run for
our DBAs (monthly due to our longer retention):

   select distinct node_name, filespace_name, count(*) as "OBJECTS", -
          cast(min(backup_date) as date) as "OLDEST_BACKUP" -
   from backups -
   where node_name like 'ORA-%' and -
         backup_date < (current_timestamp - 195 days) -
   group by node_name, filespace_name

(Here, all Oracle TDP nodes are named "ORA-FQDN", and each database
instance/SID gets its own filespace name.  Makes for easier bulk cleanup
of backups of obsolete/test/etc. databases.)


--
Hello World.                                    David Bronder - Systems Admin
Segmentation Fault                                     ITS-SPA, Univ. of Iowa
Core dumped, disk trashed, quota filled, soda warm.   david-bronder AT uiowa 
DOT edu