Collect Native Platform Call Data

Wiki (en)
, , , , ,
1

Invantive SQL real-time transports massive amounts of data contained between over 70 (cloud) platforms and user applications such as Excel, Word, Power BI, technical products such as Query Tool and server products such as Data Replicator, Data Access Point or Bridge Online.

This article helps you understand how to collect the data being transported between the Invantive SQL engine and the (cloud) platforms. This knowledge is essential when it is unclear what component(s) are causing a data integrity problems such as missing data, duplicate data or incorrect data.

Applicable Releases

Log native calls to trace has been available since release 17.32. Log native calls to disk is available starting release 20.1.44 and 20.2.0. Differentiated logging of native calls on error and success and limited in duration and number of events logged is available starting 20.2.42.

Instructions

All Invantive SQL connectors have two settings available to log the requests and responses upon these communicated by Invantive SQL to the platform:

  • log-native-calls-to-trace: send request and response data to the Windows trace, which can be read using tools such as DbgView or collected on disk using Invantive’s trace configuration options.
  • log-native-calls-to-disk: send request and response data to disk as plain text files.

These connector attribute names have been available since release 20.1.44 BETA. Log-native-calls-to-trace can also be set using the trace-native-calls option on release 17.32 and later.

Execute the following steps to run a number of queries on a database and collect the actual requests and responses:

  • Start Query Tool.
  • Log on to a database with one or multiple data containers.
  • Enable log collection by setting either or both log-native-calls-to-trace and log-native-calls-to-disk to true.
  • Start dbgview if logging to trace.
  • Run a query.
  • Collect the contents of dbgview or the disk folder and send them to the party requesting them such as the API support of the platform.

Example

In the following sample, a distributed database with Oracle with Invantive Vision, Exact Online and ActiveCampaign is opened and a number of requests are collected.

The following code is used:

--
-- Choose multiple Exact Online companies.
--
use all@eol

--
-- Enable logging to disk in the default 
-- folder %USERPROFILE%\invantive\nativelog.
-- Change the folder using the environment 
-- variable INVANTIVE_CONFIGURATION_NATIVE_LOG_FOLDER.
--
set log-native-calls-to-disk@eol true

set log-native-calls-to-disk@ac true

set log-native-calls-to-disk@ora true

--
-- Run an Oracle query, an Exact Online query
-- and an ActiveCampaign query.
--
select * from bubs_gebruikers_v@ora limit 10

select * from exactonlinerest..journals@eol

select * from campaigns@ac limit 10

Visually:

image

The contents of the folder %USERPROFILE%\Invantive\NativeLog are the last statement completes is:

image

The file names are composed of the prefix ‘native’, a time stamp when the connector instance was opened, the data container ID, the optional alias, the name of the connector and a sequence number incrementing per connector instance.

Oracle

The Oracle request log looks like this:

Outbound Execute statement on database started at 2020-05-24 11:58:09 (UTC): 
select * from QBUBS.BUBS_GEBRUIKERS_V e

and includes the direction of the request (outbound), a short description and a timestamp, together with the SQL statement sent in Oracle SQL syntax.

Exact Online

The Exact Online files include both a number of non-visible requests on ReportingBalance to collect statistics on the data volume of the company and the a requests for the journals on every selected company:

Inbound https://start.exactonline.nl/api/v1/39304/financial/Journals?$select=* GET started at 2020-05-24 11:58:15 (UTC): 
{
"d" : {
"results": [
{
"__metadata": {
"uri": "https://start.exactonline.nl/api/v1/39304/financial/Journals(guid'cea3d391-c460-4a71-82b4-6c91a68a4afb')"
, "type": "Exact.Web.Api.Models.Journal"
}
, "AllowVariableCurrency": false
, "AllowVariableExchangeRate": false
, "AllowVAT": false
, "AutoSave": false
, "Bank": null
, "BankAccountBICCode": null
...
}, ...

ActiveCampaign

The ActiveCampaign file contains a request on the instance:

Inbound https://acme.api-us1.com/api/3/campaigns?limit=100 GET started at 2020-05-24 11:58:31 (UTC): 
{"campaigns":[{"type":"single"
,"userid":"1"
,"segmentid":"0"
,"bounceid":"-1"
,"realcid":"0"
...

Differentiated Logging

Starting release 20.2.42, there are a number of new options available to reduce the amount of native calls collected:

  • log-native-calls-to-disk-on-error: Registers native calls to data container backend as disk files when an error occurred. Defaults to false.
  • log-native-calls-to-disk-on-success: Registers native calls to data container backend as disk files when successful. Defaults to false.
  • log-native-calls-to-disk-max-events: Maximum number of events to register from last activation. Defaults to unlimited.
  • log-native-calls-to-disk-max-seconds: Maximum number of seconds to register from last activation. Defaults to unlimited.

Huge Volumes

Starting release 20.2.72, new facilities ease querying millions of calls. The storage format has been changed to use NDJSON instead of plain text.

Please refer to Analyzing large volumes of native call logs on cloud platform APIs: