Skip to Content

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.

Adapter List.png

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.

Config.png

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.

Connection.png

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.

Pushdown.pngPushdown2.png


Once you have applied the configuration settings you can start the adapter. This is now ready for use in Data Services Designer.

/wp-content/uploads/2015/03/start_658076.png

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.

/wp-content/uploads/2015/03/datastore_658077.png

You can now browse the metadata as you would in any other datastore type and import the tables you require.

/wp-content/uploads/2015/03/metadata_658078.png/wp-content/uploads/2015/03/import_658079.png

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.

/wp-content/uploads/2015/03/dataflow_658080.png

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.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Arun Sasi

    Hi David,

    Excellent Article!!

    David Pugh

    I tried to follow your steps to create a JDBCAdapter to connect to SQL Server 2008 database but it gives an error

    Adapter ‘JDBCAdapter’ is not started – cannot obtain statistics.”


    Our job server is on unix and we are currently on setting up our dev server on DS 4.2 SP5.


    I have added the sqljdbc4.jar under the classpath section (/appl/sap/BODS4/dataservices/ext/lib)


    JDBC Driver Class Name: com.microsoft.sqlserver.jdbc.SQLServerDriver

    JDBC Driver URL: jdbc:sqlserver://servername:1433;databaseName=db_name

    Please help!!

    Regards

    Arun Sasi

    (0) 

Leave a Reply