Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Egypt
    Posts
    61

    Unanswered: Trouble with large number of Rows in DB2 V7.2.5

    hi all,

    i have a history table of 4.5 million rows in DB2 V7.2.5 under AIX 4.3.3 ML 11, i got the follwoing error while inserting rows in table:

    2004-02-18-06.15.19.739961 Instance:db2inst1 Node:000
    PID:51346(db2agent (MELODY)) Appid:0A0A0A3C.C15E.040218041726
    buffer_pool_services sqlbWritePageToContainer Probe:20 Database:MELODY

    SMS Tablespace 2(USERSPACE1) is FULL or file is too large (at OS or user limit).
    Detected on Container 0. ContPage= 512143 Obj=28 Type=0

    2004-02-18-06.15.19.951587 Instance:db2inst1 Node:000
    PID:51346(db2agent (MELODY)) Appid:0A0A0A3C.C15E.040218041726
    buffer_pool_services sqlbfix Probe:1235 Database:MELODY

    DiagData
    ffff d60c

    2004-02-18-06.15.20.161279 Instance:db2inst1 Node:000
    PID:51346(db2agent (MELODY)) Appid:0A0A0A3C.C15E.040218041726
    buffer_pool_services sqlbfix Probe:3 Database:MELODY
    DIA3612C Disk was full.

    ZRC=0xFFFFD60C


    when i change the soft file limit in /etc/security/limits to bigger size, then stop and restart the db engine, this error stoped.

    can anyone tell why db2 cannot use many files in SMS table space and have this problem with file size ??

    another question is, why it take 2 minutes to count the rows in this table
    db2 "select count(*) from history"
    thakns in advance
    Last edited by hanyheggy; 02-19-04 at 09:09.
    hany heggy,
    IBM certified Professional, AIX system support
    www.melodyhits.tv

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    With a select Count(*) from table_name, DB2 either has to read all the rows of the table, or read all the index entries in one of the indexes (if an index exists).

    The other option, if you have executed runstats recently and you don't need an exact count, is to look at the CARD column of syscat.tables. See the DB2 catalog definition in the appendix of the SQL Reference Vol 1, for more information.
    Last edited by Marcus_A; 02-19-04 at 09:22.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2003
    Posts
    191

    Re: Trouble with large number of Rows in DB2 V7.2.5

    Hi hanyheggy,

    you did just the right thing. But why would you want to have DB2 assign multiple containers to SMS table spaces - the OS can do that, in the case of AIX via LVM. Furthermore, it is the OS that detected a problem with the file size and prohibited further expansion for policy reasons, and DB2 had nothing to do with this..

    Johann

    Originally posted by hanyheggy
    hi all,

    ...


    when i change the soft file limit in /etc/security/limits to bigger size, then stop and restart the db engine, this error stoped.

    can anyone tell why db2 cannot use many files in SMS table space and have this problem with file size ??

    ...


Posting Permissions

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