|
  
Appendix
E . What's New in DBMaker 3.5
 E.1
Asynchronous Table Replication
E.2
Heterogeneous Table Replication
E.3
Text User-Defined Functions
E.4
Update/Delete with Subquery
E.5
Load and Unload Stored Procedures
E.6
Simplified Chinese (GB) Support
E.7
UNC Names For File Objects
E.8
ANSI-Compatible Referential Integrity Definition
E.9
ANSI-Compatible String Concatenation Operator
E.10
Remove Space Padding Option
E.11
Enhanced Backup Server
E.12
Enhanced Query Optimizer
E.13
Enhanced Database Statistics
E.14
I/O Daemon
E.15
Checkpoint Daemon
E.16
Reset Serial Number
E. What's New in DBMaker 3.5
DBMaker
3.5 adds several new features to those found in previous versions of
DBMaker. This appendix provides an introduction to these new features,
with a brief description of how each is used.
E.1 Asynchronous Table Replication
A table
replication creates a full or partial copy of a table in a remote
location. This allows users in remote locations to work with a local
copy of data. The local copy remains synchronized with the databases
in other locations. This way each database can service data requests
immediately and efficiently, without having to go to another machine
over a slower network connection. This is not the same as backing up
the database to a remote location, since the synchronization is done
on a transaction-by-transaction basis by the DBMS itself, without any
intervention from users.
There are
two primary types of table replication: synchronous and asynchronous.
Synchronous table replication modifies the remote table at the same
time it modifies the local table, while asynchronous table replication
stores changes to the local table and modifies the remote table based
on a schedule.
Asynchronous
table replication in DBMaker uses a file known as a transaction log
to replicate data to the remote table. Modifications to the local table
are stored in the transaction log, and are replicated to the remote
table according to a predefined schedule. Using the transaction log
enables DBMaker to treat the local transaction and the remote transaction
independently, allowing you to update local tables normally even if
the remote connection is not available. This allows asynchronous table
replications to tolerate network and remote database failures, since
the replication will keep trying until any failures are corrected.
For more
information on asynchronous table replication, refer to the following
commands in the SQL Command and Function Reference:
ALTER REPLICATION ADD REPLICATE
ALTER REPLICATION DROP REPLICATE
ALTER SCHEDULE
CREATE REPLICATION
CREATE SCHEDULE
DROP REPLICATION
DROP SCHEDULE
RESUME SCHEDULE
SUSPEND SCHEDULE
SYNCHRONIZE SCHEDULE
There are
two new configuration keywords introduced in DBMaker 3.5 for use with
asynchronous table replication: DB_ATRMD and RP_LGDIR. These keywords
are used in the dmconfig.ini configuration file.
The DB_ATRMD
keyword specifies whether you should start the distributor daemon for
a database. If you do not start the distributor daemon, that database
cannot be the source for asynchronous table replications.
The RP_LGDIR
keyword specifies the directory where DBMaker should put transaction,
message, and error log files for asynchronous table replication. You
should not manually remove the transaction log files, the distributor
message log (ATRP.LOG), or the distributor error log (ATRERROR.LOG).
There are
also some new system catalog tables and some modifications to existing
ones. The new system catalog tables are: SYSTRPDEST, SYSTRPPOS, and
SYSTRPPOS. The modified system tables are: SYSTABLE, SYSPUBLISH, and
SYSSUBSCRIBE. For more information on these system catalog tables, see
Appendix C, System Catalog Reference.
E.2 Heterogeneous Table Replication
DBMaker
not only allows asynchronous table replication to other DBMaker databases,
but also to Oracle and Microsoft SQL Server databases. This type of
replication allows DBMaker to coexist with other databases in a heterogeneous
environment, and is known as heterogeneous table replication.
Since DBMaker needs to preprocess the replicated data before sending
it to a third-party remote database, you must specify the type of DBMS
you are replicating to when you create a schedule in a heterogeneous
environment. You do this with the ORACLE and MICROSOFT keywords, where
ORACLE indicates a remote Oracle 8.0 database, and MICROSOFT represents
a remote Microsoft SQL Server 7.0 database.
When you
create a heterogeneous table replication, you cannot use the CLEAR DATA,
FLUSH DATA, or CLEAR AND FLUSH DATA keywords. You must manually delete
or insert data in the third-party remote database to put the table in
its initial state before the replication begins. In addition, you cannot
do schema checking on the third-party remote database. You must take
responsibility for schema checking yourself, and ensure that columns
and data types in the remote table are compatible with the columns and
data types in the local table. When creating a schedule for a heterogeneous
table replication, you must use the WITH NO CHECK keywords to prevent
DBMaker from performing schema checking. (See the description for the
WITH NO CHECK keyword later in this section.) Finally, due to the way
DBMaker makes use of the ODBC Driver Manager to perform asynchronous
table replication, the DBMaker server must be located on Windows NT,
and the definition of the remote database name cannot include a link
name. The third-party remote databases may be located on either Windows
or UNIX platforms.
For more
information on heterogenous table replication, refer to the following
commands in the SQL Command and Function Reference:
CREATE SCHEDULE
DROP SCHEDULE
RESUME SCHEDULE
SUSPEND SCHEDULE
SYNCHRONIZE SCHEDULE
E.3 Text User-Defined Functions
DBMaker
3.5 now supports several new user-defined functions you can use with
full-text searching. These new functions allow you to highlight matching
text, count the number of times matching text appears, show a specific
instance of the matching text, calculate the length of a binary large
object (BLOB), and extract the title from a HTML file. To support these
functions, user-defined functions can now accept any large object type
(such as LONG VARCHAR, LONG VARBINARY, or FILE) as the input data. The
new user-defined functions are: HIGHLIGHT, HITCOUNT, HITPOS, BLOBLEN,
and HTMLTITLE.
The HIGHLIGHT Function
The HIGHLIGHT
function accepts text as input, and outputs that text with a search
pattern highlighted. The function prototype is:
HIGHLIGHT( Text LONG VARCHAR,
Pattern CHAR,
Sensitive INTEGER,
PreTag CHAR,
PostTag CHAR) RETURN LONG VARCHAR
|
Text - This parameter can be text of the CHAR, LONG VARCHAR,
or FILE data types. DBMaker will automatically convert any of these
data types to the LONG VARCHAR data type expected by the function.
Pattern - This parameter may contain text and the boolean operators
& (AND), | (OR), and ! (NOT). The function will highlight all simple
patterns except NOT (!).
Sensitive - This parameter is a boolean value. A value of 0 indicates
that the Pattern parameter is not case sensitive, and any other value
indicates that it is case sensitive.
PreTag - This parameter specifies the tag that will be used in
front of all instances of Pattern found in the text. The tag has a maximum
size of 10000 bytes.
PostTag - This parameter specifies the tag that will be used
after all instances of Pattern found in the text. The tag has a maximum
size of 10000 bytes.
Return Value - The return value is the original text with all
instances of Pattern highlighted by placing the PreTag and PostTag text
around the pattern text.
The following
example searches for all occurrences of Intel or AMD and highlights
them by placing the matching text between the `<<' and `>>'
tags.
SELECT HIGHLIGHT(content, `Intel|AMD', 0, `<<', `>>')
FROM news WHERE content MATCH `Intel|AMD'
|
The HITCOUNT Function
The HITCOUNT
function accepts text as input, and outputs that number of times a pattern
appears in that text. The function prototype is:
HITCOUNT( Text LONG VARCHAR,
Pattern CHAR,
Sensitive INTEGER) RETURN LONG VARCHAR
|
Text - This parameter can be text of the CHAR, LONG VARCHAR,
or FILE data types. DBMaker will automatically convert any of these
data types to the LONG VARCHAR data type expected by the function.
Pattern - This parameter may contain text and the boolean operators
& (AND), | (OR), and ! (NOT). If you search for TEXT1 AND TEXT2
in the pattern, DBMaker will only count a match if the text matches
both TEXT1 and TEXT2. If you search for TEXT1 OR TEXT2 in the patter,
DBMaker will count a match if the text matches either TEXT1 or TEXT2.
Sensitive - This parameter is a boolean value. A value of 0 indicates
that the Pattern parameter is not case sensitive, and any other value
indicates that it is case sensitive.
The following
example searches for all occurrences of Intel or AMD and returns the
number of time it finds both.
SELECT HITCOUNT(content, `Intel|AMD', 0)
FROM news WHERE content MATCH `Intel|AMD'
|
The HITPOS Function
The HITPOS
function accepts text as input, and returns the position of the nth
match of the search pattern in the text. The function prototype is:
HITPOS( Text LONG VARCHAR,
Pattern CHAR,
Sensitive INTEGER,
n INTEGER,
ReturnType CHAR) RETURN LONG VARCHAR
|
Text - This parameter can be text of the CHAR, LONG VARCHAR,
or FILE data types. DBMaker will automatically convert any of these
data types to the LONG VARCHAR data type expected by the function.
Pattern - This parameter may contain text and the boolean operators
& (AND), | (OR), and ! (NOT).
Sensitive - This parameter is a boolean value. A value of 0 indicates
that the Pattern parameter is not case sensitive, and any other value
indicates that it is case sensitive.
n - This parameter specifies the occurrence in the text that
you want to return the position of. The value of n starts at 1.
ReturnType - This parameter specifies the type of position value
to return. A value of 0 indicates that the return value will be the
offset of the matching text from the beginning of the text. A value
of 1 indicates that the return value will be the offset of the matching
text from the end of the text. A value of 2 indicates that the return
value will be the length of the matching text (end offset - beginning
offset).
The following
example returns a value of 5 when the input text is `a b A c'. The value
5 is the offset from the beginning of the text of the letter `A'.
SELECT HITPOS(src, `A', 1, 1, 0) FROM t1
|
The following
example returns a value of 3 when the input text is `a b A c'. The value
3 is the offset from the beginning of the text of the letter `b'.
SELECT HITPOS(src, `A&B', 0, 2, 0) FROM t1
|
The following
example returns a value of 7 when the input text is `a b A c'. The value
7 is the offset from the beginning of the text of the letter `c'.
SELECT HITPOS(src, `!a&c', 0, 1, 0) FROM t1
|
The BLOBLEN Function
The BLOBLEN
function calculates the length of a binary large object (BLOB). The
function prototype is:
BLOBLEN(Object LONG VARBINARY) RETURN INTEGER
|
Object - This parameter is the binary large object you want to
find the length of.
Return Value - This parameter specifies the length in bytes of
Object.
The following
example returns the length of the input BLOB in bytes.
SELECT BLOBLEN(blob_column) from t1
|
The HTMLTITLE Function
The HTMLTITLE
function extracts the title from a HTML page. The function prototype
is:
HTMLTITLE(Object LONG VARBINARY) RETURN VARCHAR(1000)
|
Object - This parameter is the HTML file you want to extract
the title from.
Return Value - This parameter specifies the title of an HTML
page.
The following
example returns the title of the input HTML page.
SELECT HTMLTITLE(file_column) from t1
|
E.4 Update/Delete with Subquery
You can
now use a subquery in update and delete operations. Delete operations
can use subqueries in the WHERE condition, and update operations can
use subqueries in both the WHERE condition and the SET value.
The following
examples show a subquery in the WHERE condition. You can use any valid
SELECT statement as a subquery in the WHERE condition.
UPDATE t1 SET c1=c1+ WHERE c2 IN (SELECT c2 FROM t2 WHERE c3=1)
DELETE t1 WHERE c2 IN (SELECT c2 FROM t2) AND c3>10
UPDATE t1 SET c1=3 WHERE c2
IN (SELECT c2 FROM t2 WHERE c3
IN (SELECT c3 FROM t3))
|
The following
examples show a subquery in the SET value of UPDATE statements. You
can use any valid SELECT statement as a subquery in the SET value, provided
the result from the subquery is only one column and only has a single
rows or no rows as the result. You cannot perform any operations on
the subquery results, and the subquery can only reference the main table.
UPDATE t1 SET c1 = (SELECT MAX(c1) FROM t2)
UPDATE t1 SET c1 = (SELECT c1+c2 FROM t2 WHERE c2=t1.c1)
UPDATE t1 SET c1 = (SELECT c1 FROM t2 WHERE c2=t1.c1
AND c3 IN (SELECT c3 FROM t3))
UPDATE t1 SET c1 = (SELECT MAX(c1) FROM t2),
c2=(SELECT c1 FROM t2 WHERE c2=t1.c1),
c3=c3+1 WHERE c4 IN (SELECT c4 FROM t4) AND
c5>10
|
The following
example shows a subquery in an UPDATE statement in a distributed database.
In a distributed database, the subquery table may be a remote table,
but the main (UPDATE/DELETE) table must be a local table.
UPDATE t1 SET c1=c1+1 WHERE c2 IN (SELECT c2 FROM db2:t2)
UPDATE t1 SET c1=(SELECT MAX(c1) FROM db2:t2 WHERE c2=t1.c2)
|
E.5 Load and Unload Stored Procedures
You can
now load and unload stored procedures. This permits you to unload a
compiled stored procedure together with its catalog information and
load it into a database on another machine, without the need for a C
compiler on the destination and without the trouble of recompiling.
The following
examples unload all procedures of owner user1 to script file file1.
UNLOAD PROCEDURE FROM user1.% TO file1
UNLOAD PROC FROM user1.% TO file1
|
The following
examples unload all procedures with prefix `SP' to script file file1.
UNLOAD PROCEDURE FROM %.SP% TO file1
UNLOAD PROC FROM %.SP% TO file1
|
The following
examples unload the definition of procedure SP1 to the script file file1.
UNLOAD PROCEDURE DEFINITION FROM SP1 TO file1
UNLOAD PROC DEFINITION FROM SP1 TO file1
|
The following
examples unload the definition of all procedures of user2 to the script
file file1.
UNLOAD PROCEDURE DEFINITION FROM user2.% TO file1
UNLOAD PROC DEFINITION FROM user2.% TO file1
|
E.6 Simplified Chinese (GB) Support
You can
now use simplified Chinese (GB) in your database. This is in addition
to the Traditional Chinese (BIG5) and Japanese (Shift JIS + Half Corner)
support that is already built into DBMaker.
E.7 UNC Names For File Objects
You can
now use Universal Naming Convention (UNC) filenames for file object
path and directory names. This makes it easy to specify path and directory
names when your DBMaker server is running on Microsoft Windows.
E.8 ANSI-Compatible Referential Integrity Definition
DBMaker
now supports referential integrity definition directly in the CREATE
TABLE command, as defined in the ANSI standard. This lets you define
a table and create the relationships for it with a single command. You
can still use the old ALTER TABLE PRIMARY KEY/ALTER TABLE FOREIGN KEY
syntax also.
The following
examples create a table and specify the primary and foreign keys after
the column definitions.
CREATE TABLE t1 (c1 INT NOT NULL,
c2 INT,
c3 INT,
PRIMARY KEY c1,
FOREIGN KEY fk1 REFERENCES t2)
CREATE TABLE t1 (c1 INT NOT NULL,
c2 INT,
c3 INT,
PRIMARY KEY c1,
FOREIGN KEY fk1 (c2, c3) REFERENCES t2 (c1, c2))
|
The following
examples create a table and specify the primary and foreign keys as
part of the column definitions.
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY,
c2 INT REFERENCES t2,
c3 INT REFERENCES t3 (c1))
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY,
c2 INT REFERENCES t2,
c3 INT REFERENCES t3 (c1)
FOREIGN KEY fk1 (c2, c3) REFERENCES t4 (c1, c2))
|
E.9 ANSI-Compatible String Concatenation Operator
DBMaker
now supports a string concatenation operator (||), as defined in the
ANSI standard. This operator allows you to concatenate multiple strings,
and can be used in any expression or predicate.
The following
example concatenates the first name and last name of all employees in
the emp table.
SELECT first_name||` `||last_name FROM emp
|
The following
example concatenates the first name, middle name, and last name of all
employees in the emp table.
SELECT first_name||` `||middle_name||` `||last_name FROM emp
|
The following
example concatenates the values from two columns in the predicate.
SELECT * FROM t2 WHERE t1.c1||t1.c2 LIKE `ABC%`
|
E.10 Remove Space Padding Option
DBMaker
now supports an option to remove space padding from fixed-length character
(CHAR) data. This option can be used together with the new string concatenation
operator to allow easy concatenation of multiple strings without requiring
extra functions to remove the padding spaces.
There are
two ways to set this option:
Use the keyword DB_RMPAD in the dmconfig.ini configuration file. Setting
this keyword to a value of 1 turns on this option (removes space padding),
and setting it to a value of 0 turn off this option (does not remove
space padding).
Use
the SQL_REMOVE_SPACE_PADDING option of the ODBC function SQLSetConnectOption.
To turn on this option (removes space padding), use the following line
of code:
retcode=SQLSetConnectOption(hdbc, SQL_REMOVE_SPACE_PADDING,
SQL_REMOVE_SPACE_PADDING_ON);
|
To turn
off this option (does not remove space padding), use the following
line of code:
retcode=SQLSetConnectOption(hdbc, SQL_REMOVE_SPACE_PADDING,
SQL_REMOVE_SPACE_PADDING_OFF);
|
To get
the current value for this option, use the following line of code:
retcode=SQLSetConnectOption(hdbc, SQL_REMOVE_SPACE_PADDING,
&option_value);
|
E.11 Enhanced Backup Server
You can
now use the DBMaker backup server to perform full online backups as
well as incremental online backups. This provides a complete solution
for all of your backup needs, without requiring any manual intervention.
There are three new keywords for use in the dmconfig.ini configuration
file which are used to control the full backups performed by the backup
server.
DB_FBKTM - specifies the first time a full backup should occur.
The format for this keyword is yyyy/mm/dd hh:mm:ss, the same as the
DB_BKTIM keyword for incremental backups.
DB_FBKTV - specifies the time interval between full backups.
The format for this keyword is d-hh:mm:ss, the same as the DB_BKITV
keyword for incremental backups.
DB_BKODR - specifies a directory to put old full backup files.
The last full backup will be placed here when backup server does a new
full backup.
E.12 Enhanced Query Optimizer
You can
now manually specify the type of scan to use in a query, and which index
to use in an index scan. In addition, the DBMaker query optimizer now
automatically determines the most efficient type of scan to use, even
if you have not recently updated database statistics. There are five
different cases where you can specify the type of index you want to
use.
Force index scan
You can
force an index scan with the following syntax:
tablename (INDEX [=] idxname [ASC|DESC])
|
In addition
to specifying an index name to scan, you can also specify the value
0 to force a table scan, or the value 1 to force a primary key index
scan.
The following
example forces a table scan.
SELECT * FROM t1 (INDEX=0)
|
The following
example forces an index scan on the primary key.
SELECT * FROM t1 (INDEX=1)
|
The following
example forces an index scan on the index idx1.
SELECT * FROM t1 (INDEX idx1)
|
The following
example allows the query optimizer to decide what type of scan to use
on table t1, but forces an index scan on the index idx1 for table t2.
SELECT * FROM t1, t2 (INDEX idx1)
|
Force Index Scan With Alias
You can
force an index scan and provide an alias for the table with the following
syntax:
tablename (INDEX [=] idxname) aliasname
|
The following
example forces an index scan on the index idx1, and provides an alias
for the table.
SELECT * FROM t1 (INDEX idx1) a, t1 b WHERE a.c1 = b.c1
|
Force Index Scan With Synonym
You can
force an index scan when using a synonym with the following syntax:
synonymname (INDEX [=] idxname)
|
The following
example forces an index scan on the index idx1 when using synonym s1.
SELECT * FROM s1 (INDEX idx1)
|
Force Index Scan With View
You can
force an index scan when using a view with the following syntax:
viewname (INDEX [=] idxname)
|
The following
example forces an index scan on the index idx1 when using view v1.
SELECT * FROM v1 (INDEX idx1)
|
Force Text Index Scan
You can
force a text index scan with the following syntax:
tablename (TEXT INDEX [=] idxname)
|
The following
example forces a text index scan on the text index tidx1.
SELECT * FROM t1 (TEXT INDEX tidx1)
|
E.13 Enhanced Database Statistics
DBMaker
now includes more detailed database statistics. This gives you all the
information you need about your database, and helps the DBMaker query
optimizer to make more intelligent decisions on the most efficient way
to perform a query.
E.14 I/O Daemon
DBMaker
now has an I/O daemon to periodically write dirty pages from the least
recently used page buffers to disk. This helps reduce the overhead incurred
when swapping data pages into the page buffers, and increases performance.
There are four new keywords for use in the dmconfig.ini configuration
file which are used to control the I/O daemon.
DB_IOSVR - enables and disables the I/O daemon. Setting this
keyword to a value of 1 enables the I/O daemon, and setting it to a
value of 0 disables the I/O daemon.
DB_IOITV - specifies the time interval in seconds between I/O
daemon activity where the I/O daemon writes dirty pages from the least
recently used page buffers to disk. This value has a valid range of
1 to 2147483647.
DB_IOBUF - the maximum number of dirty pages that are flushed
to disk, expressed as a percentage. The default value is 10, and valid
values range from 1 to 100.
DB_IOSCA - the maximum number of pages checked by the I/O daemon
to see if it is dirty or not, expressed as a percentage. The default
value is 25, and valid values range from 1 to 100.
After starting
the database server, any error messages generated by the I/O daemon
are written to the file ERROR.LOG.
E.15 Checkpoint Daemon
DBMaker
now has a checkpoint daemon to periodically take a checkpoint. This
helps reduce the time you have to wait for a checkpoint that occurs
during a command, when a journal is full, or when starting or shutting
down a database. The checkpoint daemon is actually a sub-function of
the I/O daemon, which can perform I/O alone, checkpoints alone, or both
together. There are two new keywords for use in the dmconfig.ini configuration
file which are used to control the checkpoint daemon.
DB_CHTIM - specifies the first time a the checkpoint daemon should
run. The format for this keyword is yyyy/mm/dd hh:mm:ss.
DB_CHITV - specifies the time interval between checkpoints. The
format for this keyword is d-hh:mm:ss.
To turn
on the checkpoint daemon, you must turn on the I/O daemon using the
DB_IOSRV keyword. After you turn on the I/O daemon, it will automatically
take a checkpoint every hour by default. Use the keywords above to change
this behavior.
E.16 Reset Serial Number
You can
now reset the counter for a serial column. This allows you to start
a new sequence in a serial column without having to modify the table.
The syntax for this command is:
ALTER TABLE table_name SET SERIAL number
|
The following
example alters the serial counter value of table t1 from its current
value to 100.
ALTER TABLE t1 SET SERIAL 100
|
|
