db2简单操作

最近在看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
  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值