Pass parameters in Oracle JDBC URL to improve performance

 In the latest Oracle JDBC driver version 19.3 and above, it is now possible to include some connection properties as part of the JDBC URL syntax. This is a great advantage as it enables us to connect to any application where JDBC URL is configurable, and pass properties parameters to improve performance, pass security parameters, and pass Transparent Application Continuity parameters.

One of the most common changes we make to the JDBC default connection is the Fetchsize, Buffer size, or ArraySize to get the query result set. With 19c, we can now pass these properties in the JDBC URL. The default fetch size is 10, which is tuned for OLTP transactions. However, for data warehouse applications and ETLs, retrieving large amounts of data across the network using the default size can be slow. To change the default fetch size, we can set the connection property "defaultRowPrefetch" in the JDBC URL.

For example, the following URL syntax with properties can be used to set the fetch size to 1000: jdbc:oracle:thin:@(description= (address=(protocol=tcps)(port=1521)(host=example1.com)) (connect_data=(service_name=example2.com)))?oracle.jdbc.defaultRowPrefetch=1000

In Oracle19c, easy connect syntax also supports adding properties: jdbc:oracle:thin:@hostname:1521/pdb1?oracle.jdbc.defaultRowPrefetch=1000&oracle.jdbc.defaultExecuteBatch=1000

If you have more than one property to set, the syntax is to start with a "?" and any additional parameters with a "&" sign. For more detailed information, check out this document.

This is very useful when we migrate our databases to the cloud. The network latency could impact performance unless we change these settings. However, in many cases, we cannot change the code or recompile the application. To tune the application for network latency without changing the code is a big advantage. Most often, the JDBC URL is the only configurable parameter. Hence this is a great tuning tip.

Prior to 12.2, the only way to set this property used to be from within the Java application. Within a Java program, you can use setFetchSize as a connection method or at a statement level. From within the Java application, the usual ways to set the row fetch size are:

• Via java.sql.Connection vendor implementation class custom method (e.g., OracleConnection.setDefaultRowPrefetch)
• Via java.sql.Statement.setFetchSize(int): gives a hint to the driver as to the row fetch size for all ResultSets obtained from this Statement. This method is inherited by PreparedStatement and CallableStatement. Most JDBC drivers support it.
• Via java.sql.ResultSet.setFetchSize(int): gives a hint to the driver as to the row fetch size for all this ResultSet.

In conclusion, this feature is going to make the JDBC URL very flexible to connect to Oracle databases. It can now include tuning parameters, TNS_ADMIN, wallet information, and many other functionalities like HA and Transparent Application Continuity.

Connect to Oracle Autonomous database from jdbc using 19c syntax with wallet

Connecting to an Oracle Autonomous Database using a wallet and JDBC drivers is a secure and efficient way to access data stored in the cloud. This guide will walk you through the necessary steps to establish a connection between your Java application and an Oracle Autonomous Database instance.

Step 1: Download and Install Oracle JDBC Drivers

The first step is to download the Oracle JDBC drivers for your operating system. You can download the latest version of the Oracle JDBC driver from the Oracle website.

After downloading the driver, extract the zip file to a directory of your choice.

Step 2: Create an Autonomous Database Instance and Download the Wallet

To create an Autonomous Database instance, log in to your Oracle Cloud Infrastructure Console and navigate to the Autonomous Transaction Processing page.

Once you have created an Autonomous Database instance, you need to download the database wallet. The wallet contains the security credentials necessary to establish a secure connection between your application and the database.

To download the wallet, click the Download button in the Wallet section of the Autonomous Database Details page. Select the appropriate wallet type for your operating system.

Step 3: Extract the Wallet

After downloading the wallet, extract the contents to a directory of your choice. The contents of the wallet should include several files, including a cwallet.sso file, a ewallet.p12 file, a tnsnames.ora and a sqlnet.ora file.

Step 4: Configure Your Environment

Before you can establish a connection to the Autonomous Database, you need to set up your environment. This involves configuring the sqlnet.ora file to specify the directory where the wallet file is extracted

The sqlnet.ora file should be modified to contain the location where the wallet is located. The default "~/network/admin" needs to be replaced

sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/root/Downloads/wallet"))) 

Replace <wallet-directory> with the path to the directory containing the extracted wallet files.

Step 5: Configure Your Java Application

To establish a connection to the Autonomous Database from your Java application, you need to configure the JDBC driver to use the wallet.

To be absolutely sure, you can put the oraclepki.jar, ordt_core.jar, osdt_cert.jar and ojdbc8.jar in the java CLASSPATH

export CLASSPATH=/ojdbc8-full/oraclepki.jar:/ojdbc8-full/osdt_cert.jar:/ojdbc8-full/osdt_core.jar:/ojdbc8-full/ojdbc8.jar:$CLASSPATH

