OData is a REST-based and standardized protocol that provides access to data over the Web. You can use the OData protocol to access data services from the Cloud, including internal data sources like those available with a JDBC connection.
OASIS has standardized on OData V4. Informatica recommends that you use version to use OData V4, and it is the default version.
Your organization can expose OData feeds on an endpoint such as:
For example, these two endpoints expose the sampleparts table:
•OData Version 2: https://[host].rt.informaticacloud.com/active-bpel/odata/v2/Parts/sampleparts
~or~
•OData Version 4: https://[host].rt.informaticacloud.com/active-bpel/odata/v4/Parts/sampleparts
Data is available in Atom XML format or JSON, including support for the XML content-type.
When the OData schema is generated, note the following:
•The IID fields are excluded.
•The key fields are added to the OData Key definition.
•The schema uses the same type as the native data type for each field.
You can enable OData in connections configured to run on Secure Agents. The Secure Agent, rather than opening a port, opens up an outbound connection to the Informatica Cloud servers through which all communication occurs. The Secure Agent then has access to any on-premises applications or data sources.
Note: If you publish an OData enabled connection to a Secure Agent, the OData URL on the Connection page is an Informatica Cloud URL.
You do not see a Secure Agent URL. To construct the Secure Agent URL, replace all text odata/v4/Oracle in the Informatica Cloud URL with https://<host>:<port>/process-engine/.
For example, if the Informatica Cloud URL is https://ps1w2.rt.informaticacloud.com/active-bpel/odata/v4/Oracle, the Secure Agent URL is https://localhost:7443/process-engine/odata/v4/Oracle.
Supported OData V4 and OData V2 URI Conventions
You can use many OData V4 and OData V2 URI conventions to access data.
Supported OData Version 4 Conventions
You can use the following OData V4 URI conventions to access data:
•Section 2. URL Components
•Section 3 Service Root URL
•Section 4.1 Addressing the Model for a Service
•Section 4.3 Addressing Entities
- You can use only Canonical URLs
•Section 4.4 Addressing References between Entities
•Section 4.6 Addressing a Property
•Section 4.7 Addressing a Property Value
•Section 5 Query Options
•Section 5.1 System Query Options
•Section 5.1.1 System Query Option $filter
•Section 5.1.1.1 Logical Operators
•Section 5.1.1.1.1 Equals
•Section 5.1.1.1.2 Not Equals
•Section 5.1.1.1.3 Greater Than
•Section 5.1.1.1.4 Greater Than or Equal
•Section 5.1.1.1.5 Less Than
•Section 5.1.1.1.6 Less Than or Equal
•Section 5.1.1.1.7 And
•Section 5.1.1.1.8 Or
•Section 5.1.1.4.1 contains
•Section 5.1.1.4.2 endswith
•Section 5.1.1.4.3 startswith
•Section 5.1.1.6 Literals
•Section 5.1.1.6.1 Primitive Literals
•Section 5.1.3 System Query Option $select
•Section 5.1.4 System Query Option $orderby
- You can use $orderby only with a JDBC connector.
•Section 5.1.5 System Query Options $top and $skip
- You can use $skip only with a JDBC connector.
•Section 5.1.6 System Query Option $count
•Section 5.1.7 System Query Option $search.
- You can do simple searches like http://host/service/Products?$search=blue
- You cannot use $search and $filter at the same time
•5.1.8 System Query Option $format
See here for the complete OData V4 specification for URI conventions.
Supported OData Version 2 Conventions
Informatica recommends that you use OData V4. However, you can use the following OData V2 URI conventions to access data:
•Section 1. URI Components
•Section 2. Service Root URI
•Section 3. Resource Path
•Section 3.1. Addressing Entries
- You cannot use relationships and complex entities
Section 3.3. Addressing Service Operations
Section 4. Query String Options
Section 4.1. System Query Options
Section 4.3. Top System Query Option ($top)
Section 4.5. Filter System Query Option ($filter)
- With the $filter option, you can use only 'endswith' and 'startswith'
Section 4.7. Format System Query Option ($format)
Section 4.8. Select System Query Option ($select)
Section 4.9. Inlinecount System Query Option ($inlinecount)
See here for the complete list of OData V2 specification for URI conventions.
Custom Composite Keys
When you use OData to access a JDBC connector, you can define and edit custom composite keys. You define a custom composite key when a database entity does not have a primary key. With this custom composite key that you create, you can perform read operations on the JDBC database.
To use custom composite keys, you must use OData V4.
Primary Keys and Custom Composite Keys
A primary key is an object field (a column in a database table or a database view) or a set of object fields (columns in a database) that can uniquely identify each record in a database table or database view. You use primary keys to query objects in the database.
If an entity does not have a primary key, you can define a custom composite key for the entity. To create a custom composite key, manually select a field or multiple fields for that key. The custom composite key takes the place of the primary key, and you can use it to read data from a JDBC database.
Example: You connect to a JDBC database of employee records and you see that the entity empaccount does not have a primary key. You can manually select the fields email, ID, and name to define a custom composite key for empaccount.
You can also edit custom composite keys that you created. If you defined a custom composite key that references an entity that no longer exists, you can remove the key. If a custom key contains fields that no longer exist, you can remove these fields.
Viewing the Custom OData Entity Keys Section
1 Enable OData and publish a JDBC connection.
2Click the Published Metadata tab.
You see a new section, Custom ODataEntity Keys, with the following information:
- Error messages, if any, with information about entities without a primary key and custom keys assigned to entities that no longer exist.
- A list of entities and their corresponding key fields.
3Click Edit Custom Keys.
The Custom OData Entity Keys dialog box opens.
The following image shows the Custom OData Entity Keys dialog box:
On the left side, you see three types of entities:
- Entities that do not have a primary key.
- Entities that do not exist but still have a custom key mapped to them.
- Entities that have a primary key defined, and do not require a custom composite key.
On the right side, you see a list of fields that you can map to create a custom composite key.
Creating OData Custom Composite Keys
To create OData custom composite keys, you select entities that you want to use as primary keys.
1Click Edit Custom Keys.
2Select an entity from the list of entities.
3Select the fields that you want to use as a custom key. You can select multiple fields.
Important: Ensure that you analyze the data and select fields that uniquely identify each record in the database table or view. For example, if you want to use the fields FirstName and LastName to create a custom composite key, you cannot have two employees with the same first and last names.
4Click Apply.
You see the custom composite key that you created in the list under Custom ODataEntity Keys.
Editing Custom Composite OData Keys
You can edit and delete custom composite OData keys.
Publish Validation
When you publish a connection, Informatica Process Designer checks the custom composite keys you created and displays a warning if it encounters any potential issues.
The following image shows a sample warning message:
