The access data for the SQL database system is specified in an XML file. The name of this XML file is transferred to the command line tool as an argument.
The format of the XML file is specified by the following DTD:
XML element |
Description |
Example |
jdbcconf |
JDBC system configuration specifying the database systems and database connections to be used |
- |
databasesettings |
General settings for the database system used |
- |
name |
Unique name of the general database system settings |
setting_oracle |
dbtype |
Database type used; valid values: ORACLE | DB2 | SQLSERVER | OTHER | ADABAS |
ORACLE |
driverclass |
Driver class to be used for the database system |
oracle.jdbc.driverOracleDriver |
maxconditionlength |
The specified figure limits the length of the WHERE component of an SQL query |
1000 |
fetchsize |
Number of data records transferred simultaneously from the DB server to the Java client. For most database systems, the default value is 10. |
1000 |
databaseconnection |
Information on the database connection |
- |
name |
Unique name of the database connection |
oracle_connection1 |
dbsettings |
Unique name of the general database system settings |
setting_oracle |
url |
URL of the database connection |
See file extract below |
user |
Name of the database user |
ppmoracle |
password |
Password of the database user |
ppmoracle |
encryptpw |
Encrypted password as a combination of figures after evaluation of the <password> element |
62 -62 -56 |
lastreaddate |
Date from which data is extracted. Not applicable when using a data source, format: yyyymmdd |
20050131 |
lastreadtime |
Time from which data is extracted. Not applicable when using a data source, format: hhmmss |
152917 |
language |
Language of text fields extracted |
EN |
The database type can be one of the preset values for Oracle, IBM DB2, MS SQL Server, MS Access, or ADABAS database systems. The OTHER value should be used if you want to extract data from a database of a different type than the four mentioned above.
The database type ADABAS is provided for use with Adabas Process Extractor. For a detailed description of Adabas Process Extractor, please refer to the documentation Process Intelligence for Natural Applications How-To Guide.
The maxconditionlength parameter enables you to specify a figure that limits the length of the WHERE component of an SQL query to the specified value. This is necessary to comply with any length restrictions in the different database systems. If the SQL query is longer than the specified maximum length, it is split into several smaller queries that are executed separately.
The fetchsize parameter enables you to specify how many lines of data will be simultaneously transferred from the database to the JDBC extractor per read operation.
The password of the database user must be specified in unencrypted form and plain text in the password XML element. After evaluation during the following extraction, the password is written back to the JDBC system configuration in encrypted form in the encryptpw XML element. The password entry is then deleted. At any time, the system configuration may only include one of the two entries.
To change an existing password, add a new password entry to the current configuration file and delete the encryptpw entry. The new password is encrypted as part of the next extraction.
The language parameter is optional. If you specify a value for it, this is compared with the field name from the table configuration (langfieldname) (see chapter JDBC table configuration). For example, if langfieldname="LANG" is specified in a table and <language>EN</language> is specified in the JDBC system configuration, only those data records for which LANG has the value EN will be extracted.
The following file extract shows an example JDBC system configuration:
<jdbcconf>
<databasesettings name="setting_sqls" dbtype="SQLSERVER">
<driverclass>com.microsoft.jdbc.sqlserver.SQLServerDriver
</driverclass>
<maxconditionlength>1000</maxconditionlength>
<fetchsize>1000</fetchsize>
</databasesettings>
<databasesettings name="setting_oracle" dbtype="ORACLE">
<driverclass>oracle.jdbc.driver.OracleDriver</driverclass>
<maxconditionlength>1000</maxconditionlength>
<fetchsize>1000</fetchsize>
</databasesettings>
<databaseconnection name="sqls_connection">
<dbsettings>setting_sqls</dbsettings>
<url>jdbc:microsoft:sqlserver:
//PC3:1433;SelectMethod=Cursor;
DatabaseName=ppmdb
</url>
<user>ppmuser</user>
<password>ppmuser</password>
<lastreaddate>20050228</lastreaddate>
<lastreadtime>000000</lastreadtime>
<language />
</databaseconnection>
<databaseconnection name="oracle_connection1">
<dbsettings>setting_oracle</dbsettings>
<url>jdbc:oracle:thin:@pcppm:1521:orappm_test
</url>
<user>ppmoracle</user>
<password>ppmoracle</password>
<lastreaddate>20051231</lastreaddate>
<lastreadtime>235959</lastreadtime>
</databaseconnection>
<databaseconnection name="oracle_connection2">
<dbsettings>setting_oracle</dbsettings>
<url>jdbc:oracle:thin:@pcppm:1521:orappm_produktiv</url>
<user>ppmoracle2</user>
<encryptpw>48 -62 -76 -60 -108 -57 -92</encryptpw>
<lastreaddate>20050228</lastreaddate>
<lastreadtime>000000</lastreadtime>
</databaseconnection>
</jdbcconf>
For PPM Process Extractor JDBC-2-PPM to be able to use the configuration data to connect to the database, you need to copy the JDBC drivers (JAR and/or ZIP files) to the following directory of your installation.
<PPM installation directory>\ppmmashzone\server\bin\work\data_ppm\drivers