Building Flexible Database Systems

1
Building Flexible Database Systems

• GSE 02/04/2009
• Dirk Beauson
• KBC Global Services NV

2
Agenda

• KBC
• The case
• Design of the database
• Investigation on flexible database design
• Dynamic Screens
• What we implemented

3
The KBC Group in Central and Eastern Europe

• KBC was a Belgium company that was especially
  operating in Belgium, with some branches (10)
  spread all over the world
• Since a few years, KBC works together with
  several countries of Central Europe
• Czech Republic
• Hungary
• Poland
• Slovakia
• Slovenia

4
The KBC Group in Central and Eastern Europe

• Recent acquisitions
• Romania
• Bulgaria
• Serbia
• Russia
• Daughters are also active in
• Bosnia-Herzegovina
• Macedonia
• Montenegro

5
The KBC Group in The World

• Nowadays the number of branches, spread all over
  the world also increased a lot
• Not only inside Europe, but also all over the
  world
• America
• Asia

6
Agenda

• KBC
• The case
• Design of the database
• Investigation on flexible database design
• Dynamic Screens
• What we implemented

7
The case

• Develop a new ICT system that contains all Non
  Life Insurances (car, fire, ) of Belgium, Poland
  and in the future ... .
• Build it as flexible as possible
• Deal with other companies
• Deal with different needs
• Columns
• Authorizations
• Time to market !!!

8
Some assumptions

• When developing new ICT systems we now make sure
  that there is synergy between different companies
  in different countries
• So we try to develop 1 new system that can be
  used by more than 1 company
• Less maintenance
• One look and feel for the entire KBC group

9
Some assumptions

• Build it on the KBC mainframe retail platform in
  DB2
• Follow the rules, guidelines, release moments of
  that platform
• Lots of flexibility in the system to be designed
  regarding
• Processes
• Databases
• Product definitions
• Screens
• Extra fields on screens

10
Some assumptions

• Build it in UNICODE !!!

11
Some restrictions

• Release moments
• 8 release moments each year
• Database structure changes may only be done
  during these release moments
• Major program changes may only be done during
  these release moments
• FIX-process
• Only for small program changes

12
In scope

• The new to be designed system has to contain all
  data, processes, regarding all the Non-Life
  Insurances of KBC Insurance and Warta
• And it must be open
• Plug-in other companies
• Define new products
• Non-Life Insurances are
• Car
• Fire

13
History of both previous systems
14
The way we want to go

• One DB2 database model
• NO separate database models or tables regarding
• Infrastructure
• Car
• Fire

15
Agenda

• KBC
• The case
• Design of the database
• Investigation on flexible database design
• Dynamic Screens
• What we implemented

16
Design of the database

• First the people of KBC Insurance talked and
  discussed very much with their colleagues of
  Warta about
• Functionality
• Differences
• Needs
• Donts
• Time to Market Flexibility needed
• Restrictions

17
Design of the database

• At a certain moment we, the applicative DBAs,
  came in to
• Do some talking about flexible
• Database possibilities
• Designs
• DB2-stuff
• But they didnt tell us any of the restrictions

18
Design of the database

• I prepared all these magical things we have in
  DB2, and

Add columns
Add partitions
Rotate partitions
Change partition limits
Rebalance partitions
19
Design of the database

• Reasons
• Database changes ? 8 release moments in a year
• add column in between 2 release moments could
  not be used
• Only 1 datamodel in DB2,
• NO separate Datamodel for
• Infrastructure
• Car
• Fire
• Most columns will not be reused for car and fire
  !!!
• Decision from architectural and business point of
  view

20
Design of the database

• First thing going through my mind
• ???????????????????????????????????????
• There are so many things we could explore using
  DB2, to introduce the flexibility they need, and
  none of them might be used
• How do we have to solve this ?????????
• So goodbye standard relational design

21
Design of the database

• Feedback from our colleagues of Warta was that
  they work a lot with turned tables

22
Design of the database

• Put columns into rows
• It is a very well known technique, and it works
  well

23
Agenda

• KBC
• The case
• Design of the database
• Investigation on flexible database design
• Dynamic Screens
• What we implemented

24
Turned tables

• EXAMPLE
• Standard table design

25
Turned tables

• EXAMPLE
• Turned table design

26
Turned tables

• Standard table design

Impact ???
27
Turned tables

• Turned table design

Impact ???
28
Turned tables

• Standard table design

Impact ???
29
Turned tables

• Turned table design

Impact ???
30
Conclusion Adding columns

• It is much more easy to add a column to a turned
  table than adding it to a standard designed table
