Results 1 to 6 of 6
  1. #1
    Member
    Join Date
    Jan 2011
    Posts
    108
    Thanks
    13
    Thanked 3 Times in 2 Posts

    Default SQL querry Data backedup each year/month

    Hi

    Is it possible to write a querry to find out how much data was backedup/archived for a node in a particular month. The months are beyond a year or 2.

    NODENAME DATABACKEDUP MONTH/YEAR

    Regards,
    TSMDUMBER

  2. #2
    Senior Member GregE's Avatar
    Join Date
    May 2006
    Posts
    2,100
    Thanks
    9
    Thanked 17 Times in 16 Posts

    Default

    It's possible.

    I run this as a daily report but this may get you going in the right direction.

    Code:
    SELECT - 
       n.node_name||'  ' as NODE, -
       n.platform_name as PLATFORM, -
       su.activity,sum(cast(su.bytes/1024/1024/1024 as decimal(6,2))) as GB, -
       su.affected -
    FROM nodes n, summary su -
    WHERE -
       (end_time between current_timestamp - 24 hours and current_timestamp) -
       and su.activity='BACKUP' -
       and ((n.node_name=su.entity)) - 
    GROUP BY -
       n.node_name, -
       n.platform_name, -
       su.activity, -
       su.affected - 
    ORDER BY - 
       platform, -
       GB, -
       node asc

  3. #3
    Member
    Join Date
    Jan 2011
    Posts
    108
    Thanks
    13
    Thanked 3 Times in 2 Posts

    Default

    Thanks Greg. I am looking for something which can go back to a year!!with actlog retention of 100 days querryinf from summary tabel will not help!!!

    In which table is the size of a file stored?

    I found something like the below form the internet like this

    select sum(BYTES)/1024/1024/1024 "Data_Backup GB" from summary where start_time>(current_timestamp - 1 days) and End_Time< (current_timestamp) and ACTIVITY in ('BACKUP','ARCHIVE') and ENTITY='______'

    but i want the perido inbetween 2 years, sayy nov 2008 to nov 2009?

  4. #4
    Member
    Join Date
    Sep 2010
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    What about the table ARCHIVES?? and the table BACKUP??

    Those might do the trick for you.

    But be careful, according to the database size and those tables can be very big and to query them can be time consuming.

  5. #5
    Member
    Join Date
    Jan 2011
    Posts
    108
    Thanks
    13
    Thanked 3 Times in 2 Posts

    Default

    the tables archives nad backups do not have the bytes column or anything related to size

    tsm: TSM>select * from backups where node_name =
    ANR2963W This SQL query may produce a very large result table, or may require a significant amount of time to compute.

    Do you wish to proceed? (Yes (Y)/No (N)) y

    NODE_NAME:
    FILESPACE_NAME: /adsmorc
    FILESPACE_ID: 1
    STATE: ACTIVE_VERSION
    TYPE: FILE
    HL_NAME: //
    LL_NAME: 00jttadl_1_1
    OBJECT_ID: 110103009
    BACKUP_DATE: 2008-10-23 23:13:58.000000
    DEACTIVATE_DATE:
    OWNER:
    CLASS_NAME: DEFAULT

    tsm: TSM>select * from occupancy where node_name=

    NODE_NAME:
    TYPE: Bkup
    FILESPACE_NAME:
    STGPOOL_NAME:
    NUM_FILES: 54116
    PHYSICAL_MB: 48613062.85
    LOGICAL_MB: 48613062.85
    FILESPACE_ID: 1

    still the big question is In which table is the size of a file stored?

    Regards,
    TSMDUMBER

  6. #6
    Member
    Join Date
    Sep 2010
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Ok, I get it.

    Well there is the CONTENTS table, it has the FILE_NAME and the FILE_SIZE columns, also the NODE_NAME.

    Again this table is even bigger than the previous one I told you, once I had to a query and it was really slow, so much that I decided to go with occupancy.

    Regards.

Similar Threads

  1. SQL statement to get nodes added per year
    By dig1tal in forum TSM Reporting & Monitoring
    Replies: 6
    Last Post: 12-22-2009, 06:42 AM
  2. Restore data backedup with subfile option.
    By major in forum TSM Installation, Upgrade and Configuration
    Replies: 2
    Last Post: 06-16-2009, 04:41 PM
  3. dsmc querry access failed with error message ans1302
    By nickalai17 in forum Administrative Client
    Replies: 0
    Last Post: 04-12-2008, 03:42 AM
  4. Month end and Year end Schedule.
    By pmishra in forum Backup / Archive Discussion
    Replies: 1
    Last Post: 10-01-2007, 11:10 AM
  5. Replies: 7
    Last Post: 08-29-2005, 04:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •