Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    4

    Unanswered: Tempspace full and SQL challenge

    Hi folks,
    Can anyone please help me in solving this error:

    SMS Tablespace 5(TEMPSPACE2) is FULL or file is too large (at OS or user limit).
    Detected on Container 0. ContPage= 13409 Obj=11 Type=128

    I have been going thru this now for 5 days and can not find anything else to check but the SQL, my weakest point.

    We are running DB2 Level:
    DB2 code release "SQL07024" with level
    identifier "03050105" and informational tokens "DB2 v7.1.0.60", "s020313" and
    "U481406".

    on AIX v 5.1

    Tempspace is 14G, SMS and one container
    Ulimit info:
    time(seconds) unlimited
    file(blocks) unlimited
    data(kbytes) 245760
    stack(kbytes) 16384
    memory(kbytes) 32768
    coredump(blocks) 2097151
    nofiles(descriptors) 2000

    This has to be an inefficient query but I have no idea what I can do to fix it.

    Query:
    Select a.po_id, a.poitem_num, a.PRCHGRP_ID, a.ORDER_UOM_CD, a.POITEM_QTY, a.TOT_INVCD_QTY, a.TOT_ACCPTD_QTY,

    a.POITEM_CMMITD_AMT, a.TOT_ITEM_PAID_AMT, a.VEN_NAME, a.PO_ISSUE_DT, a.SPCHG_PAID_AMT, a.ibmemp_num, a.div_cd,

    a.ledgacct_major_num, a.ledgacct_minor_num, a.ledgacct_subminor, a.deptchg_ou_id, a.invitacc_proj_num, a.iia_cap_proj_id,

    a.matl_short_desc, a.poia_cappro_num, a.rcd_delete_fg, b.orig_rqsn_id
    FROM PURCH.podetailsummary_v a , purch.rqsn_v b
    where
    a.po_id = b.po_id and
    a.poitem_num = b.poitem_num

    UNION

    Select a.po_id, a.poitem_num, a.PRCHGRP_ID, a.ORDER_UOM_CD, a.POITEM_QTY, a.TOT_INVCD_QTY, a.TOT_ACCPTD_QTY,

    a.POITEM_CMMITD_AMT, a.TOT_ITEM_PAID_AMT, a.VEN_NAME, a.PO_ISSUE_DT, a.SPCHG_PAID_AMT, a.ibmemp_num, a.div_cd,

    a.ledgacct_major_num, a.ledgacct_minor_num, a.ledgacct_subminor, a.deptchg_ou_id, a.invitacc_proj_num, a.iia_cap_proj_id,

    a.matl_short_desc, a.poia_cappro_num, a.rcd_delete_fg, ' '
    FROM PURCH.podetailsummary_v a , purch.rqsn_v b
    where not
    a.po_id = b.po_id
    and not a.poitem_num = b.poitem_num
    ****************
    PURCH.podetailsummary_v:

    create view purch.podetailsummary_v (po_id, poitem_num, PRCHGRP_ID, ORDER_UOM_CD, POITEM_QTY, TOT_INVCD_QTY,

    TOT_ACCPTD_QTY, POITEM_CMMITD_AMT, TOT_ITEM_PAID_AMT, VEN_NAME, PO_ISSUE_DT, SPCHG_PAID_AMT, ibmemp_num, div_cd,

    ledgacct_major_num, ledgacct_minor_num, ledgacct_subminor, deptchg_ou_id, invitacc_proj_num, iia_cap_proj_id,

    matl_short_desc,poia_cappro_num, rcd_delete_fg ) as (Select distinct a.po_id, a.poitem_num, a.PRCHGRP_ID, a.ORDER_UOM_CD,

    a.POITEM_QTY, a.TOT_INVCD_QTY, a.TOT_ACCPTD_QTY, a.POITEM_CMMItd_amt, a.TOT_ITEM_PAID_AMT, a.VEN_NAME, a.PO_ISSUE_DT,

    a.SPCHG_PAID_AMT, a.ibmemp_num, a.div_cd, a.ledgacct_major_num, a.ledgacct_minor_num, a.ledgacct_subminor,

    a.deptchg_ou_id, a.invitacc_proj_num, b.iia_cap_proj_id, a.matl_short_desc, a.poia_cappro_num,a.rcd_delete_fg FROM

    PURCH.PODETAILTEMP_V a, purch.inv_item_account b WHERE a.PO_ID = b.po_id AND a.POITEM_NUM = b.poitem_num Union Select

    distinct a.po_id, a.poitem_num, a.PRCHGRP_ID, a.ORDER_UOM_CD, a.POITEM_QTY, a.TOT_INVCD_QTY, a.TOT_ACCPTD_QTY,

    a.POITEM_CMMItd_amt, a.TOT_ITEM_PAID_AMT, a.VEN_NAME, a.PO_ISSUE_DT, a.SPCHG_PAID_AMT, a.ibmemp_num, a.div_cd,

    a.ledgacct_major_num, a.ledgacct_minor_num, a.ledgacct_subminor, a.deptchg_ou_id, a.invitacc_proj_num, ' ',

    a.matl_short_desc, a.poia_cappro_num,a.rcd_delete_fg FROM PURCH.PODETAILtemp_V a where a.po_id||a.poitem_num not in

    (select po_id||poitem_num from purch.inv_item_account))
    **************************

    Thanks so much for any help you can give me!

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Tempspace full and SQL challenge

    I think you need to Explain your SQL Statement and see when it performs a sort or when it creates temp tables ... The easiest place to start is visual explain ... Look at one of grofaty's posts in the forum for a link on Visual Explain Tutorial ...

    Cheers
    Sathyaram

    Originally posted by lydiap
    Hi folks,
    Can anyone please help me in solving this error:

    SMS Tablespace 5(TEMPSPACE2) is FULL or file is too large (at OS or user limit).
    Detected on Container 0. ContPage= 13409 Obj=11 Type=128

    I have been going thru this now for 5 days and can not find anything else to check but the SQL, my weakest point.

    We are running DB2 Level:
    DB2 code release "SQL07024" with level
    identifier "03050105" and informational tokens "DB2 v7.1.0.60", "s020313" and
    "U481406".

    on AIX v 5.1

    Tempspace is 14G, SMS and one container
    Ulimit info:
    time(seconds) unlimited
    file(blocks) unlimited
    data(kbytes) 245760
    stack(kbytes) 16384
    memory(kbytes) 32768
    coredump(blocks) 2097151
    nofiles(descriptors) 2000

    This has to be an inefficient query but I have no idea what I can do to fix it.

    Query:
    Select a.po_id, a.poitem_num, a.PRCHGRP_ID, a.ORDER_UOM_CD, a.POITEM_QTY, a.TOT_INVCD_QTY, a.TOT_ACCPTD_QTY,

    a.POITEM_CMMITD_AMT, a.TOT_ITEM_PAID_AMT, a.VEN_NAME, a.PO_ISSUE_DT, a.SPCHG_PAID_AMT, a.ibmemp_num, a.div_cd,

    a.ledgacct_major_num, a.ledgacct_minor_num, a.ledgacct_subminor, a.deptchg_ou_id, a.invitacc_proj_num, a.iia_cap_proj_id,

    a.matl_short_desc, a.poia_cappro_num, a.rcd_delete_fg, b.orig_rqsn_id
    FROM PURCH.podetailsummary_v a , purch.rqsn_v b
    where
    a.po_id = b.po_id and
    a.poitem_num = b.poitem_num

    UNION

    Select a.po_id, a.poitem_num, a.PRCHGRP_ID, a.ORDER_UOM_CD, a.POITEM_QTY, a.TOT_INVCD_QTY, a.TOT_ACCPTD_QTY,

    a.POITEM_CMMITD_AMT, a.TOT_ITEM_PAID_AMT, a.VEN_NAME, a.PO_ISSUE_DT, a.SPCHG_PAID_AMT, a.ibmemp_num, a.div_cd,

    a.ledgacct_major_num, a.ledgacct_minor_num, a.ledgacct_subminor, a.deptchg_ou_id, a.invitacc_proj_num, a.iia_cap_proj_id,

    a.matl_short_desc, a.poia_cappro_num, a.rcd_delete_fg, ' '
    FROM PURCH.podetailsummary_v a , purch.rqsn_v b
    where not
    a.po_id = b.po_id
    and not a.poitem_num = b.poitem_num
    ****************
    PURCH.podetailsummary_v:

    create view purch.podetailsummary_v (po_id, poitem_num, PRCHGRP_ID, ORDER_UOM_CD, POITEM_QTY, TOT_INVCD_QTY,

    TOT_ACCPTD_QTY, POITEM_CMMITD_AMT, TOT_ITEM_PAID_AMT, VEN_NAME, PO_ISSUE_DT, SPCHG_PAID_AMT, ibmemp_num, div_cd,

    ledgacct_major_num, ledgacct_minor_num, ledgacct_subminor, deptchg_ou_id, invitacc_proj_num, iia_cap_proj_id,

    matl_short_desc,poia_cappro_num, rcd_delete_fg ) as (Select distinct a.po_id, a.poitem_num, a.PRCHGRP_ID, a.ORDER_UOM_CD,

    a.POITEM_QTY, a.TOT_INVCD_QTY, a.TOT_ACCPTD_QTY, a.POITEM_CMMItd_amt, a.TOT_ITEM_PAID_AMT, a.VEN_NAME, a.PO_ISSUE_DT,

    a.SPCHG_PAID_AMT, a.ibmemp_num, a.div_cd, a.ledgacct_major_num, a.ledgacct_minor_num, a.ledgacct_subminor,

    a.deptchg_ou_id, a.invitacc_proj_num, b.iia_cap_proj_id, a.matl_short_desc, a.poia_cappro_num,a.rcd_delete_fg FROM

    PURCH.PODETAILTEMP_V a, purch.inv_item_account b WHERE a.PO_ID = b.po_id AND a.POITEM_NUM = b.poitem_num Union Select

    distinct a.po_id, a.poitem_num, a.PRCHGRP_ID, a.ORDER_UOM_CD, a.POITEM_QTY, a.TOT_INVCD_QTY, a.TOT_ACCPTD_QTY,

    a.POITEM_CMMItd_amt, a.TOT_ITEM_PAID_AMT, a.VEN_NAME, a.PO_ISSUE_DT, a.SPCHG_PAID_AMT, a.ibmemp_num, a.div_cd,

    a.ledgacct_major_num, a.ledgacct_minor_num, a.ledgacct_subminor, a.deptchg_ou_id, a.invitacc_proj_num, ' ',

    a.matl_short_desc, a.poia_cappro_num,a.rcd_delete_fg FROM PURCH.PODETAILtemp_V a where a.po_id||a.poitem_num not in

    (select po_id||poitem_num from purch.inv_item_account))
    **************************

    Thanks so much for any help you can give me!
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2003
    Posts
    4

    Re: Tempspace full and SQL challenge

    Originally posted by sathyaram_s
    I think you need to Explain your SQL Statement and see when it performs a sort or when it creates temp tables ... The easiest place to start is visual explain ... Look at one of grofaty's posts in the forum for a link on Visual Explain Tutorial ...

    Cheers
    Sathyaram

    Ok, in case anyone else has a funnky query like we had here, let me explain what I did to get this job running again.

    Instead of using the view, PURCH.podetailsummary_v , in the join and UNION statements, I exported the data from the view, loaded it into its own table in its own tablespace and used the table in the query.
    From the explain and the access plan (in the DB2 control Center, under interactive), I could see that a lot of time was spent first creating the views.
    A bonus was that the processing time has been reduced by 2/3rds.

Posting Permissions

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