db2-use-notes

本篇主要的内容是：在日常工作中，记录DB2的一些操作笔记。

概述

在工作当中，会频繁的对数据进行一系列的操作。这里记录了使用DB2数据库比较常见的命令。供以后参考。

主要内容

准备工作

创建一张临时表，用来举例说明

CREATE TABLE
    TB_A
    (
        SEQ INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        NAME VARCHAR(20),
        AGE INTEGER,
        SALARY DECIMAL(18,2),
        PRIMARY KEY (SEQ)
    );

插入预先准备的演示数据

insert into TB_A (NAME,AGE,SALARY) values ('tom',18,3000);
insert into TB_A (NAME,AGE,SALARY) values ('bob',18,3000);
insert into TB_A (NAME,AGE,SALARY) values ('jck',18,3000);
insert into TB_A (NAME,AGE,SALARY) values ('lil',20,3000);
insert into TB_A (NAME,AGE,SALARY) values ('mer',20,4000);
insert into TB_A (NAME,AGE,SALARY) values ('gor',20,4000);
insert into TB_A (NAME,AGE,SALARY) values ('hah',21,2000);
insert into TB_A (NAME,AGE,SALARY) values ('yiy',21,4020);
insert into TB_A (NAME,AGE,SALARY) values ('sor',21,4010);
insert into TB_A (NAME,AGE,SALARY) values ('ppo',21,2000);
insert into TB_A (NAME,AGE,SALARY) values ('iio',21,4220);
insert into TB_A (NAME,AGE,SALARY) values ('sse',21,4110);

到目前为止，准备工作已经完成：创建了一张临时表，同时插入了演示数据。

数据的导出导入

在实际工作当中，经常会使用命令行工具对生产上的数据进行导出，然后对把导出的数据再导入本地临时表中，然后具体分析。

登录数据库

/app>db2 connect to DB_NAME user USER_NAME using passwd

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.3
 SQL authorization ID   = USER_NAME
 Local database alias   = DB_NAME

DB_NAME: 数据库名称, USER_NAME: 用户名，passwd : 密码

从表中导出数据到文件

在日常工作中，我更喜欢将数据以.ixf文件的格式来导出、导入数据，但必须保证生产上表的结构和字段顺序要和本地临时表保持完全一致，否则会出错。

使用export命令导出数据

/app>db2 "export to TB_A.ixf of ixf select * from TB_A";
SQL3104N  The Export utility is beginning to export data to file "TB_A.ixf".

SQL3105N  The Export utility has finished exporting "12" rows.


Number of rows exported: 12

/app>
/app>db2 "export to TB_A.csv of del select * from TB_A";
SQL3104N  The Export utility is beginning to export data to file "TB_A.csv".

SQL3105N  The Export utility has finished exporting "12" rows.


Number of rows exported: 12

从文件导入数据到表

使用import命令导入数据

/app>db2 "import from TB_A.ixf of ixf insert into TB_A";
SQL3150N  The H record in the PC/IXF file has product "DB2    02.00", date 
"20180412", and time "142939".

SQL3153N  The T record in the PC/IXF file has name "TB_A.ixf", qualifier "", 
and source "            ".

SQL3109N  The utility is beginning to load data from file "TB_A.ixf".

SQL3110N  The utility has completed processing.  "12" rows were read from the 
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "12".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "12" rows were processed from the input file.  "12" rows were 
successfully inserted into the table.  "0" rows were rejected.


Number of rows read         = 12
Number of rows skipped      = 0
Number of rows inserted     = 12
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 12

/app>

分组小计

在日常工作中，偶尔会有这种需求：对分组后的结果集取每组前n条信息，在之前学习的oracle数据库中，有伪劣的概念，在DB2中，也有办法来实现。下面以几个小例子来展示。

使用row_number() over(…)来对分组后结果集进行再次处理。

1
2
3

select age,name,salary,row_number() over(order by age asc) as age_rn 
from tb_a 
group by age,name,salary order by age;

1
2
3

select age,name,salary,row_number() over(partition by age order by age asc) as age_rn 
from tb_a 
group by age,name,salary order by age;

来对比一下这两条SQL的执行结果

首先这两条都语句的目的，都是对分组查询后的结果集的二次操作。区别在于第二条在over括号内使用了partition by，从两条结果集的对比中，发现，partiton by 的作用是，对结果集中，某一列再次分组，如果没有partiton by,则把结果集看做一个整体。

按照上面的分析。现在就可以做到下面这种业务场景的要求了

对TB_A按照年龄分组，取出每组的前两条信息

SELECT * FROM (
            select age,name,salary,row_number() over(partition by age order by age asc) as age_rn
            from tb_a
            group by age,name,salary order by age
        ) 
WHERE age_rn <= 2

小结

在本篇中，纪录了日常工作中较为常用的几个DB2命令。希望在工作中可以起到一点帮助作用。