.
contact contact

B.08.30 / Release Notes / Database / Database Changes

Database Changes

 
.
  This document provides a detailed list of the user visible database changes included in the B.08.30 release.


Database configuration changes

eloqdb.cfg Database configuration

The config items below are new or were changed in the eloqdb.cfg database configuration file.

The [Server] section has the following configuration changes:

[Server] Service

This configuration item specifies the service name (as defined in /etc/services) or the port number where the server should listen for requests. The default value is eloqdb.

A suffix /4, /6, or /46 may be used to establishing IPv4 only, IPv6 (with implied IPv4 support), or separate IPv4 and IPv6 listening sockets. By default, separate IPv4 and IPv6 sockets are used.

For example, eloqdb/46 would specify to use the port number associated with the service name eloqdb and to use separate IPv4 and IPv6 sockets.

[Server] ServiceHttp

The service name (as defined in /etc/services) or the port number where the server should listen for HTTP requests. If neither ServiceHttp nor ServiceHttps are specified, the HTTP/HTTPS status is disabled.

A suffix /4, /6, or /46 may be used (see Service above).

[Server] ServiceHttps

The service name (as defined in /etc/services) or the port number where the server should listen for HTTPS (secure HTTP) requests.

If neither ServiceHttp nor ServiceHttps are specified, the HTTP/HTTPS status is disabled.

A suffix /4, /6, or /46 may be used (see Service above).

To enable HTTPS a server certificate is necessary and the TLS configuration items (TLSCertChainFile and TLSCertKeyFile) must be configured. Web browsers require a certificate which is issued by a known authority. Using a self-signed certificate typically requires to configure the browsers which are used to access the HTTPS status.

[Server] ServiceLocal

This configuration item specifies the name or path of an AF_Unix socket to be used as an additional method to connect the database server. If not specified the local socket connection is disabled. This configuration item is only available on the HP-UX and Linux platform.

An absolute file name may be specified with a leading slash. Otherwise the file name is relative to the default directory /var/opt/eloquence/socket. Please note that a matching definition must be used with the client library to use local sockets.

For example, ServiceLocal=eloqdb creates the socket file /var/opt/eloquence/ socket/eloqdb. A prefix of /local:eloqdb is used to access the database using the local socket (EQ_DBSERVER=/local:eloqdb).

[Server] UseKeepAlive

A numeric flag to specify that the tcp keep alive socket option is used. Valid values are 1 and 0, the default is 1.
If set the operating system may verify whether the client is still connected after a system defined time of inactivity.

[Server] TLSCertChainFile

Absolute path to a file in PEM format containing the server certificate and optionally any additional intermediate certificates which represent the verification chain up to the root certificate. The certificates in this file must be sorted in the verification order, starting with the server certificate.
The file may in addition contain the private key associated with the server certificate, in which case the TLSCertKeyFile configuration below may be omitted.

Note: TLS (Transport Layer Security) configuration is required to enable the HTTPS (secure HTTP) status (see ServiceHttps above).
To enable HTTPS, a server certificate is needed for the eloqdb host name. Web browsers require a certificate which is issued by a known authority. Using a self-signed certificate typically requires to configure the browsers which are used to access the HTTPS status.

For additional information, please refer to the section about how to obtain the TLS CertChainFile.

[Server] TLSCertKeyFile

Absolute path to a file in PEM format containing the private key associated with the server certificate. If this key is contained in the file configured with TLSCertChainFile above, configuring TLSCertKeyFile is not necessary.


The sections [db-access] and [http-access] that are used to specify access to the Eloquence database server now support IPv6 addresses and host names.

The following syntax is supported:

  {allow|deny} = { All 
                   hostname
                   ip-address [/{addrbits|netmask}] }
  • hostname is the name of a host or network that is resolved into one or more ip addresses.

  • ip-address is either an IPv4 or IPv6 address.

    • An IPv6 address, enclosed in square brackets, such as [::1]. The addrbits option may be used to specify the number of relevant bits in the IP address.

    • An IPv4 address, such as 127.0.0.1. The addrbits option may be used to specify the number of relevant bits in the IP address. The netmask option allows to specify an IPv4 network mask, such as 255.255.255.0.
