Oracle data point is used to configure connectivity to Oracle database. For each instance of Oracle database, a separate data point will have to be created. Multiple databases within the same Oracle instance can be added in single Oracle data point. The Oracle data point will be associated to any Oracle data object created and data flows defining Oracle as the native processing platform.

To work with Oracle Data Point, follow below steps:

Step I: Create a New Data Point

Step II: Provide connection details

To connect to Oracle database following details need to be provided in the Properties tab.

  • From the Database Type dropdown list, select the type of database. You can either select Oracle on-premise or Oracle Autonomous Cloud database type. By default Oracle on-premise database type is selected.

1. Connection details for Oracle on-premise

    • Host: Specify the hostname or the IP address of the Oracle system
    • Port: Specify the port associated to the Oracle system
    • App User: Specify the user id that will be used to connect to the Oracle system. The user id specified should have necessary privileges to access the data in the database.
    • Password: Specify the password for the App User. To use the project parameter for the password, check the Use Project Parameters for Password option, and you can view and select the required Project Parameter from the Password drop-down. 
    • Service Name: Service name is the TNS alias that you give when you remotely connect to your database and this Service name is recorded in Tnsnames.ora file on your clients and it can be the same as SID and you can also give it any other name you want
    • Security Authentication: Specifies the type of security protocol to connect to the instance and you can either select SSL or None.
    • Keystore: Specify KeyStore path which is which is placed in the agent location and this field is available for SSL authentication
    • Keystore Password: Specify password for Keystore which is created while generating Keystore file and this field is available for SSL authentication. To use the project parameter for the Keystore password, check the Use Project Parameters option, and you can view and select the required Project Parameter from the Keystore Password drop-down. 
    • Truststore: Specify TrustStore path which is placed in the agent location and this field is available for SSL authentication.
    • Truststore Password: Specify password for TrustStore which is created while generating TrustStore file and this field is available for SSL authentication. To use the project parameter for the Truststore password, check the Use Project Parameters option, and you can view and select the required Project Parameter from the Truststore Password drop-down.
    • Protocol: Specify the connectivity protocol and this field is available for SSL authentication.
    • Cipher Suites: Specify the subset of cipher suites and this field is available for SSL authentication.
    • Version: Specify the ssl version and this field is available for SSL authentication.
    • Jdbc Options: Specify the options that should be used along with JDBC URL to connect to ORACLE. 
      For example, following details are provided in JDBC Options to connect to ORACLE: user=diyotta, password=****, db=TEST_DB.
    • Database Version: Specify the Oracle version being connected to.

2. Connection details for Oracle Autonomous Cloud: You must select the Credential Storage type, it can be either Oracle Wallet or Java Key Store and as per the selection provide following details:

    • User Name: Specify the user id that will be used to connect to the Oracle system. The user id specified should have necessary privileges to access the data in the database.
    • Password: Specify the password for the App User. To use the project parameter for the password, check the Use Project Parameters for Password option, and you can view and select the required Project Parameter from the Password drop-down. 
    • TNS alias: TNS alias is given when you remotely connect to your database and this Service name is recorded in Tnsnames.ora file on your clients and it can be the same as SID and you can also give it any other name you want.
    • Oracle Wallet Location: Specify the location where oracle wallet file which is generated along the oracle cloud setup, is placed in the agent location. 
    • TrustStore Location: Specify trust store location which will generate by server and this field is available for Java Key Store connection
    • TrustStore Password: Specify password for trust store which is created while generating trust store file and this field is available for Java Key Store connection. To use the project parameter for the TrustStore password, check the Use Project Parameters option, and you can view and select the required Project Parameter from the TrustStore Password drop-down. 
    • Keystore Location: Specify key store path which will generate by client and this field is available for Java Key Store connection
    • Keystore Password: Specify password for keystore which is created while generating key store file and this field is available for Java Key Store connection. To use the project parameter for the Keystore password, check the Use Project Parameters option, and you can view and select the required Project Parameter from the Keystore Password drop-down. 
    • SSL Version:  Specifies the SSL version and this field is available for Java Key Store connection.
    • JDBC Options: Specify the options that should be used along with JDBC URL to connect to ORACLE. 
      For example, following details are provided in JDBC Options to connect to ORACLE: user=diyotta, password=****, db=TEST_DB.

