Services Integration Framework Guide

10.3 HotFix 1
- 10.5
- 10.4 HotFix 3
- 10.4 HotFix 2
- 10.4 HotFix 1
- 10.4
- 10.3 HotFix 3
- 10.3 HotFix 2
- 10.3

Back Next

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);

Rename Saved Search

Table of Contents

Services Integration Framework Guide

Services Integration Framework Guide

SearchQuery

SearchQuery

Required Parameters

Optional Parameters

Retrieving Large Record Sets

Case Sensitivity

Use Case

Usage Example