Menu

Tuesday, 3 October 2023

How to Check DB latency using Datasource Test-Connection.

1. Create JdbcLatencyTest.java file and copy below code.


2. Do the changes for respective DB Driver class name and JDBC string along with Username & Password.

// ###################### JdbcLatencyTest.java File Contents ############# //

**********************************************************
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class JdbcLatencyTest {
    private static final int NUM_ITERATIONS = 10;


    public static void main(String[] args) {
        // JDBC driver class name and database URL string
        String driver = "oracle.jdbc.xa.client.OracleXADataSource";
        String url = "jdbc:oracle:thin:@123.789.123.456/SERVICENAME";


        // JDBC connection properties for Username and password
        String username = "username";
        String password = "Password";


        // Array to store latency measurements
        long[] latencyArray = new long[NUM_ITERATIONS];
        for (int i = 0; i < NUM_ITERATIONS; i++) {
            Connection conn = null;
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
            String connectionStartTime = null;
            String connectionEndTime = null;
            try {
                // Load the JDBC driver
                Class.forName(driver);
                // Start the timer for connection establishment
                long startConnectionTime = System.currentTimeMillis();
                // Capture the timestamp when the connection is created
                connectionStartTime = dateFormat.format(new Date());
                // Create the connection
                conn = DriverManager.getConnection(url, username, password);
                // Calculate the elapsed time for connection establishment
                long endConnectionTime = System.currentTimeMillis();
                long connectionLatency = endConnectionTime - startConnectionTime;
                // Capture the timestamp when the connection is closed
                connectionEndTime = dateFormat.format(new Date());
                // Store the latency value in the array
                latencyArray[i] = connectionLatency;
                // Print detailed latency information
                System.out.println("Iteration " + (i + 1) + ":");
                System.out.println("    Connection Start Time: " + connectionStartTime);
                System.out.println("    Connection End Time:   " + connectionEndTime);
                System.out.println("    Latency:               " + connectionLatency + " ms");
                // Close the connection
                conn.close();
            } catch (ClassNotFoundException e) {
                // Handle driver loading exception
                e.printStackTrace();
            } catch (SQLException e) {
                // Handle connection exception
                e.printStackTrace();
            }
        }
        // Print the overall latency statistics
        System.out.println("Connection Latency Measurements:");
        for (int i = 0; i < NUM_ITERATIONS; i++) {
            System.out.println("Iteration " + (i + 1) + ": " + latencyArray[i] + " ms");
        }
    }
}
***************************************************



FYI, JDBC Driver Class Name to connect respective DB:
  • oracle.jdbc.xa.client.OracleXADataSource   (for Oracle 19c and 12c DB , ojdbc8.jar with JDK8 complied)
  • oracle.jdbc.pool.OracleConnectionPoolDataSource  (for Oracle 11g with minimum jDK7, JDK8 complied)
  • org.postgresql.Driver (for POSTGRESQL DB , with postgresql-42.6.0.jar  )
  • com.microsoft.sqlserver.jdbc.SQLServerXADataSource (for MSSQL DB, with mssql-jdbc-12.2.0.jre8.jar)



3. Execute the below commands for compile and run.

$JAVA_HOME/java/8.0/bin/javac -cp /home/wasadmin/ojdbc8.jar JdbcLatencyTest.java

$JAVA_HOME/java/8.0/bin/java  -cp .:/home/wasadmin/ojdbc8.jar JdbcLatencyTest


################# Output #######################

[user@newhost ~]$ $JAVA_HOME/java/8.0/bin/javac -cp /home/wasadmin/ojdbc8.jar JdbcLatencyTest.java
[user@newhost ~]$ $JAVA_HOME/java/8.0/bin/java -cp .:/home/wasadmin/ojdbc8.jar JdbcLatencyTest


Iteration 1:
    Connection Start Time: 2023-10-03 06:42:24.018
    Connection End Time:   2023-10-03 06:42:30.189
    Latency:               6171 ms
Iteration 2:
    Connection Start Time: 2023-10-03 06:42:30.212
    Connection End Time:   2023-10-03 06:42:30.537
    Latency:               325 ms
Iteration 3:
    Connection Start Time: 2023-10-03 06:42:30.549
    Connection End Time:   2023-10-03 06:42:30.842
    Latency:               293 ms
Iteration 4:
    Connection Start Time: 2023-10-03 06:42:30.854
    Connection End Time:   2023-10-03 06:42:31.150
    Latency:               296 ms
Iteration 5:
    Connection Start Time: 2023-10-03 06:42:31.162
    Connection End Time:   2023-10-03 06:42:31.446
    Latency:               284 ms