Note:
  • Mandatory field names are suffixed with *. To establish the connection, provide all the mandatory property field values.
  • All the fields in the Properties tab can be parameterized using project parameters. To parameterize the fields, refer Working with Project Parameters.

3. Assign Agent: To assign or change the associated agent click Change. The Change Agent window appears and displays the list of available Agents. From the list, select the required Agent Name.

Note: To search for a specific agent, enter the keyword in the search bar, and the window displays the search result list. Select the required agent and click Ok.
  • If Default agent is assigned to the Project then automatically, the Default agent will be associated with the new Data point created.
  • If Default agent is not assigned to the Project then, no agent will be assigned automatically and appropriate agent needs to be assigned to the data point.
  • When connecting to the Agent server then, the agent installation user should have appropriate privilege to access the path where file will be placed.
  • When connecting to the remote server then, the firewall needs to be opened from the Agent server to it and user specified to connect should have appropriate privilege to access the path where file will be placed.

Step III: Test the data point connection

  • To validate that the data point is able to connect to the Oracle data point database using the details provided, refer Test Data Point Connection. 

Step IV: Enter the Schema details

Manage the required schemas to be accessed through the Schemas tab. Multiple schemas can be added here.

Note: To work with the application its mandatory to have at least one schema connection, by default the Default schema is associated with the new Data point created.

1. To add a new schema, click Add(+).

New entry for schema is generated and success message is displayed at the bottom right corner of the screen.

  • The "Name" field is a friendly name that can be assigned to the schema for easy identification. This friendly name is displayed when a schema needs to be chosen from the data point and when the schema association with other components is displayed.
  • Provide the physical name of the schema in the "Schema" field. When clicked on the entry in the "Schema" field a drop-down appears with the list of Oracle databases in the system. As you enter the schema keyword, the drop-down shows the specific schemas. The schema name can either be selected from this drop-down list or you can manually enter.
  • To assign a schema to be used for creating temporary tables as part of processing the data (generally referred to as tform schema), select the checkbox under Transforms field.

  • The Transforms field is available only for those type of data points which are supported by Diyotta as data processing platform.
  • It is mandatory to assign a schema as transform in the data point when, that data point needs to be assigned during the data flow creation and used as the processing platform.
  • To view the schema drop-down, it is a prerequisite to test the connection. For more information, refer Test Data Point Connection.
  • To search for a specific schema, enter the keyword in the search bar, and the page displays the related schemas.

Note:

  • Following operations are allowed on the schema entries: Add, Cut, Copy, Paste, Up, Down, Delete, and Search.
  • From the list of schemas, multiple schemas can be selected and we can perform/apply these operations.

Step V: Save the data point

Note:

Step VI: Modify the configured Extract and Load properties 

When moving data from one system to another the data is extracted from the source system, moved over the network and loaded into the target system. The SQLs and commands generated during execution of the jobs to extract and load data are generated based on the properties defined for these. The properties associated with the extraction and load of data should depend on the format, performance and variety of the data being moved. These properties vary based on the environment and the type of the system. Diyotta comes with default properties that covers most of the known scenarios.

To modify these properties, refer Editing Extract Properties in Oracle Data Point and Editing Load Properties in Oracle Data Point.

  • The default values for extract and load properties can be configured in the Admin module and these properties reflect in the Studio module.
  • The extract and load properties set in data point are by default used in the source and target instance of the data flow and the job flows.
  • It is a good practice to set the extract and load properties as per the company standards in the data point.
  • However, if needed any specific property can be overridden in the data flow or job flow.