New Features In InterBase 6.5

By: Bill Todd

Abstract: Bill Todd, president of The Database Group, Inc., a database consulting and development firm reviews the new features in InterBase 6.5

New Features In InterBase 6.5

by Bill Todd

Improved Cache Management

Processor Affinity

Cancelling Executing Statements

Metadata Security

64 Bit File I/O

The ROWS Clause

Exporting Data In XML Format

Other Changes

Improved Cache Management

The probelm in prior versions of InterBase that could cause performance to decrease with cache sizes over 10,000 pages has been fixed. The cache manager has also been modified to reduce the overhead for managing a large cache. For example, the total amount of memory required for a 65,000 page cache has been reduced by almost seven megabytes. Allocation speed for large caches has also been improved and so has cache I/O efficiency. The need for threads to wait for shared page latches under heavy load has also been eliminated. Increasing the cache size is no guarantee of improved performance but it will not hurt performance and will likely help with large numbers of users as well as in other situations.

Processor Affinity

The superserver versions of InterBase can suffer from decreased performance on multiprocessor systems running Windows because Windows will continually switch the InterBase process from one processor to another. Windows versions of InterBase 6.5 include the

CPU_AFFINITY 1

directive in the ibconfig file to attach the InterBase process to the first processor. You can attach InterBase to any processor by changing the numeric value to the value for the bit that corresponds to the processor. For example, use a value of 1 for the fist processor, 2 for the second processor, 4 for the third processor and so on. This syntax will also allow you to assign InterBase to multiple processors when SMP support is available in InterBase 7.0. For example, to assign InterBase to processors 2 and 3 set CPU_AFFINITY to 6.

Cancelling Executing Statements

You can cancel any executing statement using the InterBase API isc_dsql_free_statement function shown below.

procedure TfrmMain.StopQuery;
var
 StatementHandle:   TISC_STMT_HANDLE;
 ISC_Result:        ISC_STATUS;
begin
  StatementHandle := dmMain.ibqTest.StmtHandle;
  ISC_Result := isc_dsql_free_statement(StatusVector, @StatementHandle, DSQL_CANCEL);
  if ISC_Result > 0 then IBDatabaseError;
end;

You cannot cancel the statment from the thread that is running it so if you want to use this feature run your SQL statements in a background thread. You cannot use this API call with dbExpress or the BDE because they do not provide access to the statement handle that is required as the second parameter for the call. InterBase Express does surface this property and the sample code shown above was extracted from an application using an IBQuery component to run the SQL statement in a background thread.

When you cancel an executing statement InterBase behaves as though the statement encountered an error. All changes made by the cancelled statement are undone but the transaction survives. It is up to you to commit or rollback the transaction as you choose. If you execute a statement in the ISQL command line ustility and want to cancel it just press Ctrl+C.

Metadata Security

In prior versions of InterBase you could grant and revoke rights on the system tables but these rights would not survive a backup and restore. This is not true in InterBase 6.5 and the default rights for public on all system tables in any database you create in InterBase 6.5 is SELECT. This allows all users to see the system tables but they cannot change the system tables directly. InterBase 6.5 includes three scripts that allow you to easily change the rights to the system tables. The readmeta.sql script grants SELECT rights to the system tables. The writemeta.sql script grants all rights to the system tables. The blindmeta.sql script revokes all rights from all system tables.

Restricting access to the system tables in no way limits a user's ability to make changes to other tables in the database using SQL DDL statements. The InterBase engine has been modified to make the necessary changes to the system tables on the user's behalf even though the user does not have rights to access the system tables directly.

If you revoke all access to the system tables using the blindmeta.sql script or using the SQL REVOKE statement earlier vesions of InterBase will not be able to access the database at all. The first time an InterBase 6 ODS 10 database is opened by InterBase 6.5 the ODS version will be changed to 10.1 and the rights to the system tables for public will be set to SELECT only. InterBase 6.5 can also open ODS 9 databases and will not change them in any way.

InterBase 6.0 will be able to open an ODS 10.1 database if public has at least SELECT access to the system tables. However, if the database is backed up and restored by an InterBase 6.0 server all restrictions on access to the system tables will be lost.

64 Bit File I/O

InterBase now supports 64 bit file I/O on Windows, Linux and Solaris paltforms. The size of database files is now limited only by disk size. 64 bit file I/O is only available on Linux distributions based on the 2.4 kernel. On earlier versions of Linux InterBase 6.5 will automatically use 32 bit file I/O and will behave just as IinterBase 6.0 does.

The ROWS Clause

The ROWS clause is an extension to InterBase SQL that allows you to control the number of rows affected by SELECT, UPDATE and DELETE statements. The syntax of the ROWS clause is:

ROWS <lower_value> [TO <upper_value>] [BY <step_value>] [PERCENT] [WITH TIES]

The location of the ROWS clause in the SQL clause hierarchy is:

<from_clause>
  <where_clause>
    <group_by_clause>
      <having_clause>
        <order_by_clause>
          <rows_clause>

The best way to understand what the ROWS clause can do for you is to look at some examples. All of the examples in this article use the sample Employee database that comes with InterBase. The first example shows the use of ROWS in conjunction with the ORDER BY clause to select the five records with the largest discount.

SELECT * FROM SALES
ORDER BY DISCOUNT DESC
ROWS 1 TO 5 

The ORDER BY clause sorts the returned records in descending order by DISCOUNT and the ROWS clause limits the number of rows returned to the first five. Although you can use ROWS without ORDER BY it would be unusual to do so since limiting the number of rows infers that you want the first N rows based on some order. As long as the first row you want returned is the first row in the result set you can shorten the query to

SELECT * FROM SALES
ORDER BY DISCOUNT DESC
ROWS 5

If you want all of the rows that have the same values in the fields in the ORDER BY clause as the first five rows use the WITH TIES option shown below.

SELECT * FROM SALES
ORDER BY DISCOUNT DESC
ROWS 1 TO 5 WITH TIES

This query returns eight rows instead of five because some of the values that appear in the DISCOUNT field in the first five rows also appear in other rows. Note that you must use an ORDER BY clause when you use WITH TIES.

Instead of a fixed number of rows you can also request a percentage of the rows in the result set as shown in the following query which returns the top 10% of the rows in SALES by TOTAL_VALUE.

SELECT * FROM SALES
ORDER BY TOTAL_VALUE DESC
ROWS 10 PERCENT

Another interesting use of the PERCENT option is to select an evenly distributed subset of rows. This query returns five rows with each of those rows separated from its neighbor by 20% of the rows in the table. Although this query does not use an ORDER BY clause it could. For example, to obtain an even distribution of rows geographically you could order the table by state or zip code.

SELECT * FROM CUSTOMER
ROWS 100 BY 20 PERCENT

You can also use the ROWS clause in conjunction with aggregates. The following query returns the top three sales reps based on the total value of their orders while the second query returns the top 25% of sales reps based on the total value of their orders.

SELECT SALES_REP, SUM(TOTAL_VALUE) AS TOTAL
FROM SALES
GROUP BY SALES_REP
ORDER BY 2 DESC
ROWS 3

SELECT SALES_REP, SUM(TOTAL_VALUE) AS TOTAL
FROM SALES
GROUP BY SALES_REP
ORDER BY 2 DESC
ROWS 25 PERCENT

The ROWS clause can also be used with UPDATE to give the ten highest paid employees a raise.

UPDATE EMPLOYEE
SET SALARY = SALARY * 1.01
ORDER BY SALARY DESC
ROWS 10

You can also use ROWS with DELETE to delete the ten highest paid employees. If you want to delete everyone whose salary is equal to one of the ten highest paid employees just add the WITH TIES option.

DELETE FROM EMPLOYEE
ORDER BY SALARY DESC
ROWS 10

Exporting Data In XML Format

InterBase Express now includes a new class, TIBOutputXML, that lets you easily retrieve data in XML.The following table shows the properties of TIBOutputXML. To use this class you must include the IBXML unit in your uses clause. The InterBase XML support is implemented in a separate DLL so you need not distribute it if you do not want to.

Property Description
HeaderTag A string that will be used as the header tag in the XML stream. If HeaderTag is null "<?xml version="1.0">" will be used as the default value.
DatabaseTag A string that will be used as the database tag. If this property is null the string "Database" will be used.
TableTag A string that will be used as the table tag. If this property is null the string "Table" will be used.
RowTag A string that will be used as the row tag. If this property is null the string "Row" will be used.
Flags A set property with three members.xmlAttribute = generate the XML as attributes instead of tags.xmlDisplayNull = include null values in the XML data.xmlNoHeader = do not include a header in the XML.
Stream The stream that receives the XML.

The following code shows how to export to XML and display the XML in a memo component on a Delphi form.

procedure TMainForm.ShowXMLBtnClick(Sender: TObject);
var
  IBOutputXML:    TIBOutputXML;
  Stream:         TStringStream;
begin
  IBOutputXML := TIBOutputXML.Create;
  Stream := TStringStream.Create('');
  try
    IBOutputXML.HeaderTag := '' + #10#13 + '' + #10#13;
    IBOutputXML.DatabaseTag := DatabaseEdit.Text;
    IBOutputXML.TableTag := TableEdit.Text;
    IBOutputXML.RowTag := RowEdit.Text;
    IBOutputXML.Stream := Stream;
    IBOutputXML.Flags := [];
    if AttributesCb.Checked then
      IBOutputXML.Flags := IBOutputXML.Flags + [xmlAttribute];
    if NullCb.Checked then
      IBOutputXML.Flags := IBOutputXML.Flags + [xmlDisplayNull];
    if NoHeaderCb.Checked then
      IBOutputXML.Flags := IBOutputXML.Flags + [xmlNoHeader];

    SelectData;
    OutputXML(IBSQL1, IBOutputXML);

    Stream.Position := 0;
    Memo1.Lines.LoadFromStream(Stream);
  finally
    IBOutputXML.Free;
    Stream.Free;
    IBSQL1.Close;
    EmployeeTran.Commit;
    EmployeeDb.Connected := false;
  end;
end;

procedure TMainForm.SelectData;
begin
  EmployeeDb.DatabaseName := DatabasePathEdit.Text;
  EmployeeDb.Connected := true;
  EmployeeTran.StartTransaction;
  IBSQL1.SQL.Clear;
  IBSQL1.SQL.Add(SQLEdit.Text);
end;

Other Changes

InterBase 6.5 also includes InterClient 2.5, the latest version of the InterBase JDBC driver, which has been updated to be consistent with JRE 1.3. This means that you must have Java 2 installed to use InterClient 2.5.

Past versions of InterBase have transmitted VARCHAR fields from the server to the client padded to their full length with spaces. This could cause performance problems on slow or heavily loaded networks. This behavior has been changed so that only the actual data in the VARCHAR field is transmitted.

IBConsole has also been enhanced to include a separate Indexes node for each database. Selecting this node displays information about all of the indexes in the database grouped by table. The index name, sort order, uniqueness, columns included in the index, column order and whether the index is active or not. For indexes that are part of a foreign key constraint the name of the primary index referenced by the foreign key is also shown.

Conclusion

For a minor version upgrade InterBase 6.5 offers a wealth of new features. With 64 bit file I/O to make working with large databases easier, improved cache management for better performance, metadata security to protect your database design, the ROWS clause to control of the number of records affected by DML statements and XML export for easy integration with Web applications this is definitely an upgrade worth getting.

Server Response from: ETNASC04