Donnerstag, 17 of April of 2014

DB2 versions

List of most important changes every DB2 for z/OS, DB2 for OS/390 or DB2 for MVS version brought with.
(Partially in german language only.)


Important links:


DB2 11 for z/OS:

GA – general availability for ESP partners: March 8, 2013, announcement - 5615-DB2           

  • CPU reductions, performance improvements and system efficiency
    • Compared to use of DB2 10, you may achieve up to 5% out-of-the-box CPU savings for many traditional OLTP, heavy INSERT, and SAP workloads.
    • Compared to use of DB2 10, you may also achieve up to 20 to 30% out-of-the-box CPU savings for select query workloads. These performance improvements along with use of IBM DB2 Analytics Accelerator for z/OS , can further enable significant query acceleration and potential cost savings.
    • New SQL PL ARRAY support may bring up to an additional 10% CPU savings for OLTP workloads with higher read-to-write ratios
    • You may see additional performance and CPU savings by taking advantage of additional enhancements, including DPSI performance improvements and suppress null indexes.
    • Additional optimizer enhancements for distinct, group by, and sparse index support bring performance improvements across a wide variety of queries.
    • Cost effective archiving of warm and cold data with easy access to both within a single query
    • Using pageable large (1 MB) pages and Flash Express (with IBM zEnterprise EC12, zEC12)
    • Enabling support of 2 GB pages (with IBM zEnterprise EC12, zEC12)
  • Improved-data sharing performance
    • Data sharing clients may see performance improvements from a 2x to 5x more efficient index structure modification and from full LRSN spin avoidance.
  • Improved utility performance and additional zIIP eligible workload
    • Inline statistics gathering enhancements greatly reduces the need for RUNSTATS which can reduce resource consumption.
    • DB2 Utilities now offer more zIIP eligible workload for RUNSTATS and LOAD and faster LOAD processing for improved performance and CPU savings. Additional REORG enhancements minimize DBA workload with automated mapping tables and improved PBG handling.
    • Improve productivity and performance through REORG avoidance and auto index pseudo delete cleanup.
  • Availability and reliability
    • SWITCH phase reduction and improved drain processing in online REORG deliver additional availability improvements.
    • The need for planned outages is significantly reduced with online data repartitioning capabilities, including support for REORG, REBALANCE, SHRLEVEL, CHANGE, and online ALTER LIMITKEY.
    • With extended relative byte address (RBA) and log record sequence number (LRSN) addressing support you can avoid many manual recovery actions that can potentially cause extended outages.
    • Improved backup manageability enables even faster recovery.
    • Improved cloning capabilities support automatic VCAT name translation for system-level backup.
  • Other enhancements
    • Deploy real-time scoring directly within the transaction when used in conjunction with IBM SPSS offering.
    • Together with IBM DB2 Analytics Accelerator for z/OS and System z , user can efficiently colocate OLTP and data warehouse as one integrated workload in real time.
    • DB2 QMF , a separately priced feature of DB2 for z/OS , has also been enhanced to include improved analysis, forecasting, reporting, and presentation capabilities, as well as better storage management, supporting greater analytics on business data.
    • Improvements in XML, temporal support, and SQL PL deliver even more capabilities for application developers.
    • Various SQL enhancements including support for array data types and global variables, make it easier for application developers
  • Migration:
    • With the new application compatibility feature, migration plans no longer have to wait for applications to make SQL or XML changes before leveraging new DB2 features.
    • Simpler catalog migration helps your business get the value of DB2 11 faster.
    • DB2 11 for z/OS is upwardly compatible with earlier releases of DB2 for z/OS . Migration with full fallback protection is available for customers running on DB2 10. Existing customers should ensure they are successfully running on DB2 10 for z/OS (NFM) before migrating to DB2 11.

DB2 10 for z/OS:

