Check database consistency¶
This page describes countDB and diffDB, the two maintenance-console utilities you use to verify and resolve data consistency across nodes.
KX Sensors provides two maintenance-console utilities for checking data consistency across nodes. countDB reports record count mismatches between the HDB, IDB and RDB on one or more nodes — for example, to confirm that data on your primary and secondary nodes is identical or that replication is working properly. diffDB goes a step further: it identifies the actual mismatched records and lets you apply the most suitable records to each target node. Both utilities target the same set of tables, controlled by the utilTargetTables parameter in systemParams.yaml.
countDB¶
countDB allows you to count the total number of records in the HDB, IDB and RDB on one or more nodes; for example, you want to ensure that data on your primary and secondary nodes is identical or that data replication is working properly. The default tables counted are listed in systemParams.yaml; you can edit this configuration parameter if you wish to add or remove specific tables.
| Parameter | Description | Default |
|---|---|---|
utilTargetTables |
Tables targeted by countDB and diffDB utilities | ReadingReadingDeltaReadingHistoryReadingHistoryDelta |
You can look up the manual for countDB by means of the countDB`help command. It shows the default values for your nodes, start and end times, along with additional options that may be provided to the interface.
q)countDB`help
<countDB> is a utility that counts database records for the tables configured in <.cfgp.utilTargetTables>.
The list of target tables is: `Reading`ReadingDelta`ReadingHistory`ReadingHistoryDelta`DerivedReading`DerivedReadingDelta`DerivedReadingHistory`DerivedReadingHistoryDelta`VEEException
Usage: countDB[`<nodes>;<start>;<end>;<opts>] or countDB`help
nodes {symbol|symbol[]} Target nodes [DEFAULT: `A`B]
start {date|timestamp} Inclusive start time [DEFAULT: -0Wp]
end {date|timestamp} Exclusive end time [DEFAULT: 0Wp]
opts {symbol[]|dict} Program options; symbols are interpreted as a dictionary whose values are `1b`
<nodes> may contain `yesterday`today`tomorrow to set the start time to the start the specified day
e.g. countDB`yesterday <==> countDB[`;.z.d-1]
e.g. countDB`B`C`today <==> countDB[`B`C;.z.d]
Valid options:
details Enables display of detailed results (automatic on mismatch)
testName Name under which to cache <countDB> result in <.cdb.testres>
Tables with mismatching sums among nodes are shown with *. as a prefix
Use output colors by setting <.cdb.usecolors> to <1b>
If the details flag is switched on, countDB will show individual counts for each table even if there is no mismatch between the counts among nodes. If the details flag is switched off, countDB will only show individual counts for each table if there is a mismatch between the counts among nodes. The details flag is switched on by default.
You can activate output colors by defining .cdb.usecolors to 1b before executing the countDB command. Once activated, it remains in effect for the duration of your session. You can deactivate the output colors option by reverting .cdb.usecolors to 0b.
Launch countDB¶
countDB is loaded automatically after launching a maintenance console. If the interface needs to be loaded elsewhere, it can be loaded using .load.lq`:src/proc/maint/cdbmaint.q.
Parameters¶
There are 3 parameters used to control countDB: nodes, start and end.
If you enter a specific start and end time, only records with activity during that period will be considered. If you do not enter a time range, all records regardless of timestamp will be counted; that is, from negative infinity to positive infinity.
start and end may be specified as either timestamps or dates.
countDB includes three predefined keywords for start and end times: yesterday, today and tomorrow. These date keywords represent the start date in a date range.
Example 1¶
If you want to count everything (including all .cfgp.utilTargetTables on all nodes across all dates):
countDB[]
Example 2¶
If you want to count on all nodes with a date range starting from yesterday until the infinite future:
countDB`yesterday
Example 3¶
If you want to count on nodes A and B with a date range starting from today until the infinite future:
countDB`A`B`today
Example 4¶
If you want to count on nodes A and B with start and end times of negative infinity/infinity and you do not want to see individual counts for each table if A matches B:
countDB[`A`B;`;`;enl[`details]!enl 0b]
Depending on how busy your system is and the size of your tables, countDB may take a few moments to execute.
For each table included in .cfgp.utilTargetTables, countDB shows the number of records in each database (HDB, IDB and RDB). In the example below, the count was run on nodes A and B. The counts for A and B did not match and the difference in the number of records was one; that is, A had one more record than B: [0 -1]. The table with the mismatched sum is prefixed by *. (*.Reading).
q)countDB[]
2025.03.05 12:12:59.788 INFO [test] SAPI {test-17} Sending .kxsi.countDB to kxsGW_A, corr=17 args=(start=-0W end=0W opts=[0])
2025.03.05 12:12:59.788 INFO [test] SAPI {test-18} Sending .kxsi.countDB to kxsGW_B, corr=18 args=(start=-0W end=0W opts=[0])
q)2025.03.05 12:12:59.792 INFO [test] SAPI {test-17} .kxsi.countDB response received from kxsRP_A1, procs=`kxsGW_A`kxsRP_A1`kxsRDB_A1`kxsHDB_A1 RC=0 (OK) AC=0 (OK) res=([1])
2025.03.05 12:12:59.794 INFO [test] SAPI {test-18} .kxsi.countDB response received from kxsRP_B1, procs=`kxsGW_B`kxsRP_B1`kxsRDB_B1`kxsHDB_B1 RC=0 (OK) AC=0 (OK) res=([1])
2025.03.05 12:12:59.795 INFO [test] DB
tbl | kxsHDB_A1 kxsRDB_A1 kxsHDB_B1 kxsRDB_B1
---------------------------| --------------------------------------
*.Reading | 0 10000 0 9999
ReadingDelta | 0 0 0 0
ReadingHistory | 0 0 0 0
ReadingHistoryDelta | 0 0 0 0
DerivedReading | 0 0 0 0
DerivedReadingDelta | 0 0 0 0
DerivedReadingHistory | 0 0 0 0
DerivedReadingHistoryDelta | 0 0 0 0
VEEException | 0 0 0 0
|
TOTALS | 0 10000 0 9999
2025.03.05 12:12:59.795 INFO [test] DB MISMATCHED: A=10000 B=9999 -> [0 -1]
2025.03.05 12:12:59.795 INFO [test] DB invokeTS=2025.03.05D12:12:59.788275978 latency=0D00:00:00.006343070
2025.03.05 12:12:59.795 INFO [test] DB startTS=-0Wp endTS=0Wp
Known issues¶
It may be possible to experience a temporary countDB mismatch while your system catches up on pending EOIs.
If you configure one or more nodes to auto-delete old records through a system parameter such as hdbMinPrtns, the use of the negative infinity date parameter -0Wp may lead to counts that don't match exactly because of staggered EODs on each node. To avoid this issue, select a start time for your count a day or two later than your oldest partition.
diffDB¶
diffDB is a utility function that you can use to report database mismatches across target nodes and determine the most suitable records to be written to each of the nodes.
diffDB targets both on-disk historical data and the in-memory data across all of RDB/IDB/HDB.
During execution, results are cached under ${KXS_ROOT}/misc/diffDB. You can then use the apply interface to apply the most suitable records to the database on each of the target nodes.
The utilTargetTables configuration parameter located in systemParams.yaml (which is shared with the countDB utility function) controls tables targeted by diffDB.
diffDB uses the key and update timestamp columns of target tables to report database mismatches. To consider additional columns, use the isDiffDB property columns within a schema. This is essential for unkeyed tables to provide a logical key for diffDB to identify mismatches.
Before using the apply interface, you must first invoke diffDB with the fetchAll option.
View your data¶
Details are automatically displayed in the console on mismatch, but also cached in ${MISC}/diffDB for further investigation by a user.
Directories of interest:
| Directory | Contents |
|---|---|
${KXS_ROOT}/misc/diffDB/diff |
Contains the mismatches on each node as reported by diffDB |
${KXS_ROOT}/misc/diffDB/lkup |
Contains the minimal set of keys which need to be queried by diffDB to retrieve the most suitable records to write to each target node, along with which keys are to be written to each node |
${KXS_ROOT}/misc/diffDB/rows |
Contains the full rows to be written to each target node (only present after an invocation with the fetchAll option) |
These directories are structured using table name/partition combinations. For example, ${MISC}/diffDB/diff/<table>/<date>.
Note
IDB partition numbers, which are assumed to never exceed, are represented using dates. For example, int=1 is represented as prtn="d"$1=2000.01.02.
Load and use diffDB¶
diffDB will be automatically loaded on start-up into any maintenance console. The script can be loaded elsewhere using lq`:src/proc/maint/ddbmaint.q.
Valid options:
| Option | Description |
|---|---|
| fast | Stops after CRC processing, bypassing detail fetch |
| fetchAll | Fetches all values from rows with mismatched properties |
| ignoreMem | Ignores in-memory data throughout all API calls |
| ignoreIDB | Ignores IDB partitions throughout all API calls |
| details | Enables display of detailed results (automatically enabled on mismatch) |
| pit | Takes a point-in-time snapshot on each node and works from that |
| prompt | Prompts before fetching rows |
| clean | Removes remnants of a prior invocation that might have left a PIT or cached result files on a node |
| apply | Applies the differences recorded by a prior diffDB invocation performed with fetchAll enabled to the specified nodes. This command requires arming |
Example usage¶
q)diffDB`help
diffDB reports database mismatches across target nodes and determines the most suitable records to be written to each
of the nodes using key and update timestamp columns, targetting both on-disk data and the in-memory data across all
of RDB/IDB/HDB. Results are cached under ${KXS_ROOT}/misc/diffDB throughout execution, and the most suitable records
can be later applied to the database on each of the target nodes using the apply interface.
This list of tables is currently set to: `Reading`ReadingDelta`ReadingHistory`ReadingHistoryDelta`VEEException
Usage: diffDB[`<nodes>;`<start>;`<end>;`<opts>] or diffDB`clean or diffDB`apply or diffDB`help
nodes {symbol|symbol[]} Target nodes [DEFAULT: `A`B`C]
start {date|timestamp} Inclusive start time [DEFAULT: -0Wp]
end {date|timestamp} Exclusive end time [DEFAULT: 0Wp]
opts {symbol[]|dict} Program options. Symbols are interpreted as a dictionary whose values are `1b` [DEFAULT: ()!()]
Valid options:
fast Stops after CRC processing, bypassing detail fetch.
fetchAll Fetches all values from rows with mismatched properties
ignoreMem Ignores in-memory data throughout all API calls.
ignoreIDB Ignores IDB partitions throughout all API calls.
details Enables display of detailed results (automatically enabled on mismatch).
pit Takes a point-in-time snapshot on each node, and works from that.
prompt Prompts before fetching rows.
clean Removes remnants of a prior invocation that might have left a PIT or cached result files on a node.
apply Applies the differences recorded by a prior diffDB invocation performed with `fetchAll` enabled to the specified nodes. This command requires arming.
q)
See below what commands you can run with diffDB.
| Command | Description |
|---|---|
| diffDB`help | Display help |
| diffDB[] | Executes diffDB for all nodes with infinite time bounds |
diffDB[;;;] |
Same as diffDB[] but with arguments explicitly specified (any number of which can be dropped and assumed to be the null symbol by the interface) |
diffDBAB |
Executes diffDB on nodes A and B with infinite time bounds |
| diffDB[`;2024.06.01;2024.06.10] | Executes diffDB on all nodes from 2024.06.01 (inclusive) to 2024.06.10 (exclusive) |
diffDB[;;;fetchAllignoreMemprompt] |
Executes diffDB for all nodes with infinite time bounds and the fetchAll/ignoreMem/prompt options specified |
| diffDB`apply | Executes diffDB apply to resolve differences determined over all nodes (This command requires arming of the diffDB arm group) |
diffDB[AB;;;`apply] |
Executes diffDB apply to resolve differences determined on nodes A and B (This command requires arming of the diffDB arm group) |
| diffDB`clean | Executes diffDB clean to clean residual data left by diffDB over all nodes (This command requires arming of the diffDB arm group) |
diffDB[AB;;;`clean] |
Executes diffDB clean to clean residual data left by diffDB on nodes A and B (This command requires arming of the diffDB arm group) |
DBW must not be processing diffDBapply when diffDBclean is executed! This command requires arming to prevent any accidental invocations.
Note that diffDBclean will be invoked automatically following diffDBapply provided the maintenance console where it is invoked is still available to track progress of the apply operation.
q)diffDB[`;2024.09.04;2024.09.06]
2024.09.06 10:52:27.768 INFO [kxsTest_M] DDB Sending ddbSize, nodes=`A`B`C
2024.09.06 10:52:27.768 INFO [kxsTest_M] SAPI {kxsTest_M-159} Sending .kxsi.ddbSize to kxsGW_A, corr=159 args=(tns=[5] start=2024.09.04 00:00:00 end=2024.09.06 00:00:00 opts=[0])
2024.09.06 10:52:27.768 INFO [kxsTest_M] SAPI {kxsTest_M-160} Sending .kxsi.ddbSize to kxsGW_B, corr=160 args=(tns=[5] start=2024.09.04 00:00:00 end=2024.09.06 00:00:00 opts=[0])
2024.09.06 10:52:27.768 INFO [kxsTest_M] SAPI {kxsTest_M-161} Sending .kxsi.ddbSize to kxsGW_C, corr=161 args=(tns=[5] start=2024.09.04 00:00:00 end=2024.09.06 00:00:00 opts=[0])
q)2024.09.06 10:52:27.773 INFO [kxsTest_M] SAPI {kxsTest_M-160} .kxsi.ddbSize response received from kxsRP_B1, procs=`kxsGW_B`kxsRP_B1`kxsIDB_B1`kxsRDB_B1`kxsHDB_B1 RC=0 (OK) AC=0 (OK) res=([14])
2024.09.06 10:52:27.773 INFO [kxsTest_M] SAPI {kxsTest_M-161} .kxsi.ddbSize response received from kxsRP_C1, procs=`kxsGW_C`kxsRP_C1`kxsIDB_C1`kxsRDB_C1`kxsHDB_C1 RC=0 (OK) AC=0 (OK) res=([14])
2024.09.06 10:52:27.773 INFO [kxsTest_M] SAPI {kxsTest_M-159} .kxsi.ddbSize response received from kxsRP_A1, procs=`kxsGW_A`kxsRP_A1`kxsIDB_A1`kxsRDB_A1`kxsHDB_A1 RC=0 (OK) AC=0 (OK) res=([14])
2024.09.06 10:52:27.773 INFO [kxsTest_M] DDB Invoked all ddbSize callbacks
2024.09.06 10:52:27.773 INFO [kxsTest_M] DDB Mismatches found during ddbSize comparison
2024.09.06 10:52:27.773 INFO [kxsTest_M] DDB File Size Mismatches
2024.09.06 10:52:27.773 INFO [kxsTest_M] DDB tn prtn
2024.09.06 10:52:27.773 INFO [kxsTest_M] DDB -----------------
2024.09.06 10:52:27.773 INFO [kxsTest_M] DDB Reading 2024.09.05
2024.09.06 10:52:27.773 INFO [kxsTest_M] DDB Sending ddbCRC, nodes=`A`B`C
2024.09.06 10:52:27.773 INFO [kxsTest_M] SAPI {kxsTest_M-162} Sending .kxsi.ddbCRC to kxsGW_A, corr=162 args=(start=2024.09.04 00:00:00 end=2024.09.06 00:00:00 opts=[0] data=[13])
2024.09.06 10:52:27.774 INFO [kxsTest_M] SAPI {kxsTest_M-163} Sending .kxsi.ddbCRC to kxsGW_B, corr=163 args=(start=2024.09.04 00:00:00 end=2024.09.06 00:00:00 opts=[0] data=[13])
2024.09.06 10:52:27.774 INFO [kxsTest_M] SAPI {kxsTest_M-164} Sending .kxsi.ddbCRC to kxsGW_C, corr=164 args=(start=2024.09.04 00:00:00 end=2024.09.06 00:00:00 opts=[0] data=[13])
2024.09.06 10:52:27.778 INFO [kxsTest_M] SAPI {kxsTest_M-163} .kxsi.ddbCRC response received from kxsRP_B1, procs=`kxsGW_B`kxsRP_B1`kxsIDB_B1`kxsRDB_B1`kxsHDB_B1 RC=0 (OK) AC=0 (OK) res=([13])
2024.09.06 10:52:27.778 INFO [kxsTest_M] SAPI {kxsTest_M-164} .kxsi.ddbCRC response received from kxsRP_C1, procs=`kxsGW_C`kxsRP_C1`kxsIDB_C1`kxsRDB_C1`kxsHDB_C1 RC=0 (OK) AC=0 (OK) res=([13])
2024.09.06 10:52:27.779 INFO [kxsTest_M] SAPI {kxsTest_M-162} .kxsi.ddbCRC response received from kxsRP_A1, procs=`kxsGW_A`kxsRP_A1`kxsIDB_A1`kxsRDB_A1`kxsHDB_A1 RC=0 (OK) AC=0 (OK) res=([13])
2024.09.06 10:52:27.779 INFO [kxsTest_M] DDB Invoked all ddbCRC callbacks
2024.09.06 10:52:27.779 INFO [kxsTest_M] DDB No mismatches found during ddbCRC comparison
2024.09.06 10:52:27.779 INFO [kxsTest_M] DDB Starting ddbFetchKeys
2024.09.06 10:52:27.779 INFO [kxsTest_M] DDB Sending ddbFetchKeys, tn=Reading prtn=2024.09.05
2024.09.06 10:52:27.779 INFO [kxsTest_M] SAPI {kxsTest_M-165} Sending .kxsi.ddbFetchKeys to kxsGW_A, corr=165 args=(tn=Reading prtn=2024.09.05 start=2024.09.04 00:00:00 end=2024.09.06 00:00:00 opts=[0])
2024.09.06 10:52:27.779 INFO [kxsTest_M] SAPI {kxsTest_M-166} Sending .kxsi.ddbFetchKeys to kxsGW_B, corr=166 args=(tn=Reading prtn=2024.09.05 start=2024.09.04 00:00:00 end=2024.09.06 00:00:00 opts=[0])
2024.09.06 10:52:27.779 INFO [kxsTest_M] SAPI {kxsTest_M-167} Sending .kxsi.ddbFetchKeys to kxsGW_C, corr=167 args=(tn=Reading prtn=2024.09.05 start=2024.09.04 00:00:00 end=2024.09.06 00:00:00 opts=[0])
2024.09.06 10:52:27.782 INFO [kxsTest_M] SAPI {kxsTest_M-165} .kxsi.ddbFetchKeys response received from kxsRP_A1, procs=`kxsGW_A`kxsRP_A1`kxsRDB_A1`kxsIDB_A1`kxsHDB_A1 RC=0 (OK) AC=0 (OK) res=([49])
2024.09.06 10:52:27.782 INFO [kxsTest_M] SAPI {kxsTest_M-166} .kxsi.ddbFetchKeys response received from kxsRP_B1, procs=`kxsGW_B`kxsRP_B1`kxsIDB_B1`kxsRDB_B1`kxsHDB_B1 RC=0 (OK) AC=0 (OK) res=([48])
2024.09.06 10:52:27.782 INFO [kxsTest_M] SAPI {kxsTest_M-167} .kxsi.ddbFetchKeys response received from kxsRP_C1, procs=`kxsGW_C`kxsRP_C1`kxsIDB_C1`kxsRDB_C1`kxsHDB_C1 RC=0 (OK) AC=0 (OK) res=([49])
2024.09.06 10:52:27.782 INFO [kxsTest_M] DDB Invoked all ddbFetchKeys callbacks, tn=Reading prtn=2024.09.05
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB Differences determined by diffDB, node=A tn=Reading prtn=2024.09.05
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB chanID readTS | updateTS
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB ----------------------------------- | -----------------------------------
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB 2 2024.09.05D00:00:00.000000000 | 2024.09.06D10:48:33.437845609
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB 2 2024.09.05D00:30:00.000000000 | 2024.09.06D10:48:33.437845609
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB 2 2024.09.05D23:00:00.000000000 | 2024.09.06D10:48:32.437845609
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB Differences determined by diffDB, node=B tn=Reading prtn=2024.09.05
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB chanID readTS | updateTS
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB ----------------------------------- | -----------------------------------
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB 2 2024.09.05D00:00:00.000000000 | 2024.09.06D10:48:32.437845609
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB 2 2024.09.05D23:00:00.000000000 | 2024.09.06D10:48:32.437845609
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB Differences determined by diffDB, node=C tn=Reading prtn=2024.09.05
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB chanID readTS | updateTS
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB ----------------------------------- | -----------------------------------
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB 2 2024.09.05D00:00:00.000000000 | 2024.09.06D10:48:32.437845609
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB 2 2024.09.05D23:00:00.000000000 | 2024.09.06D10:48:33.437845609
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB 2 2024.09.05D23:30:00.000000000 | 2024.09.06D10:48:33.437845609
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB Most suitable records determined by diffDB, tn=Reading prtn=2024.09.05
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB chanID readTS | updateTS
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB ----------------------------------- | -----------------------------------
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB 2 2024.09.05D00:00:00.000000000 | 2024.09.06D10:48:33.437845609
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB 2 2024.09.05D00:30:00.000000000 | 2024.09.06D10:48:33.437845609
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB 2 2024.09.05D23:00:00.000000000 | 2024.09.06D10:48:33.437845609
2024.09.06 10:52:27.783 INFO [kxsTest_M] DDB 2 2024.09.05D23:30:00.000000000 | 2024.09.06D10:48:33.437845609
2024.09.06 10:52:27.784 INFO [kxsTest_M] DDB Finished ddbFetchKeys
Known issues¶
diffDB`apply only allows the resolution of HDB partitions. Resolving IDB partitions requires future PIT insertion work, and in-memory data is too complex to reliably resolve. However, diffDB can resolve this data the following day, as it will be written to HDB partitions after EOD.