Pervasive SQL and transaction durability risks.

Solution Details

Solution ID: 00014597
What is Transaction Durability and what is the risk of turning it off?

Problem Description:

Problem Environment:
Pervasive.SQL 2000 Pervasive.SQL v7

Cause of this problem:
Solution Notes:
A Transaction-Durable file is a 6.x file with a unique key or a 7.x file with a unique key or system data defined, and which was not opened in Accelerated mode.

If the Microkernel Database Engine (MKDE) Transaction Durability option is set to YES and the file is Transaction-Durable, then the MKDE returns the status to the application after committing (saving) the entire transaction to the transaction log file. The MKDE does this when it encounters the end of the transaction, such as an End Transaction, or an Abort Transaction operation. That means, non-transaction operations, i.e., Btrieve operations not enclosed within a Begin Transaction and an End Transaction (or an Abort Transaction) operation are not durable.

If the Microkernel Database Engine (MKDE) Transaction Durability option is set to NO and the file is not Transaction-Durable, then the MKDE returns and the status to the application before committing (saving) the entire transaction.

Not durable means the application may receive a successful status for the ransaction or non-transaction operation, but there is a window in which a system failure may cause the operation to be lost because it was not yet written to the data file, although it may still be present in the system cache.

Hence, if the user wants the non-transaction operations to be durable, the operations should be placed within transactions and Transaction Durability set to YES.

จากคำอธิบายข้างต้นจะเห็นว่า ข้อมูลที่ถูก update โดยไม่มีการใส่ BEGIN - END transaction ครอบเอาไว้ อาจเป็นอันตรายข้อมูลหายได้ ถึงแม้ว่าโปรแกรมจะได้รับ status=0 กลับมาจาก database engine ก็ตาม ตามรูปซึ่ง dump มาจาก log file จะเห็นว่า ตรงบรรทัดที่ 2692 เป็นการ update Inv.BTR ที่อยู่ภายในกรอบของ BEGIN - END หลังจากนั้น บรรทัดที่ 2694 มีการสั่ง update Inv.BTR อีกครั้ง ซึ่งตรงกับคำอธิบายว่าเป็น Not durable ตรงจุดนี้เป็นจุดแรกที่เกิดความเสี่ยงข้อมูลหาย

Antivirus may cause Pervasive SQL status 85

Solution Details

Solution ID: 00016378
Intermittent Status 85

Problem Description:
Status code 85: "The File is Locked" is returned to different workstations without any pattern.

Problem Environment:
Pervasive SQL

Cause of this problem:
A Status Code 85 occurs when the file is closed because Anti-Virus software opens and locks the file to scan causing the next database operation to fail.

Solution Notes:
Set the Anti-Virus software to not scan Pervasive SQL data files. Consult your Anti-Virus software manual for instructions on how to exclude files

http://www.pervasive.com/kb/kb.asp?fx=getdetails&solid=00016378&title=Intermittent%20Status%2085

จำได้ว่ากรณีนี้เคยเจอตั้งแต่สมัยที่ computer ช้ามากๆ ทำให้ Antivirus scan ไฟล์ไม่จบ ยิ่งไฟล์ database ใหญ่มากๆ ยิ่งใช้เวลาในการ scan นาน ขณะเดียวกันถ้า application มีการเปิด-ปิดไฟล์บ่อยๆ ก็เป็นการ trigger ให้ตัว antivirus ทำการ scan ไฟล์บ่อย ซึ่งกรณีนี้อาจเกิดขึ้นกับ server ที่บังเอิญมี load เยอะๆ ก็ได้ ...

Delay effect with Pervasive.SQL client cache engine

Solution Details

Solution ID: 00016865
What is the Client Cache engine ?

Problem Description:

Problem Environment:
Pervasive.SQL V8

Cause of this problem:

Solution Notes:
The client cache engine is essentially a MicroKernel database engine that stores recently-used data file pages in local memory on the client workstation. Future reads will access the client cache first prior to sending a request across the network to re-read the records. This can yield significant increases in performance as it reduces the data flowing across the network (network traffic). However, the data that is stored in the client cache is static data, and is refreshed after a maximum of every 5 seconds delay by default.

In some environments, the application behavior cannot tolerate this delay, and the client cache engine should be disabled. Also, in other environments where there are a very large number of clients connected to the server the polling between the client and the server can increase the network traffic and actually reduce performance.

Other factors such as application architecture can make a tremendous difference in the ability of the client cache engine to be used efficiently.
For transactional API applications the client cache engine is bypassed in the following circumstances:

• q Opens / Closes / Resets - are processed both locally and passed through to the server engine
• q Insert / Update / Delete (any write operation) is passed through to the server engine q Anything inside a transaction is passed through to the server engine
• q Any operation with a lock bias is passed through to the server engine The relational API is client / server in nature and all processing is performed at the server. As such the client cache engine is not used for SQL / ODBC access.

For more information on the Client Cache Engine, consult the " What's New..." and the "Advanced Operations Guide" on-line manuals which are available with the Pervasive.SQL V8 documentation.

http://www.pervasive.com/kb/kb.asp?fx=getdetails&solid=00016865&title=What%20is%20the%20Client%20Cache%20engine

จากคำอธิบายเกี่ยวกับ cache engine ของ Pervasive 8 จะเห็นว่า ข้อดีของ cache engine คือ ลดปริมาณ traffic ใน network ลง ทำให้การทำงานเร็วขึ้น แต่ก็จะมีปัญหา delay ของข้อมูล เมื่อคอมพิวเตอร์เครื่องหนึ่ง update ข้อมูลไปแล้ว กว่าที่คอมพิวเตอร์อีกเครื่องหนึ่งจะทำการ refresh cache engine จะต้องรอทุกๆ 5 วินาที แต่เท่าที่ผ่านมา ปัญหานี้ยังไม่ถูกสังเกตพบจนกลายเป็นปัญหาจากผู้ใช้ครับ

Pervasive v10 deprecated features

Netware
NetWare is no longer a supported platform for Pervasive PSQL. If you are running a previous version of Pervasive PSQL for NetWare, consider upgrading to Pervasive PSQL for Windows or for Linux.

Windows NT/98/ME
The following Windows platforms are no longer supported for any of the Pervasive SQL products:

• Windows NTWindows
• 98/ME

DOS 6.22
DOS 6.22 is no longer PSQL products.

16-bit Applications
Support is no longer available for 16-bit products or 16-bit application development with Pervasive PSQL. The 16-bit utilities are no longer available in Pervasive PSQL, nor are the 16-bit client components. The configuration settings for 16-bit clients, such as Use Thunk and so forth, are no longer available.

DOS TSR Programs
The DOS Terminate and Stay Resident (TSR) programs are no
longer supported. This includes the following programs:

• BREQUEST DOS
• requester BREQNT
• DOS requester BREQTCP DOS requester Sockets.exe

Note that the DOS requester btrbox is still supported on 32-bit Windows platforms. DOS applications are not supported on 64-bit Windows platforms; therefore, btrbox is not supported on 64-bit Windows platforms.

เป็นอันว่า ทั้ง Netware Server, Windows 9x/Me/NT นั้นถูกทิ้งเรียบร้อย แต่ดูเหมือนว่า โปรแกรม SCRAFT ของเรายังคงใช้ BtrBox สำหรับ version 32-bit ได้ สำหรับ PSQL10 คงต้องรอทดสอบกันต่อไป

Pervasive SQL & DOS application on Vista solution

Recently test my DOS accounting program with Vista.

Problem when running with old Btrieve.EXE :
It can run smoothly except the program reports e memory free is 38x KB, not enough to call the subprocess.

Problem when runnig with Pervasive SQL 8.x :
The program show error status code 20 (the database engine not install) .

now I got a good news from Pervasive.

http://www.pervasive.com/kb/kb.asp?fx=getdetails&solid=00017909&title=DOS%20application%20returns%20a%20Status%2020%20on%20Windows%20Vista

Solution Details
Solution ID: 00017909

DOS application returns a Status 20 on Windows Vista
Problem Description:
DOS application returns a Status 20.


Problem Environment:
Pervasive PSQL v9
Windows Vista


Cause of this problem:
With Windows Vista, Pervasive Software is no longer able to support applications that require the use of a T.S.R. (Terminate Stay Ready) Requester, this includes such Btrieve Requesters as Brequest, Breqtcp, Breqnt, etc. Windows Vista users can use the Pervasive provided Win32 DOS Box Support, which is a VXD (Virtual eXtended Driver) that comes with Pervasive PSQL v9. This VXD gets loaded as part of the operating system boot-up, however, since Windows Vista limits the location where applications can place files, users are required to perform additional steps to make this program execute.


Solution Notes:
Configuring BTRBOX to work with Windows Vista:

1. Copy the following file (from the \pvsw\bin directory) into the \windows\system32\ directory:
BTRVDD.DLL