1 / 69

Building Flexible Database Systems

Building Flexible Database Systems. GSE 02/04/2009 Dirk Beauson KBC Global Services NV. Agenda. KBC The case Design of the database Investigation on flexible database design Dynamic Screens What we implemented. The KBC Group in Central and Eastern Europe.

aurelia-fletcher
Download Presentation

Building Flexible Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 • Don’ts • Time to Market Flexibility needed • Restrictions • …

  17. Design of the database • At a certain moment we, the applicative DBA’s, came in to : • Do some talking about flexible • Database possibilities • Designs • DB2-stuff • … • But they didn’t 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 let’s 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 doesn’t 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 doesn’t 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

More Related