An Overview of Belgiums DB2s Kurt Struyf

1
An Overview of Belgiums DB2sKurt Struyf
2
DB2 Version In Test
3
DB2 Version In Production
4
Production Data in Gb (Total)
459
Average data in DB2 in Belgium 459 Gb
5
Production Data in Gb (lt500 Gb)
151
Average data small users 151 Gb
6
Production Data in Gb (gt500 Gb)
1628
Average data large users 1628 Gb
7
Daily Transactions (Total)
1.148.946
Average amount of daily transactions in DB2 in
Belgium 1.148.946
8
Daily Transactions (lt500 Gb)
385.250
Average amount of daily transactions in small
DB2s in Belgium 385.250
9
Daily Transactions (gt500 Gb)
3.372.812
Average amount of daily transactions in large
DB2s in Belgium 3.372.812
10
Batch Window

• 66,66 of ALL Belgian DB2 users still have a
  Batch window ? 22h 05h
• 68,5 of users with lt500 Gb have a batch window
• 60 of users with gt500 Gb have a batch window

11
Datasharing

• There are 5 companies in datasharing in
  production
• Ranging from 2 5 members
• Ranging from 150 Gb to 2900 Gb
• 2 have a batch window

12
Static vs Dynamic SQL

• 70 of all production SQL is STATIC? 1 shop
  with 50 static? 1 shop with 25 static ?
  otherwise 95 static
• The 5 Dynamic comes from SAS, QMF, BEAM etc.
• In Business Intellegence Data Warehouse
  environments mostly dynamic (98)
• Only 3 shops use REOPT(VARS)

13
Default Isolation Level
14
Default Lock Size
15
Acquire Release
16
The Average Deadlocks

• The average amount of daily deadlocks in all
  belgian shops 23
• The average amount of daily deadlocks in the
  smaller shops (lt500 Gb) 19
• The average amount of daily deadlocks in the
  larger shops (gt500 Gb) 42

17
The Average Timeouts

• The average amount of daily timeouts in all
  belgian shops 41
• The average amount of daily timeouts in the
  smaller shops (lt500 Gb) 26
• The average amount of daily timeouts in the
  larger shops (gt500 Gb) 99

18
Locking DSNZPARMS (1 of 2)
19
Locking DSNZPARMS (2 of 2)
Deadlock values
20
Lock Avoidance Tools
54
54
50
21
21
17
Logical Locks
Design Solution
Application Solution
Maxrow
Others
Row Locking
21
User Questions

• 16 companies answered the extra user
  questionsare working with / have plans to work
  with 81 ? DB2 Connect 69 ? MQseries 69 ?
  Stored Procedures 56 ? Triggers 44 ? Identity
  Columns 44 ? Websphere 44 ? Java

22
User Questions

• Number of DBA/company avg 8.2 ranging from 1
  to 40 
• How do you organize changes to the table
  structure? Most use 3th party software 
• In precentage how many of your tablespaces are
  partitioned ? avg 3.97 (0-15) 
• In percentage how much of your data is
  compressed? avg 20.6 (1-90)

23
NBB Locking Solutions
24
NBB Locking Solutions

• Serialization using DB2 tables.
• Tasks that can disturb each other ? serialized.
• Task1 begins with check serialization row in
  DB2 tableinsert row in DB2 serialization table
  commit

25
NBB Locking Solutions

• Task2 begins with check DB2 table ? not free
  ? waits x seconds ? retry times
• Task1 ends with delete row commit
• Different flags if both tasks read only

26
NBB Locking Solutions

• 2. Dividing similar data over different tables
• ? different tables for incoming and outgoing
  messages
• ? summary tables using query select
  fromwhere pk instead of select fromwhere
  key (select max())
• ? historical data and current data in seperate
  tables

27
NBB Locking Solutions

• 3. Pre-formating during the night of daily
  tables
• Step1. During the night, empty the table.
• Step2. During the night, expected amount of rows
  are being inserted.
• Step3. During the day, only UPDATE is used
• ? ADVANTAGE no work on the PK-index

28
NBB Locking Solutions

• 4. Seperate LUWs
• Split 1 long LUW up into several shorter LUWs.
• Consequence application ROLLBACK of commited
  work.

29
Ardatis Blockfetch 2 Threads
30
Ardatis Blockfetch 2 Threads

• Components the lead to this problem
• DB2 V5? Row level locking? Cursor stability?
  Current data yes
• Application devellopment tool Objectstar

31
Ardatis Blockfetch 2 Threads
O Application
O DB2 Server
DB2
32
Ardatis Blockfetch 2 Threads
Buffer full, S lock on last read row
Upd transn
Upd trans2
Upd trans1
Read Phase
-911
33
Ardatis Blockfetch 2 Threads

• Sollution
• Current data NO

34
Alcatel OPS rule
35
Alcatel OPS rule

• Purpose
• Keep track of all the Timeouts Deadlocks on
  the system
• And add history to it

36
Alcatel OPS rule

• Step 1
• Trap timeout message DSNT376I to get the
  HOLDER information.
• Trap deadlock message DSNT375I to get the
  HOLDER information.
• Trap message DSNT5O1I to get the VICTIM and
  resource information.

37
Alcatel OPS rule

• Step2
• Insert into locktable values (type, holder,
  victim, resource, timestamp)

38
Alcatel OPS rule

• Step3
• Query your locktable,
• however you want

39
Dexia Logical Locks
40
Dexia Logical Locks

• Problem pseudo-conversational transactions lose
  their locks when they show the screen (release
  commit)
• If something goes wrong in a batch job, manual
  correction of those rows, using CICS transactions
• ?Mutiple users might try to correct the same
  error
• When they see the list, they no longer have the
  lock

41
Dexia Logical Locks
DBLOWNTB
User1
PK
Insert Row
FK
Lockingpackage
Insert Row
DBLOCTB
42
Dexia Logical Locks

• Lockowner (Pk) Terminal ID (CICS) Userid
  (TSO) Jobname (Batch)
• Status 1 waiting 2 active 3 expired
• Timestamp lock begin
• Timestamp lock end

DBLOWNTB
1 row for each lockowner
43
Dexia Logical Locks

• Identification locked table
• Identification locked row or XFFFFFFFF
• Locklevel S or X
• Lockowner (Fk) Terminal ID (CICS) Userid
  (TSO) Jobname (Batch)

DBLOCTB
1 row for each lock
44
Dexia Logical Locks

• Suppose USER2 wants to lock the same resource as
  User1 ? is not allowed on the data, receives a
  message.
• Depending on which module in the locking
  package is called abort or retry
• In case of retry, the application will check
  the lock every 15 sec.

45
Dexia Logical Locks

• Warning
• this method required a iron discipline !!?
  before commit of any data changeyou have to
  CHECK if your lock is still valid !!

46
Dexia Buffer Tables
47
Dexia Buffer Tables

• Problem you NEED sequential file as output of a
  DB2 related batchjob.BUT you want to be
  restartable !!What do you do in case of a
  rollback ? What if you restart ?

48
Dexia Buffer Tables

• Solution You insert your sequential output
  into a buffer table and you add an EXTRA UNLOAD
  step.Let DB2 take care of ROLLBACK COMMIT !!