|
This section provides descriptions of new features for SQL adapters.
More explanation has been added to messages that result from a rejected outer join request.
Using Extended Bulk Load with selected adapters requires that intermediate data files are transferred using [S]FTP to a host server. These adapters include:
In previous releases, this was done by adding a connection to the Flat File adapter and was limited to a single connection per server.
As of this release, a new Configure Bulk Load option is available, and allows you to configure an additional connection for the combination of server and adapter with a unique name. This option can be accessed by right-clicking a connection for a configured data adapter.
The Adapter for Amazon Athena is new in this release. The Amazon Athena query service allows you to access data stored in Amazon Simple Storage Service (Amazon S3) by using standard SQL.
The Adapter for Amazon Redshift supports IDENTITY data types as read-only with the property FIELDTYPE=R set in the Master File.
The Adapter for Apache Hive can now be used to read columns described with the TIME and TIMESTAMP data types, provided the storage plug-in used supports it.
On UNIX, Linux, and Windows you can specify the host name, port and database name instead of a DSN when configuring a connection to Db2 in the Web Console.
A Db2 trusted context enables the connected WebFOCUS user ID to be used for database interactions instead of the authenticated user ID for the connection. Use the following command to set the trusted context user ID in a WebFOCUS procedure.
ENGINE DB2 SET TRUSTED_USERID conname /tcuser,tcpass
where:
Is the connection name for the trusted context. This connection must be the active connection for the server when the SET TRUSTED_USERID command is issued.
Is the trusted context user ID.
Is the trusted context password.
While CLOB and BLOB columns cannot be used for Change Data Capture, synonyms can now be created for Table Log Records that contain them so that the other columns can be used. Attempts to retrieve CLOB and BLOB columns from Table Log Records return a MISSING value.
The Adapter for DB2 on IBM i with a Unicode server now supports a Change Data Capture (CDC/IUD) load flow when a target has columns with different Coded Character Set Identifiers (CCSIDs).
A JDBC adapter for access to Cache data sources is available in this release.
Extended Bulk Load to the EXASol system is now supported from Windows by using an auxiliary connection to a non-Windows "buffer host", which is also known as a FTP_node.
In previous releases, attempting to create a table in Jethro or Impala from a synonym using the Recreate DBMS Table (or CREATE FILE) option in the DMC with fields described as YYMD or Date would fail with a syntax error. This was due to the fact that a data type would not be specified.
As of this release, such fields are created as TIMESTAMP and an error does not occur.
Note: Neither Jethro or Impala have a DATE datatype.
The Adapter for Oracle now supports CLOB and BLOB Extended Bulk loading that provides improved performance using the Direct Path API.
Temporal queries against Oracle System-Versioned tables have been implemented by introducing fields with TEMPORAL_PROPERTY SYSTEMTIME. Such a field is added by editing the synonym for the table. For example:
DEFINE FIELD1/HYYMDm WITH REALFLD TEMPORAL_PROPERTY SYSTEMTIME = ;$
The following are requirements for the field with TEMPORAL_PROPERTY SYSTEMTIME.
Fields with TEMPORAL_PROPERTY=SYSTEMTIME can only be used in a single WHERE test of one of the following two types.
WHERE field EQ expression
This type of WHERE test translates to an ORACLE AS OF TIMESTAMP clause.
WHERE field FROM expression1 TO expression2
This type of WHERE test translates to a VERSIONS BETWEEN TIMESTAMP clause.
where:
Must be date-time expressions or constants. When used in a FROM-TO expression, the constants DT('0001-01-01') and DT('9999-12-31') represent the oldest and most recent applicable timestamps. They translate to the ORACLE keywords MINVALUE and MAXVALUE.
SQL Server 2017 is supported with the MSODBC version of the adapter.
The Windows ODBC version of the Adapter for SQL Server supports the SQL Server 2017/2016 Always Encrypted native feature under the following conditions:
When a synonym is created, the Access File will contain the attribute ENCRYPT_TYPE={DETERMINISTIC|RANDOMIZED} for the encrypted columns.
Certain operations on encrypted columns cannot be performed in SQL. The adapter logic will account for that, and those operations will be performed by the WebFOCUS Reporting Server.
Microsoft reversed its previous decision, and un-deprecated OLE DB technology on 3/31/2018, with the release of MSOLEDBSQL Driver version 18. Microsoft calls this release the third generation of OLE DB technology.
Accordingly, Information Builders removed obsolescent status from its OLE DB adapter, and introduced support of new MSOLEDBSQL Driver 18.
Customers may now choose between MS SQL Server OLE DB or ODBC adapters, depending on required features.
Note that first (SQLOLEDB) and second (SQLNCLI/SNAC) generations of Microsoft OLE DB Providers remain deprecated.
The Adapter for Microsoft SQL Server now supports CLOB and BLOB Extended Bulk loading that provides improved performance. To take advantage of this feature the version of Microsoft BCP utility should be 11.0 or higher.
The Adapter for Microsoft SQL Server Azure Data Warehouse is new in this release. It enables you to access data stored in the Microsoft parallel processing data warehouse architecture.
The Adapters for SQL Server (suffixes SQLMSS and MSODBC) now support read/write access to the Azure Database. The MSODBC version of the Adapter is recommended on platforms where the MS ODBC driver is available.
The CLI Adapter for Teradata now supports loading CLOB and BLOB data into fields of an existing target using Extended Bulk (MERGE INTO command transpires into LOB_DEFERRED_BY_NAME method).
Note:
The CLI Adapter for Teradata now supports reporting from Teradata stored procedures that return a dynamic result set.
As of this release, when the adapter for Teradata has the BULKLOAD setting set to ON, the Teradata load procedures FASTLOAD or MULTILOAD are used for best performance.
Previously, when creating a new table in Teradata from the Web Console using Custom Copy or the HOLD...FORMAT SQLDBC (FOCUS) command, a parameterized INSERT statement was generated, which was not the fastest option.
| WebFOCUS | |
|
Feedback |