Change Log - Synergex/SqlReplication GitHub Wiki

15th July 2023

  • We updated several of the command procedures used in the OpenVMS environment.
  • We removed the Workbench development environment.
  • We updated the format and content of some documentation.

4th December 2020

  • We implemented a new code generated routine for each ISAM structure that returns the key number of the first unique key. This allows us to avoid writing code to detect the key number of the first unique key at runtime, which requires that the replicator has a channel open to each data file being replicated.
  • We implemented a new code generated routine for each ISAM structure that, when passed a record, returns the key value of the first unique key. This allows us to avoid using %KEYVAL, which requires that the replicator has a channel open to each data file being replicated.
  • Because of the previous two changes, we were able to remove the requirement for the replicator to open the underlying data files that are being replicated. The only files that the replicator now opens are the instruction queue file and log file.
  • We added code to make replicator more resilient to interruptions to network connections when using xfServer to access the instruction queue file on a remote system. If a network problem is detected, the replicator will now close the instruction queue and attempt to re-open it. It will retry this operation several times, with a delay between attempts. The number of retries and the delay between retries are both configurable via command-line options or environment variables.

30th January 2020

Following a customer visit this week we have made several enhancements to the environment.

  • We no longer code generated the GetReplicatedTables routine, because tables must now be assigned to a named replicator instance. This file must now be manually maintained as new tables are added to the environment.
  • We added a new library routine called GetInstances that returns a collection of the valid names of replicator instances. This file must be manually maintained as any additional instances are introduced.
  • We corrected a recently introduced issue in the IsDecimal validation routine.
  • We improved the capabilities and validation in the MakeTimeForCsv routine.
  • we connected an issue in the CycleLog routine.
  • We modified the bulk load mechanism to display more detailed status information when running attached to a terminal.
  • We modified the bulk load mechanism to not delete export and error files after the completion of an operation. This makes it much easier to debug bulk load operations. The files are still deleted before the next bulk load operation for the same table begins.
  • We added the ability to bulk load only the first 1000 records in any given table. This is useful for testing purposes.
  • We made several improvements to the ReplicatorMenu utility program.

The sample environment has also now been configured to require Synergy/DE V11. If for any reason you can't meet this requirement then you can revert to building for Synergy/DE V10 by:

  • Editing the project properties of the Visual Studio projects "library", "replicator" and "tools" and targeting 1 Synergy V10 runtime.
  • Editing the regen.bat batch file and commenting out the "set DBLV11=-define DBLV11" line towards the top of the file.

16th January 2020