Iteration 6:
    Connection Start Time: 2023-10-03 06:42:31.458
    Connection End Time:   2023-10-03 06:42:31.694
    Latency:               236 ms
Iteration 7:
    Connection Start Time: 2023-10-03 06:42:31.708
    Connection End Time:   2023-10-03 06:42:31.946
    Latency:               238 ms
Iteration 8:
    Connection Start Time: 2023-10-03 06:42:31.958
    Connection End Time:   2023-10-03 06:42:32.215
    Latency:               257 ms
Iteration 9:
    Connection Start Time: 2023-10-03 06:42:32.226
    Connection End Time:   2023-10-03 06:42:32.459
    Latency:               233 ms
Iteration 10:
    Connection Start Time: 2023-10-03 06:42:32.472
    Connection End Time:   2023-10-03 06:42:32.729
    Latency:               257 ms
Connection Latency Measurements:
Iteration 1: 6171 ms
Iteration 2: 325 ms
Iteration 3: 293 ms
Iteration 4: 296 ms
Iteration 5: 284 ms
Iteration 6: 236 ms
Iteration 7: 238 ms
Iteration 8: 257 ms
Iteration 9: 233 ms
Iteration 10: 257 ms
[user@newhost ~]$

Thursday, 21 September 2023

IBM WebSphere 9.0.0.0 and IHS (IBM HTTP SERVER) Installation with Fixpack using Command Line (non-root) user

###### IM (Installation Manager) installation version 1.8.9.6 or Greater than that (non-root) user.

/app/BIN/IM/userinstc -acceptLicense  -installationDirectory /app/IBM/InstallationManager  -record /app/IBM/InstallationManager/install.xml  -dataLocation /app/IBM/IMLogs -showProgress



*******************************************************************************************

###### Check listAvailablePackages 

/app/IBM/InstallationManager/eclipse/tools/imcl listAvailablePackages -repositories /app/BIN/WAS9/repository.config



###### WAS installation version 9.0.0.0 and SDK installation.

/app/IBM/InstallationManager/eclipse/tools/imcl install com.ibm.websphere.ND.v90_9.0.0.20160526_1854 com.ibm.java.jdk.v8_8.0.6007.20200324_1954 -repositories /app/BIN/WAS9/repository.config,/app/BIN/SDK8/repository.config -installationDirectory /app/IBM/WebSphere/AppServer -sharedResourcesDirectory /app/IBM/IMShared -acceptLicense -showProgress



###### WAS FP installation version 9.0.5.3 or Greater than that.

/app/IBM/InstallationManager/eclipse/tools/imcl updateAll -repositories /app/BIN/FP9.0.5.3/WASFP9/repository.config -installationDirectory /app/IBM/WebSphere/AppServer -acceptLicense -showProgress



*******************************************************************************************

###### IHS (IBM HTTP SERVER) installation version 9.0.0.0 and SDK installation.

/app/IBM/InstallationManager/eclipse/tools/imcl install com.ibm.websphere.IHS.v90_9.0.0.20160526_1854 com.ibm.java.jdk.v8_8.0.6007.20200324_1954 -repositories /app/BIN/IHS9/repository.config,/app/BIN/SDK8/repository.config -installationDirectory /app/IBM/HTTPServer -sharedResourcesDirectory /app/IBM/IMShared -properties user.ihs.httpPort=8080,user.ihs.allowNonRootSilentInstall=true -acceptLicense -showProgress




###### Plugin installation version 9.0.0.0 and SDK installation.

/app/IBM/InstallationManager/eclipse/tools/imcl install com.ibm.websphere.PLG.v90_9.0.0.20160526_1854 com.ibm.java.jdk.v8_8.0.6007.20200324_1954 -repositories /app/BIN/PLUGIN9/repository.config,/app/BIN/SDK8/repository.config -installationDirectory /app/IBM/Plugins -sharedResourcesDirectory /app/IBM/IMShared -acceptLicense -showProgress




###### IHS (IBM HTTP SERVER) FP installation version 9.0.5.3 or Greater than that.

/app/IBM/InstallationManager/eclipse/tools/imcl updateAll -repositories /app/BIN/FP9.0.5.3/IHSPLUG/repository.config -installationDirectory /app/IBM/HTTPServer -acceptLicense -showProgress




###### PLUGIN FP installation version 9.0.5.3 or Greater than that.

/app/IBM/InstallationManager/eclipse/tools/imcl updateAll -repositories /app/BIN/FP9.0.5.3/IHSPLUG/repository.config -installationDirectory /app/IBM/Plugins -acceptLicense -showProgress

*******************************************************************************************




###### SDK FP installation version 8.0.3.0 or Greater than that.

