ADMINISTRASI BASIS DATA DATABASE OBJECT
Oleh : Marudi Tri Subakti
125150200111004
Fendy Yulianto
125150207111019
Nindi Nabila ‘Aini
125150200111017
Puji Indah Lestari
125150107111010
Angga Mustika Hadi
125150101111005
Kelas C – Administrasi Basis Data Kode : 125150200111004-ABD-KELAS C-03
Dosen Pengampu Satrio Agung W., S.Kom., M.Kom.
JURUSAN INFORMATIKA PROGRAM TEKNOLOGI INFORMASI DAN ILMU KOMPUTER UNIVERSITAS BRAWIJAYA September 2014
1. DATA ROW COMPRESSION Row compression, atau kadang disebut deep compression, adalah kompresi data dengan cara mengganti nilai string (dengan pattern tertentu) yang muncul secara berulangulang pada baris-bairs dengan symbol string yang lebih pendek. Row compression dilakukan dengan menggunakan static dictionary-based algoritma kompresi untuk mengkompress data melalui baris. Kamus (dictionary) ini digunakan unutk memetakan byte patterns berulang yang ada pada baris tabel untuk digantikan dengan simboll yang lebih kecil (pendek) byte nya. Simbol-simbol inilah yang kemudian menggantikan byte patterns yang lebih panjang yang ada pada baris table. Kamus kompresi (compression dictionary) ini disimpan mengikuti table nya dan diletakkan di data object portion pada table. Adapun data yang dapat dikompress yakni data yang tersimpan di baris table dasar, catatan log (log records), object storage XML, dan data LOB yang diletakkan inline (sebaris) dalam sabuah baris table. Selain itu, temporary table juga secara otomatis menggunakan kompresi dengan DB2 Storage Optimization Feature. Ada dua tipe row compression yang bisa dipilih, yakni: Classic : hanya menggunakan table-level compression dictionary (statis) Adaptive : menggunakan table-level (statis) dan page-level (dinamis) compression dictionary Keuntungan utama dari penggunaan row compression adalah dapat menyimpan data dengan ruang yang lebih sedikit (less space), sehingga dapat menghemat biaya penyimpanan data. Selain hemat biaya, compression juga dapat meningkatkan performa karena banyak query yang ad apada compressed data dapat dilakukan dengan operasi I/O yang lebih sedikit karena masing-masing I/O dapat membaca data lebih besar dari disk. Karena row compression dapat mengurangi ukuran database, maka proses backup dan restore pun juga hanya akan memakai sedikit ruang memori (space) dan dapat dijalankan dengan lebih cepat. Berikut ini query-query yang berkaitan dengan data row compression: Mengestimasi penghematan dari penggunaan classing atau adaptive dengan menggunakan statement SELECT dengan diikuti fungsi ADMIN_GET_TAB_COMPRESS_INFO. Misal untuk table dengan nama SAMPLE.T1, maka: SELECT * FROM (‘SAMPLE’,’EMPLOYEE’));
TABLE
(ADMIN_GET_TAB_COMPRESS_INFO
Membuat table yang menggunakann compression Menggunakan statement CREATE TABLE kemudian menambahkan option COMPRESS YES [ADAPTIVE/ STATIC/ VALUE COMPRESSION]. Contoh: CREATE TABLE EMPLOYEE ( ID VARCHAR (10), NAME VARCHAR (50)) COMPRESS YES ADAPTIVE;
Menerapkan compression pada table yang sudah ada Menggunakan statement ALTER TABLE kemudian menambahkan option COMPRESS YES [ADAPTIVE/ STATIC/ ACTIVATED VALUE COMPRESSION] dan bisa juga ditambahkan perintah REORG TABLE. Contoh:
ALTER TABLE EMPLOYEE COMPRESS YES ADAPATIVE; REORG TABLE EMPLOYEE;
Mengganti atau disable compression unutk table yang telah terkompres Menggunakan statement ALTER TABLE dengan option COMPRESS NO [ADAPTIVE/ STATIC] untuk me-deactivate compression, COMPRESS YES [STATIC/ ADAPTIVE] untuk berganti tipe kompresi, DEACTIVATE VALUE COMPRESSION unutk melakukan deactivate value compression. Kemudian menjalankan perintah REORG TABLE. Contoh: ALTER TABLE EMPLOYEE COMPRESS NO ADAPTIVE; REORG TABLE EMPLOYEE;
2. CONSTRAINT Constraint adalah batasan atau aturan yang ada pada table. Constraint mencegah penghapusan data dari suatu table yang mempunyai keterkaitan dengan table yang lain. Misal terdapat keterkaitan antara table DEPARTMENT dengan table EMPLOYEE. Dimana pada table EMPLOYEE menyimpan informasi kolom nomer department (DEPTNO) yang juga terdapat pada table DEPARTMENT. Jika baris dengan DEPTNO ‘10’ akan dihapus dari table DEPARTMENT, sedangkan terdapat data pegawai yang bekerja pada department tersebut, maka dengan adanya constraint antara dua table, penghapusan tersebut tidak bisa dilakukan. 2.1 Check Constraint CHECK digunakan untuk mendefinisikan suatu kondisi yang harus dipenuhi oleh tiap baris data dalam table. Hal ini menjaga agar data yang dimasukkan ke dalam field/ kolom sesuai dengan aturan yang dibuat. Constraint CHECK dapat diterapkan saat membuat table atau pada table yang sudah ada. Misal kondisi yang harus dipenuhi pada CHECK adalah bahwa nilai dari kolom DEPTNO pada table EMPLOYEE harus berada antara 10 dan 99. Saat membuat table baru CREATE TABLE EMPLOYEE ( ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR (9), DEPTNO SMALLINT CHECK (DEPT BETWEEN 10 AND 99) );
Pada table yang sudah ada ALTER TABLE EMPLOYEE ADD CONSTRAINT CHECK (DEPTNO BETWEEN 10 AND 99)
2.2 Unique Fungsinya adalah menjaga table dari terjadinya duplikasi nilai pada kolom tertentu. Namun NULL diperbolehkan menjadi nilai data dari field UNIQUE. Berikut ini contoh table EMPLOYEE yang tidak memperbolehkan setiap pegawai memiliki ID yang sama. Saat membuat table baru
CREATE TABLE EMPLOYEE ( ID INTEGER NOT NULL UNIQUE, NAME VARCHAR (9), );
Atau CREATE TABLE EMPLOYEE ( ID INTEGER NOT NULL, NAME VARCHAR (9), UNIQUE (ID) );
Pada table yang sudah ada ALTER TABLE EMPLOYEE ADD CONSTRAINT ALTER COLUMN ID SET UNIQUE
2.3 Foreign Key Constraint ini dapat didefinisikan pada suatu kolom yang ada pada suatu table, dimana kolom tersebut juga dimiliki oleh table yang lain sebagai PRIMARY KEY. Misal pada table DEPARTMENT memiliki DEPTNO sebagai Primary Key, dan pada table EMPLOYEE juga ada kolom DEPTNO sebagai Foreign Key. Saat membuat table EMPLOYEE CREATE TABLE EMPLOYEE ( ID INTEGER NOT NULL PRIMARY KEY NAME VARCHAR (9), DEPTNO SMALLINT NOT NULL, FOREIGN KEY (DEPTNO) REFERENCES DEPARTMENT (DEPTNO) );
Pada table yang sudah ada ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DEPTNO) REFERENCES DEPARTMENT (DEPTNO);
3. APPLICATION OBJECT 3.1 Stored Procedure Stored Procedure dapat berupa subroutine, modul, atau main program yang dikembangkan (dibuat) dan diekseskusi seperti kebanyakan subroutine, namun memanggilnya menggunakan SQL CALL (bukan CALL biasa). Stored procedure akan dikelola oleh DB2 setelah developer mengeksekusi perintah CREATE PROCEDURE yang akan mengupdate table catalog SYSROUTINES dan SYSPARM (v6). Stored procedure dapat mengandung multiple static dan dynamic SQL statements. Stored procedure tersebut bisa berupa SQL Procedure (ditulis dalam bahasa SQL) dan External Procedure (dapat dibuat dari segala jenis bahasa pemrograman yang membolehkan statement SQL CALL). Keuntungan utama dari stored procedure adalah berkurangnya cost dalam melakukan formating dan transmitting pesan (yang berisi sejumlah statement SQL dan statetment prosedural) pada lingkungan client maupun server. IBM mengemukakan bahwa penggunaan stored procedure menghasilkan jumlah transaksi per detik 85% lebih banyak. Stored procedure dapat juga digunakan unutk mengelola proses-proses
dari klien secara terpusat. Mereka mencegah adanya pengubahan program pada klien jika data atau prosesnya berubah. Kita dapat mengubah stored procedure tanpa mempengaruhi program yang sedang dipanggil jika parameter call nya tidak dirubah. Procedure juga dapat memanggil procedure yang lain, dengan batasan 16 level of nested termasuk procedure, triggers, dan user defined function pada local dan remote sites. Contoh sederhana dalam pembuatan stored procedure pada command window atau pada linux adalah sebagai berikut : DB2 CREATE PROCEDURE PROCEDURE_NAME BEGIN [PROCEDURE_DETAILS] END DB2 CALL PROCEDURE_NAME
Misal kita akan membuat stored procedure dengan nama show_employee pada database SAMPLE yang jika dipanggil akan menampilkan hasil dari syntax : SELECT * FROM EMPLOYEE. Maka cara membuatnya
Lewat CMD 1. Hubungkan ke database : DB2 CONNECT TO SAMPLE 2. Membuat procedure show_employee : DB2 CREATE PROCEDURE SHOW_EMPLOYEE BEGIN SELECT * FROM EMPLOYEE; END 3. Unutk menjalankannya : DB2 CALL SHOW_EMPLOYEE()
Lewat IBM Data Studio 1. File New Project Data Development Project 2. Ketikkan nama procedure : show_employee 3. Klik : Create a new connection Next 4. Nama database nya SAMPLE kemudian isikan username dan password unutk Test Connection 5. Pada Data Project Explorer, pilih nama Project yang baru saja dibuat lalu klik kanan pada folder Stored Procedure New Stored Procedure 6. Tuliskan nama stored procedure : show_employee 7. Ganti pernyataan SELECT PROCSCHEMA, PROCNAME FROM SYSCAT.PROCEDURES dengan SELECT * FROM EMPLOYEE
8. Unutk mendeploy, klik kanan pada Data Project Explorer Window Deploy 9. Unutk menjalankan stored procedure nya, klik kanan pada procedure nya Run
3.2 User Defined Function
User Defined Function (UDF) adalah extension atau tambahan yang dibuat oleh pengguna database ke fungsi SQL yang sudah ada. Tidak seperti fungsi-fungsi DB2 yang sudah built-in, UDF dapat mengambil manfaat dari system call dan DB2’s Administartive API dengan cara menyediakan sinergi yang lebih antara aplikasi dan database. Ada 5 tipe UDF yang bisa dibuat, yakni: Source. Fungsi ini dibuat dengan cara memanggil fungsi yang lain yang telah terdaftar (ada) di database. SQL Scalar, Table, atau Row. Fungsi ini ditulis dengan menggunakan SQL secara keseluruhan. Ini dapat mengembalikan single value dan dapat disepsifikasikan ke dalam sebuah SQL statement. External Scalar. Fungsi ini ditulis dengan menggunakan bahasa pemrograman tingkat atas (high-level programming language). Fungsi ini berada di external library dan terdaftar di database degan atribut-atribut yang berhubungan. External Table. Fungsi ini ditulis dengan menggunakan bahasa pemrograman tingkat atas (high-level programming language), dan mengembalikan sebuah table ke statement SQL yang memanggilnya dan dapat dijadikan klausa FROM dari statement SELECT. OLE DB External Table. Fungsi ini ditulis dengan menggunakan bahasa pemrograman tingkat atas (high-level programming language), dan mengembalikan sebuah table dari OLE DB provider ke statement SQL yang memanggilnya. Pembuatan UDF dapat memberikan beberpa keuntungan, diantaranya:
Meningkatkan Code Reuse. Jika operasi atau aksi yang biasanya dibutuhkan oleh beberrapa user dan/ atau aplikasi, UDF akan menggunakan masingmasing user atau aplikasi untuk menjalankan operasi yang diinginkan tanpa menduplikasi kode yang digunakan untuk menjalankan operasi tersebut. Meningkatkan availability. Jika kode yang biasanya digunakan untuk melaksanakan operasi tertentu yang menempel pada aplikasi, maka hanya user yang menggunakan aplikasi tersebut yang dapat menjalankan operasi tersebut. Namun jika kode untuk pengoperasiannya adalah UDF, maka operasi tersebut dapat dijalankan baik oleh application users maupun interactive users. Meningkatkan performance. Operasi sering berjalan lebih baik ketika ada di UDF disbanding ketika terikat (embedded) secara langsung di aplikasi. Hal ini terutama sekali benar ketika operasi digunakan untuk merubah data untuk proses lebih lanjut. Enkapsulasi. Ketika membuat tipe data distinct, maka secara otomatis kita secara otomatis menyediakan fungsi untuk melakukan casting data antara tipe distinct dan tipe pada sumbernya.
Berdasarkan return valuenya, ada dua tipe fungsi, yakni:
Scalar : Fungsi ini hanya mengembalikan nilai dalam bentuk single value tanpa dapat mengubah kondisi database (tidak dapat menggunakan statement INSERT, UPDATE, DELETE)
Table : Fungsi ini mengembalikan nilai dalam bentuk table dan dapat mengubah state dari database (memungkinkan penggunaan statement INSERT, UPDATE, dan DELETE) Contoh sederhana dalam pembuatan function pada command window atau pada
linux adalah sebagai berikut : DB2 CREATE FUNCTION FUNCTION_NAME() RETURNS INTEGER BEGIN [FUNCTION_DETAILS]; END DB2 VALUES FUNCTION_NAME();
Misal kita akan membuat function dengan nama total_employee pada database SAMPLE yang jika dipanggil akan menampilkan hasil dari syntax : SELECT COUNT (*) FROM EMPLOYEE. Maka cara membuatnya
Lewat CMD 1. Hubungkan ke database : DB2 CONNECT TO SAMPLE 2. Membuat procedure show_employee : DB2 CREATE PROCEDURE SHOW_EMPLOYEE BEGIN SELECT * FROM EMPLOYEE; END 3. Unutk menjalankannya : DB2 CALL SHOW_EMPLOYEE()
Lewat IBM Data Studio 1. Dengan menggunakan project yang baru saja dibuat (pada percobaan stored procedure) klik kanan pada folder User-Defined Functions New UserDefined Function menu item 2. Tuliskan nama fungsinya : total_employee 3. Ganti statement nya dengan : RETURN SELECT COUNT (*) FROM EMPLOYEE 4. Unutk mendeploy, klik kanan pada Data Project Explorer Window Deploy 5. Unutk menjalankan functionnya nya, klik kanan pada function nya Run
3.3 Trigger Trigger adalah bagian kode yang dieksekusi sebagai bentuk respon ketika ada statement modifikasi pada data, seperti INSERT, UPDATE, atau DELETE. Trigger sangat bermanfaat untuk mengimplementasikan kode yang harus dieksekusi ketika ada suatu event yang dieksekusi. Dengan menggunakan Trigger, maslaah penjadwalan dan
integritas data dapat dikurangi karena Trigger akan dieksekusi kapanpun penyebab Trigger itu dipicu. Trigger juga dapat dikode untuk mengakses dan atau mengubah table yang lain, mencetak pesan informasi, dan menentukan batasan-batasan yang kompleks. Sebagai contoh, suatu part dapat disuplai oleh banyak supplier, dan seorang supplier dapat menyupali banyak part. Maka Trigger dapat digunakan untuk mendukung scenario berikut: Jika ada aturan yang melarang tidak boleh ada lebih dari tiga supplier yang menyuplai sebuah jenis part, maka Trigger dapat dikode untuk mengecek ketika baris akan di INSERT ke table apakah sudah sesuai dengan aturan tersebut atau belum. Sebuah Trigger dapat dibuat untuk hanya mengijinkan pemesanan part yang masih ada di stok. Berikut ini waktu aktivasi Trigger berdasarkan statement SQL nya: SQL Statement
Before
After
SELECT
Yes
Yes
VALUES
Yes
Yes
CALL
Yes
Yes
SIGNAL SQLSTATE
Yes
Yes
SET transition-variable
Yes
No
INSERT
No
Yes
UPDATE
No
Yes
DELETE
No
Yes
Misal kita ingin membuat trigger jika kolom SALARY pada table STAFF diganti, maka akan mencatat log nya pada table SALESLOG dengan nilai USER dan TIMESTAMP saat itu. Maka, pada field Trigger yang baru, isi field Column dengan STAFF, Time toTrigger Action pilih After, kemudian pilih Update untuk operations. Pada tab Triggered Action, pilih Statement dan isi text area dengan mengganti statement trigger nya menjadi : INSERT INTO SALESLOG (USERID, DAYTIME) VALUES (CURRENT USER, CURRENT TIMESTAMP);
4. FEDERATED DATABASE OBJECT
DB2 memiliki kemampuan unutk membuat database virtual yang sering disebut federated database. Ketika sebuah database di federate, maka database tersebut memiliki kemampuan untuk menggabungkan data dari berbagai sumber data tanpa melihat back-end system nya. Misalnya menggabungkan atau mengakses data dari database server yang ada di Windows dan Linux. 4.1 Wrappers Wrapper adalah mekanisme dengan apa federated server berinteraksi dengan sumber data (data sources). Federated server menggunakan routine-routine yang tersimpan di library yang disebut wrapper module unutk mengimplementasikan sebuah wrapper.
Cara membuat Wrapper: CREATE
WRAPPER
wrapper_name
LIBRARY
library_name
OPTIONS
(ADD
wrapper-option-name string-constant, …)
Dimana : -
Library : menentukan nama file yang berisi modul library wrapper
-
OPTIONS : digunakan untuk mengkonfigurasi wrapper atau bagaimana DB2 menggunakan wrapper
Contoh penggunaan Wrapper 1. Mendaftarkan Wrapper NET8 (nama wrapper yang dapat digunakan untuk mengakses Oracle data sources) pada federated server. CREATE WRAPPER NET8 2. Mendaftarkan sebuah wrapper pada federated server DB2 yang menggunakan OS Linux unutk mengakses ODBC data sources dengan cara me-assign nama odbc ke Wrapper yang sedang didaftarkan ke federated server. Path yang mengandung ODBC Driver Manager didefinisikan di wrapper option MODULE '/usr/lib/odbc.so'. CREATE WRAPPER odbc OPTIONS (MODULE ‘/usr/lib/odbc.so’)
4.2 Remote Servers Remote server adalah server yang kita akses sebagai bagian dari proses client tanpa membuka koneksi terpisah, berbeda, ataupun langsung. Dengan menggunakan statement CREATE SERVER maka kita mendefinisikan data source untuk sebuah federated database
Cara membuat Server:
CREATE SERVER server_name TYPE server_type VERSION server_version WRAPPER
wrapper_name
AUTHORIZATION
remote_authorization_name
PASSWORD password OPTIONS (ADD server_option_name string_constant)
Dimana : -
Server_name : nama dari data source yang akan didefinisikan menjadi federated database
-
TYPE : menentukan tipe data source yang digunakan oleh server_name
-
VERSION : menentukan versi data source yang digunakan oleh server_name
-
WRAPPER : nama wrapper yang digunakan oleh federated server untuk berinteraksi dengan object server yang ditentukan oleh server_name
-
AUTHORIZATION : menentukan authorization ID yang dapat melakukan tindakan-tindakan yang diperlukan
-
PASSWORD : password dari authorization ID
-
OPTION : option-option yang digunakan ketika server definition dibuat.
Contoh penggunaan Server 1. Mendaftarkan server definition unutk mengakses sebuah data source Oracle 9. Dengan informasi : - CUSTOMERS adalah nama Oracle server definition, - NET8 adalah nama wrapper yang digunakan unutk mengakses data source - ABC adalah nama node tempat server database Oracle berada - CPU untuk federated server berjalan dua kali kecepatan CPU yang mendukung CUSTOMERS - I/O devices pada federated sever memproses data 1.5 kali kecepatan I/O devices pada CUSTOMERS Berikut ini statement lengkapnya CREATE SERVER CUSTOMERS TYPE ORACLE VERSION 9 WRAPPER NET8 OPTIONS (NODE ‘ABC’, CPU_RATIO ‘2.0’, IO_RATIO ‘1.5’);
4.3 User Mapping
Ketika federated server melakukan permintaan ke data source, server terlebih dahulu harus membuat sambungan ke data source. Federated server melakukan ini dengan menggunakan user ID dan password yang valid untuk menyambung ke data source, setelah tersambung anda dapat menentukan hubungan antara otorisasi ID federated server dan user ID data source beserta passwordnya. Hubungan ini dapat dibuat untuk setiap user ID yang menggunakan sistem federation untuk mengirim permintaan yang terdistribusi, inilah yang disebut user mapping. Apabila user ID dan password yang anda gunakan untuk menghubungkan ke federated database sama dengan yang digunakan untuk akses remote data source maka tidak diperlukan lagi untuk membuat user mapping.
Cara membuat User Mapping: CREATE
USER
MAPPING
FOR
authorization_name
SERVER
server_name
OPTIONS (ADD user_mapping_option_name string_constant)
Dimana : -
Authorization_name : nama user atau aplikasi yang connect ke federated database
-
Server_name : nama objek server unutk data source yang dapat diakses oleh authorization_name
OPTIONS: option-option yang digunakan ketika user mapping dibuat.
Contoh penggunaan User Mapping 1. Mendaftarkan user mapping ke Oracle data source server object ORACLE1 dengan informasi : - MARCR adalah authorization name untuk federated database local dan user ID unutk ORACLE1. Karena authorization name dan user ID sama, maka tidak membutuhkan option REMOTE_AUTHID - Passowrd unutk MARCR di ORACLE adalah NZXCZY Berikut ini statement lengkapnya CREATE USER MAPPING FOR MARCR SERVER ORACLE OPTIONS (REMOTE_PASSWORD ‘NZXCZY’);
4.4 Nicknames
Setelah membuat definisi server dan user mapping, pemilik dari federated instance membuat nickname. Nickname adalah identifier yang digunakan untuk referensi objek yang terletak di data source yang ingin diakses. Object yang mengidentifikasi nickname tersebut disebut sebagai objek data source. Nickname merupakan pointer dimana federated server mereferensi object-object ini. Nickname membantu ketika end user atau client applications meminta permintaan distribusi ke federated server, maka tidak perlu menentukan data source, permintaan cukup mereferensi objek data source dengan nickname mereka Nickname didefinisikan dengan pernyataan CREATE NICKNAME diikuti dengan pilihan kolom nickname tertentu dan pilihan nickname.
Contoh penggunaan Nickname 1. Membuat nickname untuk view, DEPARTMENT, yang ada di Schema yang disebut HEDGES. View ini disimpan di DB2 for z/OS data source yang disebut OS390A CREATE NICKNAME DEPT FOR OS390A.HEDGES.DEPARTMENT
2. Memilih semua record dari view yang dibuat di contoh 1. View harus direferensi oleh nickname nya. SELECT * FROM DEPT
(valid)
SELECT * FROM OS390A.HEDGES.DEPARTMENT
(invalid)
