Database Administrator's Guide

ctstat - Statistics Utility

The FairCom DB Statistics Utility, ctstat, is a client utility used to display statistics collected by FairCom DB. This utility provides valuable real-time monitoring of critical FairCom DB operations.

Operational Model:

Client

Usage

# ctstat report_type [-s svn] [-u uid] [-p upw]
[-i int [cnt]] [-h frq] [-d] [-m] [-t]

Reports:

-clf	Get the CHECKLOCK_FILE list entries
-deleted_files	Deleted Files Report
-file [csv]	File Activity Report
-filelist listname	List the entries on the specified file list
-filelocks file [N]	List all locks on a data file. Display Nth key
-fileops	File Operation Activity Report
-func	Function Timing Report
-funcfile	Function timing by File Report
-iotime on\|off	Turn disk I/O call timing on or off
-isam	ISAM Activity Report
-ma logfile	Log aggregate memory allocations to the specified file
-meminfo	Server Memory Info Report
-mf logfile	Log all memory allocations to the specified file
-ml	Get current memory allocation statistics
-mr min,max	Log only memory allocation statistics
-ms	Output memory allocation statistics
-mt options	Set memory allocation tracking options. Examples: -mt +ALL -mt +PI8TYP, -MBATYP
-mu	Unload module debug symbols
-sql	SQL Activity Report
-text	System Activity Report
-tranlog_flush	Transaction Log Flush Fixed Histogram Report
-userinfo	User Report with stats from USERINFO() function
-userinfox	User Report with stats from USERINFOX() function
-userlocks user	List all locks held by a user
-vaf file...	Admin-File Report
-vah handle...	Admin-User Report by User Handle
-var	Admin-Replication Reader Report
-vas	Admin-System Report
-vat	Admin-Transaction Report
-vau user...	Admin-User Report by User Name
-vtf file...	Tivoli-File Report
-vts	Tivoli-System Report
-wrktime on\|off\|reset	Turn function call timing on or off, or reset statistics

Options:

-d	Show cache stats as delta
-h frq	Print a description header every frq outputs
-i int [cnt]	Pause int seconds for optional cnt times
-I int [cnt]	Pause int seconds also before first row for optional cnt times
-m	Show memory file stats when using -vaf report
-p upw	User password
-s svn	c-tree Server name
-t	Output timestamp with header
-u uid	User name

Authentication File

This utility supports the use of an encrypted password file. Encrypted password files keep user IDs and passwords from plain view when the utility is used within a script file. They are created with the ctcmdset utility. The plain text form of the file should be:

; User Id
USERID ADMIN
; User Password
PASSWD <pass>

Use the -1 option to specify the name of the encrypted file.

-filelocks Notes

The -filelocks option lists all locks on a data file and, optionally, displays the Nth key. The lock offset and the associated keys are not read at the same time. Since we are reading records locked by other users to generate the key, there is no guaranteed relationship between the lock and the displayed key. The following are possible scenarios:

The displayed key is from before or after any changes made by the lock holder.
The locked offset no longer holds a valid record (it has been deleted, or updated and moved).
The locked offset could have been locked/modified/unlocked more than once between the time the lock offset was acquired and the time the record is read, so the offset could hold an entirely different record than what was originally locked.

The -filelocks file [key] command supports c-tree's standard wildcard filename matching for the specified file, allowing locks from multiple files to be displayed. The standard wildcards (used by ctsrvr.cfg keywords such as MEMORY_FILE and REPLICATE, etc) are:

* - Multi-character match

? - Single-character match

^ - Negation (must be first character)

-fileops Notes

Database resource consumption requires close monitoring of all file I/O activities. FairCom DB tracks values for additional physical file operations. The following values are tracked:

logical file opens
logical file closes
physical file opens
physical file closes
file creates
file renames
file deletes

Each value is a cumulative value since server startup, stored as an 8-byte integer field in the system snapshot structure.

The counters include every call, not just successful calls. Note that the physical file open count can be smaller than the physical file close count, because when a file is created it does not increment the physical file open count.

The ctstat utility supports a new option, -fileops, that displays these counters. Example:

ctstat -fileops -t -i 1 -h 10 -u ADMIN -p ADMIN -s FAIRCOMS

logopn/s logcls/s phyopn/s phycls/s filcre/s filren/s fildel/s total/s

0 0 0 0 0 0 0 0

154 158 23 26 13 0 3 377

959 926 70 63 25 0 4 2047

1016 989 4 0 0 1 1 2011

574 562 0 0 0 0 0 1136

481 486 2 0 0 0 0 969

425 417 0 0 0 0 0 842

376 380 0 0 0 0 0 756

The ctstat -fileops option requires a server that uses snapshot version 21 or later. If this option is used with a server that uses an earlier version of the utility, the utility fails with the error message:

Error: The -fileops option requires snapshot version 21, but this server is using snapshot version <version>.

where <version> is the older snapshot version that FairCom DB is using.

The snapshot file parsing utility, ctsnpr, has been updated to support the new SNAPSHOT.FCS file format.

-tranlog_flush notes:

The -tranlog_flush displays the transaction log flush histogram values. Use the -d option to display delta values. Otherwise, ctstat displays the cumulative histogram values. For example:

C:\> ctstat -tranlog_flush -h 1 -t -d -u ADMIN -p ADMIN -s FAIRCOMS -d

Fri Oct 4 16:24:30 2024

