Engine won't use temporary space - rootdbs full
Hey all,
I have an 11.50.UC3 engine running on RedHat EL4
It has three temp dbspaces set up and the DBSPACETEMP variable is set to
use them.
Any sort query only uses space in the rootdbs - completely filling it
up.
I have tried setting client side environment variables for DBSPACETEMP
and for PSORT_DBTEMP, neither seem to be listened to.
I have tried removing and recreating the spaces / restarting the
engine...
Any clues appreciated - I'm fed up beating my head against a wall.
=========ONCONFIG SNIPPET========
[sdev@sirrp61(10.60.66.130) ~]$ onstat -c | grep DBSPACE
ROOTPATH /usr/informix/DBSPACES/rootdbs
CDR_DBSPACE er_dbspace # dbspace for syscdr database
CDR_QHDR_DBSPACE er_dbspace # CDR queue dbspace (default sameas catalog)
# DBSPACETEMP:
DBSPACETEMP tempdbs1:tempdbs2:tempdbs3
ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 =
ABORT, 2 = WAIT
==========END===================
===========onstat -d================
[sdev@sirrp61(10.60.66.130) ~]$ onstat -d
IBM Informix Dynamic Server Version 11.50.UC3 -- On-Line -- Up
00:24:45 -- 461932 Kbytes
Dbspaces
address number flags fchunk nchunks pgsize flags owner
name
4cda5808 1 0x70001 1 1 2048 N B
informix rootdbs
4e2d9e28 2 0x42001 2 1 2048 N TB
informix tempdbs1
4e2c7e30 3 0x42001 3 1 2048 N TB
informix tempdbs2
4e296330 4 0x42001 4 1 2048 N TB
informix tempdbs3
4e296490 5 0x60001 5 1 2048 N B
informix data01
4e2965f0 6 0x60001 6 1 2048 N B
informix logical_dbs
4e296750 7 0x70001 7 1 2048 N B
informix physical_dbs
4e2968b0 8 0x60001 8 2 2048 N B
informix logical_logs
4e296a10 9 0x60001 10 1 2048 N B
informix er_dbspace
4e296b70 10 0x68001 11 4 2048 N SB
informix er_sbspace
4e296cd0 11 0x60001 15 1 2048 N B
informix sysadmin_dbs
11 active, 2047 maximum
Chunks
address chunk/dbs offset size free bpages flags
pathname
4cda5968 1 1 0 32000 28953 PO-B
/usr/informix/DBSPACES/rootdbs
4e296e30 2 2 0 524288 524260 PO-B
/usr/informix/DBSPACES/tempdbs1
4e33e018 3 3 0 524288 524260 PO-B
/usr/informix/DBSPACES/tempdbs2
4e33e1e8 4 4 0 524288 524260 PO-B
/usr/informix/DBSPACES/tempdbs3
4e33e3b8 5 5 0 9773000 1434466 PO-B
/usr/informix/DBSPACES/data01
4e33e588 6 6 0 102400 2347 PO-B
/usr/informix/DBSPACES/logical_logs
4e33e758 7 7 0 204800 4747 PO-B
/usr/informix/DBSPACES/physical_logs
4e33e928 8 8 0 512000 11947 PO-B
/usr/informix/DBSPACES/logicalslogs
4e33eaf8 9 8 0 512000 1997 PO-B
/usr/informix/DBSPACES/logicalslogs2
4e33ecc8 10 9 0 512000 511847 PO-B
/usr/informix/DBSPACES/er_dbspace
4d753018 11 10 0 512000 398183 398183 POSB
/usr/informix/DBSPACES/er_sbspace
Metadata 113764 7122 113764
4d7531e8 12 10 0 512000 398222 398222 POSB
/usr/informix/DBSPACES/er_sbspace2
Metadata 113775 0 113775
4d7533b8 13 10 0 512000 398222 398222 POSB
/usr/informix/DBSPACES/er_sbspace3
Metadata 113775 12891 113775
4d753588 14 10 0 512000 398222 398222 POSB
/usr/informix/DBSPACES/er_sbspace4
Metadata 113775 12891 113775
4d753758 15 11 0 512000 490979 PO-B
/usr/informix/DBSPACES/sysadmin_dbs
15 active, 32766 maximum
==============END==============
Jarrod Teale
Fonterra
DISCLAIMER:
This email contains confidential information and may be legally privileged. If
you are not the intended recipient or have received this email in error,
please notify the sender immediately and destroy this email.
You may not use, disclose or copy this email or its attachments in any way.
Any opinions expressed in this email are those of the author and are not
necessarily those of the Fonterra Co-operative Group.
http://www.fonterra.com/
Hi Jarrod,
If your temp tables are not being created with the 'with no log' option, that
might explain why they don't get to be stored on the temporary dbspaces you
set up using DBSPACETEMP.
If that could be the reason, you can use the new ONCONFIG parameter
TEMPTAB_NOLOG. If you set it to 1, you will be disabling all logging of
temporary tables and hence they can be stored in the temp dbspaces without you
having to put the code 'with no log' on the 'create table' or 'insert into
temp' statements.
Here's more info:
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.s
qls.doc/ids_sqs_0575.htm
Hope it helps,
Veronica.
> To: ids@iiug.org
> From: Jarrod.Teale@fonterra.com
> Subject: Engine won't use temporary space - rootdbs full [15176]
> Date: Mon, 16 Mar 2009 18:21:48 -0400
>
> Hey all,
> I have an 11.50.UC3 engine running on RedHat EL4
> It has three temp dbspaces set up and the DBSPACETEMP variable is set to
> use them.
> Any sort query only uses space in the rootdbs - completely filling it
> up.
> I have tried setting client side environment variables for DBSPACETEMP
> and for PSORT_DBTEMP, neither seem to be listened to.
> I have tried removing and recreating the spaces / restarting the
> engine...
>
> Any clues appreciated - I'm fed up beating my head against a wall.
>
> =========ONCONFIG SNIPPET========
> [sdev@sirrp61(10.60.66.130) ~]$ onstat -c | grep DBSPACE
> ROOTPATH /usr/informix/DBSPACES/rootdbs
> CDR_DBSPACE er_dbspace # dbspace for syscdr database
> CDR_QHDR_DBSPACE er_dbspace # CDR queue dbspace (default same> as catalog)
> # DBSPACETEMP:
> DBSPACETEMP tempdbs1:tempdbs2:tempdbs3
> ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 =
> ABORT, 2 = WAIT>
> ==========END===================
>
> ===========onstat -d================
> [sdev@sirrp61(10.60.66.130) ~]$ onstat -d
>
> IBM Informix Dynamic Server Version 11.50.UC3 -- On-Line -- Up
> 00:24:45 -- 461932 Kbytes>
> Dbspaces
> address number flags fchunk nchunks pgsize flags owner
> name
> 4cda5808 1 0x70001 1 1 2048 N B
> informix rootdbs
> 4e2d9e28 2 0x42001 2 1 2048 N TB
> informix tempdbs1
> 4e2c7e30 3 0x42001 3 1 2048 N TB
> informix tempdbs2
> 4e296330 4 0x42001 4 1 2048 N TB
> informix tempdbs3
> 4e296490 5 0x60001 5 1 2048 N B
> informix data01
> 4e2965f0 6 0x60001 6 1 2048 N B
> informix logical_dbs
> 4e296750 7 0x70001 7 1 2048 N B
> informix physical_dbs
> 4e2968b0 8 0x60001 8 2 2048 N B
> informix logical_logs
> 4e296a10 9 0x60001 10 1 2048 N B
> informix er_dbspace
> 4e296b70 10 0x68001 11 4 2048 N SB
> informix er_sbspace
> 4e296cd0 11 0x60001 15 1 2048 N B
> informix sysadmin_dbs
> 11 active, 2047 maximum
>
> Chunks
> address chunk/dbs offset size free bpages flags
> pathname
> 4cda5968 1 1 0 32000 28953 PO-B
> /usr/informix/DBSPACES/rootdbs
> 4e296e30 2 2 0 524288 524260 PO-B
> /usr/informix/DBSPACES/tempdbs1
> 4e33e018 3 3 0 524288 524260 PO-B
> /usr/informix/DBSPACES/tempdbs2
> 4e33e1e8 4 4 0 524288 524260 PO-B
> /usr/informix/DBSPACES/tempdbs3
> 4e33e3b8 5 5 0 9773000 1434466 PO-B
> /usr/informix/DBSPACES/data01
> 4e33e588 6 6 0 102400 2347 PO-B
> /usr/informix/DBSPACES/logical_logs
> 4e33e758 7 7 0 204800 4747 PO-B
> /usr/informix/DBSPACES/physical_logs
> 4e33e928 8 8 0 512000 11947 PO-B
> /usr/informix/DBSPACES/logicalslogs
> 4e33eaf8 9 8 0 512000 1997 PO-B
> /usr/informix/DBSPACES/logicalslogs2
> 4e33ecc8 10 9 0 512000 511847 PO-B
> /usr/informix/DBSPACES/er_dbspace
> 4d753018 11 10 0 512000 398183 398183 POSB
> /usr/informix/DBSPACES/er_sbspace
>
> Metadata 113764 7122 113764
> 4d7531e8 12 10 0 512000 398222 398222 POSB
> /usr/informix/DBSPACES/er_sbspace2
>
> Metadata 113775 0 113775
> 4d7533b8 13 10 0 512000 398222 398222 POSB
> /usr/informix/DBSPACES/er_sbspace3
>
> Metadata 113775 12891 113775
> 4d753588 14 10 0 512000 398222 398222 POSB
> /usr/informix/DBSPACES/er_sbspace4
>
> Metadata 113775 12891 113775
> 4d753758 15 11 0 512000 490979 PO-B
> /usr/informix/DBSPACES/sysadmin_dbs
> 15 active, 32766 maximum
> ==============END==============
>
> Jarrod Teale
> Fonterra
>
> DISCLAIMER:
> This email contains confidential information and may be legally privileged.
If
> you are not the intended recipient or have received this email in error,
> please notify the sender immediately and destroy this email.
> You may not use, disclose or copy this email or its attachments in any way.
> Any opinions expressed in this email are those of the author and are not
> necessarily those of the Fonterra Co-operative Group.
> http://www.fonterra.com/
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
_________________________________________________________________
Get 5 GB of storage with Windows Live Hotmail.
http://windowslive.com/Explore/Hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_5gb_112008
Sorry - Update here.
Turns out it is only when you hit a view which is a union over three
tables.
If I query and sort the underlying tables, all is well.
Query sorting the view runs through the rootdbs.
The tables are quite large - maybe there isn't enough sort space?
============VIEW======================
create view "sdev".tagsamples_lt (tag_id,utc_dt_lt,utc_dt,val) as
select x0.tag_id ,DBINFO ('utc_to_datetime', x0.utc_dt ) ,x0.utc_dt
,x0.val ::varchar(255) from "sdev".samples_flt x0 union all
select x1.tag_id ,DBINFO ('utc_to_datetime', x1.utc_dt ) ,
x1.utc_dt ,x1.val ::varchar(255) from "sdev".samples_int x1
union all select x2.tag_id ,DBINFO ('utc_to_datetime', x2.utc_dt
) ,x2.utc_dt ,x2.val from "sdev".samples_str x2 ;
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Jarrod Teale
Sent: Tuesday, 17 March 2009 11:22 a.m.
To: ids@iiug.org
Subject: Engine won't use temporary space - rootdbs full [15176]
Hey all,
I have an 11.50.UC3 engine running on RedHat EL4 It has three temp
dbspaces set up and the DBSPACETEMP variable is set to use them.
Any sort query only uses space in the rootdbs - completely filling it
up.
I have tried setting client side environment variables for DBSPACETEMP
and for PSORT_DBTEMP, neither seem to be listened to.
I have tried removing and recreating the spaces / restarting the
engine...
Any clues appreciated - I'm fed up beating my head against a wall.
=========ONCONFIG SNIPPET========
[sdev@sirrp61(10.60.66.130) ~]$ onstat -c | grep DBSPACE ROOTPATH
/usr/informix/DBSPACES/rootdbs CDR_DBSPACE er_dbspace # dbspace for
syscdr database CDR_QHDR_DBSPACE er_dbspace # CDR queue dbspace (default
same as catalog) # DBSPACETEMP:
DBSPACETEMP tempdbs1:tempdbs2:tempdbs3
ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAIT
==========END===================
===========onstat -d================
[sdev@sirrp61(10.60.66.130) ~]$ onstat -d
IBM Informix Dynamic Server Version 11.50.UC3 -- On-Line -- Up
00:24:45 -- 461932 Kbytes
Dbspaces
address number flags fchunk nchunks pgsize flags owner name
4cda5808 1 0x70001 1 1 2048 N B
informix rootdbs
4e2d9e28 2 0x42001 2 1 2048 N TB
informix tempdbs1
4e2c7e30 3 0x42001 3 1 2048 N TB
informix tempdbs2
4e296330 4 0x42001 4 1 2048 N TB
informix tempdbs3
4e296490 5 0x60001 5 1 2048 N B
informix data01
4e2965f0 6 0x60001 6 1 2048 N B
informix logical_dbs
4e296750 7 0x70001 7 1 2048 N B
informix physical_dbs
4e2968b0 8 0x60001 8 2 2048 N B
informix logical_logs
4e296a10 9 0x60001 10 1 2048 N B
informix er_dbspace
4e296b70 10 0x68001 11 4 2048 N SB
informix er_sbspace
4e296cd0 11 0x60001 15 1 2048 N B
informix sysadmin_dbs
11 active, 2047 maximum
Chunks
address chunk/dbs offset size free bpages flags pathname
4cda5968 1 1 0 32000 28953 PO-B
/usr/informix/DBSPACES/rootdbs
4e296e30 2 2 0 524288 524260 PO-B
/usr/informix/DBSPACES/tempdbs1
4e33e018 3 3 0 524288 524260 PO-B
/usr/informix/DBSPACES/tempdbs2
4e33e1e8 4 4 0 524288 524260 PO-B
/usr/informix/DBSPACES/tempdbs3
4e33e3b8 5 5 0 9773000 1434466 PO-B
/usr/informix/DBSPACES/data01
4e33e588 6 6 0 102400 2347 PO-B
/usr/informix/DBSPACES/logical_logs
4e33e758 7 7 0 204800 4747 PO-B
/usr/informix/DBSPACES/physical_logs
4e33e928 8 8 0 512000 11947 PO-B
/usr/informix/DBSPACES/logicalslogs
4e33eaf8 9 8 0 512000 1997 PO-B
/usr/informix/DBSPACES/logicalslogs2
4e33ecc8 10 9 0 512000 511847 PO-B
/usr/informix/DBSPACES/er_dbspace
4d753018 11 10 0 512000 398183 398183 POSB
/usr/informix/DBSPACES/er_sbspace
Metadata 113764 7122 113764
4d7531e8 12 10 0 512000 398222 398222 POSB
/usr/informix/DBSPACES/er_sbspace2
Metadata 113775 0 113775
4d7533b8 13 10 0 512000 398222 398222 POSB
/usr/informix/DBSPACES/er_sbspace3
Metadata 113775 12891 113775
4d753588 14 10 0 512000 398222 398222 POSB
/usr/informix/DBSPACES/er_sbspace4
Metadata 113775 12891 113775
4d753758 15 11 0 512000 490979 PO-B
/usr/informix/DBSPACES/sysadmin_dbs
15 active, 32766 maximum
==============END==============
Jarrod Teale
Fonterra
DISCLAIMER:
This email contains confidential information and may be legally
privileged. If you are not the intended recipient or have received this
email in error, please notify the sender immediately and destroy this
email.
You may not use, disclose or copy this email or its attachments in any
way.
Any opinions expressed in this email are those of the author and are not
necessarily those of the Fonterra Co-operative Group.
http://www.fonterra.com/
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Jarrod,
If you query a view with a filter on one or more of its columns, the engine
has to instantiate the view into a temp table which, as you say, can get
very large. Since 11.10 allows one to update and delete rows from the
underlying tables through a view, the view instantiation temp table has to
be logged (it didn't before that feature) so that a transaction can control
its contents. Logged tables cannot reside in TEMP dbspaces.
Solution? Add one or more non-temp dbspaces just for creating logged temp
tables and add those to DBSPACETEMP. The engine will use them before
resorting to using ROOTDBS for logged temp tables.
Art
On Mon, Mar 16, 2009 at 6:31 PM, Jarrod Teale <Jarrod.Teale@fonterra.com>wrote:
> Sorry - Update here.
> Turns out it is only when you hit a view which is a union over three
> tables.
> If I query and sort the underlying tables, all is well.
> Query sorting the view runs through the rootdbs.
>
> The tables are quite large - maybe there isn't enough sort space?
>
> ============VIEW======================
> create view "sdev".tagsamples_lt (tag_id,utc_dt_lt,utc_dt,val) as
> select x0.tag_id ,DBINFO ('utc_to_datetime', x0.utc_dt ) ,x0.utc_dt>
> ,x0.val ::varchar(255) from "sdev".samples_flt x0 union all
>
> select x1.tag_id ,DBINFO ('utc_to_datetime', x1.utc_dt ) ,>
> x1.utc_dt ,x1.val ::varchar(255) from "sdev".samples_int x1
>
> union all select x2.tag_id ,DBINFO ('utc_to_datetime', x2.utc_dt
>
> ) ,x2.utc_dt ,x2.val from "sdev".samples_str x2 ;
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Jarrod Teale
> Sent: Tuesday, 17 March 2009 11:22 a.m.
> To: ids@iiug.org
> Subject: Engine won't use temporary space - rootdbs full [15176]
>
> Hey all,
> I have an 11.50.UC3 engine running on RedHat EL4 It has three temp
> dbspaces set up and the DBSPACETEMP variable is set to use them.
> Any sort query only uses space in the rootdbs - completely filling it
> up.
> I have tried setting client side environment variables for DBSPACETEMP
> and for PSORT_DBTEMP, neither seem to be listened to.
> I have tried removing and recreating the spaces / restarting the
> engine...
>
> Any clues appreciated - I'm fed up beating my head against a wall.
>
> =========ONCONFIG SNIPPET========
> [sdev@sirrp61(10.60.66.130) ~]$ onstat -c | grep DBSPACE ROOTPATH
> /usr/informix/DBSPACES/rootdbs CDR_DBSPACE er_dbspace # dbspace for
> syscdr database CDR_QHDR_DBSPACE er_dbspace # CDR queue dbspace (default
> same as catalog) # DBSPACETEMP:
> DBSPACETEMP tempdbs1:tempdbs2:tempdbs3
> ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAIT>
> ==========END===================
>
> ===========onstat -d================
> [sdev@sirrp61(10.60.66.130) ~]$ onstat -d
>
> IBM Informix Dynamic Server Version 11.50.UC3 -- On-Line -- Up
> 00:24:45 -- 461932 Kbytes>
> Dbspaces
> address number flags fchunk nchunks pgsize flags owner name
> 4cda5808 1 0x70001 1 1 2048 N B
> informix rootdbs
> 4e2d9e28 2 0x42001 2 1 2048 N TB
> informix tempdbs1
> 4e2c7e30 3 0x42001 3 1 2048 N TB
> informix tempdbs2
> 4e296330 4 0x42001 4 1 2048 N TB
> informix tempdbs3
> 4e296490 5 0x60001 5 1 2048 N B
> informix data01
> 4e2965f0 6 0x60001 6 1 2048 N B
> informix logical_dbs
> 4e296750 7 0x70001 7 1 2048 N B
> informix physical_dbs
> 4e2968b0 8 0x60001 8 2 2048 N B
> informix logical_logs
> 4e296a10 9 0x60001 10 1 2048 N B
> informix er_dbspace
> 4e296b70 10 0x68001 11 4 2048 N SB
> informix er_sbspace
> 4e296cd0 11 0x60001 15 1 2048 N B
> informix sysadmin_dbs
> 11 active, 2047 maximum
>
> Chunks
> address chunk/dbs offset size free bpages flags pathname
> 4cda5968 1 1 0 32000 28953 PO-B
> /usr/informix/DBSPACES/rootdbs
> 4e296e30 2 2 0 524288 524260 PO-B
> /usr/informix/DBSPACES/tempdbs1
> 4e33e018 3 3 0 524288 524260 PO-B
> /usr/informix/DBSPACES/tempdbs2
> 4e33e1e8 4 4 0 524288 524260 PO-B
> /usr/informix/DBSPACES/tempdbs3
> 4e33e3b8 5 5 0 9773000 1434466 PO-B
> /usr/informix/DBSPACES/data01
> 4e33e588 6 6 0 102400 2347 PO-B
> /usr/informix/DBSPACES/logical_logs
> 4e33e758 7 7 0 204800 4747 PO-B
> /usr/informix/DBSPACES/physical_logs
> 4e33e928 8 8 0 512000 11947 PO-B
> /usr/informix/DBSPACES/logicalslogs
> 4e33eaf8 9 8 0 512000 1997 PO-B
> /usr/informix/DBSPACES/logicalslogs2
> 4e33ecc8 10 9 0 512000 511847 PO-B
> /usr/informix/DBSPACES/er_dbspace
> 4d753018 11 10 0 512000 398183 398183 POSB
> /usr/informix/DBSPACES/er_sbspace
>
> Metadata 113764 7122 113764
> 4d7531e8 12 10 0 512000 398222 398222 POSB
> /usr/informix/DBSPACES/er_sbspace2
>
> Metadata 113775 0 113775
> 4d7533b8 13 10 0 512000 398222 398222 POSB
> /usr/informix/DBSPACES/er_sbspace3
>
> Metadata 113775 12891 113775
> 4d753588 14 10 0 512000 398222 398222 POSB
> /usr/informix/DBSPACES/er_sbspace4
>
> Metadata 113775 12891 113775
> 4d753758 15 11 0 512000 490979 PO-B
> /usr/informix/DBSPACES/sysadmin_dbs
> 15 active, 32766 maximum
> ==============END==============
>
> Jarrod Teale
> Fonterra
>
> DISCLAIMER:
> This email contains confidential information and may be legally
> privileged. If you are not the intended recipient or have received this
> email in error, please notify the sender immediately and destroy this
> email.
> You may not use, disclose or copy this email or its attachments in any
> way.
> Any opinions expressed in this email are those of the author and are not
> necessarily those of the Fonterra Co-operative Group.
> http://www.fonterra.com/
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
--00163646bfccfc5e670465448e1b
Thanks Art - explains a lot - especially why we never say this under
10.00
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art Kagel
Sent: Tuesday, 17 March 2009 12:11 p.m.
To: ids@iiug.org
Subject: Re: Engine won't use temporary space - rootdbs.... [15182]
Jarrod,
If you query a view with a filter on one or more of its columns, the
engine has to instantiate the view into a temp table which, as you say,
can get very large. Since 11.10 allows one to update and delete rows
from the underlying tables through a view, the view instantiation temp
table has to be logged (it didn't before that feature) so that a
transaction can control its contents. Logged tables cannot reside in
TEMP dbspaces.
Solution? Add one or more non-temp dbspaces just for creating logged
temp tables and add those to DBSPACETEMP. The engine will use them
before resorting to using ROOTDBS for logged temp tables.
Art
On Mon, Mar 16, 2009 at 6:31 PM, Jarrod Teale
<Jarrod.Teale@fonterra.com>wrote:
> Sorry - Update here.
> Turns out it is only when you hit a view which is a union over three
> tables.
> If I query and sort the underlying tables, all is well.
> Query sorting the view runs through the rootdbs.
>
> The tables are quite large - maybe there isn't enough sort space?
>
> ============VIEW======================
> create view "sdev".tagsamples_lt (tag_id,utc_dt_lt,utc_dt,val) as
> select x0.tag_id ,DBINFO ('utc_to_datetime', x0.utc_dt ) ,x0.utc_dt>
> ,x0.val ::varchar(255) from "sdev".samples_flt x0 union all
>
> select x1.tag_id ,DBINFO ('utc_to_datetime', x1.utc_dt ) ,>
> x1.utc_dt ,x1.val ::varchar(255) from "sdev".samples_int x1
>
> union all select x2.tag_id ,DBINFO ('utc_to_datetime', x2.utc_dt
>
> ) ,x2.utc_dt ,x2.val from "sdev".samples_str x2 ;
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Jarrod Teale
> Sent: Tuesday, 17 March 2009 11:22 a.m.
> To: ids@iiug.org
> Subject: Engine won't use temporary space - rootdbs full [15176]
>
> Hey all,
> I have an 11.50.UC3 engine running on RedHat EL4 It has three temp
> dbspaces set up and the DBSPACETEMP variable is set to use them.
> Any sort query only uses space in the rootdbs - completely filling it
> up.
> I have tried setting client side environment variables for DBSPACETEMP
> and for PSORT_DBTEMP, neither seem to be listened to.
> I have tried removing and recreating the spaces / restarting the
> engine...
>
> Any clues appreciated - I'm fed up beating my head against a wall.
>
> =========ONCONFIG SNIPPET========
> [sdev@sirrp61(10.60.66.130) ~]$ onstat -c | grep DBSPACE ROOTPATH
> /usr/informix/DBSPACES/rootdbs CDR_DBSPACE er_dbspace # dbspace for
> syscdr database CDR_QHDR_DBSPACE er_dbspace # CDR queue dbspace
> (default same as catalog) # DBSPACETEMP:
> DBSPACETEMP tempdbs1:tempdbs2:tempdbs3 ONDBSPACEDOWN 2 # Dbspace down
> option: 0 = CONTINUE, 1 = ABORT, 2 = WAIT
>
> ==========END===================
>
> ===========onstat -d================
> [sdev@sirrp61(10.60.66.130) ~]$ onstat -d
>
> IBM Informix Dynamic Server Version 11.50.UC3 -- On-Line -- Up
> 00:24:45 -- 461932 Kbytes>
> Dbspaces
> address number flags fchunk nchunks pgsize flags owner name
> 4cda5808 1 0x70001 1 1 2048 N B
> informix rootdbs
> 4e2d9e28 2 0x42001 2 1 2048 N TB
> informix tempdbs1
> 4e2c7e30 3 0x42001 3 1 2048 N TB
> informix tempdbs2
> 4e296330 4 0x42001 4 1 2048 N TB
> informix tempdbs3
> 4e296490 5 0x60001 5 1 2048 N B
> informix data01
> 4e2965f0 6 0x60001 6 1 2048 N B
> informix logical_dbs
> 4e296750 7 0x70001 7 1 2048 N B
> informix physical_dbs
> 4e2968b0 8 0x60001 8 2 2048 N B
> informix logical_logs
> 4e296a10 9 0x60001 10 1 2048 N B
> informix er_dbspace
> 4e296b70 10 0x68001 11 4 2048 N SB
> informix er_sbspace
> 4e296cd0 11 0x60001 15 1 2048 N B
> informix sysadmin_dbs
> 11 active, 2047 maximum
>
> Chunks
> address chunk/dbs offset size free bpages flags pathname
> 4cda5968 1 1 0 32000 28953 PO-B
> /usr/informix/DBSPACES/rootdbs
> 4e296e30 2 2 0 524288 524260 PO-B
> /usr/informix/DBSPACES/tempdbs1
> 4e33e018 3 3 0 524288 524260 PO-B
> /usr/informix/DBSPACES/tempdbs2
> 4e33e1e8 4 4 0 524288 524260 PO-B
> /usr/informix/DBSPACES/tempdbs3
> 4e33e3b8 5 5 0 9773000 1434466 PO-B
> /usr/informix/DBSPACES/data01
> 4e33e588 6 6 0 102400 2347 PO-B
> /usr/informix/DBSPACES/logical_logs
> 4e33e758 7 7 0 204800 4747 PO-B
> /usr/informix/DBSPACES/physical_logs
> 4e33e928 8 8 0 512000 11947 PO-B
> /usr/informix/DBSPACES/logicalslogs
> 4e33eaf8 9 8 0 512000 1997 PO-B
> /usr/informix/DBSPACES/logicalslogs2
> 4e33ecc8 10 9 0 512000 511847 PO-B
> /usr/informix/DBSPACES/er_dbspace
> 4d753018 11 10 0 512000 398183 398183 POSB
> /usr/informix/DBSPACES/er_sbspace
>
> Metadata 113764 7122 113764
> 4d7531e8 12 10 0 512000 398222 398222 POSB
> /usr/informix/DBSPACES/er_sbspace2
>
> Metadata 113775 0 113775
> 4d7533b8 13 10 0 512000 398222 398222 POSB
> /usr/informix/DBSPACES/er_sbspace3
>
> Metadata 113775 12891 113775
> 4d753588 14 10 0 512000 398222 398222 POSB
> /usr/informix/DBSPACES/er_sbspace4
>
> Metadata 113775 12891 113775
> 4d753758 15 11 0 512000 490979 PO-B
> /usr/informix/DBSPACES/sysadmin_dbs
> 15 active, 32766 maximum
> ==============END==============
>
> Jarrod Teale
> Fonterra
>
> DISCLAIMER:
> This email contains confidential information and may be legally
> privileged. If you are not the intended recipient or have received
> this email in error, please notify the sender immediately and destroy
> this email.
> You may not use, disclose or copy this email or its attachments in any
> way.
> Any opinions expressed in this email are those of the author and are
> not necessarily those of the Fonterra Co-operative Group.
> http://www.fonterra.com/
>
> **********************************************************************
> **
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Oninit, the IIUG, nor any other
organization with which I am associated either explicitly or implicitly.
Neither do those opinions reflect those of other individuals affiliated
with any entity with which I am affiliated nor those of the entities
themselves.
--00163646bfccfc5e670465448e1b
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
DISCLAIMER:
This email contains confidential information and may be legally privil