The db2 tutorial I am watching is: DB2 programming tips.
Some programming experience will be shared with you!
1 DB2 programming
1.1 Do not use the TAB key after CREATE when building stored procedures 3
1.2 Using Temporary Table 3
1.3 Get the first few records from the data table 3
1.4 Use of cursors 4
Note commit and rollback 4
Two ways to define cursors 4
Methods to modify the cursor's current record 5
1.5 Transcoding operations similar to DECODE 5
1.6 Similar to CHARINDEX to find the position of characters in the string 5
1.7 Similar to DATEDIF to calculate the number of days difference between two dates 5
1.8 Example of writing UDF 5
1.9 Create a table with IDENTITY values (i.e., the automatically generated ID) 6
1.10 Prevent field null values processing 6
1.11 Number of records obtained for processing 6
1.12 Usage of returning result sets (cursors) from stored procedures 6
1.13 Type Conversion Function 8
1.14 Mutual calls of stored procedures 8
1.15 Note on C stored procedure parameters 8
1.16 Stored procedures 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 isolation level for SELECT statement 10
1.21 The difference between ATOMIC and NOT ATOMIC 10
2 DB2 programming performance notes 10
2.1 Big Data Guide 10
2.2 Try to write complex SQL statements as much as possible 10
2.3 Selection of 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 View stored procedure text 12
3.2 Check the table structure 12
3.3 Check the impact of each table on SP (which SPs are used) 12
3.4 Check which tables SP uses 12
3.5 Check which SPs are used in FUNCTION 12
3.6 Modify the table structure 12
4 DB2 system management 13
4.1 DB2 installation 13
4.2 Creating DATABASE 14
4.3 Manually make remote database (alias) configuration 14
4.4 Stop starting the database instance 14
4.5 Connecting to the database and viewing the current connection to the database 14
4.6 Stop starting the database HEAD 15
4.7 View and stop the database current application 15
4.8 Check out what DATABASE 15 is under this INSTANCE
4.9 View and change the configuration of database HEAD 16
4.9.1 Change the size of the sort heap 16
4.9.2 Change the size of the thing log 16
4.9.3 Modify the program heap memory size when there is insufficient program heap memory 16
4.10 View and change the configuration of the database instance 16
4.10.1 Turn on monitoring of locking conditions. 16
4.10.2 Change the diagnostic error capture level 17
4.11 DB2 environment variables 17
4.12 DB2 command environment settings 17
4.13 Change the isolation level 17
4.14 Manage DB\INSTANCE parameters 18
4.15 Eliminate version issues after upgrading 18
4.16 View the deadlock of database tables 18
1 DB2 programming
1.1 Do not use the TAB key after creating a stored procedure
create procedure
Only spaces can be used after creating, and tabs are not available, otherwise the compilation will not be passed.
Remember, remember.
1.2 Using temporary tables
Note that temporary tables can only be built on user tempory tables space. If the database only has system tempory table space, temporary tables cannot be built.
In addition, the temporary tables of DB2 are different from the temporary tables of sybase and oracle. The temporary tables of DB2 are valid in a session. Therefore, if the program has multiple threads, it is best not to use temporary tables, as it is difficult to control.
It is best to add the with and replace option when creating a temporary table, so that the drop temporary table can be not displayed. If the option is not added when creating a temporary table and the temporary table is created and there is no drop, an error will occur.
1.3 Get the first few records from the data table
select * from tb_market_code fetch first 1 rows only
But the following method does not allow
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
Pay attention to commit and rollback
When using cursors, pay special attention to the cursor if the with hold option is not added, the cursor will be closed when Commit and Rollback. Commit and Rollback have a lot to pay attention to. Be especially careful
Two ways to define cursors
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 more complicated to use, but it is also more flexible. In particular, you can use the with hold option. If there is commit or rollback in the loop and you want to keep the cursor from being closed, you can only use this method.
&n
Some programming experience will be shared with you!
1 DB2 programming
1.1 Do not use the TAB key after CREATE when building stored procedures 3
1.2 Using Temporary Table 3
1.3 Get the first few records from the data table 3
1.4 Use of cursors 4
Note commit and rollback 4
Two ways to define cursors 4
Methods to modify the cursor's current record 5
1.5 Transcoding operations similar to DECODE 5
1.6 Similar to CHARINDEX to find the position of characters in the string 5
1.7 Similar to DATEDIF to calculate the number of days difference between two dates 5
1.8 Example of writing UDF 5
1.9 Create a table with IDENTITY values (i.e., the automatically generated ID) 6
1.10 Prevent field null values processing 6
1.11 Number of records obtained for processing 6
1.12 Usage of returning result sets (cursors) from stored procedures 6
1.13 Type Conversion Function 8
1.14 Mutual calls of stored procedures 8
1.15 Note on C stored procedure parameters 8
1.16 Stored procedures 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 isolation level for SELECT statement 10
1.21 The difference between ATOMIC and NOT ATOMIC 10
2 DB2 programming performance notes 10
2.1 Big Data Guide 10
2.2 Try to write complex SQL statements as much as possible 10
2.3 Selection of 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 View stored procedure text 12
3.2 Check the table structure 12
3.3 Check the impact of each table on SP (which SPs are used) 12
3.4 Check which tables SP uses 12
3.5 Check which SPs are used in FUNCTION 12
3.6 Modify the table structure 12
4 DB2 system management 13
4.1 DB2 installation 13
4.2 Creating DATABASE 14
4.3 Manually make remote database (alias) configuration 14
4.4 Stop starting the database instance 14
4.5 Connecting to the database and viewing the current connection to the database 14
4.6 Stop starting the database HEAD 15
4.7 View and stop the database current application 15
4.8 Check out what DATABASE 15 is under this INSTANCE
4.9 View and change the configuration of database HEAD 16
4.9.1 Change the size of the sort heap 16
4.9.2 Change the size of the thing log 16
4.9.3 Modify the program heap memory size when there is insufficient program heap memory 16
4.10 View and change the configuration of the database instance 16
4.10.1 Turn on monitoring of locking conditions. 16
4.10.2 Change the diagnostic error capture level 17
4.11 DB2 environment variables 17
4.12 DB2 command environment settings 17
4.13 Change the isolation level 17
4.14 Manage DB\INSTANCE parameters 18
4.15 Eliminate version issues after upgrading 18
4.16 View the deadlock of database tables 18
1 DB2 programming
1.1 Do not use the TAB key after creating a stored procedure
create procedure
Only spaces can be used after creating, and tabs are not available, otherwise the compilation will not be passed.
Remember, remember.
1.2 Using temporary tables
Note that temporary tables can only be built on user tempory tables space. If the database only has system tempory table space, temporary tables cannot be built.
In addition, the temporary tables of DB2 are different from the temporary tables of sybase and oracle. The temporary tables of DB2 are valid in a session. Therefore, if the program has multiple threads, it is best not to use temporary tables, as it is difficult to control.
It is best to add the with and replace option when creating a temporary table, so that the drop temporary table can be not displayed. If the option is not added when creating a temporary table and the temporary table is created and there is no drop, an error will occur.
1.3 Get the first few records from the data table
select * from tb_market_code fetch first 1 rows only
But the following method does not allow
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
Pay attention to commit and rollback
When using cursors, pay special attention to the cursor if the with hold option is not added, the cursor will be closed when Commit and Rollback. Commit and Rollback have a lot to pay attention to. Be especially careful
Two ways to define cursors
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 more complicated to use, but it is also more flexible. In particular, you can use the with hold option. If there is commit or rollback in the loop and you want to keep the cursor from being closed, you can only use this method.
&n
[1] [2] [3] [4] [5] [6] Next article