Results 1 to 9 of 9
  1. #1
    Join Date
    May 2008
    Posts
    7

    Unanswered: about loading large data into DB2 table

    Hello Gurus,

    I want to load large data( about 500,000 row) into DB2 table. using following two methods:

    (1) alter table activate not logged initially with empty table

    turn off all the related index

    insert into <table> select ... from ...

    but running very slow.

    (2) export to <flat file>
    import from <flat file > insert into <table>

    but exporting operation is very slow.

    can you tell me how to improve inserting or exporting ?

    Many thanks,
    Frank Zhang

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try the LOAD utility.

    Andy

  3. #3
    Join Date
    May 2008
    Posts
    7
    Quote Originally Posted by ARWinner
    Try the LOAD utility.

    Andy
    but my following export statement is very slow, how to make it fast:


    export to /homecomm/carbatch/frank/backup/AM_REWARD_PRG_BONUS_MONTHLY_F.dat of del
    with temp1 (AM_REWARD_PRG_NUMBER) as

    (select distinct AM_REWARD_PRG_NUMBER from AMCP.AC_D Q1 inner join AMCP.AM_BONUS_ACCOUNT_MONTHLY_F Q2 on Q1.ACCOUNT_DIM_KEY = Q2.ACCOUNT_DIM_KEY
    where Q2.AM_BONUS_ELIGIBILITY_FLAG = 'Y' and TIME_DIM_KEY = 155) ,

    temp2 ( AM_REWARD_PRG_NUMBER, AM_BONUS_CATEGORY_ID) as

    (select AM_REWARD_PRG_NUMBER, 'BUS' from temp1
    UNION
    select AM_REWARD_PRG_NUMBER, 'LOC' from temp1
    UNION
    select AM_REWARD_PRG_NUMBER, 'MTG' from temp1
    UNION
    select AM_REWARD_PRG_NUMBER, 'SAV' from temp1
    except all
    select AM_REWARD_PRG_NUMBER,AM_BONUS_CATEGORY_ID from AMCP.AC_D Q1 inner join AMCP.AM_BONUS_ACCOUNT_MONTHLY_F Q2 on Q1.ACCOUNT_DIM_KEY = Q2.ACCOUNT_DIM_KEY
    where Q2.AM_BONUS_ELIGIBILITY_FLAG = 'Y' and TIME_DIM_KEY = 155 group by AM_REWARD_PRG_NUMBER,AM_BONUS_CATEGORY_ID,AM_BONUS _ELIGIBILITY_FLAG),

    temp3 (AM_REWARD_PRG_NUMBER, AM_BONUS_CATEGORY_ID,AM_BONUS_ELIGIBILITY_FLAG ) as

    (select AM_REWARD_PRG_NUMBER, AM_BONUS_CATEGORY_ID,'N' from temp2),

    temp4 (AM_REWARD_PRG_NUMBER, TIME_DIM_KEY, AM_BONUS_CATEGORY_ID,AM_BONUS_ELIGIBILITY_FLAG ) as

    (select AM_REWARD_PRG_NUMBER, 155, AM_BONUS_CATEGORY_ID, AM_BONUS_ELIGIBILITY_FLAG from AMCP.AC_D Q1 inner join AMCP.AM_BONUS_ACCOUNT_MONTHLY_F Q2 on Q1.ACCOUNT_DIM_KEY =
    Q2.ACCOUNT_DIM_KEY where Q2.AM_BONUS_ELIGIBILITY_FLAG = 'Y' and TIME_DIM_KEY = 155 group by AM_REWARD_PRG_NUMBER,AM_BONUS_CATEGORY_ID,AM_BONUS _ELIGIBILITY_FLAG
    union
    select AM_REWARD_PRG_NUMBER,155, AM_BONUS_CATEGORY_ID,AM_BONUS_ELIGIBILITY_FLAG from temp3)

    select * from temp4

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by cetzhbo

    but exporting operation is very slow.
    That usually boils down to the optimization of the query used to select data (unless you do the export over the network, in which case the network performance also matters).
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You will have to explain what you are trying to achieve with your query, otherwise it does not make a lot of sense.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    May 2008
    Posts
    7
    Quote Originally Posted by n_i
    That usually boils down to the optimization of the query used to select data (unless you do the export over the network, in which case the network performance also matters).
    you are right, but my select statement in the export took less than half a minute to get the result.

    so I don't think this query slowed down the exporting.

  7. #7
    Join Date
    May 2008
    Posts
    7
    Quote Originally Posted by n_i
    You will have to explain what you are trying to achieve with your query, otherwise it does not make a lot of sense.

    Hi friend, this is a ETL processing in real data warehouse.

    I just want to extract data from a few data source, and then transform and loading into the target DB table , those operations are based on business requirement ( or mapping rule).

    please help me to resolve this loading issue.

    thanks very much!

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If the source and target tables are in the same DB, you can load from cursor. If not, then maybe you can set up federation and still load from cursor.

    Andy

  9. #9
    Join Date
    May 2008
    Posts
    7
    thanks guys,

    I have solved this problem.

    Many thanks
    Frank

Posting Permissions

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