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.

Retrieve few minutes older records from SQL in Informix

You can use following SQL to retrieve records older than 15 minutes in Informix. init_time is in datetime format.  

select * from table_name where init_time > (current - 15 units minute)

Search files in sub directories in Linux

Following command can use to list files staring with 1234 in all sub directories. 

ls –R | grep 1234*

List absolute paths for files in Linux

Following Linux command can be used to list absolute file paths.

ls -d /apps/*

Selenium testing in JAVA

Following JAVA code can be used to test web application with login page, filtering data and submit form.

You have to install web browser driver. I used chrome driver.

pom.xml

<dependency>
    <groupId>org.seleniumhq.selenium</groupId>
    <artifactId>selenium-java</artifactId>
    <version>3.141.59</version>
</dependency>
 
JAVA code

System.setProperty("webdriver.chrome.driver","./driver/chromedriver.exe");
WebDriver driver = new ChromeDriver();

String baseUrl = "https://testwebsite/login";
driver.get(baseUrl);

//maximizing window
driver.manage().window().maximize();

//get login form details
WebElement username = driver.findElement(By.name("username"));
WebElement password = driver.findElement(By.name("password"));
WebElement login = driver.findElement(By.xpath("//input[@value='Login']"));

// login to site
username.sendKeys("admin");
password.sendKeys("admin@123");
login.click();

// filtering using placeholder
WebElement fullName = driver.findElement(By.xpath("//input[@placeholder='Filter Full Name']"));
WebElement country = driver.findElement(By.xpath("//input[@placeholder='Filter Country']"));
WebElement filter = driver.findElement(By.xpath("//input[@value='Filter']"));

// filter sujith & Sri Lanka
fullName.sendKeys("Sujith");
country.sendKeys("Sri Lanka");
filter.click();

// add new record to list 
WebElement add = driver.findElement(By.xpath("//*[text()='Add New']"));
add.click();

//filter by name
WebElement frmFullName = driver.findElement(By.name("fullName"));
WebElement frmAge = driver.findElement(By.name("age"));
WebElement frmAddress = driver.findElement(By.name("address"));
//filter by placeholder
WebElement frmBirthPicker = 
driver.findElement(By.xpath("//input[@placeholder='yyyy-mm-dd']"));
WebElement frmCountry = driver.findElement(By.name("country"));
//filter by parent input with 'GIT' label
WebElement frmGIT = driver.findElement(By.xpath("//label[contains(., 'GIT')]/parent::*//input"));
WebElement frmHTML = driver.findElement(By.xpath("//label[contains(., 'HTML')]/parent::*//input"));
WebElement frmMale = driver.findElement(By.id("Male"));

frmFullName.sendKeys("Test full name");
frmAge.sendKeys("24");
frmAddress.sendKeys("Test address1");
frmBirthPicker.sendKeys("2010-10-10");
frmCountry.sendKeys("Sri Lanka");
//checking buttons
frmGIT.click();
frmHTML.click();
frmMale.click();

//submitting form  
WebElement submit = driver.findElement(By.xpath("//input[@value='Submit']"));
submit.click();
    
// driver.close();

use VisualVM to monitor JAVA application

Run the JAVA application with the following options.

java8 -jar 
-Dcom.sun.management.jmxremote 
-Dcom.sun.management.jmxremote.port=8662 
-Dcom.sun.management.jmxremote.rmi.port=8662 
-Dcom.sun.management.jmxremote.local.only=false 
-Dcom.sun.management.jmxremote.authenticate=false 
-Dcom.sun.management.jmxremote.ssl=false 
-Xms32m 
-Xmx128m 
TestVisualVM.jar

Now, connect the remote JMX session in 8662 port in VisualVM to monitor application.

When reading emails from JAVA application, it returns content body in xhtml format. Following code can be used to convert xhtml tages to html.

org.jsoup.nodes.Document document = Jsoup.parseBodyFragment(xhtml);

document.outputSettings().escapeMode(Entities.EscapeMode.xhtml);

String str = document.body().html();