DB2 Programming Skills (vi) _DB2

Source: Internet
Author: User
Tags db2
The DB2 tutorial being looked at is: DB2 programming skills (VI). 2.2 SQL statements as much as possible to write complex SQL
By using large, complex SQL statements, you can improve performance by combining multiple, simple statements into large SQL statements.
DB2 's SQL Engieer has a stronger ability to optimize complex statements, and basically does not have to be careful about the performance of statements.
Oracle, on the contrary, recommends simplifying complex statements, and SQL Engieer optimization is not particularly good.
This is because every SQL statement has a variety of operations, such as Reset Sqlcode and SQLState, that can consume database performance.
A general idea is to minimize the number of SQL statements.
2.3 SQL SP and C SP selection
First, C's SP has a higher performance than the SQL Sp.
Generally speaking, the SQL statement is more complex, and the logic is simpler, the SQL SP and C SP Performance difference will be relatively small, so from the workload, the use of SQL write better.
And if the logic is more complex, SQL is simpler, write in C better.

2.4 Query Optimization (hash and rr_to_rs)
Db2set db2_hash_join=y (HASH sort optimization)
Specifies that the sort is sorted with a hash, so that DB2 can be greatly improved by hashing and then join the tables as they join.
The drama Shengang said to do the experiment, 7 10 million records table's do join takes 10,000 records, again does not have the index the case 72 seconds.

Db2set db2_rr_to_rs=y
After this setting, the RR isolation level cannot be defined, and if the definition rr,db2 is automatically reduced to Rs.
In this way, DB2 does not have to manage next key and can manage something less, which can improve performance.


2.5 methods to avoid using count (*) and exists
1, first of all to avoid using the count (*) operation, because COUNT (*) is basically to do a full scan of the table, if the use of a lot will cause very slow.
2, exists is faster than count (*), but in general will scan the table, it just hit the first line of the record to stop.

If the purpose of doing both of these operations is to
Select INTO service, you can omit these two steps.
Select the field in the record directly using SELECT INTO.

If there are no records to choose from, DB2 will sqlcode=100 and sqlstate= ' 20000 '
If there is more than one record, DB2 will produce an error.

Programs can create continue handler for exception
Continue handler for not found
To detect.
This is the quickest way.

3, if the judgment is not a, you can use a cursor to calculate, with a counter, add, to reach the predetermined value and then leave. This speed is also faster than COUNT (*), because it is no longer scanned as long as it is scanned to a predetermined value, and does not need to be scan the whole table, but it is more cumbersome to write.


3 DB2 table and SP management
3.1 See 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 effects of the tables on the SP (which SP is using)
Select ProcName from Syscat. Procedures where Specificname in (select Dname to Sysibm.sysdependencies where bname in (select Pkgname) from Syscat.pac KAGEDEP where bname= ' Tb_branch ')


<

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.