Description
A relational data store type describes the properties ArcGIS Server needs to establish (and maintain) a connection to an instance of database for a given database platform (e.g. Microsoft SQL Server, SAP HANA, Teradata).
A relational data store type differs from a data item. A data item refers to a specific container of data (e.g. a database instance, a file share, etc.) that has been registered with the server, and is used while publishing and serving out GIS services. A relational data store does not refer to a specific container of data. It simply describes a generic set of connection properties for a particular database platform.
Client applications (such as Insights for ArcGIS) ask the server for the properties of a relational data store type for the purpose of establishing a Relational Database Connection portal item. The process of creating a Relational Database Connection portal item creates a Relational Catalog Service on the ArcGIS Server site that was designated as a portal's hosting server.
Before a relational data store type can be used by client applications, Java Database Connectivity (JDBC) drivers must first be uploaded to a hosting server as a ZIP file, and then registered. Only administrators can register a relational data store type.
JSON Structure
A relational data store type is described in the server as a JSON object. Only under exceptional circumstances will you ever need to edit the properties of a given relational data store type. The basic structure is as follows:
{
"adminDefinedProperties":
{
"jdbcPoolProperties": {},
"excludeSchemas": []
},
"userDefinedProperties": [],
"dialectClass": "", //Esri-internal. Do not modify.
"dialectFactoryClass": "" //Esri-internal. Do not modify.
}
The adminDefinedProperties section is a JSON object that has two properties: jdbcPoolProperties and excludeSchemas. When a client application (such as Insights for ArcGIS) asks the server for its list of relational data store types (or for the properties of a specific relational data store type), the adminDefinedProperties are not returned (they are only visible during an edit operation). The jdbcPoolProperties describe the attributes of the JDBC connection pool managed by ArcGIS Server. The excludeSchemas is a comma-separated array of strings that identify database schemas where information about tables/views should not be displayed to client applications (regardless whether an authenticated user has been granted permissions to view those entities by the database administrator or not).
The userDefinedProperties section is an array of JSON objects. Each JSON object describes a connection parameter.
userDefinedProperties: [
{
"name": "", //Attribute name as specified by JDBC connection pool or platform-specific JDBC driver
"alias": "", //User-friendly description of "name"
"defaultValue": <value>, //Value matches the "type" specified below. Optional.
"type": "string" | "int" | "boolean",
"required": true | false,
"encrypt": true | false
}, ...
]
The "encrypt" item is optional. The default value is false. However, the password is always encrypted.
Note:
The dialectClass and dialectFactoryClass properties are internal implementation details. They should never be changed.
Tip:
Always perform any changes in a development or staging environment and test thoroughly before deploying to production. When diagnosing issues with a particular relational data store type, Esri Support might ask you to revert to the relational data store type to its default set of properties in order to reproduce the issue with those settings.
Tip:
Before making any edits to a relational data store type, save a backup copy type’s JSON.
Tip:
Always validate JSON before applying any edits. There are many online tools available to do this.
Tip:
After editing a relational data store type, you can test whether the edits are valid by using Create Service to create an instance of a relational catalog service that points to the edited relational data store type. Successfully listing the new service’s entities or fields will verify whether ArcGIS Server was able to establish a connection to the database.
Caution:
Certain edits to a relational data store type can render pre-existing relational catalog services inoperable. It is advised that you only edit a relational data store type if there are no relational catalog services that depend on it. You can verify this by examining the dependentServices property returned by the relational data store type. An empty array indicates no dependent services. For example:
https://server:port/arcgis/admin/data/relationalDatastoreTypes/esri.sqlserver?f=json
Example—Microsoft SQL Server
{
"name":"Microsoft SQL Server",
"id":"esri.sqlserver",
"adminDefinedProperties":{
"jdbcPoolProperties":{
"factory":"org.apache.tomcat.jdbc.pool.DataSourceFactory",
"driverClassName":"com.microsoft.sqlserver.jdbc.SQLServerDriver",
"url":"jdbc:sqlserver://",
"initialSize":0,
"minIdle":0,
"timeBetweenEvictionRunsMillis":10000,
"minEvictableIdleTimeMillis":20000,
"testOnBorrow":true,
"validationQuery":"SELECT 1",
"connectionProperties":"sendTimeAsDatetime=false"
},
"excludeSchemas":[
"sys",
"INFORMATION_SCHEMA"
]
},
"userDefinedProperties":[
{
"name":"username",
"alias":"User Name",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"password",
"alias":"Password",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"serverName",
"alias":"Server Name",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"databaseName",
"alias":"Database Name",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"instanceName",
"alias":"Instance Name",
"defaultValue":"",
"type":"string",
"required":false
},
{
"name":"portNumber",
"alias":"Port Number",
"defaultValue":"",
"type":"int",
"required":false
}
],
"dialectClass":"com.esri.ads2.dialect.impl.sqlserver.SQLServerSupportImpl",
"dialectFactoryClass":"com.esri.ads2.factory.PooledDataSourceDialectFactory"
}
Example—SAP Hana
{
"name":"SAP HANA",
"id":"esri.hana",
"adminDefinedProperties":{
"jdbcPoolProperties":{
"urlSeparator":"/?",
"factory":"org.apache.tomcat.jdbc.pool.DataSourceFactory",
"driverClassName":"com.sap.db.jdbc.Driver",
"url":"jdbc:sap://",
"initialSize":0,
"minIdle":0,
"timeBetweenEvictionRunsMillis":10000,
"minEvictableIdleTimeMillis":20000,
"testOnBorrow":true,
"validationQuery":"SELECT 1 FROM DUMMY"
},
"excludeSchemas":[
"SYS",
"_SYS_REPO",
"_SYS_TASK",
"SYS_XS_UAA"
]
},
"userDefinedProperties":[
{
"name":"username",
"alias":"User Name",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"password",
"alias":"Password",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"serverName",
"alias":"Server Name",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"databaseName",
"alias":"Database Name",
"defaultValue":"",
"type":"string",
"required":false
},
{
"name":"portNumber",
"alias":"Port Number",
"defaultValue":"",
"type":"int",
"required":true
}
],
"dialectClass":"com.esri.ads2.dialect.impl.hana.SAPHANASupportImpl",
"dialectFactoryClass":"com.esri.ads2.factory.PooledDataSourceDialectFactory"
}
Example—Teradata 10.5
{
"name":"Teradata",
"id":"esri.teradata",
"adminDefinedProperties":{
"jdbcPoolProperties":{
"factory":"org.apache.tomcat.jdbc.pool.DataSourceFactory",
"driverClassName":"com.teradata.jdbc.TeraDriver",
"url":"jdbc:teradata://",
"urlSeparator":"/",
"initialSize":0,
"minIdle":0,
"timeBetweenEvictionRunsMillis":10000,
"minEvictableIdleTimeMillis":20000,
"testOnBorrow":true,
"validationQuery":"SELECT 1"
},
"excludeSchemas":[
]
},
"userDefinedProperties":[
{
"name":"username",
"alias":"User Name",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"password",
"alias":"Password",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"serverName",
"alias":"Server Name",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"DATABASE",
"alias":"Database Name",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"DBS_PORT",
"alias":"Port Number",
"defaultValue":"",
"type":"int",
"required":false
}
],
"dialectClass":"com.esri.ads2.dialect.impl.teradata.TeradataSupportImpl",
"dialectFactoryClass":"com.esri.ads2.factory.PooledDataSourceDialectFactory"
}
Example—Teradata 10.5.1 and later versions
{
"name":"Teradata",
"id":"esri.teradata",
"adminDefinedProperties":{
"jdbcPoolProperties":{
"factory":"org.apache.tomcat.jdbc.pool.DataSourceFactory",
"driverClassName":"com.teradata.jdbc.TeraDriver",
"url":"jdbc:teradata://",
"urlSeparator":"/",
"initialSize":0,
"minIdle":0,
"timeBetweenEvictionRunsMillis":10000,
"minEvictableIdleTimeMillis":20000,
"testOnBorrow":true,
"validationQuery":"SELECT 1"
},
"excludeSchemas":[
]
},
"userDefinedProperties":[
{
"name":"username",
"alias":"User Name",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"password",
"alias":"Password",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"serverName",
"alias":"Server Name",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"DATABASE",
"alias":"Database Name",
"defaultValue":"",
"type":"string",
"required":true
},
{
"name":"DBS_PORT",
"alias":"Port Number",
"defaultValue":"",
"type":"int",
"required":false
},
{
"name":"CHARSET",
"alias":"Charset",
"defaultValue":"UTF8",
"type":"string",
"required":false
}
],
"dialectClass":"com.esri.ads2.dialect.impl.teradata.TeradataSupportImpl",
"dialectFactoryClass":"com.esri.ads2.factory.PooledDataSourceTeradataFactory"
}