Join IIUG
 for   
 

Informix News
18 Nov 13 - ZDNet - Top 20 mobile skills in demand... Read
09 Sep 13 - telecompaper - Shaspa and Tatung have shown a new smart home platform at Ifa in Berlin. Powered by the IBM Informix software... Read
06 Sep 13 - IBM data magazine - Mission Accomplished - Miami, Florida will be the backdrop for the 2014 IIUG Informix Conference... Read
01 Feb 13 - IBM Data Magazine - Are your database backups safe? Lester Knutsen (IBM Champion) writes about database back up safety using "archecker"... Read
14 Nov 12 - IBM - IBM's Big Data For Smart Grid Goes Live In Texas... Read
3 Oct 12 - The Financial - IBM and TransWorks Collaborate to Help Louisiana-Pacific Corporation Achieve Supply Chain Efficiency... Read
28 Aug 12 - techCLOUD9 - Splunk kicks up a SaaS Storm... Read
10 Aug 12 - businessCLOUD9 - Is this the other half of Cloud monitoring?... Read
3 Aug 12 - IBM data management - Supercharging the data warehouse while keeping costs down IBM Informix Warehouse Accelerator (IWA) delivers superior performance for in-memory analytics processing... Read
2 Aug 12 - channelbiz - Oninit Group launches Pay Per Pulse cloud-based service... Read
28 May 12 - Bloor - David Norfolk on the recent Informix benchmark "pretty impressive results"... Read
23 May 12 - DBTA - Informix Genero: A Way to Modernize Informix 4GL Applications... Read
9 Apr 12 - Mastering Data Management - Upping the Informix Ante: Advanced Data Tools... Read
22 Mar 12 - developerWorks - Optimizing Informix database access... Read
14 Mar 12 - BernieSpang.com - International Informix User Group set to meet in San Diego... Read
1 Mar 12 - IBM Data Management - IIUG Heads West for 2012 - Get ready for sun and sand in San Diego... Read
1 Mar 12 - IBM Data Management - Running Informix on Solid-State Drives.Speed Up Database Access... Read
26 Feb 12 - BernieSpan.com - Better results, lower cost for a broad set of new IBM clients and partners... Read
24 Feb 12 - developerWorks - Informix Warehouse Accelerator: Continuous Acceleration during Data Refresh... Read
6 Feb 12 - PRLOG - Informix port delivers unlimited database scalability for popular SaaS application ... Read
2 Feb 12 - developerWorks - Loading data with the IBM Informix TimeSeries Plug-in for Data Studio... Read
1 Feb 12 - developerWorks - 100 Tech Tips, #47: Log-in to Fix Central... Read
13 Jan 12 - MC Press online - Informix Dynamic Server Entices New Users with Free Production Edition ... Read
11 Jan 12 - Computerworld - Ecologic Analytics and Landis+Gyr -- Suitors Decide to Tie the Knot... Read
9 Jan 12 - planetIDS.com - DNS impact on Informix / Impacto do DNS no Informix... Read
8 Sep 11 - TMCnet.com - IBM Offers Database Solution to Enable Smart Meter Data Capture... Read
1 Aug 11 - IBM Data Management Magazine - IIUG user view: Happy 10th anniversary to IBM and Informix... Read
8 Jul 11 - Database Trends and Applications - Managing Time Series Data with Informix... Read
31 May 11 - Smart Grid - The meter data management pitfall utilities are overlooking... Read
27 May 11 - IBM Data Management Magazine - IIUG user view: Big data, big time ( Series data, warehouse acceleration, and 4GLs )... Read
16 May 11 - Business Wire - HiT Software Announces DBMoto for Enterprise Integration, Adds Informix. Log-based Change Data Capture... Read
21 Mar 11 - Yahoo! Finance - IBM and Cable&Wireless Worldwide Announce UK Smart Energy Cloud... Read
14 Mar 11 - MarketWatch - Fuzzy Logix and IBM Unveil In-Database Analytics for IBM Informix... Read
11 Mar 11 - InvestorPlace - It's Time to Give IBM Props: How many tech stocks are up 53% since the dot-com boom?... Read
9 Mar 11 - DBTA - Database Administration and the Goal of Diminishing Downtime... Read
2 Feb 11 - DBTAs - Informix 11.7 Flexible Grid Provides a Different Way of Looking at Database Servers... Read
27 Jan 11 - exactsolutions - Exact to Add Informix Support to Database Replay, SQL Monitoring Solutions... Read
25 Jan 11 - PR Newswire - Bank of China in the UK Works With IBM to Become a Smarter, Greener Bank... Read
12 Oct 10 - Database Trends and Applications - Informix 11.7: The Beginning of the Next Decade of IBM Informix... Read
20 Sep 10 - planetIDS.com - ITG analyst paper: Cost/Benefit case for IBM Informix as compared to Microsoft SQL Server... Read
20 Jul 10 - IBM Announcements - IBM Informix Choice Edition V11.50 helps deploy low-cost scalable and reliable solutions for Apple Macintosh and Microsoft Windows... Read
20 Jul 10 - IBM Announcements - Software withdrawal: Elite Support for Informix Ultimate-C Edition... Read
24 May 10 - eWeek Europe - IBM Supplies Database Tech For EU Smart Grid... Read
23 May 10 - SiliconIndia - IBM's smart metering system allows wise use of energy... Read
21 May 10 - CNET - IBM to help people monitor energy use... Read
20 May 10 - ebiz - IBM Teams With Hildebrand To Bring Smart Metering To Homes Across Britain... Read
19 May 10 - The New Blog Times - Misurare il consumo energetico: DEHEMS è pronto... Read
19 May 10 - ZDNet - IBM software in your home? Pact enables five-city smart meter pilot in Europe... Read
17 March 10 - ZDNet (blog) David Morgenstern - TCO: New research finds Macs in the enterprise easier, cheaper to manage than... Read
17 March 2010 - Virtualization Review - ...key components of Big Blue's platform to the commercial cloud such as its WebSphere suite of application ser vers and its DB2 and Informix databases... Read
10 February 2010 - The Wall Street Journal - International Business Machines is expanding an initiative to win over students and professors on its products. How do they lure the college crowd?... Read

End of Support Dates
IDS 11.10 - 30 Sep 2012
Subscribe to notifications

IIUG on Facebook IIUG on Twitter

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum

RE: Query to list temporary tables

Posted By: Dirk Cornelis Moolman [ MTN - Innovation Centre ]
Date: Tuesday, 14 June 2011, at 11:18 a.m.

In Response To: Re: Query to list temporary tables (Fernando Nunes)

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-System BLOBS');

insert into flags_text values ('sysptnhdr', 8192,'Permanent System created Table ( 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 cache disabled');

> -----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
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > >
> ***********************************************************************
> > > > > > > ********
> > > > > > >
> > > > > > > > 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
> >
> >
> >
> >
> ***********************************************************************
> ********
> > 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...
>
> --001636ef0a1536fb8a04a5ad3d98
>
>
> ***********************************************************************
> ********
> 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

Messages In This Thread

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum is maintained by Administrator with WebBBS 5.12.