Table of Contents

Search

  1. Preface
  2. Introduction to Services Integration Framework
  3. Setting Up the SIF SDK
  4. Request and Response Objects
  5. Transactions and Exception Handling
  6. ORS-Specific SIF API
  7. Asynchronous SIF Requests
  8. ORS-Specific JMS Event Messages
  9. Using Security Access Manager
  10. Using Dynamic Data Masking
  11. SIF API Reference
  12. Troubleshooting
  13. Appendix A: Identifiers
  14. Appendix B: Frequently Asked Questions

Services Integration Framework Guide

Services Integration Framework Guide

SearchQuery

SearchQuery

SearchQuery searches for records in a package, base object, or remote package based on an SQL condition clause. The condition clause can reference any column in the package, base object, or remote package and can use operators supported by the target database.
If you perform a SearchQuery in which you use a ROWID_OBJECT value for a base object record that is merged into another base object record, no records are returned. For example, two base object records, one with a ROWID_OBJECT value of ROWID_A and the other with a value of ROWID_B are merged. After the merge, the ROWID_OBJECT value of the surviving base object could be ROWID_A. In this case, if you perform a SearchQuery in which you use a ROWID_OBJECT of ROWID_B, no records are returned because a base object with a ROWID_OBJECT of ROWID_B no longer exists in the base object table.
On IBM Db2, you cannot use functions such as CAST in the sortCriteria field of the SearchQuery API. The select statement of the SearchQuery API uses the ORDER BY clause that you cannot combined with functions such as CAST.
On IBM Db2, you cannot use the
lower
function in the filterCriteria field of the SearchQuery API. IBM Db2 does not support the
lower
function on the graphic physical data type that corresponds to the NCHAR and NVARCHAR data type of the MDM Hub.
When you specify the effectiveDate parameter along with the filterCriteria parameter in SearchQuery, ensure that the columns that you specify in the filterCriteria parameter are qualified.
Informatica recommends that you use the filterCriteria parameter together with the filterParameter when possible so that the database can reuse the cached query for better performance.
For example, if you have a filterCriteria parameter of
lower(name)=lower
, the following sample shows how to combine the filterParameter in SearchQuery:
<urn:filterParameter> <urn:stringValue>Peter</urn:stringValue> </urn:filterParameter>
Ensure that you list the parameters with the appropriate data types according to the database.
The following sample shows that the C_PARTY object qualifies the FIRST_NAME column:
<urn:orsId>orcl.informatica.com-MDM_SAMPLE</urn:orsId> <urn:siperianObjectUid>BASE_OBJECT.C_PARTY</urn:siperianObjectUid> <urn:filterCriteria>
C_PARTY.FIRST_NAME = ?
</urn:filterCriteria> <urn:filterParameter> <urn:stringValue>John</urn:stringValue> </urn:filterParameter> <urn:effectiveDate>
2014-03-17T20:06:00.000
</urn:effectiveDate>

Required Parameters

The following table lists and describes the parameters that the SearchQuery API requires:
Parameter
Description
SiperianObjectUid
Name and type of package, base object, XREF table, XREF history table, history table, or merge history table that you need to query.
RecordsToReturn
Sets the limit to the number of records that the SaerchQuery API must retrieve.
FilterCriteria
SQL clause to filter search results for the columns of the package that is queried.
Use the FilterCriteria parameter to specify the literal expressions (FIRST_NAME = 'JOHN') or use it in combination with FilterParameter.
FilterParameter
Specifies the parameter values to filter as a list.

Optional Parameters

The following table describes the optional SearchQuery API parameters:
Parameter
Description
EffectiveDate
The date for which you must retrieve values of the base object.
Use EffectiveDate only for timeline-enabled base objects.
HistoryDate
The date for which you retrieve base object data that is effective at the specified point in time. If HistoryDate is equal to or earlier than HIST_CREATE_DATE, SearchQuery does not return any records.
For Oracle environments, SearchQuery truncates milliseconds for HistoryDate.
In the following situations, SearchQuery does not return any records:
  • HistoryDate is later than HIST_CREATE_DATE by less than a second
  • Daylight Saving Time (DST) is enabled for the operating system and HistoryDate is later than HIST_CREATE_DATE by less than one hour during DST
Use HistoryDate only for timeline-enabled base objects.
DisablePaging
If set to
true
, it disables the paging mechanism and you can retrieve multiple pages of data. Set the parameter set to
true
for queries that return a predictable number of rows. Use
GetSearchResults
to fetch subsequent pages of search results.
RecordStates
Specifies the Hub state indicator to use for filtering the search result.
JoinUids
Specifies a list of UIDs to join with SiperiaObjectUID.
RemoveDuplicates
If set to
true
, SearchQuery removes duplicates from the result set. Enable the parameter when there is a possibility of duplicates in the result set. The default is
false
.
AdvancedMode
If set to
true
, the advanced mode of search query processing is enabled. The default is
false
. When AdvancedMode is
true
, you can use FilterCriteria with the advanced operators EXISTS and COUNT. However, when AdvancedMode is
true
, you cannot use these operators in sortCriteria.
UncommittedRead
If set to
true
, SearchQuery returns results without waiting for pending updates to commit. If set to
false
, SearchQuery does not return results until all pending data changes are committed. The default is
false
.

Retrieving Large Record Sets

To control multiple records that the query must return and set the data page size for paging, you need the SearchRequestBase. The SearchRequestBase is the base class for search requests with parameters for paging and sorting.

Case Sensitivity

The SearchQuery API is case sensitive. Use the same case as in the Operational Reference Store when you specify a filter criteria. If the case is different from the one in the Operational Reference Store, records are not found. However, to control the case sensitivity of SearchQuery, you can use the CASE_INDICATOR column in C_REPOS_TABLE. The values for the case indicator are UPPER, LOWER, and NULL. Specify a case indicator to indicate that all data in the corresponding table is in the case that you specify. The case indicator setting converts the filter criteria to the appropriate case. To implement searches that are not case sensitive, you do not require queries based on functions.
The following table describes the settings for the CASE_INDICATOR column:
Name
Description
UPPER
Converts the WHERE clause and any parameter of the query to uppercase before you run the request.
All the data in the package in the request must be in uppercase.
LOWER
Converts the WHERE clause and any parameter of the query to lowercase before you run the request.
All the data in the package in the request must be in lowercase.
NULL
Query does not require case conversions before you run the request.
The case of data in the package that is in the request does not matter.

Use Case

The scenario where you search for records in a package uses the SearchQuery request. In a custom UI, use
SearchQuery
to allow a data steward to find a particular record.

Usage Example

The following example shows the search for a record from the package PARTY_ADDRESS_READ_PKG, where PARTY_FULL_NAME starts with 'WAGNER':
SearchQueryRequest request = new SearchQueryRequest(); request.setRecordsToReturn(5); request.setSiperianObjectUid("PACKAGE.PARTY_ADDRESS_READ_PKG"); request.setFilterCriteria("PARTY_FULL_NAME LIKE ?"); ArrayList params = new ArrayList(2); params.add(new Parameter("WAGNER%")); request.setFilterParameter(params); SearchQueryResponse response = (SearchQueryResponse) sipClient.process(request);

0 COMMENTS

We’d like to hear from you!