ODBC

Connect to ODBC data sources either locally or through a remote OIBus Agent. Particularly useful for systems like Aspen InfoPlus.21® (IP21) and other ODBC-compatible applications.

Compatibility Notes

• Works with any ODBC-compatible data source
• Recommended for Aspen IP21 connections
• Supports both local and remote driver configurations

Specific Settings

Setting	Description	Default Value	Notes
Use remote agent	Enable to use OIBus Agent on remote machine	Disabled	Requires agent installation
Remote agent URL	Agent endpoint (e.g., http://host:2224)	-	Required when using remote agent
Connection timeout	Maximum time (ms) to establish connection	15000	Recommended: 5000-15000ms
Retry interval	Time (ms) to wait before retrying connection	10000
Connection string	ODBC connection string in driver-specific format	-	See examples below
Password	Authentication password (stored securely)	-	Added as PWD=<password> if not in string

Connection String Examples

Windows: Driver={driver name};SERVER=host;PORT=10014;Database=db;UID=user

Example: Driver={AspenTech SQLplus};HOST=ip21-server;PORT=10014;MAXROWS=12000000

UNIX-like systems: Driver=/path/to/driver.so;SERVER=host;PORT=10014;Database=db;UID=user

Example: Driver=/opt/lib/libmsodbcsql.18.dylib;SERVER=localhost;Database=test

macOS Setup

1. Install unixodbc: brew install unixodbc
2. Verify installation: odbcinst -j
3. List drivers: cat /opt/homebrew/etc/odbcinst.ini

Driver Installation

Platform	Installation Method
Windows	Use ODBC Data Source Administrator to configure drivers
UNIX/Linux	Install driver then specify full path in connection string
macOS	brew install unixodbc + driver installation

Aspen IP21 Configuration

For optimal IP21 performance:

1. Install OIBus Agent on IP21 machine
2. Use connection string: Driver={AspenTech SQLplus};HOST=<host>;PORT=10014;MAXROWS=12000000
3. Ensure AspenTech SQLplus driver is installed on agent machine
4. Configure agent service with appropriate permissions

Authentication

• Passwords in connection strings are stored in plain text. Use Password field instead.
• For local authentication, ensure OIBus Agent runs with proper permissions

Throttling Settings

You can optimize data requests using throttling configurations. These settings help manage large data intervals and network conditions effectively.

Key Throttling Parameters

Setting	Description	Data Type	Example Value
Max read interval	Maximum duration (in seconds) for each data request interval. Larger intervals are automatically divided into smaller chunks not exceeding this value.	Seconds	3600
Read delay	Delay (in milliseconds) between consecutive data requests. Helps prevent server overload.	Milliseconds	1000
Overlap	Time overlap (in milliseconds) between consecutive queries to ensure data continuity. This value is subtracted from the @StartTime of the next query.	Milliseconds	60000

How Throttling Works

1. Interval Division:

• Large time ranges are automatically split into smaller intervals
• Each sub-interval does not exceed the Max read interval duration
• Example: A 24-hour request with Max read interval = 3600 (1 hour) will be split into 24 separate 1-hour requests

2. Request Timing:

• The Read delay introduces a pause between consecutive requests
• Helps manage server load and prevents rate limiting
• Particularly useful during network instability or when querying large datasets

3. Data Continuity:

• The Overlap setting ensures no gaps in your data
• Creates a time buffer between consecutive queries
• Example: With Overlap = 60000 (1 minute), each query will include 1 minute of overlapping data with the previous query

Overlap Functionality

The overlap setting modifies the @StartTime of each query by subtracting the overlap value from the calculated start time. This adjustment applies to the entire query range, not to individual sub-intervals when large requests are split into smaller chunks.

This functionality is particularly useful when:

• New data is ingested after previous queries have executed
• You need to account for potential ingestion delays in the source system

Example: With overlap=60000 (1 minute), a query for the interval [10:00-11:00] will actually request [9:59-11:00], ensuring capture of any late-arriving data from the previous time period.

Recommended Configurations

Scenario	Max read interval	Read delay	Overlap
Stable network, small datasets	3600 (1 hour)	500	0 (no overlap)
Unstable network	1800 (30 min)	2000	0 (no overlap)
Large historical retrievals	7200 (2 hours)	1000	0 (no overlap)
Real-time with occasional gaps	900 (15 min)	200	15000 (15 sec)

Item Settings

Each item in the South connector can be individually configured with specific query parameters and datetime handling. Multiple queries to the same database can be executed sequentially within a single connector, with results consolidated into output files for North connectors.

Query Configuration

The query field accepts standard SQL syntax with support for internal variables that enhance data retrieval resilience and performance optimization.

Query Variables

Variable	Description	Behavior
@StartTime	Initialized to first execution time, then updated to the most recent timestamp from reference field	Automatically advances based on retrieved data
@EndTime	Set to current time (now()) or sub-interval end when queries are split	Defines upper bound for data retrieval

Example Query:

SELECT device_id, value, reading_time
FROM sensor_data
WHERE reading_time > @StartTime
AND reading_time < @EndTime
ORDER BY reading_time

Query Splitting

For handling large datasets, queries can be automatically divided into smaller time-based chunks. See Splitting Large Queries for configuration details.

Datetime Field Configuration

Setting	Description	Applies To	Example Value
Field name	Name of the datetime field in your SELECT statement	All types	timestamp, reading_time
Reference field	Designates which field determines the @StartTime for subsequent queries	All types	reading_time
Type	Data type of the datetime field in database	All fields	String, Date, DateTime
Timezone	Timezone of the stored datetime (required for string/date types)	String/Date/DateTime/SmallDateTime	UTC, Europe/Paris
Format	Format pattern for string-based datetimes	String type only	yyyy-MM-dd HH:mm:ss
Locale	Locale for format elements (e.g., month names)	String type only	en_US, fr_FR

Type Consistency Requirement

When using datetime conversions in queries:

Problematic approach (may cause unexpected behavior):

SELECT value, CONVERT(datetime, string_field) AS timestamp
FROM table
WHERE string_field > @StartTime

Recommended approach (maintains type consistency):

SELECT value, CONVERT(datetime, string_field) AS timestamp
FROM table
WHERE CONVERT(datetime, string_field) > @StartTime

Data Flow Process

1. OIBus executes each configured query sequentially
2. Results are consolidated into structured output
3. Max Instant is retrieved from the datetime field used as reference, converted to UTC format and stored to be used as the next @StartTime
4. Final output is formatted according to serialization settings
5. Processed data is sent to configured North connectors

Best Practices

1. Choose reference fields with consistent, increasing values
2. For string datetimes, ensure format matches between database storage and query variables
3. Add indexes on datetime fields for better performance
4. Use query splitting for large time ranges

CSV Serialization

OIBus provides flexible options for serializing retrieved data into CSV format with customizable output settings.

File Configuration

Setting	Description	Example Value
Filename	Name pattern for output files.	data_@ConnectorName_@CurrentDate.csv
Delimiter	Character used to separate values in the CSV file	, or ;
Compression	Enable gzip compression for output files	Enabled/Disabled

Filename Variables

The following variables can be used in filename patterns:

• @ConnectorName: Automatically replaced with the connector's name
• @CurrentDate: Inserts current timestamp in fixed yyyy_MM_dd_HH_mm_ss_SSS format

Temporal Data Handling

Setting	Description	Example Value
Output datetime format	Format pattern for datetime fields in the CSV (does not affect @CurrentDate in filenames)	yyyy-MM-dd HH:mm:ss
Output timezone	Timezone used for datetime values in the CSV	UTC or Europe/Paris

Important Notes

1. The @CurrentDate variable in filenames uses a fixed format (yyyy_MM_dd_HH_mm_ss_SSS) regardless of the datetime format setting
2. Only datetime fields specified in your configuration will be formatted according to these settings
3. Timezone conversion only applies to datetime values in the CSV content, not to the filename timestamp