The db2 tutorial I am watching is: DB2 programming skills (VI). 2.2 Try to write complex SQL statements
Try to use large and complex SQL statements, and combining multiple and simple statements into large SQL statements will improve performance.
DB2's SQL Engieer has strong ability to optimize complex statements, so you basically don't have to be careful about the performance problems of statements.
Oracle, on the contrary, it is recommended to simplify complex statements, and SQL Engieer's optimization ability 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.
A general idea is to minimize the number of SQL statements.
2.3 Selection of SQL SP and C SP
First of all, the performance of C's sp is higher than that of SQL's sp.
Generally speaking, SQL statements are more complex and the logic is relatively simple. The performance difference between SQL sp and c sp will be relatively small. In this way, from the perspective of workload, it is better to write in SQL.
If the logic is more complicated and SQL is simple, it is better to write in c.
2.4 Query optimization (HASH and RR_TO_RS)
db2set DB2_HASH_JOIN=Y (HASH sorting optimization)
Specify the use of HASH sorting when sorting. In this way, when db2 joins the table, first hash sorts the tables and then joins them, which can greatly improve performance.
Drama Shen Gang said that he would do an experiment, and 7 10 million record tables would join 10,000 records, and there would be no index.
db2set DB2_RR_TO_RS=Y
After this setting, the RR isolation level cannot be defined. If RR is defined, db2 will automatically be reduced to RS.
In this way, db2 does not need to manage Next keys, and can manage less things, which can improve performance.
2.5 How to avoid using count(*) and exists
1. First of all, you should avoid using count(*) operation, because count(*) basically needs to scan the table all, and if used a lot, it will be very slow.
2. Exists are faster than count(*), but in general, they will also scan the table. It just stops when it encounters the first matching record.
If the purpose of these two operations is to
If you want to serve, these two steps can be omitted.
Use select into directly to select fields in the record.
If no record is selected, db2 will sqlcode=100 and sqlstate='20000'
If there are multiple records, db2 will generate an error.
The program can create a continue handler for exception
continue handler for not found
Come to test.
This is the fastest way.
3. If you are judging whether it is a single one, you can use a cursor to calculate it, use a counter, accumulate it, and leave after reaching the predetermined value. This speed is also faster than count(*) because it no longer scans as long as it scans to a predetermined value, and does not need to be a scan of the full table, but it is more troublesome to write.
3 DB2 table and sp management
3.1 View stored procedure text
select text from where procname='PROC1';
3.2 Check the table structure
describe table
describe select * from
3.3 Check the impact of each table on sp (which sp are used by)
select PROCNAME from where SPECIFICNAME in(select dname from where bname in ( select PKGNAME from where bname='TB_BRANCH'))
Try to use large and complex SQL statements, and combining multiple and simple statements into large SQL statements will improve performance.
DB2's SQL Engieer has strong ability to optimize complex statements, so you basically don't have to be careful about the performance problems of statements.
Oracle, on the contrary, it is recommended to simplify complex statements, and SQL Engieer's optimization ability 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.
A general idea is to minimize the number of SQL statements.
2.3 Selection of SQL SP and C SP
First of all, the performance of C's sp is higher than that of SQL's sp.
Generally speaking, SQL statements are more complex and the logic is relatively simple. The performance difference between SQL sp and c sp will be relatively small. In this way, from the perspective of workload, it is better to write in SQL.
If the logic is more complicated and SQL is simple, it is better to write in c.
2.4 Query optimization (HASH and RR_TO_RS)
db2set DB2_HASH_JOIN=Y (HASH sorting optimization)
Specify the use of HASH sorting when sorting. In this way, when db2 joins the table, first hash sorts the tables and then joins them, which can greatly improve performance.
Drama Shen Gang said that he would do an experiment, and 7 10 million record tables would join 10,000 records, and there would be no index.
db2set DB2_RR_TO_RS=Y
After this setting, the RR isolation level cannot be defined. If RR is defined, db2 will automatically be reduced to RS.
In this way, db2 does not need to manage Next keys, and can manage less things, which can improve performance.
2.5 How to avoid using count(*) and exists
1. First of all, you should avoid using count(*) operation, because count(*) basically needs to scan the table all, and if used a lot, it will be very slow.
2. Exists are faster than count(*), but in general, they will also scan the table. It just stops when it encounters the first matching record.
If the purpose of these two operations is to
If you want to serve, these two steps can be omitted.
Use select into directly to select fields in the record.
If no record is selected, db2 will sqlcode=100 and sqlstate='20000'
If there are multiple records, db2 will generate an error.
The program can create a continue handler for exception
continue handler for not found
Come to test.
This is the fastest way.
3. If you are judging whether it is a single one, you can use a cursor to calculate it, use a counter, accumulate it, and leave after reaching the predetermined value. This speed is also faster than count(*) because it no longer scans as long as it scans to a predetermined value, and does not need to be a scan of the full table, but it is more troublesome to write.
3 DB2 table and sp management
3.1 View stored procedure text
select text from where procname='PROC1';
3.2 Check the table structure
describe table
describe select * from
3.3 Check the impact of each table on sp (which sp are used by)
select PROCNAME from where SPECIFICNAME in(select dname from where bname in ( select PKGNAME from where bname='TB_BRANCH'))