Query to list temporary tables
The poster wanted an SQL query listing temporary tables with owner and dbspace. John Miller supplied a join of sysmaster:sysptnhdr and sysmaster:systabnames filtered with bitand(flags,64). On IDS 10 this failed with error 674 (routine bitand cannot be resolved) because bitand was only added in 11.10; Fernando Nunes suggested using bitval instead, which made the query run. It returned no rows because it only catches user temp tables; Miller listed other flag bits (0x20 system temp, 0x40 user temp, 0x80 sort files) and Nunes offered an expanded bitval version, noting that tracking what fills temp space needs much more work.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Does anyone here have an sql query for me, for selecting temporary tables, their owner, and the dbspace they reside in ? Dirk ________________________________ NOTE: This e-mail message is subject to the MTN Group disclaimer see http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
How about: select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) , sysmaster:partdbsnum(T.partnum) dbspace_number, dbinfo('dbspace',T.partnum) dbspace_name from sysmaster:sysptnhdr P, sysmaster:systabnames T where P.partnum = T. partnum AND bitand(flags, 64)>0 John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 06/13/2011 11:16:21 AM: > [image removed] > > Query to list temporary tables [24016] > > Dirk Cornel.... > > to: > > ids > > 06/13/2011 11:18 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Does anyone here have an sql query for me, for selecting temporary tables, > their owner, and the dbspace they reside in ? > > 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 that if you're looking for who is filling up your temporary tablespaces, it will take much more than this... Regards On Mon, Jun 13, 2011 at 7:55 PM, John Miller iii <miller3@us.ibm.com> wrote: > How about: > > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) , > sysmaster:partdbsnum(T.partnum) dbspace_number, dbinfo('dbspace',T.partnum) > dbspace_name > from > sysmaster:sysptnhdr P, sysmaster:systabnames T > where > P.partnum = T. partnum > AND bitand(flags, 64)>0 > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 06/13/2011 11:16:21 AM: > > > [image removed] > > > > Query to list temporary tables [24016] > > > > Dirk Cornel.... > > > > to: > > > > ids > > > > 06/13/2011 11:18 AM > > > > Sent by: > > > > ids-bounces@iiug.org > > > > Please respond to ids > > > > Does anyone here have an sql query for me, for selecting temporary > tables, > > their owner, and the dbspace they reside in ? > > > > 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. > > > > > > ******************************************************************************* > 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... --0016368e1aec4a4a3604a59cc4f5
I am on IDS 10 - maybe this is not supported ? When running the query:
674: Routine (bitand) can not be resolved.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> John Miller iii
> Sent: Monday, 13 June 2011 08:55 PM
> To: ids@iiug.org
> Subject: Re: Query to list temporary tables [24017]
>
> How about:
>
> select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
> sysmaster:partdbsnum(T.partnum) dbspace_number,
> dbinfo('dbspace',T.partnum)> dbspace_name
> from
> sysmaster:sysptnhdr P, sysmaster:systabnames T
> where
> P.partnum = T. partnum
> AND bitand(flags, 64)>0
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 06/13/2011 11:16:21 AM:
>
> > [image removed]
> >
> > Query to list temporary tables [24016]
> >
> > Dirk Cornel....
> >
> > to:
> >
> > ids
> >
> > 06/13/2011 11:18 AM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > Does anyone here have an sql query for me, for selecting temporary
> tables,
> > their owner, and the dbspace they reside in ?
> >
> > 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.
> >
>
>
> ***********************************************************************
> ********
> 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
bitand is on sysmaster.
So, if you're not connected to sysmaster prefix it with "sysmaster:"
Regards
On Tue, Jun 14, 2011 at 9:57 AM, Dirk Cornel.... <moolma_dc@mtn.co.za>wrote:
> I am on IDS 10 - maybe this is not supported ? When running the query:
>
> 674: Routine (bitand) can not be resolved.>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > John Miller iii
> > Sent: Monday, 13 June 2011 08:55 PM
> > To: ids@iiug.org
> > Subject: Re: Query to list temporary tables [24017]
> >
> > How about:
> >
> > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
> > sysmaster:partdbsnum(T.partnum) dbspace_number,
> > dbinfo('dbspace',T.partnum)> > dbspace_name
> > from
> > sysmaster:sysptnhdr P, sysmaster:systabnames T
> > where
> > P.partnum = T. partnum
> > AND bitand(flags, 64)>0
> >
> > John F. Miller III
> > STSM, Embedability Architect
> > miller3@us.ibm.com
> > 503-578-5645
> > IBM Informix Dynamic Server (IDS)
> >
> > ids-bounces@iiug.org wrote on 06/13/2011 11:16:21 AM:
> >
> > > [image removed]
> > >
> > > Query to list temporary tables [24016]
> > >
> > > Dirk Cornel....
> > >
> > > to:
> > >
> > > ids
> > >
> > > 06/13/2011 11:18 AM
> > >
> > > Sent by:
> > >
> > > ids-bounces@iiug.org
> > >
> > > Please respond to ids
> > >
> > > Does anyone here have an sql query for me, for selecting temporary
> > tables,
> > > their owner, and the dbspace they reside in ?
> > >
> > > 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.
> > >
> >
> >
> > ***********************************************************************
> > ********
> > 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...
--001636c5bd691cd30004a5a8ce5f
Unless I'm making a typo ......
select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
sysmaster:partdbsnum(T.partnum) dbspace_number,
dbinfo('dbspace',T.partnum) dbspace_name
from sysmaster:sysptnhdr P,sysmaster:systabnames T where P.partnum = T. partnum
AND sysmaster:bitand(flags, 64)>0
# ^
# 674: Routine (bitand) can not be resolved.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Fernando Nunes
> Sent: Tuesday, 14 June 2011 11:39 AM
> To: ids@iiug.org
> Subject: Re: Query to list temporary tables [24028]
>
> bitand is on sysmaster.
> So, if you're not connected to sysmaster prefix it with "sysmaster:"
>
> Regards
>
> On Tue, Jun 14, 2011 at 9:57 AM, Dirk Cornel....
> <moolma_dc@mtn.co.za>wrote:
>
> > I am on IDS 10 - maybe this is not supported ? When running the
> query:
> >
> > 674: Routine (bitand) can not be resolved.> >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> Of
> > > John Miller iii
> > > Sent: Monday, 13 June 2011 08:55 PM
> > > To: ids@iiug.org
> > > Subject: Re: Query to list temporary tables [24017]
> > >
> > > How about:
> > >
> > > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
> > > sysmaster:partdbsnum(T.partnum) dbspace_number,
> > > dbinfo('dbspace',T.partnum)> > > dbspace_name
> > > from
> > > sysmaster:sysptnhdr P, sysmaster:systabnames T
> > > where
> > > P.partnum = T. partnum
> > > AND bitand(flags, 64)>0
> > >
> > > John F. Miller III
> > > STSM, Embedability Architect
> > > miller3@us.ibm.com
> > > 503-578-5645
> > > IBM Informix Dynamic Server (IDS)
> > >
> > > ids-bounces@iiug.org wrote on 06/13/2011 11:16:21 AM:
> > >
> > > > [image removed]
> > > >
> > > > Query to list temporary tables [24016]
> > > >
> > > > Dirk Cornel....
> > > >
> > > > to:
> > > >
> > > > ids
> > > >
> > > > 06/13/2011 11:18 AM
> > > >
> > > > Sent by:
> > > >
> > > > ids-bounces@iiug.org
> > > >
> > > > Please respond to ids
> > > >
> > > > Does anyone here have an sql query for me, for selecting
> temporary
> > > tables,
> > > > their owner, and the dbspace they reside in ?
> > > >
> > > > 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.
> > > >
> > >
> > >
> > >
> ***********************************************************************
> > > ********
> > > 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...
>
> --001636c5bd691cd30004a5a8ce5f
>
>
> ***********************************************************************
> ********
> 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
True... sorry.
bitand is not present on v10.
I was confusing with bitval. I believe you can try with bitval instead...
Regards.
On Tue, Jun 14, 2011 at 1:52 PM, Dirk Cornel.... <moolma_dc@mtn.co.za>wrote:
> Unless I'm making a typo ......
>
> select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
> sysmaster:partdbsnum(T.partnum) dbspace_number,
> dbinfo('dbspace',T.partnum) dbspace_name
> from sysmaster:sysptnhdr P,> sysmaster:systabnames T where P.partnum = T. partnum
> AND sysmaster:bitand(flags, 64)>0
> # ^
> # 674: Routine (bitand) can not be resolved.
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Fernando Nunes
> > Sent: Tuesday, 14 June 2011 11:39 AM
> > To: ids@iiug.org
> > Subject: Re: Query to list temporary tables [24028]
> >
> > bitand is on sysmaster.
> > So, if you're not connected to sysmaster prefix it with "sysmaster:"
> >
> > Regards
> >
> > On Tue, Jun 14, 2011 at 9:57 AM, Dirk Cornel....
> > <moolma_dc@mtn.co.za>wrote:
> >
> > > I am on IDS 10 - maybe this is not supported ? When running the
> > query:
> > >
> > > 674: Routine (bitand) can not be resolved.> > >
> > > > -----Original Message-----
> > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > Of
> > > > John Miller iii
> > > > Sent: Monday, 13 June 2011 08:55 PM
> > > > To: ids@iiug.org
> > > > Subject: Re: Query to list temporary tables [24017]
> > > >
> > > > How about:
> > > >
> > > > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
> > > > sysmaster:partdbsnum(T.partnum) dbspace_number,
> > > > dbinfo('dbspace',T.partnum)> > > > dbspace_name
> > > > from
> > > > sysmaster:sysptnhdr P, sysmaster:systabnames T
> > > > where
> > > > P.partnum = T. partnum
> > > > AND bitand(flags, 64)>0
> > > >
> > > > John F. Miller III
> > > > STSM, Embedability Architect
> > > > miller3@us.ibm.com
> > > > 503-578-5645
> > > > IBM Informix Dynamic Server (IDS)
> > > >
> > > > ids-bounces@iiug.org wrote on 06/13/2011 11:16:21 AM:
> > > >
> > > > > [image removed]
> > > > >
> > > > > Query to list temporary tables [24016]
> > > > >
> > > > > Dirk Cornel....
> > > > >
> > > > > to:
> > > > >
> > > > > ids
> > > > >
> > > > > 06/13/2011 11:18 AM
> > > > >
> > > > > Sent by:
> > > > >
> > > > > ids-bounces@iiug.org
> > > > >
> > > > > Please respond to ids
> > > > >
> > > > > Does anyone here have an sql query for me, for selecting
> > temporary
> > > > tables,
> > > > > their owner, and the dbspace they reside in ?
> > > > >
> > > > > 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.
> > > > >
> > > >
> > > >
> > > >
> > ***********************************************************************
> > > > ********
> > > > 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...
> >
> > --001636c5bd691cd30004a5a8ce5f
> >
> >
> > ***********************************************************************
> > ********
> > 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...
--001636ed6e7e33b5ca04a5aca04c
Thank you, the query runs now. The strange thing is, it is coming back with
"no rows found" - I'm sure there must be temporary tables on our database ...
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Fernando Nunes
> Sent: Tuesday, 14 June 2011 04:13 PM
> To: ids@iiug.org
> Subject: Re: Query to list temporary tables [24030]
>
> True... sorry.
> bitand is not present on v10.
> I was confusing with bitval. I believe you can try with bitval
> instead...
> Regards.
>
> On Tue, Jun 14, 2011 at 1:52 PM, Dirk Cornel....
> <moolma_dc@mtn.co.za>wrote:
>
> > Unless I'm making a typo ......
> >
> > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
> > sysmaster:partdbsnum(T.partnum) dbspace_number,
> > dbinfo('dbspace',T.partnum) dbspace_name
> > from sysmaster:sysptnhdr P,> > sysmaster:systabnames T where P.partnum = T. partnum
> > AND sysmaster:bitand(flags, 64)>0
> > # ^
> > # 674: Routine (bitand) can not be resolved.
> >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> Of
> > > Fernando Nunes
> > > Sent: Tuesday, 14 June 2011 11:39 AM
> > > To: ids@iiug.org
> > > Subject: Re: Query to list temporary tables [24028]
> > >
> > > bitand is on sysmaster.
> > > So, if you're not connected to sysmaster prefix it with
> "sysmaster:"
> > >
> > > Regards
> > >
> > > On Tue, Jun 14, 2011 at 9:57 AM, Dirk Cornel....
> > > <moolma_dc@mtn.co.za>wrote:
> > >
> > > > I am on IDS 10 - maybe this is not supported ? When running the
> > > query:
> > > >
> > > > 674: Routine (bitand) can not be resolved.> > > >
> > > > > -----Original Message-----
> > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> Behalf
> > > Of
> > > > > John Miller iii
> > > > > Sent: Monday, 13 June 2011 08:55 PM
> > > > > To: ids@iiug.org
> > > > > Subject: Re: Query to list temporary tables [24017]
> > > > >
> > > > > How about:
> > > > >
> > > > > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
> > > > > sysmaster:partdbsnum(T.partnum) dbspace_number,
> > > > > dbinfo('dbspace',T.partnum)> > > > > dbspace_name
> > > > > from
> > > > > sysmaster:sysptnhdr P, sysmaster:systabnames T
> > > > > where
> > > > > P.partnum = T. partnum
> > > > > AND bitand(flags, 64)>0
> > > > >
> > > > > John F. Miller III
> > > > > STSM, Embedability Architect
> > > > > miller3@us.ibm.com
> > > > > 503-578-5645
> > > > > IBM Informix Dynamic Server (IDS)
> > > > >
> > > > > ids-bounces@iiug.org wrote on 06/13/2011 11:16:21 AM:
> > > > >
> > > > > > [image removed]
> > > > > >
> > > > > > Query to list temporary tables [24016]
> > > > > >
> > > > > > Dirk Cornel....
> > > > > >
> > > > > > to:
> > > > > >
> > > > > > ids
> > > > > >
> > > > > > 06/13/2011 11:18 AM
> > > > > >
> > > > > > Sent by:
> > > > > >
> > > > > > ids-bounces@iiug.org
> > > > > >
> > > > > > Please respond to ids
> > > > > >
> > > > > > Does anyone here have an sql query for me, for selecting
> > > temporary
> > > > > tables,
> > > > > > their owner, and the dbspace they reside in ?
> > > > > >
> > > > > > 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.
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> ***********************************************************************
> > > > > ********
> > > > > 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...
> > >
> > > --001636c5bd691cd30004a5a8ce5f
> > >
> > >
> > >
> ***********************************************************************
> > > ********
> > > 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...
>
> --001636ed6e7e33b5ca04a5aca04c
>
>
> ***********************************************************************
> ********
> 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
The function bitand which is used was first introduced in 11.10. This
select will only find user created temp tables. If you want other type=
s of
temp tables then you will have to add the following:
0x20 system temp tables
0x40 user temp tables
0x80 sort files and some system temp space
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
|------------>
| From: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
---|
|"Dirk Cornel...." <moolma_dc@mtn.co.za> =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
---|
|------------>
| To: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
---|
|ids@iiug.org =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
---|
|------------>
| Date: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
---|
|06/14/2011 07:33 AM =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
---|
|------------>
| Subject: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
---|
|RE: Query to list temporary tables [24031] =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
---|
|------------>
| Sent by: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
---|
|ids-bounces@iiug.org =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
---|
Thank you, the query runs now. The strange thing is, it is coming back =
with
"no rows found" - I'm sure there must be temporary tables on our
database ...
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of=
> Fernando Nunes
> Sent: Tuesday, 14 June 2011 04:13 PM
> To: ids@iiug.org
> Subject: Re: Query to list temporary tables [24030]
>
> True... sorry.
> bitand is not present on v10.
> I was confusing with bitval. I believe you can try with bitval
> instead...
> Regards.
>
> On Tue, Jun 14, 2011 at 1:52 PM, Dirk Cornel....
> <moolma_dc@mtn.co.za>wrote:
>
> > Unless I'm making a typo ......
> >
> > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
> > sysmaster:partdbsnum(T.partnum) dbspace_number,
> > dbinfo('dbspace',T.partnum) dbspace_name
> > from sysmaster:sysptnhdr P,> > sysmaster:systabnames T where P.partnum =3D T. partnum
> > AND sysmaster:bitand(flags, 64)>0
> > # ^
> > # 674: Routine (bitand) can not be resolved.
> >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behal=
f
> Of
> > > Fernando Nunes
> > > Sent: Tuesday, 14 June 2011 11:39 AM
> > > To: ids@iiug.org
> > > Subject: Re: Query to list temporary tables [24028]
> > >
> > > bitand is on sysmaster.
> > > So, if you're not connected to sysmaster prefix it with
> "sysmaster:"
> > >
> > > Regards
> > >
> > > On Tue, Jun 14, 2011 at 9:57 AM, Dirk Cornel....
> > > <moolma_dc@mtn.co.za>wrote:
> > >
> > > > I am on IDS 10 - maybe this is not supported ? When running the=
> > > query:
> > > >
> > > > 674: Routine (bitand) can not be resolved.> > > >
> > > > > -----Original Message-----
> > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> Behalf
> > > Of
> > > > > John Miller iii
> > > > > Sent: Monday, 13 June 2011 08:55 PM
> > > > > To: ids@iiug.org
> > > > > Subject: Re: Query to list temporary tables [24017]
> > > > >
> > > > > How about:
> > > > >
> > > > > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
> > > > > sysmaster:partdbsnum(T.partnum) dbspace_number,
> > > > > dbinfo('dbspace',T.partnum)> > > > > dbspace_name
> > > > > from
> > > > > sysmaster:sysptnhdr P, sysmaster:systabnames T
> > > > > where
> > > > > P.partnum =3D T. partnum
> > > > > AND bitand(flags, 64)>0
> > > > >
> > > > > John F. Miller III
> > > > > STSM, Embedability Architect
> > > > > miller3@us.ibm.com
> > > > > 503-578-5645
> > > > > IBM Informix Dynamic Server (IDS)
> > > > >
> > > > > ids-bounces@iiug.org wrote on 06/13/2011 11:16:21 AM:
> > > > >
> > > > > > [image removed]
> > > > > >
> > > > > > Query to list temporary tables [24016]
> > > > > >
> > > > > > Dirk Cornel....
> > > > > >
> > > > > > to:
> > > > > >
> > > > > > ids
> > > > > >
> > > > > > 06/13/2011 11:18 AM
> > > > > >
> > > > > > Sent by:
> > > > > >
> > > > > > ids-bounces@iiug.org
> > > > > >
> > > > > > Please respond to ids
> > > > > >
> > > > > > Does anyone here have an sql query for me, for selecting
> > > temporary
> > > > > tables,
> > > > > > their owner, and the dbspace they reside in ?
> > > > > >
> > > > > > Dirk
> > > > > >
> > > > > > ________________________________
> > > > > > NOTE: This e-mail message is subject to the MTN Group
> disclaimer
> > > see
> > > > > > http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.as=
px
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > >
> *********************************************************************=
**
> > > > > ********
> > > > >
> > > > > > Forum Note: Use "Reply" to post a response in the discussio=
n
> > > forum.
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> *********************************************************************=
**
> > > > > ********
> > > > > Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > >
> > > > NOTE: This e-mail message is subject to the MTN Group disclaime=
r
> 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
>
You can try to create one in your session prior to run the query.
After that you can try to re-write the query as:
select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
sysmaster:partdbsnum(T.partnum) dbspace_number,
dbinfo('dbspace',T.partnum)dbspace_name
from
sysmaster:sysptnhdr P, sysmaster:systabnames T
where
P.partnum = T. partnum
AND
(
bitval(flags, 64) >0 OR
bitval(flags, 32) >0 OR
bitval(flags, 128) >0 OR
bitval(flags, 16384) >0 OR
bitval(flags, 131072 >0
)
This may give you some more "temp" objects.
Please check the "INSERT INTO flags_text values ('sysptnhdr',....); lines in
your $INFORMIXDIR/etc/sysmaster.sql file to understand the meaning of this.
But AFAIK the query you had should give you the temporary tables. As for the
other objects, as I wrote before, there's much more to it, and AFAIK there
is currently no way to get this in a simple manner.
Regards.
On Tue, Jun 14, 2011 at 3:32 PM, Dirk Cornel.... <moolma_dc@mtn.co.za>wrote:
> Thank you, the query runs now. The strange thing is, it is coming back with
> "no rows found" - I'm sure there must be temporary tables on our database
> ...
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Fernando Nunes
> > Sent: Tuesday, 14 June 2011 04:13 PM
> > To: ids@iiug.org
> > Subject: Re: Query to list temporary tables [24030]
> >
> > True... sorry.
> > bitand is not present on v10.
> > I was confusing with bitval. I believe you can try with bitval
> > instead...
> > Regards.
> >
> > On Tue, Jun 14, 2011 at 1:52 PM, Dirk Cornel....
> > <moolma_dc@mtn.co.za>wrote:
> >
> > > Unless I'm making a typo ......
> > >
> > > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
> > > sysmaster:partdbsnum(T.partnum) dbspace_number,
> > > dbinfo('dbspace',T.partnum) dbspace_name
> > > from sysmaster:sysptnhdr P,> > > sysmaster:systabnames T where P.partnum = T. partnum
> > > AND sysmaster:bitand(flags, 64)>0
> > > # ^
> > > # 674: Routine (bitand) can not be resolved.
> > >
> > > > -----Original Message-----
> > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > Of
> > > > Fernando Nunes
> > > > Sent: Tuesday, 14 June 2011 11:39 AM
> > > > To: ids@iiug.org
> > > > Subject: Re: Query to list temporary tables [24028]
> > > >
> > > > bitand is on sysmaster.
> > > > So, if you're not connected to sysmaster prefix it with
> > "sysmaster:"
> > > >
> > > > Regards
> > > >
> > > > On Tue, Jun 14, 2011 at 9:57 AM, Dirk Cornel....
> > > > <moolma_dc@mtn.co.za>wrote:
> > > >
> > > > > I am on IDS 10 - maybe this is not supported ? When running the
> > > > query:
> > > > >
> > > > > 674: Routine (bitand) can not be resolved.> > > > >
> > > > > > -----Original Message-----
> > > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> > Behalf
> > > > Of
> > > > > > John Miller iii
> > > > > > Sent: Monday, 13 June 2011 08:55 PM
> > > > > > To: ids@iiug.org
> > > > > > Subject: Re: Query to list temporary tables [24017]
> > > > > >
> > > > > > How about:
> > > > > >
> > > > > > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
> > > > > > sysmaster:partdbsnum(T.partnum) dbspace_number,
> > > > > > dbinfo('dbspace',T.partnum)> > > > > > dbspace_name
> > > > > > from
> > > > > > sysmaster:sysptnhdr P, sysmaster:systabnames T
> > > > > > where
> > > > > > P.partnum = T. partnum
> > > > > > AND bitand(flags, 64)>0
> > > > > >
> > > > > > John F. Miller III
> > > > > > STSM, Embedability Architect
> > > > > > miller3@us.ibm.com
> > > > > > 503-578-5645
> > > > > > IBM Informix Dynamic Server (IDS)
> > > > > >
> > > > > > ids-bounces@iiug.org wrote on 06/13/2011 11:16:21 AM:
> > > > > >
> > > > > > > [image removed]
> > > > > > >
> > > > > > > Query to list temporary tables [24016]
> > > > > > >
> > > > > > > Dirk Cornel....
> > > > > > >
> > > > > > > to:
> > > > > > >
> > > > > > > ids
> > > > > > >
> > > > > > > 06/13/2011 11:18 AM
> > > > > > >
> > > > > > > Sent by:
> > > > > > >
> > > > > > > ids-bounces@iiug.org
> > > > > > >
> > > > > > > Please respond to ids
> > > > > > >
> > > > > > > Does anyone here have an sql query for me, for selecting
> > > > temporary
> > > > > > tables,
> > > > > > > their owner, and the dbspace they reside in ?
> > > > > > >
> > > > > > > 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.
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > ***********************************************************************
> > > > > > ********
> > > > > > 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...
> > > >
> > > > --001636c5bd691cd30004a5a8ce5f
> > > >
> > > >
> > > >
> > ***********************************************************************
> > > > ********
> > > > 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...
> >
> > --001636ed6e7e33b5ca04a5aca04c
> >
> >
> > ***********************************************************************
> > ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
>
> NOTE: This e-mail message is
Thanks !!
Sysmaster.sql
{ Partition Header }
insert into flags_text values ('sysptnhdr', 1, 'Page Level Locking');
insert into flags_text values ('sysptnhdr', 2, 'Row Level Locking');
insert into flags_text values ('sysptnhdr', 4, 'System Catalog Table');
insert into flags_text values ('sysptnhdr', 8, 'Replicated Table');
insert into flags_text values ('sysptnhdr', 32,'System created Temp Table');
insert into flags_text values ('sysptnhdr', 64,'User created Temp Table');
insert into flags_text values ('sysptnhdr', 128,'Sort File');
insert into flags_text values ('sysptnhdr', 256,'Contains Varchar Data Type');
insert into flags_text values ('sysptnhdr', 512,'Contains BLOBSpace BLOBS');
insert into flags_text values ('sysptnhdr', 1024,'Contains TBLSpace BLOBS');
insert into flags_text values ('sysptnhdr', 2048,'Contains either
Varchars,BLOBS or Rows > PAGESIZE-32');
insert into flags_text values ('sysptnhdr', 4096,'Contains optical Sub-SystemBLOBS');
insert into flags_text values ('sysptnhdr', 8192,'Permanent System createdTable ( undroppable )');
insert into flags_text values ('sysptnhdr', 16384,'Special Function Temp
Tables, no Bitmap Maintenance');
insert into flags_text values ('sysptnhdr', 32768,'Light Append Partition');
insert into flags_text values ('sysptnhdr', 131072,'Hash Table');
insert into flags_text values ('sysptnhdr', 262144,'Index Partition');
insert into flags_text values ('sysptnhdr', 524288,'Sequence Object');
insert into flags_text values ('sysptnhdr', 1048576,'Page free space cachedisabled');
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Fernando Nunes
> Sent: Tuesday, 14 June 2011 04:57 PM
> To: ids@iiug.org
> Subject: Re: Query to list temporary tables [24033]
>
> You can try to create one in your session prior to run the query.
> After that you can try to re-write the query as:
>
> select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
> sysmaster:partdbsnum(T.partnum) dbspace_number,
> dbinfo('dbspace',T.partnum)> dbspace_name
> from
> sysmaster:sysptnhdr P, sysmaster:systabnames T
> where
> P.partnum = T. partnum
> AND
> (
>
> bitval(flags, 64) >0 OR
>
> bitval(flags, 32) >0 OR
>
> bitval(flags, 128) >0 OR
>
> bitval(flags, 16384) >0 OR
>
> bitval(flags, 131072 >0
> )
>
> This may give you some more "temp" objects.
> Please check the "INSERT INTO flags_text values ('sysptnhdr',....);
> lines in
> your $INFORMIXDIR/etc/sysmaster.sql file to understand the meaning of
> this.
> But AFAIK the query you had should give you the temporary tables. As
> for the
> other objects, as I wrote before, there's much more to it, and AFAIK
> there
> is currently no way to get this in a simple manner.
>
> Regards.
>
> On Tue, Jun 14, 2011 at 3:32 PM, Dirk Cornel....
> <moolma_dc@mtn.co.za>wrote:
>
> > Thank you, the query runs now. The strange thing is, it is coming
> back with
> > "no rows found" - I'm sure there must be temporary tables on our
> database
> > ...
> >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> Of
> > > Fernando Nunes
> > > Sent: Tuesday, 14 June 2011 04:13 PM
> > > To: ids@iiug.org
> > > Subject: Re: Query to list temporary tables [24030]
> > >
> > > True... sorry.
> > > bitand is not present on v10.
> > > I was confusing with bitval. I believe you can try with bitval
> > > instead...
> > > Regards.
> > >
> > > On Tue, Jun 14, 2011 at 1:52 PM, Dirk Cornel....
> > > <moolma_dc@mtn.co.za>wrote:
> > >
> > > > Unless I'm making a typo ......
> > > >
> > > > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname) ,
> > > > sysmaster:partdbsnum(T.partnum) dbspace_number,
> > > > dbinfo('dbspace',T.partnum) dbspace_name
> > > > from sysmaster:sysptnhdr P,> > > > sysmaster:systabnames T where P.partnum = T. partnum
> > > > AND sysmaster:bitand(flags, 64)>0
> > > > # ^
> > > > # 674: Routine (bitand) can not be resolved.
> > > >
> > > > > -----Original Message-----
> > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> Behalf
> > > Of
> > > > > Fernando Nunes
> > > > > Sent: Tuesday, 14 June 2011 11:39 AM
> > > > > To: ids@iiug.org
> > > > > Subject: Re: Query to list temporary tables [24028]
> > > > >
> > > > > bitand is on sysmaster.
> > > > > So, if you're not connected to sysmaster prefix it with
> > > "sysmaster:"
> > > > >
> > > > > Regards
> > > > >
> > > > > On Tue, Jun 14, 2011 at 9:57 AM, Dirk Cornel....
> > > > > <moolma_dc@mtn.co.za>wrote:
> > > > >
> > > > > > I am on IDS 10 - maybe this is not supported ? When running
> the
> > > > > query:
> > > > > >
> > > > > > 674: Routine (bitand) can not be resolved.> > > > > >
> > > > > > > -----Original Message-----
> > > > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> > > Behalf
> > > > > Of
> > > > > > > John Miller iii
> > > > > > > Sent: Monday, 13 June 2011 08:55 PM
> > > > > > > To: ids@iiug.org
> > > > > > > Subject: Re: Query to list temporary tables [24017]
> > > > > > >
> > > > > > > How about:
> > > > > > >
> > > > > > > select TRIM(dbsname)||":"||TRIM(owner)||"."||TRIM(tabname)
> ,
> > > > > > > sysmaster:partdbsnum(T.partnum) dbspace_number,
> > > > > > > dbinfo('dbspace',T.partnum)> > > > > > > dbspace_name
> > > > > > > from
> > > > > > > sysmaster:sysptnhdr P, sysmaster:systabnames T
> > > > > > > where
> > > > > > > P.partnum = T. partnum
> > > > > > > AND bitand(flags, 64)>0
> > > > > > >
> > > > > > > John F. Miller III
> > > > > > > STSM, Embedability Architect
> > > > > > > miller3@us.ibm.com
> > > > > > > 503-578-5645
> > > > > > > IBM Informix Dynamic Server (IDS)
> > > > > > >
> > > > > > > ids-bounces@iiug.org wrote on 06/13/2011 11:16:21 AM:
> > > > > > >
> > > > > > > > [image removed]
> > > > > > > >
> > > > > > > > Query to list temporary tables [24016]
> > > > > > > >
> > > > > > > > Dirk Cornel....
> > > > > > > >
> > > > > > > > to:
> > > > > > > >
> > > > > > > > ids
> > > > > > > >
> > > > > > > > 06/13/2011 11:18 AM
> > > > > > > >
> > > > > > > > Sent by:
> > > > > > > >
> > > > > > > > ids-bounces@iiug.org
> > > > > > > >
> > > > > > > > Please respond to ids
> > > > > > > >
> > > > > > > > Does anyone here have an sql query for me, for selecting
> > > > > temporary
> > > > > > > tables,
> > > > > > > > their owner, and the dbspace they reside in ?
> > > > > > > >
> > > > > > > > Dirk
> > > > > > > >
> > > > > > > > ________________________________
> > > > > > > > NOTE: This e-mail message is subject to the MTN Group
> > > disclaimer
> > > > > see
> > > > > > > >
> http://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > >