Index
  Home
  Manuals
  Samples

solidDB(TM) 6.0
Development Kit for Linux glibc2

Version 06.00.1046
22 September 2008
SDK Build 0038

Migrating to solidDB 6.0 from Earlier Versions

Migration from version 4.5 is described in the Release Notes. If you are migrating from earlier version, please read appriopriate sections here. This document illustrates the changes made to the product between major releases. Depending on the release you are migrating from, you have to accumulate the changes made between your release and the current release.

If you are using the Solid CarrierGrade Option, then please also read the section "HotStandby Migration", on the Welcome page for a list of important upgrade issues. Especially when you are upgrading from version earlier than 4.1, special migration scenarios have to be used.

MIGRATING FROM 4.2

If you are upgrading from Solid Database Engine 4.2, please read this section for additional information about changes between versions 4.2 and 4.5.

Solid Database Engine Product Family

Configuration Parameters

The following configuration parameters are new or changed in version 4.5.

New Parameters (Default Value Shown)

[General]

MaxUserMergeSteps=10         ;max. number of merge steps a user task may be divided to
NetBackupConnect             ;connect string to the backup server (no default)
NetbackupCopyIniFile=yes     ;netbackup copies the .ini file
NetbackupCopyLog=yes         ;netbackup copies the log files
NetbackupCopySolmsgOut=yes   ;netbackup copies solsmg.out
NetbackupDeleteLog=yes       ;netbackup deletes the source log files
NetBackupDirectory           ;remote backup directory name (no default)
NetBackupConnectTimeout  ;timeout(ms) on the Netbackup connect; default: system
NetBackupReadTimeout=60000   ;ms - network timeout on any request
ReadLevelMaxTime=0  ;time (seconds) the read level is held in READ COMMITTED, 0=not released
UseRelaxedReadLevel=Yes   ;RW/Startup
VersionedPessimisticReadCommitted=No   ;RW/Startup
WriterIOThreads=1            ;number of concurent I/O writing threads per device

[Synchronizer]

RefreshReadLevelRows=0 ;where 0 is the size of the block after which readLevel will be released if isolation is read-committed. Value of 0 equals to unlimited.

[MME]

AllowReadOverCommit=No ;RW/Startup

[SQL]

SQL.ConvertOrsToUnionsCount=0; A max. count of ORs that are converted o UNIONs, RW/Startup
CursorCloseAtTransEnd=Yes ;RO
RelaxedReadCommitted=yes ;read-only transacts need not be committed
SimpleOptimizerRules=no  ;cost estimates in query optimization are ignored

[Srv]

ConnectionCheckInterval=10 ;RW/Startup, unit: seconds
MemoryReportLimit=100MB ;RW/Startup
MemoryReportDelta=20MB ;RW/Startup
NetBackupRootDir=<working directory> ;root directory for netbackup
                              ;directories (at the NetBackup Server)
New Configuration File for Network Backup

By default, netbackup copies all the files to a single backup directory. It is, however, possible, in network backup, to explicitely specify the directories, the names and sizes of the backup files stored into the filesystem of the NetBackup Server. This is done by creating a 'backup.ini' netbackup configuration file in the netbackup directory. The netbackup configuration file includes just one section and zero or more file specifications similar to those in [IndexFile] section in solid.ini configuration file:

[IndexFile]

FileSpec_[1...N]=[path/]file name [maximum file size]
New Command Line Options
  • -xexecuteandnoexit: input_script_file
  • Used to keep an existing database running after the execution of the script file
SQL Statements

Below is a list of new and changed SQL commands.

New
  • ADMIN COMMAND 'netbackup'
  • Start netbackup
  • ADMIN COMMAND 'netbackuplist'
  • Retrieve a list of latest network backups
  • ADMIN COMMAND 'slowsql [top <limit>]'
  • Tool for detecting long-lasting statements
Changed

MESSAGE APPEND
Syntax changed, new syntax is:
[ { REFRESH | SUBSCRIBE }
publication_name[(publication_parameters)]
timeout[(timeout_in_seconds)]
[FULL]
]

Changed

These statements have changed in ways that don't violate backwards compatibility.

