Wednesday, December 26, 2012

Working with BizTalk Adapter 3.0 for Oracle Database Binding Properties


Working with BizTalk Adapter 3.0 for Oracle Database Binding Properties

0 out of 1 rated this helpful Rate this topic
The Microsoft BizTalk Adapter 3.0 for Oracle Database surfaces several binding properties. By setting these properties, you can control some of the adapter's behavior. This section describes the Oracle Database adapter binding properties. It also shows how you can access them by using .NET programming or by setting properties on a BizTalk Server physical port binding.
The following table shows the Oracle Database adapter binding properties grouped by category. The category refers to the node under which each binding property appears in the dialog boxes that are presented by different applications to configure the adapter (or binding).

Binding PropertyCategoryDescription.NET Type
DataFetchSize
Buffer management
ODP.NET property. Specifies the amount of data in bytes that ODP.NET fetches from the result set in one server roundtrip. The default is 65536. This property is used for performance tuning.
long (System.Int64)
InsertBatchSize
Buffer management
Specifies the batch size for multiple record Insert operations. The default is one. For values of InsertBatchSize greater than one, the Oracle Database adapter batches the specified number of records into a single ODP.NET call. If the number of records in the Insert operation is not a multiple of the batch size, the final batch will contain fewer records than the batch size value. For example, if the insert message has 10 records and theInsertBatchSize is set to 1, the adapter reads individual records and writes them into the Oracle database. So, the adapter performs 10 separate operations on the Oracle database. Similarly, if the insert message has 10 records and the InsertBatchSize is set to 5, the adapter will read and write 5 records at a time into the Oracle database, therefore performing only 2 insert operations.
If the structure of the records is not the same across a batch, aMicrosoft.ServiceModel.Channels.Common.XmlReaderParsingExceptionexception is thrown and the transaction is rolled back for the entire insert operation. A well-chosen value for InsertBatchSize can greatly improve adapter performance for multiple record Insert operations.
int (System.Int32)
LongDatatypeColumnSize
Buffer management
Specifies the maximum size in bytes of an Oracle long data type column. The default is 32767. The maximum value for this binding property is 2147483648.
long (System.Int64)
MetadataPooling
Buffer management
ODP.NET property. Specifies whether ODP.NET caches metadata information for executed queries. The default is true, which enables metadata pooling. Caching this information improves performance; however, if changes to the underlying Oracle artifacts occur on the Oracle system, this pooled metadata will be out of sync. This might cause operations performed on the Oracle system to return unexpected exceptions. This property is used for performance tuning.
bool (System.Boolean)
StatementCachePurge
Buffer management
ODP.NET property. Specifies whether the ODP.NET statement cache associated with a connection is purged when the connection is returned to the connection pool. The default is false, which disables statement cache purging. This property is used for performance tuning.
bool (System.Boolean)
StatementCacheSize
Buffer management
ODP.NET property. Specifies the maximum number of statements that can be cached by each ODP.NET connection. Setting this property to a non-zero value enables statement caching for connections. The default is 10. This property is used for performance tuning.
int (System.Int32)
EnablePerformanceCounters
Diagnostics
Specifies whether to enable the WCF LOB Adapter SDK performance counters and the Oracle Database adapter LOB Latency performance counter. The default is false; performance counters are disabled. The LOB Latency performance counter measures the total time spent by the Oracle Database adapter in making calls to the Oracle database.
bool (System.Boolean)
CloseTimeout
General
The WCF connection close timeout. The default is 1 minute. Not supported.
System.TimeSpan
EnableBizTalkCompatibilityMode
General
Specifies whether the BizTalk Layered Channel Binding Element should be loaded.
Set this to True to load the binding element. Otherwise, set this to False.
When using the adapters from BizTalk Server, you must always set this property to True. When using the adapters from Visual Studio, you must always set this property to False.
bool (System.Boolean)
Name
General
A read-only value that returns the name of the file generated by the Add Adapter Service Reference Visual Studio Plug-in to hold the WCF client class. The Add Adapter Service Reference Plug-in forms the file name by appending "Client" to the value of the Name property. The value returned is "OracleDBBinding"; for this value, the generated file will be named "OracleDBBindingClient".
string
OpenTimeout
General
ODP.NET property. Specifies the WCF connection open timeout. The default is 1 minute. This property is implemented by using ODP.NET.
Cc185291.Important(en-US,BTS.10).gifImportant
The Oracle Database adapter always uses OpenTimeout to set the connection open timeout when it opens a connection to the Oracle database. The adapter ignores any timeout (System.TimeSpan) parameters passed when you open a communication object, such as a channel.
System.TimeSpan
ReceiveTimeout
General
Specifies the WCF message receive timeout. Essentially, this means the maximum amount of time the adapter waits for an inbound message. The default is 10 minutes.
Cc185291.Important(en-US,BTS.10).gifImportant
For inbound operations such as polling, we recommend setting the timeout to the maximum possible value, which is 24.20:31:23.6470000 (24 days). When using the adapter with BizTalk Server, setting the timeout to a large value does not impact the functionality of the adapter.
System.TimeSpan
SendTimeout
General
ODP.NET property. Specifies the WCF message send timeout. The default is 1 minute. Not supported.
System.TimeSpan
EnableSafeTyping
Metadata
Enables or disables safe typing. The default is false; safe typing is disabled. This feature controls how the adapter surfaces certain Oracle data types. For more information about safe typing, see Basic Oracle Data Types.
bool (System.Boolean)
UseSchemaInNamespace
Metadata
Specifies whether the schema name (SCOTT, HR, and so on) is included in the xml namespace for operations and their associated types. The default istrue; the schema name is included in the namespace. The advantage of not having scheme name included in the namespace is that if there is a table with same name (for example, EMP) in two different schemas then the same XML can be used to perform the simple SQL operations (Insert, Update, Delete, Select) on both tables.
For example, if the UseSchemaInNamespace property is true, the namespace for these operations on the SCOTT.EMP table is "http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Table/EMP"; if it is false, the namespace is "http://Microsoft.LobServices.OracleDB/2007/03/Table/EMP".
Cc185291.Important(en-US,BTS.10).gifImportant
The message action is not affected by the UseSchemaInNamesapcebinding property; it always includes the schema name.
Cc185291.Important(en-US,BTS.10).gifImportant
We strongly recommend setting this binding property to true while generating metadata. If you set this property to false, the Oracle schema names (for example, SCOTT) will not be available in the XML namespace of the generated schema. So, if there are two tables with the same name in two different Oracle schemas, and they are added to the same BizTalk project, the BizTalk project will fail to build and deploy. If you want to include such schemas in the same BizTalk project, you must manually edit them to include the Oracle schema name in the XML namespace.
bool (System.Boolean)
ConnectionLifetime
Oracle Connection Pool
ODP.NET property. Specifies the maximum duration in seconds of a connection. The default is 0. This property is used for performance tuning.
int (System.Int32)
DecrPoolSize
Oracle Connection Pool
ODP.NET property. Specifies the number of connections that are closed when an excessive amount of established connections are not in use. The default is 1. This is used for performance tuning.
int (System.Int32)
IncrPoolSize
Oracle Connection Pool
ODP.NET property. Specifies the number of new connections to be created when a new connection is requested and there are no available connections in the ODP.NET connection pool. The default is 5. This property is used for performance tuning.
int (System.Int32)
MaxPoolSize
Oracle Connection Pool
ODP.NET property. Specifies the maximum number of connections in an ODP.NET connection pool. The default is 100. This property is used for performance tuning.
Cc185291.Important(en-US,BTS.10).gifImportant
You must set MaxPoolSize judiciously. It is possible to exhaust the number of connections available from ODP.NET, if this value is set too large.
int (System.Int32)
MinPoolSize
Oracle Connection Pool
ODP.NET property. Specifies the minimum number of connections in an ODP.NET connection pool. The default is 1. This property is used for performance tuning.
int (System.Int32)
UseOracleConnectionPool
Oracle Connection Pool
ODP.NET property. Specifies whether to use the ODP.NET connection pool. The default is true, which enables connection pooling. The Oracle Database adapter implements connection pooling by using the ODP.NET connection pool.
bool (System.Boolean)
PollingInterval
Polling Receive
Specifies the transacted polling interval, that is, the interval in seconds at which the Oracle Database adapter executes the polling statement against the Oracle database. The default is 500. The polling interval is used by the adapter for the following:
  • The time interval between successive polls. This interval is used to run the poll and post-poll queries. If these queries are executed within the specified interval, the adapter sleeps for the remaining time in the interval.
  • The polling transaction timeout value. This value must be set large enough to include the polling statement execution time, the post-poll statement (if specified) execution time, and the time to receive the reply from the client application to commit the transaction.
