Apache – jMeter – Preparation – Java JRE

Background

Pardon the pun, but measuring out jMeter appropriateness for a task we have at hand.

Error

image

NotAbleToFindJavaExecutableOrVersion_20171023_0536PM

 

textual

jmeter
Not able to find Java executable or version. Please check your Java installation.
errorlevel=2
Press any key to continue . . .

 

Troubleshooting

Validation

JRE

Outline

Let us see if we have JRE installed, and if so what version.

 

Steps

  1. Launch MS Windows Control Panel
  2. If Java Applet is not present, then we do not have Java installed, and we can skip the rest of our validation steps
  3. If Java Applet is present, access the Java Applet
  4. The “Java Control Panel” window appears
  5. Access the Java Tab
  6. Click the View button
  7. The “Java Runtime Environment Settings” window appears
    • There are two tabs, User and System
      • User Tab
        • In our User tab, we noticed that we have JRE 1.7 installed
        • We also noticed that its path indicates an install path of “C:\Program Files (x86)\Java\jre7\bin\javaw.exe”
        • Having it in x86 is ominous for us, as our OS in 64-bit

 

Image

javaRuntimeEnvironmentSettings_20171023_0539PM

 

Remediation

Download and install 64-bit Java JRE if your OS is 64-bit

 

Validation

Image

javaRuntimeEnvironmentSettings_20171023_0548PM

 

Tabulated

  1. We have two versions of JRE
    • v1.8
      • Path :- C:\Program Files\java\jre1.8.0_131\bin\javaw.exe
      • Architecture :- x86_64
    • v1.7
      • C:\Program Files (x86) \ Java\jre7\bin\javaw.exe
      • Architecture :- x86

 

Explanation

It looks like we can rumble with x86_64.

 

Success

Ran jmeter.bat again and we are good!

jmeter_20171024_0903AM

DBeaver – Database Connection Configuration – Oracle – Oracle JDBC Driver

Background

Towards a couple of sample Oracle Database sample exercises that we have in mind wanted to make sure that our all purpose database client, DBeaver, is able to communicate with an Oracle Database.

 

Preparation

Oracle

Java Version

Objective

The version of JDBC Client that we need will be based on the version of JRE that is installed or will be installed on the computer.

And, so let us establish that first; that is determine the JRE Version Number.

Outline

Using Control Panel, find and launch the Java applet.

Once Java Control Panel window appears, please access the “General” tab.

Within the “General” tab, click the “About” button

Review the Version Numbers listed and exit the “About” panel, once satisfied.

Next access the “Java” tab and review the System and User settings.

Within the Java tab, we are able to enable and disable each installed JRE package.  They are listed by Version #.

Image

Image – About

controlPanel_java_about__20171013__0831PM

 

Explanation
  1. The version number is “Version 8 Update 40” ( build 1.8.0_40)
    • We have Version 8
    • Build 1.8
Image – Java Runtime Environment Settings
Image – Java Runtime Environment Settings – System

Image – Java Runtime Environment Settings – User

Image – Java Runtime Environment Settings – System

Download

Download URL

Please visit Oracle’s JDBC download web site.

The URL is Link.

And, the current version# is 12.1.0.2.

 

Image

Matrix

Jar Package Description Detail
ojdbc7.jar JDBC driver classes except classes for NLS support in Oracle Object and Collection types. NLS Support which enables Internalization are excluded
ojdbc7_g.jar Same as ojdbc7.jar except compiled with “javac -g” and contains tracing code. Tracing & Debugging support included
ojdbc7dms.jar Same as ojdbc7.jar, except that it contains instrumentation to support DMS and limited java.util.logging calls. Instrumentation & Logging Supported included
ojdbc7dms_g.jar Same as ojdbc7_g.jar except that it contains instrumentation to support DMS. Instrumentation included

 

Explanation

We do not need support for NLS, National Language, english is sufficient.

And, we do not need tracing and logging as will not be developing code and thus need to capture code related errors and instrumentation.

In short, the base package, ojdbc7.jar, is sufficient.

Download

Please download the Jar file and move it to a location where you will like to have it available to our Client, DBeaver.

Configuration

Please launch DBeaver and let us review and configure the list of JDBC Drivers.  And, also configure connections to our various Oracle Database hosts.

Outline

  1. Driver Manager
  2. Connection

 

Driver Manager

The list of Drivers currently availed is accessible through the menu items Database \ Driver Manager.

Outline

  1. Please click the menu item Database / Driver Manager
  2. In the “Driver Manager” window
    • Choose Oracle
    • Click the “Edit” button
  3. In the “Edit Driver Oracle” window
    • Tab – Libraries
      • Review the list of libraries registered
      • Add
        • If the ojdbc driver is missing
          • Please click the “Add File” button
          • Navigate to the folder where the JDBC Driver is kept
          • And, select the jdbc jar file
          • Ensure that jar file is listed
      • Driver Class
        • Click the “Find Class” button
        • In the “Driver Class” dropdown, the Driver classes are availed
          • Driver Class
            • oracle.jdbc.OracleDriver
            • oracle.jdbc.driver.OracleDriver
          • The original driver class is oracle.jdbc.driver.OracleDriver
          • And, the modern one is oracle.jdbc.OracleDriver
          • Please choose the modern one
      • Please press the OK button once you are satisfied

Images

Driver Manager – Oracle

Edit Driver “Oracle” – Before Adding Jar File

Edit Driver “Oracle” – After adding Jar file

Edit Driver “Oracle” – Choose Driver Class – Choosing

Edit Driver “Oracle” – Choose Driver Class – Chosen

 

Connection

New Connection

Please click the “Database” / “New Connection” menu item to create a new connection.

Outline

  1. In the “Select new connection type” panel
    • Please select the Oracle driver
  2. In the “Oracle Connection Settings” panel
    • Tabs
      • Tab – General
        • Group Box
          • Group Box – Connection Type
            • In the “Basic” group box
              • Host
                • Please enter the Oracle DB Server Hostname
                  • In our case localhost
              • Port
                • Please enter the Port Number for the Oracle Instance
                  • In our case 1521
              • Database
                • Please enter the database name
                  • As we are local, we clicked on the database name scroll bar and we chose XE from the discovered local instances
              • Service or SID
                • Chose SID
            • Test Connection
              • Please click the “Test Connection” button to validate your connection settings
                • If connection attempt fails, you will get an error message similar to
                  • I/O error :- The Network adapter could not establish a connection
      • Tab – Oracle Properties
        • Group Box
          • Group Box – Session Settings
            • Language
              • Please choose Default from the list of languages
            • Territory
              • Please choose Default from the list of territories
            • NLS Date Format
              • We left the “NLS Date Format” textbox empty
                • As there is no need for Internalization
          • Group Box – Content
            • Hide empty schemas
              • Please unclick the default setting of hiding empty schemas
                • Hiding empty schemas could create potential for not seeing the entire schema footprint
            • Always Show DBA Objects
              • Left unchecked as we will are not currently interested in seeing metadata information availed through the DBA schema
          • Tab – Driver Properties
            • Will not expand on this tab at this time
    • Click the Next button
  3. In the “Network” panel
    • No need for SSH Tunnel / Proxy

Image

Select new connection type

Oracle Connection Settings
Oracle Connection Settings – General – Initial

Oracle Connection Settings – General – Completed – Incorrect

Oracle Connection Settings – General – Completed – Correct

 

Create new Connection – Network

Create new Connection – Test Connection

When test connection fails…

Create new Connection – Test Connection – IO Error : The Network Adapter could not establish the connection – Image

Explanation
  1. Oracle services not started
Create new Connection – Test Connection – ORA-12514, TNS:listener does not currently know of service requested in connect descriptor – Image

 


Create new Connection – Test Connection – ORA-12514, TNS:listener does not currently know of service requested in connect descriptor – Textual

Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

Explanation

When test connection fails…

Create new Connection – Test Connection – When things are good

Create new Connection – Finish Connection Creation
Create new Connection – Finish Connection Creation – Initial