/app/IBM/InstallationManager/eclipse/tools/imcl updateAll -repositories /app/BIN/FPSDK8.0/repository.config -installationDirectory /app/IBM/WebSphere/AppServer -acceptLicense -showProgress


Thanks :-)

Wednesday, 20 September 2023

TLS1.2 Protocol enable for IBM WebSphere with SSL Handshake Debug

 

TLS1.2 enable for IBM WebSphere with SSL Handshake Debug

>> Add below arguments on JVM java_options on NodeAgent, servers, Dmgr JVM's

-Dhttps.protocols=TLSv1.2

-Dcom.ibm.ssl.protocol=TLSv1.2

-Dcom.ibm.jsse2.overrideDefaultTLS=true

-Djdk.tls.client.protocols=TLSv1.2

-Djdk.tls.server.protocols=TLSv1.2

-Djavax.net.debug=ssl,handshake,data,trustmanager




JVM arguments, To ADD cacerts as Truststore & SSL debug

>> Add below arguments on JVM java_options on NodeAgent, servers, Dmgr JVM's

-Djavax.net.ssl.trustStore=$JAVA_HOME/java/8.0/jre/lib/security/cacerts 

-Djavax.net.ssl.trustStorePassword=changeit 

-Djavax.net.ssl.trustStoreType=jks 

-Dhttps.protocols=TLSv1.2

-Dcom.ibm.ssl.protocol=TLSv1.2

-Dcom.ibm.jsse2.overrideDefaultTLS=true

-Djdk.tls.client.protocols=TLSv1.2

-Djdk.tls.server.protocols=TLSv1.2

-Djavax.net.debug=ssl,handshake,data,trustmanager



IMPORT Certificate on cacerts file:

Command:

$JAVA_HOME/bin/keytool -import -trustcacerts -file certificate.cer -alias www.certificate.com -keystore $JAVA_HOME/java/8.0/jre/lib/security/cacerts



For LDAP SSL Handshake identificatation issue  "CWWIM4520E The 'javax.naming.CommunicationException: myldap.ibm.com:636"

>> Add below arguments on JVM java_options on NodeAgent, servers, Dmgr JVM's

-Dcom.sun.jndi.ldap.object.disableEndpointIdentification=true



Thanks :-)


How to make datasource test-connection to MSSQL DB Instance.

 Required Tool and Software: 

  • OpenJDK / Oracle JAVA 
  • dbconnect.jar
  • mssql-jdbc-12.2.0.jre8.jar



Download link for dbconnect.jar and mssql.jar 

https://drive.google.com/drive/folders/1H0UI62dmVnAQmKmPSRMrztmzrq1y49to?usp=drive_link




TEST Connection for MSSQL, with SSL and NON_SSL :

Command: With SSL and Protocol TLS1.2:  

/opt/java_1.8_64/bin/java -cp /tmp/dbConnect.jar:/tmp/mssql-jdbc-12.2.0.jre8.jar com.sid.dbConnect "jdbc:sqlserver://123.456.123.789:44332;databaseName=MSSQLDB;encrypt=true;trustServerCertificate=false;sslProtocol=TLSv1.2;" "$USERNAME" ‘$PASSWORD’  com.microsoft.sqlserver.jdbc.SQLServerXADataSource


Command: Without SSL:

/opt/java_1.8_64/bin/java -cp /tmp/dbConnect.jar:/tmp/mssql-jdbc-12.2.0.jre8.jar com.sid.dbConnect "jdbc:sqlserver://123.456.123.789:44332;databaseName=MSSQLDB" "$USERNAME" ‘$PASSWORD'  com.microsoft.sqlserver.jdbc.SQLServerXADataSource




Add below parameter on JVM arguments if you were facing SSL_protcol_Handshake issue on WebSphere Application server / WebSphere liberty.

-Dcom.ibm.jsse2.overrideDefaultTLS=true



Thanks :-)

How to make datasource test-connection to Postgresql DB Instance.

 

Required Tool and Software: 

  • OpenJDK / Oracle JAVA 
  • dbconnect.jar
  • postgresql-42.6.0.jar



Download link for dbconnect.jar and postgresql-42.6.0.jar

https://drive.google.com/drive/folders/1H0UI62dmVnAQmKmPSRMrztmzrq1y49to?usp=drive_link




TEST Connection for POSTGRESQL,

Command:

/usr/bin/java -cp /home/app/test/dbConnect.jar:/home/app/test/postgresql-42.6.0.jar com.sid.dbConnect jdbc:postgresql://123.456.123.789:1234/postgres  “$username” ‘$Password’  org.postgresql.Driver




Add below parameter on JVM arguments if you were facing SSL_protcol_Handshake issue on WebSphere Application server / WebSphere liberty.

