DB2使用经验点滴(2008/10/31更新)

zyk198500

　　1、将某个表导出为IXF档：

CONNECT TO CSI;
EXPORT TO "C:\T_R_FORM.ixf" OF IXF MESSAGES "aa" SELECT * FROM CSIDDBD3.T_REFERRAL_FORM;
CONNECT RESET;
CONNECT TO CSI;
EXPORT TO "C:\T_R_FORM_FLAG.ixf" OF IXF MESSAGES "bb"
SELECT * FROM CSIDDBD3.T_REFERRAL_FORM_FLAG;
CONNECT RESET;
　　2、日期类型（Date）作为查询条件：

SELECT * FROM CSIDDBD3.T_ID_MSGPOP WHERE BEG_DATE > '2007-01-01';
　　3、时间类型（TimeStamp）作为查询条件：

SELECT * FROM CSIDDBD3.T_ID_MSGPOP WHERE INPUT_DATE > '2007-01-01 00:00:00';
　　4、取前N条记录

SELECT * FROM CSIDDBD3.T_ID_MSGPOP FETCH FIRST 1000 ROWS ONLY;
　　5、如何利用表的NOT LOGGED INITIALLY特性清空表中的数据

alter  table table_name activate not logged initially with empty table
　　参考资料

　　6、批量更新某一查询结果集的第N条到第M条的记录的SQL(DB2)


update tableName b
set b.A=2 ,b.B=current date  --B字段为日期类型
where exists (
select 1 from (
select a.id,row_number()over(order by a.id) req --以tableName表的主键id字段进行排序
from tableName a where a.C =22 and a.D=1) c --加上查询结果集的限制条件
where b.id=c.id and c.req>=1 and c.req<=3);--将要更新查询结果集的第1到第3条记录
　　7、DB2 如何设置最大连接数?

db2 connect to dbname user username  using  passwd
db2 update db cfg using MAXAPPLS  number
 　　8、SUBSTR()函数的使用
　　函数原型：

SUBSTR(string-expression,start,length)
　　【 示例】Sample table DSN8810.PROJ contains column
PROJNAME, which is defined as VARCHAR(24). Select all rows from that table
for which the string in PROJNAME begins with 'W L PROGRAM
'.

SELECT * FROM DSN8810.PROJ WHERE SUBSTR(PROJNAME,1,12) = 'W L PROGRAM ';
　　【示例2】DB2函数substr分析
　　错误SQL：

select pk_invcl from bd_invcl
where length (invclasscode) <= length ('501')
and substr ('501', 1, length (invclasscode)) = invclasscode
order by invclasscode
　　错误日志：

2007-01-18 19:10:13 SO|java.sql.SQLException: [IBM][CLI Driver][DB2/AIX64] SQL0138N
The second or third argument of the SUBSTR function is out of range. SQLSTATE=22011
　　分析：虽然第一个条件已经限制length (invclasscode) <= length ('501') ，但是不满足第一个条件时，DB2第二个条件还会执行。
但是以下SQL却可以执行，并且以下两个SQL返回结果相同

select count(*) from bd_invcl
where substr (invclasscode, 1,length(invclasscode)+2) = invclasscode
select count(*) from bd_invcl
　　修正后：

select pk_invcl from bd_invcl
where length (invclasscode) < length ('501')
and substr ('501', 1,   case
when length (invclasscode)>length ('501')
then length ('501')
else length (invclasscode) end   )
= invclasscode order by invclasscode
 　　9、自动增长列
　　A、包含生成列的 DB2 表上的数据移入和移出 — 入门

　　B、在 DB2 通用数据库中自动生成数值序列

　　C、DB2自动增长主键的方法。

　　D、急，DB2 9中自动增长列如何返回(在线等)

　　《Understanding DB2® Learning Visually with Examples》


7.8.6. Identity Columns

An identity column
is a
numeric column in a table that automatically generates a unique numeric value in
sequence for each row inserted. A unique identifier is often used in
applications to identify a specific row. Unlike sequence objects, which we
discuss in section
7.16
, Sequences, identity columns are bound to the table they are defined
on. There can be only one identity column per table. DB2 can generate the
identity column values in two ways.

• 
  Generated always
  : The values
  are always generated by DB2. Applications are not allowed to provide an explicit
  value.
  
  
• 
  Generated by default
  : The
  values can be explicitly provided by an application; if no value is given, DB2
  generates one. In this case, however, DB2 cannot guarantee the uniqueness of the
  value generated.
  
  

