JDBC system configuration

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:

s_jdbc_conf_dtd_abb_5

XML element
or attribute

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.
Recommended value: 1000

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