Connect MySQL database and read content of table using N8N

Requirement:

Connect MySQL database and read content of table using N8N

Pre-requisites:

• n8n tools is installed
• mysql database is installed and accessible

Brief description of flow :

• here , the flow is triggered with a chat.
• In chat, the table name (only one) is given in "" (double quotes)
• Extract table name from the chat input
• Identify the table is present in database or not, using mysql node
• If table is present, extract the table content
• Extract the content of that table and send content in chat.
• If table is not present, send message back to chat "This table xxxx is not present in database""

Flow diagram is as following;

Connect oracle database with n8n

Requirement: In automation tool n8n, we want a node to connect with oracle database

Pre-requisites:

Install the NPM package

Package name : n8n-nodes-oracle-database-parameterization

Command to install  

npm install n8n-nodes-oracle-database-parameterization

Set the environment variable:

export N8N_RUNNERS_ENABLED=trueexport N8N_CUSTOM_EXTENSIONS=$(pwd)/node_modules/n8n-nodes-oracle-database-parameterizatio

        Start the n8n server: 

                    n8n start 

Create LLM Chatbot using Gradio, response in same screen from multiple models

Python code , that takes the input from user and from backend it connects with OPENAI_ChatGPT and Google Gemini Modes. 

As a response it prints the outcome on same screen. 

files:

.env

Content of .env file:

OPEN_API_KEY="<Add your Key Here"

GOOGLE_API_KEY="Add your Key Here"

Content of AImalio.ipynb

Refer the code here...

Default location for Ollama models in Mac

Question: Where is GenAI ollama models are stored in Mac OS when we run ollama run model name

Answer: Default Locations: on macOS: ~/.ollama/models/blobs , 

here ~ defines the default home of the user is logged in to the system as.

Example:

bash-3.2$ cd /Users/neerajsingh/.ollama/models/blobs

Change remote trail location RMTTRAIL in pump in Goldengate

 Summary: This section explains the method to change REMOTE TRAIL location used in PUMP process in goldengate.

Description:

This blog is for modifying the remote trail RMTRAIL location in pump.

Case if to changing the mount point.

Location Before -/u02/app/oracle/product/19.0.0.0/gghome_1/dirdat/RPPPIA02/j2

Location After -/u03/app/oracle/product/19.0.0.0/gghome_1/dirdat/RPPPIA02/j2

Add new extract in Goldengate

Summary: Add new Extract process in GoldenGate

Description:

This blog is for adding a new extract in goldengate considering few assumptions.

1. GG manager is already configured.
2. DB level metadata, table/schema trandata is configured.
3. GG user is configured and had DBA access to datatabase

DB Growth report - Oracle

Database growth report - Average per day :

For capacity analysis and abnormalities of data load, it is mandatory to manage continuous review of the database utilization growth.

Monthly Database growth report - Average 

Oracle - Explain plan cost -- What is it and how optimizer calculated

Oracle - Explain plan cost -- What is it and how optimizer calculates it

In most of the Oracle DBA performance interviews and blogs, i observed one common question related to explain plan. 

Question is -- how do you define the cost mentioned in query explain plan? 

So here is the basic formula used by Oracle optimizer. 

For any SQL statement, oracle optimizer has to perform system I/O calls and CPUs to process the called blocks. Database information is stored in the logical form of ROWs and Columns of tables. Database storage unit is DB BLOCK (which is combination of OS Blocks based on the DB Block size) which stores the information. Each row of the table has ROW_ID, which is in hexadecimal format and donates the information about – Datafile, DB Block number & Row location in that DB block.

Now when oracle raises the request to read the number of blocks from disk, the system generates the calls for either single block read and/or multi-block-reads. This is called the IO cost.

Enable SSL Ambari server

This blogs shares the steps involved for enable SSL for Ambari-server using KeyStore and Private keys.

For more details click below ---

Install Apache hadoop V1 Pseudo mode on CentOS6.5

Purpose:

We have CentOS 6.5 on which we have to install Hadoop 1.2.1 with Java JDK-8.

