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:
- 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
- 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.
PURPOSE | NEW NAME | OLD NAME |
AcceleratorLib | solidac.a | (no change) |
Control API | solidctrlstub.a | sscapir.a |
SA API | ssal2x60.a | solidsa.a |
ASCII ODBC Driver | solidodbca.a | sacl2x60.a |
UNICODE ODBC Driver | solidodbcu.a | socl2x60.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.
- 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.
- 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
- 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). - 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. - 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. - 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. - 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. - 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.
|