PRAGMA bdbsql_error_file [filename]

Redirects internal Berkeley DB error messages to the named file. If a relative path is specified to [filename], then the path is interpreted as being relative to the current working directory.

If this PRAGMA is issued with no filename, then the current target for Berkeley DB error output is returned. By default, error messages are sent to STDERR.

This PRAGMA can be issued at any time; initial database access does not have to occur before this PRAGMA can be used.

PRAGMA bdbsql_lock_tablesize [= N]

Sets or reports the number of buckets in the Berkeley DB environment's lock object hash table.

This pragma must be called prior to opening/creating the database environment.

For more details, see get_lk_tablesize and set_lk_tablesize.

PRAGMA bdbsql_shared_resources [= N]

Sets or reports the maximum amount of memory (bytes) to be used by shared structures in the main environment region.

This pragma must be called prior to opening/creating the database environment.

For more details, see get_memory_max and set_memory_max.

PRAGMA bdbsql_single_process = boolean

To create a private environment rather than a shared environment, enable this pragma. The cache and other region files will be created in memory rather than using file backed shared memory.

In Berkeley DB SQL the default behavior is to allow a database to be opened and operated on by multiple processes simultaneously. When this pragma is enabled, accessing the same database from multiple processes simultaneously can lead to data corruption. Either option supports accessing a database using a single process multi-threaded application.

By default omit sharing is disabled. This pragma must be called prior to opening/creating the database environment. Because the setting is not persistent, you may need to invoke it before every database open, or define compile option BDBSQL_OMIT_SHARING instead.

For more information, see Shared memory region. Note that this pragma causes the DB_PRIVATE flag to be specified in the DB_ENV->open() method.

PRAGMA bdbsql_system_memory [base segment ID]

Queries or sets a flag that causes the database's shared resources to be created in system shared memory. By default the database's shared resources are created in file-backed shared memory.

If a [base segment ID] is specified, the shared resources will be created using X/Open style shared memory interfaces. The [base segment ID] will be used as the starting ID for shared resources used by the database. Use different [base segment ID] values for different databases. It is possible for multi-process applications to use a single database by specifying the same [base segment ID] to this PRAGMA. Each connection needs to set this PRAGMA.

This PRAGMA may be used to set a [base segment ID] only before the first table is created in the database.

PRAGMA bdbsql_vacuum_fillpercent [= N]

Sets or reports the page full threshold. Any page in the database that is at or below this percentage full is considered for vacuuming when PRAGMA incremental_vacuum is enabled. The value is specified as a percentage between 1 and 100. By default, pages 85% full and below are considered for vacuuming.

PRAGMA bdbsql_vacuum_pages [= N]

Sets or reports the maximum number of pages to be returned to the file system from the free page list when incremental vacuuming is enabled. By default, up to 128 pages are removed from the free list.

Page vacuuming is controlled using PRAGMA auto_vacuum.

PRAGMA large_record_opt [= number bytes]

Enables optimized storage of large records. Any record larger than the given number of bytes will be stored in a new format that improves read and write performance for large records.

This pragma must be called before any data is added to the database, otherwise it will be ignored.

This optimization is incompatible with encryption, and SQLITE_ReadUncommitted.

This optimization stores large records in a folder called __db_bl in the journal folder. So if the database is manually moved to a new location, the folder with the large records most also be moved. The Online Backup Function will automatically backup up these records with the rest of the database.

PRAGMA multiversion

Controls whether Multiversion Concurrency Control (MVCC) is on or off. You can not use this PRAGMA at any time during your application's runtime after your database tables have been accessed.

For more information on MVCC and snapshot isolation, see Using Multiversion Concurrency Control

PRAGMA snapshot_isolation

Controls whether snapshot isolation is turned on. This PRAGMA can be used at any time during your application's runtime after Multiversion Concurrency Control (MVCC) has been turned on.

For more information on MVCC and snapshot isolation, see Using Multiversion Concurrency Control

PRAGMA statistics [= LOCK|LOG|MEM|MUTEX|REP]

Prints various statistics to the entered subsystem, or general environment statistics if no subsystem is entered. The subsystems are locking, logging, memory pool, mutex and replication. The output is printed to stdout, unless the statistics_file PRAGMA is used to set a file to which the output is directed.

PRAGMA statistics_file [= filename]

Sets a file to which the output from the statistics PRAGMA is printed. Otherwise the output is printed to stdout. The file will be automatically created if it does not exist, and output will be appended to the end of the file if it does exist.

PRAGMA trickle [percent]

Ensures that at least the specified percentage of pages in the shared cache are clean. This can cause pages that have been modified to be flushed to disk.

The trickle functionality enables an application to ensure that a page is available for reading new information into the shared cache without waiting for a write operation to complete.

PRAGMA TXN_BULK

Enables transactional bulk loading optimization. For more information, see Using Bulk Loading.

Fourteen PRAGMAs were added to manage and control replication. They are described in Using Replication with the SQL API:

PRAGMA bdbsql_userauth_add = "user:password:isAdmin";

Creates a new user. isAdmin should only be 0 (non-admin user) or 1 (admin user).

PRAGMA bdbsql_user_login = "user:password";

Authenticates a user.

PRAGMA bdbsql_user_edit = "user:password:isAdmin";

Change a user's login credentials or admin privilege. isAdmin should only be 0 (non-admin user) or 1 (admin user).

PRAGMA bdbsql_user_delete = "user";

Deletes a user.