Sql performance Tuning

[Hevan]

Sql performance tuning in DB2

Table Trans has multiplle records for each employee. I am updating id into lookup from trans .
Currently, it takes 3 minutes to update lookup table. Both lookup and Trans has around 1.5 million test data.
Table Trans can increase upto 1 billion. Is there any better way to increase the performance. I am using db2 udb 8.2 partitoned, aix .

Table TRANS (multiple records for each combination)

id emp_name emp_cd ac_num

a01 joe cd a890
a02 john cd b345
a01 joe cd a890
a02 john cd b345
a03 john bq b345
a04 sara cd n234
a03 john bq b345

Table LOOKUP (unique record for each combination)

id emp_name emp_cd ac_num
john cd b345
joe cd a890
john bq b345
sara cd n234


Result of lookup

id emp_name emp_cd ac_num
a02 john cd b345
a01 joe cd a890
a03 john bq b345
a04 sara cd n234

USING THIS SQL IN A STORED PROCEDURE:

update lookup a set a.id = (select ltrim(rtrim(b.id)) from trans b
where a.emp_name = b.emp_name
a.emp_cd = b.emp_cd
a.ac_num = b. ac_num
fetch first 1 rows only )
where exists(select 1 from trans b
a.emp_name = b.emp_name
a.emp_cd = b.emp_cd
a.ac_num = b. ac_num );

[kalexin]

Sql performance tuning in DB2

Table Trans has multiplle records for each employee. I am updating id into lookup from trans .
Currently, it takes 3 minutes to update lookup table. Both lookup and Trans has around 1.5 million test data.
Table Trans can increase upto 1 billion. Is there any better way to increase the performance. I am using db2 udb 8.2 partitoned, aix .

Table TRANS (multiple records for each combination)

id emp_name emp_cd ac_num

a01 joe cd a890
a02 john cd b345
a01 joe cd a890
a02 john cd b345
a03 john bq b345
a04 sara cd n234
a03 john bq b345

Table LOOKUP (unique record for each combination)

id emp_name emp_cd ac_num
john cd b345
joe cd a890
john bq b345
sara cd n234


Result of lookup

id emp_name emp_cd ac_num
a02 john cd b345
a01 joe cd a890
a03 john bq b345
a04 sara cd n234

USING THIS SQL IN A STORED PROCEDURE:

update lookup a set a.id = (select ltrim(rtrim(b.id)) from trans b
where a.emp_name = b.emp_name
a.emp_cd = b.emp_cd
a.ac_num = b. ac_num
fetch first 1 rows only )
where exists(select 1 from trans b
a.emp_name = b.emp_name
a.emp_cd = b.emp_cd
a.ac_num = b. ac_num );

from a quick look, it seems you can use the merge statement. I am not sure if it is in your version of db2 through, but it is worth a try.

[Hevan]

Thanks a lot Kalexin. Merge function works perferctly.