If the client application sends a reply before the polling interval expires, the adapter commits the transaction and waits until the polling interval is reached to execute the next poll.
If the client application returns a fault, the adapter terminates the transaction.
If the polling interval expires before the client application sends the reply, the transaction will time out. For more information about how to use binding properties in a polling scenario, see Receiving Polling-based Data-changed Messages.
int (System.Int32)
PollingRetryCount
Polling Receive
Specifies the number of times that polling is retried if an exception is encountered during the polling transaction. The default is zero, which specifies that no retry is attempted.
Cc185291.note(en-US,BTS.10).gifNote
If the TransactionIsolationLevel binding property is set to Serializable, you may get an Oracle exception like "ORA-08177: can't serialize access for this transaction." In such a case, you must set the PollingRetryCountbinding property to define the number of times you want the adapter to retry the same transaction.
int (System.Int32)
PollingStatement
Polling Receive
Specifies the polling statement in the form of a SQL SELECT statement. The default is null. You must specify a value for PollingStatement to enable polling. The polling statement is executed against the Oracle database at the interval specified by the PollingInterval property.
Cc185291.Important(en-US,BTS.10).gifImportant
The Oracle Database adapter executes the polling statement and the post-poll statement (if specified) inside of an Oracle transaction. It is recommended that you specify a FOR UPDATE clause in your SELECT statement. This will ensure that the selected records are locked during the transaction and that the post-poll statement can perform any required updates on the selected records.
For more information about how to use binding properties in a polling scenario, including the use of the FOR UPDATE clause; see Receiving Polling-based Data-changed Messages.
string
PostPollStatement
Polling Receive
Specifies a PL/SQL block that is executed after the polling statement and before the /POLLINGSTMT message is sent to the consumer. The default isnull; no post-poll statement is executed. The post-poll statement executes inside the polling transaction. Two common uses for the post-poll statement are to:
  • Update a column in the rows returned in the polling statement to indicate that they have been processed and should be excluded from subsequent polling queries.
  • Move processed records to a different table.
