Data Protection Software

Expand all | Collapse all

Looking for sql query for TSM 6.1 on AIX

  • 1.  Looking for sql query for TSM 6.1 on AIX

    Posted Thu September 27, 2018 01:18 AM
    Hi All,

    I am searching a query to get the complete details of client schedule and admin schedule success. it's very easy for me if these schedules have been configured on TSM itself but this is not the case, I am sharing the environment details please advise have any thoughts.

    1. We have TSM 7 instance on 7 different server, All are AIX few of them has 5.1.x TSM version as well.
    2. Schedule have been configured through TNG and Autosys tool.
    3. All the scripts to backing up the client is placed on one server called x on AIX platform.
    4. Maintenance jobs are scheduled through TNG.

    I wanted to run any sql query which will provide me the result of success or failure of client and Admin scheduled which is configured through TNG and Autosys.

    Note- Below are the setup which i tried.

    1. q eve * * - doesn't show anything
    2. q eve * t=a begind=-1 -doesn't show anything
    3. q act se=adminschdule/clientschdulename begind=-1

    kindly let me know if any further details required.


    ------------------------------
    Prem Singh

    ------------------------------


  • 2.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Sat September 29, 2018 10:09 AM
    Hi Guys,

    I am waiting for your advise bit urgent requirement, Please help me.

    ------------------------------
    Prem Singh

    ------------------------------



  • 3.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Mon October 01, 2018 03:04 AM

    Well Prem,

    if there is no TSM Schedule then the events table will not show anything. If you use an external tool (I don't know what TNG is) then there are two ways. See what TNG reports or have a look a the summary table.

    Have a look at http://thobias.org/tsm/sql/ - a good website for predefined SQLs which work nicely.

    And he even has the different SQLs between TSM V5 and TSM V6-later.

    Take the "Total of backup and archive per node in a specific date" which is probably what you are looking for.

    --





  • 4.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Wed October 03, 2018 01:20 AM
    Thanks Stefan,

    I am glad you have spare  time to share your valuable knowledge in my particular case we don't have control on TNG another option is to see the summary table since we have more than 3000 schedule over that it is very difficult to club all information on one place and have a look on this, do you have any suggestion for me to setup anything on TSM with the name of scheduler we can set some sort of query once the scheduler finish we will get email alerts for success and failure.

    ------------------------------
    Prem Singh

    ------------------------------



  • 5.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Wed October 03, 2018 09:05 AM
    Prem,
    Regardless if the task is an administrative or client action if it is scheduled by an external scheduler, such as TNG, the TSM server will have no knowledge of the schedule. It is asked to perform an explicit task not execute a schedule. The backup server has no knowledge of the schedule only the actual task itself.

    If the task is scheduled using the internal TSM scheduler you could then query the EVENTS table for pertinent information, for example:

    Select * from EVENTS where SCHEDULE_NAME='EXPIRATION' and ACTUAL_START>=CURRENT_TIMESTAMP-24 hours order by ACTUAL_START

    Note: Always use care in querying the events table, too broad of a query can severely impact system performance and operation.

    Alternatively, even when using an external scheduler you could query the ACTLOG table for task error messages and filter the output, but doing this can be complicated and time consuming.
    Here is an example I use for monitoring SQL backups that ran in the last 24 hours for specific database failures:

    Select date_time,nodename,message from actlog where domainname='SQL_DOMAIN' and severity='E' and date_time>=current_timestamp-24 hours order by nodename

    Combined with a simple for/next script I query all of my TSM backup servers and port results off to a csv file that can be reviewed for issues.

    However the easiest and most reliable place to generate the reporting would be from the actual scheduler itself and should be completely reasonable to expect some sort of reporting from it.

    ------------------------------
    Richard Adamson
    TSM/ISP Consultant
    ------------------------------



  • 6.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Mon October 08, 2018 07:20 AM
    Edited by Prem Singh Fri June 19, 2020 11:37 AM
    Hi Richard,

    Thank you so much for the response pls accept my apologies to replying late, the sql query you have provided i tried on that environment it is not returning anything it just shows no match found.

    ------------------------------
    Prem Singh

    ------------------------------



  • 7.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Wed October 03, 2018 08:27 PM
    Hi Prem, are you an IBMer? If so this is the Slack channel to use for this question, per our offering team: https://ibm-systems-storage.slack.com/messages/C33GW206L

    ------------------------------
    Peter Basmajian
    Storage Community Manager, IBM
    San Francisco CA 650-542-7246
    ------------------------------



  • 8.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Mon October 08, 2018 07:29 AM
    Hi Peter,

    As of now I can say i am unlucky just few months ago I left IBM , I have no other option product is already EOL and EOS as well, just trying to working out somehow.

    ------------------------------
    Prem Singh

    ------------------------------



  • 9.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Tue October 02, 2018 04:16 AM
    Hi Prem,

    You could use a third party monitoring/reporting tool such a Servergraph, which can report on external schedulers. Or, if you know the name of the scripts that are being called, you could create queries against the sessions table to see when the scripts start and finish and monitor their return codes. This would probably be best written in UNIX shell scripts with calls into dsmadmc for the TSM SQL queries.

    Regards
    Darren

    ------------------------------
    Darren Sanders
    Principal Consultant
    Celerity Ltd

    #ibmchampion storage
    ------------------------------



  • 10.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Wed October 03, 2018 01:23 AM
    Hi Darren,

    Thanks for the input, we are not allowed to use any third party trail software in the infrastructure. yes I have few name available with me but only for maintenance jobs but for client schedule I don't have anything, that's why not able to conclude from where I should start.

    ------------------------------
    Prem Singh

    ------------------------------



  • 11.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Wed October 03, 2018 09:36 AM
    Prem,

    You can install Operation Center also. It will show you all script ran. This is another tool. I use Servergraph. It is very good tool for reporting, admin management and monitor.

    ------------------------------
    Parul Patel
    ------------------------------



  • 12.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Mon October 08, 2018 07:26 AM
    Hi Parul,

    Apologies for replying late I was engaged on another task,  Operation center could be a choice in my case i have 7 Master server out of that only one has version 6, all are version 5. and operation center will also  show only when script is defined on tsm and schedule it from tsm that's my view another option is to monitor all the time,

    ------------------------------
    Prem Singh

    ------------------------------



  • 13.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Wed October 17, 2018 11:45 PM
    Hi All,

    Just an update here i found TSM Manager 5.0 in the environment, I have setup few things like, once instance down, drive is down, scratch needed, db utilization is increasing, now I am able to receive notification. but wondering on this how i can get the notification for all 7 instances, client schedule failure can we achieve from inr msg, is anyone have any thoughts on this

    ------------------------------
    Prem Singh

    ------------------------------



  • 14.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Thu October 18, 2018 06:08 AM

    Please try reviewing the following documentation links:

    'Monitoring storage solutions' (like a simple checklist)

    'Selecting, configuring, and using monitoring tools' (a set of approaches to solving the aforementioned problem)

    'Logging IBM Tivoli Storage Manager events to receivers' (likely what is needed... but requires some TSM-specific knowledge)

    Don't worry about the links being specific to a newer version of TSM (the concepts haven't changed much since TSM v4.x) - and good luck!



    ------------------------------
    Rishi Chopra
    Software Engineer
    IBM
    Concord CA
    (925) 405-5485
    ------------------------------



  • 15.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Thu October 18, 2018 12:15 PM
    Hi Rishi,
    I have checked all of the option , I am not able to find any option where I can put sql query to get the schedule success or failure report on TSM Manager, if you have clue can you pls help me to locate the location where I can inject sal query to get the desired result and most importantly schedules are the there on TSM end.

    ------------------------------
    Prem Singh
    ------------------------------



  • 16.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Thu October 18, 2018 06:13 AM

    Prem,


    Operation Center will work as long as you are running 6.4 above. Operation is design to run on any TSM server 6.4 and above. 


    Parul Patel

    Data Backup and Recovery Admin

    Einstein Healthcare Network

    1000 West Tabor Road

    Philadelphia, PA 19141

    215-456-8027

    patelp@einstein.edu

    This message is intended for the use of the person or entity to which it is addressed and may contain information that is privileged and confidential, the disclosure of which is governed by applicable law. If you are not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any disclosure, copying, or distribution of this information is strictly prohibited. If you have received this message by error, please notify the sender immediately to arrange for return or destruction of these documents.





  • 17.  RE: Looking for sql query for TSM 6.1 on AIX

    Posted Thu October 18, 2018 12:11 PM
    Hi Parul,
    that's the problem we have 6 instance on 5.x and one is on 6.1 that's why I don't have option to utilize operation center.

    ------------------------------
    Prem Singh

    ------------------------------