Managing User-Defined Functions in databases - 6.3

The regular expression function is built in several databases, but many other
databases do not support it. The databases that natively support regular expressions
include: MySQL, PostgreSQL, Oracle 10g, and Ingres while Microsoft SQL server and
Netezza do not, for example.

A different case is when the regular expression function is built in the database but
the query template of the regular expression indicator is not defined.

How to declare a User-Defined Function in a specific database

The regular expression function is not built into all different database
environments. If you want to use the studio to analyze columns against regular
expressions in databases that do not natively support regular expressions, you
can:

Either:

Install the relevant regular expressions libraries on the database. For an
example of creating a regular expression function on a database, see Regular expressions on SQL Server.

Execute the column analysis using the Java engine. In this case, the
system will use the Java regular expressions to analyze the specified
column(s) and not SQL regular expressions. For more information on the Java
engine, see Using the Java or the SQL engine.

How to define a query template for a specific database

A query template defines the query logic required to analyze columns against
regular expressions. The steps to define a query template in the studio include the
following:

Create a query template for a specific database,

Set the database-specific regular expression if this expression is not
simple enough to be used with all databases.

The below example shows how to define a query template specific for the Microsoft
SQL Server database. Regular expressions on SQL Server gives a detailed example on
how to create a user-defined regular expression function on an SQL server.

Prerequisite(s): You have selected the Profiling perspective
in the studio.

To define a query template for a specific database, do the following:

In the DQ Repository tree view, expand
Libraries > Indicators.

Expand System Indicators > Pattern
Matching.

Double-click Regular Expression Matching,
or right-click it and select Open from the
contextual menu.

The corresponding view is displayed to show the indicator metadata and its
definition.

You need now to add to the list of databases the database for which you
want to define a query template. This query template will compute the
regular expression matching.

Click the [+] button at the bottom of the
Indicator Definition view to add a field
for the new template.

In the new field, click the arrow and select the database for which you
want to define the template. In this example, select Ingres.

Copy the indicator definition of any of the other databases.

Click the Edit... button next to the new
field.

The [Edit expression] dialog box is
displayed.

Paste the indicator definition (template) in the Expression box and then modify the text after WHEN
in order to adapt the template to the selected database. In this example,
replace the text after WHEN with WHEN
REGEX.

Click OK. The new template is displayed
in the field.

Click the save icon on top of the editor to save your changes.

You have finalized creating the query template specific for the Ingres database. You can now start analyzing the columns in
this database against regular expressions.

If the regular expression you want to use to analyze data on this server is simple
enough to be used with all databases, you can start your column analyses
immediately. If not, you must edit the definition of the regular expression to work
with this specific database, Ingres in this
example.

If an analysis with a user-defined indicator runs successfully at least one time
and later the indicator definition template for the database is deleted, the
analysis does not fail. It keeps running successfully because it uses the previously
generated SQL query.