IDS Forum

Re: View with left join.

Posted By: Paul Watson
Date: Friday, 17 April 2015, at 12:13 p.m.

In Response To: Re: View with left join. (Keith Simmons)

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

Messages In This Thread

• View with left join.
  FOUAD BOUKHRISS -- Wednesday, 15 April 2015, at 12:10 p.m.
  • Re: View with left join.
    Marco Greco -- Wednesday, 15 April 2015, at 12:28 p.m.
    • Re: View with left join.
      FOUAD BOUKHRISS -- Wednesday, 15 April 2015, at 2:49 p.m.
      • Re: View with left join.
        Art Kagel -- Wednesday, 15 April 2015, at 4:25 p.m.
    • Re: View with left join.
      BENJAMIN THOMPSON -- Friday, 17 April 2015, at 5:15 a.m.
      • Re: View with left join.
        Paul Watson -- Friday, 17 April 2015, at 10:43 a.m.
        • Re: View with left join.
          Keith Simmons -- Friday, 17 April 2015, at 12:07 p.m.
          • Re: View with left join.
            Paul Watson -- Friday, 17 April 2015, at 12:13 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.