Mimer SQL on Open VMS Stefan Eck Product

Mimer SQL on Open. VMS Stefan Eck Product Marketing Director Mimer Information Technology AB

Agenda § Background § Mimer customers on Open. VMS § Platforms § Technical features § Mimer SQL on Open. VMS § Q&A

Mimer Information Technology AB § HQ in Uppsala, Sweden – Mimer Taiwan (subsidiary) – Partners in China, Japan, Korea and USA § World class experts in relational database technology § Developer of the Mimer SQL product family – Enterprise Solutions – Industrial/Embedded Solutions – Mobile Solutions § Mimer SQL used in mission critical systems world wide since the 1970 s

Mimer SQL highlights § Relational database management system § Full ISO SQL § ISO PSM stored procedures § Tight integration with Open. VMS

Mimer SQL background § Roots at the Uppsala University, Sweden § 30+ years on VMS! First port 1980 (VAX 780) § Open. VMS main development platform since 1982 (VAX Alpha Integrity) § Close co-operation with Open. VMS Engineering

Agenda § Background § Mimer customers on Open. VMS § Platforms § Technical features § Mimer SQL on Open. VMS § Q&A

Some Mimer SQL Customers NHS Blood & Transplant DJ Galvanizing Swedish Telecom Volt Delta A top-5 hand-set manufacturer Volvo Cars Chinese smart-phone manufacturer Alexander Forbes Blue colour = Open. VMS!

Mimer SQL in Life-Critical System § World’s largest centralized blood supply management system § NHS Blood and Transplant service, England & Wales – – Responsible for all donated blood and tissues System controls 2. 5 million donations annually 3, 500+ active users Database of 1 billion records +8 million / week – “Our experience is that Mimer SQL rarely gives us a problem and has a very low maintenance overhead, so there’s no need for any expensive database administrators to run the system. ” Ian Henderson, MD at Savant Enterprises, the developer of PULSE Stock Level on 08 Jul 2011

NHS Blood and Transplant An Integrated System § DONOR SYSTEM § LAB SYSTEM § STOCK SYSTEM “NHS Blood & Transplant provides a life-saving service. It cannot fail, especially in the face of major incidents. NHSBT has achieved an extremely high level of disaster tolerance through the use of dual data centers and split-site Open. VMS clusters. … In summary, this system demonstrates considerable in-depth strength to deliver extremely high availability blood-product services to NHSBT by using the PULSE software, the Mimer SQL database, and the Open. VMS clusters running on Integrity Server systems and EVA storage subsystems. ” Availability Digest, Oct 2008

Mimer SQL at NHS BT Structure § Database Server – – HP Integrity servers at 2 locations Open. VMS Mimer SQL database Serving data through ODBC § COM + Windows server – Providing “middleware” data services to clients § Clients – Windows PC (thin client through Citrix) – HTML web pages – Web services to third party clients Savant

Savant

Mimer SQL at NHS BT Vital Statistics (July 21 2011) § Live System – 1, 251, 033, 111 records in 715 tables – Largest table (donation audit) is 130+ million records § Archive System – For older, time expired records – 2, 065, 863, 274 records § More information – Availability Digest http: //www. availabilitydigest. com/public_articles/0310/uknbs. pdf – HP customer case study http: //www. xdelta. co. uk/news#nhsbtcasestudy Savant

• • Maintained 99. 9% uptime Reduced data centre footprint Maintained exceptional reliability Built disaster-tolerant solution

Mimer SQL in Production § Controls flow of parts to the assembly lines at the Volvo car plant in Gothenburg § Mission critical (unplanned database downtime very expensive) § In production since 25+ years

Mimer SQL in Omron sensor network Remote Comprehensive Surveillance monitor Access Point In-house LAN MIMER SQL Demonstration area in a clean room 2. 4 GHz Wireless Zigbee MIMER SQL Environmental sensing by multiple sensors OMRON Conclusion: § Predictable real-time guarantees needed § Standard SQL application programming lowers cost and time to market § High performance and scalability important § Wise data-management significantly reduced power consumption MIMER SQL provides this.

Agenda § Background § Mimer customers on Open. VMS § Platforms § Technical features § Mimer SQL on Open. VMS § Q&A

