DB2常用的命令

DB2常用的命令

 DB2常用的命令

1．启动数据库
    db2start
2．停止数据库
    db2stop
3．连接数据库
    db2 connect to database user db2admin using pwd

(注：database为数据库名)
4．读数据库管理程序配置
    db2 get dbm cfg
5．写数据库管理程序配置
    db2 update dbm cfg using 参数名 参数值
6．读数据库的配置
    db2 connect to o_yd user db2 using pwd
    db2 get db cfg for o_yd
7．写数据库的配置
    db2 connect to o_yd user db2 using pwd
    db2 update db cfg for o_yd using 参数名 参数值
8．关闭所有应用连接
    db2 force application all
    db2 force application ID1,ID2,,,Idn MODE ASYNC
    (db2 list application for db o_yd show detail)
9．备份数据库
    db2 force application all
    db2 backup db o_yd to d:
   (db2 initialize tape on \\.\tape0)
   (db2 rewind tape on \\.\tape0)
   db2 backup db o_yd to \\.\tape0
10．恢复数据库
   db2 restore db o_yd from d: to d:
   db2 restore db o_yd from \\.\tape0 to d:
11．绑定存储过程
  db2 connect to o_yd user db2 using pwd
  db2 bind c:\dfplus.bnd
 拷贝存储过程到服务器上的C:\sqllib\function目录中
12．整理表
   db2 connect to o_yd user db2 using pwd
   db2 reorg table ydd
   db2 runstats on table ydd with distribution and indexes all

13．导出表数据
    db2 export to c:\dftz.txt of del select * from dftz
    db2 export to c:\dftz.ixf of ixf select * from dftz
14．导入表数据
import from c:\123.txt of del  insert into ylbx.czyxx
db2 import to c:\dftz.txt of del commitcount 5000 messages  c:\dftz.msg insert into dftz
db2 import to c:\dftz.ixf of ixf commitcount 5000 messages c:\dftz.msg insert into dftz
db2 import to c:\dftz.ixf of ixf commitcount 5000 insert into dftz
db2 import to c:\dftz.ixf of ixf commitcount 5000 insert_update into dftz
db2 import to c:\dftz.ixf of ixf commitcount 5000 replace into dftz
db2 import to c:\dftz.ixf of ixf commitcount 5000 create into dftz   (仅IXF)
db2 import to c:\dftz.ixf of ixf commitcount 5000 replace_create into dftz  (仅IXF)

15．执行一个批处理文件
      db2 –tf 批处理文件名
     （文件中每一条命令用 ；结束）
16．自动生成批处理文件
    建文本文件：temp.sql
  select 'runstats on table DB2.' || tabname || ' with distribution and  detailed indexes all;' from syscat.tables where tabschema='DB2' and type='T';
db2 –tf temp.sql>runstats.sql
17．自动生成建表（视图）语句
在服务器上：C:\sqllib\misc目录中
db2 connect to o_yd user db2 using pwd
db2look –d o_yd –u db2 –e –p –c c:\o_yd.txt
18．其他命令
grant dbadm on database to user bb

19select * from czyxx fetch first 1 rows only
20db2look –d ylbx –u db2admin –w –asd –a –e –o a.txt21. 显示当前用户所有表

　　list tables
22.列出所有的系统表

　　list tables for system
23.查看表结构
　　db2 describe select * from user.tables

 

DB2常用的SQL语句

1、组合语句执行

BEGIN   ATOMIC

表达式1 分号 空格/回车

表达式2 分号 空格/回车

END

2、应该限制访问权限的表（应该撤销这些表PUBLIC SELECT访问权）

SYSCAT.DBAUTH

SYSCAT.TABAUTH

SYSCAT.PACKAGEAUTH

SYSCAT.INDEXAUTH

SYSCAT.COLAUTH

SYSCAT.PASSTHRUAUTH

SYSCAT.SCHEMAAUTH

 

比较有用的目录表

SYSCAT.COLUMNS：包含每一行对应于表或视图中定义的列

SYSCAT.INDEXCOLUSE：包含每一行包含的所有列

SYSCAT.INDEXES：包含每一行对应于表或视图中定义的每个索引

SYSCAT.TABLES：所创建每个表，视图，别名对应其中一行

SYSCAT.VIEWS：所创建每个视图对应其中一行或几行

 

通过索引保持数据唯一性：CREATE UNIQUE INDEX INDEXNAME ON TABLE (COLUMN)

消除重复行：SELECT DISTINCT COLUMN FROM TABLE

 

3、DB2关于时间的一些函数

得到当前时间的年份、月份、天、小时等等：

YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)

分别得到当时的日期和时间

DATE (current timestamp)
TIME (current timestamp)

关于时间的一些计算：

current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current time + 5 HOURS - 3 MINUTES + 10 SECONDS

计算两个日期之间有多少天：

days (current date) - days (date(’1999-10-22′))

得到去除毫秒的当前时间:

CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS

将时间转换成字符串：

char(current date)
char(current time)
char(current date + 12 hours)

将字符串转换成时间：

TIMESTAMP (’2002-10-20-12.00.00.000000′)
TIMESTAMP (’2002-10-20 12:00:00′)
DATE (’2002-10-20′)
DATE (’10/20/2002′)
TIME (’12:00:00′)
TIME (’12.00.00′)

 

注意：在DB2的命令编辑器中可以输入SQL语句和DB2中的内部命令。要想显示当前时间的话，不能直接输入current time，这个只能在SQL语言中引用，要想显示有下面方式：

1）  VALUES （current time）

2）  SELECT  CURRENT TIME FROM SYSIBM.SYSDUMMY1

这个与SQL SERVER2000中不一样，在SQL SERVER2000中可以输入Getdate（）得到时间，既可以显示，也可以在语句SQL中用。

 

4、所有返回前N条数据的表达式

在SQL SERVER2000中使用TOP N 格式

比如： SELECT TOP 10 CARDNO FROM CARD

在DB2中使用fetch first N rows only 格式

比如：SELECT CARDNO FROM SEALCARD fetch first 10 rows only

 

5、函数使用

查看系统函数： SELECT * FROM  SYSibm.sysfunctions;

比如：ABS（-89）可以作为值输入到SQL中，但是要想在命令编辑器中显示函数的结果的话可以用下列方式：

1）SELECT ABS(-89) FROM SYSIBM.SYSDUMMY1;

2）VALUES ABS(-89);

 

6、存储过程

在进行DB2存储过程开发时，我们可以利用很多DB2自带的工具，例如开发中心，控制中心等。但有时使用脚本能带给开发人员更大的灵活性和更高的工作效率。

在开始开发一个新的或修改一个已存在的存储过程时，我们通常会进行下面一些准备工作：

1.         查看该存储过程会用到的表的表结构、字段类型、相关索引和示例数据。

2.         查看相关的存储过程或用户自定义函数（UDF）的定义。

3.         查找无效的存储过程并生成绑定语句。

4.         如某个表发生了改变，查看依赖于该表的所有视图、存储过程和用户自定义函数（UDF）

虽然上述信息都可以通过DB2提供的开发工具和管理工具获得,但是通过脚本可以更快速的获得所需信息,并且可以重复执行。

使用脚本完成上述任务的关键是理解和使用DB2的系统表。我们先简单回顾一下有关的DB2的系统表和视图:

1.         syscat.routines：存储所有的存储过程和用户自定义函数（UDF）信息。其中routinename字段为存储过程或用户自定义函数（UDF）的名称，routinetype字段表示该记录表示的是存储过程（P）还是用户自定义函数（F），lib_id字段为存储过程编译后生成的包序列号，origin字段表示该存储过程或用户自定义函数的出处（Q表示是由SQL组成的，E表示用户定义的且是外部的），valid字段表示该存储过程或用户自定义函数是否有效，如果origin字段不是Q的话，该域为空。

2.         syscat.packages：存储所有被绑定的程序包。其中pkgname表示包名称，valid字段表示该包是否合法。

3.         syscat.packagedep：存储关于程序包的依赖关系。其中pkgname字段表示程序包名，btype字段表示被依赖对象的类型，bname字段表示被依赖对象的名称。

4.         syscat.routinedep：存储关于程序（routine）的依赖关系。其中routinename字段表示程序名，btype字段表示被依赖对象的类型，bname字段表示被依赖对象的名称。

5.         syscat.viewdep：存储了关于视图的依赖关系。其中viewname字段表示视图名，btype字段表示被依赖对象的类型，bname字段表示被依赖对象的名称。

回顾并了解了上述系统表和视图，我们就可以很容易的创建脚本以完成前面提到的开发存储过程所要做的准备工作。

1．  查看该表结构、字段类型、相关索引和示例数据

虽然我们可以查询sysibm.systables表获得表结构，但有一种更简便的方法来获取表结构，即使用db2look工具。该工具可以生成创建该表和相关的索引的DDL。如我们要获取指定数据库中指定表的结构和前20条数据作为参考，可编写脚本viewtbl.cmd如下，传入参数分别为数据库名和表名。

@echo ------------------ DDL of table %2 and related index(ex) ------------------

@db2look -d %1 -t %2 -e

@echo ------------------ fisrt 20 rows in table %2 ------------------

@db2 select * from %2 fetch first 20 rows only
 

2．查看已存在的存储过程和用户自定义函数（UDF）的定义，将结果存入文件并自动打开结果文件。

可以从syscat.routines表中做简单查询实现脚本viewrtn.cmd。

@db2 SELECT text FROM SYSCAT.ROUTINES WHERE ROUTINENAME=upper('%1') > %1.sql

@start %1.sql
 

3．查看所有无效的存储过程并生成绑定语句

删除存储过程引用的表会引起该存储过程无效。无效存储过程可以采用查询syscat.routines和syscat.packages的方法获得：

SELECT

    RTRIM(r.routineschema) || '.' || RTRIM(r.routinename) AS spname ,

    RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+10000000),2) AS pkgname

FROM

    SYSCAT.routines r

WHERE

    r.routinetype = 'P'

    AND (

        (r.origin = 'Q' AND r.valid != 'Y')

        OR EXISTS (

            SELECT 1 FROM syscat.packages

            WHERE pkgschema = r.routineschema

            AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)

            AND valid !='Y'

        )

    )

ORDER BY spname
 

注意要同时查询syscat.routines和syscat.packages表，因为在程序包无效时syscat.routines中的valid值仍可能为Y。

如果要自动生成重新绑定语句，只需将上述SQL改写并存成invalidSP.cmd：

@echo off

db2 "SELECT '@db2 rebind package '|| RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)||' resolve any' FROM SYSCAT.routines r WHERE r.routinetype = 'P' AND ((r.origin = 'Q' AND r.valid != 'Y') OR EXISTS (SELECT 1 FROM syscat.packages WHERE pkgschema = r.routineschema AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2) AND valid !='Y') )" >rebindsp.bat
 

4．查看某个表所依赖的视图、存储过程和用户自定义函数（UDF）

使用上述系统视图，我们很容易编写出脚本：

@echo off

echo --- dependent SPs ---

db2 "select proc.procschema, proc.procname from syscat.routines r, syscat.procedures proc, syscat.packagedep pdep where pdep.bname=upper('%2') and pdep.bschema=upper('%1') and r.specificname=proc.specificname AND pdep.pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)"

 

echo --- dependent UDF ---

db2 select routineschema, routinename from syscat.routinedep where bschema = upper('%1') and bname = upper('%2') and btype ='T' order by bname

 

echo --- dependent view ---

db2 select viewschema, viewname from syscat.viewdep where bschema = upper('%1') and bname = upper('%2') and btype ='T' order by bname
 

行业借鉴经验：提高DB2存储过程性能和健壮性的3个最佳实践

最佳实践1：在创建存储过程语句中提供必要的参数

创建存储过程语句（CREATE PROCEDURE）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供这些参数可以提高执行效率。下面是一些常用的参数:

