What knowledge points need to be mastered when using the DB2 database?

Source: Internet
Author: User
Tags db2 date ibm db2 ibm db2 database

This article focuses on some of the relevant knowledge points that we must understand during the correct use of the DB2 database, including the maintenance of the DB2 database in the future, there is a lot of knowledge to learn. This article will provide you with several knowledge points that must be understood. I believe this will be used in future work.

Knowledge points that must be learned when using the IBM DB2 database:

1. after installing the DB2 database, you can operate it through the command line or graphical interface. If your database server is not on the local machine, you need to configure a client connection in "client configuration helper.

2. the control center cannot add, delete, modify, or modify data. You can only write SQL statements to achieve this. Although the tools provided by quest can add data, they cannot use the copy, paste, and Tab keys. They must be input one by one, you can edit and modify the existing data by clicking the mouse, but the data cannot be commit, A non-write delete statement is not allowed.

However, you can use pb to edit data in cells. One drawback is that the button for editing data is too close to the button for deleting a table. If the button for deleting a table is incorrect, pb, but the table is deleted without prompting, faint

3. The order by statement cannot be directly used in the DB2 view. It must be written as follows:

 
 
  1. select × from(select a,b,c from table1 order by a)as tab 

Note: the premise of this writing method is that you have already installed patches.

4. Stored Procedure problems:

DB2 provides ltrim and rtrim functions, but trim functions are not provided. If you want to remove spaces at both ends of the characters, sorry, you must use ltrim (rtrim ()) you cannot use the expression to assign values to the insert statement. You must first assign the value to a variable and call other stored procedures without using constants as parameters, you must assign the value of this constant to a variable, and then use this variable as the parameter.

Select * from table fetch first n rows only statement is not available in the Stored Procedure

5. dynamic SQL can be used in stored procedures, but not in functions. kao

6. The cursor is automatically closed when a commit or rollback occurs. Therefore, you must use DB2 for separate submission with caution.

Proc builder always has insufficient memory during debugging and the screen is spent. However, if the pause does not take effect for a long time during breakpoint debugging, the system will prompt a timeout error.

7. Development Issues:

When using the DB2 date variable + 1 months or date variable-1 MONTHS to retrieve the date, for example, when the date variable value is, in the stored procedure, assign the date variable + 1 MONTHS to another

An error occurs when the date variable is used. The corresponding SQLSTATE is 01506 (db2? 01506): the DATE or TIMESTAMP value is adjusted to correct the invalid DATE obtained by arithmetic operations.

If you want to obtain only the next month, the alternative method is to get the first day of the month of the current date as the benchmark + 1 months or-1 MONTHS

8. You cannot assign values to variables using select ...... Into ...... Use set v = (select ......) Method,

An example is as follows:

 
 
  1. drop function SXFM.ISORDERSUBMITDATE;   
  2. CREATE FUNCTION SXFM.ISORDERSUBMITDATE(IN_ROW_ID DECIMAL(16,0))   
  3. RETURNS DATE   
  4. LANGUAGE SQL   
  5. BEGIN ATOMIC DECLARE V_SUBMIT_DATE DATE;   
  6. DECLARE V_SELL_ID DECIMAL(16, 0);   
  7. DECLARE V_BUY_ID   
  8. DECIMAL(16, 0);   
  9. set V_SELL_ID = (SELECT COALESCE(RECEIVE_ID,-1) FROM IS_ORDER WHERE ROW_ID=IN_ROW_ID);   
  10. set V_BUY_ID = (SELECT COALESCE(PAY_ID,-1) FROM IS_ORDER WHERE ROW_ID=IN_ROW_ID);   
  11. set V_SUBMIT_DATE = (SELECT DATE(MAX(A.SUBMIT_DATE)) FROM AM_AUDIT_QUEUE A,SM_US   
  12. ER B,SM_USER C   
  13. WHERE A.TABLE_CODE=’IS_ORDER’   
  14. AND A.TABLE_ROW_ID=IN_ROW_ID   
  15. AND A.AUDIT_EMP_ID=C.ROW_ID AND C.BRANCH_ID=V_BUY_ID  

Reviewer: payer and a. SUBMIT_EMP_ID = B. ROW_ID and B. BRANCH_ID = V_SELL_ID); -- submit as recipient

 
 
  1. RETURN V_SUBMIT_DATE;   
  2. END;   
  3. #SYNC 10; 

The above content is an introduction to several knowledge points that must be understood when using DB2.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.