Setup of the PSQL 13 Report Engine

Release Date: 8/14/2019
Version: PSQL 13 & up

Benefits of PSQL 13 Report Engine

The most significant feature in PSQL 13 is the report engine feature.  Before PSQL 13, the Elliott application, web services and other third party applications that needed to access Elliott data had to go through the PSQL Database Engine (DE).  If you are a large organization with hundreds of Elliott users, you may find the PSQL Database Engine busy most of the time.  This is especially true if your users create their own Crystal Reports and perform their own joining of tables. When joining of tables is not done correctly, it often results in poor performance and drags down Elliott's performance.

When faced with the PSQL DE server performance problem, many users chose to scale up their DE server.  However, we noticed that the DE server performance does not improve much after 6-8 CPU. In PSQL 13, you now have the option to scale out.  That is, you can install additional PSQL Report Engine (RE) servers to offload the burden of PSQL DE.

The name "Report Engine" suggests you can use RE for applications like Crystal Reports or Excel Query, which only read Elliott's data and do not perform updates. But the RE does support most of the relational operations including "SELECT," "UPDATE," "INSERT" or "DELETE." In relational operations you can treat it just like DE (even though the manufacturer suggests that you use RE for retrieving data only.) Generally speaking, operations that may affect database schema are not supported. For example, you can't use a relational operation to create a new database.

Hardware Requirements

Typically, we suggest the following hardware requirements for the server to host the PSQL Report Engine:
  • CPU: 4-8 CPU. 
  • RAM Memory: It should have enough memory to fully cache your Elliott database.  Just calculate the total size of the *.BTR files in your corresponding Elliott DATA folder, and use that as a basis to determine the RE server memory size. If you choose to virtualize your RE, then you can easily expand the memory size if necessary -- which we highly recommend that you do.  
  • Disk space: No physical Elliott data will reside on the RE server. We recommend that you create a C: partition that's big enough for the Windows OS and future server SP updates.  Typically, 200-300GB for the C: partition will be enough.

Installation

Installation of the PSQL Report Engine is easy. Just follow the default suggestions of the installation wizard to complete the installation.

Setup

After the installation of RE, please bring up PSQL 13 Control Center. Expand the "Engines" node, right click on your RE server name, and choose Properties. See sample screen below:


Enter Name of Storage Server

In the Properties window, go to "Access" and enter the name of the Storage Server.  This is the server where you install the PSQL 13 database engine (DE).  Then click on Apply to update. See sample screen below:


Configure Cache Allocation 

Right click on the node of your RE server on the left hand pane of the PSQL 13 Control Center, and choose Properties.  In the Properties Window, go to "Performance turning" and enter the Cached Allocation Size in MB.  We suggest that this should be anywhere between 50 - 80 percent of your RE server memory.  The example below is 32GB = 32 * 1024MB = 32,768 MB.


Make sure you do not over or under allocate cache memory.  Under allocation may result in less than desired performance.  Over allocation may cause the server to run out of memory, which can make the server run even slower if Windows OS virtual memory disk swapping starts taking place.  Monitor in Task Manager and make sure the actual server physical memory usage does not exceed 90%.


EMK

Pervasive PSQL

  1. Btrieve Error Codes 001 - 199
  2. Btrieve Error Codes 3000 - 3099
  3. Btrieve Error Codes 3100 - 3199
  4. PSQL Version Required by Each Elliott Version
  5. Do I Need to Change PSQL Server Engine Default Parameters After Installing It?
  6. New Elliott PSQL Server Processor and RAM Suggestions
  7. Can I Dynamically Adjust Elliott / PSQL 11 Server Memory?
  8. Received "Your Computer Does Not Have PSQL 10 or 11 Client " Even though PSQL Client Is Just Installed
  9. Btrieve Error 161 on Password File When Starting Up Elliott
  10. Problems with Using Pervasive Rebuild Utility on APOPNFIL and AROPNFIL Tables
  11. Security Issue with Installing PSQL Client Remotely on User's Workstation
  12. PSQL and Distributed File System (DFS)
  13. How Do I Turn on PSQL Relational Engine Security?
  14. An Example of Debugging NOTE_ORD_VIEW PSQL Expression Evaluation Error
  15. Btrieve Error 025 on COP Open Order by Salesman Report
  16. What Is the *.^01 File for My PSQL Btrieve Table?
  17. Suggested Files to be Monitored by Audit Master
  18. Pervasive Backup Agent Is Not Compatible with Creating Work Files
  19. Hardware Recommendations for Your PSQL Database Server
  20. How to Optimize SQL SELECT Statement When Retrieving Data from Invoice History
  21. New User-Defined Functions in Elliott DDF
  22. How to Improve Query Performance When Retrieving Data from Notes & Invoice History
  23. How to Retrieve Tracking Number for an Order from Notes
  24. Actian PSQL Not Started Automatically After Server Reboot
  25. Create a New Database in the PCC for Relational Engine Access
  26. Slow PSQL Relational Engine Performance
  27. IPV6 May Cause Problem for PSQL 11 Relational Query
  28. DDF Files in DATA Folder May Confuse PSQL
  29. What to Do When PSQL 11 License Is Disabled
  30. Quick Installation Guide for Audit Master
  31. Quick User Guide for Audit Master
  32. PSQL 13, Micrsoft SQL Integration Service & Pervasive PSQL OLE DB Provider
  33. Your Firewall Needs to Allow Outbound Traffic to the Netherlands for PSQL Licensing Server Purposes
  34. A Case of Btrieve Error 046 on ARCRCLOG A/R Credit Card Log File
  35. A Support Case of Migrating to Different Version of DDF
  36. How to Clear the Message "Unable to Read your Users record (9/068)"
  37. Setup of the PSQL 13 Report Engine
  38. How to Create CPHSTTRX_VIEW with Left Join to CPINVHDR Due to Invoice Database Archive
  39. How to Access Elliott's Data by Using Query in Microsoft Office Excel 2019
  40. Elliott Database Naming Convention
  41. What Does Btrieve Error 080 Mean?

Feedback and Knowledge Base