Mimer SQL products Mimer SQL Enterprise Mimer SQL Mobile Mimer SQL Micro Mimer SQL Nano Mimer SQL Real-Time Mimer SQL Embedded Resource constrained Systems Large Systems

Mimer SQL platforms Open. VMS Linux Solaris BREW Vx. Works Enea OSE Android HP-UX AIX Windows Mobile Windows CE Symbian OS Embedded Linux Resource constrained Systems Blue colour = HP platform! Large Systems

Mimer SQL platforms § Same database kernel on all platforms § Enterprise server on a mobile phone § Small footprint and zero maintenance enterprise server Resource constrained Systems Large Systems

Mimer SQL Mobile is included in millions of mobile phones Some recent devices with Mimer Inside

Original database architecture on Android Application 1 Application 2 Application 3 Content Provider SQLite Private database 1 Private database 2

Mimer SQL Mobile on Android § One single shared database server handles – Concurrency – Data integrity rules – Access control – Upgrade – Threading – Multi lingual support App 1 App 2 JD BC App 3 Content Provider App 4 Content Provider ba A se Mimer SQL Mobile Shared database L SQ ite ta Da PI

Customer benchmarking on Android: Contacts Example 12000 25000 30000 15000 20000 Mimer SQLite 10000 Mimer SQL 15000 SQLite 5000 2000 0 0 Join 45000 10000 Mimer SQLite Time in ms 15000 Update 25000 40000 25000 20000 35000 30000 25000 20000 15000 10000 5000 0 0 Mimer SQLite Time in ms 30000 50000 SQLite 4000 5000 35000 Mimer SQL 6000 10000 0 Select 8000 Time in ms 20000 Batch insert (1000 25000 rows) Time in ms Insert (1000 rows) Delete 20000 15000 10000 5000 0 Mimer SQLite

Customer benchmarking on Android: Multimedia Example Conclusion § Insert Interface test (16 fields). Results in ms. – Insert: SQLite is 2. 69 times slower than Mimer SQL with Provider. ms – Insert: SQLite is 4. 05 times slower than Mimer SQL without Provider. No of photos

Agenda § Background § Mimer customers on Open. VMS § Platforms § Technical features § Mimer SQL on Open. VMS § Q&A

SQL § Standardized by international standard bodies such as ISO and ANSI § Supported by Oracle, DB 2, SQL Server, …… § Use standard SQL to get application portability between different database systems § Mimer SQL conforms to the SQL standard § Mimer is an active member of the ISO standardization body for SQL: ISO/IEC JTC 1/SC 32, Work. Group 3, Database Languages

Mimer SQL standard compliance § X/Open SQL § SQL 99/Core 2003, including: – Persistent Stored Modules (PSM) – Triggers § Unicode § ODBC § JDBC, J 2 ME (CDC, CLDC)

Mimer SQL: Zero maintenance § Non-locking concurrency control – Deadlock free – No locking overhead – Add more workload or new applications without risk for performance deterioration due to database locks § Automatic database reorganziation – Database files always structured for optimal performance – No need for index rebuilds § Very few tuning parameters – Cache size – Number of database request threads

Mimer SQL: Heterogeneous Client/Server § No additional processes on Open. VMS server § Multiple server connections (local and/or remote) possible § Generic communication protocol for all platforms – ODBC, Java JDBC/J 2 ME, . NET, Embedded SQL – Common data format No conversion needed – You can use your mobile phone as a client to your Open. VMS server (or vice versa!) Database Client Database Server (and Client) Database Client

Mimer SQL Standard APIs § Open architecture and APIs . NET ADO. NET, Mono, ADO. NET CF Perl, PHP, Ruby on Rails C/C++ ODBC, Micro API, Embedded SQL Mimer SQL Database Server Database Java JDBC, J 2 ME

Mimer SQL server architecture Clients Access engine • Multi threaded • Scalable • Deadlock free • ACID transactions ISO SQL: 1999 • Procedures&triggers • Access control • Referential integrity • RAM based • Dynamic • Configurable Kernel Cache Database •

Linguistic Sorting § Built-in and user defined collations – 140+ different built-in languages § Text search and sorting: – Independent of case – With national characters in correct order – With or without regard for accents – … § Output from the same column can be sorted differently – Not built into column type – Secondary indexes with different sort orders

