Connection configuration

This topic describes how to view and configure the session variables supported by OceanBase Database and OceanBase Connector/J.

Configuration method

When you use OBLOADER & OBDUMPER to connect to a database, you can modify the connection configuration by configuring variables in the session.config.json file stored in the {ob-loader-dumper}/conf/ directory. session.config.json is a JSON configuration file readable to programs. It contains three types of configuration: SQL statements for connection initialization, URL options of OceanBase Connector/J, and high availability (HA) mode of OceanBase Connector/J. The following table describes the fixed tags in this JSON file.

You can view the session.config.json file in the {ob-loader-dumper}/conf directory. Here is a sample session.config.json file:

$cd /home/admin/obloaderobdumper/ob-loader-dumper-4.x.x-RELEASE/conf
$cat session.config.json

{
  "init_sql": {
    "oracle": [
      "set names utf8",
      "set autocommit=1",
      "set session ob_query_timeout=300000000",
      "set session ob_trx_timeout=180000000",
      "set session net_read_timeout=86400",
      "set session net_write_timeout=86400",
      "set session sql_select_limit=9223372036854775807",
      "alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'",
      "alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS:FF9'",
      "alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF TZR TZD'"
    ],
    "mysql": [
      "set names utf8",
      "set autocommit=1",
      "set session ob_query_timeout=300000000",
      "set session ob_trx_timeout=180000000",
      "set session net_read_timeout=86400",
      "set session net_write_timeout=86400",
      "set session sql_select_limit=9223372036854775807",
      "set session sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE'",
      "set session foreign_key_checks=0"
    ]
  },
  "jdbc_url_options": {
    "characterEncoding": "utf8",
    "socketTimeout": 1800000,
    "connectTimeout": 1800000,
    "zeroDateTimeBehavior": "convertToNull",
    "useServerPrepStmts": true,
    "noDatetimeStringSync": true,
    "useCompression": true,
    "log": false,
    "allowMultiQueries": true,
    "useLocalSessionState": true,
    "cachePrepStmts": true,
    "useSqlStringCache": true
  },
  "jdbc_hamode": null
}

Configuration file parameters

• The following table describes the parameters that are set by using the init_sql (initialization SQL statement) statement.

  Parameter	Description	Tenant type	Default value
  names	Specifies the character set.	Oracle/MySQL	UTF-8
  autocommit	Specifies whether to enable the auto-commit mode.	Oracle/MySQL	1
  ob_query_timeout	Specifies the query timeout period in milliseconds.	Oracle/MySQL	300000000
  ob_trx_timeout	Specifies the transaction timeout period in milliseconds.	Oracle/MySQL	180000000
  net_read_timeout	Specifies the network read timeout period in seconds.	Oracle/MySQL	86400
  net_write_timeout	Specifies the network write timeout period in seconds.	Oracle/MySQL	86400
  sql_select_limit	Specifies the maximum number of rows returned by an SQL query.	Oracle/MySQL	9223372036854775807
  nls_date_format	Specifies the date format for the current session.	Oracle	YYYY-MM-DD HH24:MI:SS
  nls_timestamp_format	Specifies the timestamp format for the current session.	Oracle	YYYY-MM-DD HH24:MI:SS:FF9
  nls_timestamp_tz_format	Specifies the time zone and timestamp format for the current session.	Oracle	YYYY-MM-DD HH24:MI:SS.FF TZR TZD
  foreign_key_checks	Disables foreign key checks when importing or exporting tables with foreign keys.	MySQL	0

• The following table describes the parameters of the jdbc_url_options (JDBC connection URL).

  Parameter	Description	Tenant type	Default value
  characterEncoding	Specifies the character encoding.	Oracle/MySQL	UTF-8
  socketTimeout	Specifies the socket timeout period in milliseconds.	Oracle/MySQL	180000000
  connectTimeout	Specifies the connection timeout period in milliseconds.	Oracle/MySQL	180000000
  zeroDateTimeBehavior	Specifies the behavior in handling zero datetime values.	Oracle/MySQL	convertToNull
  useServerPrepStmts	Specifies whether to use server prepared statements.	Oracle/MySQL	true
  noDatetimeStringSync	Specifies whether to disable synchronization of datetime strings.	Oracle/MySQL	true
  useCompression	Specifies whether to enable compression.	Oracle/MySQL	true
  log	Specifies whether to log.	Oracle/MySQL	false
  allowMultiQueries	Specifies whether to allow multiple queries to be executed simultaneously.	Oracle/MySQL	true
  useLocalSessionState	Specifies whether to use local session state.	Oracle/MySQL	true
  cachePrepStmts	Specifies whether to cache prepared statements.	Oracle/MySQL	true
  useSqlStringCache	Specifies whether to use an SQL string cache.	Oracle/MySQL	true
  extendOracleResultSetClass	Specifies whether to extend the Oracle result set class.	Oracle/MySQL	true
  useSSL	Specifies whether to use SSL.	Oracle/MySQL	true
  disableSslHostnameVerification	Specifies whether to disable SSL host name verification.	Oracle/MySQL	true
  trustStore	Specifies the path of the trust store file.	Oracle/MySQL	/truststore.jks
  trustStorePassword	Specifies the password of the trust store.	Oracle/MySQL	-
  keyStore	Specifies the path of the key store file.	Oracle/MySQL	-
  keyStorePassword	Specifies the password of the key store.	Oracle/MySQL	-
  jdbc_hamode	Specifies the JDBC HA mode.	Oracle/MySQL	-

• The following table describes the parameters of the direct_path_load (direct load) statement.

  Parameter	Description	Tenant type	Default value
  task_timeout_ms	Specifies the task timeout period in milliseconds.	Oracle/MySQL	2592000000
  heartbeat_timeout_ms	Specifies the heartbeat timeout period in milliseconds.	Oracle/MySQL	60000
  heartbeat_interval_ms	Specifies the heartbeat interval in milliseconds.	Oracle/MySQL	10000

References

• Command-line options

• Command-line options