l         容许SQL（allowed－SQL）

容许SQL（allowed－SQL）子句的值指定了存储过程是否会使用SQL语句，如果使用，其类型如何。它的可能值如下所示：

NO SQL： 表示存储过程不能够执行任何SQL语句。

CONTAINS SQL： 表示存储过程可以执行SQL语句，但不会读取SQL数据，也不会修改SQL数据。

READS SQL DATA： 表示在存储过程中包含不会修改SQL数据的SQL语句。也就是说该储存过程只从数据库中读取数据。

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

如果没有明确声明allowed-SQL，其默认值是MODIFIES SQL DATA。不同类型的存储过程执行的效率是不同的，其中NO SQL效率最好，MODIFIES SQL DATA最差。如果存储过程只是读取数据，但是因为没有声明allowed-SQL类型，它会被当作对数据进行修改的存储过程来执行，这显然会降低程序的执行效率。因此创建存储过程时，应当明确声明其allowed-SQL类型。

l         返回结果集个数（DYNAMIC RESULT SETS n）

存储过程能够返回0个或者多个结果集。为了从存储过程中返回结果集，需要执行如下步骤：

在CREATE PROCEDURE 语句的DYNAMIC RESULT SETS子句中声明存储过程将要返回的结果集的数量。如果这里声明的返回结果集的数量小于存储过程中实际返回的结果集数量，在执行该存储过程的时候，DB2会返回一个警告。

使用WITH RETURN子句，在存储过程体中声明游标。

为结果集打开游标。当存储过程返回的时候，保持游标打开。

在创建存储过程时指定返回结果集的个数可以帮助程序员验证存储过程是否返回了所期待数量的结果集，提高了程序的完整性。

最佳实践2：对输入参数进行必要的的检查和预处理

无论使用哪种编程语言，对输入参数的判断都是必须的。正确的参数验证是保证程序良好运行的前提。同样的，在DB2存储过程中对输入参数的验证和处理也是很重要的。正确的验证和预处理操作包括：

如果输入参数错误，存储过程应返回一个明确的值告诉客户应用，然后客户应用可以根据返回的值进行处理，或者向存储过程提交新的参数，或者去调用其他的程序。

根据业务逻辑，对输入参数作一定的预处理，如大小写的转换，NULL与空字符串或0的转换等。

在DB2储存过程开发中，如需要遇到对空(NULL)进行初始化，我们可以使用COALESCE函数。该函数返回第一个非NULL的参数。例如，COALESCE(piName,'')，如果变量piName为NULL，那么函数会返回''，否则就会返回piName本身的值。因此，可以用下面的代码检查piName是否非NULL并且非空字符串：

SET poGenStatus = 0;                                                   

   SET piName   =RTRIM(COALESCE(piName, ''));

    IF (piName ='')  

    THEN                             

        SET poGenStatus = 34100;     

        RETURN poGenStatus;          

    ENDIF;       
 

同理，使用COALESCE可以对任何类型的输入参数进行初始化或验证。下面是对参数初始化规则的一个总结：

1.       输入参数为字符类型，且允许为NULL的，如希望缺省值为空字符串，可以使用COALESCE(inputParameter, '')把NULL转换成空字符串;

2.       输入类型为整型，且允许为NULL的，如希望缺省值为0，可以使用COALESCE(inputParameter,0)，把NULL转换成0；

3.       输入参数为字符类型，且不允许是NULL或空字符串的，可以使用RTRIM(COALESCE(inputParameter, ''))把NULL转换成空字符串，然后验证函数返回值是否为空字符串；

4.       输入类型为整型，且不允许是NULL的，不需要使用COALESCE函数，直接使用IS NULL进行验证。

最佳实践3：异常（condition）处理

在存储过程执行的过程中，经常因为数据或者其他问题产生异常（condition）。根据业务逻辑，存储过程应该对异常进行相应处理或直接返回给调用者。此处暂且将condition译为异常以方便理解。实际上有些异常（condition）并非是由于错误引起的。

当存储过程中的语句返回的SQLSTATE