Since server startup:

tranlog flush count: 21761

tranlog flush time: 25.737986

tranlog flush average: 0.001183

Since last checkpoint:

tranlog flush count: 2

tranlog flush time: 0.007201

tranlog flush average: 0.003600

Since last sample:

tranlog flush count: 324

tranlog flush time: 0.495657

tranlog flush average: 0.001530

adaptive histogram adjustments: 3

time since most recent adjustment: 0

time since fixed histogram last cleared: 18

fixed histogram adaptive histogram

count time count time

132 < 0.001000 0 < 0.000031

0 < 0.002000 0 < 0.000062

192 < 0.003000 0 < 0.000093

0 < 0.004000 0 < 0.000124

1 < 0.005000 0 < 0.000155

0 < 0.006000 0 < 0.000186

0 < 0.007000 0 < 0.000217

0 < 0.008000 0 < 0.000248

0 < 0.009000 0 < 0.000279

0 < 0.010000 0 < 0.000310

0 < 0.011000 0 < 0.000341

0 < 0.012000 0 < 0.000372

0 < 0.013000 0 < 0.000403

0 < 0.014000 0 < 0.000434

0 < 0.015000 0 < 0.000465

0 < 0.016000 0 < 0.000496

0 < 0.017000 0 < 0.000527

0 < 0.018000 0 < 0.000558

0 < 0.019000 0 < 0.000589

0 < 0.020000 0 < 0.000620

0 < 0.021000 1 < 0.000651

0 < 0.022000 0 < 0.000682

0 < 0.040000 [0 avg] 1 < 0.001240 [0 avg]

0 >= 0.040000 0 >= 0.001240

-userinfox Notes

In V11.6 and later, the -userinfox option displays extended connection information. For example:

ctstat -userinfox -u ADMIN -p ADMIN -s FAIRCOMS -h 1 -t -i 1

status lastrequest trntime mem fils time uid/tid/nodename commprotocol readops readbytes writeops writebytes datarqsts datahits indexrqsts indexhits

0s busy TRANEND 0s 96K 1 3s CTQA/21/(2) ctqa FSHAREMM 643 35831372 706 16111449 18569 17930 2535 2535

0s busy USERINFOX -- 76K 2 11s ADMIN/22/ctstat FSHAREMM 4 16544 0 0 12 11 0 0

0s busy TRANEND 0s 70K 1 3s CTQA/23/(3) ctqa FSHAREMM 566 31750812 507 14227936 23180 22614 1467 1467

0s busy FRSVREC 0s 75K 1 3s CTQA/24/(4) ctqa FSHAREMM 754 41842916 520 16605870 32476 31722 2484 2484

0s busy FRSVREC 0s 90K 1 3s CTQA/25/(5) ctqa FSHAREMM 833 46135384 649 19387611 25274 24441 3602 3602

0s busy TRANEND 0s 94K 1 3s CTQA/26/(6) ctqa FSHAREMM 760 43814980 548 16663966 28098 27339 2700 2700

2s busy RRDREC 2s 236K 5 3s CTQA/27/(13) ctqa FSHAREMM 560 11343722 644 3677007 853 793 2033 1898

0s busy FRSREC 0s 253K 2 3s CTQA/28/(8) ctqa FSHAREMM 819 52887780 1690 53585664 40324 39518 6342 6341

0s busy TRANABT 0s 260K 2 3s CTQA/29/(9) ctqa FSHAREMM 776 50069732 1456 45076587 38689 37925 5968 5968

0s busy TRANEND 0s 183K 2 3s CTQA/30/(10) ctqa FSHAREMM 0 0 2198 44113920 0 0 776 776

0s busy NXTREC 0s 2956K 1 2s CTQA/31/(18) ctqa FSHAREMM 455 29557760 361 15667386 10848 10397 2 2

2s busy BATSETX 3s 16950K 6 3s CTQA/32/(12) ctqa FSHAREMM 17139 142302504 45462 42391289 10232 8130 80823 80756

-userlocks Notes

For the -userlocks report:

If UserID is a number, it is interpreted as a task ID.
If UserID is a string, it is interpreted as a name, and information on locks held by each task ID with a matching name is returned.

Because the -userlocks report may generate a large number of server calls (for each task ID and file), the -userlocks report interval may be increased up to 60 seconds, depending on the number of matching users and files involved.

Filelocks and Userlocks Note: Dumping large quantities of locks in a very active system could affect performance.

List files on internal server lists

In V12 and later, ctstat provides a list of files on specific internal server lists, such as ctKEEPOPEN.

Example

C:\> ctstat ‑filelist keepopen_list ‑u ADMIN ‑p ADMIN ‑d ‑h 1 ‑t ‑s FAIRCOMS

Fri Dec 20 09:29:39 2019

value member scale filename

1 0 0 mark.dat

-isam header changed to avoid misunderstanding

The header of the ctstat -isam command displays a /s suffix to indicate that the statistics are per second. For example, the following syntax will produce the output below:

ctstat.exe -s FAIRCOMS -u ADMIN -p ADMIN -h 2 -isam

add/s del/s upd/s read/s total/s

0 0 0 0 0

add/s del/s upd/s read/s total/s

0 0 0 0 0

Note: This constitutes a Compatibility Change.

In This Section

Admin-System Report -vas

Tivoli-System Report -vts

Admin-File Report -vaf

Tivoli-File Report -vtf

Admin-User Report -vau