• No reorganisation of your database needed
• Just insert new rows

31
Turned tables
Now lets talk about

• Querying
• Space used
• Impact of clustering
• Online vs batch
• Overall performance

32
Querying turned tables (1)

• EXAMPLE 1
• Select the contract_numbers of all the cars
  build in 2005

33
Querying turned tables (1)

• Standard table design
• Select contract_number from table
• where yoc 2005
• Result
• CONTRACT
• NUMBER
• -----------
• 00000000101
• 00000003320

34
Querying turned tables (1)

• Turned table design
• Select contract_number
• from table
• where column_name yoc
• and value 2005
• Result
• CONTRACT
• NUMBER
• -----------
• 00000000101
• 00000003320

35
Querying turned tables (1)

• Performance

36
Querying turned tables (2)

• EXAMPLE 2
• Select all data of all the cars build in 2005

37
Querying turned tables (2)

• Standard table design
• Select contract_number, brand, type, cc, yoc,
  color from table
• where yoc 2005
• Result
• CONTRACT
  
• NUMBER BRAND TYPE CC
  YOC COLOR
• ----------- ------------------- ------ ----
  ---- -----
• 00000000101 CITROEN C3 1100
  2005 BLUE
• 00000003320 OPEL ASTRA 1400
  2005 GREY

38
Querying turned tables (2)

• Turned table design
• Select contract_number, column_name, value
• from table
• where contract_number in (select
  contract_number
• from table
• where column_name yoc
• and value 2005)
• and column_name in (brand,
• type,
• cc,
• yoc,
• color)
• order by contract_number

Result
CONTRACT COLUMN
NUMBER NAME VALUE
----------- ------------ -------00000000101
BRAND CITROEN
00000000101 CC 1100
00000000101 COLOR BLUE
00000000101 OBJECT_TYPE CAR
00000000101 TYPE C3
00000000101 YOC 2005
00000003320 BRAND OPEL
00000003320 CC 1400
00000003320 COLOR GREY
00000003320 OBJECT_TYPE CAR
00000003320 TYPE ASTRA
00000003320 YOC 2005
39
Querying turned tables (2)

• Performance

40
Conclusion Querying

• Much more complex way of writing queries
• Other way of fetching results in your program
• Doing a lot more fetches in your program
• More CPU consuming
• More getpages
• More IO
• And these examples were very, very easy !!!

41
Space used

• In both tables 12.800 contract numbers are
  registered
• Table
• More columns? more rows ? more space needed
• Index
• More rows ? more space needed

42
Conclusion Space Used

• Turned tables use by default (much) more space
  than standard tables
• Always overhead of your
• Keys
• Column names
• More space means
• More I/O
• More GP
• Longer ellapsed times
• More CPU

43
Impact of clustering

• Standard table design
• If you access one row via an index
• Clustering doesnt matter that much
• Drill down index
• Get data page needed

ROOT
NON-Leaf pages
Leaf pages
Data pages
44
Impact of clustering

• Standard table design
• If you access a lot of rows in sequence in 1
  cursor
• Clustering matters
• If nicely clustered, dynamic prefetching can be
  activated
• Less I/O !!!

ROOT
NON-Leaf pages
Leaf pages
Data pages
45
Impact of clustering

• Standard table design
• If you access a lot of rows in sequence in more
  cursors
• Clustering matters
• If nicely clustered, dynamic prefetching can be
  activated
• Less I/O !!!

ROOT
NON-Leaf pages
Leaf pages
Data pages
46
Impact of clustering

• Standard table design
• If you access a lot of rows in random sequence
• Clustering doesnt matter that much
• Expensive by default, so each time
• Drill down index
• Get data page needed

ROOT
NON-Leaf pages
Leaf pages
Data pages
47
Impact of clustering

• Turned table design
• Clustering much more important !!!!!!
• Why ???
• If you want to access some or all data of a
  standard row
• You now have to fetch a row for each column
• In all cases
• (Clustering does matter)²
• Impact ???

Clustering of the rows
Clustering of the columns
48
Conclusion Impact of clustering

• Clustering is very important if you start working
  with turned tables
• (Clustering does matter)²
• Especially clustering all data forming a row of a
  std table is very important for read performance
  reasons

49
Online vs. batch
Process
Process
1 time
100.000 of times
50
Overall Performance of turned tables

• Using turned tables will always cost more
• Recurrent costs
• Performance
• Create cost more complex queries
• Even if all your rows are very well clustered
• more fetches
• more pages ? Max 256 rows /page !!!
• Clustering matters !!!
• Consider creating standard DB2-tables with data
  derived from the turned table, only for reading
  purposes on a x-time based period

