Extension Tables

• Data Import — Import all person data from multiple systems by way of custom database tables.

  Caution: Do not use special characters such as angle brackets (< or >) in data in cross-reference tables, data maps, and input files. These characters can make the integration fail.

• Data Export — The destination system requires that changes to previously exported records be accompanied by an external record ID that is stored in a custom database table by the original export.
• Data Lookup — Reference data that is not supported by the Integration Designer (the Boomi™ application), such as dynamic loads of data by APIs. These can go by way of custom database tables.

Integrations use a Connector shape to exchange data with the extensions database.

 Connector: Get data into the integration or send data out from the integration.

Connectors enable data communication with and the movement of data between the applications or data sources that are the "end points" of an integration process. The applications and data sources can be an on-premise or web-based application, a data repository such as an SFTP directory, a database, or an email server.

Unlike an API which acts like a socket that waits until something is plugged in, a connector acts like a plug into an API to get the technical details and communication protocols.

A connector combines 2 components:

• Connection is the "where", that is, the type of data source.
• Operation is the "how", that is, how to physically connect to the source and the type of data records to exchange.

Every process begins with a Start shape which gets data, may have one or more Connector shapes in the middle to get data or values from a reference table, and typically ends with one or more Connector shapes that send data to multiple destinations or as notifications.

1. Click Tap Main Menu  > Administration > Application Setup > Access Profiles > Function Access Profiles. 
2. Select the profile. Click Tap Edit. 
3. Select Manager—System Configuration.
4. Select Integrations Setup:
   1. In Connections > Access Scope, select Allowed to configure extension tables.

   2. In Extension Database:

      In Add, select Allowed to enter database commands.

      In View, select Allowed to see the detailed view of database commands.

5. Click Tap Save & Return.

Associate access profiles to people

In People Information, associate the access profiles to managers or administrators. If you don't have access to People Information, contact the administrator who does have access.

1. Click Tap Main  > Maintenance > People Information. Select a person.
2. In Employee, select Access Profiles.
3. Select the Function Access Profile.
4. Click Tap Save .  
5. Repeat for other people.

You can create Data Definition Language (DDL) commands only for the schema of your tenant.

1. Click Tap Main Menu  > Administration > Application Setup > System Configuration > Extension Tables. 
2. Click Tap Create .
3. Enter a unique DDL Command Name.
4. (Optional) Enter a Description.
5. In Commands for Execution, enter DDL commands, each separated by a semicolon (;).

   Only the following commands are supported:

   • CREATE, ALTER, or DROP table
   • CREATE, ALTER, or DROP index
   • TRUNCATE table
   • REINDEX table or index
   • ANALYZE

   Example DDL commands

   CREATE TABLE PayrollExtractTest

   (payrollExtractID BIGINT NOT NULL,

   payrollExtractName TEXT NOT NULL,

   runDate DATE,

   CONSTRAINT pk_PayrollExtractTest PRIMARY KEY (payrollExtractID));

   CREATE INDEX i1_payrollExtractName on PayrollExtractTest(payrollExtractName);

6. Click Tap Execute. 
7. If the execution remains in-progress, click tap Refresh .  

1. Click Tap Main Menu  > Administration > Application Setup > System Configuration > Extension Tables. 
2. Select a set of DDL commands.
3. Click Tap View .  
4. If the run completed, the Commands Result List shows the result of each statement.
5. Configure the Extension DB Connection in the Integration Designer.
   1. Select Design Integrations > Design Integration Templates.
   2. Configure the JDBC Connector with the DML credentials and connection details.

An integration can run only Data Manipulation Language (DML) commands.

Note: The following steps are automated for refreshed or cloned tenants, and are required only for new tenants.

1. Click Tap Main Menu  > Administration > Application Setup > System Configuration > Extension Tables. 
2. Select a set of DDL commands.
3. Click Tap View DML Credentials .
4. Copy the DML credentials and connection details.
5. Configure the Extension DB Connection in the Integration Designer:
   1. Select Design Integrations > Design Integration Templates.
   2. Configure the JDBC Connector with the DML credentials and connection details.

You can show detailed information for current extension tables, such as columns, primary and foreign keys, and indexes.

1. Click Tap Main Menu  > Administration > Application Setup > System Configuration > Extension Tables.
2. Select an extension table.
3. Click Tap View Extension Tables .

You can change the number of connections for the extension database.

1. Log into the tenant management system (TMS).
2. Click Tap Main Menu  > Administration > Application Setup > System Configuration > System Settings.
3. Select the Global Values tab.
4. In global.ihub.integrationdb.connection.limit, enter the maximum number of connections for the extension database.
   • Default = 10 connections.

     Caution: Change this value only for large organizations. The default of 10 connections is recommended for most organizations.

   • Minimum = 10.
   • Maximum for DDL = 10.
   • Maximum for DML = 500.
5. Click Tap Save.