Trace an SAP HANA Client Connection

1. Enter the following command to see the current trace settings:

   Shell Copy

   hdbsqldbc_cons SHOW ALL

   

   hdbsqldbc_cons utility can be found in the sap/hdbclient folder.

2. To enable tracing of SQL statements at the INFO level, to enable tracing of TIMING info, and to specify the trace file location and name, enter the following commands:

   Shell Copy

   hdbsqldbc_cons TRACE SQL ON LEVEL INFO
   hdbsqldbc_cons TRACE TIMING ON
   mkdir c:\temp\traces
   hdbsqldbc_cons TRACE FILENAME c:\temp\traces\SQLDBC-%p.txt

   Shell Copy

   hdbsqldbc_cons TRACE SQL ON LEVEL INFO
   hdbsqldbc_cons TRACE TIMING ON
   mkdir -p /tmp/traces
   hdbsqldbc_cons TRACE FILENAME /tmp/traces/SQLDBC-%p.txt

   The %p will be replaced with the process ID of the traced application. Including %p in the file name ensures that each process can write its own trace file.

   The next step provides an example of sending the trace output to stdout or stderr. Another option for Node.js applications is to specify a callback to receive the trace output to using the onTrace method which is shown in the tutorial Connect Using the SAP HANA Node.js Interface.

   Example trace categories include:

   • API
   • SQL
   • TIMING

   Some trace categories such as SQL also have severity levels:

   • DEBUG
   • INFO
   • WARNING
   • ERROR
   • FATAL

   For more information, see SQLDBC Tracing.

   To see the current settings run:

   Shell Copy

   hdbsqldbc_cons SHOW ALL

   

3. Run the following command to connect to HDBSQL and query for the status. Since tracing is enabled, a trace file will be generated and can be used to see which SQL statements are called by \s.

   Shell Copy

   hdbsql -U User1UserKey "\s"

4. Find and open the generated trace file. Remember to replace #### to match the trace file name you wish to open.

   Shell Copy

   cd c:\temp\traces
   dir SQLDBC*.txt /od
   notepad SQLDBC-####.txt

   Shell Copy

   cd /tmp/traces
   ls -lt
   pico SQLDBC-####.txt

   

   Notice that the trace settings are shown at the top of the file.

   Code Copy

   libSQLDBCHDB 2.16.18.1676320318
   SYSTEM: Microsoft Windows / X64
   BUILD AT: 2023-02-13 20:43:00
   BRANCH: unknown
   BUILD MODE: rel
   APPLICATION: C:\SAP\hdbclient\hdbsql.exe
   HOST: W-R90XC65K
   OS USER: I234567
   CURRENT DIRECTORY: c:\temp\traces
   TRACE FILE NAME: c:\temp\traces\SQLDBC-55652.txt
   PROCESS ID: 55652
   TRACE FILE WRAP COUNT: 0
   
   ---
   Enabled Traces:
   
   SQL Trace: Level INFO
   Distribution Trace: Level INFO
   Timing Trace
   
   ---

   If the trace settings change while tracing is enabled, the Enabled Traces section will appear again in the trace file.

   If you are experiencing issues with SQLDBC tracing, consult Troubleshooting SQLDBC Tracing.

5. Search through the trace file and notice the SQL statements that were executed.

   Code Copy

   ::PREPARE SQLCURS_1 2023-04-07 12:46:38.497000 [0x000002540c111af0]
   SQL COMMAND: SELECT VERSION FROM SYS.M_DATABASE

6. To turn off tracing for all categories, run the following command:

   Shell Copy

   hdbsqldbc_cons TRACE OFF
   hdbsqldbc_cons SHOW ALL

The following are some additional options for tracing.

1. The FLUSH setting, when turned on, can reduce I/O activity. If you are investigating a crash, it should be turned on to ensure that all the trace information is recorded.

   Shell Copy

   hdbsqldbc_cons TRACE FLUSH ON

2. Note that tracing should be turned off when not in use, as it does impact performance, memory and disk size. The following are some additional options to limit the trace file size to 100 KB (plus an archive file) and to turn on tracing only when a specific error code occurs. Error code 10 occurs when invalid credentials are used.

   Shell Copy

   hdbsqldbc_cons TRACE SIZE 100K
   hdbsqldbc_cons TRACE ONLY ON ERROR 10

3. Filtering can be used to reduce the size of trace files. Place the following SQL statements into a file named sql.sql to try this out.

   SQL Copy

   SELECT 'tracetestUSER1' FROM DUMMY; 
   CONNECT USER2 PASSWORD Password2;
   SELECT 'tracetestUSER2' FROM DUMMY;

   Execute the commands below to reset the trace settings, enable SQL trace for USER2, and then to run the above SQL statements which will execute as USER1 and then as USER2.

   Shell Copy

   hdbsqldbc_cons TRACE OFF
   hdbsqldbc_cons TRACE SQL ON LEVEL INFO
   hdbsqldbc_cons TRACE FILTER SQL USER USER2
   hdbsql -U User1UserKey -I sql.sql

   The expected result is that the resulting trace file only traces the query from USER2.

   While filtering by user is useful for isolating activity from a specific connection, it may still include multiple SQL statements. To further narrow the trace output, you can filter by a specific SQL statement using its hash value.

   To enable filtering by statement hash, you will need to obtain the MD5 value of the SQL statement. Update the sql.sql file with the following:

   SQL Copy

   SELECT 'tracetestUSER1' FROM DUMMY; 
   CONNECT USER2 PASSWORD Password2;
   SELECT 'tracetestUSER2' FROM DUMMY;
   SELECT * FROM HOTELS.CUSTOMER;

   To obtain the MD5 value, enter the following command:

   Shell Copy

   echo -n " SELECT * FROM HOTELS.CUSTOMER" | md5sum

   Ensure the SQL statement does not include a trailing semicolon (;) when calculating the MD5 hash.

   Execute the commands below to reset the trace settings, enable SQL to trace for the hash statement, and then to run the above SQL statements.

   Shell Copy

   hdbsqldbc_cons TRACE OFF
   hdbsqldbc_cons TRACE SQL ON LEVEL INFO
   hdbsqldbc_cons TRACE FILTER STATEMENT HASH "658d31bfb941b60f99decfbaf7f80b3f"
   hdbsql -U User1UserKey -I sql.sql

   The expected result is that the resulting trace file only traces the specific SQL statement.

   Filtering can be done on the following categories:

   • APPLICATION USER
   • CONNECTION ID
   • SQL USER
   • STATEMENT HASH
   • STATEMENT

   For more information, see hdbsqldbc_cons Trace Options.

4. In situations where hdbsqldbc_cons is not accessible, perhaps because a driver was installed directly using npm or pip, trace settings can be set using environment variables. The following values can be used in the trace file name.

   • %p represents the process ID
   • %a represents the application user
   • %c represents the connection ID
   Shell Copy

   set HDB_SQLDBC_TRACEFILE=c:\temp\traces\SQLDBC-%p.txt
   set HDB_SQLDBC_TRACEOPTS=SQL=INFO,FLUSH

   Shell Copy

   export HDB_SQLDBC_TRACEFILE=/tmp/traces/SQLDBC-%p.txt
   export HDB_SQLDBC_TRACEOPTS=SQL=INFO,FLUSH

   Trace settings in environment variables take precedence over those set using hdbsqldbc_cons.

   The environment variable values can be seen and removed with the following commands. The first and last commands display what the environment variable is set to. The middle command removes the value of the environment variable.

   Shell Copy

   set HDB_SQLDBC_TRACEFILE
   set HDB_SQLDBC_TRACEFILE=
   set HDB_SQLDBC_TRACEFILE
   
   set HDB_SQLDBC_TRACEOPTS
   set HDB_SQLDBC_TRACEOPTS=
   set HDB_SQLDBC_TRACEOPTS

   Shell Copy

   printenv | grep HDB_SQLDBC_TRACE
   unset HDB_SQLDBC_TRACEFILE
   unset HDB_SQLDBC_TRACEOPTS
   printenv | grep HDB_SQLDBC_TRACE

   

5. Trace information can be directed to stdout or stderr. See below for a few examples.

   Shell Copy

   hdbsql -U User1UserKey -Z traceFile=stdout -Z traceOptions=sql=warning "SELECT * FROM HOTELS.CUSTOMER"

   Shell Copy

   set HDB_SQLDBC_TRACEOPTS=SQL=WARN
   set HDB_SQLDBC_TRACEFILE=stdout
   hdbsql -U User1UserKey "SELECT * FROM HOTELS.CUSTOMER"
   set HDB_SQLDBC_TRACEOPTS=
   set HDB_SQLDBC_TRACEFILE=

6. Tracing can also be enabled in an application’s connection properties. For further details see traceFile and traceOptions in SQLDBC Connection Properties.

Java must be installed in order to complete the following steps. To check if it is installed, run the following commands:

java -version
javac -version

1. To show the current JDBC tracing configuration, run the following command:

   Shell Copy

   java -jar C:\SAP\hdbclient\ngdbc.jar SHOW

   Shell Copy

   java -jar ~/sap/hdbclient/ngdbc.jar SHOW

   Alternatively, the trace settings can be viewed and set using a GUI.

   Shell Copy

   java -jar C:\SAP\hdbclient\ngdbc.jar

   Shell Copy

   java -jar ~/sap/hdbclient/ngdbc.jar

   

2. Turn on the tracing for SQL statements using either the GUI or the commands below:

   Shell Copy

   java -jar C:\SAP\hdbclient\ngdbc.jar TRACE ON
   java -jar C:\SAP\hdbclient\ngdbc.jar TRACE FILENAME C:\temp\traces\jdbctrace
   java -jar C:\SAP\hdbclient\ngdbc.jar TRACE API ON

   Shell Copy

   java -jar ~/sap/hdbclient/ngdbc.jar TRACE ON
   java -jar ~/sap/hdbclient/ngdbc.jar TRACE FILENAME ~/tmp/traces/jdbctrace
   java -jar ~/sap/hdbclient/ngdbc.jar TRACE API ON

   The different command-line option keywords can be found at JDBC Tracing and Trace Options.

3. Execute a query which will generate a trace file.

   Shell Copy

   java -jar c:\sap\hdbclient\ngdbc.jar -k USER1UserKey -o encrypt=True -o validatecertificate=false -c "SELECT * FROM HOTELS.CUSTOMER"

   Shell Copy

   java -jar ~/sap/hdbclient/ngdbc.jar -k USER1UserKey -o encrypt=True -o validatecertificate=false -c "SELECT * FROM HOTELS.CUSTOMER"

4. Examine the trace file by using the cat command (Linux or Mac) or the type command on Windows.

   Code Copy

   <html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body><PRE><PLAINTEXT>
   Java version:   11
   ClassLoader:    jdk.internal.loader.ClassLoaders$AppClassLoader@30946e09
   Process ID:     47584
   Driver version: package com.sap.db.jdbc, JDBC, version 4.2, SAP HANA JDBC Driver, SAP SE, 2.16.9-62f3b9598d4106abb5cce5562c88d3abecca5580
   
   Trace configuration:
   Levels:                                       API,PACKET,CONNECTIONS,CLEANERS
   Show plain-text client-side encrypted values: Disabled
   Show timestamps:                              Disabled
   Show elapsed times:                           Disabled
   Trace file size:                              Unlimited
   Stop on error:                                Disabled
   
   ...
   
   com.sap.db.jdbc.HanaConnectionClean@731f8236[ID:202968].createStatement()
   => com.sap.db.jdbc.HanaStatement@117159c0
   com.sap.db.jdbc.HanaStatement@117159c0.execute("SELECT * FROM HOTELS.CUSTOMER")

5. Tracing information can be sent to stdout or stderr as shown below.

   Shell Copy

   java -jar c:\sap\hdbclient\ngdbc.jar -k User1UserKey -o traceFile=stdout -o traceOptions=CONNECTIONS -c "SELECT * FROM HOTELS.CUSTOMER"

   Shell Copy

   java -jar ~/sap/hdbclient/ngdbc.jar -k USER1UserKey -o traceFile=stdout -o traceOptions=CONNECTIONS -c "SELECT * FROM HOTELS.CUSTOMER"

6. Tracing can also be enabled via the connection parameters. For further details see Trace a JDBC Connection Using a Connection String.

Congratulations, you have now configured tracing with the SAP HANA client!