Write to DB

The Write to DB stage writes the output of a dataflow to a database.

Note: Significant performance improvements can be achieved by using multiple runtime instances of Write to DB. To specify multiple runtime instances, click the Runtime button.

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: Axion DB2 Derby/Cloudscape Firebird HSQLDB Interbase MaxDB/SapDB McKoi MySQL Oracle PostgreSQL SQL Server Sybase 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.

Note: The Write to DB stage writes all values of the 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: Insert Insert new records into the database but do not update existing records. This is the default setting. Update Update existing records in the database but do not insert new records. Note: If you select Update, the primary key column name used in the input table must match the primary key column name in the output table. If you try to update a table where the primary key column name does not match the input, or where the primary key column is not defined, the update will not work. Insert if not able to update Insert new records into the database if the record does not exist, otherwise update the existing record.
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: Data arrival rate to Write To DB stage: If data is arriving at slower rate than the database can process then modifying batch size will not improve overall dataflow performance. For example, dataflows with address validation or geocoding may not benefit from an increased batch size. Network traffic: For slow networks, increasing batch size to a medium batch size (1,000 to 10,000) will result in better performance. Database load and/or processing speed: For databases with high processing power, increasing batch size will improve performance. Multiple runtime instances: If you use multiple runtime instances of the Write to DB stage, a large batch size will consume a lot of memory, so use a small or medium batch size (100 to 10,000). Database roll backs: Whenever a statement fails, the complete batch is rolled back. The larger the batch size, the longer it will take to perform the to rollback.
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.