GSE IMSDB2working group Preventing Locking Dirk Beauson

1
GSE IMS/DB2-working group Preventing
LockingDirk Beauson

• 13 / 03 / 2003

2
Agenda

• Production system KBC
• Default isolation level UR
• Massive parallel inserts updates
• Implementing flip-flop tables

3
Agenda

• Production system KBC
• Default isolation level UR
• Massive parallel inserts updates
• Implementing flip-flop tables

4
Production system KBC-BIB
1 active syst
SSQC IPC1 DPC1
SSPC SIPC SDPC
MPP 247365
BMP MPP bank offices MPP head office
SSQC IPC2 DPC2
5
Agenda

• Production system KBC
• Default isolation level UR
• Massive parallel inserts updates
• Implementing flip-flop tables

6
Default Isolation Level UR

• Why ?
• Using without problems procopt GO in IMS
• No application locks on rows or pages for read
  process
• Runs concurrently with most other operations
• Fast
• Cheap
• No interference with UNLOAD
• DB2 manages the isolation level during update
  process

7
Default Isolation Level UR

• DB2 implementation
• All packages bind with isolation UR
• All tools like QMF, Unload, Spufi, SAS, Proedit,
  bind with UR.

8
Default Isolation Level UR

• Development rules
• If you need actual data while reading
• Use SELECT FOR UPDATE WITH CS
• Write update statements in a proper way -
  SELECT FOR UPDATE - UPDATE WHERE CURRENT
  OF

9
Default Isolation Level UR

• Commit frequently in BMP process
• Check-point restart

10
Problems ??????

• Do we have problems ?
• NO, when observing
• development rules
• execution rule
• dont re-submit a cancelled long running BMP
  before the end of the back-out operation

11
?????? Questions ??????
?
12
Agenda

• Production system KBC
• Default isolation level UR
• Massive parallel inserts updates
• Implementing flip-flop tables

13
Massive parallel inserts updates

• Specific design for the application where
  customers can print their own statements of
  account

14
TRX 1

• TRX 1 prepares 10 statements of account (a
  bundle)
• Sents 1 statement of account to printer

1 trx
INSERT row A bundle
UPDATE row
999-9999999-99 -----------------------------------
--------------------------------- Shop 1 10
eur Shop 2 15 eur Shop 3 20 eur Shop X 99 eur
10/10
Print
15
TRX 2-gt4, 6-gt9,

• TRX 2 sents 1 statement of account to printer

1 trx
Select on table
999-9999999-99 -----------------------------------
--------------------------------- Shop 1 10
eur Shop 2 15 eur Shop 3 20 eur Shop X 99 eur
10/10
Print
16
TRX 5

• TRX 5 prepares the next 10 statements of account
  (a bundle)
• Sents 1 statement of account to printer (number
  5)

1 trx
INSERT row Next bundle
UPDATE row
999-9999999-99 -----------------------------------
--------------------------------- Shop 1 10
eur Shop 2 15 eur Shop 3 20 eur Shop X 99 eur
10/10
Print
17
TRX 10

• TRX 10 prepares the next 10 statements of
  account (a bundle)
• Sents 1 statement of account to printer (number
  10)
• Extra update on tabel (inserted row TRX 1) to
  say that the bundle is completely printed.

1 trx
INSERT row Next bundle
UPDATE row
999-9999999-99 -----------------------------------
--------------------------------- Shop 1 10
eur Shop 2 15 eur Shop 3 20 eur Shop X 99 eur
10/10
UPDATE row bundle 1
Print
18
Flow of the proces

• And so on
• Each time a bundle is finished an update is done
• There is also a delete proces every night on the
  table.
• NOT all rows are deleted !!!!!!

19
Table design 1

• January 2002
• Maximum 30.000 trx an hour

• Clustering
• KRT_NR
• ANMK_DS
• BDL_VLG_NR

CBTKID03
1 IX
- Sometimes deadlocks on the table
20
Table design 1
- Due to this design, after a few trx the
inserted row can not be inserted in a good place
into the table, and will be inserted at the back
of the table. -This is an expensive way of
inserting. - And the updates are always on the
same page. - Locking, deadlocks, ...
21
Table design 2 (1)

• September 2002
• Maximum 60.000 trx an hour

PT01
PT02
CBTKID03
PT03

• Clustering
• ROT_NR
• ANMK_DS
• Old index
• KRT_NR
• ANMK_DS
• BDL_VLG_NR

