IDS Forum

RE: Engine won't use temporary space - rootdbs ful

Posted By: Jarrod Teale
Date: Monday, 16 March 2009, at 6:31 p.m.

In Response To: Engine won't use temporary space - rootdbs full (Jarrod Teale)

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.

Messages In This Thread

• Engine won't use temporary space - rootdbs full
  Jarrod Teale -- Monday, 16 March 2009, at 6:21 p.m.
  • RE: Engine won't use temporary space - rootdbs ful
    Veronica Gomes -- Monday, 16 March 2009, at 6:29 p.m.
  • RE: Engine won't use temporary space - rootdbs ful
    Jarrod Teale -- Monday, 16 March 2009, at 6:31 p.m.
    • Re: Engine won't use temporary space - rootdbs....
      Art Kagel -- Monday, 16 March 2009, at 7:10 p.m.
      • RE: Engine won't use temporary space - rootdbs....
        Jarrod Teale -- Monday, 16 March 2009, at 7:20 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.