View with left join.
A user's view (an inner join plus an ANSI LEFT OUTER JOIN over an 80GB table) failed with error 264/ISAM 131 "no free disk space", filling rootdbs, even though running the same SELECT directly worked fine. Explanations given: views containing ANSI-style joins can't be folded into the calling query, so the engine materialises them into a temporary table; without adequate DBSPACETEMP the temp data lands in rootdbs. Advice was to rework the view/query plan and size temp dbspaces properly (the engine falls back to rootdbs if no listed temp space has enough free room); a fix (IT05467) adds a warning message in the online log. No confirmation from the original poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello,
I've defined a view as follow :
create view v_bkhis asselect a.* from bkhis a
inner join bkcom b on a.age=b.age and a.dev=b.dev and a.ncp=b.ncp and
a.suf=b.suf
left outer join bkprocli c on c.pro != '001' ;
where bkhis is about 80 GB.
when I execute it, I've the Error :
264: Could not write to a temporary file.
131: ISAM error: no free disk spaceand I observed that rootdbs became full (all consumed by the request !!).
But when I Execute the request directly (without using the view) all things
are fine and no pb.
Could someone have an idea about this.
Thanks in advance.
On 15/04/15 17:10, FOUAD BOUKHRISS wrote:
> Hello,
> I've defined a view as follow :
> create view v_bkhis as> select a.* from bkhis a
> inner join bkcom b on a.age=b.age and a.dev=b.dev and a.ncp=b.ncp and
> a.suf=b.suf
> left outer join bkprocli c on c.pro != '001' ;
> where bkhis is about 80 GB.
>
> when I execute it, I've the Error :
>
> 264: Could not write to a temporary file.>
> 131: ISAM error: no free disk space> and I observed that rootdbs became full (all consumed by the request !!).
>
> But when I Execute the request directly (without using the view) all things
> are fine and no pb.
>
> Could someone have an idea about this.
> Thanks in advance.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
The view requires materialization - in other words the underlying query needs
to run and the results stored in a temporary table for later usage in the
query.
Your issue is that the materialized view is quite large and on top of that you
probably don't have temporary dbspaces, hence rootdbs being used instead.
You have to review the view and the underlying query plan.
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
Ok but before this, the view was as :
select * from bkhis where ncp < "0005000"; whitch returns about 70GB and nopb. I guess the cause isn't the volume of result size but joins where IDS
tries to store temporary data (in the case of executing the view and not when
executing directly the query).
Views with nonANSI queries can be "folded" into the calling query and so
are not instantiated into temp tables. ANSI style join queries cannot be
handled that way, so instantiate the view into a temp table thrn query that.
Vers 12.10 xC5 is supposed to increase the kinds of ANSI based views that
the optimizer can fold.
Art
On Apr 15, 2015 2:49 PM, "FOUAD BOUKHRISS" <foubouk@yahoo.fr> wrote:
> Ok but before this, the view was as :
> select * from bkhis where ncp < "0005000"; whitch returns about 70GB and no> pb. I guess the cause isn't the volume of result size but joins where IDS
> tries to store temporary data (in the case of executing the view and not
> when
> executing directly the query).
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e0122a3fa5c37750513c926c6
Hi Marco/Fouad, I would be interested to see the DBSPACETEMP set up. I know the engine has logic that means it will look at all the spaces listed in DBSPACETEMP and if none of them has enough free space to create the temporary table, it will use ROOTDBS instead, regardless of how much free space there is in there. Note that this is for temporary tables the engine creates behind the scenes to support queries, not the ones you explicitly create yourself with CREATE TEMP TABLE or SELECT... INTO TEMP. Unfortunately there is no way of avoiding this behaviour (expect by making your dbspaces listed in DBSPACETEMP sufficiently large). There is a very recent fix/enhancement where the engine print an informational message in the online log when rootdbs is used to support a query because none of the spaces had sufficient free space like this: 08:43:26 Session <123456> Insufficient space in temporary dbspaces: Creating the temporary table in the root dbspace, Temporary table size is 16 pages. The fix is http://www-01.ibm.com/support/docview.wss?crawler=1&uid=swg1IT05467 It is designed to prompt a DBA to make temporary spaces larger. I am not sure which, if any, of the recent standard builds have it. Ben.
I have an issue with 12.10.FC5 whereby even with 16GB of free temp space an 8 page temp table is being built in rootdbs, it wasn't happening in FC4. Also have customer on 11.10.x that has 750GB tempspace yet a view creation insists on using 600GB of space in rootdbs. Apparently back in the day that as correct Cheers Paul > Hi Marco/Fouad, > > I would be interested to see the DBSPACETEMP set up. > > I know the engine has logic that means it will look at all the spaces > listed > in DBSPACETEMP and if none of them has enough free space to create the > temporary table, it will use ROOTDBS instead, regardless of how much free > space there is in there. Note that this is for temporary tables the engine > creates behind the scenes to support queries, not the ones you explicitly > create yourself with CREATE TEMP TABLE or SELECT... INTO TEMP. > > Unfortunately there is no way of avoiding this behaviour (expect by making > your dbspaces listed in DBSPACETEMP sufficiently large). There is a very > recent fix/enhancement where the engine print an informational message in > the > online log when rootdbs is used to support a query because none of the > spaces > had sufficient free space like this: > > 08:43:26 Session <123456> Insufficient space in temporary dbspaces: > > Creating the temporary table in the root dbspace, > > Temporary table size is 16 pages. > > The fix is > http://www-01.ibm.com/support/docview.wss?crawler=1&uid=swg1IT05467 > > It is designed to prompt a DBA to make temporary spaces larger. I am not > sure > which, if any, of the recent standard builds have it. > > Ben. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com Oninit® is a registered trademark of Oninit LLC Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians
Paul Probably preaching to the converted, but have you got logged and un-logged temp dbspaces configured in the engine ?? Keith On 17 April 2015 at 15:43, Paul Watson <paul@oninit.com> wrote: > I have an issue with 12.10.FC5 whereby even with 16GB of free temp space > an 8 page temp table is being built in rootdbs, it wasn't happening in > FC4. > > Also have customer on 11.10.x that has 750GB tempspace yet a view creation > insists on using 600GB of space in rootdbs. Apparently back in the day > that as correct > > Cheers > Paul > > > Hi Marco/Fouad, > > > > I would be interested to see the DBSPACETEMP set up. > > > > I know the engine has logic that means it will look at all the spaces > > listed > > in DBSPACETEMP and if none of them has enough free space to create the > > temporary table, it will use ROOTDBS instead, regardless of how much free > > space there is in there. Note that this is for temporary tables the > engine > > creates behind the scenes to support queries, not the ones you explicitly > > create yourself with CREATE TEMP TABLE or SELECT... INTO TEMP. > > > > Unfortunately there is no way of avoiding this behaviour (expect by > making > > your dbspaces listed in DBSPACETEMP sufficiently large). There is a very > > recent fix/enhancement where the engine print an informational message in > > the > > online log when rootdbs is used to support a query because none of the > > spaces > > had sufficient free space like this: > > > > 08:43:26 Session <123456> Insufficient space in temporary dbspaces: > > > > Creating the temporary table in the root dbspace, > > > > Temporary table size is 16 pages. > > > > The fix is > > http://www-01.ibm.com/support/docview.wss?crawler=1&uid=swg1IT05467 > > > > It is designed to prompt a DBA to make temporary spaces larger. I am not > > sure > > which, if any, of the recent standard builds have it. > > > > Ben. > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > -- > Paul Watson > Tel: +1 913-674-0360 > Mob: +1 913-387-7529 > Web: www.oninit.com > > Oninit® is a registered trademark of Oninit LLC > > Failure is not as frightening as regret. > If you want to improve, be content to be thought foolish and stupid. > What this country needs are more unemployed politicians > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bdc110a2b2c6b0513edc58d
Yep :-) > Paul > > Probably preaching to the converted, but have you got logged and un-logged > temp dbspaces configured in the engine ?? > > Keith > > On 17 April 2015 at 15:43, Paul Watson <paul@oninit.com> wrote: > >> I have an issue with 12.10.FC5 whereby even with 16GB of free temp space >> an 8 page temp table is being built in rootdbs, it wasn't happening in >> FC4. >> >> Also have customer on 11.10.x that has 750GB tempspace yet a view >> creation >> insists on using 600GB of space in rootdbs. Apparently back in the day >> that as correct >> >> Cheers >> Paul >> >> > Hi Marco/Fouad, >> > >> > I would be interested to see the DBSPACETEMP set up. >> > >> > I know the engine has logic that means it will look at all the spaces >> > listed >> > in DBSPACETEMP and if none of them has enough free space to create the >> > temporary table, it will use ROOTDBS instead, regardless of how much >> free >> > space there is in there. Note that this is for temporary tables the >> engine >> > creates behind the scenes to support queries, not the ones you >> explicitly >> > create yourself with CREATE TEMP TABLE or SELECT... INTO TEMP. >> > >> > Unfortunately there is no way of avoiding this behaviour (expect by >> making >> > your dbspaces listed in DBSPACETEMP sufficiently large). There is a >> very >> > recent fix/enhancement where the engine print an informational message >> in >> > the >> > online log when rootdbs is used to support a query because none of the >> > spaces >> > had sufficient free space like this: >> > >> > 08:43:26 Session <123456> Insufficient space in temporary dbspaces: >> > >> > Creating the temporary table in the root dbspace, >> > >> > Temporary table size is 16 pages. >> > >> > The fix is >> > http://www-01.ibm.com/support/docview.wss?crawler=1&uid=swg1IT05467 >> > >> > It is designed to prompt a DBA to make temporary spaces larger. I am >> not >> > sure >> > which, if any, of the recent standard builds have it. >> > >> > Ben. >> > >> > >> > >> >> > ******************************************************************************* >> > Forum Note: Use "Reply" to post a response in the discussion forum. >> > >> >> -- >> Paul Watson >> Tel: +1 913-674-0360 >> Mob: +1 913-387-7529 >> Web: www.oninit.com >> >> Oninit® is a registered trademark of Oninit LLC >> >> Failure is not as frightening as regret. >> If you want to improve, be content to be thought foolish and stupid. >> What this country needs are more unemployed politicians >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --047d7bdc110a2b2c6b0513edc58d > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com Oninit® is a registered trademark of Oninit LLC Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians