Skip to Content

Trace an SAP HANA Client Connection

Enable trace settings for applications using SAP HANA Client interfaces.
You will learn
  • How to enable tracing using hdbsqldbc_cons or environment variables
  • How to record trace details to a file, stdout, or stderr
danielvaDan van LeeuwenJune 20, 2022
Created by
danielva
August 16, 2020
Contributors
danielva

Prerequisites

  • You have completed the first 3 tutorials in this mission.

Trace files can help SAP Support diagnose unexpected behavior.

Tracing can be configured using executables included with the SAP HANA Client installation. SQLDBC-based interfaces use hdbsqldbc_cons, except for ODBC, which uses hdbodbc_cons. For JDBC, use ngdbc.jar.

Trace settings can also be configured using environment variables.

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

      Shell
      Copy
      hdbsqldbc_cons SHOW ALL
      
      hdbsqldbc_cons SHOW ALL Results

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

    2. To specify the file location for the trace file and enable tracing of SQL statements, enter the following commands:

      Shell (Microsoft Windows)
      Copy
      hdbsqldbc_cons TRACE SQL ON LEVEL DEBUG
      mkdir c:\temp\traces
      hdbsqldbc_cons TRACE FILENAME c:\temp\traces\SQLDBC-%p.txt
      
      Shell (Linux or Mac)
      Copy
      hdbsqldbc_cons TRACE SQL ON LEVEL DEBUG
      mkdir /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

      Trace categories:

      • SQL
      • DEBUG
      • PACKET
      • DISTRIBUTION

      Trace levels:

      • DEBUG
      • INFO
      • WARNING
      • ERROR
      • FATAL

      For more information, see SQLDBC Tracing and Trace Options.

    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 (Microsoft Windows)
      Copy
      cd c:\temp\traces
      dir SQLDBC*.txt /od
      notepad SQLDBC-####.txt
      
      Shell (Linux or Mac)
      Copy
      cd /tmp/traces
      ls -lt
      pico SQLDBC-####.txt
      
      Replace Process ID

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

      libSQLDBCHDB 2.12.13.1642791468
      SYSTEM: Microsoft Windows / X64
      BUILD AT: 2022-01-28 19:09:11
      BRANCH: unknown
      BUILD MODE: rel
      APPLICATION: c:\SAP\hdbclient\hdbsql.exe
      HOST: W-R90XC65K
      OS USER: dan
      CURRENT DIRECTORY: c:\SAP\hdbclient
      TRACE FILE NAME: c:\temp\traces\SQLDBC-23380.txt
      PROCESS ID: 23380
      TRACE FILE WRAP COUNT: 0
      
      ---
      Enabled Traces:
      
      SQL Trace: Level DEBUG
      Distribution Trace: Level DEBUG
      
      ---
      

      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.

      ::EXECUTE WITH COMMIT SQLCURS_1 2020-12-28 07:11:09.717000 [0x000000000319c260]
      SQL COMMAND : SELECT VERSION FROM SYS.M_DATABASE
      ...
      ::EXECUTE WITH COMMIT SQLCURS_1 2020-12-28 07:11:09.729000 [0x000000000319c260]
      SQL COMMAND : SELECT B.HOST || ':' || SQL_PORT FROM PUBLIC.M_CONNECTIONS A JOIN SYS.M_SERVICES B   ON A.HOST = B.HOST and A.port = B.port   WHERE OWN = 'TRUE'
      
    6. To turn off tracing for all categories, run the following command:

      Shell
      Copy
      hdbsqldbc_cons TRACE OFF
      
  • Step 2

    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 have a performance, memory and disk size impact. 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. 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.

      Shell (Windows)
      Copy
      set HDB_SQLDBC_TRACEFILE=c:\temp\traces\SQLDBC-%p.txt
      set HDB_SQLDBC_TRACEOPTS=SQL=DEBUG,FLUSH
      
      Shell (Linux or Mac)
      Copy
      export HDB_SQLDBC_TRACEFILE=/tmp/traces/SQLDBC-%p.txt
      export HDB_SQLDBC_TRACEOPTS=SQL=DEBUG,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 (Windows)
      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 (Linux or Mac)
      Copy
      printenv | grep HDB_SQLDBC_TRACE
      unset HDB_SQLDBC_TRACEFILE
      unset HDB_SQLDBC_TRACEOPTS
      printenv | grep HDB_SQLDBC_TRACE
      
      Environment Variable Values
    4. 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 HOTEL.CUSTOMER"
      
      Shell
      Copy
      set HDB_SQLDBC_TRACEOPTS=SQL=WARN
      set HDB_SQLDBC_TRACEFILE=stdout
      hdbsql -U User1UserKey "SELECT * FROM HOTEL.CUSTOMER"
      set HDB_SQLDBC_TRACEOPTS=
      set HDB_SQLDBC_TRACEFILE=
      
    5. Tracing can also be enabled in an applications connection properties. For further details see traceFile and traceOptions in SQLDBC Connection Properties.

  • Step 3

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

    Shell
    Copy
    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
      

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

      Shell
      Copy
      java -jar c:\sap\hdbclient\ngdbc.jar
      
      JDBC Driver GUI
    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
      

      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 HOTEL.CUSTOMER"
      
    4. Examine the trace file.

      <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:     7984
      Driver version: package com.sap.db.jdbc, Java Platform API Specification, version 1.8, SAP HANA JDBC Driver, SAP SE, 2.7.9-e7d7d91fad7f2973ba232a4bff888b86d102c42b
      
      Trace configuration:
        Levels:                                       API
        Show plain-text client-side encrypted values: Disabled
        Show timestamps:                              Disabled
        Show elapsed times:                           Disabled
        Trace file size:                              Unlimited
        Stop on error:                                Disabled
      
      ---- Thread 1060b431 main
      com.sap.db.jdbc.Driver@2ac273d3.connect("jdbc:sap://localhost:30015", {encrypt=True, key=***, validatecertificate=false})
      => com.sap.db.jdbc.HanaConnectionClean@78a2da20[ID:203274]
      com.sap.db.jdbc.HanaConnectionClean@78a2da20[ID:203274].createStatement()
      => com.sap.db.jdbc.HanaStatement@47d9a273
      com.sap.db.jdbc.HanaStatement@47d9a273.execute("SELECT * FROM HOTEL.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 HOTEL.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!

    Which of the following statements are true?

Back to top