最近在看db2,边读边写了一些,记下来,虽然写的乱七八糟。以备后用。 

这些都写的很简单。我觉得也算是一些简单的操作吧,有些也是摘自别人的blog具体是引用哪的就不太记得了。 


一、DB2两种注释写法 

 -----注释 

 /*注释*/ 

 注:看起来db2的数据库架构和oracle有些相像,有用户和数据库的概念。比如我刚刚在J_TEST这个数据库中建了个表形式如下,create table table_name(字段列表);然后我在对想视图中看到table_name的模式是abc(即是我登录该操作系统的ID),而我如果查询select * from obpm_rt.table_name是查不出来的,即是表示table_name是属于abc这个帐户其它用户不可见,虽然是处于同一个数据库中。 


二、db2的基本数据类型 

 数值型 

 *小整型,SMALLINT: 整型是两个字节的整数,精度为 5 位。 

 *大整型,INTEGER: 或 INT 大整型是四个字节的整数,精度为 10 位。 

 *巨整型,BIGINT:巨整型是八个字节的整数,精度为 19 位。 

 *小数型,DECIMAL(p,s)、DEC(p,s)、NUMERIC(p,s) 或 NUM(p,s): 

 *单精度浮点数 REAL 

 *双精度浮点数 DOUBLE,DOUBLE PRECISION 或 FLOAT 


 字符串型 

 *定长字符串:CHARACTER(n) 或 CHAR(n):定长字符串的长度介于1 到254字节之间。默认1个字节。 

 *变长字符串,VARCHAR(n)、CHARACTER VARYING(n) 或 CHAR VARYING(n):VARCHAR(n) 类型的字符串是变长字符串,最长可达 32,672 字节。 

 *LONG VARCHAR:LONG VARCHAR 类型的字符串是变长字符串,最长可达 32,700 字节。 

 *字符大对象字符串 CLOB(n):CLOB 是变长字符串,最长可以达到 2,147,483,647 字节。如果只指定了 n,那么 n 的值就是最大长度。 

 *二进制大对象,BLOB(n[K|M|G]):二进制大对象是变长字符串,最长可达 2,147,483,647 字节。如果只指定了 n,那么 n 就是最大长度。 



 日期时间型数据类型 

 *DATE:DATE 是一个由三部分组成的值(年、月和日)。 

 *TIME:TIME 是一个由三部分组成的值(小时、分钟和秒)。 

 *TIMESTAMP:TIMESTAMP 是一个由七部分组成的值(年、月、日、小时、分钟、秒和微秒)。 

 *日期值的字符串表示是一个以数字开始,长度不少于 8 个字符的字符串。日期值的月份和日部分中前面的零可以省略。 

 *时间值的字符串表示是以数字开头,长度不少于 4 个字符的字符串。时间值的小时部分前面的零可以省略,秒部分可以完全省略。如果秒的值没有指定,那么就认为是 0。 

 *时间戳记值的字符串表示是以数字开头,长度不少于 16 个字符的字符串。完整的时间戳记字符串表示形式为 yyyy-mm-dd-hh.mm.ss.nnnnnn。 

 时间戳记值的月、日或小时等几部分前面的零可以省略,微秒可以截断或完全省略。如果任何时间戳记值的微秒部分尾零被省略掉了,那么将假定空缺的数位上是零。 


example: 

 create table db2admin.t_test1(id smallint,name character(10),addr clob(1000),indate date); 

 insert into db2admin.t_test1(id,name,addr,indate) values(001,'jenny','ccccccccc','2009-10-01'); 


三、db2 sql 一些语法 


 declare :定义变量 和 定义出错处理 (declare必需要 begin后面才用,而且db2没有匿名块的概念) 

 格式:declare sql-var-name data-type default default-values 


 SET : SET 语句来为变量和数组元素赋值 

 格式:SET variable_name = value/expression/NULL; 

 例子:SET total = (select sum(c1) from table); 


 VALUE INTO: 为变量赋值 

 格式:VALUES 值 INTO 变量名; 

 例子:VALUES 'TEST' INTO v1; 


 SELECT INTO : 为变量赋值 

 格式:SELECT 字段/函数 INTO 变量名 FROM TABLE; 

 例子:SELECT COUNT(*) INTO var1 FROM T1; 


 返回当前日期和时间(专用寄存器实现),类似于oracle的sysdate之类的系统函数,可以在SQL 语句或 SQL PL 语句中访问和引用: 

 CURRENT DATE 也可写成 CURRENT_DATE 

 CURRENT TIME 也可写成 CURRENT_TIME 

 CURRENT TIMESTAMP 也可写成 CURRENT_TIMESTAMP 

 CURRENT USER 也可写成 CURRENT_USER 

 CURRENT PATH 也可写成 CURRENT_PATH 


四,db2的行号写法 

 ROW_NUMBER() OVER () AS NUMBER 


五,如何在命令符下提交存储过程 

cmd -->db2cmd 

在存储过程的最后加上@符号,然后在命令符下打入:db2 -td@ -vf procfile.sql 就可以生成过程。 

非存储过程的SQL文件,在命令符下打入:db2 –tvf sqlfile.sql 


六,db2游标 

 格式:DECLARE cursor_name CURSOR FOR Select-statement [WITHOUT HOLD]/[WITH HOLD] [WITHOUT RETURN]/[WITH RETURN] [TO CALLER]/[TO CLIENT] 

 关键字解释: 

 Select-statement: 是一条有效的 SQL SELECT 语句 

 WITHOUT HOLD/WITH HOLD: 选项定义 COMMIT 操作之后的游标状态(open/close)。默认情况下为 WITHOUT HOLD。 

 如果使用了 WITH HOLD 选项定义一个游标,那么在 COMMIT 操作之后,该游标保持 OPEN 状态。 

 在 ROLLBACK 操作之后,所有游标都将被关闭。 

 WITHOUT RETURN/WITH return 选项指定游标的结果表是否用于作为从一个过程中返回的结果集。 

 WITH RETURN TO CALLER 选项指定将来自游标的结果集返回给调用者,后者可以是另一个过程或一个客户机应用程序。这是默认选项。\ 

 WITH RETURN TO CLIENT 选项指定将来自游标的结果集返回给客户机应用程序,绕过任何中间的嵌套过程。 


 若要从一个过程中返回结果集,需要: 

 创建一个过程,创建时指定 DYNAMIC RESULT SETS 子句。 

 声明游标,声明时指定 WITH RETURN 子句。 

 打开该游标,并使之保持 open 状态。 

 如果关闭该游标,则结果集将不能返回给调用者应用程序。 


 游标处理 

 为了在一个过程中处理一个游标的结果,需要做以下事情: 

 在存储过程块的开头部分 DECLARE 游标。 

 打开该游标。 

 将游标的结果取出到之前已声明的本地变量中。 

 关闭该游标。(注意:如果现在不关闭游标,当过程终止时将隐式地关闭游标)。 


七.选择、分支、循环语法 

 1)if then else (和oracle写法一样) 

 IF 条件 THEN 

 语句 

 ELSEIF 条件 THEN 

 语句 

 ELSE 

 语句 

 END IF; 

 2)case (和oracle写法一样) 

 case 

 when 条件 then 语句 

 . 

 . 

 . 

 else 语句 end case; 


 3)loop 循环 (有四种LOOP,WHILE ,REPEAT ,FOR,这里只介绍loop和for ) 

 loop_name: LOOP 

 SQL statements; 

 LEAVE loop_name; 

 END LOOP loop_name 


 典型的cursor loop格式: 

 OPEN cursor_name; 

 loop_name: LOOP 

 FETCH cursor_name INTO 变量名(可以多个之间用逗号分隔); 


 IF end_ <> 0 THEN -- 到达最后一行时退出循环 

 LEAVE loop_name; 


 END IF; 

 循环执行语句 

 END LOOP loop_name; 


 4)for 结果集上的隐式循环(db2admin.p_for_example) 

 FOR loop_name AS SELECT … FROM 

 DO 

 SQL statements; 

 END FOR; 



八、存储过程 

 格式:CREATE PROCEDURE procedure_name([IN/OUT/INOUT paraname TYPE ]) 

 SPECIFIC specific_name 

 DYNAMIC RESULT SETS integer 

 MODIFIES SQL DATA/CONTAINS SQL/READS SQL DATA 

 NOT DETERMINISTIC/DETERMINISTIC 

 CALLED ON NULL INPUT 

 LANGUAGE SQL 

 PARAMETER CCSID/ ASCII/UNICODE 

 BEGIN [NOT] ATOMIC 

 SQL-procedure-body: 

 END 


 关键字解释: 

 procedure-name: 存储过程的名字,在同一个数据库的同一模式下,不能存在存储过程名相同参数数目相同的存储过程,即使参数的类型不同也不行。 

  (IN | OUT | INOUT parameter-name data-type,...) :传入参数 

   IN:输入参数 

   OUT:输出参数 

   INOUT:作为输入输出参数 

  parameter-name:参数名字,在此存储过程中唯一的标识符。 

  data-type:参数类型,可以接收SQL类型和创建的表。不支持LONG VARCHAR, LONG VARGRAPHIC, DATALINK, REFERENCE和用户自定义类型。 

  SPECIFIC specific-name:唯一的特定名称(别名),可以用存储过程名代替,这个特定名称用于dorp存储过程,或者给存储过程添加注视用,但不能调用存储过程。如果不指定,则数据库会自动生成一个yymmddhhmmsshhn时间戳的名字。推荐给出别名。 

  DYNAMIC RESULT SETS integer:指定存储过程返回结果的最大数量。存储过程中虽然没有return语句,但是却能返回结果集。 

  CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA: 指定存储过程中的SQL访问级别 

  CONTAINS SQL: 表示存储过程可以执行中,既不可读取 SQL 数据,也不可修改 SQL 数据。 

  READS SQL DATA: 表示存储过程可以执行中,可读取SQL,但不可修改 SQL 数据。 

  MODIFIES SQL DATA: 表示存储过程可以执行任何 SQL 语句。可以对数据库中的数据进行增加、删除和修改。 

  DETERMINISTIC or NOT DETERMINISTIC:表示存储过程是动态或者非动态的。动态的返回的值是不确定的。非动态的存储过程每次执行返回的值是相同的。 

  CALLED ON NULL INPUT:表示可以调用存储过程而不管任何的输入参数是否为NULL,并且,任何的OUT或者INOUT参数可以返回一个NULL或者非空值。检验参数是否为NULL是在过程中进行的。 

 LANGUAGE SQL:指定程序的主体用的是SQL语言。 

  EXTERNAL ACTION or NO EXTERNAL ACTION:表示存储过程是否执行一些改变理数据库状态的活动,而不通过数据库管理器管。默认是 

  EXTERNAL ACTION。如果指定为NO EXTERNAL ACTION ,则数据库会确定最最佳优化方案。 

  PARAMETER CCSID:指定所有输出字符串数据的编码,默认为UNICODE编码数据库为PARAMETER CCSID UNICODE,其他的数据库默认为PARAMETER CCSID 3 ASCII。 


九、get db cfg for databasename 获取数据库的配置信息 

 update db cfg for testdatabase using logfilsiz 6000 修改日志文件大小 

 db2 ? sql964 根据错误码查看错误解释 


十、SQL 函数(据实验不能用case语句) 

 CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> ) 

 RETURNS [[OutputDataType] | 

 TABLE ( [ColumnName] [ColumnDataType] ,... ) | 

 ROW ( [ColumnName] [ColumnDataType] ,... )] 

 <SPECIFIC [SpecificName]> 

 <LANGUAGE SQL> 

 <DETERMINISTIC | NOT DETERMINISTIC> 

 <EXTERNAL ACTION | NO EXTERNAL ACTION> 

 <CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA> 

 <STATIC DISPATCH> 

 <CALLED ON NULL INPUT> 

 [SQLStatements] | RETURN [ReturnStatement] 


 FunctionName 指定要创建的 SQL 函数的名称。 

 ParameterName 指定一个或多个函数参数的名称。 

 InputDataType 指定 ParameterName 所识别的参数所需的数据类型。 

 OutputDataType 指定函数返回的数据的类型,这种情况是返回单值 

 TABLE 指表函数,返回一个表,调用方法 select * from TABLE(function_name(parameter)) alias 

 ROW 行函数, 返回一行,调用方法暂时不详 

 ColumnName 指定函数返回的一列或多列的名称(如果此函数返回表或行的话)。 

 ColumnDataType 指定 ColumnName 所识别的列返回的数据类型。 

 SpecificName 指定分配给这个 UDF 的特定名称。这个名称可以用来引用或删除函数;但是,不能用来调用函数。 

 SQLStatements 指定在调用函数时执行的一个或多个 SQL 语句。这些语句组合成一个动态复合 SQL 语句。 

 ReturnStatement 指定用于返回调用函数的应用程序的 RETURN SQL 语句。(如果 SQL 函数体由动态复合语句组成, 那么它必须包含至少一个 RETURN 语句;在调用函数时,必须执行一个 RETURN 语句。如果函数是表函数或行函数, 那么只能包含一个 RETURN 语句,而且此语句必须是使用的最后一个语句)。 


