Working with DB2 Tools and Scripting (Continue)

Posted: Thursday,October 30, 2008 in Kuliah
Tags: ,

8. Script untuk create tabel dan insert data ke dalam tabel.

Copy kode create tabel berikut dan pastekan ke dalam file DB2Script.db2

=====================================================================

CREATE TABLE CUSTOMERS (
CUST_ID        INTEGER NOT NULL PRIMARY KEY,
FIRSTNME        VARCHAR(100) NOT NULL,
LASTNAME        VARCHAR(100) NOT NULL,
ADDRESS        VARCHAR(300) DEFAULT ‘123 ANY STREET’,
EMAIL        VARCHAR(100) DEFAULT ‘sampledata@express.com’
);

CREATE TABLE BOOKS (
BOOK_ID        INTEGER NOT NULL PRIMARY KEY,
TITLE        VARCHAR(300) NOT NULL,
COST            DECIMAL (7,2),
IMAGE        BLOB(1M)
);

CREATE TABLE SALES (
SALES_ID        INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
BOOK_ID        INTEGER NOT NULL,
CUST_ID        INTEGER NOT NULL,
QTY            INTEGER NOT NULL,
PRICE        DECIMAL(7,2) NOT NULL,
PURCH_DATE    TIMESTAMP,
CONSTRAINT FK1    FOREIGN KEY (BOOK_ID) REFERENCES BOOKS(BOOK_ID),
CONSTRAINT FK2    FOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS(CUST_ID)
);

COMMIT;

=====================================================================

Copy kode insert tabel berikut dan pastekan ke dalam file DB2Script.dat

=====================================================================

INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00001,’MICHAEL’,’THOMPSON’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00002,’SALLY’,’KWAN’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00003,’JOHN’,’GEYER’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00004,’IRVING’,’STERN’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00005,’EVA’,’PULASKI’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00006,’EILEEN’,’HENDERSON’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00007,’THEODORE’,’SPENSER’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00008,’VINCENZO’,’LUCCHESSI’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00009,’SEAN’,’O”CONNELL’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000010,’DOLORES’,’QUINTANA’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000011,’HEATHER’,’NICHOLLS’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000012,’BRUCE’,’ADAMSON’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000013,’ELIZABETH’,’PIANKA’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000014,’MASATOSHI’,’YOSHIMURA’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000015,’MARILYN’,’SCOUTTEN’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000016,’JAMES’,’WALKER’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000017,’DAVID’,’BROWN’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000018,’WILLIAM’,’JONES’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000019,’JENNIFER’,’LUTZ’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000020,’JAMES’,’JEFFERSON’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000021,’SALVATORE’,’MARINO’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000022,’DANIEL’,’SMITH’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000023,’SYBIL’,’JOHNSON’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000024,’MARIA’,’PEREZ’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000025,’ETHEL’,’SCHNEIDER’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000026,’JOHN’,’PARKER’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000027,’PHILIP’,’SMITH’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000028,’MAUDE’,’SETRIGHT’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000029,’RAMLAL’,’MEHTA’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000030,’WING’,’LEE’);
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000031,’CHRISTINE’,’HAAS’);
COMMIT;

INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80001, ‘BOOK1_TITLE’, 10.99);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80002, ‘BOOK2_TITLE’, 13.00);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80003, ‘BOOK3_TITLE’, 8.99);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80004, ‘BOOK4_TITLE’, 30.92);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80005, ‘BOOK5_TITLE’, 33.56);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80006, ‘BOOK6_TITLE’, 82.12);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80007, ‘BOOK7_TITLE’, 35.43);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80008, ‘BOOK8_TITLE’, 103.11);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80009, ‘BOOK9_TITLE’, 2.00);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80010, ‘BOOK10_TITLE’, 78.31);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80011, ‘BOOK11_TITLE’, 19.78);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80012, ‘BOOK12_TITLE’, 98.21);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80013, ‘BOOK13_TITLE’, 67.43);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80014, ‘BOOK14_TITLE’, 34.59);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80015, ‘BOOK15_TITLE’, 9.23);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80016, ‘BOOK16_TITLE’, 10.00);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80017, ‘BOOK17_TITLE’, 15.00);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80018, ‘BOOK18_TITLE’, 5.65);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80019, ‘BOOK19_TITLE’, 8.90);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80020, ‘BOOK20_TITLE’, 23.00);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80021, ‘BOOK21_TITLE’, 82.99);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80022, ‘BOOK22_TITLE’, 29.75);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80023, ‘BOOK23_TITLE’, 73.12);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80024, ‘BOOK24_TITLE’, 2.99);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80025, ‘BOOK25_TITLE’, 29.22);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80026, ‘BOOK26_TITLE’, 43.21);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80027, ‘BOOK27_TITLE’, 55.23);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80028, ‘BOOK28_TITLE’, 98.33);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80029, ‘BOOK29_TITLE’, 32.19);
INSERT INTO BOOKS (BOOK_ID, TITLE, COST) VALUES (80030, ‘BOOK30_TITLE’, 55.63);
COMMIT;

INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80024, 00001, 1, 4.82, TIMESTAMP(‘2004-03-30-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80030, 00009, 2, 44.21, TIMESTAMP(‘2004-03-30-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80001, 000030, 1, 83.42, TIMESTAMP(‘2004-03-30-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80018, 00002, 1, 44.12, TIMESTAMP(‘2004-03-30-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80022, 00002, 1, 71.23, TIMESTAMP(‘2004-03-31-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80006, 000019, 2, 8.43, TIMESTAMP(‘2004-03-31-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80028, 000021, 1, 19.83, TIMESTAMP(‘2004-03-31-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80003, 000028, 1, 53.87, TIMESTAMP(‘2004-03-31-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80026, 000027, 1, 92.41, TIMESTAMP(‘2004-03-29-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80002, 00008, 1, 81.23, TIMESTAMP(‘2004-03-29-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80011, 00003, 1, 39.51, TIMESTAMP(‘2004-03-29-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80009, 000015, 1, 90.32, TIMESTAMP(‘2004-03-29-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80022, 000013, 1, 19.32, TIMESTAMP(‘2004-03-29-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80019, 000011, 1, 56.98, TIMESTAMP(‘2004-03-29-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80026, 000010, 1, 4.98, TIMESTAMP(‘2004-03-29-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80011, 000018, 1, 9.98, TIMESTAMP(‘2004-03-28-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80005, 00007, 1, 20.43, TIMESTAMP(‘2004-03-28-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80009, 00009, 1, 95.32, TIMESTAMP(‘2004-03-28-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80013, 000027, 1, 41.21, TIMESTAMP(‘2004-03-27-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80001, 000025, 3, 81.90, TIMESTAMP(‘2004-03-27-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80022, 000024, 1, 20.99, TIMESTAMP(‘2004-03-27-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80018, 00001, 1, 76.32, TIMESTAMP(‘2004-03-27-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80029, 00001, 1, 97.32, TIMESTAMP(‘2004-03-27-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80014, 000022, 1, 82.41, TIMESTAMP(‘2004-03-26-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80025, 000018, 2, 67.37, TIMESTAMP(‘2004-03-26-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80007, 000025, 1, 41.21, TIMESTAMP(‘2004-03-26-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80030, 00004, 1, 8.98, TIMESTAMP(‘2004-03-25-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80001, 00001, 1, 9.99, TIMESTAMP(‘2004-03-25-00.00.00.000000’));
INSERT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80002, 000030, 4, 10.00, TIMESTAMP(‘2004-03-25-00.00.00.000000’));
COMMIT;

=====================================================================

9. Jalankan Script

Run ->  ketik db2ce

Click the Selected -> Open menu

Select file DB2Script.db2 dan click OK

Click Run

Click the Selected -> Open menu

Select file DB2Script.dat dan click OK

Click Run

10. Create Script dengan mengpastekan kode berikut ke dalam wordpad.

set DBPATH=c1
set DBNAME=express2

rem — the percentage of memory for the autoconfigure command
set MEMORY=20

db2 CREATE DATABASE %DBNAME% on %DBPATH% AUTOCONFIGURE USING MEM_PERCENT %MEMORY% APPLY DB AND DBM

db2 CONNECT TO %DBNAME% USER %1 USING %2

del schema.log triggers.log app_objects.log

db2 set schema express
db2 -t -v -f schema.dll -z schema.log
db2 -td@ -v -f triggers.dll -z triggers.log
db2 -td@ -v -f app_objects.dll -z app_objects.log

NB :

(t) pake default terminate char
(v) nampilin
(f) ambil dari file

contoh lain penggunannya :

//dalam db2cmd mau jalankan file script
db2 -tvf namascript.sql

//ganti default terminate char jadi @

db2 -td@ -vf namascript.sql
11. Save As pada folder express.

Dengan File name : “create_database.bat”

Save as type : Text Document – MS DOS Format

12. Pastikan File-file berikut sudah ada di folder express.

app_objects.ddl

schema.ddl

triggers.ddl

13. Pindahkan Directory ke tempat dimana script-script tadi disimpan dan masukkan command.

Start > Run > db2cmd

//atur directory

create_database.bat <userID> <password>

14. Perhatikan folder express yang berisi file2 scprit. Adakah Perubahan?!!! Kalau ada penambahan .log nya berarti script sukses dieksekusi.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s