SAP Lumira - Using Query with SQL

SAP Lumira allows you to create dataset by using SQL query for target data source, manually. You can specify the source tables, columns, procedures, and functions to acquire data in SAP Lumira.

You can use JDBC drivers for typical databases like Oracle, SQL Server, IBM DB2, Sybase, Teradata.

Database JDBC Driver Name
Oracle ojdbc14.jar
Microsoft SQL Server sqljdbc4.jar
Teradata terajdbc4.jar and tdgssconfig.jar
Sybase jconn4.jar

db2jcc.jar or db2cc.jar and

db2jcc_license_cu.jar for versions earlier than 9.5

IBM Netezza nzjdbc.jar

Connecting to a Query with SQL Data Source

To connect to a database using SQL query to acquire data, you should have a good understanding of your database and good command over SQL.

You should have correct database drivers installed for your middleware, which allow the client application to connect to middleware and to database.

JDBC drivers have to be installed for database middleware for using SQL query. The access driver is the .jar file. You can download this file from the vendor site and copy to the driver folder in application path.

Given below are the steps to connect to a query with SQL Data Source.

Step 1 − To use SQL query to create dataset, go to File → New.

Select the option Query with SQL to download a dataset and click Next.

Connecting to Query Step1

Step 2 − Select SQL query. All queries in green represent the drivers, which are installed properly for middleware.

Step 3 − Select database middleware for target database and click Next.

Connecting to Query Step3

Step 4 − Enter the Login Credentials, like host name, Instance number, User name and Password and click Connect as shown below.

Connecting to Query Step4

Step 5 − In the left pane, it will show you all the Schemas and tables in the target database.

Select the target table and add to the Query panel. You can click the Preview option to see the data preview.

Connecting to Query Step5

Step 6 − You can use the SELECT statement only in the SQL editor to acquire data from database tables. Click Create to add the dataset to the Prepare tab. You can also use “Select All” option, if you want to use all the columns or uncheck the box to remove any columns to dataset. Click Create.

Connecting to Query Step6

Specifying Query with SQL connection properties

Using SQL Query option also allows you to create your own data provider, by manually entering the SQL for a target data source to acquire table data.

While using Query with SQL, connection information for the target database should be entered and various connection properties can be used.

  • User Name − To connect with target database

  • Password − To connect with target database

  • Server and Port name − of the target database

  • Database − Name of the database

You can select Advance options like −

  • Connection Pool Mode − To keep connection active

  • Pool timeout − Time duration to keep connection active in minutes.

  • Array Fetch Size − to determine number of rows to fetch from target database.

  • Array Bind Size − Larger bind array, more number of rows will be fetched.

  • Login Timeout − Time before a connection attempts a timeout.

JDBC Driver Properties

JDBC Driver Properties

These are various connection properties, which can be defined while using query with SQL option in creating Lumira dataset.