Creating a Schema for a Database Extract

Use the DB extract, when the data to be loaded on the staging table resides on a relational database.

• A schema for a database extract describes how and what data is extracted from the database. An extract may extract data from several tables.
• Each table is represented by a node on the tree on the left hand side, under the 'Response' element.
• The first element under the response element is the driving table and for each row retrieved from that table a record is written to the staging table.
• Each response node may contain child nodes allowing a hierarchical extraction process. For example, we may want to extract accounts and their linked persons. For this case, we have an Account node and a child node under Account.
• A special node on the left hand side is the 'Request' node. The Request node does not represent any particular table. It contains a list of fields that are used as selection criteria, mainly used to extract data from the main table.

Consider the following example: You have account information on a legacy system and you want to migrate that data into the system. The legacy system has an ACCOUNTS tables and an ACCOUNT_PERSON table. You want to migrate all ACCOUNTS in the legacy system, which have a given Customer Class. You want to be able to upload data each time with various Customer Class values. Therefore the first SQL statement retrieves data from the ACCOUNTS table and uses external criteria for the customer class. The second SQL statement retrieves data from the ACCOUNT_PERSONS table and uses the ACCOUNT_ID from the main record set.

To create a schema for a database extract, use the Import Database extract Wizard in the Schema Editor.

• Select Schemas/Database Extract from the menu. The Connection dialog appears.
• Indicate the database, which contains the tables required for the extract. This might be the same database as your product or a different one. Click Connect.
• You are prompted to indicate whether to create a new schema or to open an existing one. When you create a new schema, the Editor starts a Wizard that guides you through the schema definition process.
• First, you're prompted to enter the service name for this schema. This is the root element name of the schema.
• • In our example, we will use AccountsDBExtract
• Next, you are prompted to enter the list of request attributes to be used by the main SQL. Each attribute has a name, a data type and a maximum length. On the right side you can see the list of defined request attributes. You use the left side to add or modify attributes. After you have entered the request attributes, click OK. We'll define CustClass as an example.
• • Name: CustClass
  • Data Type: string
  • Max Length: 8
• Next you're prompted to enter the name of the root SQL element.
• • In our example, we will define Accounts
• The table selection list dialog appears. Select a table from the table list box. This table is used by the root SQL element.
• Once you select the table, the full screen appears. On the left side there is a tree representing the schema. On the right there is a tabbed dialog used to define the response elements. Each response node represents an SQL statement. Continuing with our example, on the Define Node tab, we enter the following:
• • Node Name: Accounts
  • Table Name: <connection name>.CI_ACCT
• Navigate to the Select tab to define the list of columns to be retrieved in this SQL. They are used to build the SELECT clause of the SQL statement. Each selected column is defined as an attribute of that response element in the schema. On the left you have the list of available columns. On the right you see the list of selected columns.
• Navigate to the Where tab to define the selection criteria for this SQL statement. Use the bottom frame to define criteria. Criteria are defined by a column, an operator and a value. The value may be a constant or a reference to an attribute in a parent element. In our example we'll use the request attribute CustClass as the value.
• • Criteria Column: CUST_CL_CD
  • Operator: =
  • Value: @AccountsDBExtract/Request/CustClass
• When you've completed the definition of a response element, click OK. This generates the SQL statement for that element and stores it on the schema.
• Now add another response element, called "AccountPersons". It is used to retrieve all persons linked to an account. Select the "Accounts" element on the left and click Add (located under the Tree View). Repeat steps described above for the new response element. Enter "AccountPersons" as the element name, select ACCT_PER as the table name and define the Select and Where clauses. This time use an attribute of the "Accounts" element to build the criteria for the "AccountPerson". This means that for each record returned by the root element, an SQL statement is generated to access the ACCT_PER table using the current ACCT_ID in Accounts.
• You have now completed the definition of the schema. Click on the toolbar "Test" button. When you test a DB extract schema, the editor prompts you for every attribute in the request element. It replaces every reference to a request attribute with the value you provide for the relevant SQL statement. In our example we only have one request attribute, the CustClass. Enter "I" for industrial customers.
• The response tab is now active and it shows the response document built as a result of executing the test. It should return all the elements of the Account table as defined in the response.

You can now save the schema by clicking Save.