Multiple Allow/Deny entries are recognized and combined.

The following defaults are used:
If the section [db-access] is not present, access is restricted to localhost (127.0.0.1 and [::1]). If the section [http-access] is not present, access to the database http status is not restricted.

For example:

allow = localhost
allow = 192.68.71.0/24   
allow = [2001:4860:4802:38:]/64
A local socket is considered local access and always allowed, subject to file system permissions. As of B.08.30 the access lists may also be configured using the dbctl access command.


The follwing entries were added to the [config] section:

[Config] AIOThreads

The AIOThreads configuration item specifies the number of internal I/O threads used by eloqdb. On HP-UX IA64 the kernel Posix AIO functionality is used by default and only two threads are required. Otherwise the specified number of internal I/O threads defines the number of concurrent async disk read operations. A zero value (default) disables async I/O.

It is recommended to set AIOThreads to two on HP-UX IA64 based systems and to at leat 4 on other platforms. On slave replication servers AIOThreads should be set to benefit from parallel disk reads applying replicated transactions.

[Config] SessionFtsLimit

The SessionFtsLimit config item may be used to limit the memory allocated for a session to hold FTS search results or intermediate results. The numeric value specifies the number of internal results supported (which could be different from the search results). An additional k or m multiplies this value by 1000 or 1 million. A zero value disables the limit. For example, SessionFtsLimit = 100m


The [devices] section is used to define dbstore/dbrestore targets. The backup path does no longer support specifying a command with a leading pipe (for example, using gzip to compress the dbstore archive).

As an alternative, the dbstore utility and the dbctl dbstore commands support compression and dbctl dbrestore transparently handles compressed archive files. As an additional enhancement dbctl dbstore/dbrestore allow absolute paths and do no longer depend solely on configuring dbstore/dbrestore targets.

The [ForwardLog] section has the following configuration changes:

[ForwardLog] FwLog

Configures the forward log file. The %N token in the file name (now mandatory) is replaced by the forward log file generation and sequence. By default, forward-logging is inactive.
For compressed forward log files the .gz extension (FwCompressExt) is automatically added to the file name.
For example, FwLog = /data/fwlog/fw-%N.log

Previous versions also allowed to specify a command to handle the forward log file, for example to compress the forward log file. This is no longer supported. The FwCompress option may be used to write a compressed forward log file.

[ForwardLog] FwArchive

FwArchive may be used to specify a list of directories, separated by a colon (or semicolon on Windows) to hold forward log files in addition to the directory specified in the FwLog config item. By default, FwArchive is not set. This configuration may be used to locate archived forward log files.

This configuration item is used by database utilities, such as dbrepl or dbrecover that access the forward log.
For example, FwArchive = /data/archive1:/data/archive2

[ForwardLog] FwCompress

A numeric flag to specify whether the forward log files are compressed. By default compression is not active.

Compression will noticeably reduce the size of forward log files in exchange for additional CPU utilization to compress the data. It depends on the system CPU resources and the disk bandwidth if this is beneficial to performance.

The forward log files may also be compressed afterwards with gzip (for example, when changing forward log files). The Eloquence utilities (as well as the fwutil library) handle compressed forward log files transparently.

[ForwardLog] FwCompressExt

FwCompressExt specifies the extra file extension to indicate a compressed file. The default is gz.

The following config items are obsolete and result in a warning.

  [Config]IOThreads
  [Config]IOQueueSize
  [ForwardLog]FwRecovery
IOThreads/IOQueueSize was used before Eloquence B.08.00 to scale the disk I/O concurrency. FwRecovery is no longer supported as of B.08.30. It was used to specify a command to uncompress a forward log file.


Session stat format

The eloqdb session stat file format has changed as of B.08.30. The B.08.30 file includes additional metricts specific to async disk I/O.

  • The B.08.30 database server modifies use of the first group of metrics (with three values). The metric group 1 (IOREAD) supplies the following information:
    • (1) number of async disk i/o requests (changed)
    • (2) number of sync disk i/o requests
    • (3) time spent in sync disk i/o requests (usec)

    The value (1) holds the count of disk i/o requests requested by this session. Previously this value was unused.

  • The B.08.20 database server added a group of metrics (with three values) with information on FTC and bulk fetch protocol calls. The metric group 13 (OTHER) supplies the following information:
    • (1) max. fts result size
    • (2) number of requests
    • (3) time spent in requests (usec)

  • The B.08.30 database server adds a group of metrics (with three values) with information on async disk i/o. The metric group 14 (IOWAIT) supplies the following information:
    • (1) zero, currently unused
    • (2) number of iowait pages
    • (3) time spent in iowait (usec)

    iowait specifies the number of times and time spent waiting for disk i/o to complete. With async disk i/o the actual i/o may be processed in another thread. However, the handler thread may need to wait for the disk i/o to complete. Where previously the execution time included an IOREAD time (sync disk i/o) some i/o may be performed asynchroneously and still progressing when trying to use the content.

  • The client IP address may use the IPv6 format when IPv6 is used.


The following example shows an example session stat record (single line):

1544177737|9|E|temp|public|2018-12-07 11:10:36|301|0|0|0|0|120|815|0|
905|4512|60000|308|26358|0|390|23527|0|330|8378|0|0|0|0|330|1076|0|
330|516|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|[::1]:52398|uid=1000 pid=25207 
pname=ordering -b /tmp/181205/ordering_v3 
Note that the above example is actually a single line in the SessionStatFile; the line wrapping has just been added to fit the long line into this document.

The example GNU awk script fmt-ssn-stats-8xx.gawk script may be useful to format the session stats:

Timestamp      2018-12-07 11:15:37
TID            9
RecType        E
OsUser         temp
DbUser         public
ConnTime       2018-12-07 11:10:36
ConnSecs       301
IO_READ        0 0 0
DBFIND         0 120 815  avg 6.79167
DBGET          0 905 4512  avg 4.98564
DBGETB         60000 308 26358  avg 85.5779
DBPUT          0 390 23527  avg 60.3256
DBUPDATE       0 330 8378  avg 25.3879
DBDELETE       0 0 0
DBLOCK         0 330 1076  avg 3.26061
DBUNLOCK       0 330 516  avg 1.56364
TXBEGIN        0 0 0
TXCOMMIT       0 0 0
TXROLLBACK     0 0 0
OTHER          0 0 0
IO_WAIT        0 0 0
ClientIP       [::1]:52398
AppEnv         uid=1000 pid=25207 pname=ordering -b /tmp/.../ordering_v3

time spent in db calls = 0.065182 sec (0.0216551% of connect time)
time spent in io reads = 0 sec (0% of connect time)

Password encoding changes

The Eloquence B.08.30 uses an enhanced encoding to maintain database passwords.

In previous versions, Eloquence used a secure hash function to encode database passwords. As of B.08.30 the standard PBKDF2 algorithm is used to hash any passwords with improved security.

This provides additional protection against reverse lookup of passwords if the hashed password hash value might have been exposed. Database password hashes are only visible to the dba user. As a related change the database protocol was enhanced to handle password salting and additional hashing schemes and to improve the encryption of passwords in transit.

As Eloquence makes use of the openssl library on HP-UX and Linux for crypto functionality, installation of openssl on HP-UX is now required to use the new password hashing. Password authentication will fail if the crypto functionality is not available.

The (interactive) dbutil utility was changed to indicate the password format. A digit after the asterisk to indicate a password is present indicates the password encoding (*1 = B.08.30).

