A variety of built in procedures are available for administrative database tasks. These can be called in the same manner as user defined procedures:
call fc_get_fcproclist();
A result set, if returned, is handled in the standard caller manner.
These stored procedures are useful in iSQL and other SQL clients because they return valuable information about the FairCom SQL engine and perform operations that cannot be done with SQL statements.
fc_add_db( )
Adds an existing FairCom DB SQL database to the list of registered databases.
Parameters (1)
dbname VARCHAR(1024)
Example
call fc_add_db('ctreeSQL');
fc_check_file_tran_state( )
Returns the current FairCom DB transaction mode for the specified table.
Result Set Fields (2)
owner VARCHAR(64)
table VARCHAR(64)
Returned modes:
- 0 No transaction control.
- 1 Transaction control without recoverability. (ctPREIMG)
- 2 Transaction control and recoverability. (ctTRNLOG)
Example
call fc_check_file_tran_state();
owner table_name
----- ----------
admin custmast
admin custorder
admin ordritem
admin itemmast
fc_create_db( )
Creates a new FairCom DB SQL database named dbname.
Parameters (1)
dbname VARCHAR(1024)
Example
call fc_create_db('ctreeSQL');
See also
In V13.0.3 and beyond, if you want to control the case sensitivity of your new table, use fc_createdb() instead.
fc_createdb( )
In V13.0.3 onwards, creates a new FairCom DB SQL database named dbname.
Parameters (2)
dbname VARCHAR(1024)
casesensitive BIT
Example
call fc_createdb('ctreeSQL', 1);
See also
For versions prior to V13.0.3, see fc_create_db() (cannot control case sensitivity).
fc_create_user( )
Creates a new FairCom DB SQL user username with initial password pass.
The username is a user ID that is limited to a 32-byte ASCIIZ string (a null-terminated ASCII string), which implies 31 bytes are available for the User ID.
The ID is limited to alphanumeric characters, hyphens, underscores, and periods. A hyphen character (-) cannot be used as the first character.
The server ignores the case of the ASCII characters. Therefore, the server considers TEST_1.FairCom-4 to be the same as test_1.faircom-4.
When RBAC is active, fc_create_user() is replace by fc_create_account().
Parameters (2)
username VARCHAR(32)
pass VARCHAR(32)
Example
call fc_create_user('accounting', 'unbreakable');
fc_get_cachestats()
Returns FairCom DB SQL current cache subsystem statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_cachestats();
DESCRIPTION VALUE
----------- -----
data buffer requests 9860
data buffer hits 9772
index buffer requests 883
index buffer hits 817
index buffer pages in use 15
max index buffers in use 26
available data cache pages 1253
data cache pages in use 57
max data cache pages in use 57
# of index buffers on upd list (tran) 0
# of index buffers on upd list 1
# of data caches on upd list (tran) 0
# of data caches on upd list 0
avail data file special cache pages 626
actual data file special cache pages 0
maximum data file special cache pages 0
16 records returned
fc_get_connstats()
Returns FairCom DB SQL current connection statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_connstats();
DESCRIPTION VALUE
----------- -----
Number of connected users 1
Maximum connected users 1
Threads in use total (ctaddwork) 1
Max threads in use (ctaddwork) 2
Num threads active in foreground 1
5 records returned
fc_get_dblist( )
Returns a resultset listing registered database names.
Result Set Fields (1)
dbname VARCHAR(1024)
Example
call fc_get_dblist();
dbname
------
demoSQL
ctreeSQL
testing
fc_get_fcproclist( )
Returns a listing of available built-in stored procedures.
Result Set Fields (1)
proclist VARCHAR(1024)
Example
call fc_get_fcproclist();
proclist
--------
fc_create_user
fc_get_userlist
fc_get_fcproclist
fc_set_debug
fc_set_priority
fc_get_priority
fc_set_blockinglock
fc_get_dblist
fc_create_db
fc_add_db
fc_set_min_card
fc_get_min_card
fc_set_selectivity
fc_get_selectivity
fc_get_taskid
fc_check_file_tran_state
fc_get_server_version
fc_set_impersonation
fc_set_file_tran_state
fc_get_filestats()
Returns FairCom DB SQL current physical file control statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_filestats();
DESCRIPTION VALUE
----------- -----
Number of open physical files 8
Number of open logical files 25
Number of c-tree FCBs in use 34
Max physical files opened 8
Max logical files opened 35
Max c-tree FCBs in use 53
Number of virtual files open 0
Number available file control blocks 1001
8 records returned
fc_get_hosttablename()
Retrieves the host table name of a Multi-Record Type (MRT) table. (V12.0.1)
Parameters (1)
Table ID as an INTEGER for which it will retrieve the MRT host name and UID. The returned result set consists of exactly 1 row containing four columns:
- The table UID or the host UID in case of multi-record type (MRT) table
- The c-treeDB table name
- The filesystem path where the table is located
- The filesystem name of the table
Example
call fc_get_hosttablename(1163);
ID NAME PATH PHYSICAL_NAME
-- ---- ---- ------------
1163 syssequences .\ctreeSQL.dbs\ syssequences
fc_get_iostats()
Returns FairCom DB SQL current I/O statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_iostats();
Description Value
----------- -----
number of read operations 1,182
bytes read 1,626,850
number of write operations 33
bytes written 158,080
number of comm read operations 0
comm bytes read 0
number of comm write operations 0
comm bytes written 0
number of log write operations 2
bytes written to log file 16,384
number of log read operations 5
bytes read from log file 17,416
number of log extension operations 0
log file extension bytes 0
14 records returned
fc_get_isamstats()
Returns FairCom DB SQL current ISAM statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_isamstats();
DESCRIPTION VALUE
----------- -----
ISAM record add count 0
ISAM record delete count 0
ISAM record update count 0
ISAM record read count 124
4 records returned
fc_get_lockstats()
Returns FairCom DB SQL current lock statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_lockstats();
DESCRIPTION VALUE
----------- -----
count of lock attempts 249
subcount of hdr lock attempts 2
count of locks denied 0
count of locks blocked 0
subcount of header blocks 0
count of dead locks 0
count of locks freed 249
count of blocks released 0
current count of locks held 0
current count of blocked requests 0
cumulative lock wait time^ 0
cumulative lock wait count 0
maximum elapsed lock wait time^ 0
net locks over unlocks 0
max net locks over unlocks 1
15 records returned
fc_get_memstats()
Returns FairCom DB SQL current memory statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_memstats();
DESCRIPTION VALUE
----------- -----
SQL current aggregate summary 2508841
SQL memory highwater 2908540
Net SQL memory allocations 1880
System memory highwater mark 51370239
System aggregate sum 50968434
5 records returned
fc_get_min_card( )
Returns the minimum cardinality value currently in use by the FairCom DB SQL database engine.
Result Set Fields (1)
value INTEGER
Example
call fc_get_min_card()
value
-----
0
fc_get_priority( )
Returns the FairCom DB SQL client thread priority.
Note: This feature is supported only on the Windows operating system.
Result Set Fields (1)
priority TINYINT
Example
call fc_get_priority()
priority
--------
0
fc_get_partbounds()
fc_get_partbounds(owner, table_name)
Returns the first and last active partition numbers for a partitioned table. This is useful to determine the oldest (Firstactprt) partition to purge, for example. You must be connected to the database that contains the table.
Parameters (2)
------------------
owner VARCHAR(64)
table_name VARCHAR(64)
Result Set Fields (2)
------------------
Firstactprt -- "First Active Partition number"
Lastactprt -- "Last Active Partition number"
fc_get_replstats()
Returns FairCom DB SQL current replication statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_replstats();
DESCRIPTION VALUE
----------- -----
Total successful operations 0
Successful apply operations 1339713297
Successful transaction begins 7233801
Successful transaction aborts 6832485
Successful transaction commits 27
Successful transaction success 19
Successful user-defined entries 30
Successful record adds 20
Successful record updates 25769806224
Successful record deletes 644245094407
Successful physical file opens 42949672970
Successful physical file closes 1134704589799574
Total failed operations 4294967297
Failed apply operations 1
Failed transaction begins 644245094400
Failed transaction aborts 0
Failed transaction commits 0
Failed transaction success 0
Failed record adds 70
Failed record updates 0
Failed record deletes 0
Failed physical file opens 0
Failed physical file closes 0
Log number of current scan pos 0
Offset of current scan pos 0
current status of replication agent 0
current c-tree API function 0
server connection status 0
28 records returned
fc_get_selectivity( )
Returns the selectivity. false if the selectivity is off; true when on.
Result Set Fields (1)
active BIT
Example
call fc_get_selectivity()
active
------
true
fc_get_sqlstats()
Returns FairCom DB SQL current SQL subsystem statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_sqlstats();
DESCRIPTION VALUE
----------- -----
SQL memory highwater 2908764
current aggregate sum 2507391
current net SQL memory allocations 1884
dynamic statement cache requests 59
dynamic statement cache hits 44
static statement cache requests 36
static statement cache hits 26
current dynamic stmt cache entries 13
highest dynamic stmt cache entries 14
maximum dynamic stmt cache entries 150
current static stmt cache entries 10
highest static stmt cache entries 10
maximum static stmt cache entries 150
current number of dynamic caches 1
current number of static caches 1
current number of local caches 1
current local stmt cache entries 0
highest local stmt cache entries 0
maximum local stmt cache entries 150
local statement cache requests 0
local statement cache hits 0
Tuple fetches by index only scan 0
Tuple fetches by index scan with record retrieval 76
23 records returned
fc_get_sysconfig()
Returns FairCom DB SQL current server configuration information:
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_sysconfig();
Description Value
----------- -----
REPL_MAPPINGS Enabled 0
Max num segments per index 12
Max num indexes per data file 32
Server path separator 92
Client path separator 92
Server Serial Number 89,123,456
6 records returned
fc_get_taskid( )
Returns the current FairCom DB task ID associated with the FairCom DB SQL connection.
Result Set Fields (1)
id INTEGER
Example
call fc_get_taskid()
id
--
19
fc_get_transtats()
Returns FairCom DB SQL current transaction control statistics.
Result Set Fields (2)
Description VARCHAR(64)
Value BIGINT
Example
>call fc_get_transtats();
DESCRIPTION VALUE
----------- -----
# transaction begins 0
# transaction ends 0
# transaction aborts 0
# transaction savepoints 1
# transaction restores 0
# transaction log flush writes 0
DOSFLUSH sync calls 0
transaction log sync calls 2
cumulative transaction time^ 0
cumulative transaction count 0
maximum elapsed tran time^ 0
check point count 1
cumulative checkpoint time^ 0
cumulative checkpoint size 0
checkpoint index buffer writes 0
checkpoint data cache writes 0
log flush count 0
log flush time 0
18 records returned
fc_get_userlist( )
Returns the current list of FairCom DB SQL defined users.
Result Set Fields (3)
USER VARCHAR(32)
DBA VARCHAR(3)
RESOURCE VARCHAR(3)
Example
call fc_get_userlist();
USER DBA RESOURCE
---- --- --------
admin Yes Yes
accounting (null) (null)
guest (null) (null)
sales (null) (null)
fc_ptadmin_num()
Performs partition administration actions to a partitioned table based on the partition number. You must be connected to the database that contains the table. This uses the ptADMINnewthd mode which requires the table to be closed.
Parameters (4)
owner VARCHAR(64)
table_name VARCHAR(64)
action VARCHAR(16)
part_num INTEGER
action is one of the following strings:
( 'purge' | 'reuse' | 'archive' | 'activate' | 'add' | 'rebuild')
Example
>call fc_ptadmin_num('admin', 'custmast', 'purge', 12345);
Requires DBA privilege.
fc_purge_db()
Purge tables that do not exist on disk from system tables.
fc_purge_db()
The stored procedure takes the following arguments:
- table name - This is a matching pattern using the like predicate syntax considering an empty string ('') and '%' (match all). Only tables matching the pattern are affected.
- mode - This parameter can be:
0 - purge missing tables
1 - purge missing tables if created outside SQL (not by the create table statement but "imported")
2 - purge all tables created outside SQL (remove them from SQL but do not delete from disk)
Mode 2 depends on the server being compiled with #define ctSQL_TRACK_ORIGIN active. If this #define is not active, an error is returned.
The fact that fc_purge_db does not delete tables from disk does not depend on the SQL_OPTION DELETE_IMPORTED; it never deletes tables from disk.
This stored procedure produces a resultset containing the list of tables entitled to be purged for which the purging was not successful due to an error, which is reported in the "status" column.
fc_set_blockinglock( )
Sets the blocking lock strategy for FairCom DB SQL queries.
Description
By default, SQL connections block on locks. Situations can arise with mixing SQL operations with existing non-SQL applications. An application may acquire a non-blocking lock, and hold that lock for a length of time, thereby blocking an SQL query which might be expected to return sooner. An "abandoned" lock might indefinitely block the query. To avoid these situation, options are available to change the blocking lock behavior for SQL connections.
Parameters (mode)
mode SMALLINT
Set mode = 1 to enable blocking locks; set mode = 0 to disable blocking locks
A negative mode value enables a blocking lock with a defined timeout value (in seconds) from the absolute value of the parameter.
For example, a value of -2 with a SELECT query times out if it waits on an existing lock for more than 2 seconds.
The scope of the locking behavior is only within the current user session (that is, the current executing SQL thread); it is not global across other connections. Each SQL thread of operation can specify it's own unique locking behavior.
Examples
- Disable blocking locks. Set blocking mode to 0 to cause a SQL statement to fail immediately when it encounters a record that another transaction has locked, updated, or deleted but not yet committed. It returns error -17042: CT - Could not obtain data record lock.
call fc_set_blockinglock( 0 );
- Enable blocking locks. Set blocking mode to 1 to cause a SQL statement to block. The statement will not return when it encounters a record that another transaction has locked, updated, or deleted. It waits indefinitely for the lock to be released or an update or delete to be committed or rolled back. This is the default setting.
call fc_set_blockinglock( 1 );
- Enable blocking locks with a timeout.. Set blocking mode to a negative integer, which is the maximum number of seconds you want a SQL statement to block before it fails with error -17156: CT - timeout. The statement will not return when it encounters a record that another transaction has locked, updated, or deleted but not yet committed. It waits until the lock is released, a pending update or delete is committed or rolled back, or the maximum number of seconds has expired. The following examples sets the timeout to 2 seconds.
call fc_set_blockinglock( -2 );
fc_set_debug( )
Sets the debug level of the FairCom DB SQL database engine for advanced diagnostics.
Parameters (1)
dbg_info VARCHAR(20)
Result Set Fields (1)
DBG_INFO VARCHAR(20)
Example
To enable Java debugging:
call fc_set_debug('NNNNNNNNNNYN');
DBG_INFO
--------
NNNNNNNNNNYN
See Also
- For definitions of the Y/N elements in the TPESQLDBG array, see Advanced FairCom DB SQL Logging
fc_set_file_tran_state( )
Sets the FairCom DB transaction mode for a table.
WARNING: Do not set this value without a complete understanding of c-tree transaction control. There is great potential for data loss if not properly set.
Parameters (3)
owner VARCHAR(64)
table_name VARCHAR(64)
mode TINYINT
Valid modes:
- 0 No transaction control.
- 1 Transaction control without recoverability. (ctPREIMG)
- 2 Transaction control and recoverability. (ctTRNLOG)
Example
call fc_set_file_tran_state( 'admin', 'custmast', 0 );
fc_set_impersonation( )
Enables impersonation of a FairCom DB SQL connection. Specify a taskid of zero to disable impersonation of the connection (default). Specify a taskid of 1 to enable impersonation of the connection by any other connection. Specify a taskid greater than 1 to enable impersonation of the connection only by a connection having that specific task ID.
Parameters (1)
taskid INTEGER
Example
call fc_set_impersonation( 19 )
fc_set_min_card( )
Sets the minimum cardinality value used by the FairCom DB SQL database engine.
Parameters (1)
value INTEGER
Example
call fc_set_min_card( 1000 );
fc_set_nodename( )
Sets the NODE name of the connection. The c-tree NODE name identifies a connection with a string value, that can then be conveniently viewed with monitoring tools.
fc_set_nodename() can be called multiple times within a FairCom DB SQL session to change the node name. It can also be called with an empty string to clear the NODE name.
Parameters (1)
nodename VARCHAR(31)
Example
call fc_set_nodename('accounting');
fc_set_priority( )
Sets the FairCom DB SQL client thread priority.
Note: This feature is supported only on the Windows operating system.
Parameters (1)
priority INTEGER
priority = -3 to +3.
Example
call fc_set_priority( 0 );
WARNING: Use extreme caution when changing this value. If the priority is set too high relative to other tasks, FairCom DB SQL may exhibit unpredictable behavior as other threads may not get enough CPU time by the process scheduler.
fc_set_rowid_visible()
Set an existing ROWID column as visible for a given table.
fc_set_rowid_visible('owner', ‘table’, ‘rowid’, ‘rowid_idx’);
Description
Use this function when you have existing tables that include the hidden $ROWID$ field and do not have a primary key. When you create new tables, use the JSON DB API because it ensures the table works well at all API layers, including the JSON DB, SQL, c-treeDB, and ISAM APIs, The JSON DB API follows best practices and does not create the hidden $ROWID$ field; instead, it automatically creates an id field that is the primary key of the table.
You can optionally create a hidden ROWID column on tables to help bridge between FairCom DB’s SQL and c-treeDB layers. This column has a special internal name of $ROWID$ and has an index. It uses the serial segment auto-numbering index mode, and, thus, creates a unique permanent id value for each row.
The stored procedure, fc_set_rowid_visible(), makes an existing c-treeDB ROWID visible as a column in the table. It also makes its index visible. You must assign a name to the column and index. When the column is visible, you can use it in queries. You cannot update or reposition the ROWID column.
A check is made that the table exists, and has a ROWID field. SQL_ERR_NOTBL or SQL_ERR_NOCOL is returned if not.
A check is made to ensure that the rowid isn’t already exposed with that id name - if so, it fails with a duplicate column exception.
Parameters
owner - Should match existing table owner, or the internal get table ID call doesn’t work.
table - table name with the ROWID to expose.
rowid - symbolic name of the exposed ROWID field
rowid_idx - assigned name for the ROWID index
Example
ISQL> call fc_set_rowid_visible('admin', 'test1', 'id', 'id_idx');
0 records returned
ISQL> select col from syscolumns where tbl='test1';
COL
---
name
age
rating
id
4 records selected
ISQL> table test1
COLNAME NULL ? TYPE LENGTH CHARSET NAME COLLATION
------- ------ ---- ------ ------------ ---------
id NOT NULL BIGINT 8
name CHAR 10
age INT 4
rating TINYINT 1
Limitations
This should only be called once for any given table. Unpredictable results can potentially happen with repeated executions.
fc_set_selectivity( )
Set the selectivity off (0) or on (1)
Parameters (1)
value BIT
Example
call fc_set_selectivity( 1 );
fc_set_sysconfig()
Dynamically enables a limited set of system configuration options as defined by the SetSystemConfigurationOption() API call.
Parameters (2)
option VARCHAR(64)
value VARCHAR(64)
option is one of the following:
'checkpoint_monitor'
'ctstatus_mask'
'diagnostics'
'dynamic_dump_defer'
'function_monitor'
'memory_monitor'
'request_time_monitor'
'vss_writer'
Examples
fc_set_sysconfig('vss_writer', 'YES');
fc_set_sysconfig('diagnostics', 'LOWL_FILE_IO');
fc_set_sysconfig('diagnostics', 'REPLICATE');
fc_set_sysconfig('ctstatus_mast', '~DYNAMIC_DUMP_FILES');
Requires DBA privilege.