Knowledgebase:
Understanding the advanced options for the MarkLogic Windows ODBC driver
24 March 2023 11:24 AM

Introduction

This article aims to provide a basic understanding of the advanced options for the MarkLogic Windows ODBC driver.

Description of advanced options

The main advanced options are:

Use Declare/Fetch

One should turn on Use declare/Fetch option to use memory more efficiently.
If set to true, the driver automatically uses declare cursor/fetch to handle SELECT statements. This is a great advantage, especially if you are only interested in reading and not updating. Option results in the driver buffering a certain number of rows at a time, making it more memory efficient.
If set to false, cursors will not be used, and the driver will retrieve the entire result set. This is inefficient for large tables and may use all Windows memory/resources.

Cache Size Cache size determines how many rows to fetch at a time.

When using cursors, this is the row size of the tuple cache (default is 100 rows). This option is only meaningful when using cursors (use Declare/Fetch must be on).
It should be tested on the customer environment to find a value that balances performance and memory usage.
One may start with 1024 cache size as starting point; However one should find optimal value based on optimal performance and memory usage in respective environment.

Data Type Options

Option affects how some data types are mapped:

Text and Unknowns are mapped as LongVarChar.
Bools as Char: Bools are mapped to SQL_CHAR, otherwise to SQL_BIT. Option should be turned on unless one need to map bools to SQL_BIT.

ReadOnly

Check box to make data source read-only. 

Show System Tables

Check box to access system tables in BI tool or Microsoft Access.

Updatable Cursors

Check to enable updateable cursor emulation in the driver. Recommended to keep uncheck.

True is -1

Represent TRUE as -1 for compatibility with some applications. Recommended to keep uncheck.

Int8 As

Define what datatype to report int8 columns as. Keep it numeric by default.
        

Level of rollback on errors

Specifies what to rollback should an error occur.

  • Nop(0): Don't roll back anything; let the application handle the error.
  • Transaction(1): Roll back the entire transaction. Keep this on.
  • Statement(2): Roll back the statement.
Connect Settings

The driver sends these commands to the backend upon a successful connection. It sends these settings AFTER it sends the driver "Connect Settings". Use a semi-colon (;) to separate commands. This can now handle any query, even if it returns results. However, the results will be thrown away. Recommended to keep blank.

The 'Global setting' dialog box

This dialog allows one to specify pre-connection/default logging options. The logging settings have impact on performance and should only be turned on during debugging.

CommLog (C:\psqlodbc_xxxx.log - Communications log) Logs communications to/from the backend. Recommended for debugging problems.
MyLog (C:\mylog_xxxx.log - Detailed debug output) Logs debug messages. Recommended for debugging problems with the driver.
MSDTCLog (C:\pgdtclog\mylog_xxxx.log - MSDTC debug output) Logs MSDTC debug messages. Recommended for debugging problems with the MSDTC.
Specification of the holder for log outputs Adjustment of write permission.

Manage DSN Dialog Box

This dialog allows one to select ODBC driver to use with the connection. Note that this may not work with third-party drivers, one should use MarkLogicSQL driver.






(0 vote(s))
Helpful
Not helpful

Comments (0)