December 6, 2018
Tech Nugget: SQL logging with FairCom DB and FairCom EDGE
Note: c-treeACE became FairCom DB and c-treeEDGE became FairCom EDGE in November 2020.
If you are using FairCom DB or FairCom EDGE, there are three ways to log information about SQL statements being run on the server. Each one works in a slightly different way, so it is important to pick the one that includes the information closest to what you want. They are controlled by keywords added to the ctsrvr.cfg file. Here are the keywords and examples:
SQL_DEBUG LOG_STMT
Adding this keyword in v11.6 and later logs a start time, IP number, database name, user, connection number and the statement to sqlserver.log. The info is written when the statement is started. This keyword is best for finding the user running long queries since it logs the info right away. This is the one to start with if it is available in your version.
Example:
{“timestamp”:”Tue Nov 13 11:54:25 2018″,”ipaddr”:”127.0.0.1″,”db”:”CTREESQL”,”user”:”admin”,”thread”:25,”statement”:”select ‘One’ from testTable “}
SQL_DEBUG LOG_STMT_TIMES
Adding this keyword logs the User#, start time and duration of statements to sql_server.log. The info is written when the statement is completed. This one gives additional info about how long the statement ran.
Example:
Start: Mon Nov 5 14:43:46 2018
– User# 00023 Elapsed: 0 sec. for PREPARE select ‘One’ from testTable
Start: Mon Nov 5 14:43:46 2018
– User# 00023 Elapsed: 0 sec. for OPEN select ‘One’ from testTable
SQL_DEBUG LOG_STUBS_MED
This keyword logs the SQL commands to the Function Monitor window (available from ctadmn).
Example:
SQL: select ‘One’ from testtable
Also, feel free to check out our previous Tech Nugget: FairCom SQL Connection Strings.