使用DB2内置函数快速构造测试数据

使用DB2内置函数快速构造测试数据
 
【案例】使用DB2内置函数快速构造测试数据
 无论您是在用原型证明某一概念，还是开发一个全新的应用程序，或者只是学习 SQL，您都需要在您的应用程序上运行测试数据。为了有效地测试应用程序的性能，您必须拥有足够的测试数据，以便暴露潜在的性能问题。只要可以得到，用实际数据来进行测试总是更可取一些。如果没有可用的实际数据，那么在许多情况下，也可以生成足够的假想数据。一般来说，从头开始构造大量数据是件很容易的工作，可以使用db2的SQL语句就可以完成。
【案例类别】测试数据、构造数据
【关键字】date create generate insert select
【问题现象】
 无论您是在用原型证明某一概念，还是开发一个全新的应用程序，或者只是学习 SQL，都需要在应用程序上运行测试数据。为了有效地测试应用程序的性能，必须拥有足够的测试数据，以便暴露潜在的性能问题。只要可以得到，用实 际数据来进行测试总是更可取一些。如果没有可用的实际数据，那么在许多情况下，也可以生成足够的假想数据。一般来说，从头开始构造大量数据是件很容易的工 作，可以使用db2的SQL语句就可以完成
【问题分析】
Db2 OLAP(分析函数简介)
 生成大量测试数据需要用到DB2的分析函数来进行有规则的构造数据。
RAND()：rand函数用来生成一组随机数，介于0-1之间的的随即小数，例如：
Select rand() as NUM from syscat.tablespaces，输出如下：
NUM       
-----------
+1.53311722517624E-001
          +5.54695940834794E-001
          +1.42065802655213E-001
          +7.82508742428622E-001
  4 record(s) selected.

RAND()函数应用：
 使用RAND()生成随即数，例如”SELECT INT(RAND() * 50 + 1) AS NUM FROM SYSCAT.TABLESPACES”,生成50以内的随即数。结果如下：

NUM       
-----------
         16
         19
         10
          8
  4 record(s) selected.

ROW_NUMBER()：Row_Number函数用于生成整数序列很方便。
ROW_NUMBER()函数应用：
 使用ROW_NUMBER()函数生成递增序列，”SELECT ROW_NUMBER() OVER() AS SEQ FROM SYSBAT.TABLESPACES”，结果如下：
SEQ                
--------------------
                   1
                   2
                   3
                   4
  4 record(s) selected.

利用DB2内置函数生成辅助表：
 为什么要生成辅助表呢？再构造负责的数据，或者是满足某种分布状态的数据，我们的构造语句会非常复杂，如果不使用辅助表（也就是临时表），下面我们看看我们需要那些辅助表：生成一个自增加序列表，如果想要使用子增加序列或者是生成主键就需要用到该辅助表，生成一个字母和数据映射的ASCII骂表，因为再处理字符串时，需要用数字来模拟生成字符串的序列，然后再通过db2的内置函数ASCII来转化为db2的字符串序列。构造生成一个与时间映射的辅助表。

自增长序列表：
 “CREATE TABLE SEQ_DATE (NUM INT NOT NULL,PRIMARY KEY (NUM))”

“INSERT INTO SEQ_DATE SELECT ROW_NUMBER() OVER() FROM SYSCAT.COLUMNS T1 JOIN SYSCAT.COLUMNS T2 ON T1 != T2 FETCH FIRST N ROWS ONLY”
第一句为构建辅助表SQL，第二句为将数据插入到临时表中，其中N修改为自己想要插入的记录条数。

ASCII码表：
 “CREATE TABLE ASCII_DATE (ASCII CHAR(1) NOT NULL, NUM INT NOT NULL)”
 “INSERT INTO ASCII_DATE SELECT CHR(NUM) AS ASCII, NUM FROM SEQ_DATE WHERE NUM>=48 AND NUM <=122”，其中CHR函数表示将数据转化为ASCII码，而WHERE条件是指，指转换可视的ASCII码，这里可以根据需要将自己需要的ASCII添加的WHERE条件中，如下表中不包含回车和换行，如果想构造特殊的字符串（包含换行或者回车），可以将WHERE条件更改为WHERE NUM>=48 AND NUM <=122 OR NUM=10 OR NUM=13
 使用SELECT * FROM ASCII_DATE查询表中数据，结果如下：
