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 Users Guide.
Controls
-
ODBC (only in Data Pipeline
if driver and back-end DBMS support this feature)
Syntax
|
1 |
InsertBlock=<span>insert_blocking_factor</span> |
|
Parameter |
Description |
|---|---|
|
insert_blocking_factor |
The number of rows that you want the To turn off block inserting for an ODBC data source in the Data |
Default
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 PBODB125
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
50in the Insert
Blocking Factor box on the Transaction page in the Database Profile
Setup dialog box. -
Application
Type the following in code:
1SQLCA.DbParameter="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.