Re.tempspace

Hello Everybody, Env.AIX 5.1 IDS 9.30 UC3 with HDR. We have 6 tempdbspaces of 1GB each on primary and secondary server, on secondary we are running Crystal Reports 8.5 couple of reports which are using VIEWS failed, when I checked online.log it says that "tempdbspace-5" is full, why it is just using tempdbspace5 and not the combination of tempdbspaces.... I could see that all other tempspaces are been used upto 25-30%, any idea why we have this problem. Thanks in advance. Sushil..... _________________________________________________________________ Add MSN 8 Internet Software to your current Internet access and enjoy patented spam control and more. Get two months FREE! http://join.msn.com/?page=dept/byoa

most likely Crystal reports is creating a temp table explicity as in CREATE TEMP TABLE ... Explicit TEMP TABLES are always created in one temp dbspace unless FRAGMENT BY ROUND-ROBIN is mentioned. OTOH, implicit TEMP tables is always fragmented equally in all temp dbspaces. Implicit TEMP tables are SELECT INTO TEMP ... Ravi ----- Original Message ----- From: "Sushil Shir...." <sushilps@hotmail.com> To: <ids@iiug.org> Sent: Monday, October 27, 2003 10:34 Subject: Re.tempspace [2078] > Hello Everybody, > > Env.AIX 5.1 IDS 9.30 UC3 with HDR. > > We have 6 tempdbspaces of 1GB each on primary and secondary server, on > secondary we are running Crystal Reports 8.5 couple of reports which are > using VIEWS failed, when I checked online.log it says that "tempdbspace-5" > is full, why it is just using tempdbspace5 and not the combination of > tempdbspaces.... I could see that all other tempspaces are been used upto > 25-30%, any idea why we have this problem. > > Thanks in advance. > Sushil..... > > _________________________________________________________________ > Add MSN 8 Internet Software to your current Internet access and enjoy > patented spam control and more. Get two months FREE! > http://join.msn.com/?page=dept/byoa > > >

Sushil A temp table can only reside in one dbspace no matter how large it grows, so when it reaches the limits of the dbspace it will fail with no space left. This behaviour _cannot_ be changed by using fragmentation. You either need to combine your 6 spaces into 3 of 2GB each or add another 1GB to each. There is a much deeper discussion on this on the c.d.i. archives. About 1 year ago I think. Also in this s.i.g. (look for title TEMPDBSPACE) Keith -> -----Original Message----- -> From: Sushil Shir.... [mailto:sushilps@hotmail.com] -> Sent: Monday, October 27, 2003 3:34 PM -> To: ids@iiug.org -> Subject: Re.tempspace [2078] -> -> -> Hello Everybody, -> -> Env.AIX 5.1 IDS 9.30 UC3 with HDR. -> -> We have 6 tempdbspaces of 1GB each on primary and secondary -> server, on -> secondary we are running Crystal Reports 8.5 couple of -> reports which are -> using VIEWS failed, when I checked online.log it says that -> "tempdbspace-5" -> is full, why it is just using tempdbspace5 and not the -> combination of -> tempdbspaces.... I could see that all other tempspaces are -> been used upto -> 25-30%, any idea why we have this problem. -> -> Thanks in advance. -> Sushil..... -> -> _________________________________________________________________ -> Add MSN 8 Internet Software to your current Internet access -> and enjoy -> patented spam control and more. Get two months FREE! -> http://join.msn.com/?page=dept/byoa -> -> -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** **

Keith, Thanks for your response. Yes I did add 2gb space to tempdbspace5 and crystal reports worked fine, but I am still not sure why it is using tempdbspace5 and not other although we have enough space in all other dbspaces(35-45% free). Maybe it is looking for one big chunk for tempspace. Sushil.... >From: "Simmons, Keith" <keith.simmons@office2office.biz> >To: "'Sushil Shir....'" <sushilps@hotmail.com> >CC: "'ids@iiug.org'" <ids@iiug.org> >Subject: RE: Re.tempspace [2078] Date: Mon, 27 Oct 2003 16:48:08 -0000 >MIME-Version: 1.0 >Received: from ntstso13.theso.co.uk ([194.128.65.252]) by >mc7-f9.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Mon, 27 Oct 2003 >08:50:10 -0800 >Received: from [10.16.5.50] by ntstso13.theso.co.uk (NTMail >7.00.0022/NY1817.00.35e8284e) with ESMTP id eyarxaaa for >sushilps@hotmail.com; Mon, 27 Oct 2003 16:47:02 +0000 >Received: from tsoexc01.theso.co.uk ([10.16.5.8]) by fw1tso01.theso.co.uk; >Mon, 27 Oct 2003 16:46:58 +0000 (GMT Standard Time) >Received: by tsoexc01.theso.co.uk with Internet Mail Service >(5.5.2653.19)id <VX1A7C79>; Mon, 27 Oct 2003 16:48:12 -0000 >X-Message-Info: JGTYoYF78jFu4lPHDkO/WSCDAHCxrjZj >Message-ID: <70C8E4409F6DD411899500805FFE967C0EC5E22F@tsoexc01.theso.co.uk> >Return-Receipt-To: "Simmons, Keith" <keith.simmons@office2office.biz> >X-Mailer: Internet Mail Service (5.5.2653.19) >Return-Path: keith.simmons@office2office.biz >X-OriginalArrivalTime: 27 Oct 2003 16:50:24.0408 (UTC) >FILETIME=[6AA50980:01C39CAA] > >Sushil > >A temp table can only reside in one dbspace no matter how large it grows, >so >when it reaches the limits of the dbspace it will fail with no space left. >This behaviour _cannot_ be changed by using fragmentation. You either need >to combine your 6 spaces into 3 of 2GB each or add another 1GB to each. >There is a much deeper discussion on this on the c.d.i. archives. About 1 >year ago I think. Also in this s.i.g. (look for title TEMPDBSPACE) > >Keith > >-> -----Original Message----- >-> From: Sushil Shir.... [mailto:sushilps@hotmail.com] >-> Sent: Monday, October 27, 2003 3:34 PM >-> To: ids@iiug.org >-> Subject: Re.tempspace [2078] >-> >-> >-> Hello Everybody, >-> >-> Env.AIX 5.1 IDS 9.30 UC3 with HDR. >-> >-> We have 6 tempdbspaces of 1GB each on primary and secondary >-> server, on >-> secondary we are running Crystal Reports 8.5 couple of >-> reports which are >-> using VIEWS failed, when I checked online.log it says that >-> "tempdbspace-5" >-> is full, why it is just using tempdbspace5 and not the >-> combination of >-> tempdbspaces.... I could see that all other tempspaces are >-> been used upto >-> 25-30%, any idea why we have this problem. >-> >-> Thanks in advance. >-> Sushil..... >-> >-> _________________________________________________________________ >-> Add MSN 8 Internet Software to your current Internet access >-> and enjoy >-> patented spam control and more. Get two months FREE! >-> http://join.msn.com/?page=dept/byoa >-> >-> >-> > > >******************************************************************************* *** >This message is sent in strict confidence for the addressee only. It may >contain legally privileged information. The contents are not to be >disclosed >to anyone other than the addressee. Unauthorised recipients are requested >to preserve this confidentiality and to advise the sender immediately of >any >error in transmission. >This footnote also confirms that this email message has been swept for the >presence of computer viruses, however we cannot guarantee that this message >is free from such problems. >******************************************************************************* *** _________________________________________________________________ Cheer a special someone with a fun Halloween eCard from American Greetings! Go to http://www.msn.americangreetings.com/index_msn.pd?source=msne134

This of course means that you are not including implicit tables in your definition of a temp table. (temp tables required for order, group, hash) cheers j. -.-- --- ..- / -. . . -.. / - --- / --. . - / .- / .-.. .. ..-. . .-.-.- / ... --- / -.. --- / .. .-.-.- ----- Original Message ----- From: "Simmons, Keith" <keith.simmons@office2office.biz> To: <ids@iiug.org> Sent: Monday, October 27, 2003 11:50 AM Subject: RE: Re.tempspace [2080] > Sushil > > A temp table can only reside in one dbspace no matter how large it grows, so > when it reaches the limits of the dbspace it will fail with no space left. > This behaviour _cannot_ be changed by using fragmentation. You either need > to combine your 6 spaces into 3 of 2GB each or add another 1GB to each. > There is a much deeper discussion on this on the c.d.i. archives. About 1 > year ago I think. Also in this s.i.g. (look for title TEMPDBSPACE) > > Keith > > -> -----Original Message----- > -> From: Sushil Shir.... [mailto:sushilps@hotmail.com] > -> Sent: Monday, October 27, 2003 3:34 PM > -> To: ids@iiug.org > -> Subject: Re.tempspace [2078] > -> > -> > -> Hello Everybody, > -> > -> Env.AIX 5.1 IDS 9.30 UC3 with HDR. > -> > -> We have 6 tempdbspaces of 1GB each on primary and secondary > -> server, on > -> secondary we are running Crystal Reports 8.5 couple of > -> reports which are > -> using VIEWS failed, when I checked online.log it says that > -> "tempdbspace-5" > -> is full, why it is just using tempdbspace5 and not the > -> combination of > -> tempdbspaces.... I could see that all other tempspaces are > -> been used upto > -> 25-30%, any idea why we have this problem. > -> > -> Thanks in advance. > -> Sushil..... > -> > -> _________________________________________________________________ > -> Add MSN 8 Internet Software to your current Internet access > -> and enjoy > -> patented spam control and more. Get two months FREE! > -> http://join.msn.com/?page=dept/byoa > -> > -> > -> > > > **************************************************************************** ****** > This message is sent in strict confidence for the addressee only. It may > contain legally privileged information. The contents are not to be disclosed > to anyone other than the addressee. Unauthorised recipients are requested > to preserve this confidentiality and to advise the sender immediately of any > error in transmission. > This footnote also confirms that this email message has been swept for the > presence of computer viruses, however we cannot guarantee that this message > is free from such problems. > **************************************************************************** ****** > >