The dbdumpcat utility (on the sysuser table) may be used to verify the password format. A password size of 16 bytes indicates the old password encoding.

See also:
https://en.wikipedia.org/wiki/PBKDF2


FTS index changes

The Eloquence B.08.30 database server comes with a number of FTS performance enhancements.

Internally, FTS maintains lists of records that reference a keyword. These "reference lists" uses various sized "bins" to cover keywords with few and a large number of references efficiently.
To reduce the number of disk I/O requests, the database server was enhanced to cache the layout of FTS results in the volume file. This avoids a potential extra disk access to locate the results.
A new result size "bin" was added to allow more efficient handling of keywords with a large number of results. While using a stop word should be considered to avoid minataining the keyword references in the fist place, the new organization should allow to half any disk accesses for keywords with more than 2000 references.

This new result size "bin" is created automatically if a database with FTS indexing is restructured, for example if a new FTS index is added. As a consequence, the FTS index is no longer backwards compatible to previous Eloquence versions and needs to be rebuilt.

As of B.08.30 the FTS indexing was enhanced to support multiple keyword pools. By default a single dictionary index is used to maintain any keyword for the entire database. This could result in a large keyword dictionary index and might affect performance in some range searches where a large number of keywords exist that are not relevant in the search context. Using a separate dictionary index makes those searches instantaneous and allows to maintain similar keywords (for example, date ranges) in a separate dictionary.

A keyword dictionary is specified as an option in the dbutil syntax and the database restructuring process changes the internal structure accordingly. For example:

create fts index on date option indx=2;
This dbutil statement species to maintain any keywords for the date field in the dictionary pool 2. An index id of zero specifies the default dictionaty pool.

As previous Eloquence database versions do not support FTS index pools any FTS indexes need to be rebuilt when a database is moved to a previous Eloquence version.

The FTS parser was changed to allow indexing empty fields. If an FTS index uses the options NP and NE them empty (all spaces for string or a zero value for a numeric field) may be indexed.

Please note: To retain full compatibility with previous Eloquence versions this functionality is only enabled when FTS indexes are initially added to a database. If FTS is currently used then the existing behaviour is retained.

create fts index on code option np,ne;
This dbutil statement species to enable indexing empty fields.

The database property FTSParserVersion with a value of 3 indicates the new behaviour, a value of 2 indicates the B.08.20 behaviour. The list of database properties may be obtained with dbutil or dbdumpcat -t11 database.


dbctl changes

dbctl access

The new dbctl access command may be used to manage the [db-access] or [http-access] filter defined in the database server configuration file.

The following syntax is supported:

dbctl access show  {db|http}
dbctl access add   {db|http} {allow|deny} address
dbctl access reset {db|http} [{allow|deny} address]
The first argument specifies the operation, the second the access-filter to operate on (db or http). dbctl access requires dba or operator capabilities.

The show operation lists the current access rules for the specified section. For example:

dbctl -u dba access show db
#  show Access
-- ------------------------------------
1  Allow localhost
2  Allow 10.64.71.0/255.255.255.0
The add operation adds a new access rule. The reset operation may be used to reset all access rules and optionally allows to add a new access rule at the same time, for example, to keep remote access functional when the reset operation is used remotely.

For example:

dbctl -u dba access add db allow 10.64.71.0/24
dbctl -u dba access reset db allow 10.64.71.0/24
Please refer to the eloqdb [db-access] documentation for more information about the access and address argument.


dbctl dbstore

The dbctl dbstore command transfers a single database to the target archive.
dbctl dbstore [/z] database target
In order to store a database, database admin (dadmin) or server admin (dba) or operator capabilities are required. The target archive may start with a leading slash to specify an absolute file name. The file must not exist and is created by dbstore.

Otherwise the target specifies either the name of a configured server device or the name of a server device and a filename separated by a slash.

If the dbstore target is a disk file, it will be created with permissions that restrict access to the UID used for running the database server.

