IDS Forum

Re: Engine won't use temporary space - rootdbs....

Posted By: Art Kagel
Date: Monday, 16 March 2009, at 7:10 p.m.

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

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

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.