GA – general availability: 22. Oct. 2010, Announcement.

  • Reduced CPU usage:
    • DB2 CPU savings of up to 5% – 10% for traditional workloads and up to 20% for specific workloads, when compared to running the same workloads on DB2 9. Customers moving from DB2 9 should expect the same CPU times for utilities, while customers moving from DB2 V8 will see CPU reductions up to 20%.
    • DB2 reduces its CPU usage by optimizing processor times and memory access, leveraging the latest processor improvements, larger amounts of memory, and z/OS enhancements.
    • Improved scalability and virtual storage constraint relief can add to the savings.
  • Productivity improvements:
    • New SQL and XML capabilities
    • Automating, reducing, or eliminating tasks
    • Resiliency improvements for virtual storage and availability
    • Installation, migration, and service processes faster and more reliable
  • Flexibility in migration paths:
    • Migration from DB2 Version 8 subsystem in new-function mode directly to Version 10.
  • Greater reliability and availability (‘resiliency’):
    • Scalability improvements
    • Virtual storage enhancements
    • 5 to 10 times more concurrent active users
    • up to 20,000 concurrent active threads
    • Schema evolution, data definition on demand
    • Manageability enhancements for query performance
    • Security improvements
    • Continuous availability enhancements:
    • Online schema enhancements (more ALTER functions on indexes and table spaces)
    • Imporved performance of online reorganization
    • Reorganization of disjoint partition ranges of a partitioned table space
    • Improves SWITCH phase performance and diagnostics
    • Removed restrictions related to the online reorganization of base table spaces that use LOB (large object) columns.
    • Reduced catalog contention:
      • links in the catalog and directory removed
      • new functionality of the lock avoidance techniques
      • improved concurrency by holding acquired locks for less time and preventing writers from blocking the readers of data
      • access to currently committed data to minimize transaction suspension (only in NFM, a read transaction can access the currently committed and consistent image of rows that are incompatibly locked by write transactions without being blocked).
    • Virtual storage relief:
    • increased capacity by moving most memory to 64-bit, which provides virtual storage relief and can improve the vertical scalability
    • 64-bit ODBC driver
    • extended support for LOB and XML streaming
    • improvements to LOB and XML processing
    • enhancements to the FETCH statement for LOB and XML data
    • Security enhancements
    • enhancements to security and auditing
    • granularity for DB2 administrative authority
    • new data security solution that enables to manage access to a table at the level of a row, a column, or both.
    • define and create different audit policies
  • Rapid application and warehouse deployment for business growth
    • SQL improvements
    • enhancements for SQL scalar functions and SQL table functions
    • native SQL procedure language (SQL PL) enhancements
    • implicit casting
    • number of digits for fractions of seconds and allowing timestamps with time zones
    • moved sums and moved averages
    • Temporal tables and versioning
    • two news types of periods, which are the system time (SYSTEM_TIME) period and the business time (BUSINESS_TIME) period.
    • versioning, keeping historical versions of rows for a temporal table that is defined with a SYSTEM_TIME period, or both time periods
    • pureXML improvements
    • performance improvements
    • binary XML format
    • XML schema validation as a built-in function
    • XML date and time data types and functions
    • XML parameters in routines

DB2 UDB Version 9.1 for z/OS

