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

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

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 will have to explain what you are trying to achieve with your query, otherwise it does not make a lot of sense.

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.

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!

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

thanks guys,

I have solved this problem.

Many thanks
Frank