430 likes | 460 Views
This presentation provides an introduction to Informix Dynamic Server, covering its background, architecture, installation, management, monitoring, and key features. Learn about significant milestones, design aspects, strengths, and architecture comments. Discover installation components, directory structure, instance setup, configuration files, starting, and stopping procedures. Explore monitoring tools and system catalog tables for effective management.
E N D
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
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
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
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
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
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
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
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
\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
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.
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
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
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
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
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
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
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
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
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
DBSpace Explorer Click a space name for more details Create a new space
DBSpace Explorer Details • Summary, Admin, Tables Extents
Session Explorer • List of all database server sessions • Ability to kill user sessions • Drill down into a users session for more details
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
Creating a Database • Can be done through dbaccess “database” menu • Can be done using CREATE DATABASE • Options:
Create a Table • Can be done through dbaccess “Table” menu • Can be done using CREATE TABLE
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
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
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
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
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