Correct DB2 Programming Application

The following articles mainly describe DB2 programming. We first do not use the TAB key to describe it after creating a stored procedure. If you program DB2, if you are curious, the following articles will unveil its mysteries.

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. If there is a commit or rollback in the loop and the cursor should not be closed, you can only use this method. The above content is an introduction to the DB2 programming skills section, I hope you will have some gains.