An interview with Bill Bach

by Douglas Reilly

As I have admitted before, I am a reformed Btrieve developer. Actually, I was a Btrieve geek. I wrote articles in Dr. Dobb’s about Btrieve file internals, I was the Inside Btrieve columnist for the Btrieve Developer’s Journal, and I made a reasonably good living selling a couple of Btrieve utilities as shareware. As recently as six months ago, I sold another copy of one of the DOS-based Btrieve utilities.

Because of this early passion for Btrieve, I was excited to interview Bill Bach, whose company, Goldstar Software, is perhaps the leading source for training and support for users of Btrieve and Pervasive databases.

The following questions were asked and answered via email.

Doug: I always thought of you as a DBA/database support type of person who specialized in Pervasive SQL. Are you a non-developer?

Bill: Not at all. I’ve been developing applications since the Btrieve 5.x and NetWare SQL 2.11 days, and continue to do so today.

Doug: How did you get involved with training and support of Btrieve and Pervasive databases?

Bill: After doing database development the old-fashioned way, via flat files, on the Apple II product line-my IIgs is still running today-and later on the PC, I started developing applications using Btrieve 5.x and NetWare SQL 2.11. Then I moved to 6.10/6.15 and Scalable SQL 3.01, and then to Pervasive.SQL.

Over the years I wrote new code, debugged existing code, installed software at customer sites, and provided phone support. I also presented training courses that covered databases and database applications, as well as the operating systems, which, in the early days, were NetWare 3.11 and DOS, but later Win3.1 and above.

I really enjoy working on puzzles and troubleshooting problems, and I even enjoy getting down to the bits and bytes inside the database. I also like assembly language programming, strangely enough.

In 1977, my wife convinced me to quit my job as a manager and we incorporated Goldstar Software Inc. to provide full-time database support to companies around the country. I’m now having a lot of fun, doing what I enjoy-solving puzzles-and it doesn’t seem like quite so much work anymore.

Shortly after starting Goldstar, I became a training partner with Pervasive Software to provide their classes to my customers. Pervasive’s first class was on Btrieve software development, but I already knew that the real market demand was for a course based on supporting the product, so I started writing my own.

In 1998, I taught our first Btrieve 6.15 service and support course in Chicago. We’ve been doing them ever since, with upgrades to the training materials for Pervasive.SQL 7, 2000i, V8 and now Pervasive PSQL v9.

We’re now the largest Pervasive reseller in the U.S. by volume. We have customers in every state except Rhode Island, a majority of Canadian provinces, several U.S. territories and numerous countries.

Doug: Describe the relationship between Pervasive SQL and the underlying Btrieve files.

Bill: The core Btrieve engine still exists inside Pervasive.SQL. It provides extremely efficient, high-speed data access that can’t be beat by any other access method. What Pervasive did was add relational capabilities-the SQL engine-to the product and sell them together, providing developers with the performance and flexibility of Btrieve, while allowing them access to data from any ODBC application. It’s a best-of-both-worlds situation.

Doug: One of the interesting parts of working with Btrieve and Pervasive SQL is that the underlying implementation details are more apparent than with many other database systems. Do you see that as a benefit or a pitfall?

Bill: It’s both. Having transparency means that developers can do exactly what they want to do with the database. But since the database files are visible, they can be edited with Wordpad, which trashes the data inside. I think the benefits win out, though, since a savvy developer will always find ways to make the database work for him or her.

Doug: Did you work with any other database systems?

Bill: I did some support work with Progress and SQLServer for a short time, but my development work has always been on Pervasive.SQL.

Doug: I am a big fan of doing database work as close to the data as possible. Early on, I tried to use Pervasive SQL to do the same sorts of things I do today in Microsoft SQL Server stored procedures. To say that the system was not a success is putting it mildly. How have you found using Pervasive SQL stored procedures?

Bill: I like them. I agree that early releases needed lots of help, but newer versions are stable, and you can actually create stored procedure code that runs many times faster than corresponding Btrieve or even interactive SQL statements! I habitually use stored procedures to help customers write database clean-up code, deleting old records, finding incorrect duplicate records and such. One time, I took a long-running process that went through six million records and worked it into a stored procedure for the client. The original process ran in two to three hours, but the stored procedure ran in less than five minutes.

This type of performance boost can be obvious when you examine how the database application uses the network. A simple Btrieve database read takes, on a decent system, less than 100 microseconds (0.1ms). Read 10,000 records for a process and you have a response time of one second. But do this same process over a slow network link-with a network latency time of only 20ms-and that same process now takes 200 seconds to complete! It gets even worse for wireless and long-haul networks.

Using SQL queries or a stored procedure can help eliminate the round-trip data transfers, providing markedly faster response time for an app over a high-latency network. This is why I also use network analyzers heavily in my support work: You can easily pinpoint the cause of most problems with a tool like that.

