[Feature Request] Distribution settings hardcoded to OFF?

[kermitbravo]

We are trying to enable statement routing on prepared statements using the golang HANA db client but there's no obvious way to set the Connection Option Distribution to ALL or STATEMENT.

SAP Documentation

https://help.sap.com/docs/SAP_HANA_PLATFORM/0eec0d68141541d1b07893a39944924e/8d61ae225ae44b0bab2fb2285009f68d.html

STATEMENT ROUTING

https://launchpad.support.sap.com/#/notes/2079372

Default option for CoClientDistributionMode set to OFF.

https://github.com/SAP/go-hdb/blob/main/driver/connection.go#L1297

[stfnmllr]

The client documentation you are referring to is not applicable to this driver implementation. The features provided by this client are described solely in
https://github.com/SAP/go-hdb/blob/main/README.md and
https://pkg.go.dev/github.com/SAP/go-hdb/driver
Anyway, statement routing like described in the attached note is not supported by this driver. Currently there is also no option to provide more than one HANA DB endpoint. Consequently the parameter "CoClientDistributionMode" is defaulted to "off". This driver is not based on the clients mentioned (SAP HANA Client e.g., SQLDBC or JDBC) but a Go native database/sql API compliant implementation which also does not support routing statements to different endpoints.
In case you want to follow up on this requirement please provide some more details about your use case.

[kermitbravo]

We want to leverage statement routing because we have a scale-out HANA system with several nodes and we have partitioned tables, where each partition is spread across each one of these nodes evenly with some metadata tables being replicated across all nodes. We are already executing prepared queries which is one of the prerequisites to being able to do statement routing. We would like to make our queries more efficient by executing in the node where the data resides instead of having HANA perform networking operations to move data across nodes.

[stfnmllr]

Thanks a lot for the additional information!
As of now I cannot confirm if this feature is going to be provided in one of the next versions of go-hdb but it is definitely not going to be available in the short term.
As an alternative you might double check on the 'official' go driver
https://help.sap.com/docs/SAP_HANA_PLATFORM/0eec0d68141541d1b07893a39944924e/0ffbe86c9d9f44338441829c6bee15e6.html?version=2.0.03&locale=en-US
As it is based on the SQLDBC lib it should support the statement routing capabilities.

[kermitbravo]

Hi Stefan,

Thanks for the response.

I might have the wrong understanding of this feature but I believe that the HANA DB does all the work, the only thing that needs to be done from the client side is to pass a value on the DISTRIBUTION parameter. Do you see this as a major change that needs to be included in version 2 (date, TBD) or could this be implemented sooner?

[stfnmllr]

Hello @kermitbravo,

I might have the wrong understanding of this feature but I believe that the HANA DB does all the work, the only thing that needs to be done from the client side is to pass a value on the DISTRIBUTION parameter.

If so, I guess we would be able to do the implementation earlier.

But according to the information you did share above

A prepared statement is a (typically parameterized) SQL statement that is sent to the server where it is precompiled. The prepared statement is returned to the client. For execution the client sends the prepared statement (usually filled with the actual parameters) to the server where (most of) the data is located by opening a new connection.

This two-step protocol (prepare and execute) is used for statement routing in a distributed system, where the “prepare” request may return information about the preferred locations for executing the statement. Based on this information, the client library sends requests for executing the prepared statement directly to one of the indicated index servers. The response for an “execute” request may also contain location information, if the situation has changed since the “prepare” request was processed. Statement routing is done by the client library and is transparent to the application. With statement routing, different statements of the same transaction may be executed on different servers.

the client library is in charge to select the right endpoint after a prepare would indicate which index server to be used. Currently go-hdb does have a 1:1 relationship of a go sql connection / db backend connection and the control of which connection is used for a statement is solely with go sql connection pooling logic (exceptions are transactions or the usage of dedicated connections via the sql Conn object).
So to me there is definitely the need for some investigation and concept work to get this right.

[kermitbravo]

I agree that to enable statement routing there might be additional work here but to at least enable ClientDistributionMode Connection (round-robin, which is better than nothing) the change would be less involved?

[stfnmllr]

Sorry for the late answer!
Yes, "connection" should be possible but unfortunately it wouldn't have any effect if the server configuration is "statement" (the default value), so the server configuration needs to be changed as well (to "all") to support a "mixed" client usage.
If this is ok I am going to provide the "connection" option in addition to "off".
Best regards

[kermitbravo]

That would be great, thanks Stefan.