Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150

    Unanswered: Reducing the size of the database backup

    We have a database that is getting rather large and the data it contains will be "pruned" on an ongoing basis. What I mean by that it will only keep 6 months of information in it at any given time, ie we will get rid of anything older than that.

    We ran a test prune script deleting a month's worth of data, afterwards we ran a reorg but the database backup file size was still the same size in the directory where it writes to (AIX 5.1, DB28 FP2).

    I was wondering if we were missing a step in our process

    thanks

    Mark

  2. #2
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Re: Reducing the size of the database backup

    Originally posted by mark_maz
    We have a database that is getting rather large and the data it contains will be "pruned" on an ongoing basis. What I mean by that it will only keep 6 months of information in it at any given time, ie we will get rid of anything older than that.

    We ran a test prune script deleting a month's worth of data, afterwards we ran a reorg but the database backup file size was still the same size in the directory where it writes to (AIX 5.1, DB28 FP2).

    I was wondering if we were missing a step in our process

    thanks

    Mark
    Just applying a reorg will not free any space. AFAIK
    I recall that you may need to export your data, drop and recreate you tablespaces and load the data again.

    I bet one of the guru's will give you the solution in more detail, so do not take any action yet
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  3. #3
    Join Date
    Mar 2004
    Posts
    1

    Re: Reducing the size of the database backup

    Originally posted by mark_maz
    We have a database that is getting rather large and the data it contains will be "pruned" on an ongoing basis. What I mean by that it will only keep 6 months of information in it at any given time, ie we will get rid of anything older than that.

    We ran a test prune script deleting a month's worth of data, afterwards we ran a reorg but the database backup file size was still the same size in the directory where it writes to (AIX 5.1, DB28 FP2).

    I was wondering if we were missing a step in our process

    thanks

    Mark
    You must run RUNSTATS on each table after the reorg to update the internal DB2 statistics on the table (pages free, etc). After you have done this, you will see the space you have 'pruned' return to general use by that object/table. Pretty standard procedure after any reorg in DB2.... good luck...

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

    Re: Reducing the size of the database backup

    Use the COMPRESS Option in the BACKUP Command ...

    Command Reference has the details ...



    Cheers
    Sathyaram

    Originally posted by mark_maz
    We have a database that is getting rather large and the data it contains will be "pruned" on an ongoing basis. What I mean by that it will only keep 6 months of information in it at any given time, ie we will get rid of anything older than that.

    We ran a test prune script deleting a month's worth of data, afterwards we ran a reorg but the database backup file size was still the same size in the directory where it writes to (AIX 5.1, DB28 FP2).

    I was wondering if we were missing a step in our process

    thanks

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

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The COMPRESS keyword for the backup is available in verison 8 fixpak 4 and above. It is not inlcuded in the Control Center backup for fixpak 4, but can be used with the backup command. Not sure if compress in included in fixpak 5 Command Center,
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2004
    Location
    Tallahassee, FL, USA
    Posts
    96
    Originally posted by Marcus_A
    The COMPRESS keyword for the backup is available in verison 8 fixpak 4 and above. It is not inlcuded in the Control Center backup for fixpak 4, but can be used with the backup command. Not sure if compress in included in fixpak 5 Command Center,

    Marcus_A,


    when you purge data,

    based on valumes of data

    take export of data after purge as IXF format

    get tables DDL by db2look

    db2look -d -e -x -t tablelist with space -o outputfile

    db2look -d -e -x -t tab1 tab2 tab3 -o tabddl

    drop tables purge tables & recreate it by using extracted DDL , & load it back.

    run RUNSTATS.

    that should release space, may redure your DB backup size


    I don't think you need to create tablespace , purge table enough .


    also what shayam told is also right , you compres DB backup in DB2 V8 fix pack 4

    here is syntax

    BACKUP DATABASE database-alias [USER username [USING password]]
    [TABLESPACE (tblspace-name [ {,tblspace-name} ... ])] [ONLINE]
    [INCREMENTAL [DELTA]] [{USE {TSM | XBSA} [OPEN num-sess SESSIONS]
    [OPTIONS {options-string | options-filename}] | TO dir/dev
    [ {,dir/dev} ... ] | LOAD lib-name [OPEN num-sess SESSIONS]
    [OPTIONS {options-string | options-filename}]}]
    [WITH num-buff BUFFERS] [BUFFER buffer-size] [PARALLELISM n]
    [COMPRESS [COMPRLIB lib-name [EXCLUDE]] [COMPROPTS options-string]]
    [UTIL_IMPACT_PRIORITY [priority]] [WITHOUT PROMPTING]

    C:\Documents and Settings\db2admin>db2level
    DB21085I Instance "DB2CTLSV" uses "32" bits and DB2 code release "SQL08014"
    with level identifier "02050106".
    Informational tokens are "DB2 v8.1.4.341", "s031027", "WR21326", and FixPak
    "4".
    Product is installed at "C:\DB2\IBM\SQLLIB\".




    --Lekharaju Ennam

  7. #7
    Join Date
    Sep 2003
    Posts
    237
    If you want to save space in 8.1.2, consider using the UNIX COMPRESS, or gzip. You will create a pipe with gzip in the destination directory and then backup the database to the pipe (instead of a directory)
    mota

Posting Permissions

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