Here is an example of how to establish a connection to the Autonomous Database using the JDBC driver and the wallet:

import java.security.Security;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;
import java.sql.DatabaseMetaData;
public class expressTest2 {
  public static void main(String[] args) throws SQLException{
    Properties props = new Properties();
    props.setProperty("user", "admin");
    props.setProperty("password", "Saturday_123");
    props.setProperty("oracle.net.ssl_version", "1.2");
    //props.setProperty("oracle.net.wallet_location", "file:/u04/ssh_express/cwallet.sso");
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
    //     TODO Auto-generated catch block
               e.printStackTrace();
                     }
                         Security.addProvider(new oracle.security.pki.OraclePKIProvider());
                            // This url syntax is from ojdbc8.jar from Oracle 18c and above.
                            String url3 = "jdbc:oracle:thin:@east_low?TNS_ADMIN=/root/Downloads/wallet";
                            Connection con = DriverManager.getConnection(url3, props);
                        DatabaseMetaData dmeta = con.getMetaData();
                        ResultSet rs = dmeta.getSchemas("%", "%");
                        while (rs.next()) {
                                System.out.println("SCHEMA = " + rs.getString(1));
                        }

          }
       }

Replace <host> with the hostname of your Autonomous Database instance, and <service_name> with the name of your database service.

Step 6: Compile and run

Eg. javac expressTest2.java

java expressTest2

Conclusion

By following these steps, you can establish a secure and efficient connection to an Oracle Autonomous Database instance using a wallet and JDBC drivers. This method provides a high level of security and reliability, allowing you to access your data in the cloud with confidence.

ETL tool (altryx) integration to BULK LOAD using AutoRest API in ADB.

Autonomous Database is a cloud-based database service that offers high availability, security, and scalability. AutoREST is a feature of Autonomous Database that enables users to generate REST APIs automatically for their database tables, views, and procedures. AutoREST also supports OAuth2 authentication, which allows users to secure their API endpoints and access control. In this blog, we will discuss how to enable AutoREST in Autonomous Database with OAuth2 and call the BULK LOAD API.

Step 1: Create a Table in the Autonomous Database

From the Autonomous database console, click on "Database Actions" and then "SQL"

In the sql worksheet , you can create a sample table where we need to load data through CSV files/

Eg. create table customers as select * from ssb.customer  where 1=2;

Step 2: Create a Table in the Autonomous Database

The first step is to enable AutoREST in Autonomous Database. This can be done by logging in to the Oracle Cloud Console, selecting the Autonomous Database instance, and clicking on the AutoREST tab. From here, you can enable AutoREST and select the database objects that you want to expose through REST APIs.

In this example, i have enable "OAuth2 authorization as well "

Step 2: Set up OAuth2 authentication .

For OAuth2 to work, we have to create a DB specific OAuth Client_id and associate the auto generated role and privilege to this Client_id to access the api.

 Do this by navigating to the Database Actions menu in the upper left of the page. Choose REST in the Development list.

 select the Security Tab on the top of the page and then select OAuth Clients.

OAuth2 is an industry-standard protocol for secure API authentication. To set up OAuth2 authentication in Autonomous Database, you need to create an OAuth2 client in the Autonomous Database instance. This can be done by following the steps below:

Enter client name, and support URL and support Email info. Note that these values are mandatory, but the info is not validated.

Next Click on the Privilege tab and select the privilege for the table created..

Accessess the Autorest API, we need to obtain a token to pass to the secured REST service once its enabled. To obtain this token, we can click the pop out menu icon  on our OAuth tile and select Get Bearer Token

The OAuth Token modal will provide the token text in the Current Token field. You can use the copy icon  to copy this token text. Save it to a text document or notes application as you'll need it when calling the secured REST service. The modal will also provide us with a cURL command to obtain a token should we need to include it in our applications.

Take note of the client_id, client_password and the token URL.

Next, get the URL to bulk load data through AutoRest. You can click on the Database Actiontions --> SQL and rigth clith the table where you enabled the Autorest and click on the Curl

.

Here note the URL for BLUKLOAD.

Now, you have all the components to put a CSV file through a POST REST call.

I configured the information cathered in the python program  which coluld push data in multiple parallel threads into ADB.

Download the python program and replace the client_id, password and tokan url and table url in the python program.

https://github.com/vijaybalebail/AutoRestBulkLOAD/blob/main/bulkloadapi.py

you can run the perl script   and pass the file name you need to load as a parameter.

Enabling AutoREST in Autonomous Database with OAuth2 authentication and calling the BULK LOAD API is a simple process that can be done in a few easy steps. This allows developers to easily integrate their applications with the database, making it easier to manage and analyze data. With these steps, you can take full advantage of the capabilities of Autonomous Database and make the most of your data.