To create an identity column, use
the CREATE TABLE
statement with the
GENERATED
clause and make sure it
contains the IDENTITY
keyword because
GENERATED
can also be used to generate
other values automatically that are not identity columns. Here is an
example.

CREATE TABLE product (
productno   INTEGER GENERATED ALWAYS AS
IDENTITY (START WITH 200 INCREMENT BY 1),
description VARCHAR(50) ) 
The column productnois an
INTEGER defined as an identity column that is always generated. The value
generated will start from 200, and it will be incremented by 1. Let's perform a
few INSERT
statements and see the
results obtained.

INSERT INTO product VALUES
(DEFAULT,'banana');

        --->inserts 200,banana
INSERT INTO product (description) VALUES
('apple');

   --->inserts 201,apple
INSERT INTO product VALUES
(300,'pear');

              --->error SQL0798N
COMMIT;


INSERT INTO product (description) VALUES
('orange');

  --->inserts 202,orange
ROLLBACK;


INSERT INTO product (description) VALUES
('plum');

    --->inserts 203,plum
COMMIT

;

The following query shows the final result.

SELECT * FROM product;
PRODUCTNO   DESCRIPTION
----------- ------------
200 banana
201 apple
203 plum
The first two INSERT
statements show that two identity column values were generated: 200 and 201. The
third INSERT
statement returns an
error because you cannot explicitly insert a value for an identity column
generated as ALWAYS

. After the third
INSERT
statement, we issue a COMMIT
to guarantee these rows are stored in
the database. The fourth INSERT
statement causes another identity column value, 202, to be generated; however,
we issue a ROLLBACK
statement right
after, so this row is not stored in the database. Note that the final INSERT
statement, which inserts the product
plum, generates a value of 203, not 202. (COMMIT
and ROLLBACK
statements are explained in more
detail in Chapter 13
, Developing
Database Backup and Recovery Solutions.)

NOTE
An identity column value is generated only once. Once the value
has been generated, even if a ROLLBACK
statement is performed, it will not be generated again.


Now
let's review another example, this time creating the same table productwith the GENERATED BY DEFAULT
clause.

CREATE TABLE product (
productno   INTEGER GENERATED BY DEFAULT AS
IDENTITY (START WITH 200 INCREMENT BY 1),
description VARCHAR(50) )
 
Next, we insert a few rows.

INSERT INTO product VALUES (DEFAULT,'banana');         --->inserts 200,banana
INSERT INTO product (description) VALUES ('apple');    --->inserts 201,apple
INSERT INTO product VALUES (300,'pear');               --->inserts 300,pear
INSERT INTO product VALUES (201,'orange');             --->inserts 201,orange
COMMIT;
INSERT INTO product (description) VALUES ('papaya');   --->inserts 202,papaya
ROLLBACK;
INSERT INTO product (description) VALUES ('plum');      --->inserts 203,plum
COMMIT;
　　



The following query shows the final result.

SELECT * FROM product
PRODUCTNO   DESCRIPTION
----------- ---------------------
200 banana
201 apple
300 pear
201 orange
203 plum
The first two INSERT
statements show that two identity column values were generated: 200 and 201. For
the third and fourth INSERT
statements, we explicitly provided the values 300 and 201, respectively, for the
identity column. Note that DB2 did not return an error as in the previous
example because we defined the identity column as GENERATED BY DEFAULT

. After the fourth INSERT
statement, we issue a COMMIT
to guarantee these rows are stored in
the database. The fifth INSERT
statement causes another identity column value, 202, to be generated; however,
we issue a ROLLBACK
statement right
after, so this row is not stored in the database. Note that the final INSERT
statement, which inserts the product
plum, generates a value of 203, not 202.
The following final example illustrates a GENERATED
value, which is not an identity
column. The example uses GENERATED
ALWAYS

, but you can also use GENERATED
BY DEFAULT

.

CREATE TABLE income (
empno     INTEGER,
salary    INTEGER,
taxRate   DECIMAL(5,2),
netSalary DECIMAL(7,2) GENERATED ALWAYS AS (salary * (1 - taxRate))
)
 
If you insert the following row:

INSERT INTO income (empno, salary, taxRate) VALUES (111, 50000, 0.3) 
The
result is:

EMPNO       SALARY      TAXRATE NETSALARY
----------- ----------- ------- ---------
111       50000    0.30  35000.00
DB2 generates the value of the last column NETSALARYbased on the SALARYand TAXRATEcolumns.