July 30, 2023

tMDMSP – Docs for ESB 7.x

  2. Talend for ESB Docs
  3. Docs 7.x
  4. tMDMSP – Docs for ESB 7.x

tMDMSP

Offers a convenient way to centralize multiple or complex queries in an MDM Hub
and calls the stored procedure easily.

tMDMSP Standard properties

These properties are used to configure tMDMSP running in the Standard Job framework.

The Standard
tMDMSP component belongs to the Talend MDM family.

The component in this framework is available in all Talend
products.

Basic
settings

Schema and
Edit Schema

In SP principle, the schema is an input
parameter.

A schema is a row description, it defines
the number of fields to be processed and passed on
to the next component.

The schema of this component is
read-only.
Use an existing
connection 		Select this check box if you
want to use a configured tMDMConnection component.

MDM
version

By default, Server
6.0 is selected. Although it is recommended to migrate existing Jobs for
this new version, the Server 5.6 option is available
to ease the process of the migration of your Jobs so as to keep them working without
modification with a 6.0 server. To do so, an option on the server must be enabled to
accept and translate requests from such Jobs.

URL

Type in the URL of the MDM server.

Username and
Password

Type in the user authentication data for the
MDM server.

To enter the password, click the […] button next to the
password field, and then in the pop-up dialog box enter the password between double quotes
and click OK to save the settings.

Data Container

Type in the name of the data container that
stores the procedure you want to call.

Type

Select Master or Staging to specify the database on
which the action should be performed.

Procedure Name

Type in the exact name of the Stored
Procedure.

Parameters (in
order)

Click the Plus button and select the various
Input Columns
that will be required by the procedures.

Note:

The SP schema can hold more columns than
there are parameters used in the procedure.

Advanced
settings

tStatCatcher
Statistics

Select this check box to gather the
processing metadata at the Job level as well as at
each component level.

Global
Variables

Global
Variables

ERROR_MESSAGE: the error message generated by the
component when an error occurs. This is an After variable and it returns a string. This
variable functions only if the Die on error check box is
cleared, if the component has this check box.

A Flow variable functions during the execution of a component while an After variable
functions after the execution of the component.

To fill up a field or expression with a variable, press Ctrl +
Space to access the variable list and choose the variable to use from it.

For further information about variables, see
Talend Studio

User Guide.

Usage

Usage rule

This component is used as intermediary component. It can be
used as start component but only no input parameters are thus needed for the
procedure to be called. An output link is required.

You can increase the timeout values for a Job using this component
to help process a large number of data records. For more information, see advanced
execution settings for JVM parameters in the article Timeout values for a Job using
MDM components on Talend Help Center (https://help.talend.com).

Connections

Outgoing links (from this component to
another):

Row: Main

Trigger: Run if;
On Component Ok; On Component Error, On Subjob Ok,
On Subjob Error.

Incoming links (from one component to this
one):

Row: Main,
Iterate;

Trigger: Run if,
On Component Ok, On Component Error, On Subjob Ok,
On Subjob Error

For further information regarding
connections, see
Talend Studio User Guide.

Executing a stored procedure using tMDMSP

This scenario applies only to Talend MDM Platform and Talend Data Fabric.

In this scenario, the Job first generates parameters and sends them to tMDMSP, which executes a predefined stored procedure,
and then extracts data from the returned execution result and presents the extracted
data in the console.

tMDMSP_1.png
Prerequisites:

  • Make sure the MDM server is up and running.

  • You have imported the MDM demo project and loaded the sample data into the data container
    Product by running the Job MDM_LoadAll.

  • You have been assigned a role with the appropriate user authorization and access rights to
    the data model Product.

  • You have created a store procedure called ProductSelection and
    deployed this stored procedure to the MDM server.

In this example, the stored procedure ProductSelection is designed to query two fields
Name and Price of
Product data records within a price range:

tMDMSP_2.png

For more information on working with stored procedures, see


Talend Studio
 User Guide.

Creating a Job to execute the stored procedure

  1. Drag and drop the following components onto the design workspace:
    tFixedFlowInput, tMDMSP, tExtractXMLField, and tLogRow.
  2. Link the components using the Row >
    Main     connections.

Configuring tFixedFlowInput to generate a price range

  1. Double-click tFixedFlowInput to open
    its Basic settings view.

    tMDMSP_3.png

  2. Click the […] button next to
    Edit schema to open the
    schema editor.
  3. Click the [+] button to add two
    parameters min and max
    that are used to define the price range.

    tMDMSP_4.png

  4. Click OK to close the schema
    editor.
  5. In the Mode area, keep the default
    option Use Single Table, and enter
    10 between quotes for the
    min parameter and
    15 between quotes for the
    max parameter.

Configuring tMDMSP to execute the stored procedure

  1. Double-click tMDMSP to open its
    Basic settings view.

    tMDMSP_5.png

  2. Enter the user name and password for accessing the MDM server.
  3. In the Data Container field, enter
    Product between quotes.
  4. In the Procedure Name field, enter
    ProductSelection between quotes.
  5. In the Parameters table, click the
    [+] button to add two rows,
    and select min and max
    respectively.

Configuring tExtractXMLField to extract data from the returned execution
result of the stored procedure

  1. Double-click tExtractXMLField to open
    its Basic settings view.

    tMDMSP_6.png

  2. Click the […] button next to
    Edit schema to open the
    schema editor.
  3. Add two columns to define the structure of the output data:
    name and price.

    tMDMSP_7.png

  4. Click OK to close the schema editor,
    and then click Yes in the Propagate dialog box.
  5. In the Loop XPath query field, enter
    result between quotes on which the loop
    is based.

    Each result of the stored procedure has the following syntax:
    <result><col0>val1</col1>...<colN>valn</colN></result>
    (where colN is the nth column in the
    SELECT clause).
    Warning:

    If a null value exists in a data record to be returned by the
    execution result of the stored procedure, some
    inconsistencies will occur between the column number and the
    value for this column in the returned data record.

  6. In XPath query column of the
    Mapping table, enter the
    exact node name on which the loop is applied:
    col0 and col1,
    respectively, between quotes.

Configuring the data display mode and executing the Job

  1. Double-click the tLogRow component to
    display its Basic settings
    view.
  2. In the Mode area, select Table (print values in cells of a
    table)     for better readability of the
    result.

    tMDMSP_8.png

  3. Save the Job and press F6 to run
    it.

    According to the stored procedure and the price range, the
    Product data records within the price
    range of 10 to 15 are displayed with the values of the
    Name and Price
    fields as expected.
    tMDMSP_9.png

Document get from Talend https://help.talend.com
Thank you for watching.
Docs 7.x
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x