Advanced Troubleshooting in Pervasive PSQL for Embedded Database Systems

Details: Category: Databases; By Mindful Chase; 02.Apr; Hits: 4

Pervasive PSQL, now Actian PSQL, is a high-performance embedded database engine widely used in legacy and vertical market applications. Its lightweight footprint and ISAM-based architecture make it ideal for applications requiring fast transactional processing. However, in modern enterprise environments, administrators and developers often encounter difficult-to-diagnose issues such as Btrieve status code errors, licensing conflicts, record locking, stale cache problems, and 32-bit vs 64-bit client mismatches. This article provides deep technical insights into diagnosing and resolving these Pervasive PSQL challenges in production systems.

Mindful Chase

Writing Code, Writing Stories

tbd

Experience

tbd

More to Explore

Understanding Pervasive PSQL Architecture

Engine Modes and Interfaces

Pervasive PSQL supports both MicroKernel Engine (MKE) for transactional access and SQL Engine for relational access. Applications may use Btrieve API, ODBC, JDBC, or ADO.NET interfaces depending on compatibility and performance requirements.

Data Access and File-Based Storage

Data is stored in .DDF and .MKD/.BTR files on disk. Record-level access through the Btrieve API is efficient but susceptible to file corruption, stale handles, and locking conflicts, especially in multi-user deployments.

Common Pervasive PSQL Issues

1. Btrieve Status Code Errors (e.g., 94, 85, 20)

Status codes indicate operational issues such as file locks (94), key not found (4), or licensing violations (161). Improper error handling or failure to close handles causes cascading failures in client applications.

2. Record Locking and Deadlocks

Applications that open files without proper lock management may encounter write conflicts or deadlocks, particularly in ISAM-style operations. Misconfigured concurrent access settings in the engine exacerbate this.

3. Client/Server Architecture Conflicts

Inconsistent versions between client drivers (32-bit vs 64-bit), middleware, and the PSQL server can lead to failed connections or mismatched data type errors during queries.

4. Data Corruption and Stale Cache

Improper shutdowns or aggressive caching can corrupt .MKD or index files. Cached reads may serve stale data if cache invalidation fails. This results in missing or ghost records.

5. Licensing and Activation Failures

License count overruns, expired trial versions, or moved installations without proper deactivation result in status 161 or 7229 errors. These block server startup or client access.

Diagnostics and Debugging Techniques

Enable PSQL Monitor and Logging

Use PSQL Monitor (PVSWMON) to trace Btrieve operations and status codes.
Enable SQL and MKDE logging via the PSQL Control Center to trace low-level file and client activity.

Inspect Status Codes with Reference Guide

Cross-reference error codes with Actian's documentation to understand root causes.
Code 94 = permission/lock issue; 85 = file in use; 161 = license exceeded.

Validate File Handles and Access Paths

Use PSQL File Manager to identify open file handles and validate path consistency across sessions.
Ensure short and long filename mismatches are not confusing the engine.

Test with Both SQL and Btrieve APIs

Isolate issues by running queries through ODBC or JDBC clients and comparing behavior with Btrieve-based tools.
Use DBNavigator or Btrieve Commander to test file-level access outside your application stack.

Check Version and Platform Compatibility

Ensure all applications use matching PSQL client versions (32-bit vs 64-bit).
Check engine version compatibility with file format via dbstat or dbmaint.

Step-by-Step Fixes

1. Fix Btrieve Status Errors

For status 94, verify file access rights and use non-conflicting open modes.
For status 161, free up licenses or re-activate with a valid key using License Administrator.

2. Resolve Locking Conflicts

Set client isolation levels and explicit lock modes in transactional code.
Reconfigure engine settings to limit stale lock retention in high-concurrency scenarios.

3. Address Version and Bitness Mismatches

Use the correct ODBC driver (32-bit for 32-bit apps, 64-bit for 64-bit apps).
Deploy matching client SDK and ensure DSN settings reflect correct driver paths.

4. Repair Corrupted Files

Use dbfix or dbrepair utilities to scan and fix corrupt index/data segments.
Enable forced-close of lingering handles and restart the engine if necessary.

5. Troubleshoot Licensing Issues

Run pslicense to validate current license status and expiration.
Deactivate on old hosts before migration to prevent activation lockouts.

Best Practices

Implement proper file open/close logic and always release locks post-operation.
Use transactional APIs over direct file access for critical systems.
Separate read/write operations using access rights or queues to avoid contention.
Regularly back up .DDF and .MKD files and verify integrity using validation tools.
Monitor license usage via Control Center and configure alerts for limits.

Conclusion

Pervasive PSQL remains a robust embedded database choice, but its legacy architecture and file-based model require disciplined deployment and management practices. Understanding the interplay between Btrieve operations, SQL access, and native drivers is crucial to diagnosing complex issues. With structured logging, consistent versioning, and clear locking strategies, teams can maintain stable and performant PSQL environments in modern enterprise systems.

FAQs

1. What does Btrieve status 94 mean?

Status 94 typically means a permission or lock violation. Ensure that no conflicting open modes are used and that file access is not shared improperly.

2. Why is my app failing after moving the PSQL server?

Licensing must be deactivated on the old host and reactivated on the new one. Check for status 161 errors indicating licensing problems.

3. How can I resolve record locking conflicts?

Use transaction blocks with clear commit/rollback logic and configure the engine's lock timeout settings to avoid deadlocks.

4. What tools help repair PSQL file corruption?

Use dbrepair or dbfix to check and repair corrupt .MKD or .BTR files. Always back up before running repairs.

5. Can I run both 32-bit and 64-bit apps on the same PSQL engine?

Yes, but each app must use the corresponding ODBC/JDBC driver version. Mismatched bitness can cause connection or query errors.

Contact Us