十一、字符串相加和oracle一相用|| 


十二、动态sql执行 

 相对于静态SQL 语句,动态SQL 在运行时是无法得知其全部的SQL 语句的。例如如果col1 和tabname 是语句中的两个变量,那么我们正在处理动态SQL: 

 'SELECT ' || col1 || ' FROM ' || tabname; 

 在 DDL 中建议使用动态SQL,以避免从属问题和包无效问题。执行递归操作时也需要动态SQL. 

 可以通过以下两种方法执行动态SQL: 

 • 使用 EXECUTE IMMEDATE 语句– 对于单独执行的SQL 这是理想的方法(和oracle一样,只不过oracel好像只有这一种执行方法) 

 • 在 EXECUTE 语句之前用PREPARE 语句 – 对于多重执行的SQL 语句此方法比较理想。 

 CREATE PROCEDURE dyn1 (IN value1 INT, IN value2 INT) 

 SPECIFIC dyn1 

 BEGIN 

 DECLARE stmt varchar(255); 

 DECLARE st STATEMENT; 

 SET stmt = 'INSERT INTO T2 VALUES (?, ?)'; 

 PREPARE st FROM stmt; 

 EXECUTE st USING value1, value1; 

 EXECUTE st USING value2, value2; 

 SET stmt = INSERT INTO T2 VALUES (9,9)'; 

 EXECUTE IMMEDIATE stmt; 

 END 


十三、db2用单引号中的数据作为字符串,如果要输入单引号则需输入两个,第一个作为了转义字符(和oracle一样) 


十四、db2的用户和oracle用户的比较 

 一个数据库只对应一个实例,但是一个实例可以有多个数据库(和oracle一样),差别比较大的应该是用户的定义不太一样 


 db2:一定得是系统用户 

 oracle:可以用具有dba权限的用户新建用户,不一定非得操作系统用户 


 db2:每个用户新建的表都是从属于该用户,但是A用户可以新建查询更新B用户的数据。只需要前面增加B用户标识感觉db2是所有数据都是放在同一个数据库,只是建表的时候把当前用户id当作了表名的一部分 

 oracle:每个用户新建的表都是从属于该用户,别的用户不能对其它用户的数据进行增删改查,除非赋予了该用户可以对其它用户数据进行操作。oracle是完全意义上的一个用户即一个独立数据库。 



看存储过程文本 


select text from syscat.procedures where procname='PROC1'; 


--看表结构 

--describe table syscat.procedures describe select * from syscat.procedures 



十五、实例操作 

 win cmd下输入: db2cmd -->db2icrt <实例名称> 即新建一个实例 

 db2ilist 显示该数据库有多少个实例 

 db2 get instance 显示当前运行的实例 

 set db2instance=实例名称 设置当前实例(也可以在win 环境变量中设置DB2INSTANCE变量) 

 db2start 启动当前实例 

 db2stop 停止当前实例 

 db2stop force 强制停止 

 db2idrop 删除一个实例 


 注:如需要显示实例或者数据库(用命令新加的)在控制中心中,需要在控制中心中手工添加(右键->添加->发现) 


 db2 create database 数据库名称 在当前实例下新建一个数据库 

 db2 list db directory 显示当实例的所有数据库 

 db2 drop database 数据库名称 删除一个数据库 

 db2 connect to <database_name> 连接数据库 


 随着数据库的创建,有几个默认的对象也同时被创建:表空间,表,缓冲池,日志文件。 

 默认几个表空间如下(基本和oracle差不多,不同的只是名字而已) 

 SYSCATSPACE(oracle:system) 表空间包含目录表(Catalog),它包含不可更改和不可删除的系统信息,否则数据库无法正常工作 

 TEMPSPACE1(oracle:temp) 当DB2实现一些类似排序等需要额外空间的操作时会用到 

 USERSPACE1(oracle:users) 如果创建一个表的时候没有指定空间,系统通常会使用USERSPACE1 来存储您的数据库表。 

 SYSTOOLSPACE(oracle:SYSAUX) 系统工具类存放空间, 