Some built-in collations § § § § § § AFRIKAANS ALBANIAN ARABIC ARUMANIAN ASTURIAN BASQUE BELARUSIAN BOSNIAN BRETON BULGARIAN CATALAN AZERBAIJANI CORSICAN CROATIAN CZECH DANISH DUTCH ENGLISH EOR ESPERANTO ESTONIAN FAROESE FILLIPINO FINNISH • • • • • • FRENCH FRISIAN FRIULIAN GALICIAN GERMAN GREEK GREENLANDIC HEBREW HUNGARIAN ICELANDIC IRISH_GAELIC ITALIAN KAZAKH KIRGHIZ KURDISH LATIN LATVIAN LITHUANIAN LUXEMBOURGISH MACEDONIAN MALTESE MOLDAVIAN NORWEGIAN OCCITAN • • • • • • POLISH PORTUGUESE ROMANIAN ROMANSCH RUSSIAN SAMI SCOTS SCOTTISH_GAELIC SERBIAN SLOVAK SLOVENIAN SORBIAN SPANISH SWEDISH TATAR THAI TURKISH TURKMEN UKRAINIAN UNICODE UZBEK VIETNAMESE WELSH

Some additional collations § Indian languages – – – – Assamese Bengali Gujarati Hindi Kannada Konkani Malayalam Manipuri Marathi Nepali Oriya Punjabi Sanskrit Sinhala Tamil Telugu § African Languages – Hausa – Igbo – Yoruba § Middle East languages – Dari – Pashto – Persian § Chinese – – § Kang. Xi (康熙) Pinyin (拼音) Zhu. Yin (注音) Wu. Bi. Hua (五笔画) Korean – Hangul and Chinese together § Japanese

Multilingual Support SQL>select word from t order by word collate japanese_3; WORD ============== SQL>select * from t where name ていねい- teinei – polite like 'AA%' collate danish_1; テープ - teepu – tape でぐち - deguchi – exit NAME テスト - tesuto – test ==== では - dewa - well, then Aalborg デパート- depaato – dep. store Århus

Advanced searching using collations § T 9 sorting for numeric keypads – For search among words matching a sequence of letters (e. g. Numeric keypads) select * from state where name like ’ 83%' collate t 9 NAME ===== Texas Vermont 2 rows found 1 4 GHI 2 3 ABC DEF 5 JKL 6 MNO 8 7 9 PQRS TUV WXYZ 0 +

Mimer SQL: Immediate restart § Immediate access to data at restart after failure § Database consistency guaranteed § Option on Open. VMS

Mimer SQL Real-Time A Hard Real-Time Database Server that: § Allows you to model all system data in one data storage § Provides safe sharing of real-time data between – Guaranteed hard real-time transactions – Flexible database transactions with full SQL support

Mimer SQL Real-Time - Features § Hard Real-Time Support with guaranteed response-times – No unpredictable blocking or locking – Full control of when to write to disk/flash § Unrivaled performance for real-time data access – ”Near shared-variable performance” § Specialized logging functionality – Safe and efficient logging of system events/control data § Full client/server functionality for SQL transactions – “Your device is your server” § Scalable cross-platform database solution

Mimer SQL Real-Time – Performance Near shared variable performance Maintained predictability Mimer SQL real-time introduced as little as 3% overhead compared to using shared variables!

Agenda § Background § Mimer customers on Open. VMS § Platforms § Technical features § Mimer SQL on Open. VMS § Q&A

Mimer SQL on Open. VMS § Developed on Open. VMS for Open. VMS § Not a Unix or Windows product ported to Open. VMS! § Use the most efficient primitives on Open. VMS to get best possible performance and scalability § Full function Mimer SQL 10. 0 evaluation version available for free download at: http: //developer. mimer. com/

Mimer SQL: Multi threaded server § One single multi threaded server Local User Application Embedded SQL interface JDBC Remote User Application ADO. NET ODBC Communication Thread Request Threads Background Threads Database Cache Mimer Database Server

Advantages with a multi threaded server § Less synchronization overhead § All threads share memory, disk channels, etc. § Efficient use of multi-core servers Local User Application Embedded SQL interface JDBC Remote User Application ADO. NET ODBC Communication Thread Request Threads Background Threads Database Cache Mimer Database Server

