Advanced Troubleshooting in Pervasive PSQL for Legacy Database Systems

Details: Category: Databases; By Mindful Chase; 31.Mar; Hits: 6

Pervasive PSQL, now known as Actian PSQL, is a high-performance, zero-maintenance embedded database engine widely used in legacy enterprise applications. While it provides reliable data management and low administrative overhead, developers and IT teams often encounter obscure issues when scaling, integrating, or maintaining Pervasive PSQL in modern environments. Common challenges include Btrieve API conflicts, ODBC driver mismatches, data file corruption, licensing restrictions, and indexing problems. This article provides deep troubleshooting strategies to resolve critical issues in Pervasive PSQL environments and ensure long-term system stability.

Mindful Chase

Writing Code, Writing Stories

tbd

Experience

tbd

More to Explore

Understanding Pervasive PSQL Architecture

Dual Engine Structure: Btrieve and SQL

Pervasive PSQL supports both Btrieve (record-level) and SQL access. Applications may use either interface or both concurrently, leading to file-locking conflicts and access synchronization problems if not properly managed.

File-Based Engine with ISAM Indexing

Pervasive operates on .DDF and .DAT files stored directly on disk, using ISAM-style indexes. Performance and integrity issues can arise from improper caching, index corruption, or disk-level inconsistencies.

Common Pervasive PSQL Issues

1. Btrieve Status Code 94: Permission Denied

This error is commonly caused by mismatched access permissions or incorrect record locking configuration. It often occurs in multi-user environments or during concurrent SQL/Btrieve access.

2. ODBC Connectivity Failures

Applications using legacy ODBC DSNs may fail to connect due to driver incompatibilities, 32-bit vs 64-bit mismatches, or missing PSQL components in the system PATH.

3. DDF File Corruption or Schema Mismatch

Corrupted or inconsistent DDF files prevent SQL access and cause errors like "Table not found" or invalid column types. This often occurs when DDFs are manually edited or copied without matching .DAT files.

4. Index File Damage (MKD/MKD Index Errors)

Improper shutdowns, file system issues, or hardware failure may cause index corruption, resulting in application hangs, missing records, or invalid read errors.

5. Licensing Errors and Engine Startup Failure

Pervasive engines require a valid license for activation. Expired or missing keys prevent startup and return errors like "Authorization key not found" or "Engine not licensed for remote access."

Diagnostics and Debugging Techniques

Check Btrieve Status Codes

Use the PSQL Control Center to monitor active engines and logged errors.
Refer to the Actian documentation for Btrieve status code definitions.

ODBC Connection Tracing

Enable ODBC trace logs via the ODBC Administrator.
Ensure proper architecture match (32-bit vs 64-bit) for DSNs and client apps.

Validate File Integrity

Use the BUTIL utility to check and recover damaged files.
Run BUTIL -STAT and BUTIL -RECOVER on affected tables.

Confirm License and Configuration

Check license status with License Administrator or pslic command-line tool.
Verify that w3dbsmgr.exe and psqlsvr.exe are running properly.

Step-by-Step Fixes

1. Resolving Status Code 94

Ensure full read/write permissions for database directories.
Avoid simultaneous SQL and Btrieve updates on the same table without proper locking.

2. Repairing ODBC Connections

Install matching PSQL ODBC drivers and configure DSNs in the correct ODBC Administrator (32-bit or 64-bit).
Set AutoReconnect=Yes in DSN settings to recover dropped sessions.

3. Fixing Corrupt DDFs

Use DDF Builder to rebuild or repair damaged dictionary files.
Ensure schema definitions match the structure of .DAT files.

4. Rebuilding Damaged Indexes

BUTIL -RECOVER mytable.dat newtable.dat
copy newtable.dat mytable.dat

5. Addressing Licensing Errors

Activate or renew license using pslic -i licensekey.
Verify that engine is allowed to run in multi-user or remote environments based on the license type.

Best Practices

Schedule regular backups and run BUTIL -SAVE to verify file consistency.
Use transaction logging to prevent data loss during power outages.
Centralize DDF files and version them alongside application code.
Avoid mixing direct Btrieve access with SQL operations without record locking strategies.
Monitor logs for silent errors using PSQL Monitor and System Viewer.

Conclusion

Pervasive PSQL remains a stable engine for embedded applications, but scaling and maintaining it in modern infrastructures requires deep system awareness. By understanding its hybrid architecture, proactively managing file integrity, and resolving driver and license issues, IT teams can maintain reliable operations even in legacy environments. Modern wrappers or migration strategies should also be explored for long-term viability.

FAQs

1. What causes Status Code 94 in Btrieve?

It indicates a permission issue or record locking conflict. Review file access rights and synchronization between SQL and Btrieve interfaces.

2. How do I fix "Table not found" errors in PSQL SQL?

Check DDF files for corruption or mismatch with .DAT files. Use the DDF Builder to validate and rebuild schema mappings.

3. Is it safe to access the same data file via SQL and Btrieve?

Yes, but it requires strict record locking and transaction boundaries to avoid corruption or access conflicts.

4. Can I run Pervasive PSQL on Windows 11?

Older versions may require compatibility settings or manual driver updates. Use the latest Actian-certified version for full support.

5. How do I rebuild corrupted index files?

Use BUTIL -RECOVER or export the data with BUTIL -SAVE and import into a new file structure to restore stability.

Contact Us