Understanding Pervasive PSQL Architecture

Microkernel vs. Relational Access

Pervasive PSQL supports two access engines: the Btrieve API (microkernel engine) and SQL engine. Understanding which engine is used by your application is crucial, as troubleshooting techniques differ dramatically between them.

File-Based Storage

Unlike server-based RDBMS systems, PSQL databases are file-based with a transaction log model. Files are accessed concurrently, so file system permissions and network latency directly impact performance and stability.

Common Troubleshooting Scenarios

1. "161: Record Locked" Errors

This occurs when a record is accessed by another process and not released in a timely fashion. It's common in shared folder setups or after application crashes.

Fix

  • Use Monitor Utility to identify the locking user/session.
  • Enable Requestor Information to trace workstation IP and user ID.
  • Configure opportunistic locking and retry logic in the application layer.

2. Unexpected File Corruption

Files may become corrupted due to power loss, improper backups, or anti-virus interference.

Fix

  • Use DBRecover or Maintenance Utility to scan and rebuild damaged files.
  • Exclude .MKD and .DAT files from antivirus scans.
  • Verify all clients are using mapped drives and not UNC paths.

3. Slow SQL Query Performance

Even small tables can have slow queries due to missing indexes or suboptimal WHERE clauses.

Fix

  • Use Query Plan Viewer to analyze execution plans.
  • Add indexes using CREATE INDEX or via Control Center.
  • Avoid cross-engine access (e.g., joining Btrieve-only tables via SQL).

4. Connectivity Issues in Virtualized Environments

Citrix or Remote Desktop deployments often experience timeout or data access issues due to drive mapping inconsistencies or session persistence errors.

Fix

  • Map drives using logon scripts instead of manual mappings.
  • Disable opportunistic locking via registry for better concurrency.
  • Ensure firewall exceptions exist for W3DBSMGR.EXE and relevant ports (usually 1583).

5. License or Engine Startup Failures

The PSQL engine may fail to start if licenses are invalid, the service is misconfigured, or logs are corrupted.

Fix

  • Check PVSW.LOG and PVSW.INI for startup diagnostics.
  • Reapply license via Actian License Administrator.
  • Restart W3DBSMGR service and ensure it is set to automatic.

Advanced Diagnostics

Using the Pervasive Monitor

The Monitor utility provides real-time views into active sessions, locks, cache usage, and file handles. Use it to identify blocking sessions or runaway queries.

Enable Trace Logging

Enable detailed logging through PVSW.CFG or the registry to capture Btrieve and SQL engine activity for in-depth analysis.

Cross-Engine Pitfalls

Avoid mixing Btrieve and SQL engine access on the same tables without proper synchronization, as this can lead to phantom reads or stale index views.

Best Practices

  • Use mapped drives with consistent paths across all clients.
  • Exclude database files from antivirus and backup scans.
  • Schedule regular file rebuilds for high-transaction tables.
  • Avoid direct OS-level file copies; use provided export/import tools.
  • Use AuditMaster or Backup Agent for compliant backup and recovery.

Conclusion

While Pervasive PSQL remains robust, its hybrid architecture and legacy design pose unique challenges. Diagnosing issues requires deep understanding of its dual access engines, file-based concurrency model, and reliance on proper environment configuration. Enterprises that actively monitor engine health, isolate locking issues, and follow disciplined deployment practices can keep their PSQL systems running reliably—even decades into their lifecycle.

FAQs

1. Why do I get record lock errors even with few users?

Locks can persist due to abnormal application exits or improper commit logic. Use Monitor to trace sessions holding locks.

2. Can antivirus software corrupt PSQL files?

Yes. Real-time scans can interrupt file access, especially on .MKD/.DAT files. Always add exclusions for database directories.

3. How do I rebuild a corrupt table?

Use DBRecover or Pervasive Rebuild Utility to scan, validate, and regenerate table structures safely.

4. What causes slow SQL queries in PSQL?

Missing indexes, table scans, and poor WHERE clause filtering can degrade performance. Use Query Plan Viewer to tune queries.

5. Why does PSQL not start automatically after reboot?

The database engine may be set to manual start or encountering license/load errors. Check PVSW logs and service configuration.