Contents x
      Model and Translator Properties
      • 24 Nov 2024
      • 6 Minutes to read
      • Print
      • Dark
        Light
      • PDF
      Contents

      Model and Translator Properties

      • Updated on 24 Nov 2024
      • 6 Minutes to read
      • Print
      • Dark
        Light
      • PDF
      Article summary

      Setting up the correct configurations is essential for smooth and secure data integration. Understanding the right properties and parameters can significantly optimize data handling, streamline querying, and ensure secure connections.

      This article explores the model and translator properties available for JDBC data sources and provides an overview of the translator properties.
      Following is information about Model and Translator properties and the associated parameters for:

      Available Data Model Properties for JDBC

      ParameterDescriptionDefault
      catalogSee DatabaseMetaData.getTables [1]null
      schemaPatternSee DatabaseMetaData.getTables [1]null
      tableNamePatternSee DatabaseMetaData.getTables [1]null
      procedureNamePatternSee DatabaseMetaData.getProcedures [1]null
      tableTypesComma separated list - without spaces - of imported table types. See DatabaseMetaData.getTables [1]null
      excludeTablesA case-insensitive regular expression that when matched against a fully qualified table name [2] will exclude it from import. Applied after table names are retrieved. Use a negative look-ahead (?!<inclusion pattern>).* to act as an inclusion filter.Null
      excludeProceduresA case-insensitive regular expression that when matched against a fully qualified procedure name [2] will exclude it from import. Applied after procedure names are retrieved. Use a negative look-ahead (?!<inclusion pattern>).* to act as an inclusion filter.null
      useFullSchemaNameWhen false, directs the importer to drop the source catalog/schema from the object name, so that the fully qualified name will be in the form of <model name>.<table name>.
      Note: When false this may lead to objects with duplicate names when importing from multiple schemas.      		true
      importKeystrue to import primary and foreign keys.
      Note: Foreign keys to tables that are not imported will be ignored.      		true
      autoCreateUniqueConstraintstrue to create a unique constraint if one is not found for a foreign key.true
      importIndexestrue to import index/unique key/cardinality informationfalse
      importApproximateIndexestrue to import approximate index information.
      See DatabaseMetaData.getIndexInfo [1]      		true
      importProcedurestrue to import procedures and procedure columns.
      Note: It is not always possible to import procedure result set columns due to database limitations. It is also not currently possible to import overloaded procedures.      		false
      widenUnsignedTypestrue to convert unsigned types to the next widest type. For example, SQL Server reports tinyint as an unsigned type. With this option enabled, tinyint would be imported as a short instead of a byte.true
      quoteNameInSourcefalse will override the default and direct the service to create source queries using unquoted identifiers.true
      useProcedureSpecificNametrue will allow the import of overloaded procedures (which will normally result in a duplicate procedure error) by using the unique procedure-specific name as the service name. This option will only work with JDBC 4.0 compatible drivers that report specific names.false
      useCatalogNametrue will use any non-null/non-empty catalog name as part of the name in source, e.g., "catalog"."schema"."table"."column", and in the service runtime name if useFullSchemaName is also true.
      false will not use the catalog name in either the name in source or the service runtime name. Should be set to false for sources that do not fully support a catalog concept, but return a non-null catalog name in their metadata, such as HSQL.      		true
      useQualifiedNametrue will use name qualification for both the service name and name in source as dictated by the useCatalogName and useFullSchemaName properties.
      Set to false to disable all qualification for both the service name and the name in source, which effectively ignores the useCatalogName and useFullSchemaName properties.
      Note: When false this may lead to objects with duplicate names when importing from multiple schemas, which results in an exception.      		true
      useAnyIndexCardinalitytrue will use the maximum cardinality returned from DatabaseMetaData.getIndexInfo. importKeys or importIndexes needs to be enabled for this setting to have an effect. This allows for better stats gathering from sources that don’t support returning a statistical index.false
      useQualifiedNametrue will use name qualification for both the service name and name in source as dictated by the useCatalogName and useFullSchemaName properties. Set to false to disable all qualification for both the service name and the name in source, which effectively ignores the useCatalogName and useFullSchemaName properties.

      Note: When false this may lead to objects with duplicate names when importing from multiple schemas, which results in an exception.      		true
      useAnyIndexCardinalitytrue will use the maximum cardinality returned from DatabaseMetaData.getIndexInfo. importKeys or importIndexes needs to be enabled for this setting to have an effect.
      This allows for better stats gathering from sources that don’t support returning a statistical index.      		false
      importStatisticstrue will use database dependent logic to determine the cardinality if none is determined. This is not yet supported by all database types – currently only supported by Oracle and MySQL.false
      importRowIdAsBinarytrue will import RowId columns as varbinary values.false

      [1] JavaDoc for DatabaseMetaData

      [2] The fully qualified name for exclusion is based on the settings of the translator and the specifics of the database. All applicable name parts used by the translator settings (e.g., useQualifiedName and useCatalogName) — including catalog, schema, and table — will be combined as catalogName.schemaName.tableName without quoting. For example, Oracle does not report a catalog, so with default settings, the name used for comparison would be schemaName.tableName.

      Available Translator Properties for JDBC

      ParameterDescriptionDefault
      DatabaseTimeZoneThe time zone of the database. Used when fetching date, time, or timestamp values.The system default time zone
      DatabaseVersionThe specific database version. Used to further tune pushdown support.

      The base supported version or the version derived from the DatabaseMetadata.getDatabaseProductVersion string. Automatic detection requires a Connection.

      If there are circumstances where you are getting an exception from capabilities being unavailable (most likely due to an issue obtaining a Connection), then set DatabaseVersion property.

      Use the JDBCExecutionFactory.usesDatabaseVersion() method to control whether your translator requires a connection to determine capabilities.
      TrimStringsTrue to trim trailing whitespace from fixed length character strings. Note that the service only has a string, or varchar, type that treats trailing whitespace as meaningful.false
      UseBindVariablesTrue indicates that PreparedStatements should be used and that literal values in the source query should be replaced with bind variables. If false, only LOB values will trigger the use of PreparedStatements.true
      UseCommentsInSourceQueryThis will embed a leading comment with session/request ID in the source SQL for informational purposes. Can be customized with the CommentFormat property.false
      CommentFormatMessageFormat string to be used if UseCommentsInSourceQuery is enabled.

      Available properties:
      - 0 - session ID string
      - 1 - parent request ID string
      - 2 - request part ID string
      - 3 - execution count ID string
      - 4 - user name string
      - 5 - VDB name string
      - 6 - VDB version integer
      - 7 - is transactional Boolean
      MaxPreparedInsertBatchSizeThe max size of a prepared insert batch.2048
      StructRetrievalStruct retrieval mode can be one of:

      - OBJECT - getObject value returned
      - COPY - returned as a SerialStruct
      - ARRAY - returned as an Array      		OBJECT
      EnableDependentJoinsFor sources that support temporary tables (DB2, Derby, H2, HSQL 2.0+, MySQL 5.0+, Oracle, PostgreSQL, SQLServer, Sybase), allow dependent join pushdownfalse

      Available Translator Properties for Microsoft Active Directory (LDAP)

      ParameterDescriptionDefault
      SearchDefaultBaseDNDefault Base DN for LDAP SearchesNull
      SearchDefaultScopeDefault Scope for LDAP Searches. Can be one of SUBTREE_SCOPE, OBJECT_SCOPE, ONELEVEL_SCOPE.ONELEVEL_SCOPE
      RestrictToObjectClassRestrict Searches to objectClass named in the Name field for a tableFalse
      UsePaginationUse a PagedResultsControl to page through large results. This is not supported by all directory servers.False
      ExceptionOnSizeLimitExceededSet to true to throw an exception when a SizeLimitExceededException is received and a LIMIT is not properly enforced.False
      Was this article helpful?
      What's Next