Several knowledge points to be aware of when using IBM DB2 database

A few knowledge points you must know when using IBM DB2 databases:

1. After installing the DB2 database, you can use the command line or graphical interface to operate, if your database server is not on this computer, you need to be in the "Client Configuration Helper" in the configuration of a client connection.

2. In the control center can not be deleted or modified data, can only write SQL statements to implement and quest provided by the tool, although it can increase the data, but cannot use the copy, Paste and tab, you must input, and then click on the mouse to switch the existing data appears to be able to edit the changes in the cell, but actually can not commit , hehe, or honest write update statements, as to delete data, is not write DELETE statement. However, you can use PB as a cell to edit the data, the corresponding disadvantage is to edit the data button and delete the table button is too close, all wrong to delete the table button, PB but without prompting the table to delete, faint

3. The DB2 view cannot be used directly with the order BY statement and must be written like this:

Selectxfrom (select A,b,c from table1 order by a) as tab

Note: The premise of this writing is that you have already patched

4. Problems with stored procedures:

DB2 provides LTrim functions and RTRIM functions, but does not provide the trim function, if you want to remove characters at both ends of the space, I'm sorry, you must use the LTrim (RTrim ()) way to invoke the INSERT statement can not be used to assign values, you must first assign the value of a variable When using other stored procedures, you cannot use constants as arguments, you must assign the value of this constant to a variable, and then use this variable as the parameter

SELECT * FROM table fetch the ' N rows only ' statement not available in stored procedure

5. Dynamic SQL can be used in stored procedures, but cannot be used in functions, Kao

6. When a commit or rollback is encountered, the cursor is automatically closed, so it is prudent to use a separate commit.

Proc Builder is always in the debugging of low memory, the screen spent. When a breakpoint is debugged, pausing for a slightly longer period of time will prompt a time-out.

7. Development issues:

When you take a date by using the date variable +1 MONTHS OR Date variable-1 MONTHS, such as when the date variable value is 2004-02-29, the date variable +1 MONTHS is assigned to another

An error occurs when a date variable. The corresponding SQLState is 01506 (DB2 01506): the date or timestamp values are adjusted to correct the invalid dates for arithmetic operations.

If you want to get only the next January, the alternative is to get the first day of the month of the current date as the benchmark +1 MONTHS OR-1 MONTHS

8. Assign values to variables cannot be used with select. Into.. method instead of using Set v= (SELECT ...) The way, the specific examples are as follows:

drop function SXFM.ISORDERSUBMITDATE;
create FUNCTION SXFM.ISORDERSUBMITDATE(IN_ROW_ID DECIMAL(16,0))
RETURNS DATE
LANGUAGE SQL
BEGIN ATOMIC
DECLARE V_SUBMIT_DATE DATE;
DECLARE V_SELL_ID DECIMAL(16, 0);
DECLARE V_BUY_ID DECIMAL(16, 0);
set V_SELL_ID = (select COALESCE (RECEIVE_ID,-1) FROM IS_ORDER where ROW_ID=IN_ROW_ID);
set V_BUY_ID = (select COALESCE(PAY_ID,-1) FROM IS_ORDER where ROW_ID=IN_ROW_ID);
set V_SUBMIT_DATE = (select DATE(MAX(A.SUBMIT_DATE)) FROM AM_AUDIT_QUEUE A,SM_USER B,SM_USER C
where A.TABLE_CODE=’IS_ORDER’ AND A.TABLE_ROW_ID=IN_ROW_ID
AND A.AUDIT_EMP_ID=C.ROW_ID AND C.BRANCH_ID=V_BUY_ID --审核方为付款方
AND A.SUBMIT_EMP_ID=B.ROW_ID AND B.BRANCH_ID=V_SELL_ID); --提交方为收款方
RETURN V_SUBMIT_DATE;
END;
#SYNC 10;