十六、db2配置 

 使用配置向导工具(configur)tion Advisor Tool)可以设置db2参数。在控制中心右键单击数据库并选择"Configuration Advisor", 

 根据您对系统资源和工作载荷的描述,配置向导会提供一个db2推荐参数列表,您可以阅读它们来获得更多关于db2配置的细节信息,也可直接使用配置向导提供的数值。 

 一个db2服务器可以在四个不同层面上加以配置: 

 环境变量 

 数据库管理器配置文件(db cfg) 

 数据库配置文件(db cfg) 

 db2概要文件注册表 


  环境变量是在服务器的操作系统层设置的。数据库管理器配置文件变量是在实例层设置的。 

  数据库配置变量是在数据库层设置的。db2的概要文件注册则可以在操作系统层或实例层设置 


 1)环境变量 

 环境变量是在操作系统层设置的变量。db2instance是其中一个主要的环境变量。这个变量显示了当前活动的实例--即您的db2命令执行操作的实例。 

 例如,在命令窗口中设置一个活动的实例'myinst',您可以运行如下操作系统命令:set db2instance=myinst. 


 2)数据库管理器配置文件 

 数据库管理器配置文件(dbm cfg)包含一些参数,这些参数影响对应的实例和及其数据库。您可以通过命令行或者DB2 控制中心查看和修改数据库管理器配置文件。 

 想要从控制中心启动DBM CFG,可以在控制中心实例(instance)文件夹内选择实例对象,然后右键单击,在弹出菜单上选择配置参数(Configure Parameters)。 

 在选择配置参数(Configure Parameters)之后,屏幕会显示dbm cfg 参数列表, 

 很多参数都是动态的,立即生效;但是,有一些参数的修改需要重启实例。可以在命令行使用db2stop 和db2start 命令来实现。 

 在停止一个实例之前,必须断开所有应用与实例的连接。如果您希望强制停止实例,可以使用db2stop force 命令。 

  还可以通过控制中心来停止和启动实例——单击实例对象选择Stop 或Start 即可。 

 db2 get dbm cfg 重新得到dbm cfg 的信息 

 db2 update dbm cfg using <parameter_name> <value> 更新dbm cfg 的参数信息 


 3)数据库配置文件(db cfg) 

 数据库配置文件(db cfg)包含影响对应数据库的参数。数据库配置文件也可以通过命令行或者控制中心来查看或者修改。 

 从控制中心启动DB CFG,可以在数据库(database)文件夹中选择数据库对象,并右键单击弹出菜单,在菜单中选择配置参数(Configure Parameters)。 

 选择配置参数后(Configure Parameters),屏幕会显示一个db cfg 参数的清单 

 get db cfg for <database_name> 重新得到指定数据库的db cfg 信息 

 update db cfg for <database_name> using <parameter_name> <value> 更新db cfg 参数的值 


4)DB2 概要文件注册表 

 DB2 概要文件注册表包含了与平台相关的全局(影响所有实例)或者实力层次(只影响某个实例)的参数。 


十七、其它一些语句 

  db2 list applications show detail 查询当前正在连接数据库的连接详细信息 

   db2 list applications 查询当前正在连接数据库的连接 

 db2 force application all  切断所有连接数据库的连接 

 db2 force application () (<application> 填入程序“db2bp.exe”的句柄,该值由db2 list applications命令获得) 

 LOGSECOND —— 主日志文件占用满时,可以分配的二级日志文件的个数 


十八、create table时可以指定列的默认值,格式如下 WITH DEFAULT 默认值 

 创建db2自动赋值的标识 subscriberID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 100), 

 创建系列及引用(和oracle一样第一次调用需要初始化,即第一次调用只能用nextval): 创建格式:CREATE SEQUENCE seq_name START WITH 10 INCREMENT BY 1 NO CYCLE 

          引用格式:nextval for seq_name(下一个值)/prevval for seq_name(当前值) 

 SYSIBM.SYSDUMMY1。这是一个包含一行一列的系统目录表。如果一个查询要求只得到一个输出,可以使用该表。(如oracle的dual表) 


 • SYSCAT.TABLES 

 • SYSCAT.INDEXES 

 • SYSCAT.COLUMNS 

 • SYSCAT.FUNCTIONS 

 • SYSCAT.PROCEDURES