Why? MDM developers and administrators sometimes need to create and run automated processes that execute a mix of queries in an Operational Reference Store (ORS) database and SIF API calls that invoke some MDM operations. Programs to make SIF API calls (building and sending requests and receiving responses) can be developed using different technologies and protocols. Informatica MDM documentation provides samples of programming SIF API calls using Java, and that is what some MDM developers use when they need to write such a program. They develop Java classes which, with the help of database drivers and Informatica Java libraries, connect to databases to perform data manipulations, and to MDM Hub servers to make SIF API calls. Those Java classes are then usually packaged as JAR files and placed on a file system, where they are executed by external programs — most often shell scripts or batch files. Those files, in turn, can be executed by scheduling applications or on demand. Now, programming in Java requires certain skills and experience which not many MDM Hub developers or administrators possess. They are usually more comfortable dealing with just their databases, writing SQL queries and simple PL/SQL programs. It would be great if such a task could be achieved using just database programs.

Another use case that can be viewed as a mix of SQL database operations and making SIF API call is deployment of changes to ORS repositories. In a mature organization, MDM developers develop changes and then database and MDM administrators deploy them to higher environments like QA or production). In a typical deployment, a database administrator may have to execute an SQL script provided by developers, then an MDM administrator would modify ORS repository configuration by applying a change file in the MDM Hub Console, then possibly the DBA would execute another SQL script, and MDM administrator would run some MDM jobs in the MDM Hub Console, etc. This requires coordination and cooperation between administrators. Again, it would be helpful if such a task could be achieved with just one database SQL script, and thus the deployment can possibly be fully automated.

The above scenarios are exactly the cases where BS_SIF_CALL package can help. It contains specific procedures that allow users to execute many types of SIF API calls. It also contains generic procedures (a framework of sorts) for making SIF API calls that are not currently implemented in the package, or to augment the package with procedures to execute other specific types of SIF API calls.

Using BS_SIF_CALL package makes it possible to implement a mix of database operations and SIF API Calls in a single database program, avoiding the need for manual operations in the MDM Hub Console. For example, with the help of this package, the following use cases can be served with a single piece of database code:

• Automate a process to populate, based on some business logic, a driving table with IDs of a base object's records and then call ExecuteBatchUnmerge SIF API to unmerge those records — a custom database stored procedure using a call BS_SIF_CALL.EB_UNMERGE procedure can do all that. That procedure can then be registered with the ORS and included as a custom job in a batch group that is executed on a schedule.
• Query database tables to determine, based on some business logic, which records of the Party base object need to be updated and how, then call PutParty SIF API to update those records — again, a database stored procedure can be created to do that, and, optionally, be included as a custom job in a batch group. BS_SIF_CALL package does not have a PUT_PARTY procedure specific to the user's MDM Hub implementation, but it would be quite easy for users to develop and add a specific procedure to the package using its framework types and procedures, or just use the existing generic BS_SIF_CALL.SIF_CALL procedure to modify records in any given base object.
• Perform the following deployment of changes to an MDM ORS:
  1. Run a SQL query to check if the ORS is not in production mode
  2. Check if the ORS repository is valid
  3. apply a change XML file that contains changes to match columns and match rules for the Party base object
  4. validate the ORS repository after applying the change list
  5. execute Synchronize batch job on Party base object to rebuild match key (STRP) table
  6. re-grant needed database privileges on the rebuilt (recreated) STRP table
  7. create a new custom stored procedure and new custom indexes on a few ORS tables
  8. register the new procedure and indexes with the ORS
  9. add a custom job to an existing batch group to execute the new custom procedure
  10. validate the ORS repository again after all the changes are applied

  — a single SQL script can do all that using calls to VALIDATE_METADATA, APPLY_CHANGE_LIST, EB_SYNCHRONIZE, REGISTER_CUSTOM_INDEX, and REGISTER_CUSTOM_TABLE_OBJECT procedures in BS_SIF_CALL package. Within the script, results of each database operation and SIF API call can be validated and the script can be terminated if anything is found wrong.