|
Database Switch
The Database Switch object is just another database object like any other in VIS, however it has one special feature. It has the ability to switch on-the-fly between other database object types such as ODBC, OLEDB, MS Sql Server and ODP.NET.
The object accomplishes this switch via the DatabaseType property. You simply supply the ConnectString and DatabaseType and VIS will use that database object when the job executes. The ConnectString and DatabaseType properties can also be parameterized, so you can easily write one VIS job that is able to work with multiple databases, and/or database types.
For example, typically if you know you are going to a SQL Server database, you will just want to use the MS SQL Server object. However, if you have a situation where you have an application or database schema that supports multiple databases, you can use the Database Switch object to write a VIS job once and simply switch between databases by using a VIS parameter. You do not have to re-compile the VIS job or do anything special, just switch the ConnectString and DatabaseType on the fly.
For ODP.NET please note: We currently do not distribute the ODP.NET driver along with VIS. If you wish to use ODP.NET with this object, please download and install the ODP.NET data provider directly from Oracle's website at: http://www.oracle.com/technology/tech/dotnet/index.html
See also: Setup requirements for ODP.NET
Properties
Action |
Describes the action to take for output streams. Ignored for input streams. Valid options are:
| ||||||||||||||
ColumnLayout |
The column layout (or file structure) of the table. Click on the Ellipse (...) button to fill in the column layout, or select an existing column layout from the repository. See also (Column level Write Flags) | ||||||||||||||
CommandTimeout |
Specify the maximum number of seconds to wait for any command that is sent to the database. Once this time is exceeded you will receive a timeout error. | ||||||||||||||
ConnectString |
This property allows you to specify the ConnectString that will be used to connect to the data source. You can enter this information in manually, or click the ellipse button to use the ConnectString builder wizard. | ||||||||||||||
DatabaseType |
This property tells the Database Switch object which database object to use during execution. It is a String property, and was built that was intentionally so that it can be easily overridden via a Parameter. This way you can build a job once and use it for multiple database object types without having to recompile the job. The valid values for this property are:
| ||||||||||||||
CreateTable |
The create table property allows you to create your output table in the database during the job execution. You can also choose to DROP the table prior to creating it. The create table wizard will also generate your create table statement for you based on the pre-defined ColumnLayout. | ||||||||||||||
DateTimeFormat |
DateTime format mask to use when loading the data. This is a useful way to format all columns defined as DateTime to standard format. It saves you time from having to format each column individually in the Join object. If the column is left blank the date will be read as it is received from the database. Click on the ellipse to use the mask builder tool. | ||||||||||||||
RowsPerTransaction |
This data object supports batch SQL transactions which can greatly enhance the loading speed of a BlueSky Integration Studio job. Simply enter the number of SQL statements to place in the internal queue before submitting them to the database. For instance; by entering 1000 in this property, BlueSky Integration Studio will not attempt to send the data until 1000 records have been read from the input source, or until the last record is read (i.e. if there are only 300 input records, of course it will then write them to the database). The database can process 1000 sql statements at once using Oracle's Array binding technology, much faster than 1000 individually sent sql statements. | ||||||||||||||
Tablename |
Database table name | ||||||||||||||
Comments |
Comment area to give descriptive text about the data object and its purpose. | ||||||||||||||
Alias |
Alias identifier. Aliases are used on all design objects to simplify coding and to ensure each object has a unique identifier. | ||||||||||||||
Name |
Descriptive name for this object. You can enter any text to describe the object | ||||||||||||||
GenerateSql |
Yes / No. By default, the data object will automatically generate the SQL Select statement for you deriving the information from other properties such as the Tablename and ColumnLayout properties. By changing this property to No you can hand-code the SQL statement in the SQLSelect property window. This gives you complete flexibility to enter SQL statements that join multiple tables together and/or use database optimizer hints. | ||||||||||||||
OnPostDMLFailure |
ContinueOn / AbortJob : When DML (data manipulation language) statements are entered into the PostDML property, you can control whether your job continues executing if the DML statement fails for some reason. DML statements include any valid UPDATE, INSERT or DELETE type of SQL statements. (see Pre / Post DML example) | ||||||||||||||
OnPreDMLFailure |
ContinueOn / AbortJob : When DML (data manipulation language) statements are entered into the PreDML property, you can control whether your job continues executing if the DML statement fails for some reason. DML statements include any valid UPDATE, INSERT or DELETE type of SQL statements. (see Pre / Post DML example) | ||||||||||||||
PostDML |
DML (data manipulation language) statement to execute after the job or Join object completes. Any valid INSERT, UPDATE or DELETE type of SQL Statement. (see Pre / Post DML example) | ||||||||||||||
PreDML |
DML (data manipulation language) statement to execute before the job or Join object starts. Any valid INSERT, UPDATE or DELETE type of SQL Statement. (see Pre / Post DML example) | ||||||||||||||
PreLoadKeysIntoMemory |
(ODP.NET Only) True or False. This property is only used when using the UpdateOrInsertNew Action property and ODPNET is specified for the DatabaseType property. By Pre-loading all of the output tables keys into memory, lookups can be performed much faster during the process of deciding whether or not the output object should Update an existing record, or insert a new record if the specified key value does not exist in the table. Please note: You must put a little thought into this property. Obviously, if your output table has 50 million records in it, you probably don't want to pre-load all the keys into memory as you will probably run out of memory in a hurry. This feature is great for small to mid-sized table, like a Dimension in a data warehouse. Your key size is also a factor, for instance; if you have a small, one column numeric key, you can fit a lot more records into memory. If your key is multiple columns, try to determine the number of bytes of your total key columns and determine how many records you can fit into memory comfortably. | ||||||||||||||
SQLOutputFile |
(output only). This SQLOutputFile is the path and filename to a file where you want the actual SQL statements being generated to be logged. This can come in handy when you are trying to debug a job, or some problematic data. In production it is best to leave this property empty. For instance: c:\jobsql.log | ||||||||||||||
SQLSelect |
The SQL Select statement that will be used for data objects used in input streams. The SQL statement is built automatically by deriving information from other properties such as the Tablename and ColumnLayout properties. You can override this SQL Statement by setting the GenerateSQL property to No. |
© 2003 - 2007 Relational Solutions, Inc. - All rights reserved