Start of change

AI_COMMONALITY scalar function

The AI_COMMONALITY function computes a similarity score by using the value of the expression argument and the centroid value of the model column.

FL 504

The centroid value represents the common behavior of the model column for all the rows in a table. As a result, when a query invokes the AI_COMMONALITY function, the resulting similarity score represents the similarity of the function argument with the common behavior of the model column, computed over all rows in the table, not just the qualified rows.

Read syntax diagramSkip visual syntax diagramAI_COMMONALITY(expressionUSINGMODEL COLUMNcolumn-name )

The schema is SYSIBM.

The machine learning model that is used to compute the score is determined by the expression or the column-name specified in the USING MODEL COLUMN clause and the centroid values of the model column. You can use the AI_COMMONALITY function to detect outliers. With -1 as the minimum score, the lower the similarity score is, the further away the argument deviates from the common behavior.

expression

An expression that specifies the value on which the similarity score is computed against the centroid values of the model column. The value returned by expression must be a built-in data type that is not binary, LOB, XML, ROWID, CHAR FOR BIT DATA, or VARCHAR FOR BIT DATA.

column-name

An identifier that specifies a column to be used as the model column. The identified column determines the machine learning model that is used for evaluating the function.

An argument to the AI_COMMONALITY function must specify a machine learning model and a model column that is used for evaluating the function. The following rules are used to determine the machine learning model and the model column for the argument:
  • If the expression argument references a standalone column and the USING MODEL COLUMN clause does not specify a model column, the standalone column is used to evaluate the function, and the vector table is used as the model. The vector table is generated from the Db2 table where the standalone column belongs.
  • If the expression argument references a standalone column, but the USING MODEL COLUMN clause specifies a different column, the column specified in the USING MODEL COLUMN clause is used to evaluate the function, and the vector table is used as the model.

    An expression other than a standalone column reference must specify a model column by using the USING MODEL COLUMN clause. The model is determined by the table where the model column belongs.

  • A model column specified in the function invocation must refer to the columns that belong to the same Db2 table or view.
  • Model training must be enabled for the table with which the model is associated, and the model must be trained. The model column must be included in the model.

A model column, specified either as a standalone column reference or in the USING MODEL COLUMN clause, may be a qualified name. The qualifier must not be a synonym name or a correlation name of a table expression. The qualifier must refer to a table or view, or to an alias of a table or view.

If the model column is a numeric column as indicated during model training, the value of the expression is cast to FLOAT during execution of the function.

The result is a double-precision floating point number (FLOAT) that is the similarity score. The FLOAT is a number between -1.0 and 1.0, where -1.0 represents the minimum common values (outlier) and 1.0 indicates the maximum common values.

The result can be null; if any argument is null, the result is the null value. If the expression evaluates to a value that is not seen during model training, the result is also the null value.

Notes

  • You must enable and configure the SQL DI functionality in Db2 to use the AI_COMMONALITY function.
  • You cannot specify the AI_COMMONALITY function in a CREATE VIEW statement or in the fullselect of a materialized-query-definition of a CREATE TABLE statement.
  • The AI_COMMONALITY function reads data from the Db2 table that is associated with the model. Neither the function nor the model training is deterministic. The model may change slightly over time even if it is retrained on the same data. This slight change can cause minor differences in the resulting similarity scores and affect the similarity ordering of the results.
  • Db2 application compatibility level V13R1M504 is required to use the AI_COMMONALITY function.
  • The AI_COMMONALITY function requires that models be trained after the following z/OS® APARs are applied:
    • Apply APAR OA64845 on z/OS 3.1 (HZAI310).
    • Apply APAR OA64844 on z/OS 2.5 (HZAI250).

    To use the function on existing models, you must retrain those models after the application of the required APARs. The function returns null if it is executed on a model that was trained without the required APARs applied.

Examples

The following sample SQL statement finds the top five outliers of the model column CUSTOMERID:

SELECT AI_COMMONALITY(CUSTOMERID) AS SCORE, C.*
FROM CHURN C
ORDER BY SCORE ASC
FETCH FIRST 5 ROWS ONLY;
End of change