CREATE TABLE ...
This command now has additional optional clauses that allows users to specify referential actions

DROP TABLE
Added new clause: CASCADE CONSTRAINTS

ALTER TABLE
This command now has additional optional clauses that allows users to specify referential actions

ADMIN COMMAND 'status'
New: ADMIN COMMAND 'status netbackup' To check the status of the recent netbackup.

ADMIN COMMAND 'abort'
New: ADMIN COMMAND 'abort netbackup' To cancel the on-going netbackup
New: ADMIN COMMAND 'abort backup' To cancel the on-going local backup

SET DURABILITY ...
A new option added:

SET DURABILITY DEFAULT
To reset the session durability level to the current server default.

For more information about these commands, see solidDB SQLGuide. For information about commands related to the HotStandby feature, see also the solidDB High Availability User Guide.

New Message and Error Codes

The following new message and error codes are introduced:

  • 10095, Database, Error, Cursor is closed after isolation change.
  • 10096 ,Database, Fatal Error, Only ... kilobytes configured for M-table checkpointing, at least ... KB is needed.
  • 11040, System, Error, Password file ... cannot be opened.
  • 11041, System, Error, No password found in password file ...
  • 13193, Table, Error, Foreign key creates update dependancy loop.
  • 13194, Table, Error, Can not drop a column that is part of a foreign key ...
  • 13195, Table, Error, Update failed, READ COMMITTED isolation requires FOR UPDATE
  • 13196, Table, Error, Delete failed, READ COMMITTED isolation requires FOR UPDATE
  • 14554, Server, Error, Server does not support required Transparent Failover level.
  • 14555, Server, Error, Netbackup: conflicting usage of backup directory ...
  • 14556, Server, Error, Netbackup: no server connection string specified.
  • 25216, Driver, Error, Transparent Failover: Rollback required.
  • Code 60 is returned by solsql if a execution of a sql statement fails.
  • Code 61 is returned by solsql if a procedure call returns an error.
Note to Solid FlowEngine Users

Solid FlowEngine was deprecated as a product name starting from the 4.2 release. However, all Solid FlowEngine functionality is included in the current versions and is continuously supported (unless otherwise stated elsewhere in solidDB documentation). The following table gives corresponding old and new solidDB product naming and main product options for the earlier FlowEngine functionality:

Old naming in earlier versions: Corresponding current naming
Solid FlowEngine Standalone: Solid EmbeddedEngine
Solid FlowEngine with HotStandby: Solid EmbeddedEngine with CarrierGrade option
Solid FlowEngine with SmartFlow: Solid EmbeddedEngine with SmartFlow option
Solid FlowEngine with Accelerator: <AcceleratorLib is included in EmbeddedEngine>

MIGRATING FROM 4.1

If you are upgrading from Solid Database Engine 4.1, please read this section for additional information about changes between versions 4.1 and 4.2.

Configuration Parameters

The following configuration parameters are new or changed in version 4.2.

New Parameters (Default Value Shown)

[HotStandby]
1SafeMaxDelay=5000 ;Max delay (in ms) of 1-safe transaction
;transfer to Secondary
NetcopyRpcTimeout=30000 ;Timeout (in ms) of netcopy data transfer
SafenessLevel=2safe ;Safeness level of the HSB replication
MaxMemLogSize=8m ;max size of the in-memory HSB log
MaxLogSize=0 ;max size of the disk-based HSB log
;By default: unlimited

[SQL]
CharPadding=no ;When 'yes', values of CHAR type are
;right-padded with blanks (SQL standard)

[Srv]
TraceSecDecimals=0 ;number of second decimals in trace output
PrintMsgCode=no ;if enabled, each message in solerr.out and
solmsg.out is preceded with a unique 8-character
code

Changed Parameters

These parameters have changed in ways that don't violate backwards compatibility.
[Logging]
LogEnabled=yes
The value "no" is now legal also in the HSB configuration.
BlockSize=16K
The new system default if 16KB (other block sizes are accepted during
recovery too).

Deprecated Parameters

The following parameters have no effect:

