Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2014
    Posts
    3

    Unanswered: Need help managing SMS tablespace as DB2 CC showing capacity 100%

    Hi, we have DB2/LINUXX8664 9.5.1 and currently databse showing 100% in control center
    size - 20659 MB
    capacity 128 MB --- 100%

    whoever installed db2, created SMS tablespace (USERSPACE1) where all application tables are there and data stores , approx 44000 rows per day.

    Please suggest best possible way to manage this tablespace on regular basis.
    ----------------------
    db2 list tablespaces show detail

    Tablespace ID = 2
    Name = USERSPACE1
    Type = System managed space
    Contents = All permanent data. Regular table space.
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 2607731
    Useable pages = 2607731
    Used pages = 2607731
    Free pages = Not applicable
    High water mark (pages) = Not applicable
    Page size (bytes) = 8192
    Extent size (pages) = 64
    Prefetch size (pages) = 32
    Number of containers = 1

    --------------------------
    db2 "get snapshot for tables on REPORTER"
    Table Snapshot

    First database connect timestamp = 07/06/2014 13:43:02.096619
    Last reset timestamp =
    Snapshot timestamp = 08/13/2014 12:08:01.759264
    Database name = REPORTER
    Database path = /BackUp/db2inst2/NODE0000/SQL00001/
    Input database alias = REPORTER
    Number of accessed tables = 19

    Table List

    Table Schema = DB2INST2
    Table Name = REPORTER_STATUS ####( this is main table where data is stored every day)
    Table Type = User
    Data Object Pages = 2283121
    Index Object Pages = 98088
    Rows Read = Not Collected
    Rows Written = 22469073
    Overflows = 0
    Page Reorgs = 0

    -------------
    db2inst2@***1 ~]$ db2 get dbm cfg | grep db2inst2
    Java Development Kit installation path (JDK_PATH) = /home/db2inst2/sqllib/java/jdk64
    Diagnostic data directory path (DIAGPATH) = /home/db2inst2/sqllib/db2dump
    Default database path (DFTDBPATH) = /home/db2inst2
    TCP/IP Service name (SVCENAME) = db2c_db2inst2
    [db2inst2@*** ~]$
    -----------------------------
    db2inst2@*** ~]$ df -h
    Filesystem Size Used Avail Use% Mounted on
    /dev/mapper/VolGroup00-LogVol00
    33G 4.3G 28G 14% /
    /dev/vda1 99M 20M 75M 21% /boot
    tmpfs 2.0G 4.0K 2.0G 1% /dev/shm
    /dev/mapper/vg_DB2-lv_DB2
    50G 4.3G 43G 10% /opt
    /dev/mapper/vg_DB2-lv_backup
    30G 27G 1.6G 95% /BackUp
    [db2inst2@*** ~]$

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    SMS tablespaces will always show 100% utilization, as they are simply directories and always take exactly as much space as the files within them. For SMS tablespaces you need to monitor the utilization of file systems where they reside.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2014
    Posts
    3
    Thanks , so we have to either keep on deleting older data and reorg the table, index and also can add the storage to filesystem . Currently filesystem is almost filled (97%) and we don't have storage.So deleting is the option or we can move older data to other location ?
    Also converting this SMS ts to DMS tablespace (which again can't done via command line directly , please correct me if i m wrong as hav to create new DMS table space and copy all data to this new TS reorg etc) may help it managing it in future or SMS ts is ok as we dont have many table and data, please suggest. Thanks again

  4. #4
    Join Date
    Aug 2014
    Posts
    3

    Need help managing SMS tablespace as DB2 CC showing capacity 100%

    Thanks , so we have to either keep on deleting older data and reorg the table, index and also can add the storage to filesystem . Currently filesystem is almost filled (97%) and we don't have storage.So deleting is the option or we can move older data to other location ?
    Also converting this SMS ts to DMS tablespace (which again can't done via command line directly , please correct me if i m wrong as hav to create new DMS table space and copy all data to this new TS reorg etc) may help it managing it in future or SMS ts is ok as we dont have many table and data, please suggest. Thanks again

  5. #5
    Join Date
    Apr 2012
    Posts
    156
    If you want to move tables from userspace1 (which I would do) you can use the procedure admin_move_table. I have moved away from SMS (accept temp tablespaces) and moved to Automatic Storage, which has the benefits of dms and the benefits of SMS. In fact SMS tablespaces for everything but temp and the catalog is deprecated as of v10. If the database is an older db and was not set up with automatic storage this can be converted per the following article: Automatic Storage Databases

  6. #6
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Extract DDL with DB2look
    Export all tables in IXF then
    drop tables
    drop SMD tablespace
    create tablespace as DMS (Preferable with automatic storage)
    create tables,and indexes using DDL extracted in 1st step
    if records in tables are less use import with replace_creation option ,
    re-create in-operative views,triggers from sysibm.sysviews, and
    sysibm.systriggers

    ssumit
    ssumit

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    admin_move_table .....
    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

  8. #8
    Join Date
    Apr 2012
    Posts
    156
    I agree with Guy, admin_move_table is a lot easier and safer.

Posting Permissions

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