db2 创建数据库、表、存储过程等

最新推荐文章于 2024-06-24 13:53:43 发布

feier7501

最新推荐文章于 2024-06-24 13:53:43 发布

阅读量6k

点赞数 2

分类专栏： DB2

本文链接：https://blog.csdn.net/feier7501/article/details/25162013

版权

DB2 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

启动

[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 =>