You can use the Java class com.idsscheer.ppm.xmlextractortools.extractor.jdbc2ppm.ZSortWithTimestamp_jdbc2ppm to extract data by time stamp (e.g., Oracle data type TIMESTAMP) in a sorted manner. The first or last record extracted is written to an event. The class is specified in the XML element table of the XML attribute classtouse. The two required parameters are specified in the corresponding XML elements:
The following example shows an extract from such a table configuration.
<table ... classtouse="com.idsscheer.ppm.xmlextractortools.extractor.jdbc2ppm.ZSortWithTimestamp_jdbc2ppm">
<parameter name="SORTCRITERION">
<value>...</value>
</parameter>
<parameter name="USE">
<value>...</value>
</parameter>
... conditions ...
... pkfields ...
... fieldstoread ...
</table>
The specified sort criterion is automatically added to the event output.
Example
Fields of the last process step status on each process step are to be extracted from the table DBO.WMPROCESSSTEP (status history of process steps). Depending on the value of the field AUDITTIMESTAMP (Oracle data type TIMESTAMP), the latest entry (MAX) is to be transferred.
<configuration name="AUFK_JCDS_AUFK">
...
<table name="WMPROCESSSTEP_END" tablename="DBO.WMPROCESSSTEP" classtouse="com.idsscheer.ppm.xmlextractortools.extractor.jdbc2ppm.ZSortWithTimestamp_jdbc2ppm">
<parameter name="USE">
<value>MAX</value>
</parameter>
<parameter name="SORTCRITERION">
<value>AUDITTIMESTAMP</value>
</parameter>
<pkfield name="INSTANCEID" fktablename="WMPROCESSSTEP" fkfieldname="INSTANCEID" />
<pkfield name="INSTANCEITERATION" fktablename="WMPROCESSSTEP" fkfieldname="INSTANCEITERATION" />
<pkfield name="STEPID" fktablename="WMPROCESSSTEP" fkfieldname="STEPID" />
<pkfield name="STEPITERATION" fktablename="WMPROCESSSTEP" fkfieldname="STEPITERATION" />
<fieldtoread name="AUDITTIMESTAMP" />
<fieldtoread name="INSERTTIMESTAMP" />
<fieldtoread name="STATUS" />
</table>
...
</configuration>
An event extracted with this configuration could look like this:
<event>
...
<attribute type="WMPROCESSSTEP_END-AUDITTIMESTAMP">24.08.2010 09:51:13.477</attribute>
<attribute type="WMPROCESSSTEP_END-INSERTTIMESTAMP">24.08.2010 09:51:13.550</attribute>
<attribute type="WMPROCESSSTEP_END-STATUS">2</attribute>
...
</event>
Extract data incl. milliseconds
By default, time stamps are extracted and sorted to the second. If several records with identical time stamp value exist one of them is randomly selected and used for value determination.
If time stamps are saved to the millisecond in the database, they can be extracted and sorted to the millisecond. To implement this, you can specify the value MILLISECOND in the precisionoftime attribute of the XML element databasesettings of the JDBC configuration file. The default value is SECOND.
With the value SECOND, all time stamps or times are extracted in the format MM/dd/yyyy HH:mm:ss or HH:mm:ss and written to the event file. The data is sorted to the second according to the time stamp.
With the value MILLISECOND, time stamps or times are extracted in the format MM/dd/yyyy HH:mm:ss.SSS or HH:mm:ss.SSS and written to the event file. The data is sorted to the millisecond according to the time stamp.
The default value is SECOND, i.e., times are extracted to the second after upgrading an existing client configuration.
The following example shows an extract from the XML JDBC configuration file:
<jdbcconf>
<databasesettings name="jdbc_oracle" dbtype="ORACLE" precisionoftime="MILLISECOND">
<driverclass>oracle.jdbc.driver.OracleDriver</driverclass>
<maxconditionlength>2000</maxconditionlength>
<fetchsize>1000</fetchsize>
</databasesettings>
<databaseconnection name="jdbc_oracle10_connection">
<dbsettings>jdbc_oracle</dbsettings>
<url>...</url>
<user>...</user>
...
</databaseconnection>
</jdbcconf>