3250 - 利用多行处理技术提升 Db2 性能.pdf

1、Orlando,FLOctober 69IBM TechXchange 2025 Session code-3250Chris Crone Broadcom Distinguished EngineerEnhance Db2 Performance with Mult-Row Processing TechniquesHow I Tested DisclaimerIBM TechXchange|2025 IBM Corporation2Z16 60X/z15 60 x Dedicated Performance Sysplex/Datasharing GPOnly used z16 2 GCP

2、s3 zIIPs1 TB Memory(Generous Bufferpools)Db2 13 FL V13R1M506Dedicated DS8K DASD(only cabled to Performance LPARs)Db2 Implicit Tables Non-PartitionedIndexes added/deleted to emphasize overhead or for needed functionData Generated from HIS is repetitiveTestcases with“no business logic”,“no think time”

3、will likely overestimate real world benefits Your Results WILL VARYWILL VARYTEST,TEST,TESTTEST,TEST,TESTAgenda 01 02 03 04 05 06 Overview Multi-Row FETCH Multi-Row INSERT SELECT FROM MERGE(aka UPSERT)ConclusionIBM TechXchange|2025 IBM CorporationOverviewStandard INSERT and FETCH process one row of d

4、ata at a timeDELETE,UPDATE,and INSERT with SUBSELECT can process one or more rows of data(Not really the subject of this presentation)Db2 also supports a plethora of other Multi-Row processing Multi-Row FETCH Multi-Row INSERT SELECT FROM(INSERT,UPDATE,DELETE,MERGE)MERGEMulti-Row Processing can save

5、significant CPU and Elapsed TimeLets Get StartedAgenda 01 02 03 04 05 06 Overview Multi-Row FETCH Multi-Row INSERT SELECT FROM MERGE(aka UPSERT)ConclusionIBM TechXchange|2025 IBM CorporationDECLARE CURSORDECLARE CURSORcursor-name names the cursorNO SCROLL/SCROLL Controls cursor scrollabilityAvoid AS

6、ENSITIVE(default)due to ambiguityholdability WITH/WITHOUT HOLD controls cursorbehavior on COMMITreturnability WITH/WITHOUT RETURN controls if the cursor is to be returned from a procedure as a result setrowset-positioning WITH/WITHOUT ROWSET POSITIONING Specifies if multiple rows can be accessed as