[Accelerator]
LocalUserPriority
MergePriority
RemoteUserPriority
SyncHistCleanPriority
SyncMessagePriority

[Logging]
CommitMaxWait

SQL Statements

Below is a list of new and changed SQL commands.

New

TRUNCATE TABLE table-name
To delete all rows of a table.
START APPLICATION
To load and start a dynamically-linked application (DLA)
STOP APPLICATION
To stop a running dynamically-linked application

New ADMIN COMMANDs


ADMIN COMMAND 'memory'
Returns the server process memeory size
ADMIN COMMAND 'save parameters [<file-name>]'
Saves the current parameters values in an INI file
at a given location. If is not given,
the file 'solid.ini' is written, in the working directory.

Changed

These commands have changed in ways that don't violate backwards compatibility.

CREATE TABLE ...
This command now has additional optional clauses that
allows users to specify default values and named constraints.
ALTER TABLE
Three new clauses have been added:
ALTER COLUMN to set and remove the default value definitions
and NOT NULL constraints
ADD CONSTRAINT to add dynamically named constraints
DROP CONSTRAINT to remove named constraints
ADMIN COMMAND 'trace'
New options has been added:
ADMIN COMMAND 'trace on info <n>' where <n> is between 0 and 8
(same semantics as in the conf. parameter [SQL]Info .
ADMIN COMMAND 'trace on flowplans' to produce plans for
SQL Flow statements.
SET ...
Two new formats are introduced:
SET SAFENESS {1SAFE| 2SAFE| DEFAULT}
SET TRANSACTION SAFENESS {1SAFE| 2SAFE| DEFAULT}

For more information about these commands, see the Solid Database Engine SQLGuide. For information about commands related to the HotStandby feature, see also the Solid High Availability User Guide.

Data Types

The following new data type synonyms are available:

Synonyms for WCHAR:
"NATIONAL CHARACTER"
"NATIONAL CHAR"
"NCHAR"

Synonyms for LONG VARCHAR:
"CLOB"
"CHARACTER LARGE OBJECT"
"CHAR LARGE OBJECT";

Synonyms for LONG WVARCHAR:
"LONG NVARCHAR"
"LONG NATIONAL VARCHAR"
"NCLOB"
"NCHAR LARGE OBJECT"

Synonyms for WVARCHAR:
"NATIONAL VARCHAR"
"NCHAR VARYING"
"NVARCHAR"

Synonyms for LONG VARBINARY:
"BLOB"
"BINARY LARGE OBJECT"

Removing Unnamed Constraints

The new command

   ALTER TABLE table-name DROP CONSTRAINT constraint-name

allows you to remove certain constraints (like foreign keys and CHECK constraints) but they have to be named. The Solid versions prior to 4.2 did not support named constraints. You may be faced with a pre-existing database having unnamed constraints that cannot be removed. Fortunately, all constraints have always had internal names. If there is a need to remove an unnamed constraint, the following workaround is possible:

  1. Run the soldd utility (data dictionary export) with the new option '-x hiddennames'. With this option, the hidden names are revealed, like in:

    FOREIGN KEY("DEPNO") REFERENCES "ADDCONSTR"."DBA"."D_ORD"("DEPNO")
    -- Constraint name $$E_ORD2ORD_FORKEY_0

  2. Use the hidden name in the DROP CONSTRAINT clause. You have to use double quotes because of the illegal characters in the name:

    ALTER TABLE tab DROP CONSTRAINT "$E_ORD2ORD_FORKEY_0";

New Message and Error Codes

The following new message and error codes are introduced:

3177,Table,Error,Cannot define UNIQUE constraint
with duplicated or implied restriction.
13178,Table,Error,Constraint by name ... not found.
13179,Table,Error,Foreign key actions other than restrict are not supported.
13180,Table,Error,Constraint name ... already exists.
13181,Table,Error,Constraint check fails on existing data.
13182,Table,Error,Added column with NOT NULL must have a non-NULL default.
13183,Table,Error,Index is referenced by foreign key, it cannot be dropped.
13184,Table,Error,Primary key not found for table ... Cannot define foreign key.
13185,Table,Error,Cannot set NOT NULL on column ... that already has NULL value.
13186,Table,Error,Cannot drop NOT NULL on column ... that is used as part
of unique key.
13187,Table,Error,Cannot access M-tables over a transaction commit/abort.
13188,Table,Error,Foreign key refers to itself.
13189,Table,Error,Positioning is not supported for M-tables.
13190,Table,Fatal Error,Definition ... in file ... is not valid.
13191,Table,Fatal Error,Parameter setting ... in file ... conflicts with the
setting in database: ...
13192,Table,Fatal Error,...
14552,Server,Error,Server is in backup server mode, no connections are allowed.
23528,Procedure,Error,Application ... is already running
23529,Procedure,Error,Application ... is not running
30803,Sorter,Fatal Error,Illegal value specified for parameter: ...
30804,Sorter,Fatal Error,Sorter temporary directory: ... does not exist
30149,SRV,Message,Server emergency shutdown.
30254,DBE,Message,Database is a broken HSB copy or netcopy database.
30255,DBE,Fatal Error,Exiting from server.
30256,DBE,Fatal Error,Database must exist!
30257,DBE,Fatal Error,Database creation date is already reset!
30258,DBE,Fatal Error,Database creation time can be reset only once!
30259,DBE,Fatal Error,Error test in file ..., line ...
30785,HSB,Message,Parameter %s is set to %s.
30786,HSB,Message,Parameter %s is set to '%s'.
30787,HSB,Fatal Error,HotStandby:pri_dologskip:bad type ..., log pos ...,
log size ...
30788,HSB,Fatal Error,HotStandby:pri_hsblogcopy_write:bad type ..., log pos ...,
log size ...
30789,HSB,Fatal Error,Failed to open hot standby replication log file ...'.
30790,HSB,Fatal Error,Failed to allocate memory for HotStandby log. Max Log size is ...
30791,HSB,Fatal Error,HotStandby:solhsby:bad type ..., log pos ..., log size ...
30802,XS,Fatal Error,Failed to create a temporary file for local sorting (system errno = ...)

MIGRATING FROM 4.0

If you are upgrading from Solid Database Engine 4.0, please read this section for additional information about changes between versions 4.0 and 4.1.

Configuration Parameters

The following configuration parameters are new or changed in version 4.1.

New Parameters

2SafeAckPolicy (for Solid CarrierGrade Option)
HSBEnabled (for Solid CarrierGrade Option)
AutoPrimaryAlone (for Solid CarrierGrade Option)
CatchupSpeedRate (for Solid CarrierGrade Option)
PingInterval (for Solid CarrierGrade Option)
ImdbMemoryLimit (for BoostEngine's in-memory tables)
ImdbMemoryLowPercentage (for BoostEngine's in-memory tables)

Changed Parameters

These parameters have changed in ways that don't violate backwards compatibility.

DurabilityLevel
  This parameter now allows you to specify that the server use
  "Adaptive Durability" when using Solid CarrierGrade Option.
ConnectTimeout
  The default value has changed. The meaning has stayed the same.
ReadThreadMode
  With this parameter you can control proliferation of session
  threads in the server.
ReleaseMemoryAtShutdown
  You can force the server to release the memory allocated
  to in-memory tables, before shutting down.

Deprecated Parameters

PrimaryAlone (this Solid CarrierGrade Option parameter has been replaced by AutoPrimaryAlone)
MaxLogSize (this CarrierGrade Option parameter no longer applies)
CatchupStepsToSkip (replaced with CatchupSpeedRate)
Timeout (this CarrierGrade Option parameter is ignored)

In addition, please note that setting the
  [HotStandby]
  Connect
parameter is no longer sufficient (nor mandatory) to "turn on" HotStandby. In all other ways, the parameter still functions as it used to.

SQL Statements

Below is a list of new and changed SQL commands.

New

CREATE TRANSIENT TABLE
CREATE TEMPORARY TABLE
ADMIN EVENT

Changed

These statements have changed in ways that don't violate backwards compatibility.

ADMIN COMMAND
Some changes to some HotStandby ADMIN COMMANDs are documented in the section of the HotStandby Migration file titled "MIGRATING SYSTEMS WITH HOT STANDBY". New commands 'shutdown force', 'errorexit' and 'listerrors all' have been added.
CREATE TABLE ...
This command now has additional optional clauses that allows users to specify whether the table should be a Transient Table or a Temporary Table.
ALTER TABLE
A new clause has been added: STORE MEMORY|DISK for changing the storage type of a table. Restrictions apply, see the In-memory Database Guide.

Deprecated

ADMIN COMMAND
Some deprecated HotStandby ADMIN COMMANDs are documented in HotStandby migration.

Incompatible

ADMIN COMMAND 'hsb status {logsize | maxlogsize | transcount}'
Three of the options to the 'hsb status' command are now illegal. The other options are still legal. For details, see HotStandby migration.

For more information about these commands, see the Solid Database Engine Administrator Guide, which has an appendix that describes SQL statements. For information about commands related to the HotStandby feature, see also the Solid High Availability User Guide.

AcceleratorLib

Note that the name of the AcceleratorLib static library has changed from solfeac.a to solidac.a (since version 4.0, see the file names below).

Library File Names

The following are the names of the static libraries. The naming convention for some files has changed since the previous version of the product.

PURPOSENEW NAMEOLD NAME
AcceleratorLibsolidac.a(no change)
Control APIsolidctrlstub.asscapir.a
SA APIssal2x60.asolidsa.a
ASCII ODBC Driversolidodbca.asacl2x60.a
UNICODE ODBC Driversolidodbcu.asocl2x60.a

Other File Names

The name of the file that contains the Solid Database Engine executable was changed from
  solfe
to
  solid

If you are upgrading from version 3.7 or an earlier version, you may need to update any custom scripts etc. that you've written that use the executable name.

BOOSTENGINE

The in-memory table capability in Solid BoostEngine is backwards compatible with BoostEngine 4.0. Some new parameters have been added to support the new Memory Control feature.

Solid BoostEngine is virtually 100% backwards compatible with Solid products prior to 4.0, such as Solid FlowEngine 3.7. There are, however, a few differences between the behavior of in-memory tables and disk-based tables. Although these differences are not "backwards incompatibilities", since no previous Solid product had in-memory tables, you may nonetheless want to know what these differences are, so we have listed them here.

  1. Default locking

    Solid BoostEngine continues to default to optimistic concurrency control for disk-based tables; however, it uses pessimistic concurrency control (i.e. locking) for in-memory tables.

    For more information about concurrency control and locking, see the Administrator Guide.

  2. For in-memory tables, the maximum length of a BLOB (implemented with VARBINARY and VARCHAR data types) is limited by the "block size". (No row of an in-memory table may exceed the length of a page or "block".)

    For more information about BLOB size limitations, see the In-memory Database Guide.

MIGRATING FROM 3.7

If you are upgrading from Embedded Engine 3.52 or earlier, or from FlowEngine 3.7 or earlier, please read this section for additional information about changes between versions 3.7 and 4.0.

CONFIGURATION PARAMETERS

The following configuration parameters are new or changed.

New

  DefaultStoreIsMemory
  DurabilityLevel
  IsolationLevel
  LockEscalationEnabled
  LockEscalationLimit
  LockHashSize
  RelaxedMaxDelay
  SetTransCompatibility3

Changed

  LogWriteMode
    The old value '3' (lazylog) has no effect.
  LogEnabled
    Not surprisingly, this applies to in-memory
    tables as well as disk-based tables. (This
    is not a "change" in behavior; it's just
    something that we wanted to make explicit.)

SQL STATEMENTS

Below is a list of new and changed SQL commands.

New

  SET DURABILITY ...
  SET TRANSACTION DURABILITY ...
  SET ISOLATION LEVEL ...
  SET { READ ONLY | READ WRITE }

Changed

  CREATE TABLE ...
This command now has an additional optional clause that allows users to specify whether the table should be stored on the disk drive or in memory.
  SET TRANSACTION ISOLATION LEVEL ...
This command now has a "scope" of only the transaction that it is executed in. Previously, the command would affect all subsequent transactions. The new behavior complies with the ANSI standard for SQL. If you want to maintain the "old" behavior, read about the solid.ini configuration parameter named SetTransCompatibility3 or use the new SET ISOLATION LEVEL command (without the TRANSACTION keyword).
  SET TRANSACTION { READ ONLY | READ WRITE }
This command now has a "scope" of only the transaction that it is executed in. Previously, the command would affect all subsequent transactions. The new behavior complies with the ANSI standard for SQL. If you want to maintain the "old" behavior, read about the solid.ini configuration parameter named SetTransCompatibility3 or use the new command SET {READ ONLY | READ WRITE } command (without the TRANSACTION keyword).

For more information about these commands, see the Solid Database Engine Administrator Guide, which has an appendix that describes SQL commands.

OTHER CHANGES

  1. ADMIN COMMAND 'info XXXsize';

    Solid provides administrative commands that return the total database size, the amount of free space left in the database files, and the log size.
      admin command 'info dbsize';
      admin command 'info dbfreesize';
      admin command 'info logsize';
    Most previous versions of Solid servers reported the sizes in bytes, with a maximum limit of 2 gigabytes (GB). Solid Database Engine 4.0  reports these values in kilobytes rather than in bytes. For example, if your database is 1 gigabyte, then Solid Database Engine 4.0 will report the size as 1048576 (kilobytes) rather than 1073741824 (bytes).
  2. SET MAINMEMORY
    The SET MAINMEMORY clause of the ALTER TABLE command is no longer supported. BoostEngine does support in-memory tables, but uses different syntax of ALTER TABLE. EmbeddedEngine does not support in-memory tables at all.
  3. LogWriteMode
    In Solid Database Engine 4.0, you may set the solid.ini configuration parameter LogWriteMode to control how the server writes log information to disk. (For details about LogWriteMode, see the Administrator Guide.)

    Solid Database Engine 4.0 differs from Solid FlowEngine 3.x by not supporting one of the values specified for LogWriteMode. FlowEngine 3.x supports 4 possible values for LogWriteMode:
      ping-pong,
      write-once,
      overwrite, and
      lazylog.
    Solid does not support the "lazylog" setting for LogWriteMode. If you need functionality similar to the "lazylog" functionality, try using one of the following:
      the SET DURABILITY RELAXED statement, or
      the DurabilityLevel=1 setting in the solid.ini configuration file.
    "Relaxed" and "lazy" logging are similar.

    For more information about SET DURABILITY and the DurabilityLevel solid.ini parameter, see the Administrator Guide.

    Caution!
    If you use relaxed logging rather than strict logging, then you may lose some of the most recent transactions if an abnormal termination occurs. Note that the LogWriteMode setting has no effect if you are using relaxed durability; it applies only when durability is strict.
  4. Diskless Option

    Solid Database Engine does not include the Diskless Option. However, you can still create a diskless database server by using the SSCStartDisklessServer() function call provided in the Solid AcceleratorLib.
  5. SET TRANSACTION ...
    In previous Solid products, SET TRANSACTION commands, such as
      SET TRANSACTION ISOLATION READ COMMITTED
    applied to all subsequent transactions, not (merely) the current transaction.

    In Solid Database Engine 4.0 and later, the SET TRANSACTION commands apply only to the current transaction. If you want a setting to apply to subsequent transactions, you should use the SET command *without* the "TRANSACTION" keyword, e.g.
      SET ISOLATION READ COMMITTED;
    Alternatively, to maintain backwards compatibility without changing your code, you can set a solid.ini configuration parameter named SetTransCompatibility3. For more information about this SQL command and this configuration parameter, see the Administrator Guide.
  6. The keyword SUBSCRIBE has been replaced with the keyword REFRESH in the following commands:
      GRANT SUBSCRIBE
      REVOKE SUBSCRIBE
      MESSAGE APPEND SUBSCRIBE
    The keyword SUBSCRIBE is still accepted, but is deprecated in these commands.

    Note that the word SUBSCRIPTION is still used in commands such as EXPORT SUBSCRIPTION.

Copyright (c) 2008 Solid Information Technology, Ltd.  All Rights Reserved.