Database export advanced options

When you click the Advanced button from the Database export node dialog box, a new dialog box is displayed in which you can specify technical details for exporting results to a database.

Use batch commit. Select to turn off row-by-row commits to the database.

Batch size. Specifies the number of records to send to the database before committing to memory. Lowering this number provides greater data integrity at the cost of slower transfer speeds. You may want to fine-tune this number for optimal performance with your database.

Use bulk loading. Specifies a method for bulk loading data to the database directly from IBM® SPSS® Modeler. Some experimentation may be required to select which bulk load options are appropriate for a particular scenario.

  • Via ODBC. Select to use the ODBC API to execute multiple-row inserts with greater efficiency than normal export to the database. Choose from row-wise or column-wise binding in the options below.
  • Via external loader. Select to use a custom bulk loader program specific to your database. Selecting this option activates a variety of options below.

Advanced ODBC Options. These options are available only when Via ODBC is selected. Note that this functionality may not be supported by all ODBC drivers.

  • Row-wise. Select row-wise binding to use the SQLBulkOperations call for loading data into the database. Row-wise binding typically improves speed compared to the use of parameterized inserts that insert data on a record-by-record basis.
  • Column-wise. Select to use column-wise binding for loading data into the database. Column-wise binding improves performance by binding each database column (in a parameterized INSERT statement) to an array of N values. Executing the INSERT statement once causes N rows to be inserted into the database. This method can dramatically increase performance.

External Loader Options. When Via external loader is specified, a variety of options are displayed for exporting the dataset to a file and specifying and executing a custom loader program to load the data from that file into the database. IBM SPSS Modeler can interface with external loaders for many popular database systems. Several scripts have been included with the software and are available along with technical documentation under the scripts subdirectory. Note that in order to use this functionality, Python 2.7 must be installed on the same machine as IBM SPSS Modeler or IBM SPSS Modeler Server, and the python_exe_path parameter must be set in the options.cfg file. See the topic Bulk loader programming for more information.

  • Use delimiter. Specifies which delimiter character should be used in the exported file. Select Tab to delimit with tab and Space to delimit with spaces. Select Other to specify another character, such as a comma (,).
  • Specify data file. Select to enter the path to use for the data file written during bulk loading. By default, a temporary file is created in the temp directory on the server.
  • Specify loader program. Select to specify a bulk loading program. By default, the software searches the scripts subdirectory of the IBM SPSS Modeler installation for a Python script to execute for a given database. Several scripts have been included with the software and are available along with technical documentation under the scripts subdirectory.
  • Generate log. Select to generate a log file to the specified directory. The log file contains error information and is useful if the bulk load operation fails.
  • Check table size. Select to perform table checking that ensures that the increase in table size corresponds to the number of rows exported from IBM SPSS Modeler.
  • Extra loader options. Specifies additional arguments to the loader program. Use double quotes for arguments containing spaces.

Double quotes are included in optional arguments by escaping with a backslash. For example, the option specified as -comment “This is a \”comment\"" includes both the -comment flag and the comment itself rendered as This is a “comment”.

A single backslash can be included by escaping with another backslash. For example, the option specified as -specialdir “C:\\Test Scripts\\” includes the flag -specialdir and the directory rendered as C:\Test Scripts\.