Cc185291.Important(en-US,BTS.10).gifImportant
If a post-poll statement is specified, PollingInterval should be set large enough for the PL/SQL block to complete before the interval expires.
For more information about how to use binding properties in a polling scenario, see Receiving Polling-based Data-changed Messages.
string
TransactionIsolationLevel
Transactions
ODP.NET property. Specifies the Oracle transaction isolation level. The default is ReadCommitted. Only ReadCommitted and Serializable are supported. For more information, about using TransactionIsolationLevelin polling scenarios, see Receiving Polling-based Data-changed Messages.
Cc185291.note(en-US,BTS.10).gifNote
In scenarios where there is parallel access and modification to the table being polled, you must set this property to Serializable.
Microsoft.Adapters.OracleDB.TransactionIsolationLevel
AcceptCredentialsInUri
Not surfaced by the Consume Adapter Service Add-in or the Add Adapter Service Reference Plug-in.
Specifies whether the Oracle connection URI can contain user credentials for the Oracle database. The default is false, which disables user credentials in the connection URI. If AcceptCredentialsInUri is false and the Oracle connection URI contains user credentials, the Oracle Database adapter throws an exception. You can set AcceptCredentialsInUri to true if you must specify credentials in the URI. For more information, see The Oracle Database Connection URI.
bool (System.Boolean)
You can set the Oracle binding properties when you specify a connection to an Oracle database. For information about how to set binding properties when you:

No comments:

Post a Comment