Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2012
    Posts
    177

    Unanswered: Getting error in select statement

    Hi ,

    In our setup Db2v9.7 with fixpack 4 partitioning databases.

    Facing issues with querying the tables.

    When i tried to execute select statement it's throughing the error:

    Unable to allocate new pages in table space "TEMPSPACE3".. SQLCODE=-289, SQLSTATE=57011, DRIVER=4.12.79


    How to get rid of this issue?

    Thanks,
    laxman..

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    So, what part of "Unable to allocate new pages in table space TEMPSPACE3" seems confusing to you?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    sms - dms ?
    filesystem full ?
    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

  4. #4
    Join Date
    Sep 2012
    Posts
    177
    Hi guy,

    Thanks for your kind reply.

    It's a DMS tablelspace, and I checked file system is not full

    Thanks,
    laxman..

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Does the auto-resize feature is enabled?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  6. #6
    Join Date
    Sep 2012
    Posts
    177
    Thanks for your kind reply aflorin.

    Auto-resize feature is not enabled for the tablespace.

    Command to create the tablespace:

    db2 "create SYSTEM TEMPORARY TABLESPACE TEMPSPACE2 pagesize 4K managed by database using (file 'tistest/temp' 2G)

    Tablespace details:
    ==============

    Tablespace ID = 6
    Name = TEMPSPACE2
    Type = Database managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 624288
    Useable pages = 624256
    Used pages = 64
    Free pages = 624192
    High water mark (pages) = 64
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1
    Minimum recovery time = 2013-03-07-05.02.12.000000

    How to enable the auto-resize for this tablespace?

    Thanks,
    laxman..

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have a look at ALTER TABLESPACE Statement syntax in InfoCenter

    --
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Sep 2012
    Posts
    177
    Hi Sathyaram,

    Even I altered the tablespace also, Tablespace details:

    Tablespace ID = 6
    Name = TEMPSPACE2
    Type = Database managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 1360720
    Useable pages = 1360608
    Used pages = 608
    Free pages = 1360000
    High water mark (pages) = 608
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 96
    Number of containers = 3
    Minimum recovery time = 2013-03-07-05.02.12.000000

    Even i am getting the same error, Could you please help me to get rid of this issue.

    Thanks,
    laxman...

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use db2pd -temptable to monitor the temporary space use while the query is running.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    What exactly are you running? Running out of temp space sounds like an infinite loop for me.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by laxman babu View Post
    When i tried to execute select statement it's throughing the error:

    Unable to allocate new pages in table space "TEMPSPACE3".. SQLCODE=-289, SQLSTATE=57011, DRIVER=4.12.79



    Even I altered the tablespace also, Tablespace details:

    Tablespace ID = 6
    Name = TEMPSPACE2
    Type = Database managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 1360720
    Useable pages = 1360608
    Used pages = 608
    Free pages = 1360000
    High water mark (pages) = 608
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 96
    Number of containers = 3
    Minimum recovery time = 2013-03-07-05.02.12.000000

    Even i am getting the same error,


    Why DMS and not SMS for temporary tablespace? You need to tune the query so that it doesn't use lots of tempspace. Get db2exfmt for this query and look where it's using tempspace.

  12. #12
    Join Date
    Sep 2012
    Posts
    177
    Hi db2girl thanks for your kind rply..

    Could you please explain how to execute the db2exfmt for that query:

    This is the select query i tried to execute it throws that tablespace error:

    SELECT DISTINCT a.site_id, a.tfm_id, a.lane_id,a.dt,a.srvy_tm,a.low_spd,a.cnt,b.declare d freeway
    FROM "db2DWH".TIS_DW_METRIC_SPD a,
    "db2DWH".TIS_LOC_LRS_DECLARED_RLTSHP b,
    "db2DWH".TIS_LOC_SITE c,
    "db2DWH".TIS_SITE_TYP_LKP d,
    "db2DWH".TIS_LOC_TFM e,
    "db2DWH".TIS_ASSMT_RESULT f,
    "db2DWH".TIS_DT b
    WHERE b.lrs_id = c.lrs_id
    AND c.site_typ_id = d.site_typ_id
    AND c.site_id = e.site_id
    AND e.site_id = f.site_id
    AND e.tfm_id = f.tfm_id
    AND f.site_id = a.site_id
    AND f.tfm_id = a.tfm_id
    AND a.data_src_cd = c.data_src_cd
    AND c.data_src_cd = f.data_src_cd
    AND a.data_intrvl_in_secs = 20
    AND a.data_src_cd = 'INDET'
    AND a.low_spd BETWEEN 60 AND 170
    AND a.is_latest_flg = 'Y'
    AND b.is_default_flg = 'Y'
    AND Coalesce(F.Sys_Data_Fix_Assmt_Flg,F.Oscltng_Assmt_ Flg,F.Matching_Assmt_Flg) In ('ACC','ATYP')
    or Coalesce(f.Sys_Data_Fix_Assmt_Flg,F.Oscltng_Assmt_ Flg,F.Matching_Assmt_Flg) = 'UNAS'
    AND f.is_latest_flg = 'Y'
    AND d.site_typ_cd = 'MIDBLOCK'
    AND f.metric_grp = 'SPEED'
    --AND a.dt between DATE('2004-07-01') and (current date + 1 month - day(current date + 1 month) days)
    AND b.declared IN ('EASTERN FREEWAY','MONASH FREEWAY', 'METROPOLITAN RING ROAD','PRINCES FREEWAY','TULLAMARINE FREEWAY','WEST GATE FREEWAY','WESTERN RING')

    And b.dt = a.dt AND b.pblc_hldy_flg = 'N'
    AND b.schl_day_flg = 'N'


    Please explain me how to execute that db2exfmt query?

    Thanks,
    laxman..

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by laxman babu View Post
    Please explain me how to execute that db2exfmt query?
    Look for db2exfmt command here:
    IBM Collecting Data for DB2 Compiler Issues - United States

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by laxman babu View Post
    Hi db2girl thanks for your kind rply..

    Could you please explain how to execute the db2exfmt for that query:

    This is the select query i tried to execute it throws that tablespace error:

    SELECT DISTINCT a.site_id, a.tfm_id, a.lane_id,a.dt,a.srvy_tm,a.low_spd,a.cnt,b.declare d freeway
    FROM "db2DWH".TIS_DW_METRIC_SPD a,
    "db2DWH".TIS_LOC_LRS_DECLARED_RLTSHP b,
    "db2DWH".TIS_LOC_SITE c,
    "db2DWH".TIS_SITE_TYP_LKP d,
    "db2DWH".TIS_LOC_TFM e,
    "db2DWH".TIS_ASSMT_RESULT f,
    "db2DWH".TIS_DT b
    WHERE b.lrs_id = c.lrs_id
    AND c.site_typ_id = d.site_typ_id
    AND c.site_id = e.site_id
    AND e.site_id = f.site_id
    AND e.tfm_id = f.tfm_id
    AND f.site_id = a.site_id
    AND f.tfm_id = a.tfm_id
    AND a.data_src_cd = c.data_src_cd
    AND c.data_src_cd = f.data_src_cd
    AND a.data_intrvl_in_secs = 20
    AND a.data_src_cd = 'INDET'
    AND a.low_spd BETWEEN 60 AND 170
    AND a.is_latest_flg = 'Y'
    AND b.is_default_flg = 'Y'
    AND Coalesce(F.Sys_Data_Fix_Assmt_Flg,F.Oscltng_Assmt_ Flg,F.Matching_Assmt_Flg) In ('ACC','ATYP')
    or Coalesce(f.Sys_Data_Fix_Assmt_Flg,F.Oscltng_Assmt_ Flg,F.Matching_Assmt_Flg) = 'UNAS'
    AND f.is_latest_flg = 'Y'
    AND d.site_typ_cd = 'MIDBLOCK'
    AND f.metric_grp = 'SPEED'
    --AND a.dt between DATE('2004-07-01') and (current date + 1 month - day(current date + 1 month) days)
    AND b.declared IN ('EASTERN FREEWAY','MONASH FREEWAY', 'METROPOLITAN RING ROAD','PRINCES FREEWAY','TULLAMARINE FREEWAY','WEST GATE FREEWAY','WESTERN RING')

    And b.dt = a.dt AND b.pblc_hldy_flg = 'N'
    AND b.schl_day_flg = 'N'


    Please explain me how to execute that db2exfmt query?

    Thanks,
    laxman..
    I got some questions, when looking into your query atfer formatting by my ways like this.
    Code:
    SELECT DISTINCT
           a.site_id
         , a.tfm_id
         , a.lane_id
         , a.dt
         , a.srvy_tm
         , a.low_spd
         , a.cnt
         , b.declared freeway
     FROM  "db2DWH".TIS_DW_METRIC_SPD           a
         , "db2DWH".TIS_LOC_LRS_DECLARED_RLTSHP b
         , "db2DWH".TIS_LOC_SITE                c
         , "db2DWH".TIS_SITE_TYP_LKP            d
         , "db2DWH".TIS_LOC_TFM                 e
         , "db2DWH".TIS_ASSMT_RESULT            f
         , "db2DWH".TIS_DT                      b
     WHERE b.lrs_id      = c.lrs_id
       AND c.site_typ_id = d.site_typ_id
       AND c.site_id     = e.site_id
       AND e.site_id     = f.site_id
       AND e.tfm_id      = f.tfm_id
       AND f.site_id     = a.site_id
       AND f.tfm_id      = a.tfm_id
       AND a.data_src_cd = c.data_src_cd
       AND c.data_src_cd = f.data_src_cd
       AND a.data_intrvl_in_secs = 20 
       AND a.data_src_cd = 'INDET' 
       AND a.low_spd BETWEEN 60 AND 170 
       AND a.is_latest_flg = 'Y'
       AND b.is_default_flg = 'Y' 
       AND Coalesce(F.Sys_Data_Fix_Assmt_Flg , F.Oscltng_Assmt_ Flg , F.Matching_Assmt_Flg)
           In ('ACC' , 'ATYP') 
       or  Coalesce(f.Sys_Data_Fix_Assmt_Flg , F.Oscltng_Assmt_ Flg , F.Matching_Assmt_Flg)
           =  'UNAS'
       AND f.is_latest_flg = 'Y'
       AND d.site_typ_cd = 'MIDBLOCK' 
       AND f.metric_grp = 'SPEED'
     --AND a.dt between DATE('2004-07-01') and (current date + 1 month - day(current date + 1 month) days)
       AND b.declared
           IN (  'EASTERN FREEWAY'
               , 'MONASH FREEWAY'
               , 'METROPOLITAN RING ROAD'
               , 'PRINCES FREEWAY'
               , 'TULLAMARINE FREEWAY'
               , 'WEST GATE FREEWAY'
               , 'WESTERN RING'
              ) 
       And b.dt            = a.dt
       AND b.pblc_hldy_flg = 'N'
       AND b.schl_day_flg  = 'N'
    (1) You used same alias "b" for two tables.
    , "db2DWH".TIS_LOC_LRS_DECLARED_RLTSHP b
    , "db2DWH".TIS_DT b

    I don't know how the fact would influence to the results and performance.


    (2) AND is precedent to OR. So I doubt the following code?
    Code:
    ...
       AND a.is_latest_flg = 'Y'
       AND b.is_default_flg = 'Y' 
       AND Coalesce(F.Sys_Data_Fix_Assmt_Flg , F.Oscltng_Assmt_ Flg , F.Matching_Assmt_Flg)
           In ('ACC' , 'ATYP') 
       or  Coalesce(f.Sys_Data_Fix_Assmt_Flg , F.Oscltng_Assmt_ Flg , F.Matching_Assmt_Flg)
           =  'UNAS'
       AND f.is_latest_flg = 'Y'
       AND d.site_typ_cd = 'MIDBLOCK' 
    ...
    this separates predicates before OR and after OR.
    meaning
    if Coalesce(F.Sys_Data_Fix_Assmt_Flg , F.Oscltng_Assmt_ Flg , F.Matching_Assmt_Flg)
    In ('ACC' , 'ATYP')
    then select rows satisfied before the predicaes
    OR
    select rows which are
    Coalesce(f.Sys_Data_Fix_Assmt_Flg , F.Oscltng_Assmt_ Flg , F.Matching_Assmt_Flg)
    = 'UNAS'
    and sasfying predicates afer the predicate.

    So, I doubt that your intention might be

    Example 1:
    Code:
    ...
       AND a.is_latest_flg = 'Y'
       AND b.is_default_flg = 'Y' 
       AND
       (   Coalesce(F.Sys_Data_Fix_Assmt_Flg , F.Oscltng_Assmt_ Flg , F.Matching_Assmt_Flg)
           In ('ACC' , 'ATYP') 
       or  Coalesce(f.Sys_Data_Fix_Assmt_Flg , F.Oscltng_Assmt_ Flg , F.Matching_Assmt_Flg)
           =  'UNAS'
       )
       AND f.is_latest_flg = 'Y'
       AND d.site_typ_cd = 'MIDBLOCK' 
    ...
    But, if your intention was Example 1, it might be simplified to
    Example 2:
    Code:
    ...
       AND a.is_latest_flg = 'Y'
       AND b.is_default_flg = 'Y' 
       AND Coalesce(F.Sys_Data_Fix_Assmt_Flg , F.Oscltng_Assmt_ Flg , F.Matching_Assmt_Flg)
           In ('ACC' , 'ATYP' , 'UNAS')
       AND f.is_latest_flg = 'Y'
       AND d.site_typ_cd = 'MIDBLOCK' 
    ...
    So, I was not sure Example 1 was your intention,
    and your original query might be exactly reflecting your requirements...
    ...

Posting Permissions

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