GA 16.03.2007, announcement: http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&appname=iSource&supplier=897&letternum=ENUS207-041

  • Rich hybrid data server support for both relational and pureXML storage, with the necessary services to support both data structures.
  • New data types (DECFLOAT, BIGINT, and VARBINARY)
  • Native SQL procedural language.
  • Improved security with roles, trusted context, and new encryption functions
  • Extensions of DB2 for z/OS V8 capabilities to make changes to the data.
  • Enhancements to large object support and performance.
  • Volume-based copy and recover
  • Refinements to the DB2 industry-leading optimization
  • QMF interface design changes that provide on demand access to data, reports, and interactive visual solutions with optional Web browser
  • Enablement for IBM System z Integrated Information Processors (zIIP)
  • Index page sizes 8K, 16K, 32K (due to larger index page sizes up to 8 times less index split)
  • Index compression
  • Asymmetric index page split instead of 50-50 (100-0) split
  • Index various sort improvement enhancements
  • Varchar performance improvement (RRF – reordered row format)
  • Some selected Details:
    • SHRLEVEL(REFERENCE) for REORG of LOB table spaces
    • Online RENAME COLUMN
    • Online RENAME INDEX
    • Online CHECK DATA and CHECK LOB
    • Faster REORG by intra-REORG parallelism
    • More online REORG by eliminating BUILD2 phase
    • LOB Lock reduction
    • Skipping locked rows option
    • Online REBUILD INDEX
    • Change SCHEMA & VCAT
    • Tape support for BACKUP and RESTORE SYSTEM utilities
    • Recovery of individual tablespaces and indexes from volume-level backups
    • Enhanced STOGROUP definition
    • Utility TEMPLATE switching
    • Conditional restart: automatic search for appropriate checkpoint
    • CLONE Table: fast replacement of one table with another
    • Buffer management by WLM
    • Global query optimization
    • Generalizing sparse index and in-memory data caching method
    • Optimization Service Center
    • Autonomic reoptimization
    • Logging enhancements
    • LOBs Network Flow Optimization
    • Faster operations for variablelength rows
    • NOT LOGGED table spaces
    • Index on expressions
    • Universal Table spaces
    • Partition-by-growth table spaces
    • APPEND option at insert
    • Autonomic index page split
    • Index page sizes 8K, 16K, 32K
    • Support for optimistic locking
    • Faster and more automatic DB2 restart
    • MODIFY RECOVERY enhancements
    • RLF improvements for remote application servers such as SAP
    • Preserving consistency when recovering individual objects to a prior point in time
    • DECIMAL FLOAT, BIGINT
    • VARBINARY, BINARY
    • TRUNCATE TABLE statement
    • MERGE statement
    • FETCH CONTINUE
    • ORDER BY and FETCH FIRST n ROWS in sub-select and full-select
    • ORDER OF extension to ORDER BY
    • Various scalar functions
    • XML support in DB2 engine
    • Native SQL Stored Procedures, able to use zIIP
    • SELECT FROM UPDATE/DELETE/MERGE
    • Enhanced CURRENT SCHEMA
    • IPv6 support
    • Unified Debugger
    • Network Trusted Context
    • Database ROLEs
    • Automatic creation of database objects
    • Modify early code without requiring an IPL
    • Utilities CPU reduction
    • Temporary space consolidation
    • and more …

DB2 UDB Version 8 for z/OS

GA in 03/2004

  • SQL Erweiterungen:
    • SELECT FROM INSERT statement
    • Generation of unique sequential numbers for applications
    • Ability to alter identity column attributes
    • Dynamic scrollable cursors
    • Scalar fullselects in SQL statements
    • Integrated XML publishing functions in DB2
    • Common table expressions in SQL statements
    • Recursive SQL
    • CURRENT PACKAGE PATH special register
    • GET DIAGNOSTICS statement
    • More than one DISTINCT keyword allowed in a single query
  • Größere Sicherheit:
    • New built-in functions for data encryption and decryption
    • New data encryption tool
    • Multilevel security with row-level granularity
    • Easier identification of system users
    • Session variables
    • Improved encrypted security in distributed computing environments
  • Größer Kompatibilität zwischen den Mitgliedern der DB2 Family:
    • Extended limits for names and SQL statements
    • Significant support for Unicode and long names in the DB2 catalog
    • Network computing enhancements
    • IBM z/OS Application Connectivity to DB2 for z/OS feature
    • ODBC expands support for encoding schemes
  • Unicode, längere Objektnamen, Änderungen im DB2 Catalog:
    • Unicode
    • Long names
    • Unicode, EBCDIC, and ASCII columns in the same SQL statement
  • Skalierbarkeit und Performance:
    • 64-bit virtual storage
    • Materialized query tables
    • Ability to use an index in more situations
    • Additional distribution statistics for improved optimization
    • Improved optimization for dynamic SQL
    • Improved trigger performance
    • More parallelism for sort operations
    • Performance enhancements for star join qualified queries
    • Multiple fetches and inserts allowed within a single SQL statement
    • 4096 partitions in a partitioned table space
    • Greater resource control with stored procedures and user-defined functions
    • Reduced overhead costs for data sharing workloads
    • Reduced lock propagation in the coupling facility
    • Improved control for accounting aggregation
    • Improved package-level accounting
  • Zuverlässigkeit, Verfügbarkeit, Wartbarkeit:
    • Greater availability and flexibility with online schema evolution
    • Greater data availability with data-partitioned secondary indexes
    • More flexibility and faster recovery with system-level point-in-time recovery
    • Improved utility functions
    • Enhanced LPL recovery processing
    • Change more parameters online without recycling DB2
    • Increased maximum number of active and archive log data sets
    • Autonomic space allocation