The /z option may be specified to enable compression of the store archive. It is recommended to use a .gz extension with a compressed dbstore archive file to indicate compressed archives. However, this is not required and a dbctl dbrestore will handle this transparently.

For example:

$ dbctl -u dba dbstore /z payrol backup/payrol.bkup.gz
Database "payrol" has been stored to "/data/backup/payrol.bkup.gz".

$ dbctl -u dba dbstore sample /tmp/sample.bkup
Database "sample" has been stored to "/tmp/sample.bkup".
B.08.30 introduced support for compressed archive files and specifying an absolute archive file.


dbctl dbrestore

The dbctl dbrestore command restores a single database from a source archive. The database must not already exist. Server admin (dba) or operator capabilities are required in order to restore a database. The /nouser option is restricted to dba users.

dbrestore [/info] source
dbrestore [/all] [/nouser] [/force] [/nice] source [new_database_name]
The source archive may start with a leading slash to specify an absolute file name.

Otherwise the source specifies either the name of a configured server device or the name of a server device and a filename separated by a slash.

A compressed archive file is handled transparently.

If the /info argument is present, information on the archive is obtained and the archive is not restored. The header of the specified archive is displayed.

If the /all argument is present, the association between database permission groups and server users ("sysmember") is retained. Usage of the /all argument is safe if the database environment where the database is restored is the same or has identical server users as the environment where the database was stored. Specifying the /nouser option has the same effect as omitting the /all option.

If the /force option is present, the database may even be restored while the server is in online backup mode. By default, dbrestore requests are rejected during online backup mode because of the possible negative impact on performance and log volume size.

If the /nice option is specified, the server attempts to mitigate the performance impact on concurrent client sessions and the operating system buffer cache at the cost of slowing down the dbrestore progress.

If a new_database_name is specified, its length must not exceed 255 characters, it must not begin with a slash ('/') and must not contain any space characters.

For example:

$ dbctl -u dba dbrestore /all backup/payrol.bkup.gz
Database "PAYROL" has been restored from "/data/backup/payrol.bkup.gz".
B.08.30 introduced support for compressed archive files and specifying an absolute archive file.


dbctl bimport

The dbctl bimport command allows the import of binary export files for a data set.

bimport [/v] [/a] [/n] [/f] database set bexp_file
A binary import retains any chain information and detail set record numbers. The affected data set is erased before the import file is processed. A compressed bexp_file is handled transparently.

If the optional /v argument is specified, additional information is displayed. Specifying /v twice (/v /v) displays progress information.

If the optional /f argument (force) is present, the database set is forced to allow import while the server is in online backup mode. By default, bimport requests are rejected during online backup mode because of the possibly negative impact on performance and log volume size.

For example:

$ dbctl bimport /v /v sample 1 $PWD/SAMPLE.001.exb

P0: server: /data/tmp/SAMPLE.001.exb: 1355 records expected
P0: server: /data/tmp/SAMPLE.001.exb: completed, 1355 records processed
B.08.30 changed the record counter to 10 digits to support tables with more than 2 billion records. A compressed bimport source file is handled transparently.


dbctl volume

The dbctl volume command allows to change database volume file properties without a server restart.
volume limit [value]
volume list
volume id [{max|ext} value]
volume add path
The following syntax is supported:
dbctl volume limit
Returns the current volume size limit

dbctl volume limit value
Allows specifying a new volume size limit (in MB)

dbctl volume list
List database volumes and properties

dbctl volume id
List the database volume properties for volume id

dbctl volume id max value
Allows to change the size limit of the volume (in MB). This has the same effect as dbvolchange.

dbctl volume id ext value
Allows to change the extension size of the volume (in MB). This has the same effect as dbvolchange.

dbctl volume add path
Allows to add another file to the server volume set. An absolute path must be specified, and the file may not exist. The server will then create the volume file.
Please note that the volume file must be added manually to the server configuration file. Otherwise the server won't start subsequently as a data volume is not available. Up to 3 volume files may be added before restarting the server process.
$ dbctl -u dba volume limit 2048
Volume limit changed

