IC86779: INGEST UTILITY: UP TO 10X PERFORMANCE DEGRADATION FROM BETA1R2 TO GA WHEN INGESTING DATES, TIMES, AND TIMESTAMPS

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • 1.  Problem description
    During a POC the customer found that ingesting almost 10M rows
    into
    their table took from 50x to 100x longer using the GA product
    compared to Beta1R2.  Part of this was due to the problem
    described
    in APAR IC85355 (performance problem in db2GetRowPartNum).  The
    fix
    for APAR IC85355 has been delivered to V10.1 FP1.  But even with
    
    that fix the performance degradation was still anywhere from 2x
    to
    10x. The problem can be seen in these timings from Beta1r2, GA
    without the fix for APAR IC85355, and GA with the fix for APAR
    IC85355:
    
    $ cat time.beta1r2
    real    0m18.14s
    user    0m0.01s
    sys     0m0.08s
    
    $ cat time.ga
    real    16m27.99s
    user    0m0.02s
    sys     0m0.04s
    
    $ cat time.ga.fix
    real    0m31.17s
    user    0m0.02s
    sys     0m0.05s
    
    Various configurations show different amounts of degradation,
    but in all cases Beta1r2 performs best, followed by GA with the
    fix for APAR IC85355, followed by GA.
    
    2.  Operating system and level: AIX
    
    3.  Client, server, and gateway information
     a. Client information, if applicable
      Project:
      Build Level:
      Environment (including Platform and bitness):
    
     b. Gateway information, if applicable
      Project:
      Build Level:
      Environment (including Platform and bitness):
    
     c. Server information, if applicable
      Project:
      Build Level:
      Environment (including Platform and bitness):
    
    4.  System setup
    (For example, if your database is partitioned, this is where
    this information should be included.  Provide clearcase view
    name, and contents of db2nodes.cfg file, including catalog,
    coordinator node information. Also which machine name has which
    node number(s) for DPF.)
    
    5.  Diagnostic information
    (This section should include applicable db2diag.log entries,
    stack traces, lineofcode tool output, and formatted dump files.
    Also include any other FODC or PD tool output that helped you or
    will help Development diagnose the failure you're opening this
    defect for.  Include the location of all files not attached but
    referenced by this defect.)
    
    6.  How to reproduce the problem
    (Include the name of the bucket and snippet of existing or
    planned test unit that exposed the failure described above.  A
    "repro" should be as small as possible. Also, include the degree
    of difficulty of the repro.  A repro is Easy if it can be done
    on demand, Medium if it can be done after several attempts, and
    Hard if it is not possible.)
    
    setup.clp
    ----------
    DB2START;
    CREATE DB testdb;
    CONNECT TO testdb;
    
    CALL SYSPROC.SYSINSTALLOBJECTS('INGEST', 'D', NULL, NULL);
    CALL SYSPROC.SYSINSTALLOBJECTS('INGEST', 'C', NULL, NULL);
    
    DROP TABLE my_table;
    CREATE TABLE my_table (
       "ORDERKEY" BIGINT NOT NULL,
        "SHIPDATE" DATE NOT NULL ,
        "COMMITDATE" DATE NOT NULL ,
        "RECEIPTDATE" DATE NOT NULL)
    DISTRIBUTE BY HASH("ORDERKEY");
    
    ingest.sh
    ----------
    db2 -tvf setup.clp
    db2 -v "INGEST SET disk_write 0"
    db2trc on -m "*.*.SQLO,SQLUDI,SQLU.*.*" -perfcount -t
    
    # The input file has about 10 million records.
    time db2 -v "INGEST FROM FILE input_file.del \
       FORMAT DELIMITED BY '|' \
       RESTART OFF \
       INSERT INTO my_table"
    
    db2trc dump cdi_perf.dmp
    db2trc off
    db2trc perffmt cdi_perf.dmp cdi_perf.fmt
    
    Then look at the timings in the performance trace.
    
    7. Did anything in particular seem to cause this problem?
    
    8.  Is this problem a regression?  If so, please provide details
    on the last successful build level similar to (3) above.
    A regression from Beta1r2.
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description.                                       *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 10.1 Fix Pack 2.                      *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 Version 10.1 Fix Pack 2.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC86779

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-09-24

  • Closed date

    2013-04-22

  • Last modified date

    2013-04-25

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSN

       UP



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

10.1

Reference #:

IC86779

Modified date:

2013-04-25

Translate my page

Machine Translation

Content navigation