Create new Connection – Finish Connection Creation – Completed

 

Usage

Sample Query

Session Information

Query


SELECT 
         SYSDATE AS currentDate 
       , SYS_CONTEXT ('USERENV', 'SESSION_USER') AS currentUser
       , SYS_CONTEXT('USERENV','HOST') AS host       
       , SYS_CONTEXT('USERENV','TERMINAL') AS terminal
       
FROM SYS.DUAL

Output

References

  1. Oracle Docs
  2.  Oracle-Base
    • Identifying Host Names and IP Addresses
      Link

Oracle Database Client on Windows

Background

In the last couple of posts we touched on installing Oracle XE to avail a small footprint Oracle DB Server.

Later we used SQLPlus to validate connectivity and add a new ‘regular‘ user.

 

Lineage

  1. “Oracle Database 11g Express Edition” – Installation on Windows
    Link
  2. Oracle – SQLPlus – Create new User
    Link

Download Area

The current version of the Oracle Database Engine Client is Oracle Database 11g Release 2 (11.2.0.1.0).

It is available for download here.

Which App

We will be using the Oracle Client DB Connectivity layer on MS Windows x64 bit.

Unfortunately, the client tool is Microsoft’s Business Intelligence Development and that client is 32 bit, we will have to take the Oracle 32-bit Client.

Here it is….

Size wise It is 700 MB.

Install

Select Installation Type

Image

Details

  1. InstantClient ( 174 MB )

 

Specify Installation Location ( Step 2 )

Image

Before

After

Details

  1. Changed from user specific folder to generic folder

Perform Prerequisite Checks ( Step 3 )

Image

Before

After

Details

  1. Path
    • Path’s Length
      • Value
        • Expected Value :- 1023
        • Actual Value :- 1239
      • Oracle will like for the path length to be less than 1024
      • Chose to Ignore

 

Summary ( Step 4 )

Image

Details

  1. Global Settings
    • Disk Space :-  174 MB
    • Install Type :- Instant Client
    • Oracle Home Location :- D:\app\oracle\product\11.2.0\client_1
  2. Inventory Information
    • Inventory Location :-  C:\Program Files (x86)\Oracle\Inventory

 

Install Product ( Step 5 )

Image

Validation

tnsping

Let us tnsping to see if we can connect to our locally installed Oracle XE ( Oracle Express Engine ).

Command


tnsping XE

Output

tnsnames.ora

Please review and modify tnsnames.ora to add new & modify existing alias.

Commendation

Please review Tyler Chessman very generous write-up for a good, solid, write-up on connectivity installation & configuration between SQL Server and Oracle.

 

References

  1. Tyler Chessman
    • Connecting to an Oracle Database from SQL Server and Microsoft BI Tools
      Avoid common pitfalls
      Published On :- 2014-May-14th
      Link

Oracle – SQLPlus – Create new User

 

Background

Now that we have Oracle DB Express installed, let us create a new normal user.

The reason being that there are some hoops you have to jump over if you try to use the sys or sysdba system accounts.

 

SQLPLus

There is a minimalist Client bundled with Oracle XE, it is called SQLPlus.

Launch

To launch on Windows access menu and underneath “Oracle Database 11g Express Edition” \ “Run SQL Command Line“.

 

Use

Connect as SYS

SQL


SQL> connect SYS as SYSDBA

Output

Output – Connecting …

Output – Connected

 

Create User

SQL


create user msftbids identified by mylittpwd;
grant connect to msftbids;

Connect User

Please launch another SQLPlus session and attempt to connect as the user you just created.

SQL


connect msftbids

Output

“Oracle Database 11g Express Edition” – Installation on Windows

Background

A week ago today, DH called me to speak about an interoperability problem he was facing accessing data on an Oracle DB from SQL Server.

The tool he was using is Microsoft’s Business Intelligence Development ( BIDs)

We tried to debug the problem over phone and email.

Did not get far and so I ended up connecting remotely to his machine and found a workaround using Microsoft Linked Server.

The problem was vexing enough and I wanted to reproduce locally in our Lab environment.

