When the DATAEXPORT calculation command is used to export data directly into a relational database and when the batch-insert method is used, the DEXPSQLROWSIZE configuration setting allows you to specify the number of rows to be inserted at one time.
To enable batch insert, set the DATAEXPORTENABLEBATCHINSERT configuration setting to TRUE. Essbase determines whether the relational database and the ODBC driver permit batch insert. If they do, Essbase determines the batch size unless you set DEXPSQLROWSIZE. If Essbase cannot determine whether the relational database and the ODBC driver support batch insert, it uses the row-insert method, and DEXPSQLROWSIZE (if set) is ignored.
The DEXPSQLROWSIZE and DATAEXPORTENABLEBATCHINSERT configuration settings apply to block storage databases only.
DEXPSQLROWSIZE [appname [dbname]] n
appname—Optional. Specifies the application for which to set the number of rows to be inserted at one time.
If you specify a value for appname and do not specify a value for dbname, the setting applies to all databases in the specified application.
To enable the setting for a specific database, you must specify an application and database.
If you do not specify an application, you cannot specify a database, and the setting applies to all applications and databases on Essbase Server.
dbname—Optional. Specifies the database, in the application specified by appname, for which to set the number of rows to be inserted at one time.
If you specify a value for dbname but do not specify a value for appname, your specification is ignored, and data associated with logged transactions is archived for all applications and databases on Essbase Server.
n—The number of rows in the batch (from 2 to 1000).
If DATAEXPORTENABLEBATCHINSERT is set to TRUE and DEXPSQLROWSIZE is set to 1, batch insert is disabled (as a DEXPSQLROWSIZE setting of 1 inserts one row at a time).
When using DATAEXPORT to export data for direct insertion into a relational database:
The table to which the data is to be written must exist prior to the data export
Table and column names cannot contain spaces
DEXPSQLROWSIZE Sample Basic 300
Specifies a 300-record batch size for data exported from Sample.Basic to a relational database using DATAEXPORT.
DEXPSQLROWSIZE Sample 500
Specifies a 500-record batch size for data exported from any database within the Sample application to a relational database using DATAEXPORT.