DB2使用经验点滴(2008/10/31更新)
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特性清空表中的数据
altertable table_name activate not logged initially with empty table 参考资料
6、批量更新某一查询结果集的第N条到第M条的记录的SQL(DB2)
update tableName bset 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 usernameusingpasswddb2 update db cfg using MAXAPPLSnumber 8、SUBSTR()函数的使用
函数原型：
SUBSTR(string-expression,start,length)【 示例】Sample table DSN8810.PROJ contains columnPROJNAME, which is defined as VARCHAR(24). Select all rows from that tablefor 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: 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) = invclasscodeselect 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 productno is 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.)
<div class="docNote">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.
页:
[1]