SAP Data Services and JDBC
SAP Data Services, as of 4.2 SP2, now supports the use of JDBC drivers as a data source. This capability has been implemented through a new adapter type, JDBC.
To add a JDBC adapter you use the Data Services Administration Console, browse to adapter instances, choose your job server and then select Adapter Configuration.
Select JDBCAdapter. I’m using SAP ASE as a source so I have added the JDBC library files for ASE (<install Directory>jConnect-7_0\classes\jconn4.jar) to the classpath.
Then you add the class name, driver URL, username and password. Class name and URL should be contained in the documentation from the JDBC driver provider. The URL for ASE includes servername, port and database name.
The next section is used to configure what Data Services pushes down to the database. This will vary by database type and JDBC driver provider.
Once you have applied the configuration settings you can start the adapter. This is now ready for use in Data Services Designer.
In Designer create a new datastore and change the type to Adapter. Select the adapter you have created in the management console and select OK.
You can now browse the metadata as you would in any other datastore type and import the tables you require.
In this example I’ve imported 2 tables and joined them together in a data flow. Within the query transform I’ve also applied functions to 3 of the fields to test what gets pushed down. You can see from the Optimised SQL that the Upper, Year and Month functions have been pushed down.
There are a few limitations with the JDBC Adapter, which include no View Data, Stored Procedures, Lookup and Table Comparisons. Hopefully, as the use of this adapter grows, these will be addressed in future releases.