The DB2 tutorial you are looking at is: DB2 Programming small tricks.
Some programming experience, share with everybody!
1 DB2 Programming
1.1 When you create a stored procedure, you must not use the TAB key 3
1.2 Use temporary table 3
1.3 Take the first few records from the datasheet 3
1.4 Use of Cursors 4
Note Commit and rollback 4
Two ways to define a cursor 4
method to modify the current record of a cursor 5
1.5 transcoding operations similar to decode 5
1.6 Similar charindex find character position in string 5
1.7 Similar DATEDIF calculates the difference days of two dates 5
1.8 Example of writing UDF 5
1.9 CREATE table 6 with identity values (that is, automatically generated IDs)
1.10 Prevention of field null value processing 6
1.11 Number of records processed 6
1.12 Usage of result set (cursor) returned from stored procedure 6
1.13 Type conversion Function 8
1.14 Mutual invocations of stored procedures 8
1.15 C Stored Procedure parameters Note 8
1.16 Stored Procedures fence and Unfence 8
1.17 SP Error Handling Usage 9
1.18 Import Usage 9
Use of 1.19 values 9
1.20 Specify isolation Level 10 for the SELECT statement
1.21 Atomic and not atomic difference 10
2 DB2 Programming Performance Note 10
2.1 Guide Table 10 of large data
2.2 SQL statements as much as possible write complex SQL 10
Choice of 2.3 SQL SP and C SP 10
2.4 Query Optimization (hash and RR_TO_RS) 11
2.5 methods to avoid using count (*) and exists 11
3 DB2 table and SP Management 12
3.1 See stored procedure text 12
3.2 View Table Structure 12
3.3 View the impact of each table on the SP (by which SP) 12
3.4 See what Table 12 the SP uses
3.5 See which SP is using the function 12
3.6 Modify Table Structure 12
4 DB2 System Management 13
4.1 DB2 Installation 13
4.2 Create DATABASE 14
4.3 Manual database Remote (alias) configuration 14
4.4 Stop starting database instance 14
4.5 Connection database and view current connection database 14
4.6 Stop starting database head 15
4.7 View and stop the database current application 15
4.8 See what database 15 is under this instance
4.9 View and change the configuration of the database head 16
4.9.1 the size of the sort heap 16
4.9.2 change the size of things log 16
4.9.3 to modify the heap memory size when the program heap memory is low 16
4.10 View and change the configuration of the database instance 16
4.10.1 turns on lock monitoring. 16
4.10.2 Change Diagnostics error Capture level 17
4.11 DB2 Environment variable 17
4.12 DB2 Command Environment setting 17
4.13 Change Isolation Level 17
4.14 Management Db\instance Parameters 18
4.15 Eliminate version issues after upgrade 18
4.16 View database table deadlock 18
1 DB2 Programming
1.1 When you create a stored procedure, you must not use the TAB key
CREATE PROCEDURE
The create can only use a space, but not to use the tab, otherwise compiled.
Remember, remember.
1.2 Using temporary tables
Note that temporary tables can only be built on user tempory tables space, and temporary tables cannot be built if the database is only system Tempory table space.
In addition, the temporary table of DB2 is not quite the same as that of Sybase and Oracle, and the temporary table of DB2 is valid in a session. Therefore, if the program has multiple threads, it is best not to use temporary tables, difficult to control.
When you build a temporary table, it is best to add the WITH REPLACE option so that you can create a temporary table without the drop temporary table, and an error occurs if the temporary table is created without drop in that session without the option to be displayed.
1.3 Take the first few records from the datasheet
SELECT * FROM Tb_market_code fetch a 1 rows only
But the following way does not allow
Select Market_code into V_market_code
From Tb_market_code to fetch 1 rows only;
Select the field of the first record to a variable in the following way instead
Declare V_market_code char (1);
Declare cursor1 cursor FOR select Market_code from Tb_market_code
Fetch 1 rows only for update;
Open Cursor1;
Fetch cursor1 into V_market_code;
Close Cursor1;
1.4 Use of Cursors
Pay attention to commit and rollback
Use cursors with special attention if you do not have the with HOLD option, the cursor will be closed when commit and rollback. There are many things to be aware of in Commit and rollback. Special care
Two ways to define cursors
One for
Declare continue handler for not found
Begin
Set v_notfound = 1;
End
Declare cursor1 cursor with hold for select Market_code from Tb_market_code for update;
Open Cursor1;
Set v_notfound=0;
Fetch cursor1 into V_market_code;
While V_notfound=0 do
--work
Set v_notfound=0;
Fetch cursor1 into V_market_code;
End while;
Close Cursor1;
This approach is more complex to use, but also more flexible. In particular, you can use the WITH HOLD option. You can only use this method if you have a commit or rollback in the loop and you want to keep the cursor from being closed.
&n
[1] [2] [3] [4] [5] [6] Next
