The db2 tutorial you are watching is: DB2 programming skills (V). 1.16 Stored procedures fence and unfence
The stored procedure of fence enables a new address space separately, while the stored procedure of unfence uses the same address space as the process that calls it.
Generally speaking, fence stored procedures are relatively safe.
But sometimes with some special requirements, such as if you want to retrieve the caller's pid, the stored procedure of fence will not be able to obtain it, and only the unfence can obtain it.
1.17 SP error handling usage
If other SP with return values are called in the SP, including the result set, temporary table and output parameter type SP,
DB2 will automatically issue a SQLWarning. In our original processing, SQLWarning was
It will be inserted into the log, and in the end, multiple warning messages with SQLCODE=0 will appear.
Solution:
Define a flag variable, such as DECLARE V_STATUS INTEGER DEFAULT 0,
After CALL SPNAME, SET V_STATUS = 1,
DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
IF V_STATUS <> 1 THEN
--Warning processing, insert log
SET V_STATUS = 0;
END IF;
END;
1.18 Import Usage
db2 import from of DEL messages insert into db2inst1.tb_dbf_match_ha
Be careful to add schma
1.19 Use of values
If there are multiple set statements that pay the value to the variable, it is best to use the values statement and rewrite it into one sentence. This can improve efficiency.
But be aware that values cannot pay null values to a variable.
values(null) into out_return_code;
This statement will report an error.
1.20 Specify isolation level for the select statement
select * from tb_head_stock_balance with ur
1.21 The difference between atomic and not atomic
atomic specifies this part of the program block as a whole. If any statement fails, the entire program block is equivalent to not being done. Including the statements that have been successfully executed in the atomic block, it is also equivalent to not being done, which is a bit similar to transaction.
2 Notes on DB2 programming performance
2.1 Big Data Guide Table
It should be that the load performance will be better after exporting, because the load does not write logs.
Better than select into.
The stored procedure of fence enables a new address space separately, while the stored procedure of unfence uses the same address space as the process that calls it.
Generally speaking, fence stored procedures are relatively safe.
But sometimes with some special requirements, such as if you want to retrieve the caller's pid, the stored procedure of fence will not be able to obtain it, and only the unfence can obtain it.
1.17 SP error handling usage
If other SP with return values are called in the SP, including the result set, temporary table and output parameter type SP,
DB2 will automatically issue a SQLWarning. In our original processing, SQLWarning was
It will be inserted into the log, and in the end, multiple warning messages with SQLCODE=0 will appear.
Solution:
Define a flag variable, such as DECLARE V_STATUS INTEGER DEFAULT 0,
After CALL SPNAME, SET V_STATUS = 1,
DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
IF V_STATUS <> 1 THEN
--Warning processing, insert log
SET V_STATUS = 0;
END IF;
END;
1.18 Import Usage
db2 import from of DEL messages insert into db2inst1.tb_dbf_match_ha
Be careful to add schma
1.19 Use of values
If there are multiple set statements that pay the value to the variable, it is best to use the values statement and rewrite it into one sentence. This can improve efficiency.
But be aware that values cannot pay null values to a variable.
values(null) into out_return_code;
This statement will report an error.
1.20 Specify isolation level for the select statement
select * from tb_head_stock_balance with ur
1.21 The difference between atomic and not atomic
atomic specifies this part of the program block as a whole. If any statement fails, the entire program block is equivalent to not being done. Including the statements that have been successfully executed in the atomic block, it is also equivalent to not being done, which is a bit similar to transaction.
2 Notes on DB2 programming performance
2.1 Big Data Guide Table
It should be that the load performance will be better after exporting, because the load does not write logs.
Better than select into.