如何用SQL Explorer或其他工具在多台Informix服务器执行SELECT查询

Got it, let's tackle this problem step by step. You're already using SQL Explorer for single Informix server queries, and now you need to run the same SELECT across multiple instances and grab individual results—here's how to make it work with SQL Explorer, plus some reliable alternative tools if you need more flexibility.

First, let's cover how to do this with the tool you're already familiar with:

Step 1: Set Up All Informix Connections #

• Open SQL Explorer and navigate to the Connections pane (usually the left sidebar).
• Click the New Connection button (typically a plus sign or "Add Connection" option).
• For each Informix server, fill in your connection details: host address, port, database name, credentials, and select the existing Informix driver you used for your single server setup.
• Pro tip: Name each connection clearly (e.g., "Informix-Prod-01", "Informix-Staging-02") so you can easily distinguish results later.

Step 2: Run Queries & Capture Results #

You have two straightforward options here:

Option 1: Manual Per-Connection Execution #

• Select one named connection from the Connections pane.
• Paste your SELECT query into the query editor.
• Click Execute (play button) and export the result set (use the "Export" option to save as CSV/Excel/JSON) with a filename tied to the server name (e.g., prod01_results.csv).
• Repeat this process for every Informix connection you set up.

Option 2: Batch Script (If Your SQL Explorer Version Supports It) #

Some SQL Explorer variants let you write a script that switches between connections. For example:

-- Switch to Informix-Prod-01
USE CONNECTION "Informix-Prod-01";
SELECT * FROM your_table WHERE your_condition;
-- Export result (check your SQL Explorer's built-in export commands if needed)

-- Switch to Informix-Staging-02
USE CONNECTION "Informix-Staging-02";
SELECT * FROM your_table WHERE your_condition;
-- Export result

Note: The exact syntax for switching connections varies by SQL Explorer version—check the built-in help if USE CONNECTION doesn't work.

If SQL Explorer's multi-server capabilities feel limited, these tools are perfect for the job:

1. IBM Data Studio (Official Informix Tool) #

This is IBM's native tool for managing Informix databases, built specifically for multi-instance tasks:

• Install IBM Data Studio (make sure to include the Informix plugin during setup).
• Add all your Informix servers to the Data Source Explorer pane.
• Create a new script file and write blocks for each server:

  -- Connect to Informix-Prod-01
  CONNECT TO informix_prod_01 USER your_username USING your_password;
  SELECT * FROM your_table WHERE your_condition;
  -- Clean disconnect
  DISCONNECT informix_prod_01;
  

• Run the full script—each server's result set will appear in a separate tab in the Results view, which you can export individually.

2. DBeaver (Free, Cross-Platform) #

A popular open-source tool with robust multi-server support for Informix:

• Install DBeaver, then add the Informix driver (it’s in the built-in driver list—just select it when creating a new connection).
• Set up named connections for all your Informix servers.
• Go to Database > Execute Script on Multiple Connections.
• In the popup, select all your Informix connections, paste your SELECT query, and click Run.
• DBeaver will display all results in a single view, labeled by server, and let you export each result set separately or all at once with server identifiers.

3. Shell Scripting + isql (Command-Line Option) #

If you prefer command-line workflows, use Informix's built-in isql utility:

• Ensure isql is installed (comes with the Informix Client SDK).
• Create a shell script (e.g., run_informix_query.sh) with this structure:

  #!/bin/bash
  # Define server list with connection details (host:port:db:user:pass)
  servers=(
    "prod01:9088:prod_db:user1:pass1"
    "staging02:9088:staging_db:user2:pass2"
  )
  
  # Your target SELECT query
  query="SELECT * FROM your_table WHERE your_condition;"
  
  # Loop through each server and run the query
  for server in "${servers[@]}"; do
    IFS=':' read -r host port db user pass <<< "$server"
    output_file="result_${host}.csv"
    # Execute query and export to CSV
    isql -S $host:$port -D $db -U $user -P $pass -b -c "$query" > $output_file
    echo "Results from $host saved to $output_file"
  done
  

• Make the script executable (chmod +x run_informix_query.sh) and run it—you’ll get a separate CSV file for each server.

内容的提问来源于stack exchange，提问作者Sundar