Intercom V01n02, 1997

,QWHU%DVH6RIWZDUH&RUSRUDWLRQ

,QWHU&RP 92/80(180%(56800(5

InterBase Makes its Mark at the Borland Conference Amelia Arnett Nashville, Tennessee—InterBase began this quarter with its first-ever trade show appearance as the InterBase Software Corporation at the 7th Annual Borland Conference (BorCon), held this year at the magnificent Opryland Hotel Convention Center. The show was a success for InterBase and its VARs, some of whom demonstrated their product at the InterBase booth for other BorCon attendees. InterBase VARs demonstrated products including: • XCase, a database design and maintenance tool from RESolution, Ltd. • SalesManager from Laconic Logic • LeaseAware from Decision Systems, Inc. • TRANSMAN from TMT Software. InterBase Software Corporation President Jim Weil was “pleasantly surprised and gratified by the amount of support from conference attendees. It was great to hear that they, too, thought [becoming a formal Borland subsidiary] was a good idea for InterBase, and some

The scene at the InterBase booth at the 7th Annual Borland Conference was one of almost nonstop activity. We were visited both by established VARs and many interested new ones. Pictured here in the dark uniforms are Quality Assurance Engineer Chris Levesque, Associate Marketing Manager Amelia Arnett, and Account Manager Scott Schnaars.

gave us constructive criticism that was genuinely helpful. We also met some new people working on very interesting client/server projects and had the opportunity to talk with them about deploying their solutions on InterBase.”

Amelia Arnett is Associate Marketing Manager for InterBase Software Corp. She can be reached at [email protected]

CONTENTS InterBase generated enough interest at this year’s BorCon to warrant its own track and a larger presence at next year’s show, so be sure to let us know what you would like to see from us. InterBase is also making plans for more trade show appearances in 1998. We’ll let you know where you can find us in the future. Keep your eye on www.interbase.com for more information!

InterBase Makes Its Mark . . . . . . . . Q&A: Mike Tossy . . . . . . . . . . . . . . . The Joy of GBAK . . . . . . . . . . . . . . . . Behind the Scenes . . . . . . . . . . . . . . . Handling Deadlocks in Delphi and C++Builder. . . . . . . . . . . . . . . . . Welcome New InterBase Staff . . . .

1 2 3 4 5 6

Q: What are the biggest surprises so far? Well, the technology continues to surprise me. This product has been completely underestimated by the marketplace. Getting the word out has got to be our number one priority! I was also surprised that InterBase hadn’t been better integrated into the Borland tools, so that is also a key priority.

Q: In what way would you like to integrate InterBase with the Borland tools?

The InterBase gang, including (l-r) Director of Finance Allison McEwen, President Jim Weil, Borland Marketing Director for International Paul Beach, and Technical Publications Manager Bill Karwin, quickly got into the country spirit at Opryland Amusement Park.

Q: Where were you before?

Q&A: Mike Tossy Director of Marketing Each issue of InterCom features an interview with a key InterBase personality. This month, we interview Mike Tossy.

A series of small companies, mostly in the relational database arena. The largest and best known is Teradata. More recently a company called NeoVista that is working in the data mining area.

I think that InterClient—our new Java interface—is a great example of the right kind of integration. By providing our own Java/JDBC driver we can provide tight integration with JBuilder. It allows a high performance interconnect. We can also provide access to InterBase’s unique technology—like event alerters—which is harder to do if you don’t own the connect technology. I think InterClient is a model for the kind of connect technology we need to provide to other Borland tools like Delphi and C++ Builder.

Q: What about Borland DataGateway for Java and other BDE-based technology?

Almost 4 months, I started on June 2, 1997.

They are also important. VARs who want applications that can run against multiple databases will want to use DataGateway. But VARs who want the closer integration with InterBase will want to choose InterClient.

Q: What brought you to InterBase?

Q: How is InterBase 5.0 coming along?

Jim Weil called me up when he became President and asked me to take a look. Well, I’ve been involved in the relational database market since the early 1980s and I was aware of InterBase almost since its beginning. I knew of its strong technology and also its not-so-strong marketing. When I saw the opportunity here I just couldn’t resist.

So far, it is right on schedule. Shipping this product on time is key. As we talk, we’re in QA Beta 1 and by the time this is published we should be in QA Beta 2 or even Early Release.

Q: How long have you been with InterBase?

Q: What are these new terms “QA Beta” and “Early Release”? QA Beta refers to the Quality Assurance Beta Release. This is a program where we give customers—many of whom are

,QWHU&20



VARs — an early copy of the 5.0 release and they help test it. The purpose is to help us to find the bugs in our code. There are two of these, thus “QA Beta 1” and “QA Beta 2.” The “Early Release Program” comes after QA Beta 2 and is a chance for our VARs and other major partners to test their applications against the new code. The purpose here is to get their integration started and to find any problems at this level. All of this used to occur under the rubric of “Beta,” but we decide it was cleaner to clarify what was expected at each phase. By the way, guaranteed entry into the Early Release Program is one of the benefits of becoming an InterBase VAR.

Q: What are your biggest challenges ahead? Marketing the product. We’ve got to rev up the PR, promotions, and advertising. InterBase can’t remain the “best kept secret.” I’m really looking forward the next 12 months as we begin to move the message out. Mike Tossy is the Director of Marketing for InterBase Software Corporation. He can be reached by phone at 408-430-1505 or by email at [email protected].

The Joy of GBAK Q: You also changed the release schedule?

Bill Karwin

Yes, I felt that there had been too much time between releases. So, for our primary platforms we’ve committed to an annual major release in December. We’ve also added a regularly scheduled maintenance release every June. Right now, we’re planning InterBase 6.0 (Dec 1998) and InterBase 7.0 (Dec 1999).

Like going to the dentist, backing up a database is a chore that most of us would prefer to avoid, even though we all know the value of disaster prevention. In the new 5.0 release, improvements to the gbak tool make backing up and restoring databases much more facile. gbak’s new companion tool, gsplit, provide even more benefits.

Q: Who do you see as InterBase’s competitors? How well does InterBase compete against them? Sybase’s SQL Anywhere and Microsoft SQL/Server would be the big two. I personally think that the Sybase product should have been named “SQL Hardly Anywhere”. The only place it runs is on Microsoft operating systems. What kind of anywhere is that? InterBase runs Windows and NT, and on UNIX, NetWare and OpenVMS: that is more truly anywhere. SQL/Server has that same problem, plus the advantages and disadvantages of being from Microsoft. We’ve joked about running an ad campaign whose headline was “InterBase: Smaller foot print than their... word processor.” I believe being a small, single-product focused company gives us some flexibility advantages.

Performance Improvements The InterBase developers have worked hard to systematically analyze and alleviate performance bottlenecks in gbak. Now a backup executes an order of magnitude more quickly than in the previous version of InterBase, particularly for databases with complex metadata. You don’t need to change the way you use gbak in order to take advantage of these improvements.

Filtering Backup Data In InterBase 4, gbak could read or write raw data only to a disk file or a tape device. In 5.0, you have another choice; you can backup to the standard output, and restore from the standard input. Use the special word stdout in place of the backup filename when writing backup data to a pipe; use the special word stdin when reading data from a pipe to restore to a database.

,QWHU&RP



This new option allows gbak to take advantage of filters such as compression utilities to modify the input or output of the tool. For example, gbak -b employee.gdb stdout | compress > employee.gbak.Z uncompress -c employee.gbak.Z | gbak -c stdin new_emp.gdb

Backing up Large Databases Some databases may be so large that their backup file won’t fit within the size limit set by the operating system (this limit is often either 2 or 4 GB; refer to your OS documentation). InterBase, with its multi-file database feature, can easily have a database larger than this limit. This is a serious constraint, since the output of InterBase 4’s gbak utility is only a single backup file. In 5.0, we have added the gsplit tool as a filter to allow backups to write to (or read from) multiple files. Using the new gbak feature of writing output to a pipe, the gsplit tool takes the data stream and writes it to several files in succession. You specify the path, filename, and maximum size of the files. Here’s an example: gbak -b employee.gdb stdout | gsplit -split emp_part01.gbak 20m emp_part02.gbak 15m emp_part03.gbak 200m

The first file, emp_part01.gbak, fills first, up to 20 megabytes. The next 15 megabytes of data write to emp_part02.gbak. The excess writes to emp_part03.gbak.The files can be located on different filesystems (drives), or even on NFS filesystems (mapped drives). To restore a database from these multiple .gbak files, use gsplit with this syntax: gsplit -join emp_part01.gbak emp_part02.gbak emp_part03.gbak | gbak -c stdin new_emp.gdb

Using a Tape Device Making a backup directly to a tape device has been improved in InterBase 5.0. Tape device I/O is supported on UNIX and Windows NT. If the data you are writing is larger than one tape, gbak

prompts you to insert subsequent tape media.

To create a backup schedule like the one in the UNIX example, issue the following command:

On UNIX, where tape is the appropriate tape device file for your flavor of UNIX (for instance, /dev/rct0 or /dev/nrst0), use:

at 8:30PM /every:Wednesday,Friday /interactive c:\db\mybak.bat

gbak -b employee.gdb /dev/tape

The syntax for writing backup information directly to a tape on the Windows NT platform is as follows: gbak -b employee.gdb \\.\tape

Windows 95 has no named tape device, so gbak cannot backup a database directly to tape; you must backup to a file on disk or network drive, then use your tape backup software to write the backup file to a tape device.

Scheduling Automatic Backups You may find it useful to perform backups on an automatic schedule, using the operating system’s scheduling facility. Here are examples of how to do this on UNIX and Windows NT: UNIX On any brand of UNIX operating system, you may use the standard utility cron. Create a shell script containing your gbak command and any other actions that you want to associate with the task: $ edit mybackup.sh : gbak -user sysdba -password xxxxxxxx -b /db/mydb.gdb stdout | compress >/dev/tape echo “Gbak completed at ‘date‘” | mail root

Create a schedule for the task. See the cron man page for details on the format. Here’s an example of a schedule for every Wednesday and Friday (days 3 and 5 of the week) at 10:30PM (2230 on the 24-hour clock). # crontab -e root 30 22 * * 3,5 mybackup.sh

Windows NT The utility for scheduling batch files on Windows NT is called AT. C:> edit c:\db\mybak.bat gbak -user sysdba -password xxxxxxxx -b c:\db\mydb.gdb \\.\tape

To configure NT to properly run batch files with AT, you must first perform the following steps: 1. log in as Administrator 2. go to the NT Control Panel / Services 3. highlight the Schedule service 4. click Stop for the Schedule service 5. click the Startup button 6. check the box marked “Allow service to interact with desktop” 7. click Ok 8. click Start for the Schedule service

The many improvements in InterBase 5.0 have increased the performance, flexibility, and convenience of backing up and restoring databases. Now there’s no excuse for avoiding this chore. Bill Karwin is Technical Publications Manager for InterBase Software Corp. He can be reached at [email protected].

Behind the Scenes: InterBase 5.0 Core Team Helen Kruse Larkin

On both UNIX and NT, be sure to set appropriate security on your batch file, since it necessarily contains your SYSDBA password. The scheduling utility must be able to read the file, but you don’t want any normal user to be able to access the file.

Everyone knows that a software release doesn’t just happen. Many different people and functions and activities play their parts along the way. Making a whole out of the sum of these parts requires careful planning and communication.

PLANs in Stored Procedures In InterBase 4, a design limitation in the restore algorithm made it impractical for a Stored Procedure or Trigger to contain a query that used a PLAN clause to override the query optimizer. In 5.0, queries in Stored Procedures and Triggers can have a PLAN clause and still backup and restore correctly. Even better, dramatic improvements to the InterBase query optimizer reduce the need for manual query optimization.

The Core Team is a small crossfunctional group, consisting of members from several departments of InterBase: Engineering, QA, Technical Publications, Technical Services, and Marketing. This team provides a regular forum in which to compare progress, identify bottlenecks, resolve conflicts, establish priorities, and find the most expeditious path to the best possible final product. In general, the task is to deal with problems sooner rather than later and to keep a weather eye out for the unexpected.

Look for the Following Articles in Upcoming Issues of InterCom 4 IBPerl, the object-oriented package for Perl 5 programmers to access the InterBase client API 4 More details of new InterBase 5.0 features 4 InterBase at work: case studies 4 Tips on upsizing Paradox applications 4 The InterBase Guru answers the really tough questions

,QWHU&20



The 5.0 Core Team has been meeting at least once a week since mid-August and keeps its focus on the release schedule and logistics as opposed to ongoing functional activity or relocation to 1800 Green Hills or longer-range planning. InterBase has so far made two beta releases. The Early Release Program for VARs will begin soon and then we’re into the chute to reach the milestone of first shipment to customers by midDecember. There will inevitably be issues that surface between now and final release, but then, that’s just why the Core Team exists! Helen Kruse Larkin is Product Manager of Java Products in the InterBase Marketing department. She can be reached at [email protected].

Handling Deadlocks in Delphi and C++Builder James Arias-La Rheir Regardless of the SQL server backend, client applications that you develop in a BDE-enabled development tool — like Delphi, C++Builder or Paradox using BDE-aware components — may encounter a deadlock after issuing a SELECT statement when another client application locks a record by issuing an UPDATE, DELETE, or INSERT statement. The BDE defaults to a mode that automatically assigns a transaction level of READ COMMITTED and commits the transaction by doing a soft commit. In InterBase, a soft commit is equivalent to a commit retaining, corresponding to the InterBase client API function isc_commit_retaining(). Refer to the API Guide for more information. Soft commits are a feature of InterBase that let the driver retain the cursor when committing changes. Soft commits

improve performance on updates to large sets of data. When an application does not use soft commits, the BDE must refetch all the records, even for a single record change. With soft commits, the BDE retains the cursor and does not need to re-fetch. Explicit transactions started by BDE client applications never use soft commits. The default for BDEinitiated statements is a transaction level set up as NO WAIT, READ COMMITTED, NO RECORD VERSIONS. Because BDE does not use record versioning in this mode, the deadlock occurs when a SELECT tries to read a record that another client has not yet committed to the table. The DRIVER FLAGS parameter in the IDAPI32.CFG file allows you to specify the transaction behavior of BDEinitiated transactions against an InterBase server. Use the BDE Administrator or the BDE Configuration Utility to view or modify this setting. The DRIVER FLAGS parameter has four settings that are described in Table 1. The two recommended modes are 512 and 4608, depending on which is the

Table 1. DRIVER FLAGS and Associated Problems Driver Flag

Isolation Level

BDE/Application Problem

0 Blank

Read Committed/Hard Commit

Deadlock - performance hit would occur on large tables if deadlock did not occur.

512

Repeatable Read/ Hard Commit

Performance hit on large tables as BDE will fetch cursor to its previous location.

4096

Read Committed/ Soft Commit (Commit Retaining)

Deadlock - InterBase retains an interest lock until a hard commit is performed. See (1) below.

4608 (512 + 4096)

Repeatable Read/ Soft Commit

Changes (UPDATEs, INSERTs and DELETEs) in other transactions are not seen and the soft commit maintains the transaction’s “view” of the dataset. See (2) below.

(1) Long running BDE-initiated transactions prevent other transactions from performing DDL on any relations that the BDE transaction has looked at. The workaround is to periodically issue an explicit commit to release the interest locks. (2) Closing and reopening the data set (Query1.Close and Query1.Open) does not show changes made by other transactions. Refreshing the view requires disconnecting and reconnecting to the database (Database1.Close and Database1.Open). This may also require changing the Database1.KeepConnected property to False (default is True). Another approach is to perform a literal Database1.StartTransaction immediately followed by a Database1.Commit. In starting a transaction, the BDE closes out any transaction it has automatically active for the Database component (this is in keeping with the SHARED_AUTOCOMMIT setting).

,QWHU&RP



more desirable behavior to handle in the application. Beginning with the inline version of the BDE v4.0, the transaction level automatically defaults to NO WAIT, READ COMMITTED, RECORD VERSIONS. The DRIVER FLAGS modes that use READ COMMITTED also have the RECORD VERSIONS option set. The inline version of BDE 4.0 shipped with the inline version of Delphi 3.0. You can identify this version of the BDE by the timestamp of 4:01 A.M. on the file IDAPI32.DLL. In addition to specifying the appropriate DRIVER FLAGS mode, you also limit the chance of deadlocks by modifying the MAX ROWS setting, which is found on the same BDE DRIVERS page as DRIVER FLAGS. The MAX ROWS setting specifies the maximum number of rows that the driver will attempt to fetch for every SQL statement sent to the server. This includes metadata (schema) queries that the driver sends to the server during a table open to retrieve column, index, and valcheck information. Limiting the number of rows that any one SQL statement can retrieve from a large table prohibits users from tieing up server resources. Since the MAX ROWS default is –1 (no limit), it is conceivable that the SELECT statement can retrieve more records than the user actually requires. If experimentation proves that users need

,17(5%$6(62)7:$5(&25325$7,21 *5((1+,//652$'68,7( 6&27769$//(<&$/,)251,$

only 50 rows at a time, then setting MAX ROWS to 50 will reduce the occurrence of deadlocks. Of course the BDE will make additional fetches when necessary. Be aware that if you set MAX ROWS too low, you might not be able to open a table because it cannot retrieve sufficient metadata information from the database. See the SQL Links help file (SQLLNK32.HLP) for more information on MAX ROWS. Deadlocks are an issue with any SQL server, but the BDE (through the InterBase DRIVER FLAGS parameter) can handle most of the problems transparently, without a lot of manual transaction control. James Arias-La Rheir is a Client/Server Technical Support Engineer at InterBase Software Corp.

Welcome to New InterBase Staff Amelia Arnett InterBase Software Corporation is pleased to announce a successful first quarter of recruitment as we settle into our identity as a new company. We have hired new Technical Publications staff who are already working hard to produce material for the

InterBase 5.0 manuals, and new QA and R&D engineers to make that product a success. Two people have joined our marketing staff to help get the word out, and we have a new Director of Finance pictured earlier in our newsletter. It has been a great first quarter— welcome to all the new employees!

Credits InterCom is a quarterly publication of InterBase Software Corporation and is sent free of charge to members of the InterBase VAR Program. Bill Karwin, editor InterBase Software Corporation 1800 Green Hills Road, Suite 150 Scotts Valley, CA 95066 Email: [email protected] InterBase is a registered trademark of InterBase Software Corporation. InterClient and InterServer are trademarks of InterBase Software Corporation. All other trademarks are property of their respective owners.  1997 InterBase Software Corporation. All rights reserved.