-Dcom.ibm.jsse2.overrideDefaultTLS=true



Thanks :-)

How to make datasource test-connection to Oracle DB Instance.

 Required Tool and Software: 

  • OpenJDK / Oracle JAVA 
  • dbconnect.jar
  • ojdbc8.jar



Download link for dbconnect.jar and ojdbc8.jar and other .jar’s 

https://drive.google.com/drive/folders/1H0UI62dmVnAQmKmPSRMrztmzrq1y49to?usp=drive_link



TEST Connection for Oracle,

Command:

/usr/bin/java -cp /opt/test/dbConnect.jar:/opt/test/ojdbc8.jar com.sid.dbConnect jdbc:oracle:thin:@123.789.123.456/SERVICENAME “$username”  ‘$Password’ oracle.jdbc.driver.OracleDriver




FYI , Class Name to be used on Datasource for  application servers to connect respective Oracle DB (11g / 12c / 19c ) :

 oracle.jdbc.xa.client.OracleXADataSource   (for 19c and 12c DB , ojdbc8.jar with JDK8 complied)

oracle.jdbc.pool.OracleConnectionPoolDataSource  (for 11g with minimum jDK7, JDK8 complied)




New JDBC String : LOADBALANCER and failover for ORACLE Datasource

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=123.456.123.789)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=123.456.123.789)(PORT=1521))(FAILOVER=on)(LOAD_BALANCE=on))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MY_SERVICE_NAME)))




Add below parameter on JVM arguments if you were facing SSL_protcol_Handshake issue on WebSphere Application server / WebSphere liberty.

-Dcom.ibm.jsse2.overrideDefaultTLS=true



Thanks :-)

How to make datasource test-connection to Oracle / PostgreSql / MSSQL DB Instance

Required Tool and Software: 

  • OpenJDK / Oracle JAVA 
  • dbconnect.jar
  • ojdbc8.jar
  • postgresql-42.6.0.jar
  • mssql-jdbc-12.2.0.jre8.jar



Download link for dbconnect.jar and ojdbc8.jar and other .jar’s 

https://drive.google.com/drive/folders/1H0UI62dmVnAQmKmPSRMrztmzrq1y49to?usp=drive_link



TEST Connection for Oracle,

Command:

/usr/bin/java -cp /opt/test/dbConnect.jar:/opt/test/ojdbc8.jar com.sid.dbConnect jdbc:oracle:thin:@123.789.123.456/SERVICENAME “$username”  ‘$Password’ oracle.jdbc.driver.OracleDriver




TEST Connection for POSTGRESQL,

Command:

/usr/bin/java -cp /home/app/test/dbConnect.jar:/home/app/test/postgresql-42.6.0.jar com.sid.dbConnect jdbc:postgresql://123.456.123.789:1234/postgres  “$username” ‘$Password’  org.postgresql.Driver




TEST Connection for MSSQL, with SSL and NON_SSL :

Command: With SSL and Protocol TLS1.2:  

/opt/java_1.8_64/bin/java -cp /tmp/dbConnect.jar:/tmp/mssql-jdbc-12.2.0.jre8.jar com.sid.dbConnect "jdbc:sqlserver://123.456.123.789:44332;databaseName=MSSQLDB;encrypt=true;trustServerCertificate=false;sslProtocol=TLSv1.2;" "$USERNAME" ‘$PASSWORD’  com.microsoft.sqlserver.jdbc.SQLServerXADataSource


Command: Without SSL:

/opt/java_1.8_64/bin/java -cp /tmp/dbConnect.jar:/tmp/mssql-jdbc-12.2.0.jre8.jar com.sid.dbConnect "jdbc:sqlserver://123.456.123.789:44332;databaseName=MSSQLDB" "$USERNAME" ‘$PASSWORD'  com.microsoft.sqlserver.jdbc.SQLServerXADataSource




FYI , Class Name to be used on Datasource for  application servers to connect respective Oracle DB (11g / 12c / 19c ) :

  •  oracle.jdbc.xa.client.OracleXADataSource   (for 19c and 12c DB , ojdbc8.jar with JDK8 complied)
  • oracle.jdbc.pool.OracleConnectionPoolDataSource  (for 11g with minimum jDK7, JDK8 complied)




New JDBC String : LOADBALANCER and failover for ORACLE Datasource

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=123.456.123.789)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=123.456.123.789)(PORT=1521))(FAILOVER=on)(LOAD_BALANCE=on))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MY_SERVICE_NAME)))




Add below parameter on JVM arguments if you were facing SSL_protcol_Handshake issue on WebSphere Application server / WebSphere liberty.

-Dcom.ibm.jsse2.overrideDefaultTLS=true



Thanks :-)