Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: load ... copy yes creates huge backup file

    db2 v9.5 /aix 6.1
    I am moving a table to another TS. so I create another table in new TS and load it
    with COPY YES to /load_dir option. When I check the original (source) table size it is only 19MB:

    select substr(t.tabschema,1,10)||’.’||substr(T.tabname,1, 20) as table
    ,char(date(T.stats_time)) as statsdate ,char(time(T.stats_time)) as statstime
    ,T.CARD as rows_per_tbl, decimal(float(T.npages)/(1024/(TS.pagesize/1024)),9,2) as used_mb
    ,decimal(float(T.fpages)/(1024/(TS.pagesize/1024)),9,2) as allocated_mb
    from SYSCAT.TABLES T ,SYSCAT.TABLESPACES TS
    where T.tbspace=TS.tbspace and T.tabname=’BV_AAA’ and T.TYPE=’T’”

    TABLE STATSDATE STATSTIME ROWS_PER_TBL USED_MB ALLOCATED_MB
    ----------- ---------- --------- ------------ -------- ------------
    DBA.BV_AAA 04/19/2010 15:28:01 135689 19.84 20.00

    but load created a huge backup file for some reason (25GB):
    25924329472 Apr 19 15:27 db_name.4.db2v9i1.NODE0000.CATN0000.20100419152500 .001

    Is anything wrong here? It's not a problem with this table, but when I try
    to do it with another table with 25 million rows in it, I run out of disk space because
    LOAD...COPY YES creates a huge backup file (>630GB). Database has archival logging so
    I have to use COPY YES option.

    Thanks in advance
    Last edited by MarkhamDBA; 04-19-10 at 17:01.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    I figured I could use nonrecoverable option of load, but still don't understand why backup file created by load is so big...
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    This can be due to table containing small LOB/LF data. When there are lot of small LOB/LF, the copy image can be huge. Please see "Usage notes" here :

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows


    "The Load utility might generate a very large copy of the image file when the COPY YES option is used. This behavior is expected when the LOAD command writes out an entire buffer of data to the copy image (approximately 128kb) for every lob/lf column value that is loaded."

  4. #4
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by db2girl View Post
    This can be due to table containing small LOB/LF data. When there are lot of small LOB/LF, the copy image can be huge. Please see "Usage notes" here :

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows


    "The Load utility might generate a very large copy of the image file when the COPY YES option is used. This behavior is expected when the LOAD command writes out an entire buffer of data to the copy image (approximately 128kb) for every lob/lf column value that is loaded."
    got it. these tables have long varchar column (32700 byte length).
    thank you, Bella
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    As you have already found out you can do it with a nonrecoverable. Use this function when ever you are doing a total reload.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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