InsertBlock
database parameter
Description
Specifies the number of rows that you want the Data Pipeline in
PowerBuilder to insert at one time into a table in the destination
database.
For instructions on using the Data Pipeline, see the section called “Working with Data Pipelines” in Users Guide.
Applies to
ODBC (only in Data Pipeline if driver and back-end DBMS support
this feature)
Syntax
|
1 |
InsertBlock=insert_blocking_factor |
|
Parameter |
Description |
|---|---|
|
insert_blocking_factor |
The number of rows that you want the Data Pipeline To |
Default value
InsertBlock=100
Usage
Requirements for using InsertBlock
To use the InsertBlock parameter, all of the following must be
true:
-
You are using an ODBC driver to access the destination
database in the Data Pipeline. -
The destination database supports the use of bind variables.
(For more about bind variables, see DisableBind.) -
The DisableBind parameter is not set to 1 (the default is 0)
in the database profile of the destination database. This enables
the default binding of input parameters to a compiled SQL statement
in PowerBuilder. -
Maximum Errors is set to 1 in the Data Pipeline.
The SQL Anywhere ODBC driver meets the first two
requirements.
To determine whether your ODBC driver meets these requirements,
see the documentation that comes with your driver.
Determining the InsertBlock value
PowerBuilder searches the following in this sequence to determine
the value for InsertBlock:
-
The section for your database profile in the PowerBuilder
initialization file -
The section for your ODBC driver in the PBODB initialization
file
If PowerBuilder does not find an InsertBlock value in these
locations, it defaults to an insert blocking factor of 100 rows.
What happens
When PowerBuilder finds a value for InsertBlock, the Data Pipeline
batches the specified number of rows and inserts them with a single call
to the ODBC driver you are using to access the destination
database.
If you specify an InsertBlock value or Data Pipeline commit factor
of fewer than 100 rows, the Data Pipeline batches and inserts the
specified number of rows into the destination database. If you specify
more than 100 rows, the Data Pipeline batches and inserts at most only
100 rows at one time.
The insert blocking factor that the Data Pipeline actually uses
depends on the size of the data in each column inserted in the
destination database. In addition, the Data Pipeline does not exceed 64K
of data in the buffer for any one column.
Turning off block inserting
To turn off block inserting for an ODBC data source in the Data
Pipeline, you can do any of the following in the database profile of the
destination database:
-
Set the InsertBlock parameter to 1
-
Set the DisableBind parameter to 1 (to disable default binding
of input parameters to a compiled SQL statement) -
In the Data Pipeline, set Maximum Errors to a value other than
1
Examples
To set the insert blocking factor in the Data Pipeline to 50
rows:
-
Database profile
Type 50 in the Insert Blocking Factor box on the Transaction
page in the Database Profile Setup dialog box. -
Application
Type the following in code:
1SQLCA.DBParm="InsertBlock=50"To set the insert blocking factor in the Data Pipeline to 50
rows, type 50 in the Insert Blocking Factor box on the Transaction
page in the Database Profile Setup dialog box.
See also