大二的时候有门课RAD(IBM的一个软件)，当时用的RAD6.0里面内置了一个数据库cloudspace，网上一搜，这个cloudspace就是JavaDB(Derby)数据库的前身，当时由于种种原因，cloudspace图形界面出不来，后来直接想到直接用命令导入，下面介绍方法
方法很简单，命令是
ij 脚本名
ij命令中每条命令都加分号;否则无法执行下一条命令
cloudscape批量SQL(内嵌模式)
切换工作目录.bat
cd C:\Program Files (x86)\IBM\Rational\ADTrial\6.0\runtimes\base_v6\cloudscape
::cd C:\Program Files\IBM\Rational\SDP\6.0\runtimes\base_v6\cloudscapecall 切换工作目录.bat
call bin\embedded\ij %~dp0\ejbbank.ddl
@pauseconnect 'jdbc:db2j:EJBBANK;create=true';
DROP TABLE ITSO.CUSTOMER;
DROP TABLE ITSO.CUSTADDRESS;
DROP TABLE ITSO.CUSTACCT;
DROP TABLE ITSO.ACCOUNT;
DROP TABLE ITSO.CHECKING;
DROP TABLE ITSO.SAVINGS;
DROP TABLE ITSO.TRANSRECORD;
DROP TABLE ITSO.CUSTOMERINFO;
CREATE TABLE ITSO.CUSTOMER (
customerid INTEGER NOT NULL,
title CHAR(3) NOT NULL,
firstName VARCHAR(30) NOT NULL,
lastName VARCHAR(30) NOT NULL,
userID CHAR(8),
password CHAR(8),
address BLOB(2000),
PRIMARY KEY (CUSTOMERID)
);
CREATE TABLE ITSO.CUSTADDRESS (
customerid INTEGER NOT NULL,
street CHAR(20),
city CHAR(12),
state CHAR(12),
zipcode CHAR(10),
PRIMARY KEY (CUSTOMERID)
);
CREATE TABLE ITSO.CUSTACCT (
customerid INTEGER NOT NULL,
accID CHAR(8) NOT NULL,
PRIMARY KEY (CUSTOMERID,ACCID)
);
CREATE TABLE ITSO.ACCOUNT (
accid CHAR(8) NOT NULL,
balance DEC(8,2) NOT NULL DEFAULT 0.00,
interest INTEGER NOT NULL DEFAULT 5,
acctype VARCHAR(8) NOT NULL DEFAULT 'ACCOUNT',
discriminator CHAR(1) NOT NULL DEFAULT 'A',
overdraft DEC(8,2) NOT NULL DEFAULT 200.00,
minamount DEC(8,2) NOT NULL DEFAULT 100.00,
PRIMARY KEY (ACCID)
);
CREATE TABLE ITSO.CHECKING (
accid CHAR(8) NOT NULL,
overdraft DEC(8,2) NOT NULL DEFAULT 200.00,
PRIMARY KEY (ACCID)
);
CREATE TABLE ITSO.SAVINGS (
accid CHAR(8) NOT NULL,
minamount DEC(8,2) NOT NULL DEFAULT 100.00,
PRIMARY KEY (ACCID)
);
CREATE TABLE ITSO.TRANSRECORD (
transid TIMESTAMP NOT NULL,
accid CHAR(8) ,
transtype CHAR(1) NOT NULL,
transamt DEC(8,2) NOT NULL,
PRIMARY KEY (TRANSID)
);
CREATE TABLE ITSO.CUSTOMERINFO (
customerid INTEGER NOT NULL,
infoid INTEGER NOT NULL,
description VARCHAR(50),
data BLOB(10K),
PRIMARY KEY (CUSTOMERID, INFOID)
);
call 切换工作目录.bat
call bin\embedded\ij %~dp0\ejbbank.sql
@pauseejbbank.sql
connect 'jdbc:db2j:EJBBANK';
INSERT INTO ITSO.CUSTOMER
(customerid, title, firstname, lastname, userid, password) VALUES
(101, 'Mr', 'Maik', 'Schumacher', 'cust101', 'MS'),
(102, 'Mr', 'Fabio', 'Ferraz', 'cust102', 'FF'),
(103, 'Mr', 'Henrik', 'Sjostrand', 'cust103', 'HS'),
(104, 'Mr', 'Ian', 'Brown', 'cust104', 'IB'),
(105, 'Ms', 'Unknown', 'Lady', null, null),
(106, 'Mr', 'Ueli', 'Wahli', 'cust106', 'UW') ;
INSERT INTO ITSO.CUSTADDRESS
(customerid, street, city, state, zipcode) VALUES
(101, 'A St', 'A village', 'Germany', '11111'),
(102, 'B Bl', 'B big City', 'Brazil', '22222'),
(103, 'C Rd', 'C City', 'Sweden', '33333'),
(104, 'D Ln', 'D Metro', 'Canada', '44444'),
(105, 'E Ct', 'E Farm', 'Hawaii', '55555'),
(106, 'F Av', 'F Town', 'California', '66666');
INSERT INTO ITSO.ACCOUNT
(accid, balance, interest, acctype, discriminator) VALUES
('101-1001', 80.00, 4, 'CHECKING', 'C'),
('101-1002', 375.26, 5, 'SAVINGS', 'S'),
('102-2001', 9375.26, 5, 'SAVINGS', 'S'),
('102-2002', 75.50, 3, 'CHECKING', 'C'),
('103-3001', 100.00, 6, 'SAVINGS', 'S'),
('104-4001', 888.88, 4, 'SAVINGS', 'S'),
('104-4002', 88.88, 4, 'CHECKING', 'C'),
('105-5001', 0.00, 2, 'CHECKING', 'C'),
('106-6001', 1000.00, 3, 'CHECKING', 'C'),
('106-6002', 2000.00, 4, 'SAVINGS', 'S'),
('106-6003', 3000.00, 6, 'SAVINGS', 'S');
INSERT INTO ITSO.CHECKING
(accid, overdraft) VALUES
('101-1001',200.00),
('102-2002',200.00),
('104-4002',250.00),
('105-5001',200.00),
('106-6001',300.00);
INSERT INTO ITSO.SAVINGS
(accid, minamount) VALUES
('101-1002',100.00),
('102-2001',100.00),
('103-3001',150.00),
('104-4001',200.00),
('106-6002',100.00),
('106-6003',250.00);
INSERT INTO ITSO.CUSTACCT
(customerid, accid) VALUES
(101,'101-1001'), (101,'101-1002'),
(102,'102-2001'), (102,'102-2002'),
(103,'103-3001'),
(104,'104-4001'), (104,'104-4002'),
(105,'105-5001'),
(106,'106-6001'), (106,'106-6002'), (106,'106-6003'), (106,'105-5001');
INSERT INTO ITSO.TRANSRECORD
(transid, accid, transtype, transamt) VALUES
(CURRENT TIMESTAMP, '101-1001', 'C', 80.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '101-1002', 'D', 200.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '102-2001', 'C', 1000.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '102-2002', 'D', 70.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '103-3001', 'C', 100.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '104-4001', 'C', 88.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '104-4002', 'C', 88.88 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '106-6001', 'D', 66.66 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '106-6001', 'C', 10.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '106-6002', 'C', 66.66 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '106-6003', 'C', 3000.00 );
INSERT INTO ITSO.CUSTOMERINFO
(customerid, infoid, description,data) VALUES
(101, 1, 'Picture', blob('xxxxxxxxxx')),
(101, 2, 'Scanned image', blob('yyyyyy')) ;
call 切换工作目录.bat
call bin\embedded\ij %~dp0\ejbbank.list
@pauseconnect 'jdbc:db2j:EJBBANK';
SELECT customerid, title, char(firstname,10) as FIRSTNAME, char(lastname,10) as LASTNAME, userid, password from itso.customer;
SELECT * from itso.custaddress;
select customerid, infoid, char(description,15) as DESCRIPTION, length(data) as DATALENGTH, blob(data,10) as DATAEXTRACT from itso.customerinfo;
SELECT * from itso.custacct;
SELECT * from itso.account;
SELECT * from itso.checking;
SELECT * from itso.savings;
SELECT * from itso.transrecord;
exit;Derby批量SQL(内嵌模式)
先开启Javadb服务
然后创建、插入、查询
开启Javadb服务.bat
::开启Javadb服务
java -jar %java_db%\lib\derbyrun.jar server start
@pause::关闭javadb服务
java -jar %java_db%\lib\derbyrun.jar server shutdown
@pause创建表结构.bat
call ij -f ejbbank.dll
@pauseejbbank.ddl
connect 'jdbc:derby:EJBBANK;create=true';
DROP TABLE ITSO.CUSTOMER;
DROP TABLE ITSO.CUSTADDRESS;
DROP TABLE ITSO.CUSTACCT;
DROP TABLE ITSO.ACCOUNT;
DROP TABLE ITSO.CHECKING;
DROP TABLE ITSO.SAVINGS;
DROP TABLE ITSO.TRANSRECORD;
DROP TABLE ITSO.CUSTOMERINFO;
CREATE TABLE ITSO.CUSTOMER (
customerid INTEGER NOT NULL,
title CHAR(3) NOT NULL,
firstName VARCHAR(30) NOT NULL,
lastName VARCHAR(30) NOT NULL,
userID CHAR(8),
password CHAR(8),
address BLOB(2000),
PRIMARY KEY (CUSTOMERID)
);
CREATE TABLE ITSO.CUSTADDRESS (
customerid INTEGER NOT NULL,
street CHAR(20),
city CHAR(12),
state CHAR(12),
zipcode CHAR(10),
PRIMARY KEY (CUSTOMERID)
);
CREATE TABLE ITSO.CUSTACCT (
customerid INTEGER NOT NULL,
accID CHAR(8) NOT NULL,
PRIMARY KEY (CUSTOMERID,ACCID)
);
CREATE TABLE ITSO.ACCOUNT (
accid CHAR(8) NOT NULL,
balance DEC(8,2) NOT NULL DEFAULT 0.00,
interest INTEGER NOT NULL DEFAULT 5,
acctype VARCHAR(8) NOT NULL DEFAULT 'ACCOUNT',
discriminator CHAR(1) NOT NULL DEFAULT 'A',
overdraft DEC(8,2) NOT NULL DEFAULT 200.00,
minamount DEC(8,2) NOT NULL DEFAULT 100.00,
PRIMARY KEY (ACCID)
);
CREATE TABLE ITSO.CHECKING (
accid CHAR(8) NOT NULL,
overdraft DEC(8,2) NOT NULL DEFAULT 200.00,
PRIMARY KEY (ACCID)
);
CREATE TABLE ITSO.SAVINGS (
accid CHAR(8) NOT NULL,
minamount DEC(8,2) NOT NULL DEFAULT 100.00,
PRIMARY KEY (ACCID)
);
CREATE TABLE ITSO.TRANSRECORD (
transid TIMESTAMP NOT NULL,
accid CHAR(8) ,
transtype CHAR(1) NOT NULL,
transamt DEC(8,2) NOT NULL,
PRIMARY KEY (TRANSID)
);
CREATE TABLE ITSO.CUSTOMERINFO (
customerid INTEGER NOT NULL,
infoid INTEGER NOT NULL,
description VARCHAR(50),
data BLOB(10K),
PRIMARY KEY (CUSTOMERID, INFOID)
);
exit;
插入表.bat
call ij -f ejbbank.sql
@pauseejbbank.sql
connect 'jdbc:derby:EJBBANK';
INSERT INTO ITSO.CUSTOMER
(customerid, title, firstname, lastname, userid, password) VALUES
(101, 'Mr', 'Maik', 'Schumacher', 'cust101', 'MS'),
(102, 'Mr', 'Fabio', 'Ferraz', 'cust102', 'FF'),
(103, 'Mr', 'Henrik', 'Sjostrand', 'cust103', 'HS'),
(104, 'Mr', 'Ian', 'Brown', 'cust104', 'IB'),
(105, 'Ms', 'Unknown', 'Lady', null, null),
(106, 'Mr', 'Ueli', 'Wahli', 'cust106', 'UW') ;
INSERT INTO ITSO.CUSTADDRESS
(customerid, street, city, state, zipcode) VALUES
(101, 'A St', 'A village', 'Germany', '11111'),
(102, 'B Bl', 'B big City', 'Brazil', '22222'),
(103, 'C Rd', 'C City', 'Sweden', '33333'),
(104, 'D Ln', 'D Metro', 'Canada', '44444'),
(105, 'E Ct', 'E Farm', 'Hawaii', '55555'),
(106, 'F Av', 'F Town', 'California', '66666');
INSERT INTO ITSO.ACCOUNT
(accid, balance, interest, acctype, discriminator) VALUES
('101-1001', 80.00, 4, 'CHECKING', 'C'),
('101-1002', 375.26, 5, 'SAVINGS', 'S'),
('102-2001', 9375.26, 5, 'SAVINGS', 'S'),
('102-2002', 75.50, 3, 'CHECKING', 'C'),
('103-3001', 100.00, 6, 'SAVINGS', 'S'),
('104-4001', 888.88, 4, 'SAVINGS', 'S'),
('104-4002', 88.88, 4, 'CHECKING', 'C'),
('105-5001', 0.00, 2, 'CHECKING', 'C'),
('106-6001', 1000.00, 3, 'CHECKING', 'C'),
('106-6002', 2000.00, 4, 'SAVINGS', 'S'),
('106-6003', 3000.00, 6, 'SAVINGS', 'S');
INSERT INTO ITSO.CHECKING
(accid, overdraft) VALUES
('101-1001',200.00),
('102-2002',200.00),
('104-4002',250.00),
('105-5001',200.00),
('106-6001',300.00);
INSERT INTO ITSO.SAVINGS
(accid, minamount) VALUES
('101-1002',100.00),
('102-2001',100.00),
('103-3001',150.00),
('104-4001',200.00),
('106-6002',100.00),
('106-6003',250.00);
INSERT INTO ITSO.CUSTACCT
(customerid, accid) VALUES
(101,'101-1001'), (101,'101-1002'),
(102,'102-2001'), (102,'102-2002'),
(103,'103-3001'),
(104,'104-4001'), (104,'104-4002'),
(105,'105-5001'),
(106,'106-6001'), (106,'106-6002'), (106,'106-6003'), (106,'105-5001');
INSERT INTO ITSO.TRANSRECORD
(transid, accid, transtype, transamt) VALUES
(CURRENT TIMESTAMP, '101-1001', 'C', 80.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '101-1002', 'D', 200.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '102-2001', 'C', 1000.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '102-2002', 'D', 70.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '103-3001', 'C', 100.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '104-4001', 'C', 88.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '104-4002', 'C', 88.88 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '106-6001', 'D', 66.66 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '106-6001', 'C', 10.00 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '106-6002', 'C', 66.66 );
INSERT INTO ITSO.TRANSRECORD VALUES
(CURRENT TIMESTAMP, '106-6003', 'C', 3000.00 );
INSERT INTO ITSO.CUSTOMERINFO
(customerid, infoid, description,data) VALUES
(101, 1, 'Picture', blob('xxxxxxxxxx')),
(101, 2, 'Scanned image', blob('yyyyyy')) ;
call ij -f ejbbank.list
@pauseejbbank.list
connect 'jdbc:derby:EJBBANK';
SELECT customerid, title, char(firstname,10) as FIRSTNAME, char(lastname,10) as LASTNAME, userid, password from itso.customer;
SELECT * from itso.custaddress;
select customerid, infoid, char(description,15) as DESCRIPTION, length(data) as DATALENGTH, blob(data,10) as DATAEXTRACT from itso.customerinfo;
SELECT * from itso.custacct;
SELECT * from itso.account;
SELECT * from itso.checking;
SELECT * from itso.savings;
SELECT * from itso.transrecord;
exit;DB2数据库批量SQL
createbank.bat
db2cmd.exe db2setcp.bat db2 -f ejbbank.ddlecho --- create the EJBBANK database ---
CREATE DATABASE EJBBANK
echo --- connect to EJBBANK database ---
CONNECT TO EJBBANK
echo --- drop tables and triggers ---
DROP TABLE ITSO.TRANSRECORD
DROP TABLE ITSO.CUSTACCT
DROP TABLE ITSO.CUSTADDRESS
DROP TABLE ITSO.CHECKING
DROP TABLE ITSO.SAVINGS
DROP TABLE ITSO.ACCOUNT
DROP TABLE ITSO.CUSTOMER
DROP TABLE ITSO.CUSTOMERINFO
DROP TRIGGER ITSO.INSERTACCOUNT
DROP TRIGGER ITSO.UPDATEACCOUNT
echo --- create tables ---
CREATE TABLE ITSO.CUSTOMER ( \
customerid INTEGER NOT NULL, \
title CHAR(3) NOT NULL, \
firstName VARCHAR(30) NOT NULL, \
lastName VARCHAR(30) NOT NULL, \
userID CHAR(8), \
password CHAR(8), \
address BLOB(2000), \
PRIMARY KEY (CUSTOMERID) \
)
CREATE TABLE ITSO.CUSTADDRESS ( \
customerid INTEGER NOT NULL, \
street CHAR(20), \
city CHAR(12), \
state CHAR(12), \
zipcode CHAR(10), \
PRIMARY KEY (CUSTOMERID) \
)
CREATE TABLE ITSO.CUSTACCT ( \
customerid INTEGER NOT NULL, \
accID CHAR(8) NOT NULL, \
PRIMARY KEY (CUSTOMERID,ACCID) \
)
CREATE TABLE ITSO.ACCOUNT ( \
accid CHAR(8) NOT NULL, \
balance DEC(8,2) NOT NULL DEFAULT 0.00, \
interest INTEGER NOT NULL DEFAULT 5, \
acctype VARCHAR(8) NOT NULL DEFAULT 'ACCOUNT', \
discriminator CHAR(1) NOT NULL DEFAULT 'A', \
overdraft DEC(8,2) NOT NULL DEFAULT 200.00, \
minamount DEC(8,2) NOT NULL DEFAULT 100.00, \
PRIMARY KEY (ACCID) \
)
CREATE TABLE ITSO.CHECKING ( \
accid CHAR(8) NOT NULL, \
overdraft DEC(8,2) NOT NULL DEFAULT 200.00, \
PRIMARY KEY (ACCID) \
)
CREATE TABLE ITSO.SAVINGS ( \
accid CHAR(8) NOT NULL, \
minamount DEC(8,2) NOT NULL DEFAULT 100.00, \
PRIMARY KEY (ACCID) \
)
CREATE TABLE ITSO.TRANSRECORD ( \
transid TIMESTAMP NOT NULL, \
accid CHAR(8) , \
transtype CHAR(1) NOT NULL, \
transamt DEC(8,2) NOT NULL, \
PRIMARY KEY (TRANSID) \
)
CREATE TABLE ITSO.CUSTOMERINFO ( \
customerid INTEGER NOT NULL, \
infoid INTEGER NOT NULL, \
description VARCHAR(50), \
data BLOB(10K), \
PRIMARY KEY (CUSTOMERID, INFOID) \
)
echo --- referential integrity ---
ALTER TABLE ITSO.TRANSRECORD \
ADD CONSTRAINT "AccountTransrecord" FOREIGN KEY (ACCID) \
REFERENCES ITSO.ACCOUNT ON DELETE RESTRICT
ALTER TABLE ITSO.CUSTACCT \
ADD CONSTRAINT "CAtoCustomer" FOREIGN KEY (CUSTOMERID) \
REFERENCES ITSO.CUSTOMER ON DELETE RESTRICT
ALTER TABLE ITSO.CUSTACCT \
ADD CONSTRAINT "CAtoAccount" FOREIGN KEY (ACCID) \
REFERENCES ITSO.ACCOUNT ON DELETE RESTRICT
ALTER TABLE ITSO.CUSTADDRESS \
ADD CONSTRAINT "CustAddr" FOREIGN KEY (CUSTOMERID) \
REFERENCES ITSO.CUSTOMER ON DELETE RESTRICT
ALTER TABLE ITSO.CHECKING \
ADD CONSTRAINT "CheckingAccount" FOREIGN KEY (ACCID) \
REFERENCES ITSO.ACCOUNT ON DELETE RESTRICT
ALTER TABLE ITSO.SAVINGS \
ADD CONSTRAINT "SavingsAccount" FOREIGN KEY (ACCID) \
REFERENCES ITSO.ACCOUNT ON DELETE RESTRICT
echo --- triggers -------------------
CREATE TRIGGER ITSO.INSERTACCOUNT NO CASCADE BEFORE INSERT \
ON ITSO.ACCOUNT referencing new as ACCT for each row mode db2sql \
BEGIN ATOMIC \
SET ACCT.acctype = \
CASE \
WHEN ACCT.discriminator = 'C' THEN 'CHECKING' \
WHEN ACCT.discriminator = 'S' THEN 'SAVINGS' \
WHEN ACCT.discriminator = 'A' THEN 'ACCOUNT' \
END; \
END
CREATE TRIGGER ITSO.UPDATEACCOUNT NO CASCADE BEFORE UPDATE \
ON ITSO.ACCOUNT referencing new as ACCT for each row mode db2sql \
BEGIN ATOMIC \
SET ACCT.acctype = \
CASE \
WHEN ACCT.discriminator = 'C' THEN 'CHECKING' \
WHEN ACCT.discriminator = 'S' THEN 'SAVINGS' \
WHEN ACCT.discriminator = 'A' THEN 'ACCOUNT' \
END; \
END
echo --- execute GRANT statements ---
GRANT CONNECT ON DATABASE TO PUBLIC
GRANT ALL ON ITSO.CUSTOMER TO PUBLIC
GRANT ALL ON ITSO.ACCOUNT TO PUBLIC
GRANT ALL ON ITSO.CHECKING TO PUBLIC
GRANT ALL ON ITSO.SAVINGS TO PUBLIC
GRANT ALL ON ITSO.TRANSRECORD TO PUBLIC
GRANT ALL ON ITSO.CUSTACCT TO PUBLIC
GRANT ALL ON ITSO.CUSTADDRESS TO PUBLIC
GRANT ALL ON ITSO.CUSTOMERINFO TO PUBLIC
echo --- connect reset ---
CONNECT RESETloadbank.bat
db2cmd.exe db2setcp.bat db2 -f ejbbank.sqlecho --- load the EJBBANK database ---
echo --- connect to EJBBANK database ---
CONNECT TO EJBBANK
DELETE FROM ITSO.TRANSRECORD
DELETE FROM ITSO.SAVINGS
DELETE FROM ITSO.CHECKING
DELETE FROM ITSO.CUSTACCT
DELETE FROM ITSO.ACCOUNT
DELETE FROM ITSO.CUSTADDRESS
DELETE FROM ITSO.CUSTOMER
DELETE FROM ITSO.CUSTOMERINFO
echo --- insert into CUSTOMER tables ---
INSERT INTO ITSO.CUSTOMER \
(customerid, title, firstname, lastname, userid, password) VALUES \
(101, 'Mr', 'Maik', 'Schumacher', 'cust101', 'MS'), \
(102, 'Mr', 'Fabio', 'Ferraz', 'cust102', 'FF'), \
(103, 'Mr', 'Henrik', 'Sjostrand', 'cust103', 'HS'), \
(104, 'Mr', 'Ian', 'Brown', 'cust104', 'IB'), \
(105, 'Ms', 'Unknown', 'Lady', null, null), \
(106, 'Mr', 'Ueli', 'Wahli', 'cust106', 'UW')
INSERT INTO ITSO.CUSTADDRESS \
(customerid, street, city, state, zipcode) VALUES \
(101, 'A St', 'A village', 'Germany', '11111'), \
(102, 'B Bl', 'B big City', 'Brazil', '22222'), \
(103, 'C Rd', 'C City', 'Sweden', '33333'), \
(104, 'D Ln', 'D Metro', 'Canada', '44444'), \
(105, 'E Ct', 'E Farm', 'Hawaii', '55555'), \
(106, 'F Av', 'F Town', 'California', '66666')
echo --- insert into ACCOUNT tables ---
INSERT INTO ITSO.ACCOUNT \
(accid, balance, interest, acctype, discriminator) VALUES \
('101-1001', 80.00, 4, 'CHECKING', 'C'), \
('101-1002', 375.26, 5, 'SAVINGS', 'S'), \
('102-2001', 9375.26, 5, 'SAVINGS', 'S'), \
('102-2002', 75.50, 3, 'CHECKING', 'C'), \
('103-3001', 100.00, 6, 'SAVINGS', 'S'), \
('104-4001', 888.88, 4, 'SAVINGS', 'S'), \
('104-4002', 88.88, 4, 'CHECKING', 'C'), \
('105-5001', 0.00, 2, 'CHECKING', 'C'), \
('106-6001', 1000.00, 3, 'CHECKING', 'C'), \
('106-6002', 2000.00, 4, 'SAVINGS', 'S'), \
('106-6003', 3000.00, 6, 'SAVINGS', 'S')
INSERT INTO ITSO.CHECKING \
(accid, overdraft) VALUES \
('101-1001',200.00), \
('102-2002',200.00), \
('104-4002',250.00), \
('105-5001',200.00), \
('106-6001',300.00)
INSERT INTO ITSO.SAVINGS \
(accid, minamount) VALUES \
('101-1002',100.00), \
('102-2001',100.00), \
('103-3001',150.00), \
('104-4001',200.00), \
('106-6002',100.00), \
('106-6003',250.00)
echo --- insert into CUSTACCT table ---
INSERT INTO ITSO.CUSTACCT \
(customerid, accid) VALUES \
(101,'101-1001'), (101,'101-1002'), \
(102,'102-2001'), (102,'102-2002'), \
(103,'103-3001'), \
(104,'104-4001'), (104,'104-4002'), \
(105,'105-5001'), \
(106,'106-6001'), (106,'106-6002'), (106,'106-6003'), (106,'105-5001')
echo --- insert into TRANSRECORD table ---
INSERT INTO ITSO.TRANSRECORD \
(transid, accid, transtype, transamt) VALUES \
(CURRENT TIMESTAMP, '101-1001', 'C', 80.00 )
INSERT INTO ITSO.TRANSRECORD VALUES \
(CURRENT TIMESTAMP, '101-1002', 'D', 200.00 )
INSERT INTO ITSO.TRANSRECORD VALUES \
(CURRENT TIMESTAMP, '102-2001', 'C', 1000.00 )
INSERT INTO ITSO.TRANSRECORD VALUES \
(CURRENT TIMESTAMP, '102-2002', 'D', 70.00 )
INSERT INTO ITSO.TRANSRECORD VALUES \
(CURRENT TIMESTAMP, '103-3001', 'C', 100.00 )
INSERT INTO ITSO.TRANSRECORD VALUES \
(CURRENT TIMESTAMP, '104-4001', 'C', 88.00 )
INSERT INTO ITSO.TRANSRECORD VALUES \
(CURRENT TIMESTAMP, '104-4002', 'C', 88.88 )
INSERT INTO ITSO.TRANSRECORD VALUES \
(CURRENT TIMESTAMP, '106-6001', 'D', 66.66 )
INSERT INTO ITSO.TRANSRECORD VALUES \
(CURRENT TIMESTAMP, '106-6001', 'C', 10.00 )
INSERT INTO ITSO.TRANSRECORD VALUES \
(CURRENT TIMESTAMP, '106-6002', 'C', 66.66 )
INSERT INTO ITSO.TRANSRECORD VALUES \
(CURRENT TIMESTAMP, '106-6003', 'C', 3000.00 )
echo --- insert into CUSTOMERINFO table ---
INSERT INTO ITSO.CUSTOMERINFO \
(customerid, infoid, description,data) VALUES \
(101, 1, 'Picture', blob('xxxxxxxxxx')), \
(101, 2, 'Scanned image', blob('yyyyyy'))
echo --- connect reset ---
CONNECT RESETlistbank.bat
db2cmd.exe db2setcp.bat db2 -fejbbank.listejbbank.list
echo --- list the EJBBANK database ---
CONNECT TO EJBBANK
echo - Customer -
SELECT customerid, title, char(firstname,10) as FIRSTNAME, char(lastname,10) as LASTNAME, userid, password from itso.customer
echo - Address-
SELECT * from itso.custaddress
echo - CustomerInfo -
select customerid, infoid, char(description,15) as DESCRIPTION, length(data) as DATALENGTH, blob(data,10) as DATAEXTRACT from itso.customerinfo
echo - CustomerAccount -
SELECT * from itso.custacct
echo - Account-
SELECT * from itso.account
echo - Checking -
SELECT * from itso.checking
echo - Savings -
SELECT * from itso.savings
echo - TransRecord -
SELECT * from itso.transrecord
扫一扫
896
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