$ dbctl -u dba volume add /data/db/data02.vol 
Added volume 3 "/data/db/data02.vol", 2 spare volumes left.

Please note that the dbctl volume commands are not replicated to a slave server. During replication this has no effect as the slave server will ignore these limits. However, on a role change (master/slave) these limits will be enforced. Consequently, it is recommended to perform the same change on any slave servers.
Any change to the global volume size limit does not persist a database server restart. It only applies to the running server process and does not automatically update the VolumeFileSizeLimit in the eloqdb server config file.


Database Utilities

All database utilities support the IPv6 protocol and the transparent fwlog file compression. Also some issues were fixed related to to ensure utilities do support more than 2 billion records per data set.

dbinfo

  • Changed capacity and entries columns to 10 digits to handle tables with more than 2 billion records.

dbimport

  • Changed record counter column width to 10 digits to handle tables with more than 2 billion records.
  • New -Z option to support compressed export files.

dbexport

  • Change entries and record counter columns to 10 digits to handle tables with more than 2 billion records.
  • New -Z option to support compressed export files.
  • Changed record counter update interval to 1000 records.

dbpasswd

  • Support for new password hashing scheme if supported by server. Otherwise fall back to previous scheme.
  • The undocumented option -X may be used to enforce a fallback to the old password hashing.

dbrepl

  • New -Z option to enable compressed slave communication.
  • Transparent support for compressed forward log files.
  • Support fwlog archive path list.
The -Z option will reduce the replication bandwidth but will use additional CPU resources. It depends on the network speed if this benefits replication performance. According to some preliminary testing compression may improve performance if the available bandwidth to the replication slave is less than 100 MBit. Compression is unlikely to improve performance on the local network.

prschema

  • Fixed capacity to account for tables with more than 2 billion records.

dbutil

  • Added support for FTS index pool option (indx=# or I#) to specify the keyword dictionary pool. Pool id must be a numeric value between 0 (use global pool) and 10000. Any keywords sharing the same index pool id are maintained in the same dictionary index.

    For example

    create fts index on date option indx=2;
    
    The "indx" option results in any keywords maintained in the index pool 2.

  • Support for new password hashing scheme if supported by server. Otherwise fall back to previous scheme.
  • The undocumented option -X may be used to enforce a fallback to the old password hashing. Please keep in mind that this option disables security enhancements and should only be used temporarily to ensure backwards compatibility.
  • Indicate password hashing scheme in the user list (* = old, *1 = PBKDF2_HMAC_SHA1).
  • Improved handling of terminal character sets for interactive use.

prdbutil

  • Support for FTS indx option.
  • Added section "db" to emit database name.
  • New option -T to output TurboIMAGE item types (dbutil -T).

dbbexp

  • Changed record counter column width to 10 digits to handle tables with more than 2 billion records.
  • New -Z option to support compressed export files.

dbdump

  • Transparent support for compressed export files.

dbcfix

  • Changed record counter column width to 10 digits to handle tables with more than 2 billion records.

dbrecover

  • Transparent support for compressed forward log files.
  • Support fwlog archive path list.

dbrepack

  • Added support for FTS index pools.

dbstore

  • New -Z option to support compressed store archive.

fwaudit

  • New -Z option to support compressed output file.
  • Transparent support for compressed forward log and audit files.

query3k

  • Fixed query3k limitations accessing tables with a size of more than 2 billion records.
  • Fixed a problem evaluating boolean operators when searching on a keyword index (FTS).
  • Remove limitation of 2GB output file.
  • The QUERY3K version number was changed to B.08.30.00 (20171107).


 
 
 
  Privacy | Webmaster | Terms of use | Impressum Revision: 2019-01-14  
  Copyright © 1995-2024 Marxmeier Software AG