1 / 43

Introduction to Informix Dynamic Server

Introduction to Informix Dynamic Server. Jacques Roy jacquesr@us.ibm.com. Agenda. Background Architecture Installation Managing and Monitoring Using IDS Features overview. This is not a comprehensive presentation on IDS More capabilities are covered in other presentations.

rrudolph
Download Presentation

Introduction to Informix Dynamic Server

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. Introduction to Informix Dynamic Server Jacques Roy jacquesr@us.ibm.com

  2. Agenda • Background • Architecture • Installation • Managing and Monitoring • Using IDS • Features overview This is not a comprehensive presentation on IDS More capabilities are covered in other presentations

  3. Informix Significant Milestones • Informix 3.3 released in 1984 • Informix-online 5.00 released in 1991 • Informix Dynamic Server 7.10 release in 1994 • Informix acquires Illustra 1996 • Informix Universal Server 9.14 released in 1997 • IBM acquires Informix in 2001 • Informix releases: • Informix Dynamic Server 11.10 July 2007 • Informix Dynamic Server 11.50 April 2008 • Current Version: 11.50.xC4

  4. Data Servers Informix Dynamic Server versions 9.x, 10.x, 11.x Informix Dynamic Server 7.x Classics Informix C-ISAM Informix Standard Engine – SE Informix Turbo Informix OnLine – Version 5.x Informix XPS 8.x Informix Red Brick 6.x Tools & Connectivity Informix 4GL Informix ESQL C Cobol Informix EGM [w/ DRDA] Informix Product Family

  5. IDS Design: Multi-threaded Architecture • Multi-threading: “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.” “Threads Primer”, Bill Lewis, Daniel J. BergPage 21 • Benefits: • IDS can dynamically adapt to changes in workload due to the low cost of creating and removing threads • Threads scale to higher throughput than process-based servers

  6. IDS Strengths • Simple to use • “Set it and forget it” • Comprehensive Administration features • Easy to integrate with other applications • High Performance, highly scalable OLTP • From 1 CPU to 80 or more CPUs on an SMP box • Used on Wall St. for high volume market data • Adjusts parameters based on load • Continuous availability Invisible Agile Resilient

  7. Column-level Encryption Crypto VPs* I/O Processing AIO*, LIO, PIO VPs ADM, MISC VPs Custom VPs* Java UDRs Shared Memory Buffer Pool, Locks, Sessions, etc CPU VPs Local Client Processes Extension Communications Java VPs* NET VPs* Remote Client Processes Disks SQL execution IDS Architecture

  8. Architecture Comments • Memory usage compensates for disk speed • Asynchronous I/O permit processing while waiting for I/O • Read-ahead operations anticipate needs and reduce I/O overhead • All virtual processors are multi-threaded • Reduce the number of processes needed

  9. Storage • Physical storage • Page: 2,048 to 16,384 bytes • Extent: allocation of a group of pages • Chunk: contiguous disk storage (device or file) • Maximum size 2GB or 4TB • Maximum 32,766 chunks • Logical storage • dbspace, blobspace, sbspace, temporary dbspace,temporary sbspace • extspace • tblspace • Maximum of around 32,000 dbspaces (max size 8PB) • Objects • Databases, tables, indices

  10. Installation

  11. Installation Components

  12. \dbssodir \lib \aaodir \gls \etc \demo … \bin IDS Directory Structure \IDS_home (IDS_HOME set by $INFORMIXDIR environment variable) oninit, onstat, onmode, ontape, etc SQLHOSTS, ONCONFIG \extend datablades Default location for cooked Data files \IFMXDATA \Instance

  13. Setting up an Informix Instance • Environment variables: • INFORMIXDIR • Location of the Informix installation • INFORMIXSERVER • Name of the server • ONCONFIG • Name of the instance configuration file • INFORMIXSQLHOSTS • Connectivity information • PATH • Include the $INFORMIXDIR/bin directory • Many others • Terminal setup, localization, localization formatting, etc.

  14. Configuration files: $ONCONFIG • Located in $INFORMIXDIR/etc • Default name: onconfig • Start with a copy of onconfig.std • Important parameters: • DBSERVERNAME, SERVERNUM • ROOTNAME, ROOTPATH, ROOTSIZE • LOGFILES, LOGSIZE • PHYSBUFF, PHYSFILE • NETTYPE

  15. Configuration files: $INFORMIXSQLHOST • Located in $INFORMIXDIR/etc directory • Default name: sqlhosts • In the registry for windows • Defines how a client can connect to an instance • Dbservername, nettype, hostname, servicename, options

  16. Starting IDS: oninit • oninit executes and puts itself in the background • Options: (partial) -v: verbose -i: initialize the instance -y: assume a “yes” answer to any prompt • Examples: • oninit –ivy Start the server and initialize the instance • oninit –v Start the instance providing verbose output of the status

  17. Stopping IDS: onmode • onmode -k brings down the server • Other options: (partial) -c Force a checkpoint -l Switch the logical log file -p Add/remove virtual processors -Y Dynamically change SET EXPLAIN -z Kill a server session

  18. Monitoring IDS • System Monitoring Interface (SMI) • System catalog tables in the sysmaster database • onstat • Options: (partial) -- Print onstat options and functions - Print output header -d Print chunks information -D Print page-read page-written information -g buf Print buffer pool profile information -u Print user activity profile

  19. Storage Management: onspaces • Add/Remove chinks, and all types of dbspaces • Rename all types of dbspaces • Example:Create a dbspace of 20MB with a page size of 8KB: onspaces -c -d -p $INFORMIXDIR/DATA/myspace.dat -s 20480 -k 8

  20. Other Utilities (partial) • onparams: Add/Drop logical logs, change physical log parameters, add a new buffer pool • ontape: Backup/restore a database • oncheck: check database objects integrity • ondblog: change database logging mode • onlog: Display the content of logical log files

  21. SQL Query Drilldown • Provide detail information about SQL statements. • Information available through onstat or sysmaster database. • Dynamically configurable • task() and admin() functions • By default disabled • See: SQLTRACE • Global and User Tracing. • Table:sysmaster.syssqltrace

  22. OpenAdmin Tool for IDS (OAT) • Web Access • Graphical Interface • Drill down to complete details • Administer multiple remote servers • Easy to Customize • Open Source • PHP-based • Uses IDS SQL Admin API

  23. Database Alerts

  24. DBSpace Explorer Click a space name for more details Create a new space

  25. DBSpace Explorer Details • Summary, Admin, Tables Extents

  26. Performance History Graph

  27. Query Drill Down

  28. SQL by Transaction

  29. Operating System Information

  30. Session Explorer • List of all database server sessions • Ability to kill user sessions • Drill down into a users session for more details

  31. Session Explorer Details - SQL

  32. Accessing IDS: dbaccess • Can be used in full screen mode or in command line mode • Can execute the content of a file:dbaccess stores mycmd.sql

  33. Creating a Database • Can be done through dbaccess “database” menu • Can be done using CREATE DATABASE • Options:

  34. Create a Table • Can be done through dbaccess “Table” menu • Can be done using CREATE TABLE

  35. Create Table (cont.)

  36. SELECT company_id, SUM(amount) FROM tab WHERE company_id = 57 AND transaction_date BETWEEN ’04/01/08’ AND ’06/30/08’ GROUP BY company_id; group sort Iterators scan empty Oct-Dec Apr-Jun Jul-Sep Jan-Mar IDS Fragmentation Performance and Availability Feature

  37. IDS 11 Features

  38. IDS 11 Features

  39. IDS 11 Features

  40. Automatic Update Statistics (AUS) • Automatically maintain optimizer statistics • Simplifies the repetitive maintenance work on the database to ensure optimal performance • Easy setup and administration • Implemented via set of procedures • Easy admin of AUS policies via OAT

  41. RTO policy to manage server restart • RTO: Recovery Time Objective • Set the amount of time, in seconds, that IDS has to recover from a problem after the server restart • Set by creating RTO policy using RTO_SERVER_RESTART configuration parameter

  42. Two threads, 4 dbspaces Thread1 Thread2 Thread1 Thread2 DBS3 DBS3 DBS2 DBS4 DBS1 DBS4 DBS1 DBS2 Time Good ordering (New) Bad ordering (Earlier) Automatic ordering of dbspaces during backup and restore • Intelligent ordering of dbspaces during backup and restore to achieve maximum parallelism • Dbspaces restored in the same order as backup • Reduces the backup and restore time

  43. Sysdbopen()/Sysdbclose() • Sysdbopen()/Sysdbclose() UDRs • Executed after a successful open/close db or connect/disconnect • Set execution parameters • Send alerts • Begin auditing • Different versions per user or for PUBLIC • Does not execute on remote UDRs or distributed DMLs • Can be defined by DBA and user Informix

More Related