Data Movement Utilities

db2move

Data movement tool yang berguna untuk memindahkan table yang sangat banyak antar database DB2. Dapat menggunakan command EXPORT, IMPORT, LOAD dan COPY.

db2look
Tool yang dapat melakukan banyak hal
- membuat DDL dari object database
- membuat statement update untuk mengupdate database manager dan database configuration parameter
- membuat command db2set untuk membuat db2 profile registries
- mengekstrak dan membuat laporan statistikal database
- membuat statement update untuk mereplicate statistik pada objek database

Berikut contoh sederhananya
db2look -d sample -u 8sdeny -e -o alltables.sql
– Creating DDL for table(s)
– Output is sent to file: alltables.sql

Biasanya file akan berada di document and C:\Documents and Settings\Administrator> tergantung path di command prompt

db2batch
Merupakan tool untuk membenchmark SQL atau XQuery, mempersiapkan secara dinamis, menjelaskan statement dan mengembalikan laporan. Jika menggunakan option yang lebih lengkap maka dapat mengembalikan kebutuhan waktu eksekusi statement, penggunaan memori seperti bufferpool dan cache.

Contoh sederhananya

Sebelum menjalankan command dibawah, buatlah sebuah file bernama db2batch.sql di C:\Documents and Settings\Administrator berisi sebagai berikut.
– db2batch.sql
– ————
–#SET PERF_DETAIL 3
–#SET ROWS_OUT 5
– This query lists employees, the name of their department
– and the number of activities to which they are assigned for
– employees who are assigned to more than one activity less than
– full-time.
–#COMMENT Query 1
select lastname, firstnme,
deptname, count(*) as num_act
from employee, department, emp_act
where employee.workdept = department.deptno and
employee.empno = emp_act.empno and
emp_act.emptime < 1 group by lastname, firstnme, deptname having count(*) > 2;
–#SET PERF_DETAIL 1
–#SET ROWS_OUT 5
–#COMMENT Query 2
select lastname, firstnme,
deptname, count(*) as num_act
from employee, department, emp_act
where employee.workdept = department.deptno and
employee.empno = emp_act.empno and
emp_act.emptime < 1
group by lastname, firstnme, deptname
having count(*) <= 2;

Kemudian jalankan command berikut db2batch -d sample -f db2batch.sql
Perintah di atas akan menghasilkan laporan yang sangat panjang.
Berikut sedikit potongan dari laporan yang dihasilkan

LASTNAME FIRSTNME DEPTNAME NUM_ACT
————— ———— ———————————— ———–
GEYER JOHN SUPPORT SERVICES 2
GOUNOT JASON SOFTWARE SUPPORT 2
HAAS CHRISTINE SPIFFY COMPUTER SERVICE DIV. 2
JONES WILLIAM MANUFACTURING SYSTEMS 2
KWAN SALLY INFORMATION CENTER 2

* 8 row(s) fetched, 5 row(s) output.

* Elapsed Time is: 0.017340 seconds

* Summary Table:

Type Number Repetitions Total Time (s) Min Time (s) Max Time (s)
rithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output
——— ———– ———– ————– ————– ————–
————– ————– ————– ————-
Statement 1 1 0.085676 0.085676 0.085676
0.085676 0.085676 5 5
Statement 2 1 0.017340 0.017340 0.017340
0.017340 0.017340 8 5

* Total Entries: 2
* Total Time: 0.103015 seconds
* Minimum Time: 0.017340 seconds
* Maximum Time: 0.085676 seconds
* Arithmetic Mean Time: 0.051508 seconds
* Geometric Mean Time: 0.038543 seconds
———————————————
* Timestamp: Tue Jun 09 2009 11:34:31 SE Asia Standard Time

This entry was posted in DB2 and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>