Using a custom database configuration file

If you need to customize the way SPSS® Modeler processes SQL, you can use a custom database configuration file. This allows for a range of customizations you might need depending on your specific circumstances. Full documentation for this capability isn't available. Contact your database administrator or IBM Support for assistance with your specific needs regarding this capability, but note that IBM doesn't support customizations done via these configuration files that aren't provided by IBM.

To implement a custom database configuration file

  1. Create a .cfg file for your specific database.
  2. Add options as desired. Extensive customization is possible. Note that the file must be properly named and properly formatted according to your specific database.
  3. Place the file in the config folder of your SPSS Modeler Server and/or SPSS Modeler Client installation directory.
The .cfg file name must follow the format odbc-<db>-custom-properties.cfg, where <db> is one of the following database names:
  • bigquery
  • db2
  • greenplum
  • hana
  • hive
  • impala
  • informix
  • mssql
  • mysql
  • neoview
  • netezza
  • oracle
  • postgresql
  • redshift
  • soliddb
  • sybase
  • teradata
  • vertica

Example: execute_while_getting_schema

When using MySQL database with custom SQL in a Database source node, by default the MySQL database driver calls SQLExecute() when getting the table schema. As a result, SPSS Modeler needs to run your SQL and obtain the data model. If you don't want SPSS Modeler to obtain the data model, follow these steps:

  1. Create a file called odbc-mysql-custom-properties.cfg.
  2. Add the following line and set it to N to override the default behavior for MySQL:
    execute_while_getting_schema, N
  3. Copy the file into the config directory of your SPSS Modeler Server and SPSS Modeler Client installations.

Example: sqlmx_sort_by

By default, order_by isn't permitted within nested SQL during SQL pushback. By enabling order_by, you can force SQL pushback to work for the Sample node when there's a Sort node ahead of it. For example, to enable order_by on Google BigQuery database, follow these steps:

  1. Create a file called odbc-bigquery-custom-properties.cfg.
  2. Add the following line and set it to Y to override the default behavior for Google BigQuery:
    sqlmx_sort_by, Y
  3. Copy the file into the config directory of your SPSS Modeler Server and SPSS Modeler Client installations.

Example: uda_list_sql_basic and uda_list_sql_parameter

You may want to use custom SQL to retrieve database functions and aggregate functions. For example, on Oracle database, follow these steps:

  1. Create a file called odbc-oracle-custom-properties.cfg.
  2. Add the following lines to the file:
    #Define the UDA (database window aggregates) sqls

uda_list_sql_basic, "SELECT '<src_database_name>',OBJECT_NAME,OWNER,'', '', CASE WHEN OWNER='SYS' THEN 1 ELSE 0 END BUILTIN FROM ALL_ARGUMENTS WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM ALL_PROCEDURES WHERE AGGREGATE = 'YES') AND OBJECT_ID NOT IN (SELECT DISTINCT OBJECT_ID FROM ALL_ARGUMENTS WHERE PLS_TYPE IS NULL) AND ARGUMENT_NAME IS NULL ORDER BY OBJECT_ID"

uda_list_sql_parameter, "SELECT POSITION, DATA_PRECISION,DATA_SCALE,DATA_TYPE,'',DATA_TYPE,'',0 FROM ALL_ARGUMENTS WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM ALL_PROCEDURES WHERE AGGREGATE = 'YES') AND OBJECT_ID NOT IN (SELECT DISTINCT OBJECT_ID FROM ALL_ARGUMENTS WHERE PLS_TYPE IS NULL) ORDER BY OBJECT_ID,POSITION"
  3. Copy the file into the config directory of your SPSS Modeler Server and SPSS Modeler Client installations.
uda_list_sql_basic and uda_list_sql_parameter may use other custom SQL provided they conform to the following table schemas below (step 2 is an example).
#table schema for uda_list_sql_basic
databaseName,function,schema,catalog,description,isBuiltIn

#table schema for uda_list_sql_parameter
position,precision,scale,returnType,returnTypeName,parameterTypes,parameterTypeNames,isVarArg