SQLBASE TRAINING MATERIAL
Table of Contents I.
!
i
Pengenalan SQL 1.1. SejarahSQL 1.2. Kategori Perintah SQL 1.3. Objek-objek SQL 1.4. System Catalog 1 . 5 . Pemrosesan Perintah SQL 1.6. Optimizer
I1 Elemen-elemen SQL 2.1 . Penarnaan 2.2. Tipe Data 2.3. Constanta 2.4. System Keyword 2.5. Expressions 2.6. Search Conditiorl 2.7. Predicate 2.8. Function 2.9. Nilai DatelTime 2.10. Join 2.1 1. Subquery 2.12. Bind Variable
Dasar Relational Database Management (RDBM) Database Administrator (DBA) Relational Database Distributed Processing 3.4. Model Client-Server 3.5. File Server vs Database Server 3.6. Distributed Database
111, Konsep 3.1. 3.2. 3.3.
IV. Referential Integrity 4.1. Primary Key
4.2. Foreign Key 4.3. ParendChild Table 4.4. ParendChild Row 4.5. Self-Referencing Tablemow 4.6. Deleted-Comected Table
4.7. Membuat Table Dengan Rl Constraint 4.8. System Catalog Table Untuk Informasi RI 4.9. Implikasi FU 4.10. Siklus Dari Dependent Table 4.1 I . SQLTalk dan RI
V. Procedure, Trigger dan Event 5.1. SQLBase Procedure 5.2. Format Dari Procedure 5.3. Tipe Data Parameter dan Local Variable 5.4. Perintah SAL Dalarn Stored Procedure 5.5. Break 5.6. Call 5.7. If, Else dan Else If 5.8. Loop 5.9. On <procedure sate> 5.10. Return 5.11. Set 5.12. Trace 5.13. When SQLError 5.14. While 5.15. Operator 5.16. Static vs Dinamic Procedure 5.17. Penggunaan Procedure 5.18. Fungsi SAL Dalarn SQLBase 5.19. Trigger 5.20. Timer Event
VI. Database 6.1. Penempatan Database Dalarn Directory 6.2. Database Cache 6.3. Transaction Log Files 6.4. Temporary File 6.5. Partition 6.6. MAIN Database
VII. SQLBase Server for Netware Interface 7.1. Server Status Display Screen (Fl) 7.2. Process Activity Display Screen (F2) 7.3. Sistem Activity Display Screen (F3)
VIII. Tarlggurlg Jawab Database Administrator 8.1. Memulai dan Mengakhiri SQLBase Server for Netware 8.2. Membuat dan Menghapus Database
8.4. Load dan Unload Database
8.5. 8.6. 8.7. 8.8.
Membuat Read-Only Database Membuat dan Mengatur Partitioned Datat - Reorganize Database Mengecek Database Integrity
Security dan Authorization 9.1. Database Authority (Otoritas Database) 9.2. Hak Atas Table dan View 9.3. Synonyms 9.4. Views 9.5. System Catalog Back up dan Restore 10.1. Rencana Back up 10.2. Perintah-perintah Recovery 10.3. Database Backup 10.4. Database Restore 10.5. Snapshot 10.6. Backup dan Restore Database & Log File 10.7. Backup dan Restore Partitioned Database Distributed Transaction 94 1 1.1. Mengatur Distributed Transaction
1 1.2. Two-phase Commit 11.3. Kinerja .SQLBase System Catalog Case Study
rpis
I. Pengenalan SQL
SQL (Slructured Query Lmgunge) adalah sekumpulan perintah Icngkap y n n ~ dapat dimanfaatkan untuk mengakses relaiionnl database. SQL adalah bahasa standar bagi b,ul>.ak relational database dan memiliki penntah-penntah terstruktur untuk mendefinlsiknn ciar;~. incngakses data dan memanipulasi data. SQL digunakan bersma-sama dcngan bnhasa pemrograman, sehmgga SQL yang struldar tidak menuldu penntah rnieractrve screen d'inlugllt' SQL juga beroritentasi set (set oriented), artinya sebuah perintah SQL dapat niengakscs ba~h sebans maupun sekumpulan bans data. SQL bersifat non-?rocedural. Ketika SQL digunakan, ha1 yang penting adalah data apa a n g diinbinkan, bukan bagaimana data tcrsebut dihasilkan. Untuk mengakses data, kit3 han!.n perlu memberikan nama table d m column. jndi kita tidak pcrlu memikirkan access tnt~rhoci Contolmya, sebuah p e ~ t a h~cpdatrdapat mcrubah scjumlah bans data dalanl database, kitn tidak perlu membenkan lokasi baris, forillat penyunpanan dan fonnat pengaksesan data
Seiarah SOL h w a y a t SQL dimulai dan sebu'ah rnakalah yyan ditulis oleh E.F. Codd pada tahun 1970. Codd adalah seorang ahli matematika y m g bcksrja di IBM Research Laboratory di Sari Josc. California. Dalam makalah yang bcrjudul "A Relational Model of Data for Large Sharcd Data Banks" (Communication of the ACM, Vol 13, No. 6, June 1970) h i , Codd n~en~forniulasikari prinsip-pnnsip dari sistem relational untuk nlengatur database dan ~nenjelaskan allabar relational untuk mengorganisir data kc dalam table-table. Empat tahun kemudian, scbuah nlaktildi lain ditulis oleh D.D. Chanberlln d m R . F Bo!cc dengan judul "SEQUEL: A Structured English Query Language" (Proceedings of the 1974 ACM SIGMOD Workshop on Data Description, Access and Control. May 1974). Kcdua pengarang ini juga beke j a di IBM Siui Josc Research Laborator),. Makalah ini nlc~ldcfin~s~h~ui suatu bahasa yang dirancang untuk n~emenuhikebutuhan aljabar relational dari Codd. Dua tahun setelah itu, Chamberlain dan rckan-rekruinya merancang sebuah versi dari bahnsn terscbut yang diberi nama SEQUELl2, dan kemudirul IBM membangun suatu sistem protot!-pc yang dinamakan Syslem R yang mcngimplementas~kan bahasa ini. Sekitar tahun 1980, namanya kemudian drubah nlenjad~ SQL sampai hari ini. Amerrcnn Nrrrroncrl Sinndcrrd Insrirztre (ANSI) dan International Orgcrnrznrron /or Standarrzafion (ISO) men~b~mgun SQL sebagai interface standar untuk Nrlntron~llDntnbnse Management Syslem (RDBMS). Fasilitas-fasilitas yang terdapat di SQL mcnlbuatnya menjadi bahasa bag1 database rclat~on;ll ANSI, I S 0 dan Departemen Pertallanan Anlurika mcndukung SQL. Selain mudah digunakan. SQL juga mernillki kemarnpuan sebagai bahasa untuk memanipulasi database. End t1.ct.r menggunakan SQL untuk melihat, nlernasukkan, rnerubah atau menghapus data. l'rogrc-ln~nl~r menulis aplikasi dengan SQL untuk nlengakses data dalam RDBMS. DBA ( D O I L I ~ U . S L , Adminisiraror) menggunakan SQL untuk nlendefinisikan database, menjamill keanlarian data (datn security) clan merubah data sesuai dengan kebutuhan.
SQLfirrse Trrrining Material Copyright 8 1995, by PT I,'ersadara Sysre~trrndo.Jokarru
Kategori Perintah SOL Dengan SQL, klta dapat 1. Membuat table dalarn database 2. Menyimpan data 3 . Mengakses data 4. Merubah data dan struktur dari table 5 Menggabungkan dan menglutung data 6. Menjamin keamanan data Seniua perintah SQL digolongkan atas bcberapa kategori: 1. Data Defmition Language (DDL) 2. Data Manipulation Language (DML) 3 . Data Query Language (DQL) 4. Transaction Control Language (TCL) 5. Data AdmLnistration Language (DAL) 6. Data Control Language (DCL) I
,
Data Definition Language (DDL) mengandung perintah-perintah untuk membuat objek-objek database seperti table atau view seperti ALTER TABLE CREATE EVENT CREATE INDEX CREATE TABLE CREATE TRIGGER CREATE VIEW DROP ERASE Data Manipulation Language (DML) meliputi perintah-perintah untuk menambah, rnerubah atau menghapus data seperti: DELETE rNSERT UPDATE Data Query Language (DQL) mengakscs data dengan perintah SELECT. Q u e q ).arig kompleks pun dapat dilakukan dengan operator relational (seperti >, <, =, >=, atau <>). Selairl itu, query juga dapat menggunakan juin ultuk menggabungkan data dari berbagal table. Input dari satu query dapat menjadi output bagi query laimya, ha1 ini disebut sebagai subselect. Tr"ransacrron Control Longzrnge (DCL) r~lenlastikan integrltas data ketika data dlruball Penntah yang tergolong dalam kategor~in1 adalah: COMMIT ROLLBACK SAVEPOINT Data Adminisrrahon Language (DAL) mernbantu DBA untuk rnenganalisa luncrja slstcnl d a ~ i operasi. Contoh p e ~ t a h n y aadalah. AUDIT MESSAGE START AUDIT STOP AUDIT SQLLJnse Training Material Copyright
O 1995, by P1: Versada~aSystenrrndo, Jukorru
Data Control Language (DCL) rnelakukan fungsi-fungsi berlkut ini: 1. Membuat dan mengatur database 2. Membuat dan mengatur partition 3 . Mengatur hak user atas database dan table Perintah-perintah SQL yang termasuk dalanl kategori ini adalah: ALTER DATABASE DEINSTALL DATABASE ALTER DBAREA DROP DATAI3ASE ALTER PASSWORD DROP EVENT ALTER STOGROUP DROP INDEX CHECK DATABASE DROP STOGROUP CHECK INDEX DROP SYNONYM CHECK TABLE DROP TABLE CREATE DATABASE DROP TRIGGER CREATE DBAREA DROP VIEW CREATE EVENT GRANT
CREATE INDEX
INSTALL DATABASE
CREATE STOGROUP CREATE SYNONYM CREATE TABLE CREATE TRIGGER CREATE VIEW
LOCK DATABASE REVOKE SET DEFAULT STOGROUP UNLOCK DATABASE UPDATE STATISTICS
Obiek-obiek SOL Objek-objek SQL meliputi: 1. Database 2. Table 3, lndex 4. View
Database adalah kumpulan dari objek-objek SQL. Mengumpulkan data ke dalani satu database akan mernudahkan luta untuk rnernulai d m mengakhlri akses dalarn satu opcrasl. Satu database terdiri dari satu atau lebh table. Setiap table mcmiliki nanla datl nlengandung sejumlah kolom (column)dan baris (row). Setiap kolom memilllu nama d m tipe data tertentu Setiap table biasanya memiliki primary key. Primary key adalah kolom / kumpulan koloni yang nilainya secara unik rnembedakan tiap baris. Kolom yang bcrasal dari tablc lain dinamakanforeign key. Foreign key berisi nilai yang sarna dengan nilai primary key dari tablc yang pertarna. Ketentuan bahwa nilai dari jorergn key hams terdapat dalam table lain discbur re)renlial conslrainl.
I n k adalah sekumpulan pointer dalarn table. Index disimpan secara terpisah dari table. Setiap index didasarkan atas nilai dan satullebih kolorn dalam table. User yang niengakses table tidak perlu mengetahui apakah SQLBase menggunakan mdex atau tidak. SQLBasc sendiri akan menentukan apakah index harus digunakan dalam pengaksesal tablc. lndex memberlkan dua keuntungan: 1. Memperbaiki kinerja (perjbrmancr) sehingga pengaksesan menjadi lebih cepat. 2. Menjarnin keunikan data schingga setiap table .ymg memiliki unique rndex tidak boleh memiliki dua baris data yang bernilai sanla . SQLBase Tminl'ng Material Copyr~ghtO 1 9 Y j . by PT Versadata Syslerntndo. Jakarta
M o v d~gunakansebaga~cara altemat~funtuk merepresentasrkan data dal'm satulleb~htablc Sebuah vlew dapat mengandung semua/sebaglan kolom dan satulleb~hbase table K ~ t ajuga dapat membuat vlew yang d~dasarkanatas vlcw lam atau kombmas~vlcw dcngan tablc lam V ~ e wdapat d~gunakansepertl table Jlka tablc rncmllllu tempat penylmpanan data, vlew t l d d memiliklnya SQLBase tidak menympan data ke dalam view itu sendin, tetapl kc dalam brrsc table ybs View memungkinan user yang berlalnan untuk mengakses data yang sans dengnn cara yang berbeda-beda, sehlngga setlap user mendapatkan data yang sesual dengan kebutuhannya
System Catalog Sysrrm cotulog (dura dictiunay) nlcnjrlmpan informasi table yang telah dibuat untuk sctlap database. Sysrem colalog berisi lnformasi tcntang table, view, column, index d m .seczrr/[J7 untuk database. Setiap kali terjadi pembuatan, perubahan dan penghapusan atas objck database, SQLBase merubah baris-baris dalam table system catalog. Sysrem calalug mengandung nama, ukuran, tipe clan nilai yang valid dari setiap kolom yarzg tcrslrnparl dalarn table. System catalog juga berisi inforl~iasi tentang table dan view yang terdapat dalam database. User dapat mengakses system catalog sama seperti ketlka mengakses table-tablt: lain.
Pemrosesan Perintah SOL Terdapat 4 tahap dasar d a r ~pemrosesan pcrlntah: 1 . Porse Mengecek apakah p e ~ t a telah h ditul~skansecara benar Memecahkan perintah ke &lam koinponen-komponen untuk optimizer Mendapatkan nama table dan kolom dalam system catalog 2. Optimize Menggantikan nama kolom view d m nama table dengan nanla sesungguhn~,a Mendapatkan statistik atas data sruroge dari system catalog Mengidentifikas~kanuccrss path yang mungkin Menglutung "harga" dari setiap access path Memilih alternatif yang terbaik 3 . Membuat execution code Menghasilkan rencana untuk menjalankan perinrah 4. Melaksanakan perintah
Optimizer Dalarn SQLBase, kita mengakses data yang dlinglnkan n~elaluiperlntah SQL d m SQLBase akan menentukan bagairnana data tersebut dapat diakses dengan menggunakan opr~mizer SQLBase memilih access yoth bcrdasarkan index, carolog statrstic yarlg tcrscdia dan komposisi dari perintah SQL ybs. Beberapa pilihan yang mungkm dapat dllakukan: 1. Mengakses index tanpa nlenibaca table data
SQLUuse Trliining Material Copyr~ght8 1995. by
Versudura Sysletnrndo. Jakurra
Jika data yang diing~nkanadalah index, maka cara ini merupakan pengaksesan yang paling efisien. 2. Mengakses index dan membaca table data Dalam situasi ini, perintah SQL dicocokkan dengan index. Hanya data yang memenuhi syarat saja yang dibaca &ri table. 3. Table scan Semua page dan baris data hbaca.
11. Elemen-Elemen SQL
Nama objek disebut sebagai identrfier dalam SQL. Nama table, nama column dan nanla index adalah contoh-contoh dari identrfier. Identvier sendiri dapat dibedakan atas ordinary rdentlfier dan delimited idenhfier: 1 . Ordinary Identifier Dirnulai dengan huruf atau karakter khusus (#,@ atau $). Identifier ini dapat b e n ~ p a huruf, angka dan garis bawah Hanya llama database saja yang dimulai dengan huruf dan hams mengandung karakter alphanumeric. 2 . Delimited ldent$er Dapat mengandung semua jenis karakter, termasuk karakter khusus seperti blank dan titik. Identifier ini dapat dimulai dengan angka. Sifat dari identrfier hi adalah case-sens~t~vz Delimited idenhjer hams dberi tanda kutip. Contoh: " 4 . ~ identifier". 7
u.
Nama dapat berupa long idenhfier maupun short idenhfier, atau identgier yang di-qual$ecl' oleh idenhfier yang lain. Panjang rnaksimum dari short identifier adalah 8 karakter, sedangkarl panjang maksimum dari long identrfier adalah 18 karakter. Contoh-contoh nama yang dapat diberikrul: CHECKS AMOUNT-OF-$ :CHKNUM $500 "NAME & NO." #CUSTOMER :3 Berikut ini merupakan daftar semua nama yang terdapat dalarn SQLBasc scrta panjang maksimum yang diij inkan: Tipe Identifier Panjang Maksimum 8 Authorization ID Bind Variable 18
I Column
Command Correlation I Database Event Index Password Procedure Synonym Table Trigger
I
1 View
18 18 18 8 18 18 8 18 18 18 18 18
SQLUaue Troining Material Copyrrghr (D 199$, by P7: Versadara Sysremrndo. Jukorro
1 I
,
T i ~ Data e (Data Type) Secara umum, tipe data yang digunakan SQLBase dalam menyimpan data ialah: 1. Character 2. Numeric 3. Date and time Tipe data menentukan: 1. Nilai dan panjang data yang disln~pandalant database 2. Format penarnpilan ketika data ditampilkan Nilai null menandakan data tidak tersedia Semua tipe data dapat mengaldung nilai null NLIC~I null tidak sama dengan zero atau bll-mk. Nilai null tidak dapat lebih besar, lcbih kccil atau sama dengan nilai-nilai lain, termasuk nila~null lain. Untuk mendapatkan kolom yang mcln~lik~ nilai null ini, predtkat NULL hams diberlkan. String yang kosong memiliki nilai nzlll.
Tipe data Character S t ~ character g terdri dari urutan huruf, digit atau karakter khusus. Tipe data ini dismpan secara case-sensitive. Yang termasuk dalam kategori ini adalah: I . CHAR (atau VARCHAR) Panjang dari tipe data ini harus diberikan dan tidak boleh lebih dari 254 byte. 2. LONG VARCHAR (atau LONG) Perbedaan LONG dengan CHAR adalah bahwa LONG dapat menyimpan stnng yang panjangnya leblh dari 254 byte. LONG dapat digunakan seperti BLOB (Binary Largc Object). Kita dapat menyimpan string, binary maupun bitmap dalam tipe data ini. Tipe data ini disimpan seperti linked-list dari page. Jlka tidak ada data yang disimpan, berart~ tidak ada page yang dialokasrkan. Tetapi bila ada data, jurnlah page yang dialokasikarl sesuai dengan besarnya data yang disimpan.
Tipe data Numeric Tipe data numerik yang terdapat dalanl SQLBase ~alah: Tipe data yang eku~valen Tipe data DECIMAL (atau DEC) DOUBLE PRECISION INTEGER (atau INT) FLOAT REAL NUMBER SMALLINT Precision dan scale datur secara internal oleh SQLBase: I . Precision merupakan jurnlah keseluruhan dari digit yang diijinkan 2. Scale merupakan jumlah digit desimal
Tipe data yang termasuk dalarn kategori in1 adalah 1. NUMBER T ~ p edata mi merupakan superset d a r ~semua tlpe data numerlk laltnya Tlpe dat,~ 1111 mendukung sampai 23 diglt prec~s~on NUMBER dgunakan dalarn situasi bila User tldak perlu mempedulikan prccrsron User mengrnglnkan SQLBase member~kanprecrsron terbcsar secara otonlatls 2 DECIMAL (atau DEC)
SQLUase
rain in^ Material
Copyr~ghtO 1995, by PT I'errudala Sysrernrndo. Jakurra
3.
4. 5. 6.
7.
!
Default precision & scale adalah 5 & 0, sedangkan h g i t maksm~umyang dapat dis~inpan ialah 15. Tipe data ini digunakan bila user ingin mengontrol preclsron dan scale, misalnya untuk keperluan nilai mata uang. INTEGER (atau INT) Tipe data ini tidak memiliki scale. Panjnrlg rnaksimalnya adalah 10 digit. SMALLINT SMALLINT juga tidak memiliki scale. Panjang maks~malrlyaadalah 5 digit DOUBLE PRECISION Tipe data ini menyimpan nilai double-precision floating poitlr FLOAT Tipe data ini menyimpan nilai d a r ~scmua precrsion dan scale. User dapat member~kan precision dengan format: FLOAT(precision). J ~ k aprecision-nya di antara 1 dan 21, formatnya adalah single-precision jloatrng pornt, tetapi bila berada antara 22 dan 5 3 . formatnya adalah double-precision fluatrng point. Jika precision tidak dibenkan, maka float ybs. akan diasumsikan sebagai double precision. REAL Tipe data uu menyimpan nilai single-precrsrun floanngpolnt.
Perhltungan untuk tipe data DECIMAL. Misalkan nilai A memililu precision p 1 dan scale s 1, sedangkan nilai B p2 d m s2. 1 PertambahanPengurangan: Precision = nilai minimum dari precision maksimum SQLBasc ( 15) max(p 1-s 1,p2-s2) + inax(s 132) -t 1 Scale = nilai maksinium dan s 1 dan s2 2. Pembagian: = precision maks~niumdari SQLBase (15) Precision = maksimunl precision - precision nilai input perta~nn+ scalc nilai Scale pertama - scale nilai input kedua input Contoh: D 1 DECIMAL(10,2), D2 DEClMAL( 10,2). Jika D 1 dibagi oleh D2, maka. precision = 15, scale = 15-10+2-2 = 5 Ada fungsi yang merubah precision maksimum. Misalnya SUM merubah prcclslon maksimum menjadl 15 selungga operasi SUM(D l)/SUM(D2) menghasilkan: precision = 15, scale = 15-15+2-2 = 0 3. Perkalian: Precision = nilai minimum d a r ~precision maksimum SQLBase ( 15) atau jumlah dari precision (p l+p2) Scale = nilai minimum dari precision nlaksimum SQLBase (15) atau jumlah dari scale (s I ts2) Contoh: D 1 DECIMAL( 10,2), D2 DECIMAL( 10,2). J ~ k aD 1 dikali dcngan D2, n ~ a k a . precision = min(15,20) = 15, scale = rnin(15,4) = 4 Terdapat fungsi-fungsi yang mcrubah precision yang maksimum. Misal~iya SUM menggunakan rumus: precision = min( 15, max(p 1-s 1, p2-s2) -t n ~ a x (132) s + 1) scale = max(s 1,s2) Sehingga operasi SUM(D l)*SUM(D2) nicnghasilkan precision = min(l5, max(8,8) + 11iax(2,2)+ 1) = mm(15,ll) = 1 1 scale = max(2,2) = 2
I I
L
SQLBase Training Material Copyr~ghr8 1993, by i'T Versadofa Sysfern,ndo,Jakarfu
Tipe data Date/Time SQLBase menyedlakan tipe data berikut in1 untuk data date dan time 1 . DATETIME (or TIMESTAMP) Tipe data ini digunakan pada kolom yang mengandung data tanggal dan waktu 2. DATE Tipe data ini menyimpan data tanggal. 3. TIME Tipe data ini menyimpan nilai waktu. Konversi Antar Tipe Data SQLBase memunglunkan konversi antar tipe data dlakukan. Konvers~antar tlpe data dapat ditunjukkan sebagai berikut: Source Data Type Target Data Type Comment Character N~laisource data type hams dalam bentuk nurnerik ),an2 Numeric I I 1 valid Numeric Character Tanda petik tunma1 tidak diperlukm Datemime Numeric Datflime Numeric Datflime Character Tanda petik tunggal tidak hperlukan Character Datemime Nilai source data type hams dalam bentuk dateltime yang valid
1
System Keyword Sytem keyword yang terdapat dalam SQLBase adalah: 1. NULL 2. ROWID 3. USER 4. SYSDATETIME 5. SYSDATE 6. SYSTIME 7. SYSTIMEZONE Contoh penggunaan system keyword: SQL> SELECT NAME FROM EMP WHERE DEPTNO IS NULL; SQL> SELECT ROWID FROM EMP WHERE HIREDATE > 0 1-JAN- 1994; SQL> CREATE VIEW MYTABLES AS SELECT * FROM SYSADM.SYSTABLES WHERE CREATOR = USER;
Expressions Expression adalah 1. Sebuah item yang menghasilkan nilai tertentu 2. Kombinasi item-item dan operator yang menghasilkan nilai tertentu Item dapat berupa: 1. Nama kolom 2. Constant 3. Bind varrable 4. Hasil &ri suatufitnction (fungsi) 5. System keyword 6. Expression lainnya Untuk menghubungkan string dengan string digunakan operator 11. Contoh SQL> SELECT 'Mr. ' (1 NAME FROM EMP; Ketentuan yang berlaku dalam menghasilkan arithmetic expression: 1. Expression yang berada dalam tanda kurung didahulukan 2. Operator unary (+ clan -) dikejakan sebelum perkalian (*) dan pembagian(/) 3 . Perkalian dan pembagian dikerjakan sebelum penambahan dan pengurangan 4. Operator dalam level yang sama dikerjakan dari kin ke kanan Contoh expression: AMOUNT * TAX (CHECKS.AMOUNT * 10) - PAST DUE HIREDATE + 90 SAL = MAX(B0NUS) SAL + : I SYSDATETIME + 4
SQLBase
column arithmetic Nested arithmetic dengan nama kolom Arithmetic column dan constant Function dengan column arithmetic Bind variable dengan column arithmetic . Dateltirne system keyword arithmetic
raini in^ Material
Copyrrghr O 199J,by PT. Versadara Sysremrndo. Jakorro
Search Condition Search condrtron (kondisi pencarian) yang din~ulaidengan WHERE menentukar~batasan yzrt'r.,' sesuai dengan kondisi tertentu. WHERE dapat digunakan untuk SELECT, DELETE. UPDATE. Search condtion rnengandung satutleblh predicate yang dihubungkan dengaii operator logical (boolean) seperti OR, AND dan NOT.
Ketentuan yang berlaku untuk kombinasi antara operator log~caldan pred~cnreadaiah. 1 . Expression boolean dalam tanda kurung didahulukan 2. Operator NOT didahulukan daripada AND 3 . Operator AND didahulukan scbclurn OR 4 . Operator yang berada dalam level yang sama dike rjakan dari kiri kc kan~ul Misalkan P dan Q adalah predicare Dua kolom pertarna meriunjukkarl kondisi d a r ~setlap predicate. Jika salah satu item dalam expression dalam senrch condirion null, maka seorch
Contoh: 1 . Perintah ber~kutini menghasilkarl karyawan dalarrl departemen 2500: SQL> SELECT * FROM EMP WHERE DEPTNO = 2500; 2. Perintah berikut ini menghasilkan karyawan dalarn departemen 2500 d m n~ulalbekcrja pada 1 Feb 1994, atau karyawan yang bekerja sebagai programmer: SQL> SELECT * FROM EMP WHERE (DEPTNO=2500 AND HIREDATE='O 1-FEB- 1994') OR JOB='Programrner', 3 . Dua perintah berikut ini akan menghasilkan hasil yang sama: SQL> SELECT * FROM EMP WHERE NOT (JOB='Progranlmer' OK HIREDATE='O 1-FEB-1994'); SQL> SELECT * FROM EMP WHERE JOB!='Prograrnmer' AND HIREDATE!zG01-FEB- 1994';
Predicate
h
it
Pred~cateyang terdapat dalam WHERE atau HAVING menentukan apakah suatu search condtion true, false atau unknown darl menghasilkan sebaris/sejumlah baris dari table Predrcnte rnenggunakan operator, expre~srondan constant untuk n~enentukank o n d ~ s~~a r i g diinginkan
L
SQLUase Training Material Copyrrghr (9 1995, by PT. Versadofa Sysren~rndo.Jokarru
Relational Predicate Ada dua macarn relational predicate yaitu: 1 . Comparison relational predicate 2. Q u a n h j e d relational predicate Comparison relational predicate membandingkan suatu nilai dengan nilai lain berdasarkan operator standar relational. Bentuk dasar dari comparison p r d c a t e ialah dua expression yang dihubungkan dengan relational operator, seperti: A>B COLl != COL2 Contoh dari comparison relational predicate: SQL> SELECT * FROM EMP WHER EMP='50642'; SQL> SELECT * FROM EMP WHERE HIREDATE <= ' 1-JAN-1994'; Quantified relational predicate membandingkan nilai expression pertanla dengan sekumpulan nilai yang dihasilkan dari perintah subselect. Perintah SELECT yang digunakan dalam predicate disebut sebagai subselect atau subquery. Subselecf~adalahper~ntallSELECT yang terdapat dalam WHERE &lam sebuah perintah SQL. Kita tidak dapat menggunakan ORDER BY dan LONG VARCHAR &lam subselect. Contoh quantrfied relarional pred~cnte: 1. SALARY tidak sama dengan ran-ram gaji. SQL> SELECT * FROM EMPSAL WHERE SALARY != (SELECT AVG(SALARY) FROM EMPSAL); SQL> SELECT * FROM EMPSAL WHERE SALARY <> (SELECT AVG(SALARY) FROM EMPSAL); 2. SALARY lebh besar dari rata-rata gaji: SQL> SELECT * FROM EMPSAL WHERE SALARY > (SELECT AVG(SALARY) FROM EMPSAL); 3. SALARY lebih kecil dari rata-rata gaji: SQL> SELECT * FROM EMPSAL WHERE SALARY < (SELECT AVG(SALARY) FROM EMPSAL); 4. SALARY lebih besar dan atau sama dengan gaji lainnya: SQL> SELECT * FROM EMPSAL WHERE SALARY >= (SELECT SALARY FROM EMPSAL);
BETWEEN predicate Predicate ini membandingkan suatu nilai dengan nilai-nilai dalarn suatu batasan tertcntu Contoh: SQL> SELECT * FROM EMPSAL WHERE SALARY BETWEEN 2000 AND 6000,
NULL predicate, Predicate ini menghasilkan data yang memiliki nilai null. Contoh: SQL> SELECT * FROM EMP WHERE DEPTNO 1s NULL;
i
EXISTS predicate Predicate mi digunakan untuk mengetahul apakah bans-bans tertentut terdapat dalan suatu table. Contoh:
SQLBase
rain in^ Material
Copyr~ghfO 199.5 by PT Versadoro Sysre~nrndo.Jokarru
SQL> SELECT FROM EMF WHERE EXISTS (SELECT * FROM EMPSAL WHERE SALARY=: 1)
LIKE predicate Predicate ini mencari string yang memenuhi pattern tertentu dan hanya bisa digunakan dengan tipe data CHAR atau VARCHAR. Pattern-matching character yang dapat dimanfaatkan adalah: Underscore ( ) I memenuhi semua single character Percentage (%) I memenuhi zero atau lebih character Backslash 0) merupakan escape character bagi &,- dan dirinya sendiri.
I I
Contoh: 1. Perintah berikut menghasilkan data yang narnanya merniliki string 'son' SQL> SELECT T* FROM EMP WHERE NAME LIKE '%on%', 2. Perintah berikut menghasilkan data yang dua character pertama dari job dimulai dengan'M' : SQL> SELECT * FROM EMP WHERE JOB LIKE 'M-'; 3. Perintah berikut menghasilkan data yang nilai kolom JOB adalah 'A24%' SQL> SELECT * FROM EMP WHERE JOB LIKE 'A24\%'; 4. Perintah berikut menghasilkan data yang nilai kolom JOB dimulai dengan 'A24%'. SQL> SELECT * FROM EMP WHERE JOB LIKE 'A24\%%';
IN predicate Predicate ini membandlngkan suatu nilai dengan sekumpulan nilai. Kunlpulan nilai tcrscbut bisa terdapat dalam perintah SQL atau hasil dari subselect. Contoh: SQL> SELECT * FROM EMP WHERE DEPTNO IN (2500,2600,2700); SQL> SELECT FROM EMP WHERE EMPNO NOT IN (SELECT EMPNO FROM EMPSAL WHERE SALARY < 40000); SQL> SELECT * FROM EMP WHERE @LEFTO\IAME, 1) IN ('J','M','D1); SQL> SELECT * FROM EMP WHERE NAME NOT IN (: 1,:2,'Jones') \
Johnson, Smith /
Function Function (fungsi) dalam SQLBase dapat digolongkan dalam beberapa katogori: 1. Aggregate finction Menghasilkan satu nilai tertentu dari sekumpulan nilai. Contoh: AVG, COUNT, MAX, @MEDIAN, MIN, SUM, @SDV. 2. String finction Menghasilkan ~nfonnasitentang tipe data character. Contoh: @CHAR, @CODE, @DECODE, @EXACT, @FIND, @LEFT, @LENGTH. @LOWER, @MID, @NULLVALUE, @PROPER, @REPEAT, @REPLACE, @RIGHT, @SCAN, @STRING, @SUBSTRING, @TRIM, @UPPER, @VALUE
SQLBrrsc Training Material Copynghr O 1995, by P7'.Versada~aSysrerrrrndo. Jokorru
1
3 . Date/time&nction Menghasilkan lnforrnasi tentang tipe data dateltime. Contoh: @DATE, @DATETOCHAR, @DATEVALUE, @DAY, @HOUR, (@MICROSECOND, @MINUTE, @MONTH, @MONTHBEG, @NOW, @QUARTER, @QUARTERBEG. @SECOND, @TIME, @TIMEVALUE, @WEEKBEG, @WEEKDAY, @YEAR. @YEARBEG, @YEARNO. 4 . Math finction Mengambil nilai numeric tertentu scbagai argumen dan menghasilkan has11 nurnerlc. Contoh: @ABS, @ACOS, @ASIN, @ATAN, @ATAN2, @COS, @EXP, IgFACTORIAL, @INT, @LN, @LOG, @MOD, @PI, @ROUND, @SIN, @SQRT, @TAN. 5 . Finance j filnction Argumen dapat dalam bentuk tipe data character jika nilai tersebut dalarn bentuk nila~ numeric yang valid (hanya digit dan character standar numeric). Contoh: @CTERM, @FV, @PMT, @PV, @RATE, @SLN, @SYD, @TERM. 6 . Logical finction Menghasilkan nilai sesuai dengan kondisi. Hasilnya selalu 1 (TRUE) atau 0 (FALSE). Contoh: @IF, @ISNA. 7 . Specialjfilncrion Menghasilkan fungsi-fungsl ymg khusus Contoh. @CHOOSE, @DECIMAL, @DECRYPI', @DECODE, @HEX, (9LICS
SUM
(
@ABS BACOS @?SIN OATAN OATAN2 @,CHAR @CHOOSE
@,CODE -@COS RCTERM B,DATE @DATETOCHAR @DATEVALUE @.DAY @,DECIMAL
I
Jurnlah keseluruhan item Nilai absolut Arcasinus I Arc-sinus Arc-tangen dua kuadrant Arc-tangen empat kuadrant Karakter ASCII untuk desimal Menampilkan satu nilai dari daftar berdasarkan correlation Kode desimal ASCII karakter pertama dari string Cosinus Menggabun~kanpericde untuk menghasilkan nilai yad. Konversi ke date Mengedit nilai date E d t nilai date Hari ke b e r a ~ adari bulan tertentu I NiIai desimal dari string hexadecimal
SQLBase Training Material Copyrighf (3 1995, by PT. VersadofoSysfernindo. Jokorfa
Math Math [ Math Math Math String Special
I
String Math Finance Dateltime Dateltime Dateltime Dateltinle Special
@DECODE @DECRYPT @EXACT @EXP @,FACTORIAL @FIND @FV @HEX @HOUR @IF
@MICROSECOND -- -
1
I
1
String, Specinl Menghas~lkanstnIig darl espresslon yang d ~ b e n k m Melakukan decn pt terhadap pass~vord ( specla1 Membandlngkan dua stnng String Exponcnsial Factorial Math Posisi dalam strlng 1 yang terdapat dalam strlng7 -String F~nxnce Nilai yad. dari sekumpulan pembavaran yang sama String hexadecimal d n r ~nllal dcs~mal Specla1 Jam &lam h a r ~ Datcltln~c Mengecek nilai, nienghasi1ka.n 1 (TRCE) atau 0 Logical (FALSE) Nlla~integer Marh
1
Panjang darl string Str~ng Sort mengunahan hun~pulankarakter ~ntemas~onal Specla1 Algontma natural Math Logarma Math Merubah huluf bcs'ir menjadl huruf kec~l String Nlla~tengall darl sehu~npular~ ~tcm ®arc N~lalmlcro dctlh -Datclt~rnc Menghasllhan strlllg, nlula~ d,~rl p o s ~ skarakter ~ pnda Stnng Menit &lam jam Bulan dalanl tihull tcrtcntu Han pertarna d a r ~bulan tcncntu Tanggal dan ~3.111sch'lrang (current date and tlmc) Menghasilkan str~ng/nurnbcr lang dltunjukkm olch 1 j ~ k ax NULL Nllal PI (3 14 159265) Pembayaran pcr~odlh)ang dlbutuhkan untuk mcmba>ar
@QUARTERBEG
Konvers~karahtcr pcrtdna d a r ~sctlap kata dalan strlllg ke huruf bcsar d,ui rricnjdirnln sctlap karakter la111dlll,lm huruf kccil Nllal sekarang (present value) darl sekurnpulan pembayarm yang sama Nilal yang n ~ c ~ \ a l \cjuartzr ~lr (per~odc3 bulannn) Han pertruncl d a r ~quarter
Datcltlnlu
Datclt~rnc Strung blath Flnnncc Str~ng
Finance Datclt~rnc Date/t~mc
M e n g g a b u ~ ~ g k ~ n s str~ng u ~ t u dalarn dlr111qa s c ~ ~ dil~r ~~ ~ t i lSht r ~ ~ l g beberapa kall M e n ~ a n t ~ k akarakter n dalrurl strmg Urutan stnng d a r ~scbcldl kanan Membulatkan nllai Math Mencar~strlng berdasarkan pattern
SQLBase Trmining Mufericrl Copyr~ghtO 1995 b) PT I'er~odotoSj:)srutnrnJoJohurr.~
--
I
I @SECOND ,@SIN @SLN OSQRT
1 I @STRING I OSUBSTRlNG
(qSYD @TAN Iri2TERM @TIME t4TIMEVALUE mTRIM , @UPPER @VALUE '3WEEKBEG @WEEKDAY @YEAR @YEARBEG
I @YEARNO
I Detik dalam menit
I D;lte/t~nic
Sinus Straight-l~nedepreciation Akar
I I Konversi nunibcr kc string I Menghasilkan scbngl;ul string
I
1 Strin~ 1
String
1 F~nancc
Sum-of-the-Year's Dlglt Depreclat~on Tangen Jumlah ~ e r l o d cvc~nbavaranuntuk suatu ~ n v c s t a s ~ Menghasilkan n ~ l adateltlnie ~ darl jam, menlt, d e t ~ k Menghasllkan n ~ l adatelt~nie ~ dengan HH MM SS [AM atau PM] Menghilangkan blanklspace yang terdapat dalam stnng Meru bah huruf kec~lrncrljadl huruf besar Konversl stnng karnkter dengan dlglt ke number Harl Sen~nd a r ~satu m111n~u Hari tertentu dari satu rninggu Tahun ke berapa, dihitung dan 1900 H a n ~ertarnadan satu tahun
--I
hlath f-~nancc Date/trmz Datc/tlr~ic Strlrlg Strlllg St1 lrlg Dateltlrnc Date/t~~nc Datelt~nie Dntcltimc
( Datelt~riic
( Tahun kalender
Dateltime memiliki system keyword scncilr~.Hzrikut
SYSDATETIME CURRENT TIMESTAMP CURRENT DATETIME SYSDATE CURRENT DATE
Math F~nancc Math
1111
nierupakan tablc s!,stcni kcy\s~ordd a r ~
Keteransan Current date and tirnc
Current date Current time
CURRENT TIME SYSTIMEZONE CURRENT TIMEZONE MICROSECOND[S]
Interval tunezone dalam hari Contoh: SYSTIMEZONE= 025 bcrarti 6 j~un Wakhr dalarn microdetik Waktu dalam detik Waktu dalam menit Waktu dalarn jam Waktu dalam hari Waktu dalarn bulan Waktu dalam tahun
Operasi penanbahan maupun pengurcuigm dapat dilakuknn kepada Jore Hnsilnyn I . Date t Jun~lah(hari) adalah DA1'E'I.l hl IS 2. Date - Jumlah (hari) adalah DATE'l'lhlE 3. Datc - Date adalah jumlah (hari)
SQLBuse Trc~ininghJc~terirrl Cop)~rrghrO 1995, byP7' I.'ersudura Syrrr~trindo,Jukurru
I
Perhat~kanlahbahwa jika k ~ t amcnruiit);lll,r~lcr~guramgkan nllal yang non-ck,lc' 1 1 1 1 1 ~J\cpad:~/C1:11 ~ I DATE, hasilnya ialah DATETIME. U n r u k mcngliasilk,m DATE, g u n k a n esprt).csrc1t7sbb DATE + Junllah DAYS Sebagal contoh, expressron berikut 1111 r~iulgliasilkiu~ nila~DATE'I'IME !.:ulg baru SYSDATETIME + 3 MINUTES nlnka ll~lnl>;ulg diber~kandiasunlslkari scbagai 0:I)'S ( ' O I I I L ~ I I Jika interval tidak discbutk:~~, bcnkut In] mcncunbah 1 hari darl i.zirrc>rrrt i ~ ~ ( c ' SYSDATE + 1 SYSDATE + 1 DAY Ketentuan untuk inten'al MON.1-H dan L'EAR. 1 lnterval MONTH dan YEAR tinn!.a dapat d l t a ~ ~ i b a h k aatau i dikurnrlgknrl dun luml;~li DATE atau DATETTIME Benar. (SYSDATE -t 3 I I A J . 5 ) 1 \'EAR Salah: SYSDATE t. (3 DAYS . I YEAR) 2 . Pada saat MONTH ditambah. niln~bulnrl ( d i n j ~ k arnungkln nllal tahun) drtullbaliknrl JI ha h a r ~yang dihasilkan melmpaul h a r ~rcrakhlr dan bulan dan tahun tcrtcnlu, SQLUasc ah;ill menyesuaikannya secara otornatls 3. SQLBase akan mengabaiknn nllai tics~~~lrll dan MONTH d m YEAR Sebngal contoh, SQLBnsc akrm rn~hng)aba~ka~l nllar . 5 ciari MONTI I i r i l t i ~ l \pcr~ntahbcr i h c ~ r In]. SQLB SELECT DISTINC'I' SYSI).-\'l'LTTIME. SYSDATETIME I 5 "L1ON.I'HS FRO.Z1 SYSTABLES 7 -
+
DateRirne Express~on 3 1-Jan-1993 + 1 MONTH 20-Jan- 1993 + 1 MONTH 3 1-Jan- 1993 + 1 MONTH - I MONTH
Rcsult
..
--
28-Feb-1993 20-Fcb- 1993
-.-- -- ---
-
Opcras~join rncngambil data d m tablc !;111g bcrbcdri-bcda dim dcngan bar is !.an3 ri.~il;ll,;ir dalam semua table. Contoh berikut lnr illc~ldi.rnons~rasikan operas1 joirl Table CUSTOMER CUSTNO 1 2 3
-
Y .AhlE
ADDRESS
.- .. . . .- .
13 A St 1 B St 12 C St
ABC INC S Y % [NC' A l IhC'
Tablc ORDERS ORDERYO 300 1 3002
ORDERDATF. 0 I -J U1,-94 03-JUL-94
SL)I,JJII.\L~ lrriining Iklrirericil Copyr~ghl@ 19%>,h\, I > / I ' t , r s [ ~ [ l.<\ y~~/ /(e~~ r ~ ~ n. I d( JuK,u ~ ~ ( I
i
S.AIJl:S 12tt'
Jill Jill
. -- - -
--
Untuk menemukan narna dan nomor ordcr >.angdibuat oleh Tom, gun&an penntah sbb SQL> SELECT NAME, ORDERNO FROM CUSTOMER, ORDERS WHERE CUSTOMER.CUSTNO=ORDERS.(~USTNO AND SALES-REP = 'Tonl'. Hasilnya adalah: NAME ORDERNO ABC INC. 3003 X Y Z INC 3004 SQLBase mcndukung tipe-t1pc,/ort7scbagai bcrikut: 1 Equ~join 2 Outer j o ~ n 3 Selfjo~n 4 Non-cqu!join
Equijoin Query berikut ini menycsuaikar~nallla customer dcngul nonior ordcr SQL> SELECT NAME, ORDERNO FROM CUSTOMER, ORDERS WHERE CUSTOMER.CUSTNO=OKDEKS CUSTNO; Baris-baris dari table OKDERS d~hubungkandcngan CUSTOMER rnclalu~kolorn C:US'I.?-0 yang terdapat di kedua table T ~ p c.stli7rch condrnon yang rnenghu bunglian du;i tab lc berdasarkan persarnaan (equnlrr~t)seperti in1 discbut eyirrjoin.
Curresiun Product Corre.rlcrn product adalah kumpulan dari scniua b a r ~ spang mungk~ndihas~lkand a r ~, ; O I ~ I atas lebih dari satu table. Lihat colltoh bcrlkut 1111 SQL> SELECT NAME, ORDERNO i:KOh'l CUSTOMER, ORDEKS. J ~ k aCUSTOMER memiliki 100 baris dan ORDERS 500 bans, n ~ a k aL C I I . [ C ~ . S I I I ~~I ! ~ . o L ~ ;II ~ L . I - I adalah 50.000 baris. Tcntu s q n buhnn ti;1s11 scpcrti In1 yang di~nginkan Untuk mcnghindari cnrresrnn proilrlcl. maha rc/u~!oin diperlukai, schlriggn pcr~nt;ill dl :Ira> perlu dirubah menjadi: SQL> SELECT NAME, OKDEKNO f:KOM CUSI'OMER, ORDEKS \+'14F2R11
CUSTOMER.CUSTNO=OKDEIiS.C~USTNO; Outer Join Bila customer belum membuat ordcr, r n ; h customer baru tersebut ti&& ALIId ~ t ; l ~ i l l ) ~ l h ; ~ l ~ dengan perintah equijorn di atas. Otrter jorn akan menarnpilkan setiap bans d a r ~satu tnt~lc dengan table lain yang kondisinya sesual atau nila~nvarlull. Hasilnya adalah scmun barls dal;lm satu table tanpa memperdulikan s p ~ k a hbaris tersebut sesuai dengan bnris lam d a r ~table In111 yang dihubungkarl. Perintah berikut menarnpilhan nmla custi~rncr.d m nomor ordcr, tcrmasuk customer !ang t~il;lh n~enlilik~ order. S Q t 3 SELECT CUSTOMER.CUSTrU'0, NAME FROM CUS'TOMER. ORDERS U ' t 1f:lUI CUSTOMER.CUSTNO=ORDEKS . CLISTNO(+); Selfjoin
S t y jorn mcnghubungkan su;ltil tablc dc~iga~l diri11>as e n d ~ r ~scpert~ , dua table > , ~ n ghCibii1.1 Untuk melakukan ha1 mi, berlhut nanla ~orrc~iirrrotl untuk table y a i g akan d~-\clf-!c~l~i I
;
1
Contoh bcr~kut1n1menanlpllkan scmu,i tu1ggal yang r i ~ e n l ~ lcb~h l ~ k ~d n r ~sntu order SQL> SELECT A ORDERNO, A ORDERDA'I'E FROM ORDERS A, ORDEfiS D U't i l 121
A.ORDERDATE=B.ORDERDAACE AND A.ORDERDA'TE<>B.ORDERNO; Table ORDERS dianggap sebagai dun tablc dcrigan menggunakan n m a corrclotror7 A d m B Tanggal order diarnbil dari tablc correlcrrlot~ A, kemudian order date ini digunakan sebaga~ search condition untuk table B . Infonnas~ynng sama juga dapat dihasilkan dengan . s ~ i h y ~ r e r ~ ~ Non-equijoin
Opcras~In1 n~cnghubungkansuatu tablc dcngln table laiiu~yaberdasarkan pcrb~1~I11lgu1, buha11 persanlaan Seniua operator relat~onaldapat digunakan seperti <, >, I=, BETWEEN, or LIKE) Contoh
SQL> SELECT NAME, ORDERNO, ORDERDATE FROM CUSTOMER, ORDERS WHERE CUSTOMER CUSTNO=ORI)EKS CUSTNO AND ORDERDATE BETWEEN 0 1-JUL-04 AND 30-SEP-94. SQLBase memperbolehkan operas1 joln untuk 10 table dalam suatu perintah SELECT Nxnun ha1 ini akan mempengaruhi lunerja. Bila database dirancang dengan baik, kemungkinan sepcrt~ ini harnpir tidak pernah te rjadi.
SQL mcmungk~nkanfas~litasrectirslve Illput d a r ~satu query dapat m c q a d ~output bag1 qucrL la~nnya Suatu query dapat d~gabungkarldcli~nnqucn lalnnya Nestcd quer: scpert~1111 dlsebut sebaga~sltbq~tery/sztbselecr Suhy~rcrymcrupnhan per~ntahSELECT ) nng t e r d ~ rdari ~ bcbcrnpn level ORDER BY dan LONG VARCHAK t~d'lkdrlpat d~gunakandala1-n kasus 1111 Untuk ~txnarnpilkansernua order yang lerjadl dalam tanggal ilomor customer 2:
sarnn dcngnn ordcr d a r ~
SQL> SELECT ORDERNO, ORDERDATE FROM ORDERS WHERE ORDERDATE=(SELECT ORDERDATE FROM ORDERS WHERE CUSTNO=2). Pertanla sekal~,tanggal order ddri noirlor custonler 2 dlanlb~ldan nllal 1111 dlgurlakatl untuh menyelesa~kansearch condrrron darl morrLozirer query Dalam contoh dl atas, s u b q u e r ~ 1111 d~eksekus~ sekal~untuk mendapatkan suatu nllal yang d~gunakanoleh main quety Dalam perintah SELECT berikutnya, >,ru~g d~scbutsebagal correlated slibqziery, szihqlter!. 1111 dieksekusi sekali untuk setiap barls kandidat dalam nrarn query. Misalkan kita ingin menanrpilkan kanaivan yang gajinya lebih bcsar dnri ratii-ruta gall karyawan lam: SQL> SELECT * FROM EMPSAL WI-lEKE SALARY >
(SELECT AVG(SALARY) FROM EMPSAL):
Bind Variable
Bind variable menunjuk ke suatu nila~data prig bcrhubungan dcngan perlntah SQL NrtrJ varrable dapat digunakan di: WHERE clause VALUES clause dalani suatu penntah 1NSEK'I' SET clause dalarn perintah UPDATE
SQLU(ise Training Material Copyright Q 199s. by PT I'ersadalo Syslettrrndo. Jukurru
Nama bind variable dmulai dengan tanda tit& dua (:) dan dapat berupa: 1. Narna variable 2. Nomor yang mengacu ke posisi data item yang berhubungan dengan perintah SQL (seperti : 1,121
111. Konsep Dasar IRDBM
Database Adnunstrator (DBA) Dept. MIS (Management Informat~onSystem) terdir~dari fungsi-fungs~b e r l a ~ n m> , x ~ saling g melengkapi. Salah satu fungsi yang cukup penting ialah DBA (Database Administrator). DBA bertanggung-jawab untuk merancang, mcrcncanakan, mengadakan rnstalas~, konfiguras~. menjamin keamanan (security), dan mengopcrasikan DBMS (Database Managcrncnt System ) . Untuk melakukan semua ha1 tersebut dengan baik, DBA hams dapat memahani aplikasi apa yang terdapat dalam sebuah organisasr, slapa saja yang menjadi pengguna (user) aplikas~,data apa yang disimpan.dan diakses, dan jen~stransaksi yang terjadi sehari-hari. Secara garis besar, tanggung-jawab seorang DBA meliputi: 1 . Memastlkan database server terus tip sepanjang jam kerja 2. Melakukan instalasi database server d m clicnt softwarc 3. Create database 4. Melakukan backup (secara berkala) dan recovery (bila terjadi kerusakan terhadap data) 5. Mengontrol security clan akses ke databasc dan objek-objek yaig tcrdapat dalam database 6. Memonitor dan melakukan yerjurmnnce cirnlng terhadap database & aplikasi y m g mengakses database tersebut 7. Mengatur komunikasi antara database scrwr d m client workstation 8 Melakukan estimasi atas besarnya mcdi:~pcnympanan yang dibutuhkan 9. Membantu developer dalarn melakukan akses dan nianipulasi atas objek-objek >.ang terdapat dalam sebuah database m~salnya:table. lndex, v ~ e w ,looding, convcrrlng, dnn unloading data 10. Menjamin data a~allablllty, acclrrrrcy, cor/ipletenc.ss,lntegrlry, dan cons/stenq 1 I . Mernantau system catalog 12. Membantu user yang mengakscs databasc
Relational Database Istilah relation pertama sekali digunakan oleh E.F. Codd dari IBM setelah mempelajal-I struktur data dengan rnenggunakan sct thcory. Set adalah kumpulan objck yang mcmilikl rule tertentu yang memungkinkan kita untuk mcngctahui apakah objek yang kita bcrikan tcnnasuk dalam kumpulan objek tersebut atau t ~ d a kSebagai contoh, kumpulan kelinci p u t ~ hadal,ah bagian dari sernua kelinci yang rnernilik~bulu bcrwarna putih. Terminologi yang digunakan untuk n~en~.ebutkan objek-objek database bcrbcda Bcrrkut In1 merupakan tabel terminologi yang biasa digunakan: Relational Database Set Theory Conventional Data Process~ng Table Relat~on File / Data Set Row Tuple Record Column [ Attr~butc/ Domarn Field
1
I
1
I
Relational database nlengurangl rcdundansr data Hal rnl &an mcmbcr~kant ~ g akeuntungan
SQLUase Training Muterial Copyrlghr 8 199>, by 1'7' Versadocl(i~ S y s ~ e ~ t ~ r nJokorru do.
1
1. Data dapat disusun dan dikombinasikan rnenjadi relasi baru karena data tldah harus sclalu disimpan dalarn suatu cara tertentu ?,ang kaku 2. Data dapat dirubah (update) dengal mudah 3. Mengurangi besarnya tempat penyinipanan yang dibutuhkan
Codd mendefmslkan tiga operas1 pengaksesan data dalam relarronal algebrrr 1 Selection Selechon a&lah proses untuk mendapatkan semua bans data dari table yang mcmenuhi kriteria tertentu 2 Projection Projectron adalah proses untuk mendapatkan satu atau l e b h kolom dari scbu,dl table dalam urutan tertentu dan mengabalkan duplikasi bans 3 Joln Joln adalah proses untuk mendapatkan holorn-koloni dan table-table yang berbeda d~nia~l,i nilai d a r ~satu / leb~hkolom dl dalan~setlap table adalah sarna Operas1 jorn inilnli kang membedakan slstem relarional dengal alstcm yang non-reln~lonnl
Distributed Processing Istilah disrribured processrng n~embenkangambaran bahwa komputer dapat ditcnipatkaii dl mana saja (di laboratorium, di Dept. Akuntlng, sampal di ruang kerja para nlanajcr). Nctworh akan menghubungkan seniua kolnputcr tcrsebut setiingga pertukaran ~nforrnasi,pcrilnkalnn sumber daya secara bersama-sama aka11 tcwujud. Sistern ini tidak memperniasalahkan dl mana inforrnasi disirnpan karena infonnasl dapat diakses dari semua bagian dalnrn orga~llsasi Perkernbangan PC (Personal Computer) dan LAN (Local Area Network) di tahun 108U-an membenkan pengertian yang baru atas dlsrrrbzrreu' prucrssing. Dengai PC, user rt1rcrfi7c.t~ menjadi rnenank dengan penampilan windows dan graflk yang membuat user dapnt rnenggunakan aplikasi dengan lebh ccpnt dari mudah. Dengar1 LAN, PC dapat sal~ng berkomunikasi dan menggunakan sumbcr daya secara bersama-sama.
Model Client-server Teknologi client-server adalah suatu bcntuk darl d/strrbured processrng dimana aplikasl secnra hngsi dibagi rnenjadi dua 1 lebih prograni yang dijalankan oleh komputer berbeda yang saling berkomunikasi dalam network. Program clrcnr dijalankan di PC, sedangkan program .server dilakukan oleh kornputer yang lebih cang,gii. C'lienr lnenginmkan pcrmintaan kepada servcr. server memproses dan mengirirnkai t~asilpcmrosesan kernbali kepada clienr
SQLUase Training Moterial Copyrrghr 0 1993. by P7' i/~.rsudu~u Syslrrrrrndo. Jukurru
Client C l ~ e n t~ F J j -(
;;;////---4 /
Server
/
LAN
Dl
7
4&2i
Y~
client
Data base Server Clien
-
Clien "-
.-
Garnbar 3 1 Model Client-server
File Server vs Database Sewer Database server berbeda dengan jife server tiilt. server berfungsi untuk rnenyimpan, bukan untuk memproses file-file yang berisi data. Pernrosesan aplikasi adalah tanggung-jawab client Bila client meminta data, maka Jlt. server akan m e n g i d a n seluruh database melalui network ke client. Jlka client yang nieniinta data banyak, maka perjurmance network rnenjadi turun. Relational database system memiliki flcksibilitas yang lebih baik. Aplikasi client menggunakan perintah SQL untuk berkornunikasi dengan database server. Cljent bertanggung-jawab untuk menjalankan aplikasi dan mengirimkan perintah SQL ke databnsr server. Database server bertanggung-jawab untuk mengecek dan menjalankan perintah SQL yang dilurirnkan client dan menginmkarl hasilnya ke kembali ke client. Jadi, database server hanya mengirimkan sebagian dari database kembali ke client. Hal inilah yang lazim disebut sebagai cooperative processing. Client Application Network
+
Database Serve (Multi-user)
Communication Library
Front-en Back-en
Ganlbar 3.2 Cooperative Processing
SQLUase Truining Material Copyrrghr @ 1995, by PT ~ e r s a d o t oSyrte,~lndo.Jokarru
D~stributedDatabase S o f i a r e darabase term mengalam1 evolus~.Pertarna sekali, sofiwnre dambnsr bersrfat monolithic dan terdapat d~ komputer besar yang d~hubungkanke dumb termlnnl. Kemudian, sofrware database menjadi lebih c a n a h dan membagi fungsi yang dkerjakan oleh database menjadi dua komponen: client (,/kont-end) dan server (back-end). Pada masa ini, bark komponen client maupun server masih tetap terdapat di mainframe atau mini computer yang sama.
Mainframe] Minicomputer
Mainframe] Minicomputer 7 I
(Client) I
1 Dumb Termina L --
I
--
1
Dumb Termina
I
1
..p--.--pp~~d.
Gambar 3 . 3 P e m ~ u l ~ aEvolusi n Software Databasc Evolusi ini terus berlangsung dm pada saat mi, komponen clienr dari software dclrahclsr dipindahkan ke PC clan LAN, sedangkan lio~llponcnserver tetap berada di dotobase sewer. --
Clientj
!
Gambar 3.4 Databasc scrver dm client dalam LAN
i
Keuntungan dari distributed databasc adalnh 1. Location Independence Lokasi dari aplikasi client tidak tergmtung kepada lokasi dan data 2. Locaf~ on Transparency
SQLBase Training Material Copyrrghr cD 1995, by PT Versadara Sysrernrndo. Jakarta
3.
4. 5.
6.
7. 8.
User dapat mengakses database tanpa pcrlu mengetahui lokasl data, selaln ~ t u ,database dapat dipindahkan tanpa mernpengaruh~user 1 aplikasl Incremental application growth Aplikasi dapat di-upgrade dengan mel~arnballkanclient-client lain atau dcngnn rncmbcl~ database server baru yang leb~hcarlggih Sire autonomy Setiap database dapat di-maintain secara terpisah satu dengan lainnya Data dapat didistribusikan d m diproteksi urltuk mernenuhl kebutuhru~ tiap dcpartcrncr~ secara spesifik Hardware a n d Sofrware Independen~.r Perangkat keras dan perangkat lunak yculg dipaka1 dapat berasal darl vendor yang berbeda dan dapat dsesuaikan sesual dengan kcbutuhan aplikasi. Distributed processing Proses dan media penyimpanan data dapat didistribusikan ke beberapa komputcr Meningkatkan a v a i l a b i l ~ ~dan y rel~abrlr(~r Bila database server-nya down, hmya client prig connect ke database tersebut terpengaruh, namun PC lainnya tetap a m m
SQLHnse Training Mnteriul Copyrtghf 0 1995, by PT Yersudutu Sy~fonrtndo.J u k ~ ~ r r u
IV. Referential Integrity
Referentrnl Integrlly (RI)menjarmn bahiva scmua referensi dan satu table ke table lain valid. artinya semua perubahan yang tejadi di satu table akan berakibat kepada table lain yang saling berhubungan.
Misalkan kita memiliki table ENGINEERS tcmpat hta menyimpa~ inforrnasi karyanan Kemudian kita ingin memindahkan karyawan kc kantor baru. SQL> INSERT INTO ENGINEEKS (EMPL-NUM,NAME,REP-OFFlCE,TlTLE, HIRE-DATE) lO/l/93,NULL); VALUES (400,'LIENA',50,'Engl~ieer', Perintah di atas dapat dilaksanakan dengan balk, tetapi dapat juga gagal. Setlap data kantor
yang terdapat di table ENGINEERS harus merupakan data yang sudah terdapat dalam tablt: OFFICE sebelumnya. Peraturan ~11lld-1yang dinamakan sebagai RI costrainr. Rl tidak rnengoreksi data yang salah ditempatkari Misalkan, seharusnya karyawan LlENA rnasuk kc office 50, ternyata operator salah key-in sehinga masuk ke office 40. RI &an menerima transaksi insert tersebut bila office 40 merupakan data yang valid. R 1 hmya rncnjarnln bah\z,a data ofice 40 terdapat di table OFFICE siija. Untuk mempermudah pembahasan topik struktur dan data sbb: Table OFFICES OFFICE 20 40 10 30
CITY San Frans~sco New York Los Angelcs Chicago
Table ENGINEERS
I EMP-NUM I
1
100 104 107 102 101 106 105 103 108 109 110 11 1
1
Paul Atkins Rob Smith Murray Rochester Lam, Sanchez Sheila Brown Sam Valdez Rob Jones Anna Rice Mary Adams Nancy Bonet k c h a r d Park Dan Chester
kita rnemerlukan suatu databasc yang mern11ik.l
REGION il.'cstern Eastern Western M~dwest
1
NAME
1111,
I
REPOFFICE 10 20 30 10 10 30 20 20 40 40 40 40
I
1
MGR 103 108 100 106
TITLE Manager Sen Enginexr Sen. Engineer Sen. Eneineer En~inccr Manager Engineer Manager Manager Sen. Engineer Engineer Eneineer
SQLBcrse Trcrining Mnterinl Copyrrghr O 1995 b ) 1'7
I krhodaicr Sysrert~rndo,Jokurro
MAJ ACCOUNT 1050 2500 -3000 100 1
1
HIRE I MANAGER DATE 1988-02-12 1992-09-05 103 1991-01-25 106 1 1989-06-1 2 1 100 I 1990- 10- 10 100 1990-04-20 199 1-09-08 103 1985-07-10 1 1988-08- 10 1989-1 1-12 108 1990-1 1-14 108 1987-03-22 11 1
Table CUSTOMERS CUST NUM 1000 2500 1001 1050 2000 3000
1
COh1PANY Acme Camera Photo- l Shop Best Photography Johnson's Canicra Company Sue's Familt Photo 1-Hour Ouick Photo
Table SERV CALLS CALL-NUM CALL-DAT E 2133 1994-05- 10 6253 1 1994-05-02 1994-05-09 7111 4250 1994-05-14
Table PRODUCTS MFR ID ACR ACR MRP 1 LMA LMA LMA MRP MRP
CUST 1000
1 3000
100 1 1050
REP
1
10 1 102 106 105
1
ACK LMA MRP M RP
10 1 102 101 4211 43 10 45 16
600 60 1 24c
WRS
CREDlT L1\1IT 5000 3000 1000 8050 5000
I
3OOO
MFR
PRODUCI' ID
1
I
SERV REP 101 110 106 105 103 102
PRODUC71'
102 14516 600 600
1
DESCKIP'~I0N
I
Trrpod Tr1pod2 Long Pu~.glr: Lens Autornat~cCamera Regular Focus 1 Regular Focus 2 Lcns Shutter Widget 1
RI merupakan salah satu fasilitas SQLUasc >.u~ngsangat penting. K1 nleIijuiiu1 integrltas dar) validasi data pada level database. M~salkunjiks k i n telah member~kanketentuan sbb: 0 Hanya boleh terdapat satu manajcr untuk tiap kantor Sernua karyawan hams dihubungkan dcngrul satu kantor d m nllulajer Setiap produk harus rnemiliki kodc manufaktur clan kode produk Semua pelanggan diberikan nmla scorang kar-awan sebagai contact-person SQLBase dapat mengatur semua RI construnt scpcrt~dl atas. K ~ t at~dakpcrlu mcnul~sapl~h;lh~
lagi. SQLBase, bukan user, yang akan mciljamin RI yang berlaku. Untuk mernpelnjar~ bagaimana RI beke rja, kita harus mengctaliui komponen-komponen RI sepcrti: 1 . Primary key 2. Foreign key 3. Parenttchild table 4. Parentlchild row 5. Self-referencing tablelrow
SQLSase lrciining Mnterial Copyr~ghl0 1995. by P7'. Versodoto Systemtndo, Jokarta
I
Primary Key Primary key (PK) adalah kolom atau kumpulan kolom yang secara unik tiietigidetit~fikasikai setiap baris. Dalam table OFFICES, kolom OFFICE merupakan PK karena secara utik niembedakan setiap kantor dengan nornor yang bcrbeda. PK dalam table PRODUCTS d~sebut sebagai composite primary key karena terdir~ dan dua kolom yaitu MFR -ID d m PRODUCT-ID. PK tidak boleh mengandung nilai NULL. Setiap table h a n ~ , boleh a meniiliL~ satu PK. Suatu table munglun memlliki lcbih darr sntu ~denrlfieryang bcrpoterlsi nicnjadi P K . S c t ~ a p kolom yang berpotensi menjad PK d~scbutI.'crnu'idare key. PK b~asanyadipilih d a r ~salah satu candidate key tersebut. Candidore key yuig tidak terpilih disebut nlrernn/e key, C L I ~ ~ ~kc). I~LI[C harus memenuhi ketentuan sbb: 1. Tidak boleh ada dua baris dalam sat11 table yang menliliki nilai yang sania untuk candidarc key 2. Canddate key tidak diperkenankan mengandung subset yang unik. Sebagai contoh, composite key MFR ID/PRODUCT -ID tidak tcrmas.uk cnndicr'n/r key b ~ l asa 1.1 3 1 s9tu kolom tersebut unik &lam table ybs Pedornan &lam memilih PK. 1 Unique Idenr~jier Pilihlah identljier yang unik nienjadi P k dalun setlap tablc s c p c n ~kolom OFFICE dnlnn~ table OFFICES. 2. Nilai permanen Jika terdapat child row yang mengacu ke PI(, nila~dan PK tersebut harus tetapiperrnx~en dan tidak dapat drubah. Sebagai contoh, koloni MGK atau CITY dalarn table OFFICES a Tetapi apabila kantor baru d~tarnbah dapat menjad kanddat PK karena n ~ l a ~ n yunik. dalam kota yang sama, atau apabila ninnajer nieninggalkan perusahaan, nilai dari kedua kolom tersebut berubah. Sehingga kcdua kolom in1 tidak dapat menjadi PK karena n ~ l a ~ n y a tidak dapat selalu unik atau perniancn 3. View Semua view yang didasarkan atas table ~ m i gme~nilduPK hams menganduiig semua PK tersebut. Jlka ketentuan h i tidak ditaati. maka pada saat INSERT kc vtew dilakukan. pesan NOT ENOUGH NON-NULL VALUES akan muncul. Pesati 1111 rniincul knrenrl semua kolom PK tidak boleh mengaldung nilai NULL. 4. Jumlah kolom Untuk PK yang composite, pakaildl jumlah kolom minimum untuk menjamm keunikan PK. Hal ini dsebabkan setiap jurelgn kcy yang menunjuk ke PK harus memiliki junllah kolom yang sama. Sebagai contoh daluii table PRODUCTS kita hanya menierlukan koloni MFR-ID dan PRODUCT-ID saja 5 . Not NULL with Default Ketika PK dibuat, pilihan NOT NULL WITH DEFAULT jangan dipakai kccual~kolonikolom PK memiliki tipe data TIMESTAMP atau DATETIME. Ketentuan yang berlaku bila membuat PI( dalam SQLBase: 1. Unique index Jika suatu table memiliki PK, kita harus selalu membuat unlyue Index atas kolom-kolotii PK ybs.
SQLBme Trtrining hfc1terinI Copyrrght 63 1995, by F7 I'errcrdara Systenirndo. Jakarfa
2. Format Format d a r ~PK harus memenuh~ Tidak dapat rnelebihi 16 kolom Jumlah dari panjang kolom tidak bole11 lebih dari 255 bytes Tidak boleh mengandung tipe data LONG atau LONG VARCHAR 3. UPDATE WHERE CURRENT UPDATE WHERE CLRRENT ckalcsr t~dakdapat dipakai dengan kolonl PK 4. Self-referenced row Dalam self-referenced row, nilai PK tidak dapat dirubah Jika suatu table rnemiliki PK, unique rndcx terscbut hams dibuat sesual dengan urutan kolomkolom PK tersebut, h d e x ini disebut scbaga~prrmary index. Setiap table ha.nj,a boleh nlcmil~k~ satu primaty index. Jlka satu table rnen~ilikilebih dari satu unique rndex yang dibuat atas kolorn-kolom PK, maka index pertama yang dibuat adalahprrmary rndex Primary index dapat humtkan secara oscendng niaupun descending. Table &an berada dalam incomplete state sarnpai primary rrldex dibuat. J~katable berada dalarn incomplete srate, operasi-operasi lain seperti insert, srlecr dan membuat foreign k e y tidak dapar dilakukan. Untuk mengantisipasi masalah scperti ini, buatlah primary rnilex segera setelah suatu table selesai dibuat.
Urutan ha1 yang hams dilakukan untuk melcngkap~proses pembuatan table yang r n c r n ~ l ~PK k~ 1 Buat table dengan perintah CREATE TABLE 2 Buat unique index dengan pemtah CREATE UNIQUE INDEX PRlMARI' 3. Rubah table untuk menambahkan P K dcngan pcrlntah ALTER TABLE KEY
Foreinn Key Foreign key (FK) mengacu ke PK dala.111 table yang sama maupun table yang lam. Kolom OFFICE dalam table OFFICES merupakan contoh PK. Kolom REP-OFFICE dalam tablc ENGINEERS adalah contoh dari FK. Data office dalam table ENGINEERS nlengacu kepada data office dalam table OFFICES. Sebcluni membuat FK, PK dan ltnrqile rndex sudah hams
ada. Setiap FK merniliki consrrarnt nume yang rnengidentifikast FK tersebut. C'onsrrnrnt nc7nie In1 diperlukan ketika operasi DROP FOREIGN KEY dilakukan. Constrarnr name d i b c r ~ k a ~pad2 i saat FK dibuat (dengan perintah CREATE TABLE atau ALTER TABLE). Constr~~rnt ncrnle dapat ditentukan oleh user. Jika user tidak mcmberikan constraint name, maka SQLBase aka1 memberikannya sesuai dengan kolori~ pzrtama dari FK. Constrnint name maksirnal dapnt menarnpung sarnpai 18 karakter. Jika terdapat beberapa FK (dari beberapa table berlainan) yang mengacu ke table >,angsnnis. setiap constraint name harus rnerniliki nama yang unik. Sebagai contoh, kitn dapat mcmbunt FK dari kolom 0FFICES.MGR dm membenkan constrarnt name HASMGR. Jika n'ma HASMGR tidak diberlkan, maka SQLRnse akan nlemberdcan constrarnr name MGR sebaga~ default.
SQLtJnse 7i.crining.Mnterial C o p y r ~ g hO f 1995. by 1'7' Yersadara Syrlem~ndo.Jukurra
Ketentuan yang berlaku untuk FK: 1. Kolom yang sesuai FK hams mengandung jumlah kolorn dan urutan kolom yang sama dengan PK. Tlpc datanya juga hams sama. FK boleh memiliki nama kolom yang berbeda dan nllal dqfuult FK juga boleh merniliki nilai NULL. Jika index ddasarkan atas kolom-kolorn FK, urutan ascending maupun descending-nya boleh berbeda dengan urutan dari prlmory index 2. Menggunakan kolom PK Satu kolom dapat dirmliki baik oleh PK maupun FK. 3. FK unhlk tiap table Satu table boleh memiliki sejumlah FK 4 . Jumlah FK Satu kolom dapat dimiliki oleh leblh darr sntu FK. 5. Jumlah kolom Satu FK tidak dapat mengandung Icbih darl 16 kolon~. 6. Parent table Satu FK hanya boleh mengacu kc sebuah PK dalam purenr table-nya. I'crrer.11 tnblr ini hams terdapat dalam database yang sama 7. Nilai NULL Sebuah kolom FK boleh bernilai NULL 8. Hak Otoritas ALTER atas table hams diberikan kepada semua user yang niengatur IU atas table ybs. 9. System catalog table FK tidak dapat mengacu ke system ~,c~tnlog mblt. 10. View FK tidak dapat mengacu ke view. 1 1 . Seljlrejl.rencing row Dalam seFreferencing rout, nilai FK hanya dapat berubah jika nilai PK yang dlacu valid
SQLBase tidak membutuhkan index dibuat untuk FK, namun index dapat meniperbaiki kinerja. Operas1join lazim dilakukan antara P K d a r ~opcrasi ini dapat meningkat
FL dan dcngan membuat indcs untuk FK, kincrja
Tidak seperti kolom PK, kolorn FK bolch bernilai NULL. Walaupun nilai NULL tldak scsua~ dengan nilai-nilai yang terdapat dalam PK, ha1 ini memenuhi IU consfruint. Jikn mungkin, jangan biarkan suatu FK memiliki nilai NULL/non-NULL yang partial. Berikut In1 diberikan contoh masalah yang akan timbul bila nilai NULL/non-NULL partial terjadi. Compos~tekey MFR-ID/PRODUCT-ID merupakan PK dan table PRODUCTS Coniposite key MFRPRODUCT merupakan FK dalam table SERV-CALLS yang mengacu ke table PRODUCTS. Misalkan kolom PRODUCT dalam table SERV-CALLS d~ljlnkan boleh mengandung nila NULL. Hal lIllberart~klta boleh memasukkan 111lal non-NULL untuk kolom SERV-CALL.MFR dan nilai NULL dalarl~Lolom SERV-CALL PRODUCT SQL> INSERT INTO SERV-CALLS VALUES(8000,9-3-93,2000,103,'WRS',NULL). I
I
Hasllnya baris lang dimasukkan terscbut mengaldung nilal FK yang tldak cocok dengan nllal PK manapun dalam table PRODUCTS SQLB(zse 7'rcrining M(iterin1 Copynghc @ 1993 by P7' ~ e r s a i o r oSysrerrrrndo.Jokurto
Table SERV CALLS CALL-NUM CALL-DAT
CUST
-
8000
1 1993-09-04
1 MFR
ID WRS WRS Table PRODUCT
2000
I PRODUC:?'
1
i
24, 25,
REP
1
ID
103
MFR
I
/
WRS
1
DESCRIP.I'ION
1
Wideet 2
PRODUCT
I I
-4
SET NULL delete rule Situasi yang sama terjadi dengan Sli'I' iWf.ld delete nrle Dengan ketentuan I ~ I ,nlenghapus sebans data dari table PRODUCTS mcngakibatkan kolom SERV_CALLS.PRODUCT menjadi NULL, Tapi pada kasus ini, impliknsin>'atidak akan terjadi karena t ~ d a kada FK >,ang memenuhi PK.
INSEftT sroternent Dcngan mempertimbmgkan RI, SQLBasc akrul monganggap baris data >.an2 NUl,L,lno~lNULL sebagai NULL. Jika suatu bans d i m g a p sebagai NULL, SQLBase tidnk &an mengecek RI-nya lagi setiap kali per~ntallINSEKT dllakukan. In1 artllllza SQLDasr: t ~ d a kaknn mengecek nilai kolom FK yang non-NU1.L untuk n~elihatapakah nilai tersebut cocok dcngar~ nilai dalam parenf mble.
Parenuchild Table Table yang menlilikl PK adalah poret~r PK dan FK menggarnbarkan hubungm i~cir~.nr,child. table, scdangkan table yang mengandung FK adalah child [able. Child darl child dlsebut descendent. Dalam contoh di bawah ini, table PRODUCTS adalah pilrrnr darl table SERV-CALLS. Untuk menjadi parenl rable, table terscbut harus memiliki PK dan primnrj index. Ada table yang tidak memiliki porenr nlaupun child table. TabIe scperti ~ n id~sebut sebagai independent table.
Parenuchild Row Satu bans dalam parenf fable yang nlcr~jadlacuan dan bans lam dalrtin ~ h r l droble d~scbur sebagal parent row. Sedangkan bans l u g niengacu ke pcrrerlt row adalah child rorr Cllrl~i row hams mem~likipaling sed~kltscltu kolom FK lang tldak NULL Tidak semua bans dalam parenf rable harus menjadi pnrenl row dalanl ani tldak ad3 raw yang mengacu ke baris tersebut. Contohnya ialali suatu bans di table PIIODUCI'S descrpfion-nya 'Autornmatic Camera' tidak nlenjadi acuarl dari baris mannpun dalam SERV-CALLS. Sama haInya bila baris tcrtcritu di child [able mengandung nila~NULL FK-nya, maka bans tersebut bukan child row
cl7110 l.allg tablc
padn
Delete-connected table
SQLUtlse Trt~ininghlaterinl Copyr~ghr@ 1993, by PT. Versodoto Systern~ndo.Jakarta
Blla menghapus satu baris dalanl satu tablc mempengaruh~table y m g lam, maka table terscbut tergolong dalam delete-connected table Sebagal contoh, menghapus satu barls d a l m tablc OFFICES mempengaruhl table ENGINEERS karcna setiap karyawan dihubungkan dalanm satu kantor tertentu Setiap table yang tcrlibat dalam operas1 delete adalah delete-ionnec~cd Defirllsi benkut ini berlaku untuk delere-connected table: 1. Self-referencing table pasti delrrr-connrcred terhadrlp dirinya sendir~ 2 . Child table selalu delere-connecrrd dengal yarenr ruble-nya tidak perduli kctcntuan DELETE apa yang ctterapkan. 3 . Suatu table m e n j d delere-connectccl dengan grandparent dan great-grondprrnr-n).a bila ketentuan DELETE antara paren[ d m grandparent, atau grandparent dengan grrargrandparent-nya adalah CASCADE.
I
1
Table
I
great-grantparent table
CASCADE grandparent table
I CASCADE Table
1 I
I
I I!
!
I
i
1
parent table I
Gambar 4.1 Delete-comccted table Dalam gambar di atas, Table 4 menjadi delere-connecred dcngan Table 2 karcna hcrcntuan DELETE antaraa Table 2 dan Table 3 adalah CASCADE Table 4 juga delrre-cotinccted dengan Tablc 1 karena kctcntuan DELETE antara Table 1 dan Tablc 2 rtdalah CASCADE Sedangkan ketentuan DELELE antara Table 4 dengan Table 3 tidak mernpcrigaruh~hubungm delete-connected yang telah terjadi
Membuat Table dengan RI constraint Kita dapat menggunakan perintah CREATE TABLE dan ALTER TABLE urltuk ~iiernbunt atau merubah table dengan PK atau FK, dan mcneiitukan KI constrarnr
Berikut ini adalah perintah CREATE TABLE untuk membuat table ENGINEERS lerlgkap dengan PK dan FK-nya:
SQLLJase Trtrining Material Copyrrghr
(3 1995, by
I'T Versudata Systenrrndo, Jakarta
SQL> CREATE TABLE ENGINEERS (EMPL-NUM INTEGER NOT NULL, NAME VARCHAR(24) NOT NULL, REP-OFFICE INTEGER, TITLE VARCHAR(15), HIRE-DATE DATE NOT NULL, MANAGER JNTEGER, PRIMARY KEY (EMPL-NUM), FOREIGN KEY WORKSIN (REP-OFFICE) REFEFENCES OFFICES ON DELETE RESTRICT); Kita juga dapat rnenggunakan perlntah AL'I'ER TABLE untuk menlbuat PI( dan FK sctclah table selesai dibuat. Untuk rncmbuat FK ISFOR dalarn table SERV-CALLS sctclah tablc berhasll d ~ b u a dengan t CREATE TABLL, gunakan pcrlntah ber~kutIn1 SQL> ALTER TABLE SERV-CALLS FOREIGN KEY ISFOR (MFR, PRODUCT) REFERENCES PRODUCTS ON DELETE RESTRICT. Table akan berada dalarn inconiplr/r .vrtrre sanlpal jlrrmnry rndex dibuat La~igkahin1 harus ~ir~~ dilakukan segera setelah table selesai dibuat Szbagai contoh, untuk menibuat. y r ~ n ~ ir7dt's untuk table OFFICES, gunakan perlntah berikut. SQL> CREATE UNIQUE INDEX 0FFIC:E-IDX ON OFFICES (OFFICE); J ~ k alnfornlasr database dl-load dengall perlntah LOAD, lndcs harus d~buatsctelall lotrci selesa~supaya klneqa tidak terganggu l ~ k prrtrrrrry a key d~tan~bahkan sctclah ALTER TABLE unlque ~ n d e xhams te1a.h ada dl kolom prrnlcrry key sebelurnnya
System catalog table untuk inform as^ R j Ada 3 SQLBase systenl catalog table yang mengandung ~nforn~asi RI. \,aitu SYSFKCONSTRAINTS, SYSPKCONSTKAINTS d m SYSTABCONSI'It4lNTS 1 . SYSADM.SYSFKCONSTKAINTS ( F K Constraints) I lnfonnasi 1:k cinr~ tablc-table, mlsalnya c,onxrrcitn/ t l i r t l i L ' . Lnlor11 Table I ~ mengandung d a r ~FK danparent table yang nlerljadi scum SQL> SELECT *FROM SYSFKCONS'I'RAINTS WHERE l\IAME='SEKV CALL.,S'. 2. SYSADM.SYSPKCONSTRAINTS (PI( Constra~nts) Table ini berisi informasi PE; d a r ~tablc-table, sepenl narna kolom PK darl nama table-11)~1 SQL> SELECT * FROM SYSPKCONSTRAINTS WHERE NAME='PRODUCTS'. 3. SYSADM.SYSTABCONSTRAlNTS (Table Constraints) Table ini berisi inforn~asi senlua L , O ~ I . S I ~ L ~ Id~ Ia[ l m setiap tclble, scpcrti niuniri dari tipi. construin/ (PK atau FK), ketelltu'u dclctc urituk FK, dan setiap ziser error t?rexsagcl \.aiig dibuat . SQL> SELECT * FROM SYSTABC:C)NSTRAINTS WHERE NAME='SERV - CALLS'. -
lmulikasi RI
RI memiliki irnplikasi yang khusus tcrhadap operas1 INSERT, UPDATE, DIIOP, SELEC'T dan DELETE dalarn SQLBase. i
SQLUnse Twining iCl(lteria1 Copyr~ghlO 1995. h~ P7' I'eruuiii~l~rSyslt'n~rndo.Jariclr.r,i
INSERT SQLBase akan menerapkan ketentum bbcrlkut b ~ l adata dl-rn~errke dalam tablc ~ , a i m g cm~llh~ satu atau lebih FK. 1 Setlap nilai non-null yang d l - ~ n ~ ekc r t dalam kolom FK harus sesunl dengnn snlah sat11 nllal dalam PK 2 J ~ k asalah satu kolom dalam FK null, SQLBase akan men~perlakukalseluruh FK sebaga~ null SQLBase t ~ d a klag1 melakukan referent101 check atas perlntah lNSERT urltuk FK yang mlainya null. rnble 3 Operas1 INSERT tldak dapat dilahukan terhadap parent atau chrld table b ~ l a/)~~rt'nl bcrada dalarn ~ncompletesrnre (rn~snlhnnprlmory key rndex-nya dl-drop) Data dapat di-~nsertlie dalarn ynrenr cnhle setlap saat tanpa rnempcngaruhl ch~lcilablc> Contohnya, kita dapat menambah kantor baru ke table OFFICES tar~pamcnipcngaruhl tablc ENGINEERS.
UPDA TE Jika child table di-update, setiap non-null FK harus sesuai dengal PK dalam purenr rub118 Satu-satunya ketentuan UPDATE yang dapat ditcrapkan ke parent rnble adalah RESTRICT 11ll berarti setiap usaha untuk merubah PK dari parent table tidak diijinkan jika tldak ada nlla~ yang sesuai dalarn child table. Ketentuan yang berlaku untuk perlntah UPDATE lalah. 1 . Perintah LIPDATE yang memberikan nilal ke PI( tidak dapat mcnspcsifikas~Icb~hdl11I siitu record. 2 . Perintah UPDATE yang mcmilik~WHERE CURRENT OF clcrztse tidak dapat merubah PK, atau kolom view yang berasal d a r ~PK.
DELETE
I
Ketentuan DELETE dapat d~berlakukan terhadap setlap relas~ parenr chlld Kctcntuan DELETE mengatur apa yang hams SQLBase lakukan jlka user berusaha menghapus scbar~s data dan parent table. Ada 3 kctentuan DELETE yang berlaku R E S T N C T , CASCADE, SET NULL DELETE RESTRICT Ketentuan ini merintangi penghapusan data dalam parent table jika data tersebut mnsih merniliki child row. Baris ini hanya dapat dihapus bila sudah tidak ada chrld row yang tergantung kepadanya. Sebagai contoh, ketentuan ini cocok untuk relasi aritara tablc SERV-CALLS dengan PRODUCTS. Data produk tidak boleh dihapus jika pruiggilan servis masih terjadi atas produk ybs SQL> ALTER TABLE SERV-CALLS FOREIGN KEY (MFR,PRODUC:'17) REFERENCES SERV-CALLS ON DELETE RESTRICT; Jika tidak ada ketentuan DELETE yang diberikan, maka default-nya adalah RESTRICT DELETE CASCADE Jika parent row dihapus, maka sernua chrld ruw-nya secara otomatls terhapus dorr c h ~ l d table Ketentuan ini cocok ditcrapkan untuk relasi antara table SERV-CALLS dan
CUSTOMERS. Data pelanggan ha1b.n boleh dihapus jika pelanggan tcrscbut telah tidnk aktif lag1 sehlngga semua panggilan scrvls d a r ~pelanuan ybs, harus dihnpus SQL> ALTER TABLE SERV-CALLS FOREIGN KEY (CUST) REFERENCES CUSTOMERS ON DELETE CASCADE; Ketentuan ini tidak menghapus ynrrnr r o w jika chrld row atau ilesct~,~dcnt rovv nlcnlilik~ ketcntuan DELETE RESTRICT. Untuk sevlrejirencing ruble, CASCADE nlerupakan satu-satunya ketentuan yang diijinkan DELETE SET NULL Jikapnrent row dlhapus, nila~FK dari scnlua child ruw-nya sccara otoi~~atis dibcrika~i1i1lai NULL. Contohnya, bila seorang kag-a~vankcluar d a r ~perusatlaan, semila panggilan scrvls pelanggan yang ditangani oleh haryacin !lbs, menjadi tangung-ja~vabkan.awan lain ).ang beluni diketahui sarnpai karyawan tcrscbut. ditugaskm. SQL> ALTER TABLE CUSTOMERS FOREIGN KEY HASREP(SERV-REP) REFERENCES ENGINEERS ON DELETE SET NULL; FK bisa diberikan nilai null bila saIa11 s;lt~lkolon~FK memperbolehkan nil31 11~111
DROP Jika satu table dhapus (drop), maka PK dim FK-n)a oromatis terhapus. J ~ k aprrrc.nr t~lblt';it.a~~ PK-nya dhapus, rejirenrial c u n s r m ~ n r - n ~juga i ~ tcrliapus. Jika hi& ALTER atas parct~rcia11 child rnhle dinliliki, PK dapat dihapus. Scbuga~contoh perintah berikut 1111 111c1ighap11sPK diir.~ table OFFICES dan relasi pnrenr/child dcngan table ENGINEERS, SQL> ALTER TABLE OFFICES DROP PRIMARY KEY; Menghapus PK tidak menghapus yrrrn~-rry~ t i d r s .Indux tetap scbaga~llniyitc 1t7ilc.x dal~unn kolorn PK sebelumnya. Jikaprimury inilcs dihapus, nlaka table beract? dalam rncotnplrte .stirre', s e h i n ~ aperlu dlbuat unique index yang lain ntas kolom PK ybs, lir/l.rc.n[7nl cons[roin/ tc'tap berlaku ~valaupunprimary index dihapus. Untuk nncnghapus FK, user h a n ~ smcm~liki hah ALTER atas pnrenr dan deyendenr tablc Conroh berikut menghapus FK ISFOR d a r ~tablc SERV-CALLS: SQL> ALTER TABLE SERV-CALLS DROP FORElCiN KEY ISFOR,
SELECT Karena perintah SELECT tidak merubah 111ln1data, muka perintah In1 t ~ d a kdipc~igaruh~ olcli RI.
Siklus dari Dependent table
1
Table ENGINEERS mengandung kolorn REP OFFICE yang Inengacu kc kololn 0FFICES.OFFICE. Table OFFICES Jilgii memlli6 FK yaitu di pada kolom MGK, !.ang lilengacu ke kolon~ENGMEERS.EMPL-NUM. Sctiap table nlenlilik~FK 1.ang mcngucu kc PK masing-masing. Relasi seperti ini membcntuk rqfirrnrial cycle. In1 berarti szlr~uabans dalam table ENGINEERS hams mengacu ke salah satu baris di table OFFICES, \.,an! mengacu lagi ke table ENGINEERS d m sctcrusnya. Contoh in1 n~cniiiijukkans ~ k l u sd n r ~du:i table. Siklus yang terjadi bisa mclibatkiui I z b ~ dl ~a r ~dun table Table ENGINEERS EMPL NUM
1
NAME
I
REP OFFICE
I
100 104 107 102 101 106
I I
PK
Paul Atkins Bob Smith Murray Rochester Larrv Sanchez Sheila B r o ~ n Sam Valdez
1 1
10 20 30 10 10 30
, OFFICE
20 40 10 30 Table OFFICES
crn San Fransisco New York Los Angcles Chicago
-
103 1O X 100 106
FK MGR -
-
Implikasi INSERT Siklus sepertt dt atas dapat mengakibatliru~ masalah untuk operas1 INSERT Misalkan J I L J seorang senlor engineer yang bemania Ronald Casey (employcc 112) baru rnasuk dan aLnn menjadl manager dl kantor baru di Boston (office SO) INSERT INTO ENGINEERS (EMPL-NUM,NAME,REP-OFFICE, T1'TLE.HIRE-DATE) VALUES (1 12,'Ronald Casey',jO,'Manager',8-15-93), SQL> CNSERT INTO OFFICES VALUES (SO,'Boston','Eastcm', 1 12,NULL).
Perintah INSERT pertama gaga1 karena rncngacu ke office 50 yarig seberiarn3a bzium adn Btla INSERT kedua dilakukan dulu, bdru lNSERT pertama juga tidak nlernecahkru~masalnli karena manager 1 12 belum ada Untuk nlcrigatasi kasus mi, paling sedlklt satu dari F K d a r ~ referentral cycle harus mengtjmkan nilai NULL Perintah yang berhastl adalah SQL> INSERT INTO ENGINEERS VALUES (1 12,'Ronald Casey1,NULL,'Mmager',8- 15-93',NULL), SQL> INSERT INTO OFFICES VALUES(jO,'Boston','Eastem',1 12,NULL), SQL> UPDATE ENGINEERS SET REP_OFFICE=50 WHERE EMPL-NUM= 1 12, Implikasi DELETE Referentlal cycle juga dapat incngak~ba&m masalah untuk operas1 DELETE Untuk menjelaskan ha1 ini, dipakai tiga table OFFICES, CUSTOMERS, ENGINEERS Tiga table In] memlllkl relas~refcrentlal cycle Table CUSTOMER adalah parent d a r ~table OFFICES OFFICES adalah parent d a r ~ ENGlNEEKS dan ENGINEERS adalah parent d a r ~ CUSTOMERS Gambar benkut ini nicnunjukkan apa tang tcrjadl j ~ k ascbnrls dnt,l d~liapus dengan ketentuan DELETE yang berbcdn
SQLUrrse Trllining Materiul Copyrfghr W 1995. by P T ~ e r s o d o r oSysrertrrndo. Jokorro
Diagram benkut menunjukkan relasi antar table j k a setiap FK dbuat dengan ketentuan DELETE RESTRICT. OFFICES OFFICE
MAJ ACCCOUNT 3000
10
ENGINEERS EMPL N U M
1
CUSTOMER CUST NUM
t
k
/
REP OFFICE
,
I
/ ' SERV REP
1001 1050 PK
FK awal proses RESTRICT
Dengan ketentuan seperti di atas, tidak ada customer yang dapat dihapus karena scrnua customer adalah parent row dari table lain. RESTRICT jangan dipaka1 untuk semua relas~ dalam referential cycle, kecuali bila DBA ingin menjaga supaya tidak ada data yang dapat dihapus.
Delete-connected table restriction Ketentuan berikut ini berlaku untuk delete-connected table: 1. Jlka operasi DELETE rnelibatkari table lain yang rnenjah acuan dalam subqucp, ketentuan DELETE terakhir hams KESTRlCT 2. Jika dua table menjadi delere-connected melalui dua atau leblh rejerrntral yolh, p n ~ h tersebut (atau path terakhir) hams rnen~ilikiketentuan DELETE yang sama, d,m tidnk dapat menggunakan SET NULL.
!
i
1
Keterangan untuk butir 1: Salah satu peraturan dasar SQL adalah hasil dari suatu operas1 sehamsnys t~dak tergantung kepada urutan baris data y'mg dakses. h berarti subqzrery dari pcrlntd~ DELETE tidak dapat mengacu ke table yang datanya ingin dhapus. Sebagai contoh, misalkan jika tidak ada f2l constrclint, maka hta dapat melakukan perintah ini: SQLBase Trrzining Material Copyrrghl 0 1995, by PT Versadato Sysretnrndo.Jakarta
SQL> INSERT INTO OFFICES VALUES(lj,'ANYTOWN','MIDWES?",333,NULL) Kemudian kita dapat menghapus semun baris dari OFFICES yang mnnaJem!.a tidnk terdapat di ENGINEER. SQL> DELETE FROM OFFICES WHERE MGR NOT IN (SELECT EMPL-NUM FROhI ENGINEERS); Tetapi bila FK di table ENGINEERS mengacu ke OFFICES, subquery dl atas m e l a n ~ a r aturan yang berlaku. Hasil dari operasi ini sangat tergantung kepada urutan baris yang diakses. SQLBase akan menggagalkan opcrasi ini dan menanlpilkan pesan kesalahan.
Keterangan untuk butir 2: TABLE 2
TABLE 2
C a s ad TABLE 3 I
'4 1, TABLE 1
i
Stru ktur 2
Struktur 1
I
!
Garnbar 4.2 Struktur cl~llere-connectedyang valid Struktur pertama mengganlbarkan struktur rcfcrenr~alyang valid dengall tlclrlc-connrcreci table. S t r u h r kedua juga rnemil~ki d~>it.rtj-connecrionyang valid. Dalani t ~ a p struktur, TABLE 1 niemiliki ketentuan DELE'I'E !.ang samn, d m ketentuan terakhlnlya bukm SET NULL.
Struktur 1
Struktur 2
Ganlbar 4.3 Struktur cli.icri.-cunnrcted yang ~nvalid
SQLBase Trnining M(lterin1 Copynghf O 1995, h i : PT. Verxodafo Sysfenrrndo,Jokurfu
Di struhzur 1, TABLE I menliliki kercntuarl SET NULL yang sama. DI struktur 7 , dua ketcntuan terakhir tidak sarna. Di strukrur 3, dun tablc dihubungkiu~dcngal~dua ketc~lruan DELETE yang berlainan Jika kita mcng~jlnkan ketentuan SET N U L L d a r ~prlh yalig bcrlainan, mnka ada kemungklnan FK-n>a null/non-nuII. Tctapi bila path-n>3 KESTRIC'T arau CASCADE, child row-nya nkan dihapus (CASCADE) ntau tetap sama (RESI'KlCT).
1
SQLTalk dan RI
Perintah SQLTalk
DATABASE REORGANIZE
Dampak terhadap R1 -. SQLBase mcnonaktlfkan seniiia pengecekan R1 scbeluni n~emulai proscs m d a n t i t k a n pcngecek'm kembali sctclah LOAD sclcsn~ -~ Operasi CHECK ITA ABASE tidak dilakukan jika ada tablc lang b c r a c c dalam kondisi pctidlrl~ -nrau ada tablc yang sedans mclakukan penpcceknii R I SQLBase rnenonnkaiifiun semua pengecekan RI scbeluni rnernula~proscs REORGANIZE dan tncng&ti&an kernball pcngccckati setelnli REORGANLZE sclcsa~ -. SQLBase mcnonnktifknn scmua pengecekan R1 scbelum mcrnulai proscs COPY dan nicngaktifkan kernbali pengecekan sctcldi COPY sclcsai dilakukan -
--
V. Procedure, Trigger dan Event
SQLBase Procedure SQLBase Procedure adalah sekumpulan SQLWlndows Applicahon Language (SAL) dan perintah SQL yang diberikan suatu nanla, dl-comp~ledan secara opt~onaldlsilnpan dalarn SQLBase database. Ada beberapa tipe dari implement as^ procedure: 1. Stored procedure di-compile dan dislmpan dalam database untuk dicksckus~kcmudian 2. Non-stored pr~ceduredi-compile untuk scgcra dieksekusi. 3. Ketika trigger clan event dibuat untuk memanggil inline procedure test, INLINE clause d a r ~ perintah CREATE TRIGGER atau CREATE EVENT hams diberikan. Inilah yang disebut sebagai d i n e procedure. Pada saat trlggcr atau event dibuat, SQLBase mcnyinipan 1n11nc procedure seperti ini dalam system catalog.
Keungulan mengunakan proccdure:'. 1. Menyederhanakan aplikasi dengan memindahkan sebagian proses ke server 2. Mengurangi network rrnSfic dengan menyimpan perintah SQL yang dieksekusi di backend tempat procedure diproses. Fronicncr' hanya perlu memanggil proccdure dan menunggu hasil pemrosesan. 3. Meyediakan fleksibilitas yarlg lebih balk dalam ha1 kcamanan (st'currty) dcngan memberikan hak kepada end-users data yang tidak berhak diakscs. 4. Meningkatkan kinerja runtime karena /~roceditrcrllogic telah di-prccotr~/~rle. Untztk .star/c. storedprocedure, perintah SQL juga dl-/)recotrryrlt.; scbagai hasilnya, S'QL cxt.clrrrot7 pl~rt7 juga telah ditentukan. 5 . Memiliki lokasi sentral tempat menampung precompile program yang dapat diakses oleh lokasi-lokasi lainnya untuk kebutuhan aplikas~yang bcrbcda-bcda. 6. Procedure dapat dsimpan, kemudian di-retrleve scrta dieksekus~ d a r ~fionlcnd >.ang berbeda-beda. SQLBasc mengijinkan user untuk rnenynlpan pcr~ntihSQL y;~11g S C ~ I I Id~~ g i ~ ~ ~ ; id;il;i111 k;i~i stored command untuk deksekusi pada masa yang akan datang. Narnun stored conunand memiliki keterbatasan, yaitu hanya dapat l~~cnyirnpru~ satu per~ntah SQL saja. P~occdurc, dalam pihak lain, mengijlnkan user mcmbuat program dengan memanfaatkan /~roced~trrrl log~c dan variable yang menggunakan lebih dari satu perintah SQL.
Tidak seperti stored command, stored proccdure tidak pemah mcnjadi invalid, walaupun stored command yang terdapat dalam stored proccdure dapat rncnjadi invalid Ini artlnya user t~dak perlu me-recompile proccdure secarn otomat~s dcngan EXECU'I'E KECIOMPILE, ncau membenkanjlag untuk keperluan recomp~ledengan AL'I'EK COMMAND Perbedaan lainnya ialah prograrrl yang mcmangg~lstored procedure t ~ d a kdapat munggunakar~ scrollable cursor (dengan SET SCROLLROW ON) untuk scroll bnck/scroll .forth has11 pernrosesan dari suatu procedure. Program yang memanggil stored procedure In1 juga ndak dapat melakukan fetch backward terhadap result set yang &hasilkan oleh suatu procedure
SQLBase Trairring Material Copyr~ghf@ 1995. by P7: Vclrsadata Sysreit~rndo.Jakarta
Format dari Procedure SQLBase procedure mengikuti format clan syntax yang sama dengan SQLLli~ndoivs.Nanlun tidak seperti SQLWindows, elemen yang terdapat dalarn SQLBase procedure terniasuk clueinsensitive. Elemen-elemen dari procedure ialah: 1. Nama procedure Nama procedure boleh berbeda dengar1 nama pada s a t procedure dis~mpan. 2. Parameter Parameter untuk keperluan input d m output ke procedure dapat didefinisikan. 3. Local variable Local variable didefinisikan untuk ternpat pcnyimpanan sementara. 4. Action Section Bagian ini digunakan untuk mcngontrol kondisi dimana perintah dieksckus~dan urutan pelaksanaan eksekusi. Nama procedure Setiap procedure hams memililu nama. Scbngn~contoh: PROCEDURE: WithDraw Nama procedure adalah long identrfier dan dapat rnenampung sampal 18 karakter Pada smt procedure disirnpan, user dapat rnemberiknn nama tambahan yang menlungkinkan user mengaksesnya lagi. Secara umurn, nama !,zing dibcrikan sama, walaupun n m a yang diberlkan boleh berbeda. Contoh: SQL> STORE WDPROC PROCEDURE: WithDraw Parameters
Parameter Parameter digunakan untuk memberikan Input dan n~cncrimaoutput dari proccdure. Bagla1 In1 optional, artinya tidak hams ada parameter di setiap procedure. Pada saat procedure dieksekusi, user hams m e n y d a k a n nilal bagi sernua parameter yang terdapat dalam procedure tersebut. Syntax dari parameter adalah: [Receive] DataType [:] ParameterNamc Contoh: Parameters Boolean: bDone Date~Time:dtBirthDate Number: nCount Receive Number: nTotal String: strLastNarne Output parameter dalarn procedure harus didihului dengan keyword lieceive. Contoh: Receive Number: nTotal
SQLTalk meneruna nila~ brnd v a r ~ o h l ~untuk Input parameter Untuk o l t r i ~ u r r ~ , c e r v t ~ parameter, user hams rnenydakan place holder, dengan atau tanpa m l a ~untuk b1t7d ke semila parameter tersebut. Jika recerve parameler d~gunakansecara kons~stensebaga~output, user dapat menggunakan tanda koma (,) tanpa spas1 sebagal place holder. Array t ~ d a kdapat digunakan sebagai parameter.
i
Local variable Local variable ruelakukan beberapa fungs~cialmm perrntah SQL. 1 . Menyimpan data. 2 . Bind input data ke perintah SQL. Vnriablc scmacam in1 dinanlaknn bind virr/~~hIc~ 3. Menenhlkan dimana lneletakkan output dari pznntah SELECT. Variable dalnm IN.1'0 clause disebut into varinble
Local variable juga tidak hams selalu tzrdapat dalam procedure. Tidak ada keyword Necclvt' dalam local variable. Local variable didcklarusikan sillna seperti sjntas parameter. Data Type [:I LocalVariablcNanc Bagian ini mengandung perlntah 4.ang hsrus d~zksekusitergantung levcl dari proccdurc Sclaln itu, juga niengandung logic jlon' lnngzlc~,g~* y r i g mcngontrol urutan peiigcksekus~alipcrlritah User tidak dapat memasukkan user-dqf;ne>cljitnction dalam procedure. Procedure dapat memanggil procedure lain untuk menjalnnkan fungsi yaiig diinginkan. Satu block dalam bagian ini mcngandurig sckunlpulaii pcnntah yang dieksekusi scsuai derigai urutan. Semua perintah dalam satu block mcnliliki indentation level yang s a n a atau draw311 dan diakhiri dengan Begin dan End.
Tipe data parameter dan local vanable User hams rnemberikan salah satu d a r ~ trpe data bcrikut In1 pada saat nicndcfi~l~s~kari parameter dan local variable Tipe data Nilai default -
Sql Handle Datemime
none 0
null string
I
.-
1iSqlCur 1 dtStartDatc
-
Hanya ulltuk local var~ablc __I
s (atau) srr
Standard system constant yang dapnt d ~ g u n A a nialdl: 1. strNull null string constant. 2. TRUE dan FALSE boolean constant. 4. DBP parameter: DBP AUTOCOMMIT, DBP-BRAND, DBP-PRESERVE,
DBP VERSION, DB? LOCKWAI'I-1.1hlEOUT, DBP-ROLLB.4CKTIMEO U7' 5 . DBV_BRAND database brand: DBY_BRAND_DB2, DBV-BRAND-OSZEE. DBV-BRAND-OR4CLE d m DBV-BRAND -SQL Semua tipe data dapat ditulis dalam bcntuk lam pang disebut rec.c.l\v d~ltrr type. >.ang mengidentifikasikan output parameter l(ri.eivr ~lcrtotjy)e niengtjinkan user untuk membcrrkan data ke procedure secara referensr (by rcf2retice) darlpada by value I111 berarti SQLBnsc
SQLfJuse 'l'ruining hluteriul Copyrrghc 8 1993. ,hi. P7' i'ersod~rrrr Sysle~~rrndo, Jakurra
Contoh: Parameters Receive Boolean: bOrderFilled ...
Actions Set bOrderFilled = TRUE
Perintah SAL dalam Stored Procedure
I
i
Perintah SAL digunakan untuk mengontrol aliran logic dalam procedure menyediakan fasilitas SAL berikut mr. 1. Break 2. Call 3 . If, Else dan Else If 4 Loop 5. On 6. Return 7. Set 8. When SqlError 9. While
SQLBase
Break Perintah ini r n e n g h r i Loop. Fasilitas irli nlenlungkinkan user untuk mengakhiri lebih dari satu level dari loop. Jlka nama loop tidak diberikan, maka perintah in1 akan niengakhlrl loop yang paling akhir dilakukan. Syntax:
Break [loopname] Contoh: Loop Set nOutput2 = noutput2 + nInput2 If nOutput2 > nInput2 + 10 Break
Call Pcrintah ini mcngeksckusi SAL firncriotl Syntax:
Call FunctionNarne(l'ararneter, . . ) Contoh: Call SqlImmediate ('DELETE FROM CUSTOMER WHERE CUSTNO = ' 1290')
SQLUasr 'liuinirrg hlutrriul Copyrrghr O 1995, b ) ~PT I,'ersoJara Sysfe~tr~ndo. Jukurru
Bila menggunakan perintah Call, ini berarti user akan mendapatkan finetion return vnlite Tetapi jika kesalahan diternukan, SQLBase mernindahkan kontrol ke error handle yang
If. Else dan Else If Perintah ini mengeksekusi permtah-perintah lain berdasarkan hasil dari suatu ex1)ressron
ELSE dan ELSE IF optional. Untuk sctiap perintah IF, user dapat rncnuliskan ELSE IF sebanyak yang diinginkan, tetapi hanya boleh satu ELSE saja.
YExpressionl <statement(s)l> Else JfExpression2 <statement(s)2> <statement(s)3>
Jika hasil evaluasi Expression1 TRUE, starement(s)l dieksekusi Jika hasil evaluasi Express~onlFALSE,E.rpress1on2 dievaluasi. Jika hasil evaluasi Expression2 TRUE, sraremenr(s)2 dieksekusi Jika hasil evaluasi hipression2 FALSE, srarcrnent(s)3 dieksekusi.
If nMonthly-Salary < 1 000 Set nTax-Rate = 10 Else If nMonthly-Salary < 2000 Set nTax-Rate = 20 Set nTax-Rate = 25
Perintah ini mengulangi sekumpulan pcrultah sanlpai perintah BREAK atau RETURF
Loop [loopname] Loopname bersifat optional. Men~berikan loopname memudahkan mengidentifikasikan loop yang tepat sebelum menjalankan BREAK.
If NOT SqlExecute (hSqlCur) SQLBusr Truining Muteriul Copyright Q 1993, by PT Versadata Systernrndo. Jakarta
user
untuk
Return 20 1 Set I3 = I3 - 1 Else Break Outer
Suatu procedure dapat berada &lam s a l d ~satu tahap di bawah ini: 1 . Procedure Startup 2. Procedure Execute 3 . Procedure Fetch 4. Procedure Close User tidak hams selalu n~enggunakan ON dalam mcnulis stored procedure. Jlka ON tldak digunakan, SQLBase akan memproses sclun111 procedure. Artinya. SQ LBase akan memperlakukan keadaan ill1 sama sepertl b ~ l ahanya ada ON Procedztre Execitre dalam Ac.r/on Sebenarnya keadaan default (ON Proccdurc Execute) biasanya cukup untuk kcbanyakan procedure. Walaupun demikian, ada dua sltuasl tertentu dimana ON <proccdurc state> dibutuhkan: 1. Jika user ingin mengeksekusi suatu proccdurc sccara bcnrliuig-uliuig bila d~berikannlla~ parameter yang berbeda-beda. Adalah lebih efisien untuk memberikan On Procedure Slarlzcp dan rnenuliskm perintah-pcrlntd~yang hanya dieksekus~satu kali saja (mlsalkan untuk database connectron dan pcnlberian n~lai variable). Hal In1 akan nienghindar~ eksekusi berulang-ulang yang tidak dipcrlukan 2. Jika user ingin melakukan fetching untuk mztltlple row, On Procedure Fetch dlbutuhkan. SQLBase menjalankan Procedure Srclrtup dan Procedure Close hanya sckall. Procedzir~' Execute dan Procedure Fetch dapat diproses berulang kali sesuai dengan kebutuhar~ user Local variable berubah sejalan dengan operas; execute dan fetch yang bcrulang-ulang; 111lainya hanya dihilangkan pada saat Procedure Close dijalankan. Untuk menampung semua output data yang dihasilkan oleh procedurc, jumlah output variable yang disediakan harus scbanynk jumlah item yang ingin dihasilkan. Syntax: On <procedure state> <statement(.) > Procedure Startup Suatu procedure dlsebut bcrada dalarr tahap procedure srartup bila kedua langkrd~dl ba\\aIi ini telah selesai dilakukan: 1. Program pemanggil rneng-cornprlc procedure 2. Program pemanggil mengeksekusl procedure untuk pertama kallnya.
Setelah memproses perintah-pemtah j a i g terdapat dalam tahap Procedure Startup, per~ntah eksekusl pertama dari program pemangll juga memproses pemtah-penntah dalarn Procc~d~irt. Execute Dengan kata lam, program pemmggl menlproses balk Procedure Start~cpmaupun Procedure Execute. Penntah eksekus~berlkutnya dan program pemanggll hanya memproses Procedure Execute saja, jadl Procedure Srclrttip t~dakd~proseslag1 SQLBase Ziuining Muteriul Copyright
O 1995. by P7'
Versadoru Systemrndo. Jakorro
Procedure Execute Suatu procedure disebut berada dalam tdlnp procedztre execlttc bila kcdua Ia~lgkahdl b a u h In1 telah selesai dilakukan: 1 Program pemanggil mengeksekus~proccclurc untuli pcrtana kal~nya. 2 . Procedure Startup diproses. Procedure Execute diproscs d m dlrcproscs sctlap k a l ~ program pcmnngg~l mcnjLil~rth.ln per~ntahexecure.
Procedure Fetch J ~ k aprogram pemangg~l n1en~a~ank;ul pcr~ntahjerch dan tcrdapat bag~anProcedure 1;rrch dalam procedure, maka per~ntah-penntahdalanl Procedttre Ferch tersebut d~prosesProc5cdltrc Ferch diproses dan direproses setlap k a l ~pcrintah ferch d~lakukan BagIan Procedure Fetch harus ada blla 1ncl~2kuka.n fkrch terhadap rnztlrll~it~ row Scln111~ t u . periritah return juga dianjurkan untuk rncn~bcrikannilai O pada saat j;rcil scdruig bcrlangsung. dan kemudian membenkan nilai 1 sete1ahjt;rch sclcsai. Urituk mendapatkan semua output data yang dihasilkan oleh procedure, jun~lahoutput variable hams didcklarasikan sebanyak junllah item yang dlhasilkan.
Procedure Close Pada saat program pemanggd n~enjalartkanpcrintah d~sconnecratau penntah laln dl-c.ornplit' dengan cursor yang juga digunaka~luntuk mcngcksckus~suatu procedure, 1'ro~'t~~I~lrt' ('/u\c, drjalankan. Contoh procedure dengan ON PROC'EI)Lif?E Table sang digunakan oleh proccdurc bcr~hutadnlah GldES T' \L"T SQL> CREATE TABLE GUEST-W?' (NAME varchar(25), WEIGHT dcclnial(3,0), WHEN date), SQL> INSERT INTO GUEST-W?' values( I , 2, 3 ) -
\
JANE FYUDAI, 132, 13-OCT- 1992 DON JACKSON, 165, 11-OCT-1992 DON JACKSON, 159, 12-OCT-1992 I
E
I
Contoh berikut prepare, execute, dan fetch has11d a r ~procedure GULST-WTPROC SQL> PREPARE PROCEDURE GUEST-WTPROC' Parameters Strlng sName Rccc~veNumber nWc~ght Local Var~ables Sql Handle hSqlCur I S t r ~ n gsSelect Number nInd Act~ons On Procedure Startup Call SqlConnect (hSqCur 1)
Set sSelect='Select WEIGHT from GUEST-WT where NAME=:sName into :nWeightl Call SqlPrepare(hSq1Cur1, sSelect) On Procedure Execute 2 Call SqlExecute(hSq1Cur1) On Procedure Fetch 3 If NOT SqlFetchNext(hSq1Cur1, nlnd) Return 1 Else Return 0 On Procedure Close Call SqlDisconnect(hSq1Cur1);
SQL> perform GUEST-WTPROC \
JANE FYUDAI,, I SQL> FETCH 1; SQL> perform GUEST-WTPROC \
DON JACKSON,, 1
SQL> FETCH 2; SQL> SELECT * FROM GUEST-WT: Keterangan: 1. Bagian ini hanya diproses sekali pada saat EXECUTE pertanla kali oleh program pemanggil. Jika program pemanggil re-execute lagi procedure yang sama, perintahperintah dalam bagian ini tidak diproses lagi. Hal ini &an mcngurangi procedure performance overhead. 2. Bagian ini diproses setiap kali program per~~anggil menjalankan EXECUTE. Jikn tidak terdapat ON <procedure stares> dalanl procedure, procedure defi7ttlf ke bagian ini . 3. Bagian in1 diproses setiap kali program pemanggil menjalankan FETCH, dan sangat berperan dalam melakukanfefch tcrhadnp mltltiple row. 4. Bagian ini diproses hanya: Setelah procedure telah menyelcsarkan scrntla pcrnrosesan at;l~~ Perintah lain di-compile atau d~-es~clite dengan cursor yang sarila, atnu clrr.scjr tersebut di-disconnect. 5. Program pemanggil mcngeksckusi proccdure untuk pcrtana kalinya. Uclgrcrn 0 1 1 Procedure Startup dan On Procedlrrr Execute diproscs. Uscr harus nlernberikan placeholder comma untuk output rec
*ang berbeda. Hanya bagian On Procedure Execztte &proses. 8. Program pemanggil melakukan FETCH berikutnya, kali ini dengan nilai bind yang berbeda. Bagian On Procedztre /q't.lch diproses dun kali. 9. Bagian On Procedure Close diproscs. SQi!.du~e'l'ruirlirtg Muteriul Copyrrghr O 1993 by 1'7
I >rsadoro ,Sy~ct.~r~rndo Jukorro
Contoh procedure tanpa On Procedure Contoh berrkut ini melakukan comprle, execute dan fetch satu baris dan procedure yang h berada di bagian Action. default-nya ke On Procedure Execute untuk semua p e ~ t a yang SQL> PROCEDURE: GUEST-WTPROC Parameters Receive Number: nSumWeight Local Variables Sql Handle: hSqlCurl String: sSelect Number: nInd Actions Call SqlConnect (hSqCur I ) Sct sSelect='Select max(WE1GtiT) from GUEST-W?' into :nSumWcight' Call SqlPrepare(hSq1Cur 1, sSelect ) Call SqlExecute(hSqlCur 1) If NOT SqlFetchNext(hSq1Cur 1, nlnd) Return 1 Else Return 0 Call SqlDisconnect(hSq1Cur 1)
1 . Karena tidak ada bag~an On l'rocrdiire, scluruh procedure d1-deji7ztll ke bnglnn Ot7 Procedure Execute. 2. Tidak terdapat bagian On Procedurcj 1.i.tch dalani procedure mi. In1 berarti program pemanggil dapat FETCH dari On l'rocedlire Execute dengan memberkan perlntah SqlFetchNext. User hanya dapat J2tc.h d m retzirn ke program pemanggll satu bans saja (walaupunfetch berada dalam loop). Dalani kasus lnl, FETCH d a r ~prograni pernruiggll hanya mengembalikan (return) nilai rcccrvr yrrrorncter dan tidak melakukan proses lalnnya
Contoh dengan single row fetch dun multiple row result Contoh berikut menjalankan srngle row frtch dan niemanipulasi data terscbut untuk menghasilkaan multiple row result. Ini tnerupakan metode yang baik untuk nienghasilkan skenario 'bagairnana-jika'. Secara unlurn, semua jktch d a r i program pemanggil tidak hams memiliki sumber database dalam procedure. SQL> PROCEDURE: GUEST-WTPROC
Parameters String: sName Receive Number: nCurrentWT Receive Number: nDays Local Variables Sql Handle: hSqlCur 1 String: sSelect SQLUuse 'I'rrrinirlgMutrriul Copyrrghr O 1995. by PT Versacl~lraSysre~rrrndo,Jakarta
Number: nMaxWeight Number: nInd
On Procedure Startup Call SqlConnect (hSqCur I) Sct sSclcct='Sclcct max(WEIG1-IT) from GUEST-WT where NAME=:sName into :nMaxWeight' Call SqlPrepare(hSq1Cur I , sSclect) On Procedure Execute Call SqlExecute(hSqlCur 1) Call SqlFetchNext(hSQ1Cur 1, nlnd) Set nCurrentWT = nMaxWeight On Procedure Fetch
If nCurrentWT < 200 Set nCurrentWT = nCurrentWT Set nDays = nDays + 1 Return 0
+
10
Return 1 On Procedure Close Call SqlDisconnect(hSqlCur 1) DON JACKSON,,,
1. Karena bagian On Procedure Fetch ada, srngle row jivtch tidak dikcnlbalikan kc program pemanggil dan hanya digunakan secara internal oleh procedure untuk proses selanjutn>ra 2. Perintah ini mendaftarkan berat badan tarnu setiap hari (10 pound untuk peningkacan harian) sampai berat badan lebih dari 200 pound, mulai dari berat badan maksimum Dalam kasus ini, program pemanggil tidak secara langsungjttch dari database.
Perintah ini menghentlkan aliran kontrol d m mengemballkan pengontrolan kc program pemanggil. Pengecualiannya adalah bila l(c~trrndieksekusi dari When SqlError. Dalani situasl ini, kontrol dikembalikan ke procedure derigan boolean return (TRUEIFALSE) dnn menj;ld~ return value untuk SAL Sql Function >.ru~g gagal. Procedure akan nieneruskan pengeksekuslxi berdasarkan Boolean return. Jika perintah lZeturn tidak terdapat dalaii procedure, kontrol dikembalikan kc program 1. Jika terjad SQL error, procedure Iangsung mengembalikan error code yang d~has~lknn oleh SAL Jhnction. Pengecualiannyn ialah bila perintah Return dieksckusr d a r ~ Wiit~n
SQLBuse Truirring Muteriul Copyrrghr 0 1995, by P7' Versadafa Sys[enrrndo. Jokarro
Syntax: Return <expression>
Expression harus diberikan (mandatory) dan dapat berupa apa saja yang mengevaluasi suatu nilai: Jika Return terdapat dalam When SqlError, nilai yang dikembalkan hanya TRUE atau FALSE. Jika Return terdapat di luar When SqlError, nilai yang dikembalikan hanya integer. User dapat mengatur apakah nilai integer ini berupa constant atau variable, namun tidak dapat berupa string, &te/time atau Sql Handle local variable type. Contoh: On Procedure Startup When ~ q l ~ r r o r Set nRcd=SqlError(hSqlCur 1) If nRcd4O 1 Return FALSE Else Return TRUE
... On Procedure Fetch If NOT SqlFetchNext(hSq1Cur1, nInd) Return 1 Else Return 0
Perintah ini memberikan nilai ke variable. User juga dapat memberikan nilai dari satu variable ke variable lainnya. Syntax: Set VariableNarne
= Expression
Contoh berikut ini mendeklaraslkan dua variable untuk End-of-File dan Return Code: Local Variables Boolean: bEOF Number: nRCD
... Actions Set bEOF = FALSE Set nRCD = 0
SQLBase Training Matrrial Copyright (D 1995, by PT. Versadara Sysretnindo. Jakorra
Perintah ini digunakan pada saat debuggrng procedure untuk mengecek nilai vanable. User dapat menggunakan perintah ini sebelum dan sesudah suatu perintah tertentu yang dapat merubah nilai variable. Perintah ini berbeda dengan SET TRACE di SQLTalk yang mengecek semua perintah dalam procedure. Untuk menggunakan fasilitas trace ini, user tidak perlu menggunakan SET TRACE ON terlebih dahulu. Secara default, output dari fiingsi Trncc drlurirn ke Process Activify screen pada multi-user server, dan tidak ditampilkan pada singleuser engine. Biasanya user mengirunkan output-nya ke server dengan perintah SQLTalk SET TRACEFILE. Syntax: Trace variablel, variable2, ..., variableN
I
Contoh berikut menunjukkan suatu procedure yang menggunakan perintah trace untuk mengecek nilai'dari dua variable 'nCount' dan 'nRcd' pa& dua bagian yang berbeda dalarn procedure. SQL> PROCEDURE: P24 Local Variables Number: nCount Actions Trace nCount h p Set nCount = nCount + 1 Trace nCount If nCount > 10 Trace nCount Return 0
When SalError Perintah When SqlError mendeklarasikan local error handle Syntax: When SqlError <statement(s)> Contoh berikut ini menunjukkan penanganan local error dengan SqlError dengan menggunakan table GUEST-WT: SQL> CREATE TABLE GUEST-WT (NAME varchar(25), WEIGHT dec1mal(3,0), WHEN date); SQL> INSERT INTO GUEST-WT values ('DON JACKSON', 159, 12-OCT- 1992); SQL> COMMIT; Contoh berikutnya menggunakan stored command W EIG HT-Q UERY: SQL> STORE WEIGHT-QUERY SELECT WEIGHT from GUEST-WT where NAME=: 1;
SQLUase Traitring Material Copyrrghr 8 1995,by PT. Verrodora Sysren~lndo.Jakarlu
. r
S c l l E r ~ ri!;il;lm ~ o ~ p~~oLcclurc aka11 I ~ ~ L ~ I ~ ~ L ~ I I I ~ ~~. I\ ~~ I r~ ::.!07 o, , rI I ~
( ( ' o I ~ ~ I I ~ ; I I I ,I ~I , ~ I
,
'\i
,
,,,,
'1::1iI : .
'1,
I,,,:I!L
\,I:
L i ; l l l l l l c l l l l > L ~ r ~ l > ~ l rl lL1 l; l ~ : l I : ~ l l 1111
. l ' l < . \ Y l : \{ l , l ( ; t 1 I (11' K , ' I i l l ~ I < O ( ' l : l ~ l < l (;\\ P I < ( ) (
<()I 5()1
,
l);11 , 1 1 1 1 L ~ l ~\~ 1 \ I 1 Ill>,
.\Llll,L
l < , ~ L ~ L ~,,l \ ~ l l l l l l ~ , ~!!\i l Lli!til
l.oc.11 \ ' ; 1 1 1 , 1 1 1 i L ' \
icil I I , I I I C ! I C I I S ~ (11. ~ II ( \lllI1I~L.l
11111~1
\ l l l l l l \ ~ ~ ! 11t
:
' j
<~[l ,,, !I
~ , i \ I !
1
Ifl l ( . l l
bill l , , l . l
111
Si.[ l l l < C < ! ! 11. III
I t
'.ill
(
,II
I
U
l
l
l
t < ~ , t l ~ rI nli1
I
\I
I,,\.:<
,\ 1 1I
'\
.
iI
1
I ,Ii
3
1: t
,
.
\
t
\t . i!",s
I
~ L ~ I C ' O I I I I C C t~1( 5 < ! 1 (
) l l l ' l - , ~ L ~ L ~ ( j l l l - tL ~\ C C t l I C
I
) I ! 1'1 L : L I L I I L ,
!!I
,
,
;
11
,\\I,I;,
I
l
\
i
!
5 ~ ~ l I ~ \ i ~ i l l t c ( i 1 5 , i 1 i
:: \ ( ) I '
l~'L>l~ll bl]lf.
l Z c , l 1 1 1 11
~ , + l '
, ,
1
lictlll-11
1
\\ I
,
L
I \ ~ j l ~ < L * I II L , L C ~ I ~ . \ ~ ~ ' (
(
1
01 I 10
:I i
-
I
r
1 1 1 1 ' 1 , ~ ~ . c , d (~' I O~ \ Lr , c
,!I!
~ c j l l ~ l , ~ ! ~ l l l l L ~, .L\ ~ ! l
I ) ( 11 !-\(~Ksot',.~
I
:
111',!11
,111 ; > I . , ) , . L , ( . ~ ~ I I . L ~~ I I II
J c ~ , lIi t i l I / t 7
1"
2
"!.>I
-
l,,!:iL,k,l
I1L,l l l l ! . ~ ! l - ~ ~ L! ~: lI l ~ l I l ~ l . l l ~ 1 1 1 1
~ ~ C I I ~ C ~,!> C L ~ I \ )
> i ll \ll l ,-g!c~k~ld
,
f
,
11l~'-tc\
,t
<
'>( j;
[ i j ' ~
,.!.( >/
, t ~ ~~ !. , i ! ~ ~ l i , , r I 11L.i b ( ) l
g i , L i ) ~ t ~
',,!.:,
,I1
,i
J'I
O"."
.;):.,
.:\ \
J ,
CI1 0 1
, I , : , i k~ .,
I;ii~i
ill! :
,
P,II
:,
I
I l 1 l l l
f
. I l l
t
I
I I ! I I ! !.
~ l ~ ~ l l \ t~l L ,. \ il, \ l ~
'! ,'
' , '
I,,,
' s ~ ' / l L t \l I ~< ~ [ t ' / i t i j ,
, ,. .
,
'
I
,
,"
. .
: . :.
~ ~ L ~ I ~ ! ~, I !~; ~ I I I <~ [ ~ ! I ~, , : I ~ I
;Ill1 ~ ~ ~ l ~ l ~ l l I ~ l \
P C I . I I I I ~1I1 1~1 I c ~ k ; l~ : I~S ; I~I L~,~ f,;!l !, I I ~ L . I I I ; ~,SqI/it
.l!l
) I
!
I
1
,
; r
.. . , I - . ,
';\
!I
. :
Perintah ini berulang terus sampai expression yang dievaluasi menjadi FALSE Syntax: While Expressron
<stoternent(s) > Contoh: ...
While nlnputVar3 > 0 If NOT SqlExecute (hSqlCur 1 ) Return 201 Set nInputVar3 = nInputVar3 - 1
Operator-operator benkut dapat digunakrul dalam procedure. Operator Keterangan Tanda ku rung Unary 1 *,I Numeric: perkalian, pcmbag~an Numeric: pertambahan, pengurangan , +, >, <, >=, <= Relational: leblh besar, lcbih kecil, lebih besar sanla dengnn. dengan = I= Relational: sama dengnn, t~daksama dengan AND
1
I
-.
--
7 '
... .-
,-
-
:
-
' ; I
1
1
Boolean NOT Boolean AND Boolean OR
I
Stat~cvs Dynarnlc Procedure Suatu procedure dapat berupa statlc mnupun dynam~c Default-n,'a adalah Perbedaannya dltunjukkan oleh table berlhut In1 Fasllltas Perlu dl-store dulu sebelum di-execute') Parselprecompile procedural logic') Ya Parse SQL pada saat dl-store? Tld'ak Precompile SQL pada saat di-store') Mendukung djnamlc SQL? Leb~hLaubat D~gunakanbersama Trigger dan Event?
SQLBuse i'iuirrirrg Muterial Capyrrghl O 1995, by PI: Versadata Sysre~t~indo. Jukarru
dynm~~c
Ya Ya
Leb~hCepat
SQLBase melakukan compile clan oprlmrze (mcnentukan query plan) pcrlntah SQL Iang terdapat dalam statrc stored procedttre Scmua perintah d m query C X E C I ~ ~jll~zr~ ~ U ~ ~~ a n g d~hasllkand~slrnpandalam database Statlc procedure harus dl-srore sebeluin d~ekschusl Static procedure yang mengandung perlntah SQL harus menlenuh kritcrla bcrikut in1 Bukan perintah Data Definition bngzlrrge (DDL). Perintah CREATE, ALTER, DROP tidak dapat digunakan dalanl static procedure String d m tidak mengandung var~abledi luar bind atau INTO varlablc Contoh 1 SqlPrcpare (cur, 'select * from emplo).cc') Contoh 2: Select Coll, Co12 from sysadn~.TableI ~ n t o:Out 1, :Out2
Dynunzic procedure Dynamic procedure dapat rnengandur~gpcrintah SQL ).ang dinam~s Lnr.cn;l ci~nam~s, nlaka perlntah SQL tersebut tidak dapat d~-~)rec,urnp~le, Dynamic procedure t l d d pcrlu dl-srort~ sebelum di-execute. Karena pcrintah SQL, dalan~dynarn~cstorcd procedure t ~ d a kd~-j)crr.se, sebelum dieksekusi, SQLBase tidak dapat rncndeteksi SQL error dalam procedurc pada s u r procedure tersebut di-store. Contoh 1: Set sCmd = 'select * from employee' SqlPreparc (cur, sCmd) Contoh 2: Set sColumns = 'Coll, Co12' Set SELECT = 'Select 11 sColunuls // 'from sysadn~.Tablelinto :Out I , Out2'
Penggunaaian procedure Men) lmpm suatu procedure berartl rncn:, lmpm procedurc tersebut ke dalmi rq 5 rcrtr iiirciIoq rnble untuk dl-rerrieve dan dl-execi~rekcmudla11 Pada saat procedure d ~ b u a tdengan pcr~nt~lll PROCEDURE, user dapat menentukarl apnkah procedure tersebut srrrrrc ataii ~/~,riilrnrcPndn saat suatu procedure dls~rnpm(dl-srort.), SQLBasc juga mcnylmpiln L , L L ~ L I I ( I O ~ I~)ILIII ci,lr~ procedure tersebut User dapat mengunakan perintah SQLTalk STORE untuk menyirnpan suatu procedure Perintah ini digunakan pada saat proccdurc: dibuat dengan perintah PROCEDURE. Contoh: SQL> STORE WD-PROC PROCEDURE: WITHDRAW Parameters
1
3
i
Per~ntahPKOCEDURE secara otonlatIs mcng-cumnprle d m rneng-cxrc~rtcproccdurc. 0sc1JLI_C;I dapat nienggunakan perintah SQLTalk PREPARE atnu RETRIEVE bersama-snma Jcng:111 PERFORM untuk melakukan conrj~ilrt2.tt1i,llrelztau rrtrrrve.rxtJc1rte procedurc !';ulg ci11nglnk:\11 dalan dua langkah yang berbeda. Untuk mclcFkukan retrlrve d m execute dalaln satu langkall.
gunakan perintah EXECUTE. Perintah ini akan menerima nilai input d m mc-retrieve data sekaligus execute stored procedure ybs. Contoh. SQL> EXECUTE WD-PROC
Untuk menghapus suatu procedure dari database, gunakan perintah SQLTalk ERASE Contoh: SQL> ERASE W D-PROC; Untuk membenkan hak kepada user lam dalam menggunakan stored proccdurc, gunakaran GRANT EXECUTE Sedangkan untuk nlencabut kembali hak yang d ~ b e r ~ k a ngunakan , REVOKE EXECUTE
Fungsi SAL dalam SQLBase
1
Benkut ml daftar fungs~SQLWlndoibs irulg dapat d~gunakandalam procedure Fungs~SQLWlndows Keterangan i SqlClearInlrnediate Disconnect Sql Hiuidle vang digunakan olch Sqllnunediat~. ---Ii SqlClose Mengakhiri cursor SqlComm~t Commit trruls;&si SQL \.ang sedang berlangsun~ . . ... SqlConnect Connect Sql Handle ke database --. ..- -.-SqlDisconnect Disconnect Sql Haindle dari database ------ ~ - -1 SqlDropStoredCmd Menghapus stored command atau stored procedure 1 SqlError Mendapatkan error code paling f i r untuk Sql Handle tertentu -- .... I SqlExecute Execute perintall -SQL, stored command, atau stored procedure SqlExists Mengecek ap&h baris-baris tertentu ada dalanl databasc .__~. SqlFetchNext Fetch baris berikutnya dalam result set - ... SqlFetchPrevious Fetch baris sebelurnnya dalanl result set 1 I SqlFetchRow Fetch baris tertcntu dari result sct SqlGetErrorPosition Mendapatkan offsct dari suatu kcsalahan dalarn pcr~ntahSQL 1 I SqlGetErrorText Mendapatkan pesan kesalahari untuk suatu SQL error nurnbcr 1 SqlGetModifiedRows Mengembalikan nomor baris yang dirubah ole11 perlntah LNSEfI.1'. UPDATE, DELETE -* SqlGetParameter Mengernbaliknn parameter databasee . SqlGetParamctcrAll Mengembalikan paramctcr database -, I Mengemballkan nomor bans dalam result set SqlGetResultSetCount A SqlGetRollbackFlag Mengemballkan database rollback flag -- 1 SqlIrnmediate I Con~piledan csccutc pcrintah SQL -.- Mernbuat sunti1 cursor dan mengekschus~pcrintnh SQL Compile penntah SQL atau non-stored procedure untuk dlckschus~ SqlPrepareAndExecute Compile d m e\ccutc pcr~ntahSQL atau non-storcd proccdurc -. Retr~evestored command atau stored proccdurc I SqlSetIsolat~onLevel Set ~solatlonlcvcl SqlSetLockT~meout Set perlode tlmcout pada saat mcnunggu lock -1 Set parameter database - -,
I
-
--
I
~
- -
~~
-1
SQLBrue l'ruinirrg Muteriul Copyrrghr O 1995 by
Pf
J'ersuilufa Sysret~r~nclo Jukurfu
I SqlSetPararneterAll i SqlSetResultSet
!
SqlStore
Set parameter database ----Set result set modc rnenjadi ON atau OFF Compile dan storc suatu cot~unandatau procedut-c
~~~~,
-, -
-
> ..
T r i g e r mengaktifkan stored atau inlinc procedure yang dicksckusi SQLBilsc sccar;l otomatls pada saat user berusaha merubah data dalani table. User dapat rnembuat satu atau lebh trlggcr dalam suatu table dengan keperluan yang bcrbeda-beda. Trigger memungklnkan user untuk. 1 hlcng~mplementasikanRl consts:~~nt, scpcrt~r n c ~ ~ j ~ bahwn u i ~ ~ nnilat F K ~oic>L dc'lig;111I'h 2 Mcrintaigi user lain rncrubdi data sccara tidnli benar schlngga mct-usalika~l11ltc~r1t~ili database. 3 Mcngan~bilt~ndakanberdasarkarl 1111;11 \>;11-1s S C ~ C I L I I I I atau sctc1;Fh pcrilb;ih;in te~:l~lii~ 4. Memindahkan pemrosesan logic kc backcnd scliingga rnengurangl network traffic T r t g e r t ~ d a kdapat mcmangg~lnon-stored proccdurc Statlc procedure harus dls~nipandcngcm perintah STORE. Gunakan penntah CREATE TIUGGER untuk membuat t r i g e r dan pcr~nr,iti DROP TRIGGER untuk mcnghapusnya
a
Tr~ggeryang nienggunakan stored proccdu~chanya b ~ s adtbuat blla 1 Anda nieniiliki hak DBA a h u SYSADM 2 h d a tucrupakan pcm~lrkdart storcd procedure !b b 3 . Anda tclah diberikan hak EXECUTE untuk stored proccdure ybs
Tinier Evcnt l'irner even! mcngeksekus~suatu procedurc pada saat yang telah ditentukan. I'initlr digunaknn untuk mclakukan suaru tlnd;~ka~l sccar;~otomatls yrulg didasaskan ntas ir,aktu even! hanya dapat mengeksekus~satu static proccdurc
tJ~.t,rlc
7'ir~rc,t.
Untuk niembuat suatu timer even!, gunakan CREATE EVENT' Everit y.ang dibuclt dc~pai berupa prrrodic atau one-rime I ' e n o t i l ~ .even! dituryukkan dcngan EVERY clt-r~r.sl-,u n r u h melakukan suatu tindakan setiap tntcl-vul waktu tcrtcntu. One-rime even! d~~ad\v:llknnuntuh &if hanya satu kali saja. Untuk mcnghapus cvtwr, gunakan DROP EVEN]' Timer even! mulai dijadwalkan bila trans;As~l.ang mcmbuat evcnt terscbut tclali dl-coninlir lnilah yang disebut sebagai even! rnrrrnrron rrme. Waktu yang ditentukan supaya evcnt tcrscbur mulai beroperasi disebut raise time. Inrrrarron rime dari suatu timer even! trdah Aimberubrlli walaupun server di-shut down. hlisaln!,a bila mrse rrrrle ditentukari 10:00 a . m . nanlun transaksi in1 tidak di-commit sampai j~uii 11.00 a . m . . niaka SQLBase ,&at1 mer~ndwnikn~l rimer even! In1 mulai jam 1 1 :00.
User dapat niembuat frmer evcnr untuk suntu storcd atau lnl~ncproccdurl:
PROCEDUKE: PROC-UNLOAD static SQLUuse Iiuit~irrghluleriul Copyr~ghrO 1995, b), PT L'ersudutu <)~s,e~nrndo. Jukurra
Actions Call SqlIrnmediate ('unload database DOC.UNL on server') SQL> CREATE EVENT EVENT UNLOAD raise at 12:00:00 AM everq 1 DAYS (execute P R O C - W L O A D ~ ) ; SQL> COMMIT; Untuk menghapus tlmrr event dari sysrrm corolog, gunakan DROP EVEN?'
SQLBuse lkuining Muteriul Coyyrrghr
(P 199.5,
by P7' Ifersaduro Sysrert~rndo,Jakurru
1
VI. Database SQLBase memiliki sebuah database rcttrpltrle pig bernnma Starr.clh.s S c t ~ a pk a l ~k~t:l I I I C I ~ S create sebuah database baru, rnaka bcrarti kita telah lnendapatkan satu c o p d a r ~start dbs Template ini berisi system catcilog tables bang di-maintain oleh SQLBase. S e l a ~ nrtu, tcmplatc ini juga akan berisi semua perubahan struktur database, nlisalnya p c r u b d ~ mstruktiir darl sntu \,ersi SQLBase ke versi lainnya. Sernua database memilih extension .dbs
Pencnluatan Database dalanl Dircctor~. SQLBase rncrnbut~ihkansatu bottle L ~ L ~ I L I ~ L I . ) . Ldllrcc1or.11 J (blasan)a F.',.Sqlb;isc) L ~ I I ~si't~;q) LI~ non-j~arlitionrd d ~ ~ t a b a s eyang tcrdnpat dl survcr, SQLBasc akan rncnlb~~arscbuall subdirecton yang baru di b a w d homc datnbasc director). Nama dnrl subdrrcctor\ 1111 ~ 1 1 1 1 ; 1 dengan nama database (tentunya ranpa tilc cstcrltlon) Subd~rccton.1111 nk;i~i~ L ' I ~ S I 1 Database file (*.dbs) 2. Transact~onlog file (*.log) 3. Read-only h~storyfile (*.his)
' . 7
DEMO\
TEST\
G m b a r 6 1 Organisnsi d a r ~SQLBase databasc dircctory Database dapat ditempatkm di d r ~ v cd;in I I O I T I ~ database dircctory dcng~in r n c r ~ g g u i i a h ~ ~ ~ ~ keyword dbdir di file sql.ini. Misaln~.abrln kita ing~nmenempatkan database c i ~\.olurnc SL'S dan di bawah home database directory SQLBasc dalanl scbuah network. mnka k ~ t alia~l!;~pcr l i i mengetik baris b e r i h ~ tini di file syl. 1t7, dbdir=sys :\sqlbase
database MAIN. Databasc ini pada g~l~runnya ,&an dnrobase ybs. d a i Lug jile-nya.
menunjukkm lokas~dari j)trr~~r/otlc'ri
Database Cache
S Q l ~ ~ Trnittitrg ~ s c hloteriul Copyrighi 65 199.5. by 1'7' I OrsLrr(ata.S),sre~~linJo. Jakorro
SQLBase menggunakan cache untuk niengoptlmas~kan database Input d m output crrcht, adalah suatu area di memory server yang mengandung pages yang sedang d~gunakanuscr i~ntukkeperluan read / wnte
Pages dalam database file Database cache dalarn memory
Gambar 6.2 Database page dalarn cache
I'clge adalali sebuah unit data d a r ~darnbasc file yang merigandung satu / Icbrh b a r ~ sd n r ~tnbli. atau data Index. Page adalah unit yang paling niendasar dan penyirnpa~ianfisik d a r ~datab:lsc. Page dis~mpanseperti linked list clan 1 page berukuran 1024 bytes ( 1 Kb!.te). Pnda saar ustr read / write satu baris row / index, SQLBase mengccek apakah pagc yang dituju tclah bcrniia di cache. Jika tidak, SQLBase &an meng-copy-nya kc cache. Tetapi jika page tclah tcrdnpilt dl cache, SQLBase dapat langsung nl~rnanf~mtkanny a . Hal ini akan nlengurarlgl disk ~ n p tu d m output karena lebih cepat bila langsung rcxi 1 write ke memory komputcr clarlpadn kc hnrcidisl,
Transact~onLog Files
Trunsacrion log jles mengandung kondisikeadm~database sebclu nl dan sctelah pcru bahnn terjadi serta log record untuk [ronsclcrlon conrrol (misalnya checkpu~nt.~). lr~'msoc;.l,ot~ ~~)/rlt.o/ meliput~dokumentasi kapan suatu transaksi dimulai dan bagaimana tra~isaks~ tersebut bcrnklur (contohnya commit dan rollback). Tran.snc~lonlog memiliki tiga fungs~ 1 Rollback Transaction log file rnengandung data gang dibutuhkan untuk mclnkukan rollback ~lras sebuah transaksi. Rollback ~ n dapilt i diprakarsai baik oleh SQLBase rnaupun uscr 2 . Crash Recovery Transaction log file rnengandung data yalg d~butuhkanuntuk mcngembal~kruidutabasc kc kondisi yang stabil setelah terjadinj~acrush yang diakibatkan olch regalgati l ~ s t r ~Innupun h kesalahan operator. Database berada dalarn kondisi yang tidak stabil / crash b ~ l adatabasc tcrsebut tidak di shut donmn scsuai dengan prosedur yang telah ditetapkan, contohn!,n scperti bila sewer dimatikm tanpa menonaktifkan SQLBase tcrlebih dahulu Pada s ~ 1 r
3
uscr connect ke database ynng crnsh setelah servcr on lagi, SQLBasc aka11 secara otonl;it~s melakukan crnsh recovery. Media Recovery Transaction log yang disirnpan dalam bcntuk backup mcngandutig data y.arig dibutuhAn~l untuk melakukan restore jika terjadi kerusakan database yang dlsebabkm ole11 kcgagalari media (rnedin.foilure). Kegagalai ~ncdiadapat terjadi karena ksrusakan hardlvare Dnlatii kcadaarl seperti ini, recovery yang Icngkap han1.a dapat dilakukan n ~ e l n l urcstorc ~ stat~s dari backup operating system
Transaction log menjamin konsistcns~data (dam ~ ~ u n s i s t e n qJlka ) , suati~tra~isajis~ ro//hL~i,k atau tcrjadi kegagalan sisterdmedia, SQLBase mengunakan transact~onlog u~itukniclakukan restore dan mengcmbalikan database terscbut ke keadaan scmula.
Database tile berada dalam kondisi semula yangstabil
'
'.I0
,
!\
-.
- - -'
5.10
i -. . -. - -
.!
.-~.--
Setiap transaction log file rnenggambarkan perubahan yang terjad terhadap database setiap waktu
Garnbar 6 3 Database filc tirui Trn~isact~on Log filc
Pada saat proses, SQLBase mcmbunt bcbcrapa jcn~s~crnj~urar~. file 1 Son file Meligandung hasil akhlr dart .\.or[ y n g diakibatkan oleli DISTI'NC'I', ORDER R 1 . GROUP BY, atau CREATE I N D E X SQLBasc akan nicnlbunt satu or-r.tilt' untuh [lap jmis pcngurutan. 2. Read-only history file Mengandung data page sebclum perubahan tcrjadi. SQLBnsc membuar sat11 rc,crti-ot~r: hislory file untuk tiap database. I J r ~ t r lj~c~gccadalah ternpat untuk tiicn? 1111pru1b;lr~s-bn~ I.; data dalam scbuah table. 3 General-use filc Mcngnndung resulf ser, lenlporor?, ruble. ~crripornry ~nciex nng d~gunaknn dula111
,
Setiap temporary file yang terbentuk akarl berstniktur sql.~xss,lrnp dimana s.v.v.v ndalah nomur scri yang dibenkan secara acak (mnduni)
SQLBast. lrciining R.l(lterial Copyr~ghrC? 1995, by i'7'
l irsiiJci~uS'ysfe)rlindo,Ji~h[lrrri
Database yang berukuran besar daprtt disunpan dalam bcberapa volumi: d ~ s h untuh mempercepat kinerja. Dengan dcmikian, niaka ukuran database hanya dibntas~olch bcsarn!,:~ disk yang tersedia di server. Database d m log file dapat disi~iip~ul dl cliiriihri.~~' iit.cJci. >.a~tu penyimpanan fisik yang memungk~nkan disk input 1 output yang paralel j.4~11 1111 J I I ~ L I menlungkinkan user rnclahukan log hocklip sccara bersaniaan 1)crtohosr crrerr adalah bag~andlsk yang d~Mi~~suskan urituk nicn>,lrnpan suatu dnt-:111g ~nenirlikrukuran yang berbcda-bcda Ucsanlya d ~ s kdalam satu databasc ar~ciidlbagl n~ctijaili bcberapa bag~an untuk menj,miprui bcbcrapa database Hal irll d ~ s e b uscbagal ~ csrcnti I:luren.~ion unit adalah jurnlah ukurarl disk dalarn database area >,any dialokrts~knn untuh nienyrmpan satu database dan log file-n!,ri Database l ~ l e s
F I-.
I
D a t a b a s e log f ~ l e s
L 1
-
-
--
6
~ D S LI O G
!
-
-
hIDS2 L O G
Y-'
,__-'
-.,'
I 1
i I LOG !
LOG
~
I
4--
I
Database a r e a l
Database area2
Database area3
Gambar 6 4 Database dan log file lang tersebar dalarn t ~ g adatabdsi' rtrc,l I'ada Garnbar 2.4, kita nlel~liatdua darnhasc !.,u~g bcrnama V D S l dnn VDS3 set-t:~10,q tiit yang terscbar di tiga database ilrro. Hal In1 dapat diwujudkan dcny:ln ~llcmbuat( / l ~ ( l : i ~ ( i ~ l areo, nienggolongkannya ke storogc grolip dan ~nentbuat database dnlam ,slorti,yc' gru~ill tersebut. 90rngt. gruup adalah dafhr d a r ~ ciLitiiho~c 'rrerr yang digunakm u ~ l t u h r ~ r ~ ~ r i ~ q o r . , q , i t ~ i . \ ; t . ptrrr~tiur~cd d~llab~7se. Konsep rnl sama scpcrtl rleclror~icmail grolip Lnng bcrrs~dattnr Liscr yang menjadi ariggota dari rlectronic. ni~111 tersebut. Dengan nicnggunakan storugc gr011/1. database dapat d i s h p a n dalam volurnc disk yang berbeda d a i mcrnungklnkan database menangani pengalokasian ternpat penyirllpan~ulsccara oromatis. I
Storage Group
I
I I
-
--. .
I
Database area
--
Database area .- -
-
Database drea .
--- -
---
Ganibar 6 5 Tlgn database: arc3 mcmbcntuk saru storage group
MAIN database
-
SQLBasc akan mcmbuat MAIN database jika. 1. P~7rtrtioneddatabase dibuat 2 . Mcngunakan keyword commltserver di file sql.ini untuk kzperlum r/~.c(r~h~i(cii transaction MAlN database berisi inform as^: 1. N a n a partitioned database 2 Nama database area . rxtcnl da11 ukurunn>a 3 N a n a storage group 3 Pihak yang terlibat dalarn u'lstr1butc.d trcinsuctrot7 5 . Oyerasi truo-phase comnllt MAlN database menuldu luy\tt.m
L L I I L I / ~itlhles ~
tcrscnd~ri untuh pLirt~iioti~d c l i / l i / i ~ t ~ ~\ 1~ ~ 1 1 1
d r trlbzdrcd ~ transncr~on
Nama table CSVPARTS
Keterangan Mengandung informasi tentang 'semua pihak yang terlibat dalnm distributed transaction Digunakan olch commitserver dalarn distributed tr'msactlon ur~ti~k menanganl t\+.o-pllnsecornrrlit . Berisi daflar semua default storage group ... Berisi daftar scmua partitioned database -Berisi d a h r storage group Berisi d a h r database area Berisi d a h r database area dan storage group-nya -~ Berisi d a h r extent yang sedang dipakai --. .- .Berisi daftar extent yang belum dpaka~ -. Digunakan oleh kebutuhan internal saja -. .. .. Digunakan oleh kebutuhan internal saja --
CSWRANS DEFAULTS DATABASES STOGROUPS W A S STOAREAS EXTENTS FREEXTS CSWRANSV CSVPARTSV
-
--
~
-
p~
-
Server status yang ditarnpilkan pada gambar di atas adalah untuk server VDS. Hal ini d ~ a t u dl r file sql. ini dengan men~etikkan:sewername VDS,sqlspx. Client Node Menampilkan client yang connect ke database server. Nama client diartlbil d a r ~file sql, ini yaitu di bagian keyword clientnm~e. User Naine Nama user database yang connect ke database ybs. Database Nanla database yang sedang diakses Menyatakan apakah prosesnya lagi Active atau Idle.
-
Bagla11 ini ber~sidaftar dari selnua database p i g teldl dl-install sebeluninya. lnfonllas~ tentang database ini juga terdapat di file sql. in1 yaitu di keyword dbnamr
FUNCTlOr< LEY S F1 - S e m r Slatus
F3 - System A c t ~ r y PROCESS ACTIVITY
Dabtasm prre~s crsaed -0 c u - o MWO c f r o fco= I lcurivrtl datatascf-[PLNNV] u w r e n m = [ J W w r j L W r Q a r t s o f W blfdbE-3 P L N W
Ciarnbar 7.2 P c n a m p i l a ~I'ruccss ~ A c l ~ \ . i l yJar1 .'iQI,ljils~Scrvcr fur Net\\;lrr
SQIJIase Training Muterial Copyrrghr 8 1995, by Pii, I/ersorlufu Sy:ystettr~nrlo. Jukurfu
SQLLJuse Training i2.1utericll Copyrrghr
B 199.5, by PT l'rrs~~doroSysrs~nrndo.J u k r ~ r r u
I
System Activity display screen (F31
SQLBASE SERVER N M FOR bETWARE SYSTEMS 6 0 COPYRIGHT (C) GUPTA CORPORATION 1985 - 1994
11 45
'
1
ALL RIGHTS RESERVED I
FUNCTION KEYS
-
F1 Semr Status F3 - System A c l ~ r y
F2 - Process Aclwrty ESC - Ex11Sewer
SYSTEM ACTIVITY Sysarndab
L Gambar 7.3 I-'enarnpllan Systcni A c t ~ ~ i Jar1 t ) SQ1,Uasc
/' Scrver
for N e l \ r , i r r
Baritier
Banner / judul lnyar menampilkan nmla dan versl d a r ~produk, dalam gambar dl atas 1;11nh SQLBase Server for Netware, versi 6.0 F ~ t n c r ~ oKeys n
Ada empnt pilihan yang disediakan di bagian Inl: F1 untuk melilint Sewer S't~itlr.~,F2 ~ ~ r i t ~ l l i Proce.~.~ A c t i v r ~ , F3 , untuk Sysrem Acrlvrry dan ESC urltuk menonakti£kar~SQLUasc Scrkcr Syslem A c t i v r y
Menampilkan mforn~asiaktivitas internal scnjcr Systeni Ac~ivl
SQLBase Training M(ltericr1 Copyright O 1995. by PT 1 irsudoru Sysre~il~ndo. JLI~U~ILI
B a ~ ~ n~ nn menampilkan i informasi server Field Jumlah memorv vang tersedia setelah memory dialokasikan kc L,LIL,IIL, mc.monSavarlable cur Jumlah slot czcrsor yang dialokasikan sejak server dijalarlkan db Jumlah database vang aktif sejak server dljalankan. .Jumlah maksimum user yang diijinkan server. Pr Hal ini dapat datur di file sql.inr yaitu dengan keyword ilsers..... - . .....- - . Jurnlah cache page di server. PS
-
..
--
Ili
Ifv
1
berart~tldak ada batasan jurrrl:h lock Jumlah resource sistetn bang dipaka1 N l l a ~0 berart~rcauilrce s ~ s t z ~t ~nd a kd ~ p a k a ~
--
..
-.-.
Proccss ~nfonnation Mengandung infonnasi dari setiap proses Jan2 tcrjiid~ Kctcrangm Nomor proses prim
-
Nomor transaksi Mcssage yang mengindikas~kanl)ro~,t'.s.s L ~ c , / I \ ~ I / > ' srperu waitingjbr rrquesr performing re quta.sr sending respunsc. handling error wait for 10 lo~,k
--- - . .
l<er~~rn code dari requesr tcrakhlr
Single thread system 1oc.k
-
-
-- - -- - -
I = lncrelllerlt
..
..
.
. . .
BagIan
database yang sedan$ diproscs di servcr Keterangan Narna database File descrrptor Page terakhir cant;d~alokas~km Am..- .. . ....... . Page terakhlr yang d~tulis(ukuran file databasc dalrun page ) Jumlah transaksl yang rlktif .. - -... -.. Jumlah cttrsor Fang cotitltl~,i kc dnlabase ybs - -. -.. . .. Log file descrrpror -- -...Nornor cztrrcnt log lilt -Ukuran logfile dalani k~lob>zcs -.....-Checkpo~niirtne 1nlcrvi11ciaIan nicn~t -.2.---..-.p
1111 ~ ~ i e ~ l g a n dlnformasi u~ig
-----
-
-
-
-- -
.
pLu
- --
~~
- -
-
Flu
.
- -- -
-- --
.
-
~~
-- . . -
- - -
LoKgbenkut y ~ h&s ~ gdi-backup Log berikut yang hnrus di-backup (setelah BACKUP SNAPSHOT) -. .... .. Transaction span lirrrrt (nilal 0 berarti tidak adn batasan) -.- .. ..Log polnter ~
--
-
cpp, cpl. ftp, fap, fbl, Ibl
Contoh salah satu bar~s!tang terdapat pacia bagla11Oc~tnhcr.\,c.rti1 LS'.stern A~,rr\,ri~. 1 S004:006:007 nomor transaksi qang rncmlllk~ l o c k cjt7rrv atau nornor prox.;
004 006 007
.Shared lock Yoge group yang n~enilllk~ lock Siorfrng page number L n d ~ n gpage number
SQLUase Training bfutericll Copyright O 1995, by PT Versudortl Sysletti~ndo.J a k u r ~ u
u ~ l r u h )/)c'c 1 t 1 1
- -
VIII. Tanggung Jawab DBA Operasl-operas; yang menjadi tanggung-jawab DBA ialah: 1 . Memulai dan mengakhiri SQLBase Server for NetWare 2. Membuat dan menghapus database 3 Drinsfall clan reinstall database 4. Load dan unload database 5 Membuat read-only database 6. Membuat dan mengatur par~ir~onrd d(rrcrbase 7 . Reorgun~zedatabase 8. Mengecek database inrrgntjt
Mcmulai dan Mengakhiri SQLBasc Server for NetWare Untuk memulai SQLBase, ada tiga file yang harus tersedia: 1 error.sq1 2. message. sql 3 sql.in1 SQLBosr Server f i r Netware adalah NerWcrrt, Loadablr Modtrlr (NLM) >,ulg rtrnr~rng bersana operating system Novel1 NetWare NLM adalah program yang dapat dl-Io~rd'itnu dl-unload dari memory server pada saat servcr beropcrasi Pada saat dl-load, NLM menjadl bagian dan operat~ngsystem NetWarc: Pada saat dl-unload, NLM n~ernbebuskan memory dan resource yang dlalokaslkan scbelurnnya
Langkah-langkah untuk food SQLBase Server for NetWare: 1 . Pastikan file clib.nlm dan mofhlib.nlm telah dl-food sebclurml?.a. Kcd~rn filc n~engandungCj'irnction yang dibutuhkm oleh SQLBasc Server load clib load mathlib 2. Jika nlenggunakan SQLBase Server for NetWare 3.x. load directfs load dfs load nlm:\sqlbase\dll load nlm:\sqlbase\spxdll load nlm:\sqlbase\dbnservr SQLBase Server for NetWare 4 , s . load dfs load nlm:\sqlbase\dll load nlnl:\sqlbase\spxdll4~ load nlm:\sqlbase\dbnwsrv Langkah-langkah untuk unload SQLBasc Ser\.er for NetWare: 1. Tekanlah key Esc untuk unload SQLBase NLM 2. Jika menggunakan SQLBase Server for NetWare 3 . x :
SQLBase Trrrining Mrrterirrl Copyrrght O 1995, bv P7: I'ersadato Sysrenllndo. Jakarta
unload spxdll unload dl1 unload dfs unload d~rcctfs SQLBase Server for NetWare 4 u unload spxdll40 unload dl1 unload dfs
Membuat dan Menghapus Database Berlkut In] merupakan contoh bagalmru~amcntbuat d'itabase dengan SQLTaIL SQL> SET SERVER VDSIVERSA; SQL> SHOW DATABASES ON SERVER VDS;
***** DATAEiASES ON SERVER VDS DEMO
*****
SQL> CREATE DATABASE POWERCOM; DATABASE CREATED
SQL> SHOW DATABASES ON SERVER VDS;
***** DATABASES ON SERVER VDS DEMO POWERCOM
*****
Bcrlkut In1 merupakan contoh bagairnana ntcnghapus database dengal SQLT,llA SQL> SET SERVER VDSIVERSA; SQL> SHOW DATABASES ON SERVER VDS;
***** DATABASES ON SERVER VDS POWERCOM
*****
SQL> SHOW CONNECT;
***** Username DBA Database DEMO Cursor number 1
*****
SQL> DROP DATABASE POWERCOM; SQL> SHOW DATABASES ON SERVER VDS;
SQLBase Trninitzg Mnterinl Copyright 6 1995 by PT Versudora Sysrervtndo Jukurru
***** DATABASES ON SERVER: VDS DEMO
*****
Deinstall dan Reinstall Database Database d-deinsrall untuk mencegah user connect ke database ybs. Berkut ini merupakan contoh bagaimana melakukan delnstnll database dengan SQLTalk SQL> SET SERVER VDSIVERSA; SQL> SHOW DATABASES ON SERVER VDS;
***** DATABASES ON SERVER: VDS DEMO POWERCOM
*****
SQL> SHOW CONNECT;
***** Username DBA Database: DEMO Cursor number 1
*****
SQL> DEINSTALL DATABASE POWERCOM; SQL> SHOW DATABASES ON SERVER VDS;
***** DATABASES ON SERVER VDS DEMO
*****
Benkut In1 merupakan contoh bagalmana melakukari rernsrall database dcngan SQLTaIA SQL> SET SERVER VDSIVERSA; SQL> SHOW DATABASES ON SERVER VDS;
***** DATABASE ON SERVER. VDS
***** SQL> SHOW CONNECT;
***** Username DBA Database DEMO Cursor number 1
*****
SQLBtzse Trtlining M(lteritzl Copyrrghf cD 1995, by PT. Versadata Syrtsrn~ndo.Jakarrrr
v -
-
SQL> INSTALL DATABASE POWERCOM; SQL> SHOW DATABASES ON SERVER VDS;
***** DATABASES ON SERVER: VDS DEMO POWERCOM
*****
Load dan Unload data bas^ Unlood dclrcl berarti rncmindahkan infornlasi dan perintah DDL (Ualcr I&finrlrun Language) dari database ke file lain. Ser~luairlformasr yang tclah dipindalkan dalani file lni kernudian dapat di-load lagi ke database yang baru. SQLBase menggunaknn scrvcr untuk rnelakukan proses load dan unload. Hal ini a k a n~engurangilalu-linras nct~vork karena server dapat rnelakukan ha1 secara lokal tanpa perlu melibatkan client. Selaln ~ t u . perintah LOAD maupun UNLOAD rnenyediakan fasilitas data.kornpresi untuk menluiganr data yang berskala besar. Operasi load dapat rnelakukan restore dari backup file (yaig dihasilkan dari operasi unload sebelurnnya), dm juga dapat rnernasukkan data ke dalam database dari file-file yang rnerniliki fonnnt SQL, ASCII, DIF Operasi ini juga memiliki fasilitas segnlcrltasi yang tnernungkinkan DBA untuk rnclakukan perirltah LOADNNLOAD database yang besarnya lebih dari satu volume disk. Pada s a t menggunakan segmentasi, DBA dapat menentukan bcrapa byte informasr yang ditetapkan untuk tiap jile segment dan di rnana letak tiap segment terscbut. Untuk rricrigatur file segment, SQLBase menggunakan conrrol file yang bcrisi informasi. 1 Prefiks narna file segment 2. Directory tujuan di rnana file segment terlctak 3. Ukuran maksirnum dari tiap file segment (untuk operasi UNLOAD saja) Ada dua tipe c o n t r o l j l e yaitu Unload conrroljile dan Load conrroljile Untuk menlbuat conrrol jile, gunakan salah satu editor yang tcrsedia di sistem. Conrrolfile harus berada dalam format ASCII dan mengandung infornlasi berikut in^: FILEPREFIX SIZE <masiiiium size of the unload segnlcnt file 111 DIR <destination dir> megabytes> SIZE <maximum size of the unload segment file In DIR <destination dir> megabytes> Catatan. parameter SIZE hanya digunakari urituk unload con~rolfilt.,dan t1dA d~gurlnknn di lood conrrolfile Contoh lrnlond controlfile: FILEPREFIX dbs DIR c:\unldir\ SIZE I00 DIR d:\unldir\ SIZE 50 DIR e:\unldir\ SIZE 200
SQLllase Training Material Copyrrght @ 1995, by PT ~epsadoloSyr~ernmdo,Jakarro
Pada saat operasi UNLOAD berlansung, conrrol file mengarahkan SQLBase untuh ~nelakukantlnlond informasi database dalam urutan bcrikut: 1 . Informasi sebesar 100 megabytes ke file yang bemanla c:\unldir\dbs.1 2 . Informasi sebesar 50 megabytes ke file vang bernama d:\unldlr\dbs.2 3 Informasi sebesar 200 rncgabytes ke file yang b e r n m a e:\unldir\dbs 3 Selain itu, SQLBase juga membuat scbuah load canrrol file yang berna~nacihs JLJ' pncia directory yang sama tempat unload conrrolfilr disimpan. Lihat contoti di baivah In1 FILEPREFIX dbs DIR c:\unldir\ DIR d:\unldir\ DIR e:\unldir\ Contoh-contoh perlntah LOAD 1 Load external file dalam format DIF j x i g terletak dl cl~entke table EMP SQL> LOAD DIF emp d ~ EMP, f 2 Load external file dalam format SQL yang terlctak dl scrver dcngan ~ n c n g g i ~ r ~ ~ ~ h ~ i ~ l control file dan log message ke message log file yang terletak dl server SQL> LOAD SQL CONTROL dbs Icf ON SERVER LOG db log, 3 Restart load pada bans 101 dengan menggunakan control file dan log nlcssagc hc message log file SOL> LOAD SQL CONTROL dbs Icf ON SERVER LOG db log STAR I AT 10 I . Contoh-contoh perintah UNLOAD: 1 Unload table EMP dan DEPT (hanya data) dalarn format SQL. SQL> UNLOAD DATA SQL pcrsonnel.sq1 EMF DEPT; 2. Unload seluruh database di server dengan nienggunc&an control file yang j ~ g atcrdnpat di scrver: SQL> UNLOAD DATABASE CONTROL contrll .fil ON SERVER, Urltiik niempcrbaiki kine j a dan mcnghemat media pcnympanan, lakukrin pcrintah bzr~kut pada salt rnenjalankan LOAD: set recovery off; lock database; load [sql 1 ascii I difl exrerr~nlfilron scwcr; conmiit; unlock database; set recovery on;
l<end-onlydatabase dlbuat untuk berbaga~tujuan sepertl 1 Menjamin database hanya mengalrun1 pcrubah'm pada waktu j a i g teldi dltcntuhan 2 Menjamin bahwa di seluruh organisas1 hanya terdapat satu database pusat yang dapat drrubah dan rnend~stribusikancopy d a r ~database terscbut (dalam bcntuk ~ C ' L I C J - ~)I kc: I~ seniua cabang
Untuk membuat read-only database SQL> SET READONLYDATABASE ON, SQLBcrse Training Mrrterial I
Copyrrghr
cW 1995, b y 1'7'
C'erstrdofo S}~sren~rndo. Jukurfu
Untuk mengembalikan read-only database kc keadaan normal: SQL> S E T READONLYDATABASE OFF;
hlernbuat dan Menaatur Partitioned ,!)atabase Langkah 1 : Membuat Database Area Langkah pertama dalam rrienibuat pnrt~t~oned databa.vr adr~lah mrmbuat sebuah database area. Misalkan bila DBA ingin membuat ~kitnhcrst~ tzrco VDSl dalam directory :sqlbnse pada volume VOLl dcngan ukurnn 5 megabyte, maka perintahnya adalah: SQL> CREATE DBAKEA VDS 1 AS VOLl :\SQLBASE\VDS I SlZE 5 . Langkah 2 Membuat Storage Group Langkah benkutnya adalah niernbuat sebuah storagr grozrp (STOGROIJP) Storage group sebcnarnya berls~daftar darl dat~~base nren M ~ c a l n l ab ~ l a DBA lngln membuat sebuah storage gr021p yang bernarna VDSFILES untuh database area VDS I dan VDS2, maka per~ntahnyaadalah SQL> CREATE STOGROUP VDSFILES USING VDS I . VDS2. Langkah 3: Membuat Database Langkah terakhlr adalah mcrnbuat database dalan~storclgr grollp. Sclalri 1t11, DBA juga dapat menempatkan log Jle dalarn storagr group !.ang bcrbcd;~. Sebagai contoh, seorang DBA ingin membuat database VDS dnlam .cronr CREATE DATABASE VDS IN VDSFILES LOG -1'0VDS LOG. DBA harus rnengupayakan supaya database dan log jilt-nva d~tcnlpathan d a l m volume disk yang berbeda. DBA juga haws berusaha n~cncnlpathan sebuah database dalarn beberapa volume disk.
SQLBase Training Mtrterinl Copyr~ghr8 1995, bj' PT l'ersodatu Sysre~ti~ndo. Jokrrrta
i
Langkah 1: Membuat database area
i
SQL> CREATE DBAREA VDSl AS VOLl :\SQLBASE\VDSI SlZE 5: SQL> CREATE DBAREA VDS2 AS VOLl :\SQLBASE\SPA2 SlZE 10; SQL> CREATE DBAREA VDS3 AS VOL2:\SQLBASE\SPA3 SlZE 10;
Langkah 2: Membuat storage group
1I
Storage Group VDSFILE
Storage Group VDSLOG
SQL> CREATE STOGROUP VDSF ILES USING VDSI. VDS2; SQL> CREATE STOGROUP VDSLOG USING VDS3;
I
! I
I
Langkah 3: Membuat database VDS database
Storage Group VDSFILE
I
I
I I
Storage Group VDSLOG
SQL> CREATE DATABASE VDS IN VDSFILES LOG TO VDSLOG;
~
Ganlbar 8.1 Langkah-langkali untuk mc~nbuatp r l r ~ l o n e dd l ~ ~ t r h l c ~ c
Sctelah yartrtioned darnbase selesai djbuat, nlnka u'ar~rbnseoren dar~ tot-(lgc grojlp ~ a r i g ada hams di-mainmin dengan baik. Bcrikut ini nierupakan operasi-operas1 ! m g b~nsii dililkukan untuk mcngatur database area dan storngr group.
Oorclbose Area
Merubah ukuran &ri database area DBA dapat menambah atau mengurangi ukuran dari database arecr Jang tclali ada Misalnya bila ingin menambah ukuran databnse area VDS 1 rne~i~adi h rnegab!.tc. rnaka gunakan perintah berikut in^. SQL> ALTER DBAREA SPA1 SIZE 6. Mennmbah atau menghapus drrohnsr L I ~ C dun ~ O . \ . I ~ ~ L I , ,gr011/) ~c' DBA dapat nienambah utau nic~~ghapus d(~1l7hi1.s~' urea kc atni~ciar1 .\iorii)jc', C I . ~ J ! ~ ! J hlisalnya bila mgln ~ncnarnbahknli ~/ll~tlhe~.sc~ ilrec~VDS? kc dnlnl11 ~.rot.civcv t . ~ : ~ ! ~ VDSf:ILES. kctlkkan SQL> ALTER S l ' O ( ~ l i O l ~ \'l)SFII.LS l' .A[)U VDSi, Scdungkan unruk mcnghnpi~st i c i ~ ~ i n i/rct/ ~ r \ . ~V~D S ? d a r ~.~iorci:qc, C r . o l i i 1 \ 1 ) , 5 t ~ I l . t ' 4 ; lahukan SQL--. AL7'I;K S'I'0C;KOI.f) \'L)SI~II,I':S L>li(.)t'VDS.?, Mcnghapus dorcrbu.sc iirtlo DBA dapat rncnghapus ciu~c~hn.\c' r / t . c J ~ /jikn t~dak :lda dntnb;~sc' /O,~Z -I!C \ . I I I ~ mcnggunakan area tcrscbut darl b ~ l nl / i ~ r ~ ~ h irrcJl/ o . s t ~tcrsebut t~daks c i i n ~ ~bcr:lda s iiainl~l sebuah .r~nmgegroup. Misalnjx bila ingln rncnghapus d~1rl1hll.i.cl/rcJn\'US I. l;ikuk,lrl SQL> DROP DBAREA VDS I .
Mcn!.cd~akan sebuali dcfci/i//.\ rorr~,qcgrorlp Sctelnh .sroroge grolrp d~buat. S()l,Basc akan mcnganggal? S ~ I I I ~ cI ~; ;~I [ ; I ~ J ; ~\ s. ~I I I ~ dibunt berikutnya sebaga~p o r / ~ r / o t ~ cdir/(7h11si~ t/ DBA dapat 1ncng;ltul- :;al;~li . \ : I ~ L I . \ . ~ o r o ,Lqrozc/~ q ~ j.mg tcrsed~ascbaga~r/t;ri/~l/r.\rorc-,,qcg r o ~ i lb~I ~ s n l l - ~u~1 i.~ai 1 1I\ ~ I L ~ I ~ ~ ~ I I , V D S FILES sebagai ~/c~,fi~ri// .v/or.cri,~, yrori/,. _ S I I I I ; ~ ~ ; Ll~cr~n[;ll~ II ~)L'I.I~\LI~ S(]L,j SET DEFAULT STOC;KOL.I1' VDSI:Il.ES. Contoh dl atas ~nengasums~kall bali\\.a I>BA tclnh mcrnbuat ~ l c ~ l ~ ~ hL tI ~~ C. .s' c~\ ., l ) 5 I l!.lll .\/orcrgt. ,grotrp VDSFILES J ~ k anama . ~ / l ~ r r /grolip , q ~ ~ d~aba~karl. l i ~ ' t c ~ ~ i / / ;-lnl \ \a ;~~i dikembalikan ke null. Hal In1 ak;111 rric~~gak~batkiin SQLBasc kcmb;ll~ ~nti~clnpatA.~~i database yang baru ke dalam file s!.stcm . a n g 11ormnl (non-/>rrr/i11ot7~1/ 1/17rt1hi~\t') 1C1ci-ubid1 srorclge group DBA dapat mcrubah srortrgr gt.orip \.ang b c r ~ sdatabasc ~ mau pull 1, ,< / i i k contoh, u~itukmerubah S I O T L I ~ C,groilp VDS ke VDSLISTER. lakukiln SQL> ALTER DATABASE VDS STTOGROUP VDSLISTER. Ukurar~database darl /og.fi/e-nj.2 tldnk nkan tcrpcngaruh olch pcr~ntah1111
I
~ I
SL,~~~~;;~II
Mcnghapus sloroge groici) DBA dapat nlenghapus vtorc7g~~ ,yro/ip b ~ l a.\.rort/,y~, ,qrolcp !,l?s sudall r1ii;lh ~ I I ~ L I I ~ . I L . , I I : lag1 oleh database manapun dall hlla .storcrg~,group rerscbut bukan L / c ~ f ~\ ~r ~l i~i~~. ~ i : : ~ groi//) pada saat penghapussn d~lakuhnn.Contoh ilnti~k1i1cripIinl1~1~ slotc~yt~:).o!/!' VDSFILES SQL, DROP STOGKOUP L'DS1:ILES.
SL)l,lltr.s~~ 7'ririning Rltrteritrl (.'up),r~,yhr&)/99.>. /"I
I b r , s ~ ~. ! ~\ ~ lJ ~~~ ~I , Ii ~~~ ..',,co,r,! I~~ o ,
Mengatur ukuran extension Ukuran database akan terus meningkat bila data pang disirnpan nlak~nbnn?.nk Pnda s a t suatu database nienjadi penuh, SQLBase akan menambah extent b c n k u r ~ ykc ~ dalam database ybs. DBA bertangung-jawab untuk mencntukan ukurnn d a r ~t!stcr71 tcrsebut. DBA dapat menggunakan per~ntah SET EXTENSION untuk mcngarur ukuran exrenr baik untuk yarrirroned luaupun non-portltrunrd database G l n t r ~ xd a r ~ perrntah ini adalah: SQL> SET EXTENSION <#kbytes>;
Untuk rnencntukan bcsarnya tcnipat ?.ring ters~sadalam l~nrrrrronrd dotohtr.st>,gunaka11 scnpt berikut ini. SQL> SET LINEWRAY ON, column 1 width 8 heading 'Database‘. column 2 width 8 heading 'Stoareas'. column 3 width 8 heading 'Arcanarne'. colunm 4 width 8 heading 'Pathnarnc', colun~n5 width 8 heading 'Areasize'. column 6 width 8 heading 'Free%'. break on 1 2; a.name, b stogroup. b.nrcanalnrnc. i/.tnr~i(c .pathna~~ic). sclcct c.areasizc, (~nu11valuc(100*d ~ ~ t ~ ~ z c / c , i i r ~ ~ s ~ z ~ ) , O j from syssql .databases a, syssql .stoareas b, syssql.areas c, syssql.freecsts d where b.areaname=c.na~ne and c .nanlc=d.namc(+) n~lrl ((a.stogroup=b.snogroup)or (a.logstogroup=b.stogroup) 1 order by 1,2
Neorvonize Database Ada dua tlpe framentasi ).,ang dapat nicnipcngaruh~kinerja (pcrforniancc) 1 Frngmentasi database (*.dbs) Hal ini terjadi bila database tidak nicrniliki disk s/?nce yang cot7r1g1cott.s 2 Fragmcntasi table d a l m database Hal ini terjadi bila modifikasi tcrhadap data sering dilakukan. DBA dapat memanfaatkan fasilitas sd d a r ~Norron Utrlr~yuntuk n~clnkuhant'rngn~c~lras~ database. Sedangkan untuk fragnientas~table dalaii databasc, pcr~ntall REORCiAKlZl; dari SQLTalk dapat digunakan. Proses yang terjadi pada saat REOKGANIZF adalnh sbk 1 UNLOAD database ke file 2 Inisialisasi database
i
SQLBcrse Trrrining Material
C o p y r r g h ~Q lY9.5. h j , PT Vrrscrdoro S,v.rtr~~r~nJo. Jukuriu
3. Re-LOAD database ybs. SQLTalk menggunakan temporary file yang benlarna sqltmp.nnn untuk melaksanakan unload. Sebelum melakukan REORGANIZE, sirnpanlah satu copy dan database ybs. karena tenporary file dan database ybs. akan rusak apabila terjarfi kesalahan pada saat reorganisasi. Jika DBA ingin melakukan fiagrnentasi yang lengkap baik bagi database maupun ,table-table yang terdapat dalam database ybs, gunakan fasilitas sd (dari Norton Utility) dm kemudian rnanfaatkan perintah REORGANIZE. Contoh: SQL> REORGANIZE; LNLOAD COMPLETED INITIALIZING DATABASE STARTING TO LOAD CREATE TABLE DBA.EMP (I INTEGER) PCTFREE 10
I
TABLE CREATED INSERT INTO DBA.EMP VALUES(: 1) LOADING TABLE DBA.EMP PROCESSING DATA 50 ROWS LOADED
50 ROWS INSERTED
LOAD COMPLETED
Mengecek database intenrip Perintah CHECK melakukan pengecekan atas integritas (integrip) clan keseluruhan atau sebagian database. Pengecekiln integritas meliputi: 1. Mengecek integritas sistem dan data struktur data yang mernilllu ruang kosong 2. Mengecek data dan stnrktur yang dialokasikan 3 . Mengecek jurnlah row dalarn table dan jumlah sebenarnya dan row 4. Mengecek setiap index ddam base table 5 . Mengecek integritas dm setiap row dan index page 6 . Menjamin bahwa setiap page adalah bagian dari struktur yang teralokasi atau terdapat dalam d a h r page yang kosong SQLBase membaca setiap page dalam database pada saat pengecekan lntegr~tasclan mengalubatkan penempatan shared lock dalam tiap page tersebut. Pada saat SQLBase menemukan kesalahan integritas (inregrip vrolahon), pengecekan diherltikan dan error
SQLBase Training Material Copynghf 8 1993, by PT Versadata Systemrndo. Jakarta
message &tampilkan. DBA dapat menghapus objek yang memilllu kesalahan integritas. Jika SQLBase menemukan banyak masalah integritas, DBA dapat melakukan resrore dari database yang di-backup sebelumnya. Tipe-tipe perintah CHECK yang dapat digunakan:
Perintah CHECK DATABASE CHECK SYSTEM ONLY
Keterangan Mengecek integritas atas seluruh database -. .. Mengecek integritas atas systemdefmcd table: & ~ n d c s dnn rncngabaikan user-created table & index Mengecek integritas hanya pada index tertentu s y a- Mengecek integritas hanya pada table dan index tcrtentu saja Mengecek integritas hanya pada table tertentu dan rllcngabalkarl semua index ymg berhubungan dengan table tersebut p~
CHECK INDEX CHECK TABLE CHECK TABLE WITHOUT INDEXES Contoh:
SQL> CHECK DATABASE; SQL> CHECK DATABASE SYSTEM ONLY; SQL> CHECK INDEX idxl; SQL> CHECK TABLE emp; SQL> CHECK TABLE emp WITHOUT INDEXES;
SQLBase Training Material Copyrrgh~8 1995, by PT.-Vursoduto Sysrernrndo. Jokorro
IX. Security dan Authorization
D atabase Authority (Otoritas database)
Database authority (otoritas database) mengontrol siapa yang berhak mengakses datczase dan operasi apa saja yang &pat dilakukan. SQLBase mengontrol hak akses ke database melalui username clan password. SQLBase mernildu empat level user sebagai benkut:
I
SYSADM
1
Level
Otoritas User
RESOURCE
1
Gambar 9.1 Level otoritas database
1
Secara singkat, keterangan clan setiap level adalah: Level Otoritas Keterangan Membuat user, menentukan level otoritas user dan password-nya SYSADM DBA Memberi, merubah atau mencabut kembali hak dari setiap user atas setiap objek RESOURCE Membuat dm menghapus objek. Memberi, merubah atau mencabut kembali hak user lain atas objek4bjek ybs. CONNECT Mengakses objek, tetapi tidak dapat membuat objek Hanya SYSADM yang dapat membuat/menambah user dengan mengunakan penntall GRANT. Level otoritas &edaDat di-GRANT oleh SYSADM adalah: Keterangan Level otoritas ini hams dbenkan dahulu sebelum level-level yang lain diberikan. Level ini akan memunglunkan user untuk: Mengakses database SELECT table & view dari user lain bila hak SELECT tclah diberikm sebelumnya INSERT, UPDATE dan DELETE data ke dalam I dari table user lain bila semua hak yang diperlukan telah dbenkan sebelumnya Membuat view dan synonym RESOURCE Level otoritas ini dapat menggunakan semua hak yang terdapat dalam CONNECT ditambah dengan hak untuk: Membuat table dm rn&ghapus table yang dibuat sebelumnya / Memberi, merubah dan mencabut kernbali hak atas table ybs. keldan user Y
I
I I
1
1
I
I
I I
I
lain
DBA
Level otoritas ini dapat meng~unakan semua hak yang terdapat dalanl level
SQLBase Training Material Copyright rD 1995, by );PT. VersadataSystemindo. Jakarta
I (
CONNECT dan RESOURCE serta semua hak atas semua objek dalam database. Selain itu, DBA dapat: Memberi, merubah atau mencabut kembali hak-hak atas semua objck keldan user lain Contoh: 1 . Membuat user dengan nama ERWIN dan password ERW SQL> G M N T CONNECT T O ERWIN IDENTIFIED BY ERW; CONNECT AUTHORIIY GRANTED 2 . Menarnbah level otoritas user ERWIN ke RESOURCE SQL> GRANT RESOURCE T O ERWIN; RESOURCE AUTHORITY GRANTED 3. Mcnarnbah level otoritas user ERWIN ke DBA SQL> GRANT DBA T O ERWIN; DBA AUTHORITY GRANTED Ada dua cara untuk merubah password: 1 . Melalui perintah GRANT yang hanya dapat dilakukan oleh SYSADM Misalnya password user ERWIN ingin dirubah dari ERW rnenjadi SECRET SQL> GRANT CONNECT T O ERWIN IDENTIFIED BY SECRET; CONNECT AUTHORITY GRANTED 2. Melalui perintah ALTER PASSWORD yang dapat dilakukan oleh setiap user Misalnya password user ERWIN ingui dirubah dan SECIET menjad RAHASIA SQL> ALTER PASSWORD SECRET T O RAHASIA; PASSWORD ALTERED Perintah REVOKE akan merubah level otoritas database user. SYSADM dapat I ~ ~ - R E V O K E level otoritas berikut ini: Level Otoritas Keterangan DBA Mengurangi otoritas user rnenjad level CONNECT. Alubatnya user tidak dapar membuat dan menghapus table lagi, tidak &pat rnembcri d m niencabut kemb:ll~ I hak atas table-table terscbut dari user lain. ( RESOURCE I Mengurangi otoritas user rnenjadi level CONNECT. Akibatnya user t~dakciapat 1 mernbuat dan menghapus table l a g , tldak dapatt membcri dan mcncabut hembal~ hak atas table-table tersebut dan user lam. CONNECT User tidak dapat mengakses database lagi. SYSADM hams mencabut kernball semua hak atas semua table dan view sebelum mencabut level CONNECT 1111
I
1
Contoh: 1. Mencabut otoritas user ERWIN yang rnermliki level DBA: SQL> REVOKE DBA FROM ERWIN; DBA AUTHORITY REVOKED 2. Mencabut otoritas user ERWIN yang nlemilllu level CONNECT S Q L , REVOKE CONNECT FROM ERWIN; CONNECT AUTHORITY REVOKED
/
Hak atas Table dan View
SQLBuse Training Material Copyrrghr (9 1995, by I'T. Versadata Syslernrndo, Jakarra
diilan~ User yang memildu otontas DBA dapat memberlkan hak atas semua table dan database. User yang memiliki otoritas RESOURCE hanya dapat membcrikan hak ntas scniua table yang dibuatnya dan view yang didasarkan atas table-table yang dibuatnya Karcna uscr yarig memiliki otoritas CONNECT tidak dapat membuat objek, maka user tipc lnl t ~ d a kciapat ~ilembcrikanhak p pa pun.
1 1
User yang metnbuat tablelvicw ~nerupakanowner (pemilik) dari objck tcrscbut dnn r n c n u l ~ h ~ hak penuh atas objek-objek yang hbuatnya, termasuk hak untuk mernber~kanaLses supn! a user lain dapat menggunakan objek-objek terscbut. Sebagal p c n ~ ~ l i kobjck, user dapnr nlembenkan hak sbb: Hak I Keteranrzan SEI.ECT I Mendanatkan data dan t~hlelview ----- - - - - - - ---- - --- -- - - - - - -. . - - . . INSERT ] Menambah baris (data) ke dalani tablc/view . DELETE / Menghapus bans (data) dan tablelview .Merubah data padakolo~n-kolomtertentu dalam suitu table Membuat atau menghapus indcx &lanl table ... ..-. - . .. ALTER I Merubah table ALL Menerapkan semua h,& dl atas atas tablelview -
-
p~
-
--
1
(
-
I
Keyword PUBLIC mewakili semua user. Dcngan memberlkan hak ttertentu kc PLjBl-,I(-. nlah;~ berarti mengijlnkan semua uscr (yang tclah a& dan y m g &an datang) mcndapatknn Iiah \ ang. dibcrikall tadi secara otomatis. GRANT INDEX, GRANT ALTER, REVOKE INDISS d;iri REVOKE ALTER tidak dapat dilakukan tcrhndsp v~c\v Contoll: 1. USERl menlberikan hak kepada USER2 ulltilk nicl~hatdata tcrd~ipatdalanl t;it)lc' EMP: SQL> G R A N T S E L E C T O N E M P T O USERZ; PRIVILEGE(S) GRANTED ON TABLE OR VIEW DBA membenkan hak kepada USER3 untuk nlelihat &pat j9arlg terdapat cialnm t;lbIc 2. EMP yang dibuat oleh USERl. SQL> GRANT SELECT O N USER1.EMP T O USERS; PRIVILEGE(S) GRANTED ON TABLE OR VIEW 3. DBA membenkan hak kepada USER4 untuk melihat dan merubah data pada hol~lr~i PHONENO dalarn table EMP yang dibuat oleh USERI: SQL> G R A N T S E L E C T , UPDATE(PHONEN0) O N USER1.EMP T O L'SERJ; PRMLEGE(S) GRANTED ON TABLE OR VIEW 4 . DBA memberikan hak kepada semua user untuk melihat data dalnlli tablz E M P >iiIig dibuat oleh USERI : SQL> G R A N T S E L E C T O N USER1.EMP T O PUBLIC; PlUVlLEGE(S) GRANTED ON Tc\RI.,E OR VIEW 5 . USERl nle~icabuthak USER2 untuk ~nclihatdata d a r ~table EMP SQL> R E V O K E SELECT O N E M P F R O M USER2; PRIVILEGE(S) REVOKED ON TABLE 6. DBA mencabut hak USER2 untuk melihat data di table EMP yang d ~ b ~ ~CISEK ;it 1 SQL> R E V O K E S E L E C T O N USER1,EMP-INFO F R O M USER2; PRIVILEGE(S) REVOKED ON TABLE 7. DBA mencabut hak USER2 untuk rncrubah kolom PHONENO dnri table EbIP >alig dibuat oleh USERl:
-
1
S Q D REVOKE UPDATE(PHONEN0) .ON USER1.EMP FROM USERZ; PRlVILEGE(S) REVOKED ON TABLE 8 . DBA mencabut hak PUBLIC untuk mellhat data dari table EMP yang hbuat oleh USER1: SQL> REVOKE SELECT ON USER1.EMP FROM PUBLIC; PRIVILEGE(S) REVOKED ON TABLE
Setiap kali seorang user mengakses tablelview yang dbuat oleh user lain, user tersebut harus menyebutkan table/view ybs. lengkap dengan nama user yang rnembuat tablelview tersebu t, misalnya: SYSADM.SYSTABLES, USER1 .EMP, dst. Synonym memungkmkan user mengakses tablelview yang dibuat oleh user lain tanpa perlu menyebutkan nama tablelview tsb secara lengkap (&npa perlu mencantumkan owner-nya). Semua nama synonym ditampung di system catalog table yang bernarna SYSADM.SYSSYNONYMS. Synonym hanya bisa dlhapus oleh SYSADM, DBA, atau owner-nya. Contoh: 1. Membuat synonym EMPLOYEE dan table USER1 .EMP: SQL> CREATE SYNONYM EMPLOYEE FOR USER1.EMP; SYNONYM CREATED 2. DBA membuat PUBLIC synonym EMPLOYEE untuk semua user: S Q D CREATE PUBLIC SYNONYM EMPLOYEE FOR USER1.EMP; SYNONYM CREATED 3. Menghapus synonym EMPLOYEE: SQL> DROP SYNONYM EMPLOYEE; SYNONOYM DROPPED 4. Menghapus PUBLIC synonym EMPLOYEE: SQL> DROP PUBLIC SYNONYM EMPLOYEE; SYNONYM DROPPED Views Salah satu cara untuk mengontrol database object security adalah melalui view. Dengan cara ini, user tidak perlu diberikan hak untuk melihat data secara langsung dari table. View menjamin keamanan dalarn segi: 1. Akses per baris DBA dapat membatasi baris-baris data yang dapat diakses oleh user dengan menWHERE pada saat membuat view. 2. Akses per kolom DBA dapat membatasi kolom-kolom mana saja yang boleh diakses oleh user dengan menyebutkan nama kolom pada saat view dibuat.
Table benkut ini akan digunakan oleh contoh kasus: NAME DEPT SALARY
-------Smith
355
---------
PHONENO
32654
832
SQLBase Training Material Copyright 0 1995, by PT. Versodolo Syrrarnindo. Jakarta
Jones Brown Joyce Chen Herrlein
150 700 150 355 700
Misalkan DBA ingin rnernbatasi akses dari user ANNA hanya pada kolorn nama, gaji dan nornor telepon. Selain itu, user ini hanya boleh rnelihat data pada Departernen 355 saja. View yang akan dibuat adalah: SQL> CREATE VIEW EMP AS SELECT NAME, SALARY, PNONENO S Q b FROM EMP-INFO WHERE DEPT=355; VIEW CREATED Setelah itu, DBA akan rnernberikan hak untuk dapat melihat data kepada user ANNA supaya &pat rnenggunakan view yang telah dibuat tadi dengan: SQL> GRANT SELECT ON EMP TO ANNA; PRIVILEGE(S) GRANTED ON TABLE OR VIEW Pada saat ANNA rnengakses view EMP dengan: SQL> SELECT * FROM DBA.EMP; Maka hasilnya adalah: NAME SALARY PHONENO
------
------
-------------
--------
Smith 32654 Chen 37666 2 ROWS SELECTED
832 222
DBA lalu membuat sebuah view baru supaya sernua user dapat rnelihat nama dm nornor telepon saja: SQL> CREATE VIEW PHONE AS SELECT NAME, PHONENO FROM EMP-INFO; VIEW CREATED SQL> GRANT SELECT ON PHONE TO PUBLIC; PR.IVILEGE(S) GRANTED ON TABLE OR VIEW Hasil yang terlihat ketika sernua user melakukan SELECT * FROM PHONE adalah: PHONENO NAME
-Smith Jones Brown Joyce Chen Herrlein
832 285 72 1 145 222 185
View hanya dapat dihapus oleh pembuatnya, SYSADM, atau DBA. SQLBase secara otomatis akan rnenghapus semua hak yang terdapat &lam view yang dhapus, dan juga rnenghapus semua view lain yang berhubungan dengan view yang drhapus tersebut.
I
Contoh: SQL> DROP VIEW EMP; VIEW DROPPED
SQLBase Training Material Copyright 8 1995, by PT. Versadatcl Sysremindo. Jakarta
-
commit
Prepare to Commit
PREPARE STAGE
Transfer
I
COMMIT STAGE
I I Delete Transaction 4Lnfo
END Transaction
COMMIT debit
(
I
1
COMMIT credit
]
I
4
Gambar 1 1.2 Tahapan untuk COMMIT dalam distributed transaction
Tidak semua distributed transaction yang COMMIT, ada juga yang melakukan ROLLBACK. Misalnya cabang Medm tidak dapat melakukan commit terhadap transaksi dan menginmkan pesan ROLLBACK ke Jakarta, Operasi roIlback akan membatalkan semua perubahan ke semua database. Sebagai contoh, bila suatu transaksi dibatalkan karena tejadinya kesalahan, rnaka rollback akan dilakukan ke seluruh database yang terlibat dalam transaksi tersebut.
€3 Medan
Prepare to Commit I
PREPARE STAGE
I
I
ROLLBACK
Deposit
I
I
COMMIT STAGE
ROLLBACK
Delete Transaction
END Llnfn 1 ( Transaction 1
Gambar 1 1.3 Tahapan untuk ROLLBACK dalarn distributed transactiton
Two-phase commit adalah fasilitas yang penting untuk menjamin integritas dalarn distributed transaction. Walaupun dernikian, fasilikfs ini menciptakan lalu-lintas network yang padat dan &pat mempenganh kinerja. Jlka terdapat X transaksi, maka coordinator hams n~engirimkan dan menerima paling seddc.it pesan sebanyak 4X untuk melakukan commit atas transaksi ybs. Sebelum memanfaatkan two-phase commit, pertimbangkanlah rnasalah kinerja hi.
SQLBase Training Material Copyrrghr 0 1995. by PT. Ijersadora Sysrerrr~ndo,Jrrkarra
i
I
XII. SQLBase System Catalog
System catalog adalah sekumpulan table yang mengandung informas1 objek-objek dala1-r-r database. Semua system catalog table dirnilrlu oleh SYSADM dan datur oleh SQLBase System catalog juga lazim dsebut sebagai data dictionary.
Berikut ini dsaiikan semua SOLBase svstem catalon table beserta keterangan sinnkatr~\.a Nama table Keterangan SYSCOLAUTH Mencatat semua hak update kolom clan tiap user. Mencatat semua kolom dalam tiap table. SYSCOLUMNS Mencatat semua stored command dan ~rocedure. SYSCOMMANDS SYSEVENTS Mencatat semua system timer event. Mencatat level otoritas execute user untuk tiap stored procedure SYSEXECUTEAUTH Mencatat setiap FK constraint. SYSFKCONSTRAINTS SYSINDEXES Mencatat index dalam tiap table. Mencatat semua kolom dalam tiap index. SYSKEYS SYSPARTTRANS Mencatat s e t i a ~indoubt distributed transaction. SYSPKCONSTRAlNTS I Mencatat s e t i a ~PK constraint. SYSROWIDLISTS Mencatat informasi tentang Result set yang dsimpar. SYSSYNONYMS Mencatat semua table dan view synonym. . SYSTABAUTH Mencatat hak tiap user atas table. SYSTABCONSTRAINTS enc cat at constraint dari tiap table. Mencatat semua table dan view. -SYSTABLES SYSTRGCOLS Mencatat semua kolom yang mernilllu UPDATE trigger. Mencatat semua trigger. SYSTRIGGERS SYSUSERAUTH Mencatat setiap%el otoritas database dan user SYSVIEWS Mencatat text dan tiao view.
-
-
SYSADM.SYSCOLAUTH Table ini mengandung hak update user atas setiap kolom tablelview. Nama kolom Keterangan GRANTEE Authorization ID dari user yang memildu hak update. CREATOR Authorization ID dari user yang rnernbuat table ternpat be;lakun)-a hak update. Nama tablelview tempat berlakunya hak update. TNAME COLNAME Narna kolom t e m ~ a berlakunva t hak u ~ d a t e .
SYSADM.SYSCOLUMNS Table ini mengandung informasi semua kolom yang terdapat dalam tiap table dan (termasuk kolom dari system catalog table).
SQLBnse Training Mnterinl Copyr~ghr@ 1995, by PT. Versadara Sysrerri~ndo.Jokarru
VICV,
Nama kolom
I
NAME TBNAME TBCREATOR COLNO
Keterangan
I
SCALE
UPDATES
Nama kolom. Nama tablelview dari kolom. I Authorization ID dari user yang membuat tablehiew. Nomor kolom dalam table. Nomor ini tidak berubah walaupun ada 1 kolom lain dalam table yang sama dihapus. Panjang data dalam kolom. Panjang standar yang berlaku untuk tiap tipe data: INTEGER 4 SMALLrNT 2 FLOAT 8 CHAR panjang string VARCHAR panjang rnaksimum dari string DECIMAL jumlah precision Khusus untuk kolom dengan tipe data DECIMAL. Nilainya 0 untuk tipe data lainnya. 'Y' jika null dijmkan dalam kolom ybs i 'N' j~kaNOT NULL ; I 'D' jlka NOT NULL WITH DEFAULT 'Y' jlka kolom dapat drubah 'N' jika kolom hanya read-only. User-specific comment untuk tiap kolorn Panjang rnaksimum 254 karakter. User-specific label tentang tiap baris. Panjang maksimum 30 karakter . Panjang kolom rata-rata untuk semua bans dalam table. Ini 1 mi& berbeda dengan panjang kolom karena SQLBare menyirnpan semua kolom sebagai data variable. I Panjang rata-rata dari kolom LONGVARCHAR untuk semua bans dalam table. Nilainya 0 untuk kolom non-LONGVARCHAR
I
/
REMARKS LABEL AVGCOLLEN
AGCOLLONGLEN
I
Table ini mengandung mformasi untuk setiap stored command atau stored procedure. , Nama kolom Keterangan CREATOR Authorization ID dari user yang membuat stored command atau 1 I stored urocedure. I NAME Nama stored command atau stored procedure. Pada saat SQLBase menyimpan command yang terdapat dalanl static procedure, SQLBase akan membenkan narna yang digenerate secara internal. TYPE Command (C) atau Procedure (P). SYSTEM 'Y' bila SQLBase yang membuat stored command atau stored procedure; 'N' bila user yang membuat. Pada saat SQLBase menyimpan commandprocedure 4,ang terkandung dalam static procedure, nilainya 'Y'. Sebaliknya, b ~ l a werintah STORE diaunakan. nilainva 'N'. STATIC 'Y' bila static stored urocedure: 'N' bila bukan. I VALID I 'Y' bila command valid; 'N' bila comrnand invalid.
1
SQLBase Training Matetic11 Copyright 0 1995. by PT Versodolo Sysrert~rndo.Jokurro
1
Stored command menjadi invalid bila referenced column dlhapus, ' atau index pada referenced column &pus. Stored procedure tidak d a ~ am t e n i d invalid. Jika di-set ON dan stored command akhirnya menjadi invalid, SQLBase secara otomatis me-recompile bila stored procedure 1 tersebut di-execute. i Text dan stored command atau perintah-perintah SQL dalam stored ~rocedure. Kolom ini bertipe data integer dan merupakan identifikasi (nomor seri) yang berhubungan dengan SYSCOMMANDS.
I
AUTORECOMPILE
TEXT
'
SNUM
SYSADM.SYSEVENTS Table ini mengandung informasi untuk tiap event (peristiwalkejadian). Nama kolom Keterangan Authorization ID dari user yang membuat event. CREATOR NAME Nama event. /-TYPE I Command (C) atau Procedure (P). Tipe event. 1; Sampai versi ini, hanya timer event type saja yang ada. I ID dari event. EVENTID BEGINTIME Permulaan waktu event. I INTERVAL Interval waktu (dalam detik) eksekusi dari event. 'Y' bila event merupakan systemdefined; 'N' bila tidak SYSTEM SPSNUM Kolom integer yang menunjuk ke nomor seri (SNLN dalam SYSCOMMANDS) dari stored procedure yang dieksekusi oleh
1 I
1
I
;-
1
event
' TEXT ACTIONCODE I
1
Text dari stored command atau perintah SQL d a l k Compiled code dari stored command atau perintah SQL dnlmi procedure. hi untuk keperluan internal saja.
SYSADM.SYSEXECUTEAUTH Table ini mengandung informasi untuk setiap perintah GRANT PRIVILEGES da1a.m stored procedure. Nama kolom Keterangan CREATOR Authorizatjon ID dan pembuat stored procedure. NAME Nama yang diberikan secara internal untuk stored procedure. GRANTEE Authorization ID dari grantee (user dan stored procedure). USECREATORPRIV 'Y' jika GRANTEE men an hak pembuat; jika tidak blank USERGRANTEEPRIV 'Y' jika GRANTEE m e n s a n hak senhri; jika tidak blank.-
~ I /
-1 i
7
SYSADM.SYSFKCONSTRArNTS Table ini mengandung informasi kolom FK dari tiap table. Nama kolom Keterangan CREATOR Authorization ID da.ri user yang membuat table. NAME Narna table tempat FK berada. CONSTRAINT Nama FK constraint. I Nomor urut dari kolom FK dalarn FK. SQLJrr.se Training Materirrl
LFJCOLSEQNUM
I
Copyrrghr O 1995, by PT 1,'ersodoroSysra~~r~ndo, Juhurru
I
I
I
-7
1 4 I L
u-
-
A
DA
A
Nama clan nomor kolom referensi dalam table Author~zation ID dan user yang membuat parent t ~ b l e ~ a l g m e n j d acuan FK. Nama parent table yang menjah acuan FK Narna kolom yang menjadi kolom acuan &lam parent table
REFSCOLUMN REFDTBCFEATOR
-
REFDTBNAME REFDCOLUMN
SYSADM.SYSINDEXES Table ini mengandung mformasi setiap index, termasuk index dalam system catalog table. Ketermgan Nama kolom __l_i Nama indsx. . NAME Authoriza1:ion ID dari pembuat index. CREATOR --TBNAME Nama table tempat index hdefmisikan. Authorizat~onID dari pembuat ta5le tempat index didefinisikan. TBCREATOR -4 'D' bila dudikasi index dijinkan,'LJ' bila index u d . UNIQUERULE --COLCOUNT I Jumlah kolcm dalam index. I IXTYPE dana 'B' untuk B+ tree. I ---CLUSTERRULE 'Y' untuk clustered index dan 'N' untuk non-clustered i~ldcs SYSTEM index, 'N' jlka bukan. ! Jumlah baris &lam table, sesuai dengan yang ditentukarin user. PERCENTFREE Jumlah free apace yang hams diberikan untuk tiap index pagc pada ( saat index p*:rtama kali dbuat. Jlka tidak htentukatl user, malca default-nya ialah 10 persen. : HEIGHT Ketinggian index. Nilai minimum adalah 1, artinya index hcu11.a 1 mernililu satu page. Ketinggiankedalaman index tree adalah jurnlah 1 node yang haius dibaca dari root ke leaf level. Statistik it11 dlatur ! secara dinamtlc dalam index control page, tctapi han~ma d~cntat, dalam table SISINDEXES baik pada saat index dibuat pertxila ( I ) kali maupun UPDATE STATISTICS. Untuk B+ tree mden, Ilu mempakan jumlah index page Untuk llarli index, nilainya null. I LEAFCOUNT Untuk B+ tree index, in1 mempakan jumlah node pada tlngkat lcof II pada index (leaf page). Selain itu juga rnerupakan j u m l d ~p q c i dalam mdex sequence set. Untuk hash mdex, mlalnya null CLUSTERCOUNT Untuk B+ tree index, irli adalah jumlah clustcr, yang rncrupahnn , jumlah total perul~ahanpage yang mungkin tcjadi jika sclurull table dibaca melalui index sequence set. Nilai minimum (untuk clustered B+ tree intiex yang lengkap) adalah jumlah dari data pagc. i Nilai maksimum (urltuk unclustered index lengkap) adalah jumlah I I baris dalam table. Untuk hash index, nilainya null. PRIMP AGECOUNT Untuk B+ tree index, nilainya null. Untuk hash index, mi rnerupkrui j junllah primary page yang dalokaslkan untuk index subjcct table. i Iru sama dengan jumlah hash slot yang tersedia urltuk mendistribusikan baris-baris dalarn table. .. .- * OVFLPAGECOUNT Untuk B+ tree index, nilainya null. Untuk hash index, I ~ 1 I rnerupakan jumlah overflow page yang dialokasikan untuk ~ n d c s( subject table. Ketika table dan index dibuat, ini adalah jurnlah 1 overflow page yang distxhakan SQLBase. Setelah itu. nllalny bertambah sejalan dengan jumlah overflow page yulg dibutuhkm i untuk menangani hashmg collision. ~.,
I
-~
1
--
1
1 I
4
1
- -. I
~
I
---
SQLBnse Trtrining /I.l(~teritrl ( - u / ~ y r ~ g hBr !YY.T, b y 1'7: I/ersctd~~rn S y s f ~ . ~ ~ l l n.liirioi-iii i/o.
Untuk B+ tree index, ini rnerupakan panjang key rata-rata untuk sernua index entry. Statistik ini dbutuhkan karena SQLBase , mengatur semua index entry sebagai panjang variable, field prefix I only minimum. Untuk hash index, nilainya null. 7 Untuk B+ tree index, ini merupakan nornor grup untuk index page. / Untuk hash index, ini adalah nomor grup dari overflow page Jumlah frekwensi index digunakan dalam rnengalokasikan d a b Pada saat table atau index digunakan untuk pertama kalinya, n ~ l a, ~ variable ini di-load ke memory. Variable memory ini ditambah ) setiap kali full index scan digunakan. Kolom ini dirubah ket~kaI cursor terakhir dsconnect dari database. Untuk merubah kolom 1111. SQLBase rnenggunakan internal cursor.
AVGKEYLEN
I
i
i
i
1
SYSADM.SYSKEYS Table ini rnengandung lnfonnasi kolorn-kolorn untuk tiap index. Nama kolom Keterangan IXNAME Nama index. IXCREATOR Authorization ID dari pembuat index. I COLNAME Nama kolom clan key. i Posisi numenk dari kolorn dalam bans; contohnya 2 (dari 7 kolorn ( COLNO 1 dalarn satu table). COLSEQ Posisi numenk dari kolom dalam key; contohnya 2 (dari 3 kolom yang membentuk key). ORDERING Sort order dari kolorn dalam key. 'A' jika ascending, 'D jlka 1I descending. Definisi @function digunakan untuk rnendefinis~kankey; contohnya ( CREATE INDEX EMPIX ON EMP (@UPPER (NAME)) I mernberikan nilai @UPPER(NAME) dalam kolorn ini. I DISTrNCTCOUNT Jurnlah distinct key dari kolom pertama sampai nilai COLNO. I SYSKEYS rnerniliki satu e n t j untuk tiap-prefix key dari rnultl- 1
7 1
1
I
I
1
1
-A
I
1
1I
I
I
i -
I
column index key. Entry pertam; rnengidung nilai DISTMCTCOUNT untuk kolom pertama dalam key. Entry kedua 1 mengandung nilai DISTINCTCOUNT untuk dua kolom pertama j dari key, dst. i
SYSADM. SYSPARTRANS Table ini mengandung informal in-doubt distributed transaction. Jika transaksi ini telah diresolved, maka data tersebut h p u s dari table ini. Table ini tidak mengandung semua informasi in-doubt transaction pada saat database mash aktif; table ini hanya mengandung inforrnasi in-doubt transaction setelah recovery. 7 Nama kolom Keterangan I Transaction ID global. ID 1 t ID2 Di unakan untuk ke erluan an akan datan . i & i Current status dari transaksi. Dapat berupa: I
STATE I
PROTOCOL LASTTMODTIME
'
protocol yang d i g k untuk &o-phase commit. saatInl.1 SQLBase hanya rnenggunakan protocol STANDARD. Timestamp yanp, rnenunjukkan kapan status transaksi dimodifikasi
i
SQLBase Training Material Copyrighf O 1995, by PT. Versudarcr Sysre~t~indu, Jakarro
I
I DB
I untuk terakhir kalinva 1 Nama dari co&t server
USERNAME PASSWORD SOURCE
Narna user yang &-connect ke commit server. Password yang dgunakan untuk connect ke commit server -. Suatu string yang rnengindrkaslkan bagainuna iilfornlasi tra~llsahs~ / dimaasukkan ke &lam table ini. I Nilai yang valid adalah: I NORMAL i Mctlunjukkan baris dimasukkan ketika database aktif CRASH Menunjukkan baris ditnasukkan setelah crash recoverq ROLLFORWARD Menunjukkan baris hmasukkan setelah rollforward recoven. __--~-J
SYSADM.SYSPKCONSTRAINTS Table h i mengandung informasi kolom PK d a r ~table.
1 NAME 1
1
PKCOLSEQNUM COLNAME
I
Nama table tempat PK dibuat. Nomor kolonl dari PK kolom. Nama kolom yang menladl PK
---. .- .. .. . . .
.
,
. -.
--
.. ..
-A
SYSADM.SYSROWIDLISTS Table ini mengandung informasi result set dm setiap row IL). J k a SET KESTR1C:TlON dl-.vrr ke ON, table ini hanya mengandung informasi tentang result set user saja. I Nama kolom Keterangan ._ . . NAME Nama row id list (result sct) yang hsinlpan. CREATOR Nama pembuat dari result set.
--
,~ I i
SYSADM. SYSSYNONYMS Table ini mengandung informasi synonym dari tiap table atau view. Keterangan Nama kolom Synonym daari table atau view. NAME Authorization ID dari pembuat synonym. CREATOR TBNAME Nama table atau view. TBCREATOR Authorization ID dari pembuat table atau view.
SYSADM. SYSTABAUTH Table ini mengandung informasi hak semua user dalam tiap table atau view. I Narna kolom Keterangan GRANTEE Authorization ID user yang memiliki hak. TCREATOR Authorization ID user yang membuat table atau vlew tcmpnt hak
blank
ALTERAUTH DELETEAUTH INDEXAUTH
SELECTAUTH
1
1
'Y' bila user dperbolehkan merubah target table. Jika rid&. nilainya blank. 'Y' bila user diperbolehkan untuk menghapus bans dalarn target table atau view. Jika tidak, diberikan blank. 'Y' bila user diperbolehkan membuat atau menghapus index dalarn target table. Jlka tidak, rlllainya blank. 'Y' bila user &perbolehkan maambah baris kc dalarn targct atau view. Jika tidak, nilainya blank. 'Y' bila user diperbolehkan membaca data dari target table atau view. Jika tidak, nilainya blank. 'Y' bila user dperbolehkan merubah data &lam target table ntau view. Jika tidak, nilainya blank.
/
;d
INSERTAUTH
I
/
Nama table atau view tempat hak berada. 'T' berarti target. '*' bila user memiliki hak update atas beberapa kolom dalam target table. Nama kolom tempat berlakunya hak update terdapat di table SYSCOLAUTH. Jika user tidak memiliki hak update atau bila update dlperbolehkan untuk setiap kolom dalam table, kolorn lni
TTNAME UPDATECOLS
UPDATEALITH
I
I
I
1 I I
I
SYSADM. SYSTABCONSTR4lNTS Table ini mengandung informasi table constraint. 1 Nama kolom Keterangan ~ CREATOR Authorization ID dari user yang membuat table. NAME Narna table tempat constraint berlaku. CONSTRArNT Nama constraint yang dgunakan database. Koloi-r-rii-rijuga menunjukkan nama FK. Untuk PK, kolom ini di-set ke 'PRIh4ARY'. Tipe constraint. Diberikan nilai 'P' untuk PK d m 'F' untuk FK. DELETERULE Ketentuan delete yang berlaku. Kolom ini tidak diguliakan untuk 1 PK constraint. i Kolom ini dapat &-set menjadi: CASCADE (C), SET NUL,L (N), RESTRICT (R). Default-nya RESTRICT. USRERIUNSDEP User-specified error message number untuk kesalaharn -..J
i
--I
USRERRUPDDEP
1
1
USRERRDELPAR
I
USRERRUPDPAR I
(
INSERT-DEPENDENT. J ~ k atidak ada user-specified error, kolom ) ini dibenkan nilai 0. User-specified error message number untuk kesalahan I UPDATE-DEPENDENT. Jtka tidak ada user-specified error, I I kolorn ini dibenkan nilai 0. User-specified error message number untuk kesalahan DELETE-PARENT. Jika Gdak ada user-specified error, kolom ini dberikan nilai 0. 1 I User-specified error message number untuk kesalahan 1 UPDATE-PARENT. Jika tidak ada user-specified error, kolorn ~ n i 1 diberikan nilai 0. I
1
1
SYSADM.SYSTABLES Table ini mengandung informasi semua table atau view. Nama kolom Keterangan A I Authorization ID user yang membuat table atau view. CREATOR NAME Nama table atau view. I COLCOUNT Jumlah kolom dalam table atau view. REMARKS User-specified comment untuk tiap baris. Panjang rnaksimu~~~ untuk 1 I comment ini adalah 254 karakter. I -7 TYPE 'T' jika table; 'V' jika view. I 'Y' jtka table hasil system-defined; 'N' jika tidak. SYSTEM Nomor seri yang unik yang berfungsi sebagai table ID. Ini SNUM digunakan oleh perintah UNLOAD DATABASE LABEL User-specified label tentang tiap table clan maksimum dari label adalah 30 karakter PERCENTFREE Jumlah fiee space untuk tiap table row page Default-nya 10 1 ersen. ROWCOUNT iumlah baris dalam table. h matur secara d i n a r k d a l a m x i control page, tetapi hanya dicatat &lam table ini pada s a t per~nrali UPDATE STATISTICS dilakukan. PAGECOUNT Jumlah base row page dalarn table. Kolom ini dirubah sctlap kali 1 perintah UPDATE STATISTICS dlakukan. Jika t~dakada nila~ I yang diberikan user dengan perintah SET, maka kolom in1 merupakan jurnlah clan nilai ROWPAGECOUNT, i EXTENTPAGECOUNT dan LONGPAGECOUNT. Default-nya 1 I adalah 2. 1 ROWPAGECOUNT Jumlah base row page dalarn table dan meliputi sernua extent page yang dialokaslkan. Hanya &catat dan dirubah setiap kali pcr~ntdl I i LPDATE STATISTICS dilakukan. Default-nya adalah 2 1 LONGPAGECOUNT Jumlah page dalam table yang dialokasikan untuk rnenyimpan kolom LONG VARCHAR. Hanya dicatat clan d~rubahsetlap ka11 i perintah UPDATE STATISTICS dilakukan. EXTENTPAGECOUNT Jumlah extent page. FREESLOTS Jumlah total dari free slot dalarn semua data page. I Ketlka satu page baru dralokasikan ke table, terdapat satu entry dalam slot table, yang disebut sebagai free slot. Pada s a t baris baru dtarnbahkan ke dalam page, lebih banyak entry yang ditarnbahkan ke slot table dan space untuk setiap slot tanbahan
-j
I
i
i
1II !
4
I
~
I
!
I
,
I
SQLBase Training Muterial C o p ~ ~ r ~@ g h199.7 l h}, 1'7' I ~ ' e r . w d oS~~~~l s r r ~ r r ~Jclkrrr-rti ndo,
USEDSPACE FREESPACE
1 AVGROWLEN i I AVGKOWLONGLEN i
I GROUPNUM I
1 TABLESCAN i
i I 1
; I
'
11
1
i
diambil dari free slot. Jlka satu baris dhapus, slot-nya rnenjadi free kembali. Jumlah total byte yang dpakai dalam semua data page, terrnasuk user data, page header, dsb. Jumlah total dari free byte dalam semua data page yang digunakan free slot. Ratio besar dari freespace dibandingkarl dengan usedspacc rnenunjukkar bahwa database reorgmzation atau load/unload dbutuhkar. Ratio ideal antara freespace dan usedspace haruslah kecil. Panjang rata-rata baris dalam table. Ini dapat berbeda dari panjang baris yarlg dideh~~sikan karcnrt SQLBase rnenyimpan sernua kolom sebagai variable data tanpa rnellhat tipe data yang digunakan dalam pendefi~uslankoloni. In1 rnerupakan panjang kolom yang disimpan dalam base table page sehingga mengabaikan semua kolorn LONG VAKCHAR. Panjang rata-rata dari semua kolorn LONG VARCHAR \.ang dsimpan dalam table. Nilainya 0 bila tidak ada kolorn LONG VARCHAR. Untuk B+ tree index, ini rnerupakan nomor grup untuk index page Untuk hash index, ini adalah nomor grup dari overflow page Jumlah frekwensi operasi table scan yang lengkap dllakukan terhadap table. Pada saat table atau index digunakan untuk pertanla kallnya. current setting untuk vanable ini di-load ke rnernoq Var~able1 memory ini htambah setiap kali table scan yang lengkap dilakukan. Kolom ilu dirubah saat cursor t&akhir disconnect darl database. Untuk merubah kolom ini, SQ1,Basc mcnggun:lhan internal cursor. .
1
I
, I
~~
SYSADM.SYSTKGCOLS Table ini mengandung informasi tiap kolom yang dipengaruhi oleh ~rpdatetrrgger Narna kolom Keterangan A CREATOR I Authorization ID dari pembuat trigger. 1 I NAME Nama trigger. TBSNUM Nornor sen dari tngger table ACTIONTIME Apakah trigger didefuuslkan untuk aktif sebelurn atau ssesudall I usaha rnodifikas~. COLNO Posisi relatif kolom dalam tngger table (rnulai dari 1) A
i
1
!
7
SYSADM.SYSTRIGGERS Table ini mengandung informasi untuk setiap trlger Nama kolom Keterangan CREATOR Authorization ID dari pernbuat trigger. ---1 NAME Nama trigger. "A 1 TBSNUM Nomor sen dan tngger table I ACTIONTIME Apakah trigger didefinisikan aktlf scbcluni arau sesudah , rnodlfikasl. 1 TRIGGEREVENT I Event (INSERT, UPDATE atau DELETE) diniana trigger d~bunt
1
1
SQ13ase Ilrrrining Material I
Copyr~ghr@ 1995. hy PT I'ersodora S)~sret~r~ndo. Jlrk~rrra
I untuk beroperasi. Alias (nama lain) table yang mengandung nilai kolom yang lama. Alias (narna lain) table yang mengandung nilai kolom yang baru. Apakah trigger didehisikan &if untuk setiap baris atau permtah. 'Y' bila system-defined trigger; 'N' bila bukan. Kolom integer yang menunjuk ke nomor seri (SNUM dalam SYSCOMMANDS) dari stored procedure yang dieksekusi oleh
OLDVALUENAME NEWVALUENAME FREQUENCY SYSTEM SPSNLTM
r--
trieeer.
NLMCOLUMNS
nya 0, yang berarti trigger diaktifkan oleh setiap usaha modifkasi atas salah satu kolom. Nilainya juga 0 untuk trigger yang d i d e h s ~ k a nuntuk o~erasiMSERT dan DELETE.
SYSADM.SYSUSERAUTH Table ini mengandung level otoritas database dari tiap user. Nama kolom Keterangan NAME Authorization ID dari setiap valid user dikatakan valid bila rnemillki itoritas CONNECT. RESOURCEAUTH 'Y' jika user dapat membuat dan menghapus table atau 'G' bila user adalah SYSADM. Selain dari itu, blank. DBAAUTH 'Y' jika user memiliki otoritas DBA, 'G' bila user adalah S Y A ~ A M Selain . dari itu, blank. Password yang diasosiasikan dengan authorization ID. Untuk PASSWORD melihat yang sesungguhnya, gunakan @DECRYPT.
I
I
SYSADM.SYSVIEWS Table ini mengandung informasi setiap view. Narna koIom Keterangan NAME Nama view. CREATOR Authorization ID dari pembuat view. Nomor urut dari baris yang berisi text dari view. SEQNO CHECKFLAG Menunjukkan apakah CHECK option diberikan di penntah CREATE VIEW. 'Y' berarti ya, dan 'N' bararti tidak. TEXT Text dari perintah CREATE VIEW. Kolom ini memiliki panjang 250 karakter. Jlka panjang perintah CREATE VIEW lebih dari 40 karakter, maka tiap baris berisi sebagian dari text.
SQLBase Training Material Copyrrghr 0 1995. by PT. Versadara Sysrer~rrndo,Jokarro
I I
