Improving Performance by Offloading Query Execution from a Zen Server
Big report queries can impose a large load on a Zen server, competing for resources with the normal stream of smaller transactions. To reduce the load for report execution, you can set up Zen Client Reporting Engine as a proxy server. This specialized 64-bit engine combines features of Zen Relational Engine and Zen Client Cache engine to enable offloading of SQL reporting jobs to lessen their impact on server activity. When big report queries run on the reporting engine instead of on the server, server performance can rise for normal production operations.
A Client Reporting Engine is configured with the name of a Zen server as its “storage server,” where the master copy of all data resides. Once configured, the reporting engine presents to SQL clients all of the same databases as the master storage server, so that all SQL queries can then be submitted to the reporting engine. Use of reporting engine requires you to make changes in your design so that queries and other related requests are sent to this client instead of to the server.
Zen Client Reporting Engine has Windows performance counters to track cache usage. Zen Enterprise Server and Cloud Server now have Windows performance counters that distinguish between direct application use and use by remote cache engines, such as the reporting engine. In addition, the counter for cache-hit-ratio has been changed to reflect recent activity instead of cumulative.
Note Although most database operations could successfully execute on this specialized engine, it is designed for read-mostly reporting jobs. The Reporting Engine may be used by Btrieve applications as an always-on Client Cache Engine.
The following topics cover server and client properties used in configuration:
Zen Client Reporting Engine has two additional server properties:
•Storage Server – Name of the Zen server installation that the Client Reporting Engine uses as the master data copy. This installation can be either a Windows or Linux personal computer or server.
•TEMPDB Directory – Location on the Client Reporting Engine to use as a temporary local database. The default path is C:\ProgramData\Actian\Zen\tempdb, which on most systems is satisfactory.
These two settings are not used by other Zen editions.
To begin using Client Reporting Engine, you need to set only the storage server name. Storage server refers to the permanent location for data files, as opposed to data cached locally on this client. Note that references to TEMPDB tables are processed locally on the reporting engine.
The rest of the server properties can be used with their default values or are unneeded and have been removed. Server properties are documented under Server Configuration Properties on All Platforms in Advanced Operations Guide.
►To set the storage server
1 In ZenCC, expand the Engines node
2 Right-click the name of the Client Reporting Engine and select Properties.
3 In the Properties window, click Access.
4 In the Storage Server field, enter the name or IP address of the Zen server to be supported. The value is not case-sensitive.
5 Click Apply.
6 When ZenCC displays a message that the engine must be restarted, click OK.
7 Click OK to close the Properties window.
8 Expand the Services node, right-click Zen Reporting Engine, and select Restart Service.
9 To confirm that the Client Reporting Engine is connected and working properly, expand its Engines node. Under the reporting engine name, you should see the list of storage server databases.
►To change the temporary database directory
1 In ZenCC, expand the Engines node.
2 Right-click the name of the Client Reporting Engine and select Properties.
3 In the Properties window, click Directories.
4 In the TEMPDB Directory field, enter a new path. The location must exist on the system where the Client Reporting Engine is running.
5 Click Apply.
6 When ZenCC displays a message that the engine must be restarted, click OK.
7 Click OK to close the Properties window.
8 Expand the Services node, right-click Zen Reporting Engine, and select Restart Service.
Client Properties for Client Reporting Engine
Zen Client Reporting Engine has a subset of the typical client properties. The following groups of settings are not displayed because they are available under the server settings or are not needed by the Client Reporting Engine:
•Cache Engine. The cache size can be changed in the Performance group of the server properties for Client Reporting Engine.
•Cache Engine Debugging. The trace and debug settings can be changed in the Debugging group of the server properties for Client Reporting Engine.
•Performance Tuning. The Use Cache Engine setting is always on, so this setting and group are not configurable.
In general, the default values for the rest of the client properties enable a Client Reporting Engine to function smoothly. Client properties are documented under Windows Client Configuration Properties in Advanced Operations Guide
Database Properties for Client Reporting Engine
Do not attempt to change the settings for Zen internal databases, such as DefaultDB or TempDB. In most cases, ZenCC issues messages and warnings that database properties cannot be modified on a Client Reporting Engine.
To manage database properties, use ZenCC or bcfg on the storage server. In ZenCC, the settings are listed in the Client Reporting Engine server Properties window under Access.
Client Reporting Engine Limitations
Because of the customized features of the Client Reporting Engine and the way it serves as a proxy engine for a Zen server, it has the following SQL query limitations:
•You cannot use CREATE DATABASE, DROP DATABASE, or MODIFY DATABASE.
•You cannot modify database paths or code pages.
•In the properties for a database, the Security settings group is disabled, and any attempt to use SET SECURITY returns an error.
•You cannot create a table using an alternate collating sequence file.
•You cannot reference an alternate collating sequence file in a COLLATE clause.
•You cannot create a table where the path specified in the USING clause is a relative path or does not already exist. Attempting to do so results in a message that the file name is invalid.
•You cannot create a view or a stored procedure in a secure, V2 database. The Zen engine on the storage server must set the permissions for the new object during this operation, which cannot be done from the Client Reporting Engine. Attempts to do so return the message that an error occurred while checking or setting security.
The workaround for these limitations is to run SQL queries locally on the storage server or remotely from the Client Reporting Engine by connecting to the storage server through ZenCC, bcfg, or other tool.
In addition, if you are running multiple applications on an individual Client Reporting Engine, then to insure maximum cache synchronization, as a best practice we recommend that all of your SQL applications connect through that Client Reporting Engine. The only exception is for specific operations that require a direct connection to the storage server.
DDF Builder is disabled in Client Reporting Engine installations. If you try to use DDF Builder to access a database, it displays a message that the database cannot be modified.
A Zen server on Windows IoT Core and Nano Server and on Raspbian cannot be used as a storage server.
Using the Client Reporting Engine as a Btrieve Cache Engine
The Client Reporting Engine functions as a Client Cache Engine when it opens files on the storage server or on another remote Zen database engine.
The Btrieve interface cannot open files local to the Client Reporting Engine. Attempts to do so fail and may require the Btrieve session to be reset.
All administrative concerns that apply to running Client Cache Engine as a service also apply to running Client Reporting Engine. For example, it may be necessary to change the account under which the service runs or to configure pvnetpass to use a Btrieve application with the Client Reporting Engine. These concerns do not apply to applications that report using only SQL.
Using the Client Reporting Engine with the DTI API
The following DTI calls, as well as their DTO equivalents, fail when used locally on a Client Reporting Engine: