http://adsm.org/forum/ TSM and SQL Scripting en Tue, 18 Jun 2013 05:59:53 GMT vBulletin 60 http://adsm.org/forum/images/misc/rss.png http://adsm.org/forum/ http://adsm.org/forum/showthread.php?27928-Where-s-that-!-tape!&goto=newpost Wed, 05 Jun 2013 22:03:37 GMT Ok, here is the senerio... - Three tape libraries with over 1500 tapes - Seven Windows TSM servers and three are library managers. - DRM is... Ok, here is the senerio...

- Three tape libraries with over 1500 tapes
- Seven Windows TSM servers and three are library managers.
- DRM is being used

Occasionally I need to find who/what is using a tape. Sounds simple enough, but the tape I am looking for could be a scratch tape, a DB backup tape, a "normal" tape (with data and being filling), or a DRM tape already sent back offsite.

Currently, to find these tapes I have to do a 'q libv' on my three library managers, and a 'q drm' on all my tsm servers, then sift through all the output looking for the one tape I am interested in.

Before I go reinventing wheels, does anyone have a slick script/macro that can search multiple TSM servers for a tape...no matter what the tape is being used for? ]]> Scripting droach http://adsm.org/forum/showthread.php?27928-Where-s-that-!-tape! http://adsm.org/forum/showthread.php?27920-Different-result-from-select-in-v5-vs-v6&goto=newpost Tue, 04 Jun 2013 10:55:07 GMT Hello TSM'ers

I have been using the following statement in TSM v. 5, to monitor how long since the last server restart;
select cast((current_timestamp)-(restart_date) hours as integer) from status

Running the same in v 6, does not take into account that the date has changed, and will only display in the range of 0-23 hours. It looks as it only calculates based on the time, and not including the date.

Does anyone have a select statement for v 6 that will give me the real number of hours since last restart?

Any help much appreciated.

G

EDIT: AAAaaahhh - after trying long enough, i found one possible solution;
select cast((current_timestamp)-(restart_date) hours as integer) + (day(current_timestamp - restart_date))*24 from status
This adds up the difference in hours + difference in days*24 to get the correct number of hours. Not so pretty, but it works.
G ]]> Scripting Geirr http://adsm.org/forum/showthread.php?27920-Different-result-from-select-in-v5-vs-v6 http://adsm.org/forum/showthread.php?27891-Billing-SQL-Query&goto=newpost Tue, 28 May 2013 07:12:33 GMT Hi all, I am looking for an SQL query which will allow me to determine how many GB is being used in the primary storage pool by the nodes... Hi all,

I am looking for an SQL query which will allow me to determine how many GB is being used in the primary storage pool by the nodes associated with domains.

I have my policy domains configured for departments, like so:
ACCNT_FILE
ACCNT_DB2
ACCNT_EXCH
LEGAL_FILE
LEGAL_DB2
LEGAL_EXCH

So I am looking for an SQL query which will determine the amount of occupancy by the nodes associated with the domains for accounting, for legal, for marketing etc...

I've spent a couple of hours trying to work it out myself but I've really gotten nothing but excessive failed SQL queries. Does anyone have any queries which will help me here?

Thanks in advance.

Kind regards,
Michael Andrews ]]> Scripting bigred15 http://adsm.org/forum/showthread.php?27891-Billing-SQL-Query http://adsm.org/forum/showthread.php?27883-SQL-for-sessions-running-during-a-specific-time-frame&goto=newpost Fri, 24 May 2013 01:30:27 GMT I need a select statement that can give me a list of sessios (output should look like q sess) but for a specific date and time range. i.e.: today... I need a select statement that can give me a list of sessios (output should look like q sess) but for a specific date and time range. i.e.: today between 9:00am and 10:00am. ]]> Scripting ebaracco http://adsm.org/forum/showthread.php?27883-SQL-for-sessions-running-during-a-specific-time-frame http://adsm.org/forum/showthread.php?27869-Format-HH-MM-SS-field&goto=newpost Mon, 20 May 2013 13:03:20 GMT Hello all, I have this script that is working except that in the hours, minutes and seconds fields when any of them have a leading zero the zero gets... Hello all, I have this script that is working except that in the hours, minutes and seconds fields when any of them have a leading zero the zero gets dropped. For instance: if a session has been running for 12 minutes and 7 seconds the output will display 0:12:7 instead of 00:12:07. A trailing zero is ok. Those get displayed. I suspect I have to do something with the decimal(2) but not sure what that might be yet so I thought I'd ask the experts.

Thanks!


select client_name, session_id, state, -
substr(cast((start_time) as char(29)),1,10) as "Start Date", -
substr(cast((start_time) as char(29)),12,8) as "Start Time", -
cast(float(bytes_sent/1024/1024) as dec(12,3)) as "MB Sent", -
cast(float(bytes_received/1024/1024) as dec(12,3)) as "MB Recvd", -
CAST((current_timestamp-start_time)hours as decimal(2))||':'|| -
CAST((current_timestamp-start_time)minutes as decimal(2))||':'|| -
CAST((current_timestamp-start_time)seconds as decimal(2)) as "ELAPTIME HH:MM:SS" -
from sessions order by session_id ]]> Scripting rdemaat http://adsm.org/forum/showthread.php?27869-Format-HH-MM-SS-field