In recent weeks we have been working on making some improvements to our reference implementation that demonstrates how to easily replicate Synergy data to a SQL Server database. Some of these changes were done in collaboration with a customer that uses the environment extensively, and we thank them for their input and support. Other changes have been on our road-map for some time, and we think you'll be excited about what has been achieved. Here's a summary of the changes:

  • Synergy 11 introduced a new SQL Connection API capability which calls the ODBC API function SQLDescribeParam behind the scenes to improve performance and avoid excessive cache memory thrashing for SQL statements that have I/O parameters when accessing SQL Server (VTX12_SQLNATIVE). Synergex recommends setting SSQL_PERFORMANCE_SQL to yes (or setting the SQLPERFORMANCESQL environment variable. We have updated the environment to do this, which should result in improved performance when running in Synergy 11 or higher environments.

  • We have added the ability to run multiple instances of the replicator process side-by-side, within a single data set, and to divide up the replication of different files between these multiple replicator instances. Each instance is assigned a unique instance name and has its own associated instruction queue file, the name of which includes the instance name, as does the log file produced by each instance. In a multi-instance environment developers can choose on a file-by-file basis which data files are replicated via which queue file, and therefore via which replicator instance. It is important to understand that in this scenario there is no synchronization of the sequence in which changes are applied to the underlying SQL database between the multiple instances of the replicator.

  • We have added the ability to exclude certain fields in a record from being appearing in and being replicated to the associated SQL Server database table. It is important that fields associated with keys not be excluded unless those keys are also excluded (see the next item). Generally, the fewer fields/columns that are replicated the faster the replication will be.

  • We have added the ability to exclude certain keys from processing so that matching database indexes will not be created in the associated database tables. Generally, the fewer indexes that exist on a table the faster the replication will be.

  • We have added the ability for the replicator to detect some kinds of database failure, caused by network errors, or the database being shut down or otherwise unavailable, and allow it to gracefully disconnect, and then attempt to reconnect, with a configurable number of retries and retry interval. This should make the replicator processes more robust.

  • We have added the ability to change the database datatypes of fields. The main use case envisaged is transforming decimal fields into implied decimal fields (adding decimal places in the database data), but there may be other use cases, such as transforming Y/N fields into Boolean true/false fields, etc.

  • We have also corrected an issue that could occur if the replicator encountered an unexpected error and was configured to use BATCH COMMIT mode. In some circumstances, if there were uncommitted changes at the time of an error any uncommitted changes could be lost because the change records were deleted from the instruction queue one by one before the batch was committed. Now any instructions related to uncommitted changes remain in the queue file until the batch is committed, and are then all deleted. When the replicator is restarted it will then attempt to reply the changes to the database again. This should help prevent the database from getting out of sync if there is a replicator failure.

Many of these new features rely on using the latest and greatest version of CodeGen (5.4.8) which was also released today.

2nd February 2019

  • We corrected an issue related to the exporting of time fields to delimited files used for bulk upload.
  • We altered the code that verifies date values when date fields are used in a primary key in the SQL table. Synergy allows dates to have a value of zero, indicating no date is defined. In SQL Server the equivalent is to set the date to a "null" value, but SQL server does not allow any primary key segment to contain a null value. We have altered the code to insert the SQL Server minimum date (1st January 1753) whenever a zero or invalid date value is detected in a field associated with the table's primary key.
  • We modified the code that exports a file's data to a delimited file prior to bulk load to use a SELECT statement instead of a READS loop.
  • We renamed the JsonParser class to JSON (in line with the Harmony Core project where the master copy of the code resides) and added a new JsonSerializer class that can be used to create JSON formatted data. This new class is not in use in the environment yet but will become useful when planned future enhancements are made.
  • We removed the SEALED keyword from both I/O Hooks classes and changed various PRIVATE members to PROTECTED. This enables both classes to be inherited from in the case where multiple I/O hooks classes might be required.
  • We added a new mechanism to allow SQL Replication to be suppressed even though all of the code may be present in the environment. By setting the environment variable SQL_REPLICATION_DISABLED to a value of YES the I/O hooks classes will not write any instructions to the replication instruction file.

January 11th, 2019

  • We changed the names of the common variables used to preserve cursors. The previous names had a 12 character overhead and thus only allowed for structure names up to 18 characters before the name of the variable became too long. The new only has a 2 digit overhead and will work with structure names up to 28 characters, or longer if underscores are involved as they will be removed.

January 9th, 2019

  • Fixed an issue with %IsNumeric when "-" characters are present in the value.
  • Added validation for zero dates and bad numeric values when exporting to delimited files for the bulk load.

December 24th, 2018

  • We implemented the ability to use CodeGen structure aliasing to change the names of the tables created in the SQL database. This feature is enabled by removing the rem comment from the set USE_STRUCTURE_ALIASES line in regen.bat and replacing %STRUCTURES% with the list of alias table names that you wish to use. You should provide an alias name for each of the structures that you are generating code for, and be aware that using aliases changes the name of the source files that are generated. For example:
    set STRUCTURES=EMP DEPT

    set USE_STRUCTURE_ALIASES=-a EMPLOYEE DEPARTMENT
  • We implemented the ability to use CodeGen field aliasing based on field alternate names to alter the names of the columns created in database tables. This feature is enabled by removing the rem comment from the set USE_ALTERNATE_FIELD_NAMES line in regen.bat. Note that using this feature requires CodeGen V5.3.11 or later.

July 16th, 2018

  • We implemented a third database commit mode called "Batch Commit" mode, which is now the default behavior. Refer to database commit modes for additional information.

  • We corrected an issue that could occur if any blank alpha primary key fields were encountered during insert tr update. Because the default behavior is to trim and null terminate alpha fields, this would cause a problem when this happened to primary key segments, because SQL Server does not allow null values in columns which are part of the primary key. Alpha primary key segments are no longer trimmed or null-terminated.

July 12th, 2018

  • We fixed an issue that was preventing an error from being reported if the replicator was unable to open its log file. If a terminal is attached then the problem will now be successfully reported on the terminal, if no terminal is attached the problem is reported in the system log.

  • We implemented a new "stop now" mechanism which will once again allow the replicator to be stopped as soon as it has finished processing the current transaction. The mechanism which allows a stop command to be scheduled as a regular queue item is still present.

July 11th, 2018

  • We changed the replicator so that the log file is not opened and closed each time a log message is written because the performance impact was too high. This means that the log file will be locked while the replicator is running.

  • We added a new replicator instruction CYCLE_LOG which when processed causes the replicator to close the current log file and open a new one. The name of the new log file is recorded as the last message in the current log file.

  • We modified the replicator menu utility so that it has a new option to cycle the log file.

  • We added a new queue monitor utility that reports the current replicator queue length every second.

  • If a replicated ISAM file does not have a unique primary key, the index associated with the first unique key will now be created as a clustered index in the database.

July 9th, 2018

  • We changed the sample environment to place SQL Server into "auto-commit" mode at all times except when executing the old-style table load mechanism (pre bulk load). By doing so we can avoid having to explicitly execute start transaction/end transaction statements, each of which is a network operation in client/server environments. If you wish to revert to the previous behavior then you can use the new replicator command-line option -autocommit NO or alternatively set the environment variable REPLICATOR_AUTO_COMMIT to a value of NO.

  • We changed the transaction log file to use an auto-sequence key (I8) instead of the previous %datetime timestamp (A20) key. If you have an existing environment and are not able to implement the change at a time that your transaction queue file contains no records then you will need to perform a data conversion on the file (refer to the included sample program ConvertQueueFile.dbl). Please be aware that because of the use of an auto-sequence key the file now contains binary data in the first 8-bytes, so it is no longer possible to unload or reload the file using the ISLOAD utility, and if you need to unload or reload the file using the FCONVERT utility it is important that you use the "counted file" format when exporting the data. Unfortunately, this change also means that it is no longer possible to inject shutdown commands at the head of the queue, so for the time being shutdown requests have reverted to being processed in sequence with any other outstanding instructions.

May 31st, 2018

  • We addressed an issue when a bulk load operation was taking more than 60 seconds to process the BULK INSERT statement. There was a default 60-second timeout on the execution of the SQL statement, which is inappropriate for large BULK INSERT operations. We suppressed the timeout for such operations.

  • We enhanced the replicator menu utility by adding some basic support for replicator instance names. We also added an option to view running replicator processes on Linux systems.

  • We made several enhancements to the Linux replicator management scripts.

May 17th, 2018

  • We changed XCALL REPLICATE so that it adds shutdown requests to the beginning of the instruction queue instead of the end. This will mean that shutdown requests will be processed as soon as possible, i.e. as soon as the replicator has completed its current task. Previously a shutdown request would not be processed until all currently queued tasks had been completed.

  • We fixed the Load and Bulk Load operations for multi-record-format (tagged) structures. These were inadvertently broken when we switched from the custom IO routines to regular IO statements.

February 22nd, 2018

  • We changed the bulk load mechanism to now use a new feature in FileService called "chunked upload". This mechanism breaks down the upload of large files into multiple synchronous operations, in turn reducing the memory requirements and making it possible to upload very large files.

  • The REPLICATOR_REMOTE_EXPORT environment variable has been removed and replaced with two new environment variables REPLICATOR_FILESERVICE_HOST and REPLICATOR_FILESERVICE_PORT.

February 15th, 2018

  • We have removed the previous bulk load mechanism that used xfServer to upload data to the database server for subsequent BULK INSERT and have replaced it with a better and more flexible solution. The environment now includes a Synergy .NET project named FileService which is the code for a Windows Service that can be installed on the Windows database server host. The Windows service in turn hosts a Web API 2 RESTful web service that allows the delimited data files that are used during bulk load operations to be uploaded to the server via an HTTP post. This capability can be supported by any Synergy environment, including OpenVMS systems. The bulk load code that the replicator uses has been updated to use this new HTTP mechanism instead of using xfServer on the database server system. If you wish to use this new bulk load mechanism then you must have a Synergy runtime (RUN10) license available on the database server system, in addition to the SQL Connection API (SCSQ10) license that is also required.

January 19th, 2018

  • We introduced a mechanism for relative files to be replicated in addition to ISAM files. The code generated for relative files will include a "RowNumber" column that is populated with the associated record number from the underlying relative file. This row number is then used to synchronize the data in the file and table.

  • We introduced a new "bulk load" mechanism which in some circumstances can substantially reduce the time taken to load initial data into a table. The mechanism exports the data to a delimited file in the Synergy environment, then uses xfServer to copy the file to the database server where it is subsequently processed with a BULK INSERT statement. The sample EMPLOYEE file (which has 25,000 records) loads 20x faster using the new mechanism in my test environment (2 seconds vs 20 seconds). This mechanism requires an instance of xfServer to be running on the SQL Server system, and so can't be used when the replicator is running on OpenVMS. This capability is intended to be a temporary solution for use on Windows and UNIX / Linux until a better and more generic solution can be developed.