temporary dbspace usage
A user on HP-UX with IDS 10.00.FC8 had six 4GB temp dbspaces listed in DBSPACETEMP, but a single query doing joins/group by/sort would fill just one of them and fail with "not enough disk space" instead of spilling into the other, unused temp dbspaces. Everett Mills suggested fragmenting explicitly created temp tables across the spaces (not applicable, since the query used only regular tables), checked whether all spaces were truly temp, and asked about PDQ/parallel sort settings; the poster confirmed PDQPRIORITY 100 and a correct config. No resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, I have a production environment, 6 temp dbspaces in use which have 4G for each, 24G total. OS: HP-UX IDS: IDS 10.00 FC8 temp dbspace: tempdbs01,tempdbs02,tempdbs03,tempdbs04,tempdbs05,tempdbs06 In my enviroment, when several sessions run at then sam time which have sort、left join or group by etc， need temporary dbspace, the 6 temp dbspace can be used parallel, but the single session can use temp dbspace only one, when the dbspace use rate reach 100%, then prompt : not enough disk space, why the single session cann't use the other one or more dbspace？ How can I do this, I want in my query, I can use temp dbspcae for all?
does anyone meet that? and how can I use all the temp dbspaces in a single query???
I think you're asking "How do I fragment my temp tables?" Maybe?
Assuming that is correct, if you build a temp table explicitly, you can
tell it to fragment:
CREATE TEMP TABLE foo_temp
(
My_column1 smallint,
My_column2 varchar(1000)
)
WITH NO LOG
FRAGMENT BY ROUND ROBIN IN my_temp_dbspace1, my_temp_dbspace2, etc
When you do a query that does "SELECT some_columns FROM some_table INTO
TEMP new_temp_table WITH NO LOG", the manual seems to indicate that you
can't fragment implicitly defined temp tables.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
SHAN
> SEAN
> Sent: Thursday, September 03, 2009 8:23 PM
> To: ids@iiug.org
> Subject: Re: temporary dbspace usage [16861]
>
> does anyone meet that? and how can I use all the temp dbspaces in a
single
> query???
>
>
>
************************************************************************
******
> *
> Forum Note: Use "Reply" to post a response in the discussion forum.
anwser to Mills, thinks for your response, but you understand incorrect. In my select query, I didn't use temp table, select table are standard tables, but in select SQL have goup by or sort or left join, so it will use temp dbspace, I have 6 temp dbspace, but one select only use one dbspace, if it not enough, the query not use the next unuse temp dbspace, it will response error: not enough dbspace.
I see. Sorts and joins are supposed to use temp space by round robin by
default. Is only one of your temp spaces defined as temp and the rest
logged? It would help to see an example query and the output from
"onstat -d" and "grep DBSPACETEMP onconfig".
Also, you haven't mentioned your IDS version or your OS info.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
SHAN
> SEAN
> Sent: Friday, September 04, 2009 11:06 AM
> To: ids@iiug.org
> Subject: Re: temporary dbspace usage [16872]
>
> anwser to Mills, thinks for your response, but you understand
incorrect.
> In my select query, I didn't use temp table, select table are standard
tables,
> but in select SQL have goup by or sort or left join, so it will use
temp
> dbspace, I have 6 temp dbspace, but one select only use one dbspace,
if it not
> enough, the query not use the next unuse temp dbspace, it will
response error:
> not enough dbspace.
>
>
>
************************************************************************
******
> *
> Forum Note: Use "Reply" to post a response in the discussion forum.
Also, do you have parallel sorting turned on?:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/co
m.ibm.perf.doc/perf139.htm
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Everett
> Mills
> Sent: Friday, September 04, 2009 11:31 AM
> To: ids@iiug.org
> Subject: RE: temporary dbspace usage [16874]
>
> I see. Sorts and joins are supposed to use temp space by round robin
by
> default. Is only one of your temp spaces defined as temp and the rest
> logged? It would help to see an example query and the output from
> "onstat -d" and "grep DBSPACETEMP onconfig".
>
> Also, you haven't mentioned your IDS version or your OS info.
>
> --EEM
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
Of
> SHAN
> > SEAN
> > Sent: Friday, September 04, 2009 11:06 AM
> > To: ids@iiug.org
> > Subject: Re: temporary dbspace usage [16872]
> >
> > anwser to Mills, thinks for your response, but you understand
> incorrect.
> > In my select query, I didn't use temp table, select table are
standard
> tables,
> > but in select SQL have goup by or sort or left join, so it will use
> temp
> > dbspace, I have 6 temp dbspace, but one select only use one dbspace,
> if it not
> > enough, the query not use the next unuse temp dbspace, it will
> response error:
> > not enough dbspace.
> >
> >
> >
>
************************************************************************
> ******
> > *
> > Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
************************************************************************
******
> *
> Forum Note: Use "Reply" to post a response in the discussion forum.
haha， you didn't look the 1st message carefully.
say again:
OS: HP-UX
IDS: ids 10.0 FC8
In my error sql, it doesn't have sort, it only have 5 or 6 tables left join.
onconfig file
DBSPACETEMP tempdbs01,tempdbs02,tempdbs03,tempdbs04,tempdbs05,tempdbs06
it doen't have any incorrect profile.
before that sql I open the pdq. such as:
[
set pdqpriority 100;
select .... from tab1 left join tab2 on ... left join tab3 on .... ....
where ...;]
Related threads
- IDS 10 table-level restore
- Informix Development Webinar December 11, 2007
- ontape -p/r with changed ROOTPATH
- Migrate from HP PA-RISC to HP ITANIUM by ontape