Advanced DataTools
Webcast
from the IBM Informix Champions
Informix Tutorial
Getting Started with
Informix
by Lester Knutsen
Thursday, January 30, 2020 at
2:00pm EDT
Lester Knutsen
Lester Knutsen is President of Advanced DataTools
Corporation, and has been building large data
warehouse and business systems using Informix
Database software since 1983. Lester focuses on
large database performance tuning, training, and
consulting. Lester is a member of the IBM Gold
Consultant program and was presented with one of
the Inaugural IBM Information Champion awards by
IBM. Lester was one of the founders of the
International Informix Users Group and the
Washington Area Informix User Group.
lester@[Link]
[Link]
703-256-0267 x102
2
Webcast Guidelines
• The Webcast is being recorded. The
Webcast replay and slides will be
available in a few days.
• Please Mute your line. Background
sounds will distract everyone.
• Use the Chat Button in the upper right
to ask questions.
3
Informix Tutorial - Getting
Started with Informix by
Lester Knutsen
The Webcast is a step by step guide to installing and
getting up and running with a basic Informix Server.
We will introduce using InformixHQ, the IBM Informix
Knowledge Center, and how to find resources to get
started with Informix.
Agenda
Getting Started with Informix
• Informix Products Overview
• Informix Architecture
– Memory, CPU, Disk Requirements
• Planning an Informix Install
• Demo – Informix Workgroup Install
• Software Directory Structure
• Using Informix SQL
• Informix Documentation
• Informix Resources on the Web
5
Informix Products Overview
Informix Servers History
• Informix C-ISAM
• Informix Standard Engine (SE)
• Informix OnLine 4.x and 5.x
• Informix Dynamic Server 7.x
• Informix Extended Parallel Server 8.x
• Informix Dynamic Server 9.x,10.x, 11.x
• Current - Informix 12.x and 14.x
Informix DBA 7
Informix 12.x and 14.X
Editions
• No Cost Editions
– IBM Informix Developer Edition
– IBM Informix Innovator-C Edition
• Purchase Editions
– IBM Informix Express Edition
– IBM Informix Workgroup Edition
– IBM Advanced Workgroup Edition – 12.x only
– IBM Informix Advanced Developer Edition
– IBM Informix Enterprise Edition
– IBM Informix Advanced Enterprise Edition
Informix DBA 8
Connectivity/Client
Products
• Informix Client Software Developer's Kit
(SDK) includes
– Informix ODBC
– Informix ESQL
• Informix Enterprise Gateway
• Informix JDBC Driver/Embedded SQL
• IBM Data Server Driver (IBM Common Driver)
Informix DBA 9
DataBlade Modules and
DataBlade Developer's Kit
• Informix NAG DataBlade
• Informix C-ISAM DataBlade
• Informix Data Director for Web
• Informix Geodetic DataBlade
• Informix Image Foundation DataBlade
• Informix Spatial DataBlade
• Informix TimeSeries DataBlade
Informix DBA 10
Development Tools
• Informix ESQL/C
• Informix 4GL
• Informix SQL
• Informix Genero (4J’s)
• Java
• PHP
• Ruby on Rails
• Perl
• Python
Informix DBA 11
Informix Warehouse
Accelerator
Informix DBA 12
Informix Administration
Tools
• Command Line Utilities
• InformixHQ – New GUI Admin Tool
release with Informix 14
• Server Studio
[Link]
Informix DBA 13
InformixHQ
Informix DBA 14
InformixHQ on an iPhone
Informix DBA 15
Informix Architecture
Memory, CPU, Disk Requirements
Informix Architecture
• SQL Client process
• Server process – (oninit)
• Shared Memory
• Disk space – (dbspace)
Informix DBA 17
Informix Architecture
SQL SQL SQL SQL SQL
Clients Clients Clients Clients Clients
Oninit Oninit Oninit
Oninit
Informix
Shared
Oninit Memory Oninit
Disk Drives
Informix DBA 18
Oninit Process
Informix Introduction 19
Oninit Process
onstat –g sch
Informix Introduction 20
Oninit Process - Class
• CPU - Executes all user and session threads and some system threads
• PIO - Handles physical log file when cooked disk space is used
• LIO - Handles logical log file when cooked disk space is usedAIO -
Handles disk I/OSHM - Performs shared memory communications
• TLI - Performs TLI network communications
• SOC - Performs socket network communications
• FIFO - Performs FIFO operaations
• OPT - Handles optical disk I/O
• ADM - Executes administrative threads
• ADT - Executes auditing threads
• MSC - Handles request for system calls
Informix Introduction 21
Informix Shared Memory
•Control Tables
Resident Memory Segment •Buffers
LRU Page Page
LRU Page Page
LRU Page Page
LRU Page Page
•Dictionary cache
Virtual Memory Segment •Working Storage
•Sort Space
Message Memory Segment
Informix Introduction 22
Informix Shared Memory
onstat –g seg
Informix Introduction 23
Memory Classes
• R – Resident Memory Segment
• B – Buffer Pool Segment for data
• V – Virtual Memory Segment for
Working Storage
• M – Message Segment for
communications between clients
Informix Introduction 24
Informix Disk Space
• Dbspace – Logical container of Chunks
• Chunk – Physical container
– Raw Disk – Informix direct access to disk
– Cooked Disk – Informix accesses disk
through OS filesystem
• Page – I/O Block: 2K, 4K, 8K, 16K
• Tablespace – All pages assigned to a table
• Extent – Set of contiguous pages
Informix Introduction 25
Informix Dbspace
onstat -d
Informix Introduction 26
Disk Layout - Pages, Chunks, and Dbspaces
DBSPACE
Chunk Chunk Chunk
Page Page Page Page Page Page Page Page Page
Page Page Page Page Page Page Page Page Page
Page Page Page Page Page Page Page Page Page
Page Page Page Page Page Page Page Page Page
Page Page Page Page Page Page Page Page Page
Page Page Page Page Page Page Page Page Page
Informix Introduction 27
Disk Layout -Tables and Extents
Too many extents will slow disk access
DBSPACE
Table Chunk Chunk Chunk
Orders
Page Page Page Page Page Page Page Page Page
Page Page Page Page Page Page Page Page Page
Table
Codes
Page Page Page Page Page Page Page Page Page
Page Page Page Page Page Page Page Page Page
Table
Products Page
Page Page Page Page Page Page
Page Page Page Page Page Page Page Page Page
Informix Introduction 28
Informix User Sessions
SQL SQL SQL SQL SQL
Clients Clients Clients Clients Clients
Oninit Oninit Oninit
Oninit
Informix
Shared
Oninit Memory Oninit
Disk Drives
Informix Introduction 29
Informix User Sessions
onstat -u
Informix Introduction 30
Planning an Informix Install
Planning an Informix Install
Prep Questions
• What directory will the Informix Software
be installed in?
– /opt/informix14.10.FC3
– /local_apps/informix14.10.FC2
• Install the software in a directory with
the version name and the create a link
to /opt/Informix
ln –s /opt/informix14.10.FC3 /opt/Informix
Informix DBA 32
Planning an Informix Install
Prep Questions
• What are the name(s) of the Informix
Servers?
• Recommend different names for the
different connection methods
– train1- for shared memory connections
– train1tcp – for network connections
Informix DBA 33
Planning an Informix Install
Prep Questions
• How much memory is required and will
be allocated to Informix?
– BUFFERPOOL
– SHMVIRTSIZE
– SHMADD
Informix DBA 34
Planning an Informix Install
Prep Questions
• How much disk space is required and
will be allocated for Informix?
– Rootdbs
– Logical Logs
– Physical Logs
– Temp data space
– Data space
– Index space
– Blob dbspace
– Smart Blob dbspace
Informix DBA 35
Planning an Informix Install
Prep Questions
• Will you use Raw or Cooked space?
• Where will the space be located?
• Best Practices
– Use symbolic links
– /informixchunks/servername/dbchunks
– Use non journaled filesystems for cooked
space
– Use Ext2 filesystems for Linux
Informix DBA 36
Planning an Informix Install
Prep Questions
• How many CPU’s will be allocated for
Informix?
– Traditional Best Practices is number of
physical CPU CORE-1
– Current CPU CORE are fast enough to
handle 2-3 oninits per CORE
Informix DBA 37
Planning an Informix Install
Prep Questions
• What are the protocols users will conect
to Informix with?
– Shared Memory
– TCP Sockets
– DRDA
– REST
• What tcp ports will be used for network
connections?
Informix DBA 38
Planning an Informix Install
Prep Questions
• Will the InformixHQ be installed?
– Admin login and password
– Ports (default is 8080)
Informix DBA 39
Installing Informix Demo
Compare the Informix
Editions
[Link]
41
Download Informix
Developer Edition
[Link]
42
Installing Informix
Developer Edition
43
Software Directory Structure
Software Directory
Structure
The following are some of the important directories found under
$INFORMIXDIR:
• bin - Informix binaries and executable files. This directory must
be included in your path.
• demo - Demonstration files and source code. Use the demo
programs to copy these files to your own directory.
• etc - Informix configuration files
• incl - Library files used by Informix software and compilers
• msg - Error messages and text message files
• release - Release notes and documentation. This contains the
latest information and updates.
Informix DBA 45
Using Informix SQL
Connecting to Informix
Informix uses four environment variables:
• INFORMIXDIR
– Points to the directory where Informix products are installed.
• PATH
– $INFORMIXDIR/bin must be added to the PATH for executables to
be found.
• INFORMIXSERVER
– Points to the name of the Informix Database Server the user will
access.
• ONCONFIG
– Points to the server configuration file. This is only required when
OnLine is started and for DBA functions. This is not used by end
users.
Informix DBA 47
Example Environment
Variables
INFORMIXDIR=/opt/informix
export INFORMIXDIR
PATH=$INFORMIXDIR/bin:$PATH
export PATH
INFORMIXSERVER=train1
export INFORMIXSERVER
ONCONFIG=onconfig.train1
export ONCONFIG
Informix DBA 48
Informix Dbaccess vs ISQL
• Dbaccess (dbaccess)
– Comes with the database engine
– Includes all new features of the engine release
– Does NOT include ACE Reports and Perform Forms
• INFORMIX-SQL (isql)
– Separate stand-alone product
– Separate purchase
– Does not include all new features of current engine release
– Includes ACE Report Writer
– Includes Perform Form Screens
Informix DBA 49
Dbaccess Main Commands
DBACCESS: Query-language Connection Database Table Session Exit
Use SQL query language.
-------------------------------------- Press CTRL-W for Help --------
• Query-Language - Use SQL query language
• Connection - Connect or disconnect from a database
environment
• Database - Select, create, info, drop, or close a database
• Table - Create, alter, or drop a database table
• Session - Retrieve information about the current DB-Access
session
• Exit - Exit DBACCESS
Informix DBA 50
Dbaccess SQL
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Enter new SQL statements using SQL editor.
--------------------- stores7@train1 --------- Press CTRL-W for Help -------
• New - Create new SQL statements using the Informix built-in editor
• Run - Execute SQL statement
• Modify - Changes SQL statement using the Informix built-in editor
• User editor - Create or modify SQL statements using selected editor
• Output - Output results of SQL statement to printer, file or pipe
• Choose - Select SQL statements saved in current directory
• Save - Save current SQL statement in a file in the current directory
• Info - Display information about tables in the database
• Drop - Delete SQL files in current directory
Informix DBA 51
Dbaccess SQL Built-in Editor
• ESC - Done editing, return to menu
• CTRL-A - Toggle between Typeover/Insert
mode
• CTRL-R - Redraw screen
• CTRL-X - Delete current character
• CTRL-D - Delete rest of current line
Informix DBA 52
Dbaccess Command Line
• dbaccess <database name>
• Starts dbaccess using selected database
• dbaccess stores7
• dbaccess -<first letter of menu option>
• Starts dbaccess in select menu options.
• dbaccess <database name> -<first letter of
menu>
• Starts dbaccess using selected database in the selected menu
option
• dbaccess stores7 -qi
• This starts dbaccess using the stores7 database and goes into
the Query, Info menu option.
Informix DBA 53
Dbaccess Command Line
• dbaccess <database name> <sql script name>
• Executes SQL script using the selected database
• dbaccess - -
• Starts dbaccess, SQL in interactive option with no menus. This
is useful for writing shell scripts
• dbaccess -v
• Displays version number and serial number
• dbaccess -e <database> <script>
• Echo sql statements as they are executed
• dbaccess -ansi
• Provides warnings when Informix extensions to ANSI SQL are
used
Informix DBA 54
Displaying Error Messages -
finderr
Usage: finderr msgnum [msgnum2 ...]
• finderr searches the file of error message explanations
distributed with Informix products and copies the text of one or
more error messages to the standard output.
• If an unsigned number is given, a negative sign is assumed.
• Examples:
– finderr 327 (looks for message number -327)
– finderr -327 (looks for message number -327)
– finderr +1234 (looks for message number 1234)
– finderr -233 107 113 134 143 144 +1541 | more
Informix DBA 55
Informix Command Line
Utilities
• ONSTAT - shows shared memory and server statistics
• ONCHECK - checks and repairs disk space
• ONMODE - changes Server's operating mode
• ONLOG - logical log debugging tool
• ONINIT - initialize and start up the database server
• ONSPACES - configure dbspaces and chunks
• ONPARAMS - configure logs
• ONTAPE - backup and restore utility
• ONLOAD - loads databases and tables
• ONUNLOAD - unloads databases and tables
Informix DBA 56
More Informix Tools
• Dbschema – Prints Schema
• Dbimport - Imports a database
• Dbexport – Exports a database
• Dbload – Loads data
• Ipload - High Performance Loader
Informix DBA 57
Informix Documentation
Documentation
• All documentation is available online at:
[Link]
• IBM Informix Server v12.10 Information
Center
[Link]
• IBM Informix Server v14.10 Information
Center
[Link]
Informix DBA 59
Key Administrator Manuals
• Getting Started with Informix Dynamic Server
– This manual provides an overview of the Informix database server and SQL API
environment, summarizes important features of Informix Dynamic Server, and provides
a road map to help you use the documentation set.
• Administrator's Guide for Informix Dynamic Server
– This manual is both a user guide and a reference manual to the features of Informix
Dynamic Server. It is intended to help you understand, configure, and use your
database server.
• Informix Administrator's Reference
– This manual provides reference material for Informix Dynamic Server. It provides the
syntax of database server utilities such as onmode and onstat, and comprehensive
descriptions of configuration parameters, the sysmasters tables, and logical-log
records.
• Informix Backup and Restore Guide
– This manual explains the concepts and methods that you need to understand when
you use the ON-Bar utility to back up and restore data.
• Performance Guide for Informix Dynamic Server
– This guide provides information on how to configure and operate Dynamic Server to
achieve optimum performance.
Informix DBA 60
Key SQL Manuals
• Informix Guide to Database Design and Implementation
– This manual provides information to help you design, implement, and manage your
Informix databases. It includes data models that illustrate different approaches to
database design and shows you how to use Structured Query Language (SQL) to
implement and manage your databases.
• Informix Guide to SQL: Reference
– This guide provides information on the following topics: Informix databases, data types,
system catalog tables, environment variables, and the stores_demo demonstration
database. It also contains a glossary.
• Informix Guide to SQL: Syntax
– This guide provides detailed descriptions of the syntax for all Informix SQL and SPL
statements.
• Informix Guide to SQL: Tutorial
– This guide provides a tutorial on SQL, as implemented by Informix products. It
describes the basic ideas and terms that are used when you work with a relational
database.
Informix DBA 61
Informix Resources on the Web
Informix Resources from
IBM
• Informix Documentation
[Link]
• Compare the Informix Editions by
Carlton Doe, IBM
[Link]
• A quick look at the new Informix v.14.10
installation mechanism, Created by
Carlton Doe, IBM
[Link]
Informix DBA 63
Informix Resources - IIUG
• The International Informix User Group
– [Link]
• Membership is FREE
Informix DBA 64
Informix Resources
• Advanced DataTools Tech Info
– [Link]
• Advanced DataTools Webcast Replays
– [Link]
Informix DBA 65
Questions?
Send follow-up questions to
Lester@[Link]
International Informix User Group:
[Link]
67
Free Informix Tutorials Webcasts
from the IBM Informix Champions
A step by step guide to using Informix Database Servers
Ø Getting Started with Informix by Lester Knutsen on January 30th, 2020 at 2:00 pm EST
This Webcast is a step-by-step guide to installing and getting up and running with a
basic Informix Server. We will introduce using InformixHQ, the IBM Informix
Knowledge Center, and how to find resources to get started with Informix.
Ø Configuring a New Informix Server by Lester Knutsen on February 27th, 2020 at 2:00
pm EST
This Webcast will be an introduction to the Informix ONCONFIG file and configuring
memory, CPUs, network, and disk for a more extensive Informix Server.
Ø Managing Informix Disk Space - March 19, 2020 at 2:00 pm EDT
Ø Managing Informix Logs - April 30, 2020 at 2:00 pm EDT
Ø Informix Backup, Recovery, and High Availability - May 28, 2020 at 2:00 pm EDT
Ø Connecting Users to Informix Servers - June 25, 2020 at 2:00 pm EDT
Ø Creating Databases and Tables in Informix - July 23, 2020 at 2:00 pm EDT
Ø Basic Informix Server Monitoring - August 20, 2020 at 2:00 pm EDT
Registration and more information: [Link]
Informix Training
Updated for Informix 14.10
Attend classes online on the web or in person at our training center in Virginia. All you need is a web browser to connect to our
WebEx training system, and an SSH client (like Putty) to connect to our training lab for hands-on exercises. Each student uses an
8-core Linux server, with 16GB RAM, SSD drives with Informix 14, and several large databases for benchmark exercises.
Informix Training in 2020
Ø May 18-21, 2020 - Informix for Database Administrators
Ø July 6-9, 2020 - Advanced Informix Performance Tuning
Ø October 5-8, 2020 - Informix for Database Administrators
More information and registration at:
[Link]
69
Informix 14 Training
Each student in class
will have a server
running Informix
14.10 with:
- 8 CPU Cores
- 16 GB RAM
- 1 SSD Disk
- 1-4 Disks
Class size is limited to
8 students.
Attend online or in
person!
70
Visit our New Website
[Link]
71
Visit our New Website
[Link]
72
Informix Support and Training from the Informix Champions!
Advanced DataTools is an Advanced Level IBM Informix Data Management Partner, and has been an authorized
Informix partner since 1993. We have a long-term relationship with IBM, we have priority access to high-level support
staff, technical information, and Beta programs. Our team has been working with Informix since its inception, and
includes 8 Senior Informix Database Consultants, 4 IBM Champions, 3 IIUG Director’s Award winners, and an IBM
Gold Consultant. We have Informix specialists Lester Knutsen and Art Kagel available to support your Informix
performance tuning and monitoring requirements!
• Informix Remote DBA Support Monitoring
• Informix Performance Tuning
• Informix Training
• Informix Consulting
• Informix Development
Free Informix Performance Tuning Webcast replays at:
[Link]
Email: info@[Link]
Web: [Link]
73
Thank You
Advanced DataTools Corporation
For more information:
Lester@[Link]
[Link]