Doug: There is a relatively new version of Pervasive SQL available. Can you explain any of its new features?

Bill: Pervasive concentrated on the SQL engine in PSQLv9, providing improved performance, a new query tool, system stored procedures and a more Microsoft-like syntax, so SQL Server developers would feel more at home. Pervasive also expanded on the Btrieve-level performance and scalability improvements in the v7 and v8 releases by allowing an 8K page size and a 128GB maximum file size. Toss in some newer versions of utilities and some better Linux support, and you have a good, solid release. The features added in Pervasive.SQL V8.5, such as encryption and improved Btrieve-level security, are still there, of course.

Doug: I see from your web site that you consult on database synchronization. Can you talk a little about that?

Bill: Pervasive’s DataExchange product enables near-real time replication of Pervasive data from a primary server to one or more secondary servers, providing much-needed failover capabilities. We have helped several airlines implement solutions with this product, including on-site and off-site replication. While DataExchange is a fairly low-cost add-on product to Pervasive.SQL, it can be complicated to setup and maintain when used with ever-changing applications.

I never realized how complicated synchronization could be before digging into the low-level activities behind managing the database changes. Luckily, our clients can benefit from our experience, as can students in our Implementing DataExchange training course.

Doug: These days, even large database systems like Oracle and Microsoft SQL Server need to interact and exchange data with other database systems. Are you involved in transferring data between Pervasive and other systems?

Bill: All the time. We’ve helped numerous companies create data dictionary files (DDFs), which contain the metadata needed to access the Btrieve data from ODBC with the SQL engine. The use of variant tables or complicated structures makes this difficult sometimes, but we’ve rarely found a situation that can’t be fixed.

Once the DDFs are ready, any ODBC-compliant application can get in there and extract information. I’ve even written my own command line-based ODBC extraction tool called SQLExec that can fire stored procedures or run SQL scripts against any ODBC data source, with several formatting options for the output. It’s cheap, but still requires some know-how to transform the data for the target system.

I much prefer Pervasive’s Data Integrator product line, which offers a way to integrate disparate data sets without a lot of custom-built coding. As a developer, I can probably write my own code more cheaply, but supporting it long term is a problem. With Data Integrator, you write the translation map once, and then you can re-use it in project after project, eliminating a lot of custom coding.

Doug: Have you done anything with XML?

Bill: Only minimal work to date. We wrote a utility that extracts the DDF metadata into an XML-based format, providing a user with a way to modify DDF information and then load it back into a database. I think it was much easier, and more efficient, to use custom data structures for storage, but that’s just my old-timers experience coming into play. I mean, why use 20 bytes when two will do? But the flexibility and interoperability that XML provides should eventually outweigh the storage and processing overhead.

Doug: What sort of work are you doing these days?

Bill: I always have multiple projects moving forward, most of which are creating tools to make a developer’s or support technician’s life easier. One will be useful for rebuilding files when DataExchange is in use, automatically maintaining the System Data key that DX uses to track database changes. Another, and probably the most ambitious utility, provides an advanced way to recover data from corrupted files, automating some processes that we currently do manually for customers during our corruption-recovery work.

This is exciting stuff because we have been able to recover data that has been mangled by server crashes, bad memory, failed hard drives and other such anomalies, where the standard tools have not been successful at getting to the data. There’s lots of work still to be done in this area, however, because the number of possible problems is huge.

Doug: Have you read any good database-related books lately?

Bill: In the Pervasive world where I live, there are not a lot of books out there, outside of Jim Kyle’s Btrieve Complete. The knowledge that I’ve picked up while writing courses and troubleshooting customer environments has been quite useful, however, and all of it has been stored in my own knowledge base. Let’s just say that another book on Pervasive.SQL might be in the cards, especially if I have some free time this summer.

Doug: Can you think of any particularly cool tip or trick that many Pervasive database developers do not know about?

Bill: The biggest thing that Pervasive.SQL developers don’t know about is SQL! Many developers are still working with the high-speed Btrieve API, and they don’t often provide data dictionaries, or the dictionaries are poorly compiled, making it difficult to get to the data from SQL. But once I show them how powerful the SQL statement can be for database cleanup, review and debugging, they are usually hooked.

My favorite example is to use SQL for updating area code changes in databases. The task can be a nightmare for Btrieve developers, but if SQL access is available, it boils down to a simple Update statement that end users can put together and run on their own. Once a developer frees him- or herself from having to worry about reporting and data management tasks, he or she can concentrate on making the best possible program, making customers even happier!

Do you know someone who deserves to be a Database Geek of the Week? Or perhaps that someone is you? Send me an email at editor@simple-talk.com and include “Database Geek of the Week suggestion” in the subject line.