启动
[root@localhost ~]# su - db2inst1
[db2inst1@localhost ~]$ db2start
SQL8007W There are "13" day(s) left in the evaluation period for the product
"DB2 Connect Server". For evaluation license terms and conditions, refer to
the License Agreement document located in the license directory in the
installation path of this product. If you have licensed this product, ensure
the license key is properly registered. You can register the license via the
License Center or db2licm command line utility. The license key can be
obtained from your licensed product CD.
SQL1063N DB2START processing was successful.
[db2inst1@localhost ~]$
创建数据库
[db2inst1@localhost ~]$ db2 create database hibernate;
SQL1001N "hibernate" is not a valid database name. SQLSTATE=2E000
数据库名字应该是1-8个字符
[db2inst1@localhost ~]$ db2 create database hiber;
DB20000I The CREATE DATABASE command completed successfully.
列出数据库
[db2inst1@localhost ~]$ db2 list db directory
System Database Directory
Number of entries in the directory = 3
Database 1 entry:
Database alias = TESTDB
Database name = TESTDB
Local database directory = /home/db2inst1-m
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = HIBER
Database name = HIBER
Local database directory = /home/db2inst1-m
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 3 entry:
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /home/db2inst1-m
Database release level = d.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
[db2inst1@localhost ~]$
连接数据库
[db2inst1@localhost ~]$ db2 connect to hiber;
Database Connection Information
Database server = DB2/LINUX 9.7.6
SQL authorization ID = DB2INST1
Local database alias = HIBER
[db2inst1@localhost ~]$
列出表
[db2inst1@localhost ~]$ db2 list tables;
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
0 record(s) selected.
[db2inst1@localhost ~]$
创建表
[db2inst1@localhost ~]$ db2 "create table tb_user(id integer primary key, user_name varchar(10) not null, user_age integer not null)"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0542N The column named "ID" cannot be a column of a primary key or unique
key constraint because it can contain null values. SQLSTATE=42831
[db2inst1@localhost ~]$
这个在oracle里是可以执行的，但是在db2上，primary key的字段一定要指明not null才行：
[db2inst1@localhost ~]$ db2 "create table tb_user(id integer not null primary key, user_name varchar(10) not null, user_age integer not null)"
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$
执行代码块
db2 -td@表示命令以@结束
[db2inst1@localhost ~]$ db2 -td@
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.6
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => begin atomic
db2 (cont.) => declare v_id int;
db2 (cont.) => declare v_name varchar(10);
db2 (cont.) => declare v_age int;
db2 (cont.) => set v_id=1;
db2 (cont.) => while (v_id<=100) do
db2 (cont.) => insert into tb_user values(v_id, 'user_' || v_id, v_id);
db2 (cont.) => set v_id=v_id+1;
db2 (cont.) => end while;
db2 (cont.) => end @
DB20000I The SQL command completed successfully.
db2 =>
取得前N条数据
db2 => select * from tb_user fetch first 10 rows only@
ID USER_NAME USER_AGE
----------- ---------- -----------
1 user_1 1
2 user_2 2
3 user_3 3
4 user_4 4
5 user_5 5
6 user_6 6
7 user_7 7
8 user_8 8
9 user_9 9
10 user_10 10
10 record(s) selected.
db2 =>
清空表truncate
db2 => truncate table tb_user immediate@
DB20000I The SQL command completed successfully.
这跟oracle不同
存储过程
创建
db2 => create procedure pro_insert_user(in row_count integer)
db2 (cont.) => begin
db2 (cont.) => declare v_id int;
db2 (cont.) => declare v_name varchar(10);
db2 (cont.) => declare v_age int;
db2 (cont.) => set v_id=1;
db2 (cont.) => while (v_id<=row_count) do
db2 (cont.) => insert into tb_user values(v_id, 'user_' || v_id, v_id);
db2 (cont.) => set v_id=v_id+1;
db2 (cont.) => end while;
db2 (cont.) => end @
DB20000I The SQL command completed successfully.
调用
db2 => call pro_insert_user(10)@
Return Status = 0
控制语句if else
db2 => create procedure pro_insert_user(in row_count integer)
db2 (cont.) => begin
db2 (cont.) => declare v_id int;
db2 (cont.) => declare v_name varchar(10);
db2 (cont.) => declare v_age int;
db2 (cont.) => declare v_count int;
db2 (cont.) => declare v_cnt int;
db2 (cont.) => set v_count = 1;
db2 (cont.) => set v_cnt = 0;
db2 (cont.) => select count(id) into v_cnt from tb_user;
db2 (cont.) => if (v_cnt >= 1) then
db2 (cont.) => select max(id)+1 into v_id from tb_user;
db2 (cont.) => else
db2 (cont.) => set v_id = 1;
db2 (cont.) => end if;
db2 (cont.) => while (v_count<=row_count) do
db2 (cont.) => select cast(rand()*100 as int) into v_age from sysibm.sysdummy1;
db2 (cont.) => insert into tb_user values(v_id, 'user_' || v_id, v_age);
db2 (cont.) => set v_id=v_id+1;
db2 (cont.) => set v_count=v_count+1;
db2 (cont.) => end while;
db2 (cont.) => end @
DB20000I The SQL command completed successfully.
db2 =>
事务控制
默认是自动提交的，无须commit;
db2 => list command options@
Command Line Processor Option Settings
Backend process wait time (seconds) (DB2BQTIME) = 1
No. of retries to connect to backend (DB2BQTRY) = 60
Request queue wait time (seconds) (DB2RQTIME) = 5
Input queue wait time (seconds) (DB2IQTIME) = 5
Command options (DB2OPTIONS) =
Option Description Current Setting
------ ---------------------------------------- ---------------
-a Display SQLCA OFF
-c Auto-Commit ON
-d Retrieve and display XML declarations OFF
-e Display SQLCODE/SQLSTATE OFF
-f Read from input file OFF
-i Display XML data with indentation OFF
-l Log commands in history file OFF
-m Display the number of rows affected OFF
-n Remove new line character OFF
-o Display output ON
-p Display interactive input prompt ON
-q Preserve whitespaces & linefeeds OFF
-r Save output to report file OFF
-s Stop execution on command error OFF
-t Set statement termination character ON
-v Echo current command OFF
-w Display FETCH/SELECT warning messages ON
-x Suppress printing of column headings OFF
-z Save all output to output file OFF
db2 =>
不要自动提交
db2 => update command options using c off@
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
db2 => list command options@
Command Line Processor Option Settings
Backend process wait time (seconds) (DB2BQTIME) = 1
No. of retries to connect to backend (DB2BQTRY) = 60
Request queue wait time (seconds) (DB2RQTIME) = 5
Input queue wait time (seconds) (DB2IQTIME) = 5
Command options (DB2OPTIONS) =
Option Description Current Setting
------ ---------------------------------------- ---------------
-a Display SQLCA OFF
-c Auto-Commit OFF
-d Retrieve and display XML declarations OFF
-e Display SQLCODE/SQLSTATE OFF
-f Read from input file OFF
-i Display XML data with indentation OFF
-l Log commands in history file OFF
-m Display the number of rows affected OFF
-n Remove new line character OFF
-o Display output ON
-p Display interactive input prompt ON
-q Preserve whitespaces & linefeeds OFF
-r Save output to report file OFF
-s Stop execution on command error OFF
-t Set statement termination character ON
-v Echo current command OFF
-w Display FETCH/SELECT warning messages ON
-x Suppress printing of column headings OFF
-z Save all output to output file OFF
db2 =>