Query to list indexes (and the tables they belong
Dirk (IDS 11.7 on AIX 6) wanted a query listing detached indexes and their parent tables per dbspace, but joining across two databases gave error 568 "Cannot reference an external database without logging." Doug Lawry supplied a query using only sysmaster tables (systabnames joined to sysptntab via partnum/tablock, with DBINFO('dbspace', partnum)), so no cross-database reference to an unlogged DB is needed. Fernando noted the direction matters: connect to the user database and reference sysmaster, not the reverse. Dirk thanked the group; a working approach was given.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.7 / AIX 6 I was working on a query to list indexes *and the tables they belong to* in a specific dbspace. But because my query ran over 2 databases, I got this message: "568: Cannot reference an external database without logging." Does anyone have a query I can copy ? All I want is: - specify the dbspace - then return the indexnames and the tables they belong to (for that dbspace specified). Dirk NOTE: This e-mail message is subject to the MTN Group disclaimer see http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
PS. our indexes are detached, so that is why I am looking for a query to list the indexes that are sitting in their own dbspaces. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Dirk Cornel.... > Sent: Tuesday, 21 August 2012 10:43 AM > To: ids@iiug.org > Subject: Query to list indexes (and the tables they bel.... [28088] > > IDS 11.7 / AIX 6 > > I was working on a query to list indexes *and the tables they belong > to* in a > specific dbspace. But because my query ran over 2 databases, I got this > message: > > "568: Cannot reference an external database without logging." > > Does anyone have a query I can copy ? All I want is: > > - specify the dbspace > - then return the indexnames and the tables they belong to (for that > dbspace > specified). > > Dirk > > NOTE: This e-mail message is subject to the MTN Group disclaimer see > http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum. > NOTE: This e-mail message is subject to the MTN Group disclaimer see http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
Try this:
SELECT x0.dbsname,
x2.tabname,
CASE WHEN x0.tabname != x2.tabname THEN x0.tabname END AS idxname,
DBINFO('dbspace', x0.partnum) AS dbspace
FROM sysmaster:systabnames x0,
sysmaster:sysptntab x1,
sysmaster:systabnames x2
WHERE x1.partnum = x0.partnum
AND x2.partnum = x1.tablock
AND x0.dbsname MATCHES '[a-z]*'
AND x2.tabname MATCHES '[a-z]*'
AND x0.dbsname NOT MATCHES 'sys*'
AND x2.tabname NOT MATCHES 'sys*'
Regards,
Doug Lawry
But you cannot use that in an unlogged database.
Art
Sent from my Galaxy S®IIIDOUG LAWRY <douglawry@hotmail.com> wrote:Try this:
SELECT x0.dbsname,
x2.tabname,
CASE WHEN x0.tabname != x2.tabname THEN x0.tabname END AS idxname,
DBINFO('dbspace', x0.partnum) AS dbspace
FROM sysmaster:systabnames x0,
sysmaster:sysptntab x1,
sysmaster:systabnames x2
WHERE x1.partnum = x0.partnum
AND x2.partnum = x1.tablock
AND x0.dbsname MATCHES '[a-z]*'
AND x2.tabname MATCHES '[a-z]*'
AND x0.dbsname NOT MATCHES 'sys*'
AND x2.tabname NOT MATCHES 'sys*'
Regards,
Doug Lawry
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
It's only referencing sysmaster, Art, so can be run while connected to sysmaster regardless. By the way, why did the indentation get removed from my SQL by the IDS Forum site and some extra line feeds inserted?!
Oops, sorry, I was on my phone and thought you were joining <somelocaldb>:sysindices to sysmaster:systabnames when I saw idxname. On the reformatting, don't know. Something in the email parser, but what? Dunno. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 21, 2012 at 9:30 AM, DOUG LAWRY <douglawry@hotmail.com> wrote: > It's only referencing sysmaster, Art, so can be run while connected to > sysmaster regardless. > > By the way, why did the indentation get removed from my SQL by the IDS > Forum > site and some extra line feeds inserted?! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9341119a7405604c7c6d0e9
The 3 tables used in the SQL are in sysmaster
Cheers
Paul
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art S.
Kagel
Sent: Tuesday, August 21, 2012 7:46 AM
To: ids@iiug.org
Subject: RE: Query to list indexes (and the tables they bel [28091]
But you cannot use that in an unlogged database.
Art
Sent from my Galaxy SRIIIDOUG LAWRY <douglawry@hotmail.com> wrote:Try this:
SELECT x0.dbsname,
x2.tabname,
CASE WHEN x0.tabname != x2.tabname THEN x0.tabname END AS idxname,
DBINFO('dbspace', x0.partnum) AS dbspace
FROM sysmaster:systabnames x0,
sysmaster:sysptntab x1,
sysmaster:systabnames x2
WHERE x1.partnum = x0.partnum
AND x2.partnum = x1.tablock
AND x0.dbsname MATCHES '[a-z]*'
AND x2.tabname MATCHES '[a-z]*'
AND x0.dbsname NOT MATCHES 'sys*'
AND x2.tabname NOT MATCHES 'sys*'
Regards,
Doug Lawry
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
I suppose you're connected to sysmaster and referencing another database...
something like:
database sysmaster:
select .... from systabnames, other_database:systables...
if you do the opposite:
database other_database:
select .... from systables, sysmaster:systabnames
it will work.
But I suppose you'll get better answers since what you want to do is
possible to do only with sysmaster so it will work for any database(s) you
may have.
Regards.
On Tue, Aug 21, 2012 at 9:42 AM, Dirk Cornel.... <moolma_dc@mtn.co.za>wrote:
> IDS 11.7 / AIX 6
>
> I was working on a query to list indexes *and the tables they belong to*
> in a
> specific dbspace. But because my query ran over 2 databases, I got this
> message:
>
> "568: Cannot reference an external database without logging."
>
> Does anyone have a query I can copy ? All I want is:
>
> - specify the dbspace
> - then return the indexnames and the tables they belong to (for that
> dbspace
> specified).
>
> Dirk
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--485b397dce03c83bd204c7c7fc19
Thanks everyone. I will play around a bit more .....
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Fernando Nunes
> Sent: Tuesday, 21 August 2012 05:08 PM
> To: ids@iiug.org
> Subject: Re: Query to list indexes (and the tables they.... [28095]
>
> I suppose you're connected to sysmaster and referencing another
> database...
> something like:
>
> database sysmaster:
> select .... from systabnames, other_database:systables...>
> if you do the opposite:
>
> database other_database:
>
> select .... from systables, sysmaster:systabnames>
> it will work.
> But I suppose you'll get better answers since what you want to do is
> possible to do only with sysmaster so it will work for any database(s)
> you
> may have.
>
> Regards.
>
> On Tue, Aug 21, 2012 at 9:42 AM, Dirk Cornel....
> <moolma_dc@mtn.co.za>wrote:
>
> > IDS 11.7 / AIX 6
> >
> > I was working on a query to list indexes *and the tables they belong
> to*
> > in a
> > specific dbspace. But because my query ran over 2 databases, I got
> this
> > message:
> >
> > "568: Cannot reference an external database without logging."
> >
> > Does anyone have a query I can copy ? All I want is:
> >
> > - specify the dbspace
> > - then return the indexnames and the tables they belong to (for that
> > dbspace
> > specified).
> >
> > Dirk
> >
> > NOTE: This e-mail message is subject to the MTN Group disclaimer see
> > http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> >
> >
> >
> >
> ***********************************************************************
> ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --485b397dce03c83bd204c7c7fc19
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
PS. very interesting, i did now know. Thanks Fernando.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Fernando Nunes
> Sent: Tuesday, 21 August 2012 05:08 PM
> To: ids@iiug.org
> Subject: Re: Query to list indexes (and the tables they.... [28095]
>
> I suppose you're connected to sysmaster and referencing another
> database...
> something like:
>
> database sysmaster:
> select .... from systabnames, other_database:systables...>
> if you do the opposite:
>
> database other_database:
>
> select .... from systables, sysmaster:systabnames>
> it will work.
> But I suppose you'll get better answers since what you want to do is
> possible to do only with sysmaster so it will work for any database(s)
> you
> may have.
>
> Regards.
>
> On Tue, Aug 21, 2012 at 9:42 AM, Dirk Cornel....
> <moolma_dc@mtn.co.za>wrote:
>
> > IDS 11.7 / AIX 6
> >
> > I was working on a query to list indexes *and the tables they belong
> to*
> > in a
> > specific dbspace. But because my query ran over 2 databases, I got
> this
> > message:
> >
> > "568: Cannot reference an external database without logging."
> >
> > Does anyone have a query I can copy ? All I want is:
> >
> > - specify the dbspace
> > - then return the indexnames and the tables they belong to (for that
> > dbspace
> > specified).
> >
> > Dirk
> >
> > NOTE: This e-mail message is subject to the MTN Group disclaimer see
> > http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> >
> >
> >
> >
> ***********************************************************************
> ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --485b397dce03c83bd204c7c7fc19
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
NOTE: This e-mail message is subject to the MTN Group disclaimer see
http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
I see what you mean. The same happened to my last post now. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > DOUG LAWRY > Sent: Tuesday, 21 August 2012 03:30 PM > To: ids@iiug.org > Subject: Re: RE: Query to list indexes (and the tables .... [28092] > [snip] > > By the way, why did the indentation get removed from my SQL by the IDS > Forum > site and some extra line feeds inserted?! > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum. > NOTE: This e-mail message is subject to the MTN Group disclaimer see http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
That's one (of many) particularity of the sysmaster database.
Technically there is no reason why you couldn't run statements across
databases with different logging (ANSI could be an exception to this).
The reason is more conceptual... If you open a transaction how would the
non-logging part deal with it?
But given the nature of sysmaster the limitation was not implemented on
that direction.
Regards.
On Wed, Aug 22, 2012 at 3:24 PM, Dirk Cornel.... <moolma_dc@mtn.co.za>wrote:
> PS. very interesting, i did now know. Thanks Fernando.
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Fernando Nunes
> > Sent: Tuesday, 21 August 2012 05:08 PM
> > To: ids@iiug.org
> > Subject: Re: Query to list indexes (and the tables they.... [28095]
> >
> > I suppose you're connected to sysmaster and referencing another
> > database...
> > something like:
> >
> > database sysmaster:
> > select .... from systabnames, other_database:systables...> >
> > if you do the opposite:
> >
> > database other_database:
> >
> > select .... from systables, sysmaster:systabnames> >
> > it will work.
> > But I suppose you'll get better answers since what you want to do is
> > possible to do only with sysmaster so it will work for any database(s)
> > you
> > may have.
> >
> > Regards.
> >
> > On Tue, Aug 21, 2012 at 9:42 AM, Dirk Cornel....
> > <moolma_dc@mtn.co.za>wrote:
> >
> > > IDS 11.7 / AIX 6
> > >
> > > I was working on a query to list indexes *and the tables they belong
> > to*
> > > in a
> > > specific dbspace. But because my query ran over 2 databases, I got
> > this
> > > message:
> > >
> > > "568: Cannot reference an external database without logging."
> > >
> > > Does anyone have a query I can copy ? All I want is:
> > >
> > > - specify the dbspace
> > > - then return the indexnames and the tables they belong to (for that
> > > dbspace
> > > specified).
> > >
> > > Dirk
> > >
> > > NOTE: This e-mail message is subject to the MTN Group disclaimer see
> > > http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> > >
> > >
> > >
> > >
> > ***********************************************************************
> > ********
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --
> > Fernando Nunes
> > Portugal
> >
> > http://informix-technology.blogspot.com
> > My email works... but I don't check it frequently...
> >
> > --485b397dce03c83bd204c7c7fc19
> >
> >
> > ***********************************************************************
> > ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--bcaec517ab5e53262c04c7dc6147