what's new in db2 and db2 on cloud · pdf filewhat's new in db2 and db2 on cloud...
TRANSCRIPT
2
DB2 Aktuell 2017
Agenda
Overview
New in Db2
New in Db2 on Cloud (dashDB for Transactions)
New in Db2 Warehouse on Cloud (dashDB for Analytics)
Summary
3
DB2 Aktuell 2017
The Family
Old New
Database
IBM DB2 Linux, Unix and Windows (LUW)
IBM DB2 for i
IBM DB2 for z/OS
IBM DB2 on Cloud
IBM dashDB for Transactions
Data Warehouse
IBM dashDB Local
IBM dashDB for Analytics
IBM DB2 Analytics Accelerator (IDAA)
4
DB2 Aktuell 2017
Agenda
Overview
New in Db2
New in Db2 on Cloud (dashDB for Transactions)
New in Db2 Warehouse on Cloud (dashDB for Analytics)
Summary
5
DB2 Aktuell 2017
Online Crash Recovery (Async UNDO)
Prior to 11.1.2.2, during Crash Recovery (or HADR Takeover by
Force), connections are not allowed to the database until all
recovery is complete
– A REDO phase to redo all transaction activity to the end of log
– An UNDO phase to rollback transactions that had not committed
In Db2 11.1.2.2, we allow connections and activity into the database
after the REDO phase and while the UNDO phase is executing
– This was a technical preview in Db2 11.1.1.1
6
DB2 Aktuell 2017
REDO
10000
15000
20000
25000
30000
35000
40000
45000
Time
Tota
l T
ransa
ctio
ns
Per
Sec
ond
prior to
11.1.2.2DB down DB up
DB down DB upDB up (*)11.1.2.2
UNDO
5000
Online Crash Recovery (Async UNDO)
(*) Some data may be locked while UNDO proceeds concurrently
CurrentNew
pureScale
Connections allowed to DB after REDO,
just as asynchronous UNDO begins
7
DB2 Aktuell 2017
Multi-Core Parallelism – BLU INSERT INSERT INTO table2
SELECT * FROM table1
db2agents db2agent
table1 table2
db2agents db2agents
table1 table2
Db2 has exceptional multi-core
scalability for queries:
Combined MPP and SMP parallelism
Includes columnar and row-based tables
Applies to sub-select of INSERT
New Multi-Core Parallelism for INSERT
Includes a variety of data population
methods (i.e., INSERT, INGEST, etc.)
Extends to index maintenance
Also applicable to MPP deployments
Very significant reduction in time for:
ETL/ELT batch jobs
Data population/ingest jobs
Move/copy data from one table into another
8
DB2 Aktuell 2017
Multi-Core Parallelism – BLU INSERT
9
DB2 Aktuell 2017
Synopsis TableS_DATE QTY ...
2005-03-01 176 ...
2005-03-02 85 ...
2005-03-02 267
2005-03-04 231
...
2006-10-17
2006-10-25
User table: SALES_COL
SYN130330165216275152_SALES_COL
TSNMIN TSNMAX S_DATEMIN S_DATEMAX ...
0 1023 2005-03-01 2006-10-17 ...
1024 2047 2006-10-25 2007-09-15 ...
...
TSN = Tuple Sequence Number
0
1023
1024
2047
Meta-data that describes which ranges of
values exist in which parts of the user table
Transparently maintained by Db2
Size: ~0.2% of the user table
Enables DB2 to skip portions of a table
when scanning data to answer a query
Benefits from data clustering or loading pre-
sorted data
Updated on commit or every 1024 rows
0
1023
10
DB2 Aktuell 2017
Synopsis Table Improvements
Problem (before 11.1.2.2)
Every write transaction create a new synopsis record even if only
very few record are insert or updated
Worst case: single row inserts with commit after every row
Synopsis gets large and inefficient, not effective for data skipping
Solution in 11.1.2.2
Synopsis updates are collected in memory until 1024 rows have
accumulated
The transactions that adds the 1024th entry to the cache forces a
single new entry into the synopsis table
In case of crash recovery, lost synopsis entries are rebuilt by
scanning tail end of the table
11
DB2 Aktuell 2017
Other Enhancements
Federation support for PostgrSQL and MySQL, instead of generic
ODBC wrapper
Federation with SSL between Db2 family products
db2set DB2_AVOID_LOCK_ESCALATION=[on|off] SQL0912N
Improved rollback performance (5x to 6x faster in some cases)
CHANGE HISTORY event monitor extended record details of online
backup operations (parallelism, buffers, compresison, encryption, etc.)
New options for the STMT_CONC db cfg parameter (comments, etc.)
JSON SQL functions (json2bson, json_val, json_table, json_update, ...)
Db2 Developer Community Edition
Solaris support for Db2 11 in ~Q4 2017
12
DB2 Aktuell 2017
Technical Preview: Indexes on Column Organized Tables
Currently Db2 BLU has limited index support
– Unique indexes implicitly created for PRIMARY/UNIQUE KEY constraints
– A unique index can be used to access data if at most 1 row qualifies
• Every key in the index must have an equality predicate OR
• FETCH FIRST 1 ROW ONLY clause is specified
Indexes improve performance for a wide range of applications:
– Transactional data access: The “T” in HTAP
– Operational analytics: ETL/ELT with transactional characteristics
– Analytics: Some ‘heavy lifting’ queries with certain access patterns
Indexes on BLU when delivered (TBD) is initially targeted for
operational analytics
13
DB2 Aktuell 2017
Technical Preview: Indexes on Column Organized Tables
In Db2 11.1.2.2, Indexes are provided as a technical preview
– For now: non-production use only, i.e. no support
– Requires 2 registry variables to be set
1. DB2_BLU_OLTP=INDX:Y
• Allows CREATE and DROP INDEX
2. DB2_EXTENDED_OPTIMIZATION="COL_ISCAN IXONLY_UR“
• Enables use of indexes for UR isolation, index only access queries only
Limitations in technical preview:– CREATE INDEX and REORG INDEX .. REBUILD mode will allow concurrent readers but not concurrent writers.
– Jump scan
– Deferred fetch index plans (index ANDing, ORing and list prefetch)
– Star join and zigzag join
– Index extensions (most commonly used by spatial extenders)
– Expression-based indexes
– Scan sharing
– Intra-partition parallel scans
– RANDOM key order
– EXCLUDE NULL KEY option
– INCLUDE columns
– WITH SPECIFICATION ONLY clause (applies to nicknames)
– CLUSTER (incompatible with BLU implementation)
Smith
Jones
TaylorHewitt
Brown
jj
jk
jl
jm
jn
14
DB2 Aktuell 2017
Agenda
Overview
New in Db2
New in Db2 on Cloud (dashDB for Transactions)
New in Db2 Warehouse on Cloud (dashDB for Analytics)
Summary
15
DB2 Aktuell 2017
Db2 on Cloud (formerly known as dashDB for Transactions)Fully managed relational database service for transactional workloads.
"Precise Performance" Plans
Precise Performance
500*Precise Performance
1400
Precise Performance
10000
Server: Virtual Bare Metal Bare Metal
# Cores: 2 (virtual) 12 48
Memory: 8 GB 128 GB 1 TB
Storage: 500 GB HDD 1.4 TB SSD 11 TB SSD
Monthly Price
(USD)
Non-HA:
HA:
$250
$500
$4,000
$8,000
$18,000
$36,000
*free for the first 7 days Each plan available in a High Availability (HA) configuration
• Primary/standby pair with synchronous replication
• Single floating IP address for simplified access to cluster
• Automatic failover
16
DB2 Aktuell 2017
Db2 on Cloud - Consumption Details
All configurations available via pay-as-you-go (PAYGO)
– Digital purchasing directly through Bluemix using a credit card or subscription
First 7 days of small plan (Precise Performance 500) are free
– Billing starts on day 8. Requires credit card or Bluemix credits.
– If cancelled before 8th day then customer will not be charged
– Example: If customer buys for 20 days then they will only be charged for 13
days (20 days - 7 days)
Customers billed for the days that the service is active
– Virtual plan (2 cores): No minimums on number of days
– Bare metal plans (12 cores, 48 cores):
• 30 day minimum usage
• 14 days advance notice required to discontinue the service
17
DB2 Aktuell 2017
Db2 on Cloud – Flex Plan for Elastic Scalíng
Flex Plan
Elastic scaling of compute & storage
Server : Virtual
# Cores: 1 - 32
Memory: 4 - 128 GB
Storage: 2 GB - 4 TB
Monthly
Price (USD)
(Non-HA)
$189 for:
• 1 core
• 4 GB RAM
• 2 GB storage
• 2 million IOs
Scale beyond that for:
• $52 per core & 4 GB RAM
• $1 per GB storage
• $0.20 per 1 million IOs
HA: 2x non-HA price
(except IOs – IOs only charged on primary server)
Customer controlled
scaling of compute and
storage resources.
Storage and compute
layer scale
independently.
Fixed ratio of 4GB RAM
per core.
Near-instant change of
resources and cost.
No or minimal outage
(depending on selected
change).
Uses SoftLayer virtual machines (VM) to grow cores and memory.
Each VM is dedicated to a customer. Multiple VMs can share a single physical server but do
not share cores and memory. Cores, memory, and disks are dedicated to each VM.
18
DB2 Aktuell 2017
Flex Plan – Scaling Compute and Storage Resources
Compute
(Cores & RAM)Storage
Scale up Yes Yes
Scale down Yes No (for now)
Scaling levels 1 (4 GB RAM)
2 (8 GB RAM)
4 (16 GB RAM)
8 (32 GB RAM)
16 (64 GB RAM)
32 (128 GB RAM)
2GB
10GB
25GB
50GB
100GB
250GB
500GB
1TB
2TB
4TB
Outage Yes, <= 15 minutes No (in most cases)
HA Plan reduces
outage (rolling update)Yes Yes
19
DB2 Aktuell 2017
Use sliders in the Bluemix UI to select or change
desired storage and compute capacity.
Can scale storage and compute independently.
Flex Plan – Scaling Compute and Storage Resources
20
DB2 Aktuell 2017
High Availability Plans:
Non-High Availability Plans:
Db2 on Cloud - Service Level Agreement (SLA)
Availability during a contracted
month
Compensation (% of monthly fee)
towards future invoice
>= 99.95% 0%
>= 99.9% and < 99.95% 10%
< 99.9% 25%
Availability during a contracted
month
Compensation (% of monthly fee)
towards future invoice
>= 99.5% 0%
>= 99% and < 99.5% 10%
< 99% 25%
21
DB2 Aktuell 2017
Service Level Agreement (SLA)
SLA = agreement to compensate a customer if the service does not meet
the documented levels of uptime
– Credited against a future invoice for the service
Downtime
– Measured from the time the customer reports the event until the service is restored
– Downtime does not include time related to:
• A scheduled or announced maintenance outage (i.e. planned outages do not count)
• Problems with client or third party content, technology, designs or applications
• Client-caused security incident or client security testing
• Causes beyond IBM's control
How does a customer make an SLA claim?
– A severity 1 ticket must be opened within 24 hours of first becoming aware that there is
a critical business impact and the service is not available
– A claim must be made within 7 business days after the end of the month for which the
claim is being made
22
DB2 Aktuell 2017
Db2 on Cloud - Outlook
Customer-controlled backup & restore operations
Federation (Fluid Query)
HA option with an additional standby node in a different data center
Primary Server Standby ServerStandby Server
SYNCASYNC
e.g. Frankfurte.g. Amsterdam
23
DB2 Aktuell 2017
Benchmark: Db2 on Cloud vs. Amazon RDS Aurora
Db2 on Cloud Aurora 4XL Aurora 8XL
OfferingDb2 on Cloud
Precise perf 1400db.r3.4xlarge db.r3.8xlarge
CPU cores 12 cores @ 2.4GHz 16 vCPU @ 2.5GHz 32 vCPU @ 2.3GHz
RAM 128 GB 122 GB 244 GB
Storage 1.4 TB SSD 1.4 TB SSD 1.4 TB SSD
RDBMS Db2 11.1Aurora 5.6.10a
(MySQL 5.6 comp)
Aurora 5.6.10a
(MySQL 5.6 comp)
Monthly cost
incl. support$4000 $2022 + I/O $3890 + I/O
Workload
– TPC-E but with 60 instead of 300 days of historical data. Scaling: 300,000 customers.
– 75 concurrent customer emulators. Run time: 2 hrs with 20min ramp up.
24
DB2 Aktuell 2017
Benchmark: Db2 on Cloud vs. Amazon RDS Aurora
Workload
– TPC-E but with 60 instead of 300 days of historical data. Scaling: 300,000 customers.
– 75 concurrent customer emulators. Run time: 2 hrs with 20min ramp up.
Db2 on Cloud Aurora 4XL Aurora 8XL
OfferingDb2 on Cloud
Precise perf 1400db.r3.4xlarge db.r3.8xlarge
CPU cores 12 cores @ 2.4GHz 16 vCPU @ 2.5GHz 32 vCPU @ 2.3GHz
RAM 128 GB 122 GB 244 GB
Storage 1.4 TB SSD 1.4 TB SSD 1.4 TB SSD
RDBMS Db2 11.1Aurora 5.6.10a
(MySQL 5.6 comp)
Aurora 5.6.10a
(MySQL 5.6 comp)
Monthly cost
incl. support$4000 $2022 + $118 I/O $3890 + $543 I/O
Transaction
sper second1900 229 1048
$ per TPS $2.10 $9.34 $4.22
25
DB2 Aktuell 2017
Agenda
Overview
New in Db2
New in Db2 on Cloud (dashDB for Transactions)
New in Db2 Warehouse on Cloud (dashDB for Analytics)
Summary
26
DB2 Aktuell 2017
DB2 Warehouse on Cloud: Web Console 2.0
27
DB2 Aktuell 2017
Web Console 2.0: History and status of LOAD jobs
28
DB2 Aktuell 2017
Web Console 2.0: More detailed user management
29
DB2 Aktuell 2017
Web Console 2.0: Storage Usage Monitor
30
DB2 Aktuell 2017
For now, SLA applies if Db2 Warehouse on Cloud is purchased
through Bluemix (subscription or credit card)
– Based on Bluemix Service Description
Purchase through PPA or IBM Sales: SLO 99.5%
Db2 Warehouse on Cloud - Service Level Agreement (SLA)
Availability during a contracted monthCompensation (% of monthly fee)
towards future invoice
>= 99.5% 0%
>= 99.0% and < 99.5% 10%
< 99.0% 25%
31
DB2 Aktuell 2017
Db2 (Warehouse) on Cloud - REST API 2.0
REST API has been vastly extended in the following areas
– Settings (SSL enforcement)
–Monitoring of connections and storage
– User management
– SQL execution
– Database objects (list/create/drop tables or schemas)
– Load jobs
– File storage
32
DB2 Aktuell 2017
Db2 (Warehouse) on Cloud - REST API
33
DB2 Aktuell 2017
External Tables
An external table is a delimited text file on disk with an associated
schema definition in the database
– Table data can be unloaded into an external file
– Data from external table files can be inserted into the database
Use SQL to process file data as part of the load and unload process,
for example:
– Functions and Operators (substring, concatenation, etc)
– JOINs with database tables for key values
– Load a subset of a file based on a value (WHERE province=‘ON’)
Efficient way to import, export and transfer data!
https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r_create_ext_table.html
34
DB2 Aktuell 2017
External Tables: Sample Usage
Create an external table ET_CUSTOMER with the column definitions of the
existing CUSTOMER table using an external data file “/tmp/customer.del”
CREATE EXTERNAL TABLE et_customer
SAMEAS customer
USING (DATAOBJECT ('/tmp/customer.del');
INSERT INTO customer
SELECT * FROM et_customer WHERE C_NATION = 'Canada';
INSERT INTO et_customer SELECT * FROM customer;
Unload a table into an external table:
Insert data into a table from an external table:
In this example a WHERE condition is used to only load a subset of the data
35
DB2 Aktuell 2017
Customer chooses a small, medium, or large range
– Customer does not choose number of nodes – determined automatically
Can scale dynamically within the chosen range:
– Small: Storage in 640GB chunks, Compute in multiple of 8 cores
– Medium: Storage in 2.4TB chunks, Compute in multiple of 24 cores
Storage scaling: expand only, no downtime, no shrink option (for now)
Compute scaling: expand or shrink, short downtime (restart)
Uses an MPP architecture built on bare metal servers (using container technology)
Billing by the hour
Range Storage Compute
Small 640GB – 6.4 TB 16 – 192 cores
Medium 2.4 TB – 48 TB 72 – 576 cores
Large Coming later (TBD) Coming later (TBD)
Strategic direction: Db2 Warehouse on Cloud with
Elastic Storage and Compute Layers
36
DB2 Aktuell 2017
In Bluemix: slider bars to customize the amount of storage and computeChoosing
resources for
the small
range
Strategic direction: Db2 Warehouse on Cloud with
Elastic Storage and Compute Layers
37
DB2 Aktuell 2017
Agenda
Overview
New in Db2
New in Db2 on Cloud (dashDB for Transactions)
New in Db2 Warehouse on Cloud (dashDB for Analytics)
Summary
