Ubuntu Informix数据库管理方法

Installing Informix on Ubuntu
Before managing an Informix database on Ubuntu, you must first install the software. Start by creating a dedicated informix user and group to isolate database operations:

sudo groupadd informix
sudo useradd -g informix -d /opt/informix -m informix
sudo passwd informix

Set up environment variables in the informix user’s ~/.bash_profile to simplify command execution:

export INFORMIXDIR=/opt/informix
export INFORMIXSERVER=your_server_name
export ONCONFIG=onconfig.your_server
export LD_LIBRARY_PATH=$INFORMIXDIR/lib:$LD_LIBRARY_PATH
export PATH=$INFORMIXDIR/bin:$PATH

Reload the profile with source ~/.bash_profile. Download the Informix Linux package from IBM’s website, extract it to /opt/informix, and run the installer:

tar -xvf informix_package.tar.gz -C /opt/
cd /opt/informix
./ids_install

Follow the on-screen prompts to complete the installation.

Configuring the Database Server
After installation, configure core files to define database behavior. Copy the sample onconfig.std to a custom file (e.g., onconfig.your_server) and edit critical parameters:

cp $INFORMIXDIR/etc/onconfig.std $INFORMIXDIR/etc/onconfig.your_server

Key settings include:

• ROOTPATH: Path to the root dbspace file (e.g., /opt/informix/data/rootdbs).
• DBSERVERNAME: Unique name for your database server.
• LOGSIZE: Initial size of the logical log files (e.g., 100000 pages).

Create the root dbspace file and set proper permissions:

mkdir -p /opt/informix/data
touch /opt/informix/data/rootdbs
chown informix:informix /opt/informix/data/rootdbs
chmod 660 /opt/informix/data/rootdbs

Initialize the database server with:

onmode -ky  # Stop any running instance
oninit -iv  # Initialize and start the server

Verify the server status using onstat -i; you should see “Server is up and running”.

Managing Database Instances
Use oninit to control the server lifecycle:

• Start: oninit -i (initializes if not already running) or oninit -v (verbose mode).
• Stop: onmode -ky (graceful shutdown) or onmode -ky -s (immediate shutdown).
• Restart: onmode -ky && oninit -iv.

For remote access, configure the sqlhosts file ($INFORMIXDIR/etc/sqlhosts) to define connection methods (e.g., TCP/IP):

your_server onsoctcp your_hostname informix_services

Update /etc/services to include the Informix service port (e.g., informix_services 9088/tcp). Restart networking for changes to take effect:

sudo systemctl restart networking

Test connectivity with dbaccess, Informix’s command-line tool:

dbaccess your_database -e "SELECT 1 FROM systables WHERE tabid = 1;"

This confirms the server is accessible and the database is operational.

Monitoring and Maintenance
Regularly monitor system resources to ensure optimal performance. Use these tools:

• System Monitor: Graphical tool to view CPU, memory, and disk usage (gnome-system-monitor).
• Command-Line Tools: top (real-time process monitoring), htop (enhanced top), and df -h (disk space).
• Informix Tools: onstat -g seg (check disk space allocation), onstat -g sql (monitor active SQL queries), and onstat -g ses (view active sessions).

Optimize performance by:

• Indexing: Create indexes on frequently queried columns (e.g., CREATE INDEX idx_name ON table_name(column_name)).
• Query Tuning: Avoid complex subqueries; use EXPLAIN to analyze query plans.
• Configuration: Adjust ONCONFIG parameters like BUFFERPOOL size based on workload.

Back up databases regularly to prevent data loss. Use dbexport for logical backups (exports schema and data to flat files) or ontape for physical backups (writes to tape or disk):

dbexport your_database
ontape -s -L 0 -f /backup/your_database.tape

Schedule backups via cron to automate the process.

Security Best Practices
Secure your Informix deployment to protect against unauthorized access:

• Firewall: Restrict incoming traffic to the Informix port (e.g., 9088) using ufw:

  sudo ufw allow from trusted_ip to any port 9088
  sudo ufw enable
  

• SSH Hardening: Disable root login, use key-based authentication, and change the SSH port:

  sudo sed -i 's/PermitRootLogin yes/PermitRootLogin no/' /etc/ssh/sshd_config
  sudo sed -i 's/#PasswordAuthentication yes/PasswordAuthentication no/' /etc/ssh/sshd_config
  sudo systemctl restart sshd
  

• File Permissions: Ensure Informix directories (/opt/informix) are owned by the informix user and have restrictive permissions (e.g., 750).
• Passwords: Enforce strong passwords for the informix user and database users. Regularly update passwords and audit account activity.