51
Conclusion Use of Turned Tabels

• If possible ? standard DB2-tables !!!
• If not possible ? turned tables can be a solution
  when
• Use small tables
• Data can be loaded in clustering sequence
• Flexibility much more important than performance
  !!!
• Time to market shortened
• NO DB-actions needed adding a column
• Be aware
• Cluster all data forming a row of a standard
  table
• Much more fetches
• Not done in batch

52
Alternatives studied

• Standard DB2 table design lt-gt zone
• Zone with copybook
• Zone with definition in a DB2 table

53
Standard lt-gt Zone

• ZONE is defined as a column of a DB2-table.
• All the other columns are just plain, known,
  used, DB2 columns
• What we put in the ZONE
• New fields we want to add more or less on the fly
• Data that is different among the types of rows
  defined in the table
• Define all the columns, NULLS ALLOWED
• Split up the tables (one for each type)

Flexible design
Standard design
54
Zone with COPYBOOK
55
Zone with COPYBOOK

• COPYBOOK type CAR
• 01 CAR PIC X(80).
• 03 BRAND PIC X(26).
• 03 TYPE PIC X(06).
• 03 CC PIC X(04).
• 03 YOC PIC X(04).
• 03 FILLER PIC X(40).
• COPYBOOK type FIRE
• 01 FIRE PIC X(80).
• 03 BUILD-TYPE PIC X(12).
• 03 FLOORS PIC S9(03) COMP-3.
• 03 SQUARE-MTR PIC S9(11) COMP-3.
• 03 FILLER PIC X(60).

56
Zone with COPYBOOK

• We can move the zone easily straight into a
  copybook
• No parsing needed
• NO querying on the fields described in the
  copybook
• Faking IMS in DB2 ???
• Adding a new field
• Edit your copybook
• Put it into production
• Add logic to your programs to work with the new
  field
• Compile and link your programs
• Put them into production
• MAX size !!!
• Tip
• The name of the copybook can be included in a
  column of your table

57
Zone with definition in a DB2 table
58
Zone with definition in a DB2 table

• Definition of the DB2-table

59
Zone with definition in a DB2 table

• How to get your information
• First read the DB2-table
• Fetch the right data to unpack the zone
• Unpack the zone ? expensive string functions in
  COBOL
• NO querying on the fields described in the zone
• Faking IMS in DB2 ???
• Adding a new column
• Add a new row in the table
• Add logic to your programs to work with the new
  field
• Compile and link your programs
• Put them into production
• MAX size !!!
• Recurrent cost much to high

60
Conclusion Use of ZONE

• If possible ? standard DB2-tables !!!
• If not possible ? working with a zone can be a
  solution when
• Flexibility is important
• Time to market shortened
• NO DB-actions are allowed adding a new column
• Be aware
• No querying on any of the colums defined in the
  zone
• Not going to save lots of space
• NULLS dont use much space
• Compression can help you a lot
• Faking IMS in DB2 !!!
• Define process to convert added columns in the
  ZONE as a real column

61
Agenda

• KBC
• The case
• Design of the database
• Investigation on flexible database design
• Dynamic Screens
• What we implemented

62
Dynamic Screens

• Other columns/company
• KBC Nr identification papers
• WARTA PEZEL nr
• Different profiles of employees
• Front office
• Back office
• Architecture
• Steering via MF
• Very flexible

63
Dynamic Screens

• Datamodel to control this

64
Dynamic Screens

• Problem
• This datamodel was more complex than most other
  datamodels we have.
• Accessing it every transaction to get the
  necessary information to build the screen was
  very expensive.
• Sometimes ½ trx-time

65
Dynamic Screens

• Solution
• Derived table with all information needed to
  build a screen
• So we have to access the table only once for each
  screen
• Table fed every release moment, not more, so very
  stable

66
Dynamic Screens

• Benefit
• In certain programs we called 3 public funtions
  (programs) - 1750 times, to collect the
  necessary data out of the classic datamodel
• Now we call 1 public function once
• - 5250 OPEN lots of FETCH -gt 1 OPEN 7 FETCH
• And this for each TRX!!!
• BUT create cost was high

67
Agenda

• KBC
• The case
• Design of the database
• Investigation on flexible database design
• Dynamic Screens
• What we implemented

68
What have we implemented
BufferWAS
WAS
WAS
MF
Max 160K
???
Load clusteredturned tables
Somezonetables
?
Pconsult
contract
MSGWAS
Stdtables
Productcreatedefinition
X 32K rowsderived table
Contract
Product definition
Derivedtables
69
(No Transcript)