Forum Stats

  • 3,751,804 Users
  • 2,250,415 Discussions
  • 7,867,596 Comments

Discussions

Multithreading in Java and opening multi DB connections

SachinP-Oracle
SachinP-Oracle Member Posts: 17 Employee
edited May 17, 2021 8:22AM in Java Programming

================= This For loop in MAIN class which will create 30 session =========================

import java.io.*;

import oracle.jdbc.pool.OracleOCIConnectionPool;

import java.util.Properties;

import java.sql.*;

import java.time.LocalDate;

import java.time.LocalTime;


public class InsertMain {


public static void main(String[] args) throws SQLException {

// TODO Auto-generated method stub

if (args.length < 1) {

System.out.println("Usage: Java LOBTest <parameter file>");

return;

}

try {

Parameters.parseFile(args[0]);

} catch (IOException e) {

System.out.println("Error parsing parameter file " + args[0]);

return;

}


Connection[] conn = new Connection[Parameters.p_sessions];

System.out.print("Register driver ... ");

try {

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

} catch (SQLException e) {

System.out.println("Failed in registering Driver.");

return;

}

System.out.println("Succeed in registering the Driver.");


try {


Properties poolConfig = new Properties();

poolConfig.put(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "70");

poolConfig.put(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "400");

poolConfig.put(OracleOCIConnectionPool.CONNPOOL_INCREMENT, "2");

OracleOCIConnectionPool cpool = new OracleOCIConnectionPool(Parameters.p_username, Parameters.p_password,

Parameters.p_connect_string, poolConfig);


System.out.println("START Date-Time :" + LocalDate.now() + " " + LocalTime.now());

for (int i = 0; i < 30; i++) {

System.out.println("inside for loop");

conn[i] = cpool.getConnection(Parameters.p_username, Parameters.p_password);

ThreadPLSQL thread = new ThreadPLSQL(Parameters.p_imgtest02_destable, conn[i]);

thread.start();

System.out.println("inside for loop after start");

try { // starting workload thread 10 seconds=10000 

Thread.currentThread().sleep(200);

} catch (InterruptedException e) {

}

}

System.out.println("All Threads generated");

} catch (Exception ex) {

System.out.println("Connection String failed");

System.out.println("Error: " + ex);

ex.printStackTrace();

return;

} finally {

System.out.println("Closing all connections");

for (int i = 0; i < Parameters.p_sessions; ++i)

    if (conn[i] != null)

    conn[i].close ();

}

}

}


======================= Class ThreadPLSQL ==================================

//p_connect_string = jdbc:oracle:oci:@testdb

/* ThreadClassInsert:

 * */


import java.util.*;

import java.io.*;

import java.sql.*;


public class ThreadPLSQL extends Thread {


int p_number;

String nm = "test";

String tName;

String[] tId;

int p_basetablesize;

Random rand = new Random();

Connection connThread;


public ThreadPLSQL(String basetablename, Connection conn) {

p_number = Parameters.p_imgtest01_end;

connThread = conn;

}


public void run() {

long t_start = System.currentTimeMillis();

long t_now = t_start;

try {

CallableStatement cs = connThread.prepareCall("{? = call Thunder.Fn_Schemacreation(?,?)}");


// while (t_now - t_start < Parameters.p_duration * 10) {

tName = Thread.currentThread().getName();

tId = tName.split("-", 0);

System.out.println("Running thread number:"+tId[1]);

try {

// do_plsql (tId[1], connThread);


cs.registerOutParameter(1, Types.VARCHAR); // The first ?

cs.setString(2, tId[1]);

cs.setInt(3, 100000);

cs.execute();

String rvalue = cs.getNString(1);


System.out.println("The retrun value for Thread No: " + tId[1] + " is:" + rvalue);

cs.close();

connThread.close ();

} catch (SQLException e) {

e.printStackTrace();

} finally {

}


try {

Thread.currentThread().sleep(Parameters.p_thinktime);

} catch (InterruptedException e) {

}

t_now = System.currentTimeMillis();

// } // while loop ends


} catch (SQLException e) {}

}

}




The above code should open 30 connections to DB and each connection will call PLSQL package Thunder.Fn_Schemacreation. But when I am executing the code only 20-25 connections get opened and rest are throwing below exception. Shall I need to increase the connection pool or some other parameter which I am missing?


java.sql.SQLException: ORA-01109: database not open


at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:1256)

at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:1144)

at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:755)

at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:1032)

at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:84)

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:681)

at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:602)

at java.sql.DriverManager.getConnection(DriverManager.java:664)

at java.sql.DriverManager.getConnection(DriverManager.java:247)

at DBConnection.open(DBConnection.java:67)

at DBConnection.conn(DBConnection.java:44)

at ThreadPLSQL.run(ThreadPLSQL.java:35)

Failed.

Exception in thread "Thread-27" java.lang.NullPointerException

at ThreadPLSQL.run(ThreadPLSQL.java:35)

Comments