Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2014
    Posts
    7

    Unanswered: Issue with query used in view, which is over running the space

    Hello,

    We have a view, created using an select query. When I run (select count(*) the view) or just run the (select query used inthe view seperately) it is running for a while untill the space is filled up and throwing an error below:

    db2 "select count(*) from adsiw.v_adsiw_summ_data with ur"

    1
    -----------
    SQL0968C The file system is full. SQLSTATE=57011

    we are using temp space as SMS

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    maybe the view is based on joins or other and tempspace is filling up filesystem
    describe the view - db2level - platform
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Without the view sql and table structures, there isn't much we can say about your issue.
    Dave

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Seems that the optimizer underestimates the temp space needed for an access plan chosen.
    It's better to understand why, but you can try to influence the optimizer by setting the following registry variable as well:
    DB2_OPT_MAX_TEMP_SIZE
    Regards,
    Mark.

  5. #5
    Join Date
    Mar 2014
    Posts
    7

    attacing the sql

    db version : db2 ese v9.7, Os : aix

    attaching the query used.
    Attached Files Attached Files

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    J,
    There still isn't much we can tell you with the info you have provided. My guess would be you are missing an index on one of these tables you are left outer joining to, Your main table is very large and from what you have shown us thus far you are not supplying any limiting criteria. Have you run an explain to see what type of access path you are getting to each of the tables? I would think you have one or more tablespace scans and probably some materialized result sets being generated.
    As I said previously, we would need to know table structures, including what indexes exist. The visual explain info would help tremendously.
    Dave

  7. #7
    Join Date
    Mar 2014
    Posts
    7

    attaching the explain plan

    Thanks for you post and replies,

    Attaching the explain plan on the select query.
    Attached Files Attached Files

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If removed whole GROUP BY clause(consequently remove MAX functions from MAX(BMS.OPPTY_NAME) AS PROJECT_NAME, MAX(CUST.CUSTOMER_NAME), MAX(CUST.UPPER_CUSTOMER_NAME) )
    and DISTINCT keyword,
    what duplicated rows did you got?

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I wonder why grouping was neccesary?

    If grouping was really neccesary,
    it might imply that there might be multiple OPPTY_NAME for a OPPORTUNITY_ID or multiple CUST.CUSTOMER_NAME(or CUST.UPPER_CUSTOMER_NAME) for a CUST.CUSTOMER_NUMBER.

    (1) BMS.OPPORTUNITY_ID was a grouping column.
    And, "MAX(BMS.OPPTY_NAME) AS PROJECT_NAME" was in select list.

    I guessed OPPTY_NAME correspond to OPPORTUNITY NAME.
    If my guess was right,
    it might be unusual there are multiple OPPTY_NAME(i.e. OPPORTUNITY NAME) for a OPPORTUNITY_ID.

    (2) Same guess might be possible for BMS.CUSTOMER_NUMBER and MAX(CUST.CUSTOMER_NAME) or MAX(CUST.UPPER_CUSTOMER_NAME).

    There was a condition "ON BMS.CUSTOMER_NUMBER = CUST.CUSTOMER_NUMBER",
    then there might be multiple CUST.CUSTOMER_NAME or CUST.UPPER_CUSTOMER_NAME for a BMS.CUSTOMER_NUMBER(same as CUST.CUSTOMER_NUMBER).

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The explain info is just as I had guessed above. Please take note from your explain the table scans you are getting. Can you create indexes for those tables on the columns that you are using in your joins? In the View SQL you have SELECT DISTINCT, is that really needed, considering that you are performing a group by? When using the view, you should be providing indexable input for the main table(BMSIW.BMSIW_NON_FINANCIAL BMS) and no input for the other tables. Reason for the last part of that statement is that any criteria to filter rows from the LEFT OUTER JOINED tables on the view will then cause an inner join to the resultset of the left outer join. This may/may not cause a difference in the data returned and will kill your performance. Robert Catterall wrote about LEFT OUTER JOINs several years ago and he included links to white papers that Terry Purcell had written a couple years before, which would give you some great info to ensure you are using them properly.
    I think taking care of the lack of indexes and providing valid inputs you will be able to run this query with no problems.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •