DB2 programming skills (6)

The db2 tutorial is: DB2 programming skills (6 ). 2.2 write complex SQL statements as much as possible
Try to use large and complex SQL statements. Combining Multiple and simple statements into large SQL statements will improve the performance.
DB2's SQL Engieer has strong optimization capabilities for complex statements, so you don't have to worry about the statement performance.
On the contrary, it is recommended that complicated statements be simplified, and SQL Engieer's optimization capability is not particularly good.
This is because every SQL statement will have various operations such as reset SQLCODE and SQLSTATE, which will consume database performance.
The general idea is to minimize the number of SQL statements.
2.3 SQL SP and C SP Selection
First, the performance of the C sp is higher than that of the SQL sp.
In general, SQL statements are complex, while the logic is relatively simple. The performance difference between SQL sp and c sp will be relatively small. In this way, it is better to write SQL statements in terms of workload.
If the logic is complex, SQL is relatively simple, and c is better to write.

2.4 query optimization (HASH and RR_TO_RS)
Db2set DB2_HASH_JOIN = Y (HASH Sorting optimization)
HASH Sorting is used when sorting is specified. In this way, db2 performs hash Sorting on each table before joining, which greatly improves the performance.
Dr. Shen Gang said that in the experiment, 10 million records were obtained from 7 10000 record tables for join operations, and 72 seconds without any index.

Db2set DB2_RR_TO_RS = Y
After this setting, the RR isolation level cannot be defined. If RR is defined, db2 will also be automatically downgraded to RS.
In this way, db2 does not need to manage the Next key, but can manage less things to improve performance.


2.5 avoid using the count (*) and exists Methods
1. Avoid using the count (*) operation first, because count (*) basically needs to scan all the tables. If you use many tables, it will be slow.
2. exists is faster than count (*), but in general it also scans the table. It just stops when it encounters the first matching record.

If the purpose of these two operations is
The select into service can omit these two steps.
Directly use select into to select fields in the record.

If no record is selected, db2 will set sqlcode = 100 and sqlstate = '20160901'
If there are multiple records, db2 will generate an error.

The program can create a continue handler for exception.
Continue handler for not found
To detect.
This is the fastest way.

3. If it is to judge whether it is a single item, you can use a cursor to calculate it, use a counter, accumulate it, and exit after reaching the predefined value. This speed is faster than count (*), because it does not scan the entire table as long as it scans the predefined value, but it is troublesome to write.


3. DB2 table and sp management
3.1 view Stored Procedure text
Select text from syscat. procedures where procname = 'proc1 ';
3.2 View table structure
Describe table syscat. procedures
Describe select * from syscat. procedures

3.3 view the impact of each table on the sp (which sp is used)
Select PROCNAME from SYSCAT. PROCEDURES where SPECIFICNAME in (select dname from sysibm. sysdependencies where bname in (select PKGNAME from syscat. packagedep where bname = 'tb _ branch '))


<