Debugging SPL routines

You can use a Routine Debugger client application to identify and analyze logical errors in an SPL routine.

You can include the TRACE statement in your SPL routine to generate tracing output while the SPL routine runs. For information about how to produce and examine TRACE statement output, see the description of TRACE.

Current® restrictions for debugging SPL routines

The following software products currently support client environments that can be used for debugging HCL OneDB™ SPL routines:
  • (ODS)
  • (IDAIVS)

Refer to the data type support document for information on which HCL OneDB data types are read-only and which are updateable.

The following restrictions apply to both the (ODS) and (IDAIVS) debugging environments:
Unlogged databases

HCL OneDB nontransactional databases do not support SPL debugging. You cannot use either the ODS or the IDAIVS debugging environments with the HCL OneDB database whose CREATE DATABASE statement omits the WITH LOG keywords.

Secondary server instances

Secondary servers within a cluster environment do not support 'Step into' trigger procedure operation for Insert, Delete, or Update triggers.

Data strings delimited by double (") quotation marks

The current interpretation by the ODS or IDAIVS debugging client of quotation-mark delimiters might conflict with the expected behavior of some SPL routines.
  • Currently, in ODS debugging sessions the DELIMIDENT environment variable is set to 'y' by default in the .NET Core Provider Driver for JDBC and SQLJ connection string. The default value of DELIMIDENT is 'n' for JDBC connection.
  • Currently in IDAIVS debugging sessions, the DELIMIDENT environment variable is set to 'y' by default in the OneDB .NET Core Provider connection string.

The setting of the DELIMIDENT environment variable affects how the database server interprets quoted strings:

  • 'y' specifies that strings enclosed by double quotation ( " ) marks are delimited SQL identifiers. Client applications must use single quotation ( ' ) marks to delimit character strings, and must use double quote ( " ) symbols only around delimited SQL identifiers, which can support a larger character set than is valid in undelimited identifiers. In locales that support lettercase sensitivity, letters within delimited strings or within delimited identifiers are case-sensitive. This is the default value for .NET.
  • 'n' specifies that client applications can use double ( " ) or single ( ' ) quotation marks to delimit character strings, but not to delimit SQL identifiers. If the database server encounters a string that is delimited by double or single quotation marks in a context where an SQL identifier is required, it issues an error. An owner name that qualifies an SQL identifier can be delimited by single quotation ( ' ) marks. You must use a pair of the same quotation mark symbols to delimit a character string.
  • If no value for DELIMIDENT is specified on the client system, the default setting is used. As indicated above, for OCD this default is 'n' (from the .NET Core Provider Driver for JDBC and SQLJ connection string. For IDAIVS. this default is 'y' (from the OneDB .NET Core Provider connection string).

Restrictions that only affect ODS debugging sessions

By default, 'AUTOCOMMIT' transaction mode is set to 'TRUE' by the .NET Core Provider Driver for JDBC and SQLJ. This is enabled in ODS debugging session for all logging databases, including databases that were created WITH LOG MODE ANSI, and also databases that are not ANSI-compliant.

Currently, the HCL OneDB server performs an implicit commit operation after every SQL statement completes in an debugging session. If there is an explicit transaction started inside the HCL OneDB SPL procedure, the database server ignores SQL ERROR -535 and continues the debugging session.

Restrictions that only affect IDAIVS debugging sessions

Currently, SPL function debugging is not supported by . You can only use the IDAIVS debugging environment with HCL OneDB SPL procedures that do not return any value to the calling context.