And, so here we go downloading and installing a Light version of the Oracle DB Engine, Oracle Express.

Download

Overview

To download please visit Oracle’s Express Edition Overview page here.

The current version is Express Edition 11g Release 2.

Download

Image

Textual

  1. Oracle Database Express Edition 11g Release 2 for Windows x64
  2. Oracle Database Express Edition 11g Release 2 for Windows x32
  3. Oracle Database Express Edition 11g Release 2 for Linux x64

 

Installation

Image

Welcome

License Agreement

Choose Destination Location

Original

Revised

Explanation
  1. Changed destination folder from C:\Oraclexe to D:\Oraclexe
  2. Space Required to 631124 K ( 630 MB)

 

Specify Database Passwords

Original

Revised

Explanation
  1. Enter password for the System Account
    • Btw, the system accounts are
      • SYS
      • SYSTEM

Summary

Image

Explanation
  1. Oracle Database Listener :- 1521
  2. Oracle Services for Microsoft Transaction Server :- 2030
  3. Oracle HTTP Listener :- 8080

Complete

Image

Oracle – Top N Records

Background

This last week, Mr. P and I were talking shop as to how difficult it can be to return “Top N Records” from a Query.

In SQL Server, it is not that difficult we simply use select top N.

For more difficult scenarios where grouping is needed, we use Windowing Functions.

 

Oracle

He emailed me what he came up with.  And, I promised to see how badly I will stumble.

Now that I discovered Oracle Live SQL, I know that I don’t even have to request access to our in-house Oracle database, I can just use a free Cloud DB Playground.

Oracle Live SQL

Table & Data

As always, I need a test table and some data.

Found out that Oracle Live SQL provides two schemas, HR for small form factors.

And, Sales History for bigger data.

Metadata

Let us see which tables are availed within the HR schema.


select 
          tbl.OWNER
        , tbl.TABLE_NAME
        , tbl.TABLESPACE_NAME
        , tbl.STATUS
        , tbl.SAMPLE_SIZE
        , tbl.LAST_ANALYZED
        
from   ALL_TABLES tbl

where tbl.OWNER in ('HR')

Output

 

Sample Queries

From Googling, found some good Q/A on StackOverflow.

Outline

They directed me at the following:

  1. Fetch First
  2. Analytic Function
  3. ROWNUM

 

Query – Get all Data

From reviewing ALL_TABLES, I know that I have only 10 records in the HR.JOB_HISTORY table.  And, so it is OK to simply return all records and order by specific ranking columns.

 


select 

         tblJH.END_DATE as dateEnd
       , tbLJH.START_DATE as dateStart
       , tblJH.EMPLOYEE_ID as employeeID
       , tblJH.JOB_ID as jobID
       , tblJH.DEPARTMENT_ID as departmentID

from   HR.JOB_HISTORY tblJH

order by
      tblJH.END_DATE desc

 

Output

 

Explanation

  1. Returns 10 records

 

Query – Fetch First

Fetch_First needs an ordered inner list.
And, within the First_First clause specify number of records.

select * 
 
from 
 
    ( 
          select  
              tblJH.END_DATE as dateEnded 
            , tblJH.START_DATE as dateStarted   
            , tblJH.EMPLOYEE_ID as employeeID 
            , tblJH.JOB_ID as jobID 
            , tblJH.DEPARTMENT_ID as departmentID 
            , ROWNUM as rowNumber 
       
        from   HR.JOB_HISTORY tblJH 
 
        order by 
                tblJH.END_DATE DESC 
              , tblJH.START_DATE DESC
              , tblJH.EMPLOYEE_ID ASC
     
    ) tblA 
     
FETCH FIRST 3 ROWS ONLY

Output

 

 

Query – Use Analytic Function

The Analytic Function pathway needs an inner select and within that an inner select a computed column.

That computed column is filled out by the system.

And, it can order or rank based on partition ( grouping) or against entire dataset.


select *  
   