Mimer SQL: Database cache § Stores large parts of database in main memory § Possible to use Reserved Memory Registry in Open. VMS – No paging – No working set quota taxation Local User Application JDBC Local User Application Embedded SQL interface Remote User Application ADO. NET Remote User Application ODBC – Reserved at boot time Database Cache Mimer Database Server

Mimer SQL: Supported APIs on Open. VMS § ODBC driver (native Mimer SQL) § JDBC Type 4 driver (all Java) § Embedded SQL – Cobol – Fortran § ACU Cobol § X/Open XA (Distributed transactions)

Mimer SQL: Local Client/server Local clients – Shared memory based Local User Application JDBC Local User Application Embedded SQL interface Remote User Application ADO. NET ODBC – Avoids unnecessary copying of data – Executive mode – $HIBER/$WAKE synchronization Database Cache – User-Written System Service (UWSS) Mimer Database Server

Mimer SQL: Remote Client/server § Remote clients – TCP/IP and DECNET – Single asynchronous $QIO Local User Application JDBC Local User Application Embedded SQL interface Remote User Application ADO. NET ODBC – AST triggered communication – Scalability – No communication thread Database Cache Mimer Database Server

Mimer SQL: Disk I/O § Performed by both Request and Background threads § Asynchronous $QIO § Dynamic prefetch of data Local User Application JDBC Local User Application Embedded SQL interface Remote User Application ADO. NET ODBC Database Cache Mimer Database Server

Mimer SQL and HPVM On-Line Guest Migration § Successful tests using – BL 860 c i 2 with 32 GB and two Itanium 9350 – BL 870 c i 2 with 32 GB and four Itanium 9350 – C 7000 with network and SAN VC – P 2000 G 3 FC – HP-UX 11 iv 3 1009 with HPVM 4. 3 – Open. VMS 8. 4 with update 500 – Mimer SQL Enterprise 10. 0. 5 § Thanks to Lars “Zinken” Sundqvist, HP

Mimer SQL and HPVM On-Line Guest Migration § On-Line migration – Nine Mimer SQL database applications § ”Nasty”: Two small tables updated intensely § Database server busy all the time – Open. VMS guest using 4 v. CPU, 8 GB memory – 3500 on-line migrations during several days – No reboot needed § Performance – Complete on-line migration in 30 -35 seconds – The freeze 1 second

Agenda § Background § Mimer customers on Open. VMS § Platforms § Technical features § Mimer SQL on Open. VMS § Q&A – Migration?

Mimer SQL deployment on Open. VMS § New projects – Use standard SQL & open APIs and tools § . NET, ODBC, Java § C, Cobol, Fortran § Migration of existing applications and databases – RMS – Oracle – Rdb

Migration to Mimer SQL from RMS § Step 1 – Map existing RMS file structure to database tables – Change $PUT, $GET, … to SQL Insert, Select, … § Step 2 – Normalize your database – Utilize rich set of datatypes (large objects, Unicode, …) – Use more advanced features (views, procedures, triggers, …)

Migration to Mimer SQL from Oracle/Rdb § Standard SQL? Use the Mimer SQL Validator (http: //developer. mimer. com/validator/index. htm): select col 1, col 2 from tab 1 Result: Conforms to Core SQL-2003 § Stored procedures? – PL/SQL not conforming to the ISO PSM standard used by e. g. Mimer SQL, DB 2 and My. SQL § Development tools – Open APIs and tools or – SQL*Plus, RDO and other Oracle tools

See you at the Open. VMS Boot Camp! 1. Introduction to Mimer SQL on Open. VMS – Bengt Gunne, VP Product Development, Mimer § Monday Sep 19, 11 am (prel) § Tuesday Sep 20, 1 pm (prel) 2. Technical session Mimer SQL on Open. VMS – Bengt Gunne, VP Product Development, Mimer – Lars “Zinken” Sundqvist, HP (HPVM on-line migration) § Monday Sep 19, 3 pm (prel) § Tuesday Sep 20, 3 pm (prel)

E-mail questions to stefan. eck@mimer. com Visit www. mimer. com and developer. mimer. com Thank you!