PT04
PT05
PT06
CL IX
PT07
PT08
OLD IX
PT09
PT10
22
Table design 2 (2)

• ROT_NR is the last position of ANMK_DS
• Due to the clustering index (ROT_NR and ANMK_DS)
  all the new rows are inserted at the end of each
  partition.
• So we can handle much more inserts
• And the updates are spreaded over the 10
  partitions
• At 80.000 trx an hour we got deadlocks on the
  table

23
Table design 3 (1)

• October 2002
• The peek in january will be 300.000 trx an hour.
• Be prepared.

• Clustering
• ROT_NR
• ANMK_DS
• Old index
• KRT_NR
• ANMK_DS
• BDL_VLG_NR

PT01
PT02
CBTKID03
PT03
PT04
PT05
PT06
CL IX
PT07
PT08
OLD IX
PT09
PT10

PT50
24
Table design 3 (2)

• ROT_NR (0 to 49) is the last two positions of
  ANMK_DSIf gt 49 then number - 50
• Due to the clustering index al the new rows stay
  inserted at the end of each partition.
• So we can handle much more inserts
• And the updates are spreaded over the 50
  partitions

25
Table design 2 (3)

• At peeks we ran 250.000 trx an hour
• We had 20 deadlocks during that week
• We did more or less 60 trx a second running 36
  regions
• At 40 regions the lock-wait-time started to
  become a problem. So we lowered the amount of
  regions where the trx could run in at 36.
• Average elptime trx1 -1200 ms
• Average elptime trx5-10 -600ms
• Average elptime trx2-3-4-6-7 -100ms

26
The Future ??????

• If we want to run more trx in more regions, we
  think of going to 100 PT.
• So we can handle more inserts AND the updates are
  spread 100 partitions.

• Clustering
• ROT_NR
• ANMK_DS
• Old index
• KRT_NR
• ANMK_DS
• BDL_VLG_NR

PT01
PT02
CBTKID03
PT03
PT04
PT05
PT06
CL IX
PT07
PT08
OLD IX
PT09
PT10

PT100
27
?????? Questions ??????
?
28
Agenda

• Production system KBC
• Default isolation level UR
• Massive parallel inserts updates
• Implementing flip-flop tables

29
Implementing flip-flop tables

• Specific design for the application which
  inserts transactions who have been done on an
  customers account.
• Normally the transactions on an account stay 90
  days in the database. - 100.000.000 rows
• Some transactions stay longer in the database,
  others not.

30
Problems

• High insert rate
• Retrieving rows for an account must be very
  performant -gt clustering by account
• May not be reorged frequently (24 24 / 7 7)

31
Table design
Table 2a Day Active or not
CBTREK14 FLIP Day Active or not
CBTREK03 History Day - 1
One of the flip-flop tables is active It
switches everyday around 4 PM High insert rate
CBTREK15 FLOP Day Active or not
32
CBTREK03

• Clustering
• REK_TYPE_KD
• REK_NR
• SREK_NR
• All data of one account together
• Use of BP32K
• Due to this, we have to reorg less
• Reorg parms positively influended by the use of
  the BP32K

CBTREK03 History Day - 1
33
CBTREK03

• Each day the data older than 90 (avg) days is
  deleted.
• This proces makes place for the coming inserts.
• Then all data of the not active flip-flop table
  is inserted into Table A.
• Then the specific flip-flop table is cleared.
• Due to the use of the BP32K we have to reorg
  Table A much less.

CBTREK03 History Day - 1
34
CBTREK14 / CBTREK15

• Clustering
• REK_TYPE_KD
• REK_NR
• SREK_NR
• Start with an empty table
• So all rows inserted at the back of the table
• Special runstats treatment of the tables !!!!!!

CBTREK14 CBTREK15 Day
35
Runstats on the Flip-Flop tables

• Normally we dont run runstats on these tables
• Each time we functionally reorg them, both tables
  are loaded with the actual data
• Then we runstats both tables
• We clear the not actual table

CBTREK14 CBTREK15 Day
36
Accessing the tables
-If we want to acces data day-1 or older - We
only access Table A - If we want to access data
of today - We join the flip-flop tables - If we
want to access all data of an account - We join
the 3 tables Therefore it in important that the
flip-flop tables have good runstats -gt good
access path
37
?????? Questions ??????
?