Jenkins job to run SQL code in remote DB server.

Jenkins job to run SQL code in remote DB server.

SQL code is executed (as shell file) in Oracle DB server (Jenkins Slave server)

What is Jenkins:

▪️ Jenkins is a powerful tool that allows continuous integration (CI) and continuous delivery (CD) of projects, regardless of the platform.

▪️ It is a free source that can handle any kind of build or continuous integration.

▪️ We can integrate Jenkins with a number of testing and deployment technologies.

OS platform	Jenkins Master node – Mac OS Jenkins Slave node – Linux
Database platform	Oracle DB (on Slave node) Version: Oracle 19c
CI tool used	Jenkins (continuous Integration Tool) URL:  http://localhost:8080/
Base Script	Shell script (from remote DB server) Script Location: /home/oracle/Jenkins-agent
Jenkins Job	Jenkins Job type – (Freestyle job) Goal – Jenkins job to run the SQL code in remote database.

👉 Step 1: Configure Oracle DB server as Jenkins Agent (Slave node):

▪️ The Oracle DB server act as slave node to run the SQL script from Jenkins.

▪️ The SQL script is treated as a Build job which will be executed from Slave node.

▪️ The jdk files (java installation files) has to be copied(or)installed to /home/oracle/jenkins-agent directory.

▪️ The PATH variable and JAVA_HOME variables are to be set in the .bash_profile file as Oracle user.

▪️ The owner of the jdk file should be Oracle user, If not change the ownership.

▪️ Command:   chown -R oracle:oinstall  /home/oracle/jenkins-agent/jdk

▪️ The slave node will be in sync when the agent is up and establishes connection by SSH command.

              Set the Environment variable – for Oracle user in slave machine.

              Test SSH connection as Oracle user: This same SSH should also work from Jenkins.

             Configure Slave node (Oracle server) in Jenkins.

           Slave node (Oracle server) successfully launched in Jenkins agent (JDK)

👉 Step 2: Create/Test a Shell script with SQL code in Oracle DB server (Slave node):

▪️ Run/Test the shell script independently in the oracle server to execute the SQL query.

▪️ The shell script will be created in agent location /home/oracle/Jenkins-agent

▪️ The shell script contains the SQL query to get executed by the Jenkins job.

▪️ The PATH variable and JAVA_HOME variables are set in the .bash_profile file for Oracle user.

▪️ Test the shell script to execute the SQL code and to provide output.

             Execute/Test the Shell script which will run the SQL code in the database.

        

👉 Step 3: Create a Jenkins job (Free style job) to run SQL as shell file in Slave (DB server):

▪️ Create a Jenkins job (Free style job) as per below screenshots.

▪️ Add the build step to execute the shell file through Jenkins job.

▪️ Save the Job configuration after providing the details for the shell file to run.

▪️ Click the Build job from the left side pane of the Jenkins tool.      

👉 Step 4: Check the console output of the Jenkins job.

▪️ Check the Job # number turns GREEN for the successful job execution.

▪️ Click the console output of the Jenkins job.

▪️ Verify the SQL result in the console output.

▪️ Check the status of the end of console output as SUCCESS.

 

 

Author    : Venkat Vinod Kumar Siram 

LinkedIn : https://www.linkedin.com/in/vinodsiram/

Assisted by Shanmugavel

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates.

KTExperts is always active on social media platforms.

Facebook  : https://www.facebook.com/ktexperts

LinkedIn    : https://www.linkedin.com/company/ktexperts/

Twitter       : https://twitter.com/ktexpertsadmin

YouTube   :  https://www.youtube.com/c/ktexperts

Instagram  : https://www.instagram.com/knowledgesharingplatform