Different data store types support different levels of indexing and searching. Indexing increases the efficiency of searches in tables with many records. Querying to find particular values can be expensive when the search must span many columns and many records. To improve performance, you can restrict searches to particular text-based columns using the Hybrid Data Pipeline proprietary query parameter, ddSearch. To search across all columns in the schema, even those not enabled in the schema map for searching, you can use OData $filter. But, you cannot combine ddSearch and $filter in the same request.
This release supports use of ddSearch for all data store types, and full-text search taking advantages of indexes in the following data source types:
DB2 on Linux, UNIX, and Windows — Each column to be searched must have a separate full text index, the full text services must be running, and the database must be enabled for full text. See the DB2 documentation for more information.
Oracle — Each column to be searched must have a separate full text index, the full text services must be running, and the database must be enabled for full text. See the Oracle documentation for more information.
Microsoft SQL Server — Each column to be searched must have a separate full text index and the full text index engine must be running. See the Microsoft documentation for more information.
To use text search:
For data stores that support full-text search, make sure that the underlying data store is indexed and is up to date with the current schema.
Use the ddsearch parameter with a search string, as described below.
Hybrid Data Pipeline treats multiple terms by using a logical and. For example, a search for Sales & Marketing returns records that contain both the word Sales and the word Marketing, the ampersand is ignored. The case-sensitivity of the search string depends on the underlying data source.
The ddsearch parameter will either return an empty response or an error in the following circumstances:
If the schema map does not specify the table as searchable.
If the table does not contain searchable fields.
If searching is not enabled in the backend data store.
For Salesforce, if you have not enabled use of ddSearch as a custom query parameter.
The following example returns a list of records containing the string "TX" from an ACCOUNT table: https://<myserver>:<port>/api/odata/DDCdemo/ACCOUNTS?ddsearch=TX
where <myserver> is the DSN name or the IP address of the machine where Hybrid Data Pipeline is installed.
DB2 on Linux, UNIX, and Windows — Each column to be searched must have a separate full text index, the full text services must be running, and the database must be enabled for full text. See the DB2 documentation for more information.
