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

    Unanswered: .LRG file increasing in DB2 V 9.7

    Hi All,

    In our setup db2 v 9.7 with fixpack 1

    DB21085I Instance "db2adtp" uses "64" bits and DB2 code release "SQL09071"
    with level identifier "08020107".
    Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23034", and Fix Pack
    "1".
    Product is installed at "/db/ibm/db2/V9.7".

    .LRG tablespace consumes more space it's keep on increasing.. Checked all the transaction tables are created in USERSPACE1 tablespace, It could cause this issue?

    504G /db/instances/db2XXX/db2XXX/NODE000/XXXXX/T0000002/C0000000.LRG.


    How to reclaim this space??

    Thanks,
    laxman..

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    list tablespaces show detail or sysibmadm view to see freespace
    alter tablespace..... (with resize options - reduce ...)
    see infocenter for correct syntax
    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
    Sep 2012
    Posts
    177
    Guy,

    Thanks for your reply.

    List the tablespace details for USERSPACE1

    Tablespace ID = 2
    Name = USERSPACE1
    Type = Database managed space
    Contents = All permanent data. Large table space.
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 16492864
    Useable pages = 16492832
    Used pages = 13579936
    Free pages = 2912896
    High water mark (pages) = 14640672
    Page size (bytes) = 32768
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1
    Minimum recovery time = 2014-11-14-02.55.02.000000

    In the sysibmadm tablespace utilization

    db2 => SELECT TBSP_ID, SUBSTR(TBSP_NAME,1,20) as TBSP_NAME, TBSP_TYPE, TBSP_CONTENT_TYPE, TBSP_STATE,TBSP_TOTAL_SIZE_KB,TBSP_USABLE_SIZE_KB, TBSP_USED_SIZE_KB,TBSP_FREE_SIZE_KB,TBSP_UTILIZATI ON_PERCENT FROM SYSIBMADM.TBSP_UTILIZATION

    TBSP_ID TBSP_NAME TBSP_TYPE TBSP_CONTENT_TYPE TBSP_STATE TBSP_TOTAL_SIZE_KB TBSP_USABLE_SIZE_KB TBSP_USED_SIZE_KB TBSP_FREE_SIZE_KB TBSP_UTILIZATION_PERCENT
    -------------------- -------------------- ---------- ----------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- -------------------- ------------------------
    0 SYSCATSPACE DMS ANY NORMAL 196608 196480 188928 7552 96.15
    1 TEMPSPACE1 SMS SYSTEMP NORMAL 0 0 0 0 -1.00
    2 USERSPACE1 DMS LARGE NORMAL 527771648 527770624 434553856 93216768 82.33
    3 SYSTOOLSPACE DMS LARGE NORMAL 32768 32640 2816 29824 8.62
    4 SYSTOOLSTMPSPACE SMS USRTEMP NORMAL 0 0 0 0 -1.00

    5 record(s) selected.


    USERSPACE1 IS USING 82.33% how to alter this USERSPACE1 issue in production environment.

    Thanks,
    laxman..

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as indicated before : the infocenter keeps all detailed info about all commands : alter tablespace..
    http://www-01.ibm.com/support/knowle.../t0005185.html
    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

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    additional comment :
    if this is a production db, why have all data been created in userspace1
    not a good idea to just have 1 tablespace with complete db
    recovery entity is limited to restore tablespace (in your case = db)
    look at the books : best practices.. in developerswork about db-design and physical design...
    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

Posting Permissions

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