Is there any way out to find/delete temp tables in XPS?

Posted in 2003

Mohan, — Tue, 28 Oct 2003 11:39:45 — source: IIUG Forums & Mailing Lists

Hi All, Our temp db is 99% full currently . I would like to know which tables are occupying space in temp db(with their size). Is there some query that will allow us to find the tables that are created in temp dbspace? Thanks Krishna ------_=_NextPart_001_01C39D47.78356A52 Content-Type: text/html Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3DUS-ASCII"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2657.19"> <TITLE>Is there any way out to find/delete temp tables in XPS?</TITLE> </HEAD> <BODY> Hi All, Our temp db is 99% full currently . I = would like to know which tables are occupying space in temp db(with = their size). Is there some query that will allow = us to find the tables that are created in  temp dbspace? Thanks Krishna </BODY> </HTML> ------_=_NextPart_001_01C39D47.78356A52-- ------=_NextPartTM-000-c3786df6-1d78-497b-8d91-7224a30af449--

↪ replying to Mohan,

Gerd Kaluzinski — Tue, 28 Oct 2003 13:09:51 — source: IIUG Forums & Mailing Lists

You can use "oncheck -pe" to see all that's in you chunks. If you want to use sysmaster database you can use: database sysmaster; select dbsname dbname, tabname, c.owner,name dbspace,ti_nrows,ti_rowsize from sysdbspaces a, systabinfo b, systabnames c where a.dbsnum = ( trunc ( b.ti_partnum/1048576 ) ) and b.ti_partnum = c.partnum and ( bitval(ti_flags,'0x0020')=1 or bitval ( ti_flags, '0x0040') = 1 or bitval ( ti_flags, '0x0080') = 1 ) OR: database sysmaster; select c.chknum, d.name[1,8], e.dbsname[1,12] dbname,e.tabname[1,18] table --, count(*) extents , sum(size) pages --, c.fname --, d.name from sysextents e, syschunks c, sysdbspaces d where trunc(e.start/1000000,0) = c.chknum and c.dbsnum = d.dbsnum and d.name = "tempdbs" group by 1,2,3,4 order by 1,2,3,4 On Tue, 28 Oct 2003 06:39:46 -0500 (EST) "Mohan, Krishna" <krishna.mohan@fmr.com> wrote: > Hi All, > > Our temp db is 99% full currently . I would like to know which tables are > occupying space in temp db(with their size). > Is there some query that will allow us to find the tables that are created > in temp dbspace? > > Thanks > Krishna > > ------_=_NextPart_001_01C39D47.78356A52 > Content-Type: text/html > Content-Transfer-Encoding: quoted-printable > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> > <HTML> > <HEAD> > <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = > charset=3DUS-ASCII"> > <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = > 5.5.2657.19"> > <TITLE>Is there any way out to find/delete temp tables in XPS?</TITLE> > </HEAD> > <BODY> > > Hi All, > > > Our temp db is 99% full currently . I = > would like to know which tables are occupying space in temp db(with = > their size). > Is there some query that will allow = > us to find the tables that are created in  temp dbspace? > > Thanks > Krishna > > > </BODY> > </HTML> > ------_=_NextPart_001_01C39D47.78356A52-- > > ------=_NextPartTM-000-c3786df6-1d78-497b-8d91-7224a30af449-- > > -- Mit freundlichen Grüßen Gerd Kaluzinski \\\\\\\\|// (o o) --------------------------------------------------ooO-(_)-Ooo--- Gerd Kaluzinski mailto:gerd.kaluzinski@bytec.de Manager Consulting mailto:support@bytec.de Informix Certified Senior System Engineer http://www.bytec.de BYTEC GmbH Telefon: 07541-585-1019 Hermann-Metzger-Str. 7 Fax : 07541-585-2019 88045 Friedrichshafen Ooo. -------------------------------------------------.ooO----( )--- ( ) (_/ \\\\_)

↪ replying to Mohan,

Rajib Sarkar — Tue, 28 Oct 2003 16:10:40 — source: IIUG Forums & Mailing Lists

Hi Krishna, The following query should retrieve the TEMP tables in the database ... database sysmaster;SELECT hex(i.ti_partnum) partition, trim(n.dbsname) || ":" || trim(n.owner) || ":" || trim(n.tabname) table, i.ti_nptotal allocated_pages FROM systabnames n, systabinfo i WHERE ((bitval(i.ti_flags, "0x0020") = 1) OR (bitval(i.ti_flags, "0x0040") = 1) OR (bitval(i.ti_flags, "0x0080") = 1)) AND i.ti_partnum = n.partnum; The first 3 nibbles (characters ) of the 1st column will indicate the dbspace# if you are on anything less than 9.40. HTH Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi "Mohan, Krishna" <krishna.mohan@fm To: ids@iiug.org r.com> cc: Sent by: Subject: Is there any way out to find/delete temp tables in XPS? [2087] forum.subscriber@ iiug.org 10/28/2003 04:39 AM Hi All, Our temp db is 99% full currently . I would like to know which tables are occupying space in temp db(with their size). Is there some query that will allow us to find the tables that are created in temp dbspace? Thanks Krishna ------_=_NextPart_001_01C39D47.78356A52 Content-Type: text/html Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3DUS-ASCII"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2657.19"> <TITLE>Is there any way out to find/delete temp tables in XPS?</TITLE> </HEAD> <BODY> Hi All, Our temp db is 99% full currently . I = would like to know which tables are occupying space in temp db(with = their size). Is there some query that will allow = us to find the tables that are created in  temp dbspace? Thanks Krishna </BODY> </HTML> ------_=_NextPart_001_01C39D47.78356A52-- ------=_NextPartTM-000-c3786df6-1d78-497b-8d91-7224a30af449--

Related threads

Glossary

dbspace