ASCII 十进制 ASCII 十进制 ASCII 十进制
0 48 D 68 ] 93
1 49 E 69 ^ 94
2 50 F 70 _ 95
3 51 G 71 ` 96
4 52 H 72 a 97
5 53 I 73 b 98
6 54 J 74 c 99
7 55 K 75 d 100
8 56 L 76 e 101
9 57 M 77 f 102
: 58 N 78 g 103
; 59 O 79 h 104
< 60 P 80 i 105
= 61 Q 81 j 106
> 62 R 82 k 107
? 63 S 83 l 108
@ 64 T 84 m 109
A 65 U 85 n 110
B 66 V 86 o 111
C 67 W 87 p 112
w 119 X 88 q 113
x 120 Y 89 r 114
y 121 Z 90 s 115
z 122 [ 91 t 116
v 118 \ 92 u 117

 这样在构造特定字符序列的时候就可以使用先生成数据，然后转换为ASCII，这样构造特定字符序列的问题就转化为了生成特定的数据的问题了。

日期辅助表：
 
 由于日期辅助表比较灵活，需要根据具体的应用场景来生成具体的辅助表，所以这里就举例说明，下面不再累述。
 场景1：需要生成2010年3月份的某一天。
 “CREATE TABLE TIME_SEQ (DATE_ CHAR(8) NOT NULL, NUM INT NOT NULL)”
 “INSERT INTO TIME_SEQ SELECT DATE('03/01/2010') + (NUM - 1) DAYS AS DATE_1, NUM  FROM SEQ_DATE WHERE NUM>=1 AND NUM <=30”
 生成的数据如下：

DATE_1 NUM DATE_1 NUM
03/01/2010 1 03/16/2010 16
03/02/2010 2 03/17/2010 17
03/03/2010 3 03/18/2010 18
03/04/2010 4 03/19/2010 19
03/05/2010 5 03/20/2010 20
03/06/2010 6 03/21/2010 21
03/07/2010 7 03/22/2010 22
03/08/2010 8 03/23/2010 23
03/09/2010 9 03/24/2010 24
03/10/2010 10 03/25/2010 25
03/11/2010 11 03/26/2010 26
03/12/2010 12 03/27/2010 27
03/13/2010 13 03/28/2010 28
03/14/2010 14 03/29/2010 29
03/15/2010 15 03/30/2010 30

同样生成日期的问题转换为了生成数据序列，数据序列是我们很好生成的。

使用如下语句生成10000条3月份随即某一天的记录：
“SELECT DATE FROM TIME_SEQ AS S1, (SELECT INT(RAND() * 30 + 1) AS NUM FROM SYSCAT.COLUMNS T1 JOIN SYSCAT.COLUMNS T2 ON T1.COLNAME != T2.COLNAME FETCH FIRST 10000 ROWS ONLY) AS S2 WHERE S1.NUM=S2.NUM”

构造辅助表是我们构造负责数据的基础，通过辅助表可以将复杂的数据生成规则转换成为生成简单的数据序列。掌握好这样原则，构造灵活好用的辅助表可以使你的数据分布达到你的要求。

常用生成规则实例：
唯一主键：
 唯一主键一般有俩中，一种是固定长度的随即数据序列，一种是变长的递增数字序列。
 固定长度的数据序列（长度为5位的唯一序列）：
 
 “CREATE TABLE SIMAPLE_TABLE (KEY VARCHAR(5) NOT NULL, PRIMARY KEY (KEY))”

 “INSERT INTO SIMAPLE_TABLE SELECT
    RTRIM(CHAR(INT(RAND() * 10)))
||RTRIM(CHAR(INT(RAND() * 10)))
||RTRIM(CHAR(INT(RAND() * 10)))
||RTRIM(CHAR(INT(RAND() * 10)))
||RTRIM(CHAR(INT(RAND() * 10))) AS KEY FROM SYSCAT.COLUMNS T1 JOIN SYSCAT.COLUMNS T2 ON T1.COLNAME != T2.COLNAME FETCH FIRST 10000 ROWS ONLY”
 
 其中用到的DB2的内置函数有RTRIM、CHAR、INT、RAND，RTRIM，RAND() * 10是产生一个0-10的随即浮点数，INT将浮点数转化为INT类型，将INT转化为字符串类型，将字符串使用RTRIM将右边的空格去掉。

 变长的递增数字序列：

 “CREATE TABLE UNIQUE_SEQ (KEY VARCHAR(30) NOT NULL, PRIMARY KEY (KEY))”

 “INSERT INTO UNIQUE_SEQ SELECT CHAR(ROW_NUMBER() OVER()) AS KEY FROM SYSCAT.COLUMNS T1 JOIN SYSCAT.COLUMNS T2 ON T1.COLNAME != T2.COLNAME FETCH FIRST 10000 ROWS ONLY”

 其中用到的db2内置函数有ROW_NUMBER() 、OVER()，ROW_NUMBER()函数是对结果集行数的统计函数，而OVER()是配合ROW_NUMBER()函数，以使ROW_NUMBER()能够按照某种分布或是排序来统计。

随即字符串：
 
 “CREATE TABLE STRING_DATE(STRING VARCHAR(5) NOT NULL)”

 “INSERT INTO STRING_DATE
SELECT CHR(INT(RAND() * 75 + 48))
||CHR(INT(RAND() * 75 + 48))
||CHR(INT(RAND() * 75 + 48))
||CHR(INT(RAND() * 75 + 48))
||CHR(INT(RAND() * 75 + 48)) AS STRING
FROM SYSCAT.COLUMNS T1 JOIN SYSCAT.COLUMNS T2 ON T1.COLNAME != T2.COLNAME”

其中用的DB2内置函数有CHR、||，CHR函数是将INT转换为ASCII，而表达式INT(RAND() * 75 + 48)是生成48~122的INT整数，ASCII可视部分都在这个范围之内。||是DB2中的字符串连接。

【问题解决】
 可见使用DB2内置函数来生成测试数据是很轻松的，效率高，而且学习成本比较低，对于构造简单数据规则的数据比较有效。
 
 项目具体实例：
 构造USER表的数据：
  数据规则：userid从1递增，手机号码从13900000001递增。
  数据数量：10000条
  用时：在毫秒级别内

 “INSERT INTO USER (USERID, PASSWD, CREATETIME, REGISTERTYPE, USERSTATE, MOBILE, USERSIGN) SELECT CHAR(ROW_NUMBER() OVER()) AS USERID, 'QTkU2ow3COlC9WDFf7J1JQ==' AS PASSWD, '20100327012752' AS CREATETIME, 3 AS REGISTERTYPE, 0 AS USERSTATE, '+86'||CHAR(13900000000 + ROW_NUMBER() OVER()) AS MOBILE, '1' AS USERSIGN FROM SYSCAT.COLUMNS T1 JOIN SYSCAT.COLUMNS T2 ON T1.COLNAME != T2.COLNAME FETCH FIRST 10000 ROWS ONLY”

【问题总结】
 灵活使用DB2内置函数，可以简化编程。