InterBase XE Readme

The topics below cover the critical information you need to implement the 64-bit application.

    Compatability Issues

• The local and remote connections from older clients are expected to work with newer 32-bit/64-bit servers; and vice-versa.
  
  
• IBMgr.exe and IBConsole.exe continue to be 32-bit applications. But they are expected to work with 32-bit and 64-bit servers.
  
  
• This version supports existing ODS 12.x and ODS 13.x databases that were introduced in previous releases of InterBase.
  
  
• You can expect to move databases between 32-bit and 64-bit kits of InterBase (with this version) as long as they support compatible ODS versions.
  
  

    Client Library Name Changes for 64-Bit DLL's

    The following table displays the new client library names for the 64-bit DLL's.

Library Name	Location	Notes
ibclient64.dll	<interbase>/bin	This is a new InterBase client DLL for native 64-bit applications. It is being used by 64-bit InterBase command-line tools currently, and will need to be deployed with 64-bit applications built by customers (in place of gds32.dll which is for the 32-bit target).
ibxml64.dll	<interbase>/bin	This is a new InterBase XML DLL for native 64-bit applications. It will need to be deployed with 64-bit applications built by customers (in place of ibxml.dll which is for the 32-bit target) if they are using the InterBase XML api.
ib_util64.dll	<interbase>/bin	This is a new InterBase UTILS DLL for native 64-bit applications. It will need to be deployed with 64-bit applications built by customers (in place of ib_util.dll which is for the 32-bit target).
ibclient64_ms.lib	<interbase>/SDK/lib_ms	Import library for building 64-bit applications targeting the ibclient64.dll.
ibxml64_ms.lib	<interbase>/SDK/lib_ms	Import library for building 64-bit applications targeting the ibxml64.dll.
ib_util64_ms.lib	<interbase>/SDK/lib_ms	Import library for building 64-bit applications targeting the ib_util64.dll.

JDBC Driver Updates for Blob/Clob Support

New interfaces have been implemented which support the following:

JDBC Name	InterClient Name	Exceptions/Comments
java.sql.Blob	interbase.interclient.Blob	For all API the parameter pos is ignored and assumed to be 1, hence the complete BLOB is returned. For example in the method OutputStream setBinaryStream(long pos) the parameter pos is ignored. The same is true for all other methods which take the "pos" parameter. public long position(byte[] pattern, long start) and public long position (java.sql.Blob blob, long start) are not supported.
java.sql.Clob	interbase.interclient.Clob
java.io.inputStream	interbase.interclient IBBlobInputStream	Special implementation of the java.io.inputStream for InterBase Blob (and Clobs). Use the read() methods from this stream to access the underlying data.

The following methods have been implemented in this release:

In the java.sql.PreparedStatement class

• public void setObject (int parameterIndex, Object x) now works when parameter x is of type java.io.Inputstream.
• All variations of the setCharacterStream () method are implemented
• All variations of the setAsciiStream() and setBinaryStream() methods are implemented
• all variations of method setBlob() and setClob() are implemented.
• Method isClosed() is implemented.

In the java.sql.Result class

• All variations of the getCharacterStream () method are implemented
• All variations of method getBlob () and getClob() are implemented

Stronger Password Protection

This release implements stronger password protection on InterBase databases to comply with password requirements from the Payment Card Industry - Data Security Standard (PCI DSS). This additional functionality supports a longer effective password length, resulting in stronger password protection.

Requirements/Constraints

• This design supports server-wide user authentication as manifested by the USERS table of the security database, configured with the IBCONFIG.ADMIN_IB property parameter, which defaults to the file admin.ib.
  
  
• The design also supports EUA databases. As with the non-EUA databases, it also has to be explicitly enabled by the owner/administrator. Please note that the USERS table in admin.ib has RDB$USERS as the counterpart in EUA databases; so the earlier references have to be compatible with EUA database references.
  
  
• For EUA databases, the database metadata upgrade to 13.2 is automatic (for ODS 13.x databases); the database owner does not have to create the PASSWORD_DIGEST domain or modify the RDB$USERS table explicitly to set 'DES-CRYPT' for existing user accounts. The explicit actions are required only for admin.ib since the user account table on that database is a user table and not a system table.
  
  
• A plaintext password length of 32 bytes is supported in this release, up from 8 bytes in earlier versions of InterBase.
  
  
• An updated version of IBConsole is present in the kit. This version does not show the “Default” buttons in the database/server login screens.
  
  
• A batch script (changepassword.bat) is now provided in the /bin directory to update the SYSDBA account password post-install.
  

        Getting Started
The DES-CRYPT password algorithm has been replaced with a modern cryptographic hash function that is more widely accepted by organizations in private industry and government. The design uses SHA-1, which generates a fixed length 160-bit hash.

1. Before starting, it is strongly recommended that you backup your old admin.ib from the current installation before installing the new InterBase. This allows you to restore it, if needed.

2. If you are upgrading to a newer version you may want to continue using your admin.ib from an earlier InterBase. If so, enable stronger password protection on that admin.ib by using the following SQL commands after IB has been installed on the server:

isql admin.ib -user SYSDBA -pass xxxxxxx
sql> ALTER DATABASE SET PASSWORD DIGEST 'SHA-1';
sql> CREATE DOMAIN PASSWORD_DIGEST AS VARCHAR(16) CHARACTER SET ASCII;
sql> ALTER TABLE USERS ADD PASSWORD_DIGEST PASSWORD_DIGEST;
sql> UPDATE USERS SET PASSWORD_DIGEST = 'DES-CRYPT';
sql> COMMIT;

NOTE:   If it is a completely new installation of InterBase you do not need to run the SQL commands.

The ALTER DATABASE command can only be run by the database owner or SYSDBA. This command modifies RDB$DATABASE.RDB$PASSWORD_DIGEST to the string value "SHA-1". This means that all new password hash generation for new or existing user accounts in the USERS table will use the SHA-1 hash function.

The password hash function can be reset to DES-CRYPT using the same DDL:

ALTER DATABASE SET PASSWORD DIGEST 'DES-CRYPT';

The admin database is now prepped so that new user accounts or modifying the password of existing accounts will generate SHA-1 password hashes against plaintext passwords up to an untruncated length of 32 significant bytes.

GSEC [add | modify], IBConsole, and the IB Services API support the SHA-1 password hash algorithm. Any of these tools can be used to maintain the passwords of server-wide user accounts. If an existing user account has had its password changed then that user must log in to the server using the new IB client library.

Caution: There will be backward compatibility problems if the converted admin.ib database is backed up and restored by an older IB engine after the password hashes have been converted to SHA-1. Older IB engines will not understand the different password hashes and will cause unrecoverable login errors.

Larger Database Cache Settings for 64-bit InterBase

There is now a larger database cache setting for 64-bit InterBase. The limit for the 64-bit engine is 75 million pages, as compared to 750K pages for 32-bit engines.

EXECUTE STATEMENT for Stored Procedures

InterBase XE now provides support for the EXECUTE STATEMENT functionality.

        User Interface/Usability
This feature enhances the InterBase Stored Procedure language. Once this is implemented, Stored Procedure developers can embed three variations of EXECUTE STATEMENT within their Stored Procedures. The variations depend on the number of rows returned from the EXECUTE STATEMENT command. The three cases are: No rows or data returned; One row of data returned; and Variable number of rows returned.

 
No Rows or Data Returned

EXECUTE STATEMENT <statement>

<statement>         ::= a SQL statement returning no rows of data 

Example:

CREATE PROCEDURE EXEC_STMT_NO_RET (proc_name varchar(20))
AS
DECLARE VARIABLE EMPNO INT;
DECLARE VARIABLE EXECSTMT;
BEGIN
   SELECT MAX(EMP_NO) from EMPLOYEE into EMPNO;


   EXECSTMT = 'EXECUTE PROCEDURE' || proc_name || '( ' || cast (EMPNO as varchar(10)) || ')';

   EXECUTE STATEMENT EXECSTMT;

END

One row of data returned

EXECUTE STATEMENT <select-statement> INTO :<var>[, :<var>..]

<select-statement>::=         SQL statement returning one or no rows of data

<var>      ::=         valid procedure variable, the ":" is optional.


Example:

CREATE PROCEDURE EXEC_STMT_SINGLETON (TABLE_NAME VARCHAR(50))
AS
DECLARE VARIABLE MAXEMPNO

BEGIN
EXECUTE STATEMENT 'SELECT MAX(EMP_NO) FROM' || TABLE_NAME INTO :MAXEMPNO;


SUSPEND

END

Any number of data rows returned

FOR EXECUTE STATEMENT <select-statement> INTO :<var> [, :<var> ..]

DO <compound-statement>

<select-statement>::=         SQL statement returning one or no rows of data


<var>            ::= valid procedure variable, the ":" is optional.

Example:

CREATE PROCEDURE EXEC_STMT_ANY (TABLE_NAME VARCHAR(50), INT_FIELD INTEGER)
RETURNS
(INT_RETVAR INTEGER)
AS
DECLARE VARIABLE IFIELD INTEGER
BEGIN

FOR EXECUTE STATEMENT

'SELECT' || INT_FIELD || 'FROM' || TABLE_NAME INTO :IFIELD
   DO

      IF (IFIELD = 0) THEN
         INT_RETVAR=0;
      ELSE
         INT_RETVAR = INT_RETVAR+IFIELD;
   SUSPEND;

END

New Error Messages Added
The following table displays the new error messages added:

SQL Code	Error Number	Error Message
-204	335544850	EXECUTE STATEMENT could not prepare statement : <string>
-204	335544851	SQL statement invalid as it returns no records. SQL : <string>
-204	335544852	Parameter mis-match for the statement : <string>
-204	335544853	Could not execute statement : <string>
-204	335544854	EXECUTE STATEMENT fetch error
-204	335544855	EXECUTE STATEMENT in this form must return single row, not multiple rows.
-204	335544857	Sql statement not allowed in EXECUTE STATEMENT : <string>

Requirements/Constraints
Please note the following when using EXECUTE STATEMENT:

• The Statement is "prepared" every time it is executed which will affect the performance of the Stored Procedure.
  
  
• No checks are done on the statement when the procedure is created; dependency checks are not done when the procedure is created, also the checks for existence of tables or column names referred to in the execute statement are not performed. All these checks will be done at execute time and will result in errors if an error condition occurs.
  
  
• The feature can be used to perform DDL operations.
  
  
• All statements are executed based on the privileges of the user executing the Stored Procedure.
  
  
• SQL statements, "COMMIT", "COMMIT RETAIN", "ROLLBACK", "ROLLBACK RETAIN" and “CREATE DATABASE” are not supported with EXECUTE STATEMENT. These statements return the error code isc_exec_stmt_disallow.

Migration Issues
This feature is only available in InterBase XE and is not backward compatible.

Database Fast Sweep

Sweeping a database is a systematic way of removing outdated records. Periodic sweeping prevents a database from growing too large. In the past sweeping slowed system performance and users disabled the automatic database sweep function because of the impact on product operations.

InterBase databases periodically need to be swept. Otherwise the main memory allocated for each transaction's bitmap increases to the point where performance becomes unacceptable. The longer sweep takes to complete, the more main memory requirements increase for starting new transactions.

With the implementation of the fast sweep optimization in InterBase XE, the memory allocation issue has been mitigated. The user has the option to configure their databases for automatic sweep. In cases where large databases have large archival or infrequently modified tables, a database sweep will have minimal impact on the performance of running transactional operations.

User Interface/Usability

There is no new user interface or action required by the user to enable this functionality. Manual sweep initiated by the GFIX command line tool, IBConsole, or programmatically, as well as automatic sweep configuration on a database, use the fast sweep mechanism.

Requirements and Constraints

• Only ODS 15 and later databases can perform fast database sweeps.
  
  
• The effectiveness of a fast sweep is directly proportional to the fraction of database data pages that have been modified since the last sweep. If every data page has been changed, fast sweep is no faster than the former methodology. If very few pages are changed, fast sweep is nearly instantaneous. If half the pages were updated, fast sweep is then half the former sweep time.

Migration Issues

A database needs to be created or backed up and then restored to ODS15.

Table-Specific Blocking Factor

The term blocking factor is used to denote the number of records stored in a block. InterBase employs a single database-wide blocking factor that maximizes the number of rows that can be stored on a data page.

In InterBase XE, ODS 15 databases introduce table-specific blocking factors that optimize how many rows can be stored on a data page and minimizes the row number assigned to a row. In addition, the engine minimizes the size of a number of run-time data structures. This is especially important as tables storing a very large number of rows and accessed by a large number of database connections can cause excessive memory consumption.

User Benefit

Databases with tables containing a large number of rows or is expected to grow to a large number of rows benefit from table-specific blocking factors. It allows those tables to store more rows in the same record number space, while using less memory for run-time retrieval data structures.

User Interface/Usability

There is no user interface or any action required by the user to enable this functionality. The table-specific blocking factors are set automatically after a database restore.

The blocking factor values for individual tables can be observed in the system columns:

RDB$RELATIONS.RDB$DATA_BLOCKING_FACTOR

RDB$RELATIONS.RDB$BLOB_BLOCKING_FACTOR.

If a table does not have a table- specific blocking factor, this system column will query as NULL. Each time a database restore is performed, these blocking factors are retuned for the actual row data restored. Therefore, it is possible that these values will change over time as the database is modified and is backed up and restored.

Requirements and Constraints

Only ODS 15 and later databases have table-specific blocking factors.

Not all tables are capable of having a table-specific blocking factor.

If a table has Blob columns and no indexes defined, then that table uses the database-wide blocking factor as its Blob blocking factor.

System tables, views, external tables, and temporary tables do not use table-specific blocking factors.

A table-specific blocking factor cannot be modified in-place once it has been set by database restore.

Migration Issues

If an ODS 15 database has been created and loaded with data, taking a backup and restoring will set the table-specific blocking factors.

Larger Index Key Segment Size

With the ODS 15 databases the maximum index key size limit is increased. Now larger column data can use this for both single-byte character sets and multi-byte (such as UTF8) columns.

Because InterBase XE supports UTF8 and multiple other multi-byte character sets, the limit has been increased. For example, a single-column key using 4-byte UTF8 character would calculate to 1020/4 = 254 UTF8 characters with a 4KB page size.

ODS 15 databases automatically allow index definitions where the underlying key size is now a factor of the database page size.

• An index key can now be up to 4 bytes less than 1/4th the page size.
  
• By default, InterBase databases are created with a 4KB page size. This can be overridden up to 16KB page size by the database developer.
  
• The 4KB page size database would allow indexes that can accommodate 1020 bytes per key.
  
• A 16KB page size can accommodate 4092 bytes per key and so on.
  

Requirements and Constraints

Databases created with engines enabled with this functionality cannot be moved back to older versions of InterBase.

Also a database restore to a smaller page size will fail if indexes with a large key size cannot fit within limit specified above.

No user interface or actions are required by the user to enable this functionality. Each time a database restore is performed, the indices are recreated.

Migration Issues

Only ODS 15 and later databases have support for larger index keys. If you want to use this facility, restore your database to ODS 15. Other indices that use a smaller size than 252 bytes continue to have the same on-disk storage without any penalty.

Migration Issues for InterBase XE

• Be sure to back-up all databases, including the security database, before uninstalling the previous version.
  
  
• We recommend that you upgrade your InterBase clients to 10.0.292 or above if you are targeting a SUSE 11 SP1 (or above) server. There are known problems with older clients waiting for events from a SUSE 11 SP1 server.
  
  
• Back-up the ibconfig file if it has been customized.
  
  
• This version creates new databases with ODS version 15.
  
  
• Please only provide 64-bit UDF libraries for a 64-bit edition, if any, of InterBase. Note that the 64-bit server will not be able to load any 32-bit UDF libraries that you may have. The UDF library that is provided (OOTB, ib_udf) is already built for 64-bit and is installed with the product.
  
  
• InterBase XE increased the number of records that can be stored in a table. The current count has 32-bit limitations. If you want to get a count of the number of record values beyond 32-bit, you need to take the following steps:
  
  

  If a table does have more than 2G rows then the developer must code a CAST(COUNT(*) AS NUMERIC(18,0)) to get a 64-bit count.

• InterBase XE implemented stronger password protection. If you have older InterBase clients (locally or from remote systems) communicating with this installation, please note the following while upgrading your system to InterBase XE:
  
  
  • If you have an existing user authentication database (from previous InterBase versions), use that database file (copied to the InterBase XE folder) and authentication for local/remote clients will work correctly.
    
    
  • If you use the new admin.ib from InterBase XE, note that this database has "SHA-1" strength passwords (by default). If you want to continue using the (weaker) DES password algorithm (previous InterBase releases), use the ALTER DATABASE command referred to in the Stronger Password Protection topic.
    
    
  • If you want to use "stronger password length" (provided in InterBase XE), you will need to recreate your user accounts AND install the new InterBase clients in remote machines connecting to this InterBase XE server. This is because the "old" InterBase clients are not capable of computing the "SHA-1" passwords and thus pass in the "DES" strength passwords which in turn don't match what the InterBase XE server expects. This gives you the error "Your user name and password are not defined..."
    
    
• InterBase XE updated SSL parameter names. The old OTW client properties have been replaced by new OTW properties. Please refer to the OperationsGuide.pdf "Setting up OTW Encryption" and Table 5.2 located in Chapter 5.
  
  
• Restoring database results in the error "unassigned code".
  When restoring a database with InterBase XE, get the error unassigned code. With databases with a long lineage or databases backed up with InterBase 2009 and restored with InterBase XE, each case has different metadata security settings. So when selecting a system table (for example: RDB$RELATIONS) you get the error message: no permission for read/select access to table RDB$RELATIONS by user SYSDBA.
  
  This error occurs with databases which have these criteria:
  
  
  • Originally created with a version of InterBase prior to InterBase 6.5.
    
    
  • Backed up with a version of InterBase prior to InterBase 2009.
    
    
  • Readmeta.sql has not been previously applied.
  
  This behavior is exhibited due to stricter enforcement of meta data rights in InterBase XE during the restore of a database.
  
  Solution:
  To resolve this problem execute readmeta.sql against your database before you back it up. readmeta.sql may be found in \examples\security. You can execute readmeta.sql against your database using isql or IBConsole.
  
  Explanation:
  The problem occurs with databases that have a long lineage. The two cases are (1) a database has a restore history of IB6->IB7->IB2007->IB2009->IBXE; and (2) a database backed up and restored as IB2009->IBXE. Each case has different metadata security settings. The first instance never had metadata security because it originated from IB6. However with the second instance, it was created (not restored) by IB2009 with a full complement of security privileges for all system tables.
  
  With the first case, the database was backed up and restored and with each succeeding release, the new release would install privileges for the new system tables in that release (think RDB$USERS, RDB$ENCRYPTIONS, RDB$ROLES, etc.). But it couldn't alter the original system tables because it had no way of knowing if the database owner had already altered their security privileges. For example, a user might have revoked all privileges to RDB$TRIGGERS and RDB$PROCEDURES to conceal their trigger and stored procedure code.
  
  Also, in the first case, a SYSDBA may have run readmeta.sql years ago and refined the metadata from that baseline to a custom security profile. InterBase cannot override that customization by automatically resetting it after the XE restore. InterBase XE can't assume that every database it restores should unconditionally install the default metadata privileges because it doesn't know the history of individual databases.
  
  So it is recommended to run readmeta.sql, which sets the default or starting point for configuring it the way you want it. This advice is independent of whether you are migrating to XE.
  
  Example using isql:
  
  isql "path to database" -user sysdba -password masterkey -i readmeta.sql
  
     Executing readmeta.sql with IBConsole
  
  
  1. Connect to your database with IBConsole.
     
  2. Tools | Interactive SQL ...
     
  3. Query | Load Script, select readmeta.sql, click OK.
  
  
  
  
• Error: No Permission for read/select access to table RDB$XXXX by user SYSDBA
  Databases originally created before InterBase 6.5 may have the error: no permission for read/select access to table RDB$XXXX by user SYSDBA with InterBase XE.
  
  Solution:
  InterBase XE enforces tighter meta data security and this error may result from doing meta data operations on databases originally created with versions of InterBase prior to version 6.5. Meta data operations involve requesting information about system objects such as listing system objects or updating them and using the Performance Monitor.
  
  To resolve this error you need to perform two similar operations. The first operation grants rights to system tables. To do so, execute readmeta.sql from the examples\security folder in your InterBase install directory.You can execute readmeta.sql against your database using isql or IBConsole.
  
  Example using isql:
  
  isql "path to database" -user sysdba -password masterkey -i readmeta.sql
  
    Executing readmeta.sql with IBConsole
  
  1. Connect to your database with IBConsole.
     
  2. Tools | Interactive SQL ...
     
  3. Query | Load Script, select readmeta.sql, click OK.
  
  
  
  Second, you need to grant rights to system temorary tables if you are going to do performance monitoring. Due to potential security concerns, most installations will want to grant rights for system temporary tables only to sysdba and the database owner, which is what is presented below. If you wish for all users to be able to view system temporary tables, modify this example to GRANT TO PUBLIC. Some installations will want only specific users to have rights, in which case a more customized script may be needed.
  
  To grant rights for system temporary tables, save the following as a text file, then execute it the same as readmeta.sql above.
  
    create procedure granttmp as
      declare variable stmt varchar(1024);
      declare variable ownername varchar(66);
      declare variable tablename varchar(66);
    begin
        select rdb$owner_name from rdb$relations where rdb$relation_name = 'RDB$RELATIONS'
        into :ownername;
    for select rdb$relation_name from rdb$relations where rdb$system_flag>0   and rdb$relation_name starts with 'TMP$' into :tablename do
    begin
      stmt = 'grant all on ' || tablename || ' to sysdba';
      execute statement stmt;
      stmt = 'grant all on ' || tablename || ' to ' || ownername;
      execute statement stmt;
    end
  end;
  execute procedure granttmp;
  drop procedure granttemp;
  commit;
  exit;
  

Known Issues

Windows Error Reporting (WER) dialog pops up intermittently if and when an InterBase server crashes.

Resolution: We are working on fixing any crashes that we are aware of. In the meantime you can disable the Windows Error Reporting dialog from popping up by modifying the Windows Registry thus. Set the registry attribute HKEY_CURRENT_USERS\Software\Microsoft\Windows\Windows Error Reporting\DontShowUI value to 1 to disable. This is as per recommendation of MSDN article http://msdn.microsoft.com/en-us/library/bb513638(VS.85).aspx We may address this configurable option from within ibserver.exe in future builds by informing WER to disable this only for InterBase server binary.

Resolved Defects

The following are resolved defects for InterBase XE. Additional bug fixes are listed in the Release Notes.pdf.