List directories with sizes in Linux

du -sh *

Above command can be used to grep directory sizes in Linux. Go to required directory and execute above command.

JSCH "Algorithm negotiation fail" error

It was received the following error when creating SSH session using JSCH client. I was able to resolve this after updating JSCH version to 0.1.54.

com.jcraft.jsch.JSchException: Algorithm negotiation fail

        at com.jcraft.jsch.Session.receive_kexinit(Session.java:583)

        at com.jcraft.jsch.Session.connect(Session.java:320)

        at com.jcraft.jsch.Session.connect(Session.java:183)

<!-- https://mvnrepository.com/artifact/com.jcraft/jsch -->

<dependency>

    <groupId>com.jcraft</groupId>

    <artifactId>jsch</artifactId>

    <version>0.1.54</version>

</dependency>

Kill processes created under specific user in Linux

 You can use following commands to kill processes created under specific user in Linux.

for k in `ps -ef | grep usr01| grep ssh | awk '{print $2}'` ; do kill -9 $k ; done

Here, it is killing ssh sessions created by usr01 user.

Edit XSD files to resolve AXIS web service invalid element error

 When executing webservices using AXIS, you can receive following error due to invalid elements.

                {http://xml.apache.org/axis/}stackTrace:org.xml.sax.SAXException: Invalid element in com.mobitel.axis.AdjustmentResult - AdjustmentSerialNo

You can edit XSD file for this WSDL and add AdjustmentSerialNo element to AdjustmentResult element to resolve this. You have to download XSD files and generate webservice clients.

Retrieve few minutes older records from SQL in Oracle

You can use following SQL syntax to retrieve data in 5 minute interval in Oracle database. 

select * FROM test_table where added_time <= systimestamp - numtodsinterval(5,'MINUTE')

Above SQL returns values which are older than 5 minutes  than system time in added_time column.

Insert Timestamp to Oracle Timestamp in JAVA

 You can use following formatting to insert JAVA timestamp to Oracle Timestamp.

insert into test_table(added_time) values(TO_TIMESTAMP('2021-01-18 17:43:42.000000000', 'YYYY-MM-DD HH24:MI:SS.FF'))

Oracle Timestamp Issue

When querying Oracle Timestamp to JAVA Timestamp following error can be occurred. 

java.lang.ClassCastException: oracle.sql.TIMESTAMP cannot be cast to java.sql.Timestamp

This error can resolve using following two options.

1. Add JVM startup parameters. -Doracle.jdbc.J2EE13Compliant=true in the application

2. Add system properties in the application. System.getProperties().setProperty("oracle.jdbc.J2EE13Compliant", "true")

Reference site

https://docs.oracle.com/cd/B28359_01/java.111/b31224/datacc.htm

Informix query optimizer

 

The Informix Query Optimizer

• The query optimizer attempts to determine the most efficient way to execute a SQL statement

 • Examines every possible method to implement the query and selects the least costly method

• It is Dynamic, so when data changes the same SQL can select a better path

How Optimizes the SQL statement to determine the best access method

– Which table to read first….

– Which index to use first…

– Which filter to use first…

 

How to Turn ON Query Explain? To See the SQL Optimizer Query Plan

After executes below queries with “SET EXPLAIN” it is creates a output file called “SQL_EXPLAIN.OUT”

Method#1

                SET EXPLAIN ON -

                                Display the query plan that optimizer chooses, then execute the query.

                                Example:

                                                set explain on;

                                                select cust_name,cust_addrs from customer where cust_code=”C001”;

Method#2

                SET EXPLAIN ON FILE TO [“path/to/filename”]

                                Save the query plan into a specific file also execute the query.

Can useful when programming unit testing.

Method#3

                SET EXPLAIN ON AVOID_EXECUTE

                                Display the query plan that optimizer chooses, but do not execute the query.

Can useful when a query takes hours to run/for time consuming queries.

“SQL_EXPLAIN.OUT” file results explanation

A. Show SQL/SELECT statement.

B. Query cost

C. Number of Rows Expected to return

D. Order to access tables during execution.

E. Access Plan:

                SEQUENTIAL Scan - read all rows in sequence

                INDEX PATH - Scan 1 or more indexes

                AUTO INDEX PATH: Optimizer suggest to create an indexes.

F. Query Statistics

Shows tables map, estimated row count and estimated query costs step by step.

                This will helps to find out in which part of the query cost is high.

                So can change the query based on results output.