DB2 UDB Version 7 for OS/390

FCS Early Support Program June 2000, GA later

  • improved performance, availability, scalability for e-business and data warehouse applications
  • Scrollable Cursors
  • UNION und UNION ALL in Views
  • Veränderungen der ‘ZPARMS’, ohne DB2 stoppen zu müssen
  • UNLOAD Utility
  • Mehr als 15 Tables in einem Join mit Star-Join-Technique
  • Schnellerer REORG ONLINE
  • RESTART light für schnelleres Data Sharing Recovery
  • Neue IBM-Upgrade-Strategie: Upgrade von Version 5 und von Version 6 möglich!
  • UNICODE Support
  • Zusatz-Feature
    • DB2 XML Extenders for OS/390 (kostenfrei)
    • DB2 Warehouse Manager (Restricted-Use Lizenz von DB2 UDB 71. EE, OLAP Starter Kit, DW Center, Information Catalog, OS/390 Agents and Transformers), QMF for OS/390, QMF HPO und QMF for Windows)
    • DB2 Tools Package (Try & Buy, dann kostenpflichtig):
      • DB2 Administration Tool
      • DB2 Bind Manager
      • DBRM Checker
      • DB2 Buffer Pool Tool (siehe auch Tool auf dieser Site)
      • DB2 Data Propagator for OS/390 (siehe auch Tool auf dieser Site)
      • DB2 Forms
      • DB2 Performance Monitor
      • SQL Performance Analyzer
      • DB2 Recovery Manager
    • DB2 Management Tools Package (kostenfrei):
      • DB2 Control Center
      • DB2 Stored Procedures Builder
      • DB2 Installer
      • DB2 Visual Explain
      • DB2 Estimator
    • Net.Data (kostenfrei)
    • REXX Language Support (kostenfrei, seit V5.2 Service Pack)

DB2 UDB Version 6 für OS/390

approx  june 1999

Universally extensible:

  • Very large objects
  • User-defined functions
  • User-defined distinct data types
  • Triggers for active data
  • More Built-In Functions

Universally accessible through network computing:

  • SQLJ (Java embedded SQL)
  • DDF connection pooling (Type 2 Inactive Thread)
  • DRDA three-part names
  • Stored procedure enhancements
  • Net.Data Version 2 with enhanced performance
  • QMF, QMF HPO, and QMF for Windows (opt.)
  • DB2 DataPropagator Version 6 (opt.)

Universally scalable and reliable:

  • Support for 16 TB tables (single tablespace)
  • Faster, more usable, more parallel utilities (COPY, LOAD, REORG, REBUILD INDEX)
  • Data sharing enhancements
  • Easier to alter partitions
  • Virtual storage constraint relief
  • Faster restart and recovery
  • Query enhancements, prediction/governing, Query performance enhancements
  • 8 and 16 KB page sizes

Universally manageable:

  • Workstation tools for Windows NT and OS/2:
    • DB2 Installer
    • Visual Explain
    • Tuning and Analysis
    • DB2 Estimator
  • DB2 PM V6 (opt.)
  • DB2 Admin Tool (opt.)
  • DB2 Buffer Pool Tool (opt.)

DB2/MVS Version 5

in 1997

Client/Server und Open Systems Erweiterungen:

  • Native TCP/IP Support
  • ATM Support (SNA oder TCP/IP)
  • Verbesserung bei Stored Procedures durch temporäre Ergebnistabellen
  • Verbesserung der Umgebung von Stored Procedures
  • Bessere Performance für DRDA Anwendungen: Besserer Block Fetch, SELECT mit OPTIMIZE FOR n ROWS, ASCII Tables, schnelleres Dynamic SQL, effizientere VTAM SEND/RECEIVE Operationen
  • Verbesserte Security: DCE Security Tokens, RACF Passticket, DRDA User Password Change Prompt
  • Call Level Interface

