Re: Select Stmts. in TSM - Outer join is not needed
-- You can't do an outer join (whatever that is... I'm just learning this stuff...) You cannot do outer joins simply because AFAIK they're not implemented. The reason for the latter is of the same complexity - they're not needed. Outer join allows you to get some field be NULL when the foreign key field is NULL. Example: Table t1, fields File_Name Mgmt_Class c:\file1classA d:\file2classB e:\file3NULL Table t2, fields Class_Name Retension ClassA 10 ClassB 8 Now simple join will produce: select file_name, retension from t1,t2 where t1.mgmt_class = t2.class_name Output: c:\file110 d:\file28 Outer join will give us: select file_name, retension from t1, outer join t2 where t1.mgmt_class = t2.class_name output: c:\file110 d:\file28 e:\file3NULL I hope this helps. TSM SQL is not generic purpose RDBMS engine so its features are driven only by TSM server needs. Outer join is not needed - it is not implemented. Same for INSERT, UPDATE, etc. As Steffan pointed you can usee DB2 docs. He is getting them from the web site. I have them locally. With AIX 4.3.3 there are three versions of DB2 coming (5.2,67). Those who have no DB2 still can read the site. Remember: TSM DB is not DB2 and a nice feature found in DB2 manuals most probably is not implemented in TSM. But the basic syntax, table names, predicates, etc. are working as documented in DB2. So whenever you're having question about TSM SQL you can look at DB2 docs. But to be sure it would work you have to test it in TSM. At the end this is another argument to management to buy you a test TSM server if you do not have it :-) Zlatko Krastev IT Consultant Please respond to ADSM: Dist Stor Manager [EMAIL PROTECTED] Sent by:ADSM: Dist Stor Manager [EMAIL PROTECTED] To: [EMAIL PROTECTED] cc: Subject:Re: Select Stmts. in TSM Here is a script that does a join; it's just std SQL syntax. /* Show filespaces not backed up in 6 months w/space on server */ /* This query runs a long time; suggest writing output to a file */ /* Also strongly recommend running in commadelimited mode*/ select oc.node_name, oc.filespace_name as filespace, - physical_mb as mbytes , stgpool_name as stgpool, - date(fs.backup_start) as bkup_date - from occupancy oc, filespaces fs - where oc.node_name=fs.node_name - and oc.filespace_name=fs.filespace_name - and cast((current_timestamp-backup_start)days as integer)=183 - order by stgpool_name, oc.node_name, oc.filespace_name You can't do an outer join (whatever that is... I'm just learning this stuff...) Wanda Prather The Johns Hopkins Applied Physics Lab 443-778-8769 [EMAIL PROTECTED] Intelligence has much less practical application than you'd think - Scott Adams/Dilbert -Original Message- From: Peppers, Holly [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 12:43 PM To: [EMAIL PROTECTED] Subject: Select Stmts. in TSM Has anybody tried to do a join in the TSM tables? I went looking thru the help utility and the manuals, but did not see the syntax of how to do this. Let me know please. Thanks. Holly L. Peppers Blue Cross Blue Shield of Florida [EMAIL PROTECTED] Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
Re: Select Stmts. in TSM: Manuals
We are going to submit a requirement to Tivoli to provide a SQL Statements Manual solution. -Original Message- From: ARhoads [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 03, 2002 4:14 PM To: [EMAIL PROTECTED] Subject: Re: Select Stmts. in TSM Holly, I use the DB2 SQL docs from the IBM software site. Between the TSM online help (that shows you available verbs/syntax) the DB2 docs I can generally figure out whatever I need. Please remember that TSM is not the best SQL engine should not be overused during otherwise busy periods. Steffan - Original Message - From: Peppers, Holly [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 22, 2002 9:42 AM Subject: Select Stmts. in TSM Has anybody tried to do a join in the TSM tables? I went looking thru the help utility and the manuals, but did not see the syntax of how to do this. Let me know please. Thanks. Holly L. Peppers Blue Cross Blue Shield of Florida [EMAIL PROTECTED] Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
Re: Select Stmts. in TSM: Manuals
On the 3rd ADSM Workshop in Karlsruhe 1998 there was a fine lecture on the SQL interface by Andy Raibeck. All handouts from this Workshop are available at http://www.rz.uni-karlsruhe.de/Uni/RZ/Dienste/ADSM/WORKSHOPS/3rd/handouts/ Andys handouts: http://www.rz.uni-karlsruhe.de/Uni/RZ/Dienste/ADSM/WORKSHOPS/3rd/handouts/raibeck/index.htm and http://www.rz.uni-karlsruhe.de/Uni/RZ/Dienste/ADSM/WORKSHOPS/3rd/handouts/raibeck.ps As TSM developed since then, getting an update would be fine, but the old document is still very helpful. Regards, Michael -- Michael Bartl, Dipl. Inform. (FH) mailto:[EMAIL PROTECTED] Backup Services, IT Germany/Austria Tel: +49-89-92699-806 Cable Wireless Deutschland GmbH. Fax: +49-89-92699-302 Landsberger Str. 155, D-80687 Muenchen http://www.cw.com/de Seay, Paul wrote: We are going to submit a requirement to Tivoli to provide a SQL Statements Manual solution. -Original Message- From: ARhoads [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 03, 2002 4:14 PM To: [EMAIL PROTECTED] Subject: Re: Select Stmts. in TSM Holly, I use the DB2 SQL docs from the IBM software site. Between the TSM online help (that shows you available verbs/syntax) the DB2 docs I can generally figure out whatever I need. Please remember that TSM is not the best SQL engine should not be overused during otherwise busy periods. Steffan - Original Message - From: Peppers, Holly [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 22, 2002 9:42 AM Subject: Select Stmts. in TSM Has anybody tried to do a join in the TSM tables? I went looking thru the help utility and the manuals, but did not see the syntax of how to do this. Let me know please. Thanks. Holly L. Peppers Blue Cross Blue Shield of Florida [EMAIL PROTECTED] Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
Re: Select Stmts. in TSM
Holly, I use the DB2 SQL docs from the IBM software site. Between the TSM online help (that shows you available verbs/syntax) the DB2 docs I can generally figure out whatever I need. Please remember that TSM is not the best SQL engine should not be overused during otherwise busy periods. Steffan - Original Message - From: Peppers, Holly [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 22, 2002 9:42 AM Subject: Select Stmts. in TSM Has anybody tried to do a join in the TSM tables? I went looking thru the help utility and the manuals, but did not see the syntax of how to do this. Let me know please. Thanks. Holly L. Peppers Blue Cross Blue Shield of Florida [EMAIL PROTECTED] Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
Re: Select Stmts. in TSM: What is an OUTER JOIN
Is anyone else getting multiple (about one an hour) of this message from Paul? -Original Message- From: Seay, Paul [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 2:10 PM To: [EMAIL PROTECTED] Subject: Re: Select Stmts. in TSM: What is an OUTER JOIN An OUTER join really does not apply to an application like TSM. There is a right, left and plain. An OUTER basically keeps the left or right select (or both) and joins what it can with the items that do not match having null results. -Original Message- From: Prather, Wanda [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 1:02 PM To: [EMAIL PROTECTED] Subject: Re: Select Stmts. in TSM Here is a script that does a join; it's just std SQL syntax. /* Show filespaces not backed up in 6 months w/space on server */ /* This query runs a long time; suggest writing output to a file */ /* Also strongly recommend running in commadelimited mode*/ select oc.node_name, oc.filespace_name as filespace, - physical_mb as mbytes , stgpool_name as stgpool, - date(fs.backup_start) as bkup_date - from occupancy oc, filespaces fs - where oc.node_name=fs.node_name - and oc.filespace_name=fs.filespace_name - and cast((current_timestamp-backup_start)days as integer)=183 - order by stgpool_name, oc.node_name, oc.filespace_name You can't do an outer join (whatever that is... I'm just learning this stuff...) Wanda Prather The Johns Hopkins Applied Physics Lab 443-778-8769 [EMAIL PROTECTED] Intelligence has much less practical application than you'd think - Scott Adams/Dilbert -Original Message- From: Peppers, Holly [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 12:43 PM To: [EMAIL PROTECTED] Subject: Select Stmts. in TSM Has anybody tried to do a join in the TSM tables? I went looking thru the help utility and the manuals, but did not see the syntax of how to do this. Let me know please. Thanks. Holly L. Peppers Blue Cross Blue Shield of Florida [EMAIL PROTECTED] Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
Select Stmts. in TSM
Has anybody tried to do a join in the TSM tables? I went looking thru the help utility and the manuals, but did not see the syntax of how to do this. Let me know please. Thanks. Holly L. Peppers Blue Cross Blue Shield of Florida [EMAIL PROTECTED] Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
Re: Select Stmts. in TSM
I don't believe it's possible. I've researched this too. If anyone else has found otherwise, please let me know. Timothy A. Ford, MCSE LAN Services Micron Technology, Inc. -Original Message- From: Peppers, Holly [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 10:43 AM To: [EMAIL PROTECTED] Subject: Select Stmts. in TSM Has anybody tried to do a join in the TSM tables? I went looking thru the help utility and the manuals, but did not see the syntax of how to do this. Let me know please. Thanks. Holly L. Peppers Blue Cross Blue Shield of Florida [EMAIL PROTECTED] Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
Re: Select Stmts. in TSM
Here is a script that does a join; it's just std SQL syntax. /* Show filespaces not backed up in 6 months w/space on server */ /* This query runs a long time; suggest writing output to a file */ /* Also strongly recommend running in commadelimited mode*/ select oc.node_name, oc.filespace_name as filespace, - physical_mb as mbytes , stgpool_name as stgpool, - date(fs.backup_start) as bkup_date - from occupancy oc, filespaces fs - where oc.node_name=fs.node_name - and oc.filespace_name=fs.filespace_name - and cast((current_timestamp-backup_start)days as integer)=183 - order by stgpool_name, oc.node_name, oc.filespace_name You can't do an outer join (whatever that is... I'm just learning this stuff...) Wanda Prather The Johns Hopkins Applied Physics Lab 443-778-8769 [EMAIL PROTECTED] Intelligence has much less practical application than you'd think - Scott Adams/Dilbert -Original Message- From: Peppers, Holly [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 12:43 PM To: [EMAIL PROTECTED] Subject: Select Stmts. in TSM Has anybody tried to do a join in the TSM tables? I went looking thru the help utility and the manuals, but did not see the syntax of how to do this. Let me know please. Thanks. Holly L. Peppers Blue Cross Blue Shield of Florida [EMAIL PROTECTED] Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
Re: Select Stmts. in TSM
For Joins go to oracle.com and do search on join u get tons of info using select statements. -Original Message- From: Peppers, Holly [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 11:43 AM To: [EMAIL PROTECTED] Subject: Select Stmts. in TSM Has anybody tried to do a join in the TSM tables? I went looking thru the help utility and the manuals, but did not see the syntax of how to do this. Let me know please. Thanks. Holly L. Peppers Blue Cross Blue Shield of Florida [EMAIL PROTECTED] Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
Re: Select Stmts. in TSM
Hey Wanda, If this does what I think it does check out the Tivoli supplied script fsnobackup. You can pass in any number you want to find filespaces with old backup dates. Example run fsnobackup 183. Results in seconds. Won't give you the amount of space but you will see the old stuff if you're looking to do some housekeeping. Curt Magura Lockheed Martin EIS Gaithersburg, Md. 301-240-6305 -Original Message- From: Prather, Wanda [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 1:02 PM To: [EMAIL PROTECTED] Subject: Re: Select Stmts. in TSM Here is a script that does a join; it's just std SQL syntax. /* Show filespaces not backed up in 6 months w/space on server */ /* This query runs a long time; suggest writing output to a file */ /* Also strongly recommend running in commadelimited mode*/ select oc.node_name, oc.filespace_name as filespace, - physical_mb as mbytes , stgpool_name as stgpool, - date(fs.backup_start) as bkup_date - from occupancy oc, filespaces fs - where oc.node_name=fs.node_name - and oc.filespace_name=fs.filespace_name - and cast((current_timestamp-backup_start)days as integer)=183 - order by stgpool_name, oc.node_name, oc.filespace_name You can't do an outer join (whatever that is... I'm just learning this stuff...) Wanda Prather The Johns Hopkins Applied Physics Lab 443-778-8769 [EMAIL PROTECTED] Intelligence has much less practical application than you'd think - Scott Adams/Dilbert -Original Message- From: Peppers, Holly [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 12:43 PM To: [EMAIL PROTECTED] Subject: Select Stmts. in TSM Has anybody tried to do a join in the TSM tables? I went looking thru the help utility and the manuals, but did not see the syntax of how to do this. Let me know please. Thanks. Holly L. Peppers Blue Cross Blue Shield of Florida [EMAIL PROTECTED] Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
Re: Select Stmts. in TSM
Yes, it is very possible. These are most of the supported verbs from the help. There are predicates that are not listed here. If you do a HELP SELECT you get this: The SELECT command supports the following expressions, clauses, functions, and predicates: ALLCURRENT_USERMAX ANYDISTINCTMIN AVGEXISTS NULL AS EXTRACT ORDER BY BETWEENFROMPOSITION CASE GROUP BYSOME CAST HAVING SUBSTRING COUNT IN SUM CURRENT_DATE JOINTRIM CURRENT_TIME LIKEWHERE CURRENT_TIMESTAMP -Original Message- From: taford [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 12:58 PM To: [EMAIL PROTECTED] Subject: Re: Select Stmts. in TSM I don't believe it's possible. I've researched this too. If anyone else has found otherwise, please let me know. Timothy A. Ford, MCSE LAN Services Micron Technology, Inc. -Original Message- From: Peppers, Holly [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 10:43 AM To: [EMAIL PROTECTED] Subject: Select Stmts. in TSM Has anybody tried to do a join in the TSM tables? I went looking thru the help utility and the manuals, but did not see the syntax of how to do this. Let me know please. Thanks. Holly L. Peppers Blue Cross Blue Shield of Florida [EMAIL PROTECTED] Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
Re: Select Stmts. in TSM
The only way I have been able to do this is buy using the ODBC driver and having an Access database join link tables. Bud Brown Information Services Systems Administrator 303-436-5986 -Original Message- From: Seay, Paul [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 1:13 PM To: [EMAIL PROTECTED] Subject: Re: Select Stmts. in TSM Yes, it is very possible. These are most of the supported verbs from the help. There are predicates that are not listed here. If you do a HELP SELECT you get this: The SELECT command supports the following expressions, clauses, functions, and predicates: ALLCURRENT_USERMAX ANYDISTINCTMIN AVGEXISTS NULL AS EXTRACT ORDER BY BETWEENFROMPOSITION CASE GROUP BYSOME CAST HAVING SUBSTRING COUNT IN SUM CURRENT_DATE JOINTRIM CURRENT_TIME LIKEWHERE CURRENT_TIMESTAMP -Original Message- From: taford [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 12:58 PM To: [EMAIL PROTECTED] Subject: Re: Select Stmts. in TSM I don't believe it's possible. I've researched this too. If anyone else has found otherwise, please let me know. Timothy A. Ford, MCSE LAN Services Micron Technology, Inc. -Original Message- From: Peppers, Holly [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 10:43 AM To: [EMAIL PROTECTED] Subject: Select Stmts. in TSM Has anybody tried to do a join in the TSM tables? I went looking thru the help utility and the manuals, but did not see the syntax of how to do this. Let me know please. Thanks. Holly L. Peppers Blue Cross Blue Shield of Florida [EMAIL PROTECTED] Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
Re: Select Stmts. in TSM: What is an OUTER JOIN
An OUTER join really does not apply to an application like TSM. There is a right, left and plain. An OUTER basically keeps the left or right select (or both) and joins what it can with the items that do not match having null results. -Original Message- From: Prather, Wanda [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 1:02 PM To: [EMAIL PROTECTED] Subject: Re: Select Stmts. in TSM Here is a script that does a join; it's just std SQL syntax. /* Show filespaces not backed up in 6 months w/space on server */ /* This query runs a long time; suggest writing output to a file */ /* Also strongly recommend running in commadelimited mode*/ select oc.node_name, oc.filespace_name as filespace, - physical_mb as mbytes , stgpool_name as stgpool, - date(fs.backup_start) as bkup_date - from occupancy oc, filespaces fs - where oc.node_name=fs.node_name - and oc.filespace_name=fs.filespace_name - and cast((current_timestamp-backup_start)days as integer)=183 - order by stgpool_name, oc.node_name, oc.filespace_name You can't do an outer join (whatever that is... I'm just learning this stuff...) Wanda Prather The Johns Hopkins Applied Physics Lab 443-778-8769 [EMAIL PROTECTED] Intelligence has much less practical application than you'd think - Scott Adams/Dilbert -Original Message- From: Peppers, Holly [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 12:43 PM To: [EMAIL PROTECTED] Subject: Select Stmts. in TSM Has anybody tried to do a join in the TSM tables? I went looking thru the help utility and the manuals, but did not see the syntax of how to do this. Let me know please. Thanks. Holly L. Peppers Blue Cross Blue Shield of Florida [EMAIL PROTECTED] Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
