Patent Application
20120023116
A portion of the disclosure of this patent document contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.
The present invention is generally related to transferring data between different systems, and is particularly related to a system and method for performing real-time conversion of data present in the form of messages on JMS-compliant messaging systems into database transactions, which can subsequently be applied to multiple heterogeneous databases.
A common data integration problem is the need to apply data transactions that occur in a first system, at a second, perhaps totally different, system, whilst preserving the transactional semantics of the data. Oftentimes, the operations belonging to particular transactions need to be applied either entirely or partially as soon as possible after the original transaction has completed (i.e. in real-time). In some environments, messages representing a change to an original data are placed on a messaging system and/or a message queue by an application at the first system. Generally, in order to perform a similar operation at a second system (e.g. a database), custom software code must be developed to read the data from the message queue, and apply it as appropriate to the second system). Problems can occur if the second system is not known a priori, or if the transaction needs to be recovered, since there is no means to restore data from messages that have already been consumed. Certain systems would benefit from such integration, but no real-time means is currently available. This is the general area that embodiments of the invention are intended to address.
Described herein is a system and method for performing real-time conversion of data which is present in the form of messages on JMS-compliant or other messaging systems into database transactions, which can then subsequently be applied to multiple heterogeneous databases or other systems. In accordance with an embodiment, the invention provides a means by which data can be read from messages in a messaging system, and converted to a set of database operations that are then stored as a persistent trail file (for example, as an Oracle GoldenGate trail file). The operations, as recorded in the trail file, can then be routed via a network and applied to target systems as required, for example to maintain a replicated set of information at one or more different or heterogeneous systems. The data in the message can be formatted in a variety of ways, for example, as fixed width, delimited, or XML data, and the system can be configured to convert this data as appropriate. In accordance with an embodiment, the data can be read in real-time from the messaging system, and written out as quickly as it can be consumed to the persistent trail files. In scenarios where there is no current means of integration of two systems, the invention can be used as a bridge if, for example, the source system is modified or configured to write data changes to a messaging system, and Oracle GoldenGate is used as a means of delivery to the target system. Embodiments of the invention address the problem of propagating transactions from a message queue to a target system in real-time, do not require custom programs to be developed that are specific to each target database system, and provide a way to retransmit transactions in failure scenarios.
Described herein is a system and method for performing real-time conversion of data present in the form of messages on JMS-compliant messaging systems into database transactions which can subsequently be applied to multiple heterogeneous databases. Advantages of the solution include that it operates in real-time, providing the ability to integrate changes from one system to another as they occur with very little latency; provides flexible conversion options that allow the system to read a variety of formats of source messages, including XML, fixed width and delimited data, and have complete control on how those messages are converted into equivalent database operations; supports persistent storage of operations in a trail file and enables operations to be replayed into target systems should the target system need to be recovered from an earlier point in time; enables messages to be read from the queue in a transactional fashion and transaction semantics are embedded in the resultant trail; works out-of-the-box without any custom coding requirements to read messages and convert to operations in a trail; and, once the message queue data has been converted to trail files, can be applied to other heterogeneous databases.
In accordance with an embodiment, the messaging capture adaptor can be provided as an extension to a data capture and replication system or product 110, for example as an extension to the Oracle GoldenGate product which enables extraction and replication of data in the form of trail information or trail files 112 from a first database or transaction system to a second database or transaction system.
At runtime, while the source system operates, messages are placed onto queues or topics corresponding to changes in the data at the source system, and using standard, e.g. JMS techniques for placing messages onto such queues. Depending on the particular implementation and user's preferences, an existing queue or topic can be used, or alternately a new queue or new topic can be configured for use in the replication process. The choice between using a queue or topic can also vary with the particular implementation and user preference, generally a queue will provide a 1:1 association between a message provider and a message queue subscriber, while a topic will allow for 1:N associations between a message provider and multiple N topic subscribers.
In accordance with an embodiment, the messaging capture adaptor retrieves messages from the configured queues or topics, similarly using standard, e.g. JMS techniques for retrieving messages from such queues, converts the messages, and provides them as an output to the data capture and replication system or product. The extract process can then be used to generate a trail containing the processed data, which trail can be fed to other systems 114, 115, or and/or databases 116.
In accordance with an embodiment, the messaging capture adaptor can be provided as a Vendor Access Module (VAM) plug-in or application program interface (API) to the data capture and replication system or product. A set of properties 124, rules, and external files and definitions 126 can be used to provide messaging connectivity information, and to define how the messages are to be parsed and mapped to records in the target trail.
In accordance with an embodiment, the messaging capture adaptor comprises three major components: a messaging connectivity component 118; a data parsing component 120; and a VAM Interface component or API 122. It will be evident that in accordance with other embodiments, different arrangements and other types of components, features, coding or logic, can be used to provide similar functionality.
In accordance with an embodiment, all messaging connectivity is provided through, e.g. a generic JMS interface. The JMS connectivity can be configured through property files, in a similar way to existing Java delivery components, that allow the following to be set: the Java classpath for the JMS client; a JMS source destination (queue or topic) name; JNDI connection properties, such as standard JNDI connection properties for Initial Context, connection factory JNDI name, and destination JNDI name; and Security information, such as JNDI authentication credentials, and JMS connection username and password.
In accordance with an embodiment, the messaging capture adaptor can connect to the messaging provider on start-up; the ‘extract’ process can be configured to automatically restart using standard ‘manager’ autorestart parameters in the event of any connectivity related errors during processing.
In accordance with an embodiment, the messaging capture adaptor supports JMS text messages. When asked by the messaging capture adaptor for the next message, the messaging connectivity component: starts a local JMS transaction if not already started; reads a message off the queue; if no message exists returns end-of-file; and otherwise returns the contents of the message and any message header properties to the messaging capture adaptor. Once the messaging capture adaptor has successfully parsed the message, has output an entire transaction to a trail, and ensured the transaction has been flushed to disk, the messaging connectivity component commits the JMS local transaction, removing the messages from the queue or topic. In the event of any error this local transaction can be rolled back, leaving the unprocessed messages on JMS server.
In accordance with an embodiment, JMS properties obtained from the header are available as values in all parsers when specified in the properties file in the form $JMSProperty.
In accordance with an embodiment, the messaging capture adaptor provides one or more of: fixed width message parsing delimited message parsing XML message parsing In order to parse the data and translate it to Oracle GoldenGate records, the data parsing component needs to be provided with the source and target formats of the data, and rules to translate from the source to the target. The mechanisms used to provide this information are parser dependent.
In accordance with an embodiment, a separate utility 128 can be provided to generate a sourcedefs file 132 that is based on the data definition and parser properties. Since the trail may be stored in the form of binary data in a trail file, the sourcedefs file allows another system or product to decipher its contents.
In accordance with an embodiment, the VAM plug-in or application program interface (API) provides a read interface that can invoked through a standard Oracle GoldenGate extract parameter file as is demonstrated in the following sample:
In accordance with an embodiment, the messaging capture adaptor can be provided as a shared library (for example as a .so or .dll file) that integrates into the dataflow via, e.g. the GoldenGate extract process. In accordance with these embodiments, the separate utility can use the same properties file as the messaging capture adaptor, reading in properties and any parser specific data definition, and creating a GoldenGate sourcedefs file to be used by an Oracle GoldenGate product.
As described above, in accordance with an embodiment, the system comprises a messaging capture adaptor which provides for conversion and communication of data from one or more source systems, which in turn include or are associated with one or more messaging systems or messaging queues. The following sections describe a particular implementation of such an embodiment messaging capture adaptor.
in accordance with an embodiment, the Messaging Capture Adaptor (Adaptor) can be provided as an extension to a system such as, e.g. Oracle GoldenGate for Java, that processes data received in the form of messages on a message queue, and communicates with an Oracle GoldenGate extract process in order to generate a GoldenGate trail containing the processed data.
In accordance with an embodiment, the Adaptor can be provided as a Vendor Access Module (VAM) plug-in to a generic extract process. A set of properties, rules and external files can provide messaging connectivity information and define how messages are parsed and mapped to records in the target GoldenGate trail. As described above with regard to
In accordance with an embodiment, the Message Capture can be provided as a shared library (for example, a .so or .dll file) that integrates into the dataflow via the GoldenGate extract process. A separate utility can be provided that uses the same properties file as the Message Capture, reads in properties and any parser specific data definition, and creates a GoldenGate “sourcedefs” file to be used in conjunction with the GoldenGate trail produced by the Message Capture.
In accordance with an embodiment, all messaging connectivity is through a generic JMS interface. The JMS connectivity should be configured through property files, in a similar way to existing Java delivery components, allowing the following to be set: the Java classpath for the JMS client; the JMS source destination (i.e. queue or topic) name; JNDI connection properties (i.e. standard JNDI connection properties for Initial Context; connection factory JNDI name; and destination JNDI name); and security information (i.e. JNDI authentication credentials; JMS connection username and password).
The Adaptor can connect to the messaging provider on start-up; and the ‘extract’ process can be configured to automatically restart using standard ‘manager’ autorestart parameters in the event of any connectivity related errors during processing.
In accordance with an embodiment, the Adaptor supports JMS text messages. When asked by the Adaptor for the next message, the Messaging Connectivity component: starts a local JMS transaction if not already started; reads a message off the queue; if no message exists returns end of file; otherwise returns the contents of the message and any message header properties to the Adaptor.
Once the Adaptor has successfully parsed the message, has output an entire transaction to the trail, and ensured the transaction has been flushed to disk, the Messaging Connectivity component commits the JMS local transaction, removing the messages from the queue or topic. In the event of any error this local transaction can be rolled back, leaving the unprocessed messages on JMS server.
JMS properties obtained from the header are available as values in all parsers when specified in the properties file in the form $JMSProperty.
In accordance with an embodiment, the Adaptor provides one or more of: fixed width message parsing; delimited message parsing; or XML message parsing.
In order to parse the data and translate it to, for example, Oracle GoldenGate records, the Parser needs to be provided with the source and target formats of the data, and rules to translate from source to target. The mechanisms used to provide this information are parser dependent. In accordance with an embodiment, a separate utility can be provided to generate an Oracle GoldenGate “source clefs” file based on the data definition and parser properties.
In accordance with an embodiment, the Adaptor supports fixed with parsing based on a data definition provided, e.g. in Cobol Copybook format, together with a set of properties that indicate how to map the Copybook to logical table records in an Oracle GoldenGate trail file and associated source definitions file. The Copybook should have records defined at level 01 that will map onto logical tables, together with higher level fields (05, 20, etc.) that correspond to columns of these logical tables. The data to be parsed should consist of a standard format header containing a number of fixed width fields, followed by a data segment also containing fixed width fields. The header should be defined by one of the Copybook level 01 records.
Within this record definition there must be fields that represent: a commit timestamp/change time for the record; a function code to differentiate operations of the following types (Insert; Update; Delete); and the Copybook record name required to parse the data segment.
The Copybook record name for the header record definition, and the field names for timestamp, function code and data record name should be configurable through a set of properties. An example Copybook definition containing these values is as follows:
with corresponding example configuration properties of:
The logical table name output by the Adaptor will match the table value defined above. In accordance with an embodiment, it is possible to define the logical schema name through a static property:
Additionally, in accordance with an embodiment, it is possible to use more than one field to determine a record name. The defined fields will simply be concatenated in the order they are provided. For example:
will give logical schema and table names of the form:
In accordance with an embodiment, the Adaptor should parse the timestamp using a default format of “YYYY-MM-DD HH:MM:SS:FFF” with FFF depending on the size of the field. It should also be possible to override this default format using a comment before any date/time field that contains the format in a normalized fashion. For example, to parse using the format “YYYY-MM-DD-HH.MM.SS.FF” a comment would be added as follows:
The values in the optype should be mapped onto standard GoldenGate operation types using a set of properties, indicating which value of the optype maps onto which operation type. For example:
Any fields in the header record not mapped to the GoldenGate header fields should be output as columns for all records parsed by the Adaptor. The data in the header and record data will be parsed based on the PIC definition of that data and written to the trail depending on the translated data type as follows: any field definition preceded by a timestamp format comment should be translated to an Oracle GoldenGate date/time field with an appropriate size, if no timestamp format is present, the field should be treated as its underlying datatype; any X field should be translated to CHAR datatype with the defined length; and any 9 field should be translated to a NUMBER datatype with the defined precision and scale. Signed and unsigned numbers with and without decimal points should be supported. Table 1 shows some examples.
In order to identify key columns within data (not header) records in the copybook, a comment of * KEY should be used. This will mark these columns as keys when the source definitions are generated with the utility. For example:
indicates that the Key column is a key for the TABLE1 table.
In accordance with an embodiment, the Adaptor supports delimited parsing based on a set of properties and a pre-existing source definitions file. The properties determine the delimiters to use and other rules; the source definitions file determines the valid tables that can be handled and the ordering and data types of columns in those tables. A delimited message has the format:
The meta columns determine fields in the delimited record that have special meaning, corresponding to a header for that record. Valid meta columns should include:
The parsing of the delimited data should be governed by a set of properties that determine delimiters and other values as follows:
In accordance with an embodiment, a value can be defined to look for inside data values that equates to an escaped version of any of the above delimiters. For example, inside a quoted value, replace “ ” with “, or \\\n with \n. Data values may be present in the record with or without quotes. However, unescaping will only happen within quoted values, and a non-quoted string that matches a null indicator will be treated as null. The following additional properties should allow for more fine grained control over how the text in a delimited record is parsed:
When parsing dates, a default format of “YYYY-DD-MM HH:MM:SS.FFF” should be used. However, in certain cases this will need to be overridden. The user should be able to override this on a global, table or column level. For example:
In accordance with an embodiment, the parser should first read and validate the meta columns for each record. Once this step is complete, the table name should be available. This should be used to lookup the column definitions for that table in the source definitions file. The columns should then be parsed and output in the trail in the order, and in the format, defined by the source definitions. If the specified table is not present, the capture should stop.
In accordance with an embodiment, the Adaptor supports XML parsing based on a set of properties and a pre-existing source definitions file. The properties determine the rules used to determine XML elements and/or attributes that correspond to transactions, operations and columns; the source definitions file determines the valid tables that can be handled and the ordering and data types of columns in those tables. An XML message to be parsed can be formatted in one of two general styles:
Every operation to every table will have the same basic message structure consisting of transaction, operation and column elements. The table name, operation type, timestamp, column names, column values, etc. are obtained from attribute or element text values.
In this case, the NewMyTableEntries element marks the transaction boundaries. The NewMyTableEntry indicates an insert to MY.TABLE. The timestamp is present in an element text value, and the column names are indicated by element names.
In accordance with an embodiment, it should be possible to define rules in the properties file to parse either of these two styles of XML through a set of XPath like properties. The goal of the properties is to map the XML to a predefined source definitions files through XPath matches.
Independent of the style of XML, the system should be enabled to determine how to ascertain: transaction boundaries; operation entries and meta data including table name, operation type, timestamp; and column entries and meta data including column name and/or index, column before and/or after values.
This can be done through a set of inter-related rules. In accordance with an embodiment, for each type of XML message to be processed there can be one high-level rules that encapsulates the detailed rules to obtain the above data. These can be specified through a set of properties. For example:
All rules should have the following generic properties: name—a mechanism to identify the rule (part of property name); type—either transaction, operation or column; match—an XPath expression determining when to activate the rule (can be omitted); and subrules—a set of rule names to be executed when this rule is matched. Each of the rule types should then have additional properties specific to that type.
In accordance with an embodiment, the XML parser supports a subset of XPath expressions necessary to match elements and extract data. It is not the intention to support the full set of XPath functionality. An expression can be used to match a particular element, or extract data. In the data extraction case most of the path is used to match, the tail of the expression is used for extraction. The following constructs should be supported:
This should support simple expressions such as:
or more complex expressions such as:
In addition to matching paths, the XPath expressions can also be used to obtain data values, either absolutely, or relative to the current node being processed. Data value expressions can contain any of the path elements above, but must end with one of:
In accordance with an embodiment, in addition to XPath expressions, values should also be able to be directly extracted from JMS message properties as detailed earlier, or hardcoded values of the form “value”.
In accordance with an embodiment, the boundary for a transaction is the highest level rule. The options for how to determine transactions from the contents of XML are as follows: each message contains a single transaction; each message can contain multiple transactions; transaction can span messages. These are specified as follows:
If a transaction rule is specified, the following properties of the rule may also be defined through XPath or other expressions:
Transaction rules can have multiple subrules, but each must be of type operation.
In accordance with an embodiment, an operation rule can either be a subrule of a transaction rule, or a highest level rule (if transactionality is determined via a property of the operation. In addition to the standard rule properties an operation rule should also define the following through XPath or other expressions:
Operation rules can have multiple subrules, of type operation or column.
In accordance with an embodiment, a column rule must be a subrule of an operation rule. In addition to the standard rule properties, a column rule should also define the following through XPath or other expressions:
In accordance with an embodiment, the example shown in Table 2 uses the XML samples provided before, with appropriate rules to generate the same resulting operation on the MY.TABLE table.
In accordance with an embodiment, a utility that generates a GoldenGate source definitions file from the properties defined in a properties file can be provided, which reads in the properties file, and outputs a normalized definition of tables, based on property settings and other parser specific data definition values. The usage should be:
In accordance with an embodiment, the Message Capture Adapter requires and/or is intended for use with a build of Oracle GoldenGate version 10.4 or above.
In accordance with an embodiment, the Message Capture can be prebuilt and prepackaged for a particular platform (e.g. on windows it is shipped as a zip file; on UNIX, as a “tar” file). The file should contain: shared library; required Java code; sample VAM properties file(s); and a sample extract parameter file.
In accordance with an embodiment, a typical GoldenGate Configuration for Message Capture is shown and described above with regard to
In accordance with an embodiment, the VAM should be invoked through a standard GoldenGate extract parameter file, for example as shown in Table 3.
Due to the nature of messaging systems, standard GoldenGate repositioning commands may not be supported. For example, alter elms begin now will have no effect on the extract, it will always resume taking messages from the end of the queue.
In accordance with an embodiment, the Message Capture reads properties from a properties file specified via the PARAMS parameter within the extract parameter file as shown above. This properties file contains information such as logging settings, parser mappings, and JMS connection settings.
The following sections detail the various property settings required for the Message Capture VAM. The property settings are split into the following sections: Logging; JMS Connection; and Parsing. Each property specification includes usage description and examples. All properties in the property file are of the form:
The value may be a single string, integer, or boolean, or could be comma delimited
strings. Comments can be entered in to the properties file with the # prefix at the beginning of the line. For example:
Properties themselves can also be commented, which is useful in testing configurations without losing previous property settings.
Logging is standard to many GoldenGate adaptors and is controlled by the following properties. In accordance with an embodiment, for the Messaging Capture and other Java Adaptors, the Java side of the integration can also be configured to produce an additional log file.
log.logname
Takes any valid ascii string as the prefix to the log file name. The log file produced has the current data appended to it in yyyymmdd format, together with the .log extension.
would produce a log file of name msgvam—20090503.log on May 3, 2009. The log file will roll over each day independent of the starting/stopping of processes. It is possible to include directory names in the name of the log file, e.g. dirrpt/msgvam, but the directories should already exist.
log.level
Set the overall log level of the logging module for all modules.
The default logging level is INFO. The messages in this case will be produced on startup, shutdown and periodically during operation, but would not impede performance of the data path. If the level is switch to DEBUG, large volumes of messages may occur which could impede performance. For example:
sets the global logging level to INFO
log.tostdout
Determines whether log information should be written to standard out. Useful if the extract process running the VAM is started on the command line, or on operating systems where stdout is piped into the report file.
Determines whether log information should be written to the specified log file.
In accordance with an embodiment, these specify how to start up the JVM running the JMS integration, and properties for the JMS Connection.
jvm.bootoptions
This property configures the arguments passed to the internal instance of the JVM:
These options should be the same as would be passed to “Java” if this it were executed on the command-line. Options may include: the classpath; system properties; JVM memory options (max memory, initial memory, etc.) that are valid for the particular instance of Java being used. Valid options may vary per JVM version and provider. On Windows, classpath entries must be separated with a semicolon (“;”). On Linux/UNIX, they must be separated with a colon (“:”). Additional system properties may be set, such as the Log 4J configuration file. For example:
jms.report.output
Determines where the jms report is written to:
Determines the frequency of report generation based on time.
Determines the frequency of report generation based on number of records.
Determines how to create a unique ID passed back from the JMS integration to the Message Capture VAM. This may be used by the VAM as a unique sequence ID for records.
The ID returned must be unique, incrementing, and fixed-width. If there are duplicate numbers, the duplicates will be skipped. If the message ID changes length, “extract” will abend.
jms.destination
Determines the queue or topic name to be looked up via JNDI.
Determines the connection factory name to be looked up via JNDI.
Determine the user name and password of the JMS connection itself, as specified by the JMS provider.
This is not used for JNDI security—for setting JNDI authentication, the properties “java.naming.security.” can be used)
For example:
In accordance with an embodiment, in addition to specific properties for the Message Capture VAM, the JMS integration should also support setting of JNDI properties required to obtain a connection to an Initial Context from which the connection factory and destination can be looked up. The following properties must be set:
and the following may be set if JNDI security is enabled:
For example:
In accordance with an embodiment, these specify which parser to use, how the unique record sequence number is obtained, and specific properties of the chosen parser.
parser.type
Specifies the parser to use:
In accordance with an embodiment the following are required properties for parser=fixed.
fixed.schema
Specifies what kind of file is used by the Message Capture as metadata. The two valid options are sourcedefs and copybook.
If the sourcedefs option was specified above, then this property specifies the location of the sourcedefs file to be read in.
Location of the copybook file to be read in by the Message Capture process. To be used if the schema property was set to copybook.
Determines the name of the source defs entry or copy book record that contains header information used to determine the data block structure:
Determines the name of the header field, JMS property, or system value that contains the seqid used to uniquely identify individual records. This value must be continually increasing and have the last character be the least significant.
Determines the name of the field, JMS property, or system value that contains the timestamp.
fixed.timestamp.format
Determines the format of the timestamp field.
Determines the name of the field, JMS property, or system value that contains the txid used to unique identify transactions. This value must be continually increasing for each transaction.
fixed.txowner
Optionally, determines the name of the field, JMS property, or static value that contains an arbitrary user name associated with a transaction. This value may be used to exclude certain transactions from processing
fixed.txname
Optionally, determines the name of the field, JMS property, or static value that contains an arbitrary name to be associated with a transaction
Determines the name of the field, or JMS property that contains the operation type, which is validated against the op type values specified in the next sections.
The value to look for to identify an operation as an Insert type operation. The default is ‘I’.
The value to look for to identify an operation as an Update type operation. The default is ‘U’.
The value to look for to identify an operation as a Delete type operation. The default is
Determines the name of the table, which enables the parser to look up the corresponding data record definition needed to translate the portion of the data remaining after the header.
More than one comma delimited field name may be used to determine the name of the table Each field name corresponds to a field in the header record defined by the fixed.header property, or JMS property. The values of these fields are concatenated together to identify the data record. For example:
fixed.schema
Determines the static name of the schema when generating table names SCHEMA.TABLE.
Optionally determines the name of the field, or JMS property that contains a transaction indicator, which is validated against the transaction indicator values. If not defined all operations within a single message will be seen to have occurred within a whole transaction. If defined, then will determine the beginning, middle and end of transactions. Transactions defined in this way can span messages.
fixed.txind=$TX_IND
fixed.txind.beginval
The value to look for to identify an operation as the beginning of a transaction. Defaults to ‘B’.
The value to look for to identify an operation as the middle of a transaction. Defaults to ‘M’.
The value to look for to identify an operation as the end of a transaction. Defaults to ‘E’.
The value to look for to identify an operation as a whole transaction. Defaults to ‘W’.
In accordance with an embodiment the following are required properties for parser=delim.
delim.sourcedefs
Specifies the location of the sourcedefs file to be read in.
Determines the list of values to be read before encountering the data and assigns names to each value.
delim.seqid
Determines the name of the header field, JMS property, or system value that contains the seqid used to uniquely identify individual records. This value must be continually increasing and have the last character be the least significant.
Determines the name of the JMS property, header field, or system value that contains the timestamp.
delim.timestamp.format
Determines the format of the timestamp field.
Determines the name of the JMS property, header field, or system value that contains the txid used to unique identify transactions. This value must be continually increasing for each transaction.
delim.txowner
Optionally, determines the name of the JMS property, header field, or static value that contains an arbitrary user name associated with a transaction. This value may be used to exclude certain transactions from processing
delim.txname
Optionally, determines the name of the JMS property, header field, or static value that contains an arbitrary name to be associated with a transaction
Determines the name of the JMS property, or header field, that contains the optype to be validated against an optype insertval, etc.
The value to look for to identify an operation as an Insert type operation. The default is ‘I’.
The value to look for to identify an operation as an Update type operation. The default is ‘U’.
The value to look for to identify an operation as a Delete type operation. The default is
Determines the name of the JMS property, or header field that contains the schema and table name in the form SCHEMA.TABLE.
Determines the name of the JMS property, header field, or hard-coded value that contains the schema name.
Determines the name of the JMS property, or header field that contains the table name.
Optionally determines the name of the JMS property, or header field that contains the transaction indicator to be validated against beginval etc. If not defined all operations within a single message will be seen to have occurred within a whole transaction. If defined, then will determine the beginning, middle and end of transactions. Transactions defined in this way can span messages.
delim.txind=txind
The value to look for to identify an operation as the beginning of a transaction. Defaults to ‘B’.
The value to look for to identify an operation as the middle of a transaction. Defaults to ‘M’.
delim.txind.middleval=1
delim.txind.endval
The value to look for to identify an operation as the end of a transaction. Defaults to ‘E’.
delim.txind.endval=2
delim.txind.wholeval
The value to look for to identify an operation as a whole transaction. Defaults to ‘W’.
Determines the delimiter value to be used to separate fields (columns) in the data. Can be defined through characters or hex values:
delim.linedelim
Determines the delimiter value to be used to separate lines (records) in the data. Can be defined through characters or hex values:
Determines the value to be used to identify quoted data, Can be defined through characters or hex values:
Determines the value to be used to identify NULL data, Can be defined through characters or hex values:
Determines the value to look for that indicates a true field delimiter is present in some data. This will then be replaced with the fielddelim value.
Determines the value to look for that indicates a true line delimiter is present in some data. This will then be replaced with the linedelim value.
Determines the value to look for that indicates a true quote is present in some data. This will then be replaced with the quote value.
Determines the value to look for that indicates a true null indicator is present in some data. This will then be replaced with the nullindicator value.
Determines whether before values are present in the data.
Determines whether column names are present in the data.
Determines whether the column names, before and after images should be expected grouped together for all columns or interleaved for each column.
Determines the date format for column data. Should be able to parse using similar format strings to parser.timestamp.format. This should be able to be specified at a global level, a table level or column level.
where:
xml.sourcedefs
Specifies the location of the sourcedefs file to be read in.
Determines the list of XML rules to be used for parsing a message and converting to transactions, operations and columns:
Determines the type of an XML rule:
{rulename}.match
Specifies an XPath expression used to determine whether the rule is activated for a particular document or not:
Specifies a list of rule names that should be check for matches if the parent rule is activated by its match.
{txrule}.timestamp
Optionally determines the XPath expression or JMS property that contains the transaction commit timestamp, or to use the current system time.
Determines the format of the timestamp field.
Determines the seqid for a particular transaction. This can be used when there are multiple transactions per message. Determines the XPath expression, JMS property, or system value that contains the transactions seqid. Any XPath expression must end with a value accessor such as @att of text( ).
{txrule}.seqid={xpath-expression}|${jms-property}|*seqid
For example:
Determines the XPath expression, JMS property, or system value that contains the txid used to unique identify transactions. This value must be continually increasing for each transaction.
{txrule}.txowner
Optionally, determines the XPath expression, JMS property, or static value that contains an arbitrary user name associated with a transaction. This value may be used to exclude certain transactions from processing
Optionally, determines the XPath expression, JMS property, or static value that contains an arbitrary name to be associated with a transaction
{txrule}.txname={xpath-expression}|${jms-property}|“value”
For example:
Optionally determines the XPath expression or JMS property that contains the operation commit timestamp, or to use the current system time.
Determines the format of the timestamp field.
Optionally overrides any seqid defined in parent transaction rules. Must be present if there is no parent transaction rule. Determines the seqid for a particular operation. Determines the XPath expression, JMS property, or system value that contains the operations seqid. Any XPath expression must end with a value accessor such as @att of text( ).
Optionally overrides any txid defined in parent transaction rules. Must be present if there is no parent transaction rule. Determines the XPath expression, JMS property, or system value that contains the txid used to unique identify transactions. This value must be continually increasing for each transaction.
{oprule}.txowner
Optionally, determines the XPath expression, JMS property, or static value that contains an arbitrary user name associated with a transaction. This value may be used to exclude certain transactions from processing
{oprule}.txname
Optionally, determines the XPath expression, JMS property, or static value that contains an arbitrary name to be associated with a transaction
Determines the XPath expression JMS property or hard-coded value that contains the schema and table name in the form SCHEMA.TABLE. Any XPath expression must end with a value accessor such as @att ot text( ). Verified to ensure the table exists in source defs.
Determines the XPath expression, JMS property or hard-coded value that contains the schema name. Any XPath expression must end with a value accessor such as @att ot text( ).
Determines the XPath expression, JMS property or hard-coded value that contains the table name. Any XPath expression must end with a value accessor such as @att ot text( ).
Determines the XPath expression, JMS property or hard-coded value that contains the optype to be validated against an optype insertval, etc. Any XPath expression must end with a value accessor such as @att of text( ).
{oprule}.optype.insertval
The value to look for to identify an operation as an Insert type operation. The default is ‘I’.
The value to look for to identify an operation as an Update type operation. The default is ‘U’.
The value to look for to identify an operation as a Delete type operation. The default is ‘D’.
Optionally determines the XPath expression or JMS property that contains the transaction indicator to be validated against beginval etc. If not defined all operations within a single message will be seen to have occurred within a whole transaction. If defined, then will determine the begin, middle and end of transactions. Any XPath expression must end with a value accessor such as @att of text( ). Transactions defined in this way can span messages.
The value to look for to identify an operation as the beginning of a transaction. Defaults to ‘B’.
{oprule}.txind.beginval={value}|\x{hex-value}
For example:
The value to look for to identify an operation as the middle of a transaction. Defaults to ‘M’.
The value to look for to identify an operation as the end of a transaction. Defaults to ‘E’.
The value to look for to identify an operation as a whole transaction. Defaults to ‘W’.
Determines the XPath expression or hard-coded value that contains a column name. If not specified then the column index must be specified and the column name will be resolved from that. If specified the column name will be verified against the source definitions file. Any XPath expression must end with a value accessor such as @att ot text( ).
{colrule}.index
Determines the XPath expression or hard-coded value that contains a column index. If not specified then the column name must be specified and the column index will be resolved from that. If specified the column index will be verified against the source definitions file. Any XPath expression must end with a value accessor such as @att ot text( ).
{colrule}.value
Determines the XPath expression or hard-coded value that contains a column value. Any XPath expression must end with a value accessor such as @att ot text( ). If the XPath expression fails to return any data because a node or attribute does not exist, the column value will be deemed as null. To differentiate between null and missing values (for updates) the isnull and is missing properties should be set. The value returned is used for delete before values, and update/insert after values.
Optionally determines the XPath expression used to discover if a column value is null. The XPath expression must end with a value accessor such as @att ot text( ). If the XPath expression returns any value, then the column value is null.
Optionally determines the XPath expression used to discover if a column value is missing. The XPath expression must end with a value accessor such as @att ot text( ). If the XPath expression returns any value, then the column value is missing.
Optionally override {colrule}.value to specifically say how to obtain before values used for updates or deletes. Has the same format as {colrule}.value. For example:
Optionally override {colrule}.isnull to specifically say how to determine if a before value is null for updates or deletes. Has the same format as {colrule}.isnull. For example:
Optionally override {colrule}.ismissing to specifically say how to determine if a before value is missing for updates or deletes. Has the same format as {colrule}.ismissing. For example:
As per {colrule}.before.value except for after values.
{colrule}.after.isnull
As per {colrule}.before.isnull except for after values.
{colrule}.after.ismissing
As per {colrule}.before.ismissing except for after values.
The present invention may be conveniently implemented using one or more conventional general purpose or specialized digital computer, computing device, machine, or microprocessor, including one or more processors, memory and/or computer readable storage media programmed according to the teachings of the present disclosure. Appropriate software coding can readily be prepared by skilled programmers based on the teachings of the present disclosure, as will be apparent to those skilled in the software art.
In some embodiments, the present invention includes a computer program product which is a non-transitory storage medium or computer readable medium (media) having instructions stored thereon/in which can be used to program a computer to perform any of the processes of the present invention. The storage medium can include, but is not limited to, any type of disk including floppy disks, optical discs, DVD, CD-ROMs, microdrive, and magneto-optical disks, ROMs, RAMs, EPROMs, EEPROMs, DRAMs, VRAMs, flash memory devices, magnetic or optical cards, nanosystems (including molecular memory ICs), or any type of media or device suitable for storing instructions and/or data.
The foregoing description of the present invention has been provided for the purposes of illustration and description. It is not intended to be exhaustive or to limit the invention to the precise forms disclosed. Many modifications and variations will be apparent to the practitioner skilled in the art. The embodiments were chosen and described in order to best explain the principles of the invention and its practical application, thereby enabling others skilled in the art to understand the invention for various embodiments and with various modifications that are suited to the particular use contemplated. It is intended that the scope of the invention be defined by the following claims and their equivalence.
This application claims the benefit of priority to U.S. Provisional Patent Application No. 61/367,323, titled “SYSTEM AND METHOD FOR CONVERSION OF JMS MESSAGE DATA INTO DATABASE TRANSACTIONS FOR APPLICATION TO MULTIPLE HETEROGENEOUS DATABASES”, filed Jul. 23, 2010; which application is herein incorporated by reference.
| Number | Date | Country | |
|---|---|---|---|
| 61367323 | Jul 2010 | US |
