Write to DB
The Write to DB stage writes the output of a dataflow to a database.
General Tab
Option Name |
Description |
---|---|
Connection |
Select the connection for the database you want to use in the Connection field. To make a new database connection, click Manage. For more information on creating database connections, see Database Connection Manager. |
Table/View |
After selecting a connection, specify the table or view to write to. Click the browse button ([...]) to navigate to the table or view that you want to use, or click Create Table to create a new table in the database. Note: If you are writing to a SQL database, you cannot write to
views that reference more than one table. This is due to a
limitation in SQL Server.
|
Create Table |
Creates a new table in the selected database. Choose the owner for the table in the Table owner field and specify the name for the new table in the Table name field. Table names are case sensitive. Specify a primary key by selecting a checkbox in the Primary key column. Also, specify the fields you want to write to the new table by checking the box in the Include column. Width column specifies the field's length for string data type. By default it is 512. If the column Allow Null is checked and the Input Fields contain a null value, then the dataflow will write the null value in the database. Note that you can edit the column name by changing the value in the Output Fields column. The Create Table button supports table creation in the following databases:
Note: For DB2 databases, if you try to create a table and the page size is smaller than the total length of all string columns, you will get an error that says "Failed to build body from content. Serializable class not available to broker."
|
Stage Fields |
In the Stage Fields column you can specify the field you want to write to the database field shown in the Database Field column. |
Include |
The Include column allows you to select the fields you want to write to. Note: To prevent poor performance you should have a sorted index or
key in the database table.
|
date
datatype as String
values. This is the
behavior of the jTDS driver, which is the default driver used by
Spectrum. To handle all date
datatype values as is, use Microsoft's
JDBC driver.Database Connection Manager
The Database Connection Manager allows you to manage registered database connections. To add, modify, delete, and test connections:
- In the Write To DB Options dialog box, click Manage.
- Click Add, Modify, or Delete.
- If you are adding or modifying a database connection, complete these fields:
- Connection name—Enter the name of the new connection.
- Database driver—Select the appropriate database type.
- Connection Options—Specify all the options, typically host, port, instance, user name, and password.
Note: You can test the connection by clicking Test. - If you are deleting a database connection, select the connection you want to remove and click Delete.
Runtime Tab
Option Name |
Description |
---|---|
Write Mode |
Specifies the type of actions to take when writing to the database. One of the following:
|
Batch commit |
Select this option to commit changes to the database after a specified number of records are processed. By default this option is not selected, which means that changes are committed after each record is processed. Selecting this option can significantly improve the performance of the Write to DB stage. |
Batch size |
If you enable the Batch commit option, specifies the number of records to commit to the database in each batch. The default is 1,000. For dataflows created in Spectrum™ Technology Platform 7.0 and earlier, the default is 100. A larger batch size does not always offer better load performance. Consider the following factors when choosing a batch size:
|
Commit at the end | Select this option to ensure that the commit to database operation occurs after all the records are transferred to the database. |
Batch count to commit | Specify a value after which the records are to be committed. Records are committed to the database after every (batch count to commit * batch size) number of records are transferred to the database. For example, if Batch size is set as 1000 and Batch count to commit is set as 3, then the commit occurs after every 3000 records are transferred to the database. |
Truncate table before inserting data |
Select this option if you want to clear all data from the table before writing to the database. |
Drop and recreate the table if it already exists |
Select this option to delete and recreate the table before writing the dataflow's output to the table. This option is useful if you want the table's schema to match the fields from the dataflow and not contain any extraneous schema information. The table that will be deleted and recreated is the one specified in the Table/View field on the General tab. For example, if you specify the Customers table in the Table/View field, and you select Drop and recreate the table if it already exists, then the Customers table will be deleted from the database, and a new table named Customers will be created with a schema that matches the actual fields written to the table. |