Install Apace Ambari-Server on Linux Centos 6.5

Purpose: 
This blog explains the Ambari Server in Installation on CentOS.

Drop the SQL PLAN BASELINE

Drop the SQL_BASELINES

This post explains to Drop the SQL plan baselines of the SQL.

set lines 200 pages 2000
column sql_handle for a50
col plan_name for a40
col SQLTEXT_Trimmed for a60
select  sql_handle,plan_name,created, substr(sql_text,0,50) SQLTEXT_Trimmed,accepted, fixed, enabled 
from dba_sql_plan_baselines order by created desc;

From above command you can find the details of the baseline like name of the Baselines , SQL PLAN handle and PLAN name. Note: these plan name and SQL_HANDLE will be different from the SQLID and PLAN_HASH_VLAUE. To find out the sql_handle you can query the details from V$SQL for specific SQLID.

Now till now we have 2 things handy -

1.  If we have the SQLID , then from V$SQL we have the SQL_PLAN_HANDLE name.
2. From the SQL_PLAN_HANDLE, there can be multiple PLAN_NAMES mentioned in the DBA_SQL_PLAN_BASELINES view.

Now we have the package available in oracle to drop the SQL PLAN BASELINE -

 declare 
 drop_result pls_integer;
 begin 
 drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE( 
 sql_handle => '&SQL_HANDLE_NAME',  
 plan_name => '&SQL_BASELINE_PLAN_NAME'); 
 dbms_output.put_line(drop_result);    
 end; 
/

Fetch the SQL BASELINE evolve Report without actually change the ACCEPTED flag

In this blog we will explain the details of how to fetch the SQL BASELINE Report.

first of the the method we use for SQL PLAN BASELINE is the EVOLVE and the feature name used in Oracle is SQL PLAN MANAGER.  Oracle has provided the DBMS package for this is SPM. 

Now in the SPM Package ,we evolve the SQL PLAN BASELINE manually. Once we run the evolve this will evaluate the benefit of SQL PLAN BASELINE plans and accept them. Once we do not need to make changes and only to fetch the report of the SQLPLAB BASELINE, the cause COMMIT=NO is used. 

Sample example - 

set lines 200 pages 20000

set serveroutput on
  declare evolve_out CLOB;
  begin
  evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_abdfaaa7e926cf0a', 

  COMMIT => 'NO' );
  dbms_output.put_line(evolve_out);
  end; 

/

Explain plan for SQL_BASELINES using DBMS package in ORACLE

Explain plan for SQL_BASELINES

This post explains to find out the explain plan used by the SQL plan baselines of the SQL.

set lines 200 pages 2000
column sql_handle for a50
col plan_name for a40
col SQLTEXT_Trimmed for a60
select profile, sql_handle,plan_name,created, substr(sql_text,0,50) SQLTEXT_Trimmed,accepted, fixed, enabled 
from dba_sql_plan_baselines order by created desc;

From above command you can find the details of the baseline like name of the Baselines , SQL PLAN handle and PLAN name. Note: these plan name and SQL_HANDLE will be different from the SQLID and PLAN_HASH_VLAUE. To find out the sql_handle you can query the details from V$SQL for specific SQLID.

Now till now we have 2 things handy -

1.  If we have the SQLID , then from V$SQL we have the SQL_PLAN_HANDLE name.
2. From the SQL_PLAN_HANDLE, there can be multiple PLAN_NAMES mentioned in the DBA_SQL_PLAN_BASELINES view.

If you have only SQLPLAN_HANDLE name -


select * from table(dbms_xplan.display_sql_plan_baseline('&SQL_PLAN_HANDLE')); 

If you want to check the explain plan of specific SQL_PLAN NAME, then you can add the second argument in the above command.


select * from table(dbms_xplan.display_sql_plan_baseline('&SQL_PLAN_HANDLE','&PLAN_NAME');

Recover data of table from FLASHBACK

In this post , we are going to explain how can you recover the data of a table from the past timestamp with FLASHBACK on