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

 

 

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.