Hi,

I have noticed a lot of strangeness about the collocgroup table in sql.

Consider these 2 queries -

select count(*) from collocgroup

 Unnamed[1]
-----------
         10

select count(node_name) from collocgroup

 Unnamed[1]
-----------
        183

Pretty weird, huh?

What you expect from an sql table is this -

select count(*) from nodes

 Unnamed[1]
-----------
        184

select count(node_name) from nodes

 Unnamed[1]
-----------
        184


Bill Colwell


> -----Original Message-----
> From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU]
> On Behalf Of Andrew Raibeck
> Sent: Friday, August 26, 2005 7:50 AM
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: Re: SQL query problem on collocgroup table
>
> Hi Arnaud,
>
> It's already been relayed.
>
> Best regards,
>
> Andy
>
> Andy Raibeck
> IBM Software Group
> Tivoli Storage Manager Client Development
> Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
> Internet e-mail: storman AT us.ibm DOT com
>
> The only dumb question is the one that goes unasked.
> The command line is your friend.
> "Good enough" is the enemy of excellence.
>
> "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 2005-08-26
> 04:35:10:
>
> > Andy,
> >
> > Thanks for your response, and yes, this collocgroup is the first one
> > displayed while running your query.
> > Thought it was a bug too, but wanted to make it sure first !
> > Will you relay this to development team, or shall I open a PMR ?
> > Cheers.
> >
> >
> > Arnaud
> >
> >
> **************************************************************
> **********
> > ******
> > Panalpina Management Ltd., Basle, Switzerland, CIT Department
> > Viadukstrasse 42, P.O. Box 4002 Basel/CH
> > Phone:  +41 (61) 226 11 11, FAX: +41 (61) 226 17 01
> > Direct: +41 (61) 226 19 78
> > e-mail: arnaud.brion AT panalpina DOT com
> >
> **************************************************************
> **********
> > ******
> >
> > -----Original Message-----
> > From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU]
> On Behalf Of
> > Andrew Raibeck
> > Sent: Friday, 26 August, 2005 13:08
> > To: ADSM-L AT VM.MARIST DOT EDU
> > Subject: Re: SQL query problem on collocgroup table
> >
> > Well, well, well, this looks like a bona fide bug to me. Is
> AIX_0_6 the
> > first collocation group name in the output from:
> >
> >    select collocgroup_name from collocgroup
> >
> > If so, this would be consistent with my findings (though I
> cannot say I
> > have looked at this exhaustively): your script works only when the
> > collocation group name is the first one in the list.
> >
> > As a test, try running this:
> >
> >    select node_name, collocgroup_name from collocgroup where
> > collocgroup_name in ('AIX_INACTIVE')
> >
> > that should work, though I realize it isn't what you really want.
> >
> > Andy Raibeck
> > IBM Software Group
> > Tivoli Storage Manager Client Development Internal Notes
> e-mail: Andrew
> > Raibeck/Tucson/IBM@IBMUS Internet e-mail: storman AT us.ibm DOT com
> >
> > The only dumb question is the one that goes unasked.
> > The command line is your friend.
> > "Good enough" is the enemy of excellence.
> >
> > "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 2005-08-26
> > 02:12:31:
> >
> > > Hi all,
> > >
> > > I'm facing some strange SQL problem while querying
> COLLOCGROUP table,
> > > and can't find out what is happening here ...
> > > I've built a script (q_collocgroup) which looks like :
> > >
> > > select collocgroup_name from collocgroup where
> node_name=upper('$1')
> > > select node_name, collocgroup_name from collocgroup where
> > > collocgroup_name in (select collocgroup_name from
> collocgroup where
> > > node_name=upper('$1'))
> > >
> > > For some nodes, the scripts works perfectly :
> > >
> > > run q_collocgroup asbrs000
> > >
> > > COLLOCGROUP_NAME
> > > ------------------
> > > AIX_0_6
> > >
> > > NODE_NAME              COLLOCGROUP_NAME
> > > ------------------     ------------------
> > > ASBRS000               AIX_0_6
> > > PACRS070               AIX_0_6
> > > PACSP030               AIX_0_6
> > >
> > >
> > > For other ones, the second query fails :
> > >
> > > run q_collocgroup old_pacrs111
> > >
> > > COLLOCGROUP_NAME
> > > ------------------
> > > AIX_INACTIVE
> > > ANR2034E SELECT: No match found using this criteria.
> > > ANR1462I RUN: Command script Q_COLLOCGROUP completed successfully.
> > >
> > > I tried an equivalent query :
> > > (SELECT COLLOCGROUP.NODE_NAME, COLLOCGROUP.COLLOCGROUP_NAME FROM
> > > COLLOCGROUP WHERE (((COLLOCGROUP.COLLOCGROUP_NAME)=(SELECT
> > > COLLOCGROUP.COLLOCGROUP_NAME FROM COLLOCGROUP WHERE
> > > (((COLLOCGROUP.NODE_NAME)=[enter node_name]))))); using
> MS Access and
> > > ODBC driver, with the same problem
> > >
> > > Does someone have an idea of what is happening here ?
> > > Thanks in advance !
> > > Cheers.
> > >
> > > Arnaud
> > >
> > >
> **********************************************************************
> > > **
> > > ******
> > > Panalpina Management Ltd., Basle, Switzerland, CIT Department
> > > Viadukstrasse 42, P.O. Box 4002 Basel/CH
> > > Phone:  +41 (61) 226 11 11, FAX: +41 (61) 226 17 01
> > > Direct: +41 (61) 226 19 78
> > > e-mail: arnaud.brion AT panalpina DOT com
> > >
> **********************************************************************
> > > **
> > > ******