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特性清空表中的数据

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 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.)

 

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 product with 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 NETSALARY based on the SALARY and TAXRATE columns.

分享到：

AVI转MP3的软件 | 用SQL语句增加删除修改字段

• 2008-10-17 14:13
• 浏览 3899
• 评论(0)
• 查看更多