Understanding Pervasive PSQL Architecture

Client-Server vs. Workgroup Engine

Pervasive PSQL supports both a Workgroup Engine (PWE) for peer-to-peer setups and a Client-Server Engine (PVSW) for centralized environments. These modes behave differently in terms of locking, caching, and network protocols. Misalignment can lead to connection instability and unexpected record locks.

Data Access Methods

The engine supports both Btrieve API access (low-level record I/O) and SQL interfaces. Conflicts can arise when applications mix both inappropriately, leading to unexpected locking or stale reads due to separate transaction contexts.

Common Issues in Enterprise Deployments

1. Record Lock Contention

Occurs when multiple clients access the same table using both Btrieve and SQL methods. The engine may mismanage lock escalation due to mismatched isolation levels or transaction overlap.

2. Engine Fails to Start as a Service

Common after Windows updates or registry corruption. The service fails with error 1717 or remains stuck in "starting" state. This is usually due to incorrect permissions or missing registry keys.

3. Data Corruption After Virtualization

Pervasive PSQL is sensitive to disk caching and snapshot-based backup systems. When I/O caching is enabled on virtual disks, writes may be lost during snapshots, causing corruption or mismatched index pages.

4. SQL Connection Timeouts

Happens in mixed-mode deployments or environments with unstable DNS resolution. ODBC clients may time out or hang if the database name is not properly resolved or if server caching conflicts occur.

5. Licensing or Activation Failures

Activation errors due to corrupted license keys, missing components, or virtual MAC address changes. Errors include "license exceeded" or "engine not licensed for this platform".

Diagnostics and Debugging Techniques

Review PVSW Log Files

Check PVSW.LOG and MKDE.LOG in the C:\PVSW\Logs directory for engine-level errors and crash info.

notepad C:\PVSW\Logs\MKDE.LOG
# Look for error codes like 94 (lock), 3012 (no connection), 161 (license)

Inspect Btrieve Error Codes

Use the Butility tool or application logs to identify specific Btrieve error codes:

butility -stat mytable.btr
// Error 46 = access denied
// Error 85 = record locked

Service Debugging with Event Viewer

Open eventvwr.msc and filter by PSQL source to find service initialization failures.

Network Analysis

Run netstat -ano to confirm port 1583 (Pervasive SQL) is open. Use telnet to confirm connectivity from clients.

License Activation Logs

Check ACTIVATION.LOG in the installation directory for details on key rejection or hardware mismatch.

Step-by-Step Fixes

1. Resolve Locking Issues

• Avoid mixing SQL and Btrieve APIs on the same dataset in the same transaction context
• Set proper isolation levels (READ COMMITTED or REPEATABLE READ) in SQL queries
• Use BMon utility to monitor locks and release hung sessions

2. Fix Engine Service Startup

• Ensure pvsw service has "Log on as a service" permission
• Restore missing registry keys from backup or reinstall engine
• Unblock EXEs if recently downloaded (check file properties)

3. Prevent Data Corruption in VMs

• Disable disk write caching on virtual disks used by PSQL
• Never snapshot while engine is writing—quiesce first or shut down the service
• Use native PSQL backup tools rather than file system-level backups

4. Solve Connection Timeouts

• Use IP addresses instead of hostnames in DSN
• Clear PSQL client cache (use client.cfg and clear cache option)
• Ensure consistent DNS resolution between client and server

5. Fix Licensing Problems

• Use License Administrator tool to re-activate keys
• Contact Actian support if VM MAC address changed (some licenses are MAC-bound)
• Use volume license for containerized deployments or frequent rebuilds

Best Practices

• Use stable hardware UUIDs for VM deployments to avoid license issues
• Separate Btrieve and SQL workloads if both interfaces are used
• Schedule safe backup windows with write activity paused
• Update to latest Zen versions which improve SQL performance and support modern OS
• Enable detailed logging during incidents for future RCA

Conclusion

Pervasive PSQL remains a cornerstone in many mission-critical legacy systems, but its operational intricacies demand careful consideration—especially in modern infrastructure contexts. From handling locking mechanisms and virtualized disk behavior to resolving obscure licensing issues, successful deployment requires in-depth platform understanding and proactive configuration. By following structured diagnostics and embracing architectural best practices, teams can ensure long-term stability and performance of their PSQL-backed applications.

FAQs

1. Can PSQL run reliably in virtual machines?

Yes, but disk write caching and snapshot-based backups must be carefully managed to avoid corruption.

2. How do I monitor active locks?

Use the BMon tool provided by Actian to inspect and release active record locks.

3. Why does the engine service fail after Windows updates?

Updates can reset service permissions or corrupt registry keys. Reassign "Log on as a service" rights and reinstall if needed.

4. Can I automate PSQL backups?

Yes, use the DBCopy and Backup Agent utilities which are designed to avoid locking issues during backup.

5. What causes error 161 in PSQL?

Error 161 indicates a licensing issue—often due to MAC address change or corrupted activation data.