1 / 32

DB2 – Informix Terminology and Architecture Comparison

DB2 – Informix Terminology and Architecture Comparison. By Pradeep Kutty. Jan 30 th , 2004. DB2 – Product Family. Enterprise Server Edition (ESE). Enterprise Extended Edition (EEE). Version 8. Enterprise Edition (EE). Workgroup Edition (WE). Personal Edition (PE). Everyplace.

nizana
Download Presentation

DB2 – Informix Terminology and Architecture Comparison

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. DB2 – InformixTerminology and Architecture Comparison By Pradeep Kutty Jan 30th, 2004

  2. DB2 – Product Family Enterprise Server Edition (ESE) • Enterprise Extended Edition (EEE) Version 8 • Enterprise Edition (EE) • Workgroup Edition (WE) • Personal Edition (PE) • Everyplace

  3. Products Positioning • Single Product line for all Applications • Common code base for UNIX and NT • Differentiation depending on target platform and licensing strategy • Additional applications built upon the database server • Data warehouse • OLAP • Connectivity (DB2 Connect, Data Joiner) • Web enablement (Web Sphere Application Server)

  4. Informix – Product Family 7.3x 9.5x 9.4x 9.3x 9.2x 8.5x 7.2x 9.1x 9.x 8.4x 7.1x 8.3x Online 7.x 8.2x Online 6.x 8.1x XPS 8.x Online 5.x Online 4.x SE / CISAM

  5. Products Positioning • IDS (7.x and 9.x): • OLTP • Data blades and Extensibility • Replication (HDR and ER) • Max Connect • XPS (8.x) – • Data Ware Housing • Cluster support

  6. Informix – Long Term 2002 2003 2004 2005 2006 IDS: RAS, Ease-of-Use, Security 9.40 9.50 9.60 XPS:BI Tool Integration,Ease-of-Use 8.40 8.50 8.60 Red Brick:BI Tool Integration,Performance 6.2 7.1 7.2

  7. Platforms Supported • DB2 • AIX • NUMA-Q PTX • Linux • SUN Solaris • HP UX • Linux/390 (Not EEE) • AS/400 • OS/390 • Windows • OS/2 • XPS • AIX • NUMA-Q PTX • Linux • SUN Solaris • HP UX • Compaq True Unix • Reliant Unix • IDS • AIX 32/64 Bit • NUMA-Q PTX • Linux Intel / Alpha • SUN Solaris Intel / Sparc • HP UX 32/64 Bit • Compaq True Unix • Reliant Unix 32/64 Bit • SCO Open Server • SCO UNIXWARE 7 • Windows NT / 2000 • Tandem Nonstop UX C61 • DEC TRU64 • NCR • Fujitsu/Siemens • SGI 32/64 Bit • Data General DGUX

  8. DB2 Architecture Applications Client TCP/IP or SHM and Semaphores or any Network Protocol Coordinator agent EDU Server Log Buffer Subagents EDU Prefetch Manager Prefetch Requests Prefetchers Log requests Buffer Pool Logger EDU Deadlock Detector Logs Read Req Page Cleaners Parallel Page write requests Containers

  9. Client Net VP Shm VP CPU VP Resident LIO VP Virtual PIO VP AIO VP Communication Disks Ext VP Logs IDS/XPS Architecture IDS only Other VPs: ADM, ADT, Java, etc.

  10. Process vs. Thread “In Solaris, creating a process is about 30 times slower than creating a thread, synchronization variables are about 10 times slower, and context switching about 5 times slower.” Thread PrimerPage 21

  11. DB2 Instance Database Partition/Node Node group Table space Container IDS/XPS Instance Database Co-server Co-group Dbslices, Dbspace Chunks Terminology

  12. Configuration • DB2 • Instance Parameters controlled by DBM CFG • Every database has its own database configuration • Registry and Environment Variables • SQL SET command • Multi threaded only on NT • Informix • Instance Parameters controlled by ONCONFIG • Environment Variables • SQL SET Command • Multi threaded on UNIX and NT

  13. DB2 (table spaces) System Managed Storage (SMS) Database Managed Storage (DMS) Files Devices Configurable page size: 4KB, 8KB, 16KB, 32KB IDS/XPS (db spaces)- Database ManagedStorage Cooked Raw IDS pre-defined page size XPS configurable: 2KB, 4KB, 8KB Storage

  14. Extent Allocation • DB2 • Extent Size set at the table space level • Allocated in a round robin fashion among containers • Valid for all tables within the table space • Informix • Extent size set at the table level • Allocated in a serial manner

  15. Tables • DB2 • One table per table space • Partitioning in a EEE environment • Two extents are allocated at table creation time • Extent map • Data extent • Informix • Can span multiple db spaces (fragmentation) • First and next extent control extent sizing • Doubling after every 16 extents

  16. DB2 Not logged initially Global Temp Table Volatile Table Summary Tables Informix TEMP Scratch (XPS) Raw (XPS) Static (XPS) Operational (XPS) Standard (XPS) Table Types

  17. DB2 Determined by the page size Out-of-row objects: BLOB, CLOB, DBCLOB, LONG VARCHAR, LONG VARGRAPHIC IDS/XPS 32KB (page linking) (IDS) Out-of-row objects: BLOB, BYTE, CLOB, TEXT Row size

  18. Object Names Sizes Object DB2 XPS IDS Database name 8 18 128 User name 8* 8 32 Table name 128 18 128 Column name 30 18 128 Index name 18 18 128 Procedure name 18 18 128

  19. Memory Usage • IDS/XPS • Instance memory shared by all databases(buffer pool, locks, heap, stack, sort, cache, etc) • DB2 • Instance shared memory • Database shared memory(buffer pool, lock list, sort, heap, etc.) • Application shared memory • Agent private memory

  20. DB2 Separate Table space than table (DMS) Forward and Reverse Scans B-Tree MDC Index Db2advis [Advisor] Informix Attached and Detached Indexes B-Tree R-Tree (IDS Only) Functional (IDS Only) Bitmap (XPS Only) GK (XPS Only) Indexes

  21. DB2 Row level is the default LOCK parameters include: LOCKLIST and MAXLOCKS LOCK escalation can occur based on parameter settings Informix Specified at table creation Default is page ONCONFIG Parameter: LOCKS No Lock escalations Locking

  22. DB2 Logs allocated at Database level Primary and secondary logs Circular logging Archival Logging Backed up using USEREXIT Dual logging Infinite log space Informix Logs allocated at Instance level Default is No logging Backed up using ontape onbar Logical Logs

  23. DB2 Control Center (backup, index, configuration wizards) Alert center Performance monitor Snapshot Monitoring Locks BufferPools SQL Sort Operations Event Monitors Explain [dynexplain/db2exfmt] Informix onstat onparams onspaces, etc. ISA I-SPY Explain Management [Analysis] Tools

  24. DB2 Import / Export REORGCHK REORG RUNSTATS db2look Db2batch Load db2move Informix dbexport HPL Unload Load Update statistics dbschema External Table (XPS) Utilities

  25. DB2 Online Offline Incremental Delta Split Mirror Redirected Restore Informix Level 0 Level 1 Level 2 EBR Imported Restore Backup / Restore

  26. DB2 Types: Distinct Structured DB2 Functions Scalar Table IDS Types: Distinct Row Opaque Collection (set, list, …) IDS Functions: Scalar Iterator Aggregate Extensibility

  27. DB2 Extenders Spatial XML Text Audio Video Image IDS Data Blades Spatial/geodetic Time Series, NAG Text, image, video Third parties IDS Bundles Financial Foundation Law Enforcement Foundation Other to come Data Blades and Extenders

  28. DB2 Future features • Autonomic Computing • 4GL and ESQL/C (SQLI) support • IDS date types and functions • IDS replication • HDR in V8.2 • IDS fragmentation • R-Tree Indexes • Time Series Data blade

  29. IDS – The Future 2006 IDS9.60 Announced GA March 28th • Backup & Restore enhancements • High Availability Data Replication working with ER • Remove Capacity Limitations • >2gig chunk size • Security Enhancements • System Monitoring Enhancements • track causes of deadlocks • Utility Enhancements • > 2gig file support Driven by Customer & Partner Requests 2005 • Safety – High Availability • XML Standards Compliance • On-Line Reorg IDS9.50 In Planning: 2004 • Safety – High Availability • Application Dev / XML / Stds. Compliance • 4GL with WebSphere / IDS • Autonomic / Ease of Administration • Software Group Integration • Security Enhancements • Partner Enhancements • Infrastructure Improvements • Performance, Install, I-Star for Built-in UDTs IDS9.40 2003

  30. Conclusion • DB2 and IDS complement each other • The synergy between development groups will accelerate technology migration both ways • The IBM portfolio strengthens the Informix position • The DB2 federated approach helps support dual environments

  31. For Additional Information • Web sites: • http://www.ibm.com/software/data/db2 • http://www.ibm.com/software/data/informix

  32. Questions?

More Related