Performance, Kapazität und Verfügbarkeit:

  • Query Performance: Sysplex Query Parallelism
  • Data Sharing Erweiterungen
  • Cache für Dynamic SQL
  • Reoptimierung des Zugriffspfads zur Laufzeit
  • SQL Sprachumfang: CASE, Index-Use trotz komplexer Ausdrücke
  • Geschwindigkeitszuwachs Batch und Online: Authorization Caching, Pieces´, User-defined Rows per Page´, Log Preformatting
  • Large Partitioned Tablespaces: Grenze 1 TByte statt bisher 64 GBytes
  • Online Reorg Utility
  • Diverse Utility Erweiterungen:
    • LOAD und REORG mit Inline COPY
    • Höhere Geschwindigkeit von LOAD, REORG, RUNSTATS durch Sampling, RECOVER INDEX
    • Dynamisches COPY (kein, Incremental, Full)

User Productivity Erweiterungen:

  • DB2 Installer und DB2 Visual Explain von Workstations aus
  • Aufnahme von C++ in Installation Panels und DB2I Interactive Panels
  • RRSAF Attachment Facility mit 2-Phase-Commit-Fähigkeit, Thread Reuse
  • Neue SQL Funktionen:
    • WITH CHECK OPTION CASCADED|LOCAL
    • Foreign Key Reference auf Unique oder Primary Key
    • Delete Rule NO ACTION
    • REFERENCES mit Spaltenliste
    • Multi-Row UPDATE von Primary Keys
    • CASE Ausdruck
    • Temporäre Tables
    • RENAME TABLE
    • neue eingebaute Funktionen
    • EXPLAIN Erweiterungen
    • max. Anzahl von Rows nun 255 ohne Alteration-Aufwand
    • DB2 Meldung bei ungenügender COMMIT-Frequenz von Anwendungen
  • SYSOPR-Autorisierung für START DATABASE Command

DB2/MVS Version 4

1995

  • Database Administration Features:
    • DB2 Catalog Reorg, User-defined Indexes für den Catalog
    • COPY, RECOVER und Reorg Verbesserungen
    • Data Sharing
    • TYPE 2 INDEX, UNIQUE WHERE NOT NULL Indexes, Row Level Locking
  • Client/Server Feature:
    • Stored Procedures
    • bis zu 25.000 Distributed Connections
  • Performance Feature:
    • Partition Scanning
    • Query CP Parellelism
    • Uncommited Read
    • keine Locks auf Type 2 Indexes
  • Programming Features:
    • Outer Join
    • In-Line Views
    • COALESCE Funktion
    • AS um Spalten umzubenennen
  • DB2 Catalog
    • 3 neue Tables
    • 16 veränderte Tables, Communications Database (CDB) wird in die DB2 Catalog Database verlegt

DB2/MVS Version 3

1994

  • Erweiterung der Bufferpools (50 x 4K und 10 x 32K)
  • Unterstützung von Hiperpools
  • Parellel I/O Processing für Query-Anforderung auf Partitioned Tablespaces
  • Partition-Unabhängigkeit durch Sperr-Niveau Partition
  • Multi-Site-Update-Fähigkeit bei verteilten Datenbanken

DB2/MVS Versionen 1.1 to 2.3

  • Version 2.3:                                                             1992
    • Packages und Collections
    • Remote Unit of Work Konzept (Application Directed Access)
    • Performance Verbesserungen: verzögerter CLOSE, Index Lookaside
    • erweiterte Limits
  • Version 2.2:                                                             1990
    • Distributed Databases (Distributed Unit of Work, System Directed Access)
    • Performance Verbesserungen: z.B. Multiple Index Access
  • Version 2.1:                                                             1988
    • Referential Integrity
    • Untersützung der RACF Autorisierungsgruppen
    • Audit Trace
    • Resource Limit Facility
    • Performance Verbesserungen
  • Version 1.3:                                                             1987
    • SQL Spracherweiterungen: DATE, TIME, TIMESTAMP, Scalar Functions
    • Performance Verbesserungen (wer hätte es gedacht)
  • Version 1.2:                                                             1985
    • Performance-Release mit erheblichen Verbesserungen
    • Restrukturierung des DB2 Catalogs
  • Version 1.1                                                              1983