from 
   
    (  
          select  
              tblJH.END_DATE as dateEnded  
            , tblJH.START_DATE as dateStarted    
            , tblJH.EMPLOYEE_ID as employeeID  
            , tblJH.JOB_ID as jobID  
            , tblJH.DEPARTMENT_ID as departmentID  
            , row_number()  
                 over  
                  ( 
                       order by  
                            tblJH.END_DATE desc 
                          , tblJH.START_DATE desc 
                          , tblJH.EMPLOYEE_ID asc 
                  ) as seqNbr 
         
        from   HR.JOB_HISTORY tblJH  
   
        order by 
            tblJH.END_DATE DESC 
       
    ) tblA  
       
where seqNbr <= 3 

Output

 

Query – Use RowNum

The RowNum query is the one that is the easiest to get a bit sloppy over.

And, it seems to need two sub-selects.

The innermost query will have the Order By.

The middle will add the ranking.

And, the outermost will filter based on the ranking.

-- Use RowNum
select *
from   (
            select  
                  dateEnded
                , dateStarted
                , employeeID
                , jobID
                , departmentID
                , ROWNUM as seqNbr
            from 
            -- get ordered data
            ( 
                select  
                      tblJH.END_DATE as dateEnded 
                    , tblJH.START_DATE as dateStarted   
                    , tblJH.EMPLOYEE_ID as employeeID 
                    , tblJH.JOB_ID as jobID 
                    , tblJH.DEPARTMENT_ID as departmentID 
                from   HR.JOB_HISTORY tblJH 
                order by 
                      tblJH.END_DATE DESC 
                    , tblJH.START_DATE DESC
                    , tblJH.EMPLOYEE_ID ASC
            ) tblA
        ) tblB    
-- filter based on seqNbr     
where seqNbr <= 3
;


References

  1. Oracle-Base
    • Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)
      Link
  2. Home / Database / Oracle Database Online Documentation, 10g Release 2 (10.2) / Administration
    • Database SQL Reference
  3. Vertabelo
    • Row_Number
      • Bartłomiej Jańczak
        • Oracle ROWNUM Explained
          Link
  4. Stack Overflow
    • How do I limit the number of rows returned by an Oracle query after ordering?
      Link
    • How to use Oracle ORDER BY and ROWNUM correctly?
      Link

Oracle Live SQL

Background

Needing to test out a Table creation script before posting it online.
SQL Server is easy, but not so much Oracle.

And, so googled for free online Oracle DB playground.

Oracle Live SQL

URL

Oracle Live SQL is available here.

Here is my welcome screen.

Account

Previous Account

Tried to use an old Oracle account that I have from yester years, but no go.

New Account

Signed up for a new Account.

Got sent an email and confirmed it is mine.

 

SQL

Sample SQL

Here is my sample SQL:


declare 
           numberofTableMatches number  := 0;
           objectName VARCHAR2(60);
           sqlStatement VARCHAR2(255);
           sqlMessage_ VARCHAR(255);
           
           table_name_ VARCHAR(60);

begin

  numberofTableMatches := 0;
  objectName := 'MetaStage_Loc_Info';

  SELECT count(*) 
  into   numberofTableMatches
  FROM   USER_TABLES
  where  (
                ( table_name = objectName )
            or  ( table_name = upper(objectName) )
        )    
  ;
  
  
  
  sqlMessage_ := 'numberofTableMatches ' ||  numberofTableMatches || '';
      
  dbms_output.put_line(sqlMessage_);  
  IF (numberofTableMatches = 10) THEN
  
      sqlStatement := '
create table MetaStage_Loc_Info
(
     Computer			VARCHAR2(64)
   , SoftwareProduct	VARCHAR2(64)
   , DataStore			VARCHAR2(64)
   , DataSchema		VARCHAR2(64)
)';

      
      dbms_output.put_line('Executing SQL pasted below :');
      
      dbms_output.put_line(sqlStatement);
      
      execute immediate sqlStatement;
      
      dbms_output.put_line('Executed SQL:');

  ELSE
  
      sqlMessage_ := 'Table (' || objectName || ') exists';
      
     dbms_output.put_line(sqlMessage_);
      
  END IF ;

end;

 

Image

 

Save Session

 

 

Saved Session