What are the tips for DB2 database coding?

The following articles mainly describe some of the minor operation skills of the DB2 database program, I found a piece of information on some tips on DB2 coding on a reputable website for you to share. The following articles describe related content.
 

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.

2. Use a temporary table

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 database 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.

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;  
 
 

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.

The other is

 
 

  
  
pcursor1: for loopcs1 as cousor1 cursor as  
  
  
select market_code as market_code  
  
  
from tb_market_code  
  
  
for update  
  
  
do  
  
  
end for;  
 
 

The advantage of this method is that it is relatively simple and does not need or allow) use open, fetch, close.

However, the with hold option cannot be used. If you want to use commit in a cursor loop, rollback cannot use this method. If you do not have the commit or rollback requirements, we recommend this method (it seems that there is a problem with the For method ).

How to modify the current record of a cursor

 
 

  
  
update tb_market_code set market_code='0' where current of cursor1; 
 
 

However, be sure to define cursor1 as a modifiable cursor.

 
 

  
  
declare cursor1 cursor for select market_code from tb_market_code  
  
  
for update;  
 
 

For update cannot be used together with group by, DISTINCT, order by, for read only, UNION, EXCEPT for union all.

5. transcoding operations similar to decode

Oracle has a function select decode (a1, '1', 'n1 ', '2', 'n2', 'n3 ') aa1 from

This function is not available in the DB2 database, but it can be modified.

 
 

  
  
select case a1  
  
  
when '1' then 'n1'  
  
  
when '2' then 'n2'  
  
  
else 'n3'  
  
  
end as aa1 from  
 
 

6. Search for the position of a character in a string similar to charindex

Locate ('y', 'dfdasfay ')

Find the location of 'y' in 'dfdasfay.

7. Calculate the number of different days for two dates similar to datedif

 
 

  
  
days(date(‘2001-06-05’)) – days(date(‘2001-04-01’)) 
 
 

Days returns the number of days calculated from 0001-01-01.

8. UDF writing example

For C, see sqllib \ samples \ cli \ udfsrv. c.

9. Create a table containing the identity value (automatically generated ID)

Writing such a table

 
 

  
  
CREATE TABLE test  
  
  
(t1 SMALLINT NOT NULL  
  
  
GENERATED ALWAYS AS IDENTITY  
  
  
(START WITH 500, INCREMENT BY 1),  
  
  
t2 CHAR(1));  
 
 

Only one column of identity is allowed in a table. The above content is an introduction to some minor operation skills of the DB2 database program. I hope you will gain some benefits.