Open Menu

Synergex Blog


Replicating Data to SQL Server – Made Easy

By Steve Ives, Posted on July 28, 2016 at 5:29 pm

For some time now we have published various examples of how to replicate ISAM data to a relational database such as SQL Server in near to real time. Until now however, all of these examples have required that the ISAM files that were to be replicated needed be modified by the addition of a new “replication key” field and the addition of a corresponding key in the file. Generally this new field and key would be populated with a timestamp value that was unique to each record in the file. While this technique guarantees that every ISAM file can be replicated, it also made it hard work to do so because the record layout and key configuration of each ISAM file needed to be changed.

However, almost all ISAM files already have at least one unique key, and when that is the case one of those existing those keys could be used to achieve replication without requiring changes to the original record layouts or files. When this technique is combined with the capabilities of I/O hooks it is now possible to achieve data replication with only minimal effort, often with no changes to the ISAM files being replicated, and with only minimal modification of the original application code.

I am pleased to announce that I have just published a new example of doing exactly that. You can find the example code on GitHub at https://github.com/SteveIves/SqlReplicationIoHooks. Of course if you are interested in implementing data replication to a relational database but need some assistance in doing so, then we’re here to help; just contact your Synergex account manager for further information.


It’s a good time to clean up your old Synergy DBMS data

By William Hawkins, Posted on November 11, 2009 at 4:27 pm

Synergy/DE is a pretty forgiving environment when it comes to dealing with fields containing unexpected data. For example, you can have a d field that is either all spaces, all numbers, or some combination, and Synergy/DE will treat the spaces as zeros. (For those of you old enough to remember DBL version 2, back then you could even increment a decimal field that was all spaces, resulting in a field that was all spaces except for the last character which was a !, and DBL would still see the decimal field as a numeric 1. When that occurred, it made for some interesting debugging!)

Now that the world of Synergy/DE for .NET is imminent (a beta version is available for those who are interested), the issue of data interoperability and ensuring that C#/ VB.NET/Framework-language-of-choice can work with Synergy data is also moving to the forefront. For those of you who have been using xfODBC and/or SQL Connection, some of these issues may be familiar, but a lot of developers will foray into this arena for the first time with Synergy/DE for .NET. This is especially true for those of you using xfNetLink .NET edition and looking to bypass the xfNetLink layer and use your code directly from Synergy/DE for .NET. (xfNetLink shelters you from these issues, as it can coerce inappropriate data into a usable native form.)

Synergy/DE basically has three “native” data types: integer, alpha, and decimal. Here are some of the issues that you’ll need to think about for each:

· Synergy integer fields (not to be confused with System.Integer fields) – No issue here; you really cannot store inappropriate data in a Synergy integer field. All you really need to watch for is storing an integer field that is all spaces. Of course, this is still a valid integer; it’s just not the zero value that you may have expected.

· Synergy alpha fields – Non-printable characters (outside the 7-bit characters space thru tilde) really need to be processed as a byte array and not thought of as a string (or alpha) field. But for most situations, alpha fields are relatively problem-free.

· Synergy decimal fields – Here’s your main problem area. I think of Synergy decimals as a special form of Synergy alphas that only contain numeric data and that you can perform math functions on. So, you need to ensure that decimal fields only contain the characters 0 thru 9, with the last character optionally being p thru y (to deal with trailing over-punch data). It’s not uncommon for decimal fields to be all spaces or to have spaces inside the field instead of zeros. It really depends on your code and the last time that you wrote the data to the file. It’s not all that uncommon to have data that’s over 10 years old and hasn’t ever been updated.

Another common issue is that Synergy/DE allows a d8 date to have a value of 00000000. In most other languages, this is an invalid date. If you don’t have a date, other languages use null, not zero, to indicate this. When you move your d8 date field to a datetime field in .NET, you’ll need some conditional logic to cope with this. Your code may also deal with a date field that contains a value such as 20090230, and it will just ignore the fact that this a bad date and display 02/30/2009 (U.S. format) or 30/02/2009 (rest of the world). Try to do this in other languages and you could be in for an unpleasant surprise.

Hopefully, most of the reasons for having inappropriate data in your Synergy DBMS files have been addressed over time. However, you may still have residual data out there that is technically “bad” but is hidden due to the forgiving nature of Synergy/DE. In our experience, adding logic to the intermediate layer to check and fix the data as it’s being pushed to non-Synergy/DE systems can add significant overhead to the application. It’s much more efficient to fix the data (hopefully as a one-off exercise) and modify your application to stop adding bad data to the Synergy DBMS database. For example, commands such as the INIT statement can help ensure that decimals and integers contain correctly initialized data. There are probably a few of you out there who have cleared a Synergy record and then realized that an ISAM key contained a number that got stored as a space and not a zero, so that the key contained records that were “out of order.”

To help, we have added a utility to CodeExchange (fixdata.dbl) that will allow you to apply a Synergy Repository structure definition to a Synergy DBMS file, forcing fields to contain valid characters and (for example) date fields to contain valid dates. We will be extending the utility to include other features, such as validation logic based on Synergy Repository definitions, so that it will be able to detect when a field contains an “out of range” value. If you already expose or plan on exposing your data to .NET, xfODBC, or SQL Connection, I encourage you to download and run this utility. If you have any comments or suggestions on this utility, please email me at William.Hawkins@Synergex.com.


New select classes are a home run

By synergexadmin, Posted on September 23, 2009 at 4:18 pm

Early last week, I was given a copy of the beta build of Synergy/DE 9.3.  My task was to do some testing of one the exciting new features it includes: The Select classes.

Now, testing isn’t always fun, and it can be frustrating trying to figure out if a bug is really a bug, or just a problem born of having no clue what I’m doing.  This time, however, any minor problems I encountered were completely overshadowed by the sheer awesomeness of the new classes.

The Select classes provide a SQL-like syntax for communicating with Synergy databases, and it’s amazing just how simple they are to use.  Once I had a basic understanding of how they worked, I was able to compress a simple READS loop – complete with “filters” and error checking – into a single line.

Consider the following code, which loops through active customer records and prints out the customer number, name and last sales date of anyone with no sales for more than a year:

    repeat
        begin
        reads(ch_cusmas,cusmas)  [err = eof]     if (cusmas.status .ne. ‘A’); If customer is not Active, ignore it
    nextloop
        if (cusmas.last_sale.year < lastYear)
        call printLine
    end
eof,    etc…

The basic syntax and usage of the Select Class is:

    foreach myRecord in @Select(@From[, @Where][, @OrderBy])
    doSomething

And so, using the Select classes, I condensed everything into:

customers = new From(ch_cusmas,cusmas)
noNewSales = new Select(customers,(where)status.eq.’A’ .and. last_sale.year < lastYear)
foreach cusmas in noNewSales
    call printLine

(I actually condensed the first three lines into just one foreach statement, but the result is a line of code that doesn’t fit nicely into a blog entry, and therefore becomes more difficult to read.)

The syntax is neat, but it’s not the best part; the really cool stuff is happening under the hood.  The actual database I/O layer is now handling all of the “filter” logic, and it’s doing it faster than a regular READS loop can handle.  In fact, during my tests, a filtered return of around 18,375 records showed a performance benefit that ranged from 11 to 21 percent.  Now, that’s a small data set and we’re only talking about milliseconds, but it demonstrates a performance boost nevertheless – and that’s for a local application, running against a local database.  The savings over a network connection to a remote database (i.e., xfServer) is likely to be enormous, as the I/O layer on the server is now doing the filtering, rather than returning the data to the client to handle.

Other features include the OrderBy class, which (as expected) sorts the returned data in either ascending or descending order based on the key being read.  The classes also provide for a sparse record population, in which only the fields needed by the application are actually returned.  There are even methods available to get at each individual record returned in the set, write back to the file, etc.

The fact that an update to Synergy/DE 9.3 is all that’s required is impressive as well.  There’s no need to perform any database conversions, or add additional services or products; the Select classes work right out of the box.

The Select classes represent a significant addition to the language, and I can imagine a time in the not-too-distant future when they become the primary Synergy database access mechanism.  My hat’s off to the Synergex development team; it appears that they’ve hit this one out of the park.


RSS

Subscribe to the RSS Feed!

Recent Posts Categories Tag Cloud Archives