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...)
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:\file1                classA
d:\file2                classB
e:\file3                <NULL>

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:\file1        10
d:\file2        8

Outer join will give us:
select file_name, retension
        from t1, outer join t2
        where t1.mgmt_class = t2.class_name
output:
c:\file1        10
d:\file2        8
e:\file3        <NULL>

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,6&7). 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" <ADSM-L AT VM.MARIST DOT EDU>
Sent by:        "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
To:     ADSM-L AT VM.MARIST DOT EDU
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
wanda_prather AT jhuapl DOT edu

"Intelligence has much less practical application than you'd think" -
Scott Adams/Dilbert
************************************************************************