DB2 programming tips

The db2 Tutorial we are reading is a little bit of DB2 programming skills.
Some programming experience to share with you!

1 DB2 Programming
1.1 do not use the TAB key 3 after creating a stored procedure
1.2 use temporary table 3
1.3 fetch the specified first few records from the data table 3
1.4 Use of cursor 4
Note: commit and rollback 4
Two ways to define a cursor 4
Method 5 for modifying the current record of the cursor
1.5 DECODE-like transcoding operation 5
1.6 locate the position of a character in a string similar to CHARINDEX 5
1.7 calculate the number of different days for two dates, similar to DATEDIF: 5
1.8 UDF writing Example 5
1.9 create table 6 with the IDENTITY value (automatically generated ID)
1.10 prevent handling of null fields 6
1.11 retrieve the number of processed records 6
1.12 usage of returning a result set (cursor) from a stored procedure 6
1.13 type conversion function 8
1.14 mutual calling of stored procedures 8
1.15 C stored procedure parameters Note 8
1.16 Stored Procedure FENCE and UNFENCE 8
1.17 SP error handling usage 9
1.18 IMPORT usage 9
1.19 use of VALUES 9
1.20 specify the isolation level 10 for the SELECT statement
1.21 differences between ATOMIC and not atomic 10
2 DB2 programming performance note 10
2.1 big data import table 10
2.2 try to write complex SQL statements 10
2.3 SQL SP and C SP selection 10
2.4 query optimization (HASH and RR_TO_RS) 11
2.5 avoid using COUNT (*) and EXISTS methods 11
3 DB2 table and SP management 12
3.1 view Stored Procedure text 12
3.2 View table structure 12
3.3 view the impact of each table on the SP (which SP is used) 12
3.4 check which table 12 is used by SP
3.5 check which SP uses 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 remote database (alias) configuration 14
4.4 stop starting a database instance 14
4.5 connect to the database and view the currently connected database 14
4.6 stop starting database HEAD 15
4.7 view and stop the current database application 15
4.8 check which databases under this INSTANCE 15
4.9 view and change the database HEAD configuration 16
4.9.1 change the size of the sorting heap to 16
4.9.2 modify the transaction log size 16
4.9.3 modify the program heap memory size when the program heap memory is insufficient 16
4.10 viewing and changing database instance configurations 16
4.10.1 enable monitoring of lock conditions. 16
4.10.2 change diagnostic error capture Level 17
4.11 DB2 environment variable 17
4.12 DB2 command environment settings 17
4.13 change isolation level 17
4.14 manage DB \ INSTANCE parameters 18
4.15 remove version problems after upgrade 18
4.16 view database table deadlocks 18

1 DB2 Programming
1.1 do not use the TAB key after creating a stored procedure
Create procedure
You can only use spaces, but not tab keys, otherwise the compilation will fail.
Remember, remember.

1.2 use temporary tables

Note that temporary tables can only be created on user tempory tables space. If the database only has system tempory table space, temporary tables cannot be created.
In addition, the temporary tables of DB2 are not the same as those of sybase and oracle. The temporary tables of DB2 are valid in a session. Therefore, if the program has multiple threads, it is better not to use temporary tables, which is difficult to control.
It is best to add the with replace option when creating a temporary table so that the drop temporary table is not displayed, if you do not add this option when creating a temporary table and the temporary table has been created in this session and has not been dropped, an error occurs.
1.3 retrieve the specified first few records from the data table
Select * from tb_market_code fetch first 1 rows only

However, the following method is not allowed.
Select market_code into v_market_code
From tb_market_code fetch first 1 rows only;

Select the field of the first record to a variable in the following way
Declare v_market_code char (1 );
Declare cursor1 cursor for select market_code from tb_market_code
Fetch first 1 rows only for update;
Open cursor1;
Fetch cursor1 into v_market_code;
Close cursor1;

1.4 Use of cursors
Note: commit and rollback
When using a cursor, pay special attention that if the with hold option is not added, the cursor will be closed during Commit and Rollback. There are many things to note about Commit and Rollback. Be careful

Two Methods for defining a cursor
One is
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 method is complex but flexible. In particular, you can use the with hold option. This method can only be used if the cursor is not closed due to a commit or rollback in a loop.
& N

[1] [2] [3] [4] [5] [6] Next