DX Unified Infrastructure Management

Hi All,

From the below tech tip, I can see there is DB connection provided for MSSQL and not for ORACLE database.

Tech Tip: UIM - correlating several alarms in the NAS 

We are using Oracle DB. What are the equivalent statements for below to connect to ORACLE DB and run the query?

database.open("Provider=SQLOLEDB;Initial Catalog=CA_UIM;Data Source=<databaseserver>,1433;User ID=sa;Password=<databasepassword>;Network Library=dbmssocn;Language=us_english")

database.query("select * from nas_alarms WITH (NOLOCK) where robot = '"..a.robot.."' and (message like '%total cpu is now%' or message like '%processor queue length%')")

Thank you.
Regards,
Rajashekar

here is a sample lua script to connect to and oracle database.

-- Connect to the database
database.open("Plugin=Oracle;Servicename=nimdb;Port=1521;Hostname=***.***.***.***;User ID=nimmon;Password=nimmon")
local rs = database.query("SELECT CURRENT_DATE AS data_atual FROM dual")

print(rs)
print(#rs)

if rs ~= nil and #rs > 0 then
print(rs[1].DATA_ATUAL)
end


database.close()

Thank you Gene for your prompt response as always.

Sorry Gene for commenting back on this thread after long time. I see the suggested syntax is not working for me.

I am trying the below code.

database.open("Plugin=Oracle;Servicename=ccauimd;Port=1521;Hostname=10.1.2.3;User ID=uimadmin;Password=xxxxx")
local rs = database.query("SELECT CURRENT_DATE AS data_atual FROM dual")

print("Number of rows:",#rs)

database.close()

Where cauimd is the DB name, uimadmin is the the owner of the UIM DB and the host name 10.1.2.3 is the DB server IP where the DB is hosted.

When I test the script, it is returning Number of rows:0 always for print("Number of rows:",#rs) statement.

Please let me know whats wrong with the above.

Regards,

Rajashekar

Hi,

So in your database to you have a table called dual?

if not this would be the reason.

My post was an example you would need to modify for your environment

you could try something simple such as the following if you are looking the the UIM database

local rs = database.query("SELECT COUNT(*) as cs FROM CM_COMPUTER_SYSTEM;")

also check your connection and query with sqlplus from the primary hub.

Since you guys are talking about oracle...oracle mainly likes all uppercase column and table names...however if you created a table named dual using quoted identifiers, you will need to continue to use quoted identifiers to access the table when using lowercase created tablenames and column names....hope that helps

SELECT COLUMN_A, "column_b" FROM "dual"

In earlier example, if I run the query "SELECT CURRENT_DATE AS data_atual FROM dual" on database using sqlplus or toad I am seeing the output.

Using the below code, I still see it is just printing Number of rows:0.

database.open("Plugin=Oracle;Servicename=ccauimd;Port=1521;Hostname=10.1.2.3;User ID=uimadmin;Password=xxxx")
local rs = database.query("SELECT COUNT(*) as cs FROM CM_COMPUTER_SYSTEM;")
print("Number of rows:",#rs)
database.close()

How can I check if the database.open is opening the connect to database successfully?

Below is the sqlplus output.

you should be able to use a print(database)

so see the results of the database open process.

I am seeing the below output.

table:0x7f63f001d340
Number of rows:0

I need more information.

What OS type and version are you testing this on?

I assume this is on your primary is this correct?

Are you using oracle for your back end database currently?

If so do you have the oracle thin client installed?

Here are the details.

What OS type and version are you testing this on? --> RHEL release 7.5

I assume this is on your primary is this correct? --> Yes, NAS probe is running on Primary Server

Are you using oracle for your back end database currently? --> Yes

If so do you have the oracle thin client installed? --> Yes, Oracle client is installed on the server where the nas probe is running.

UIM is installed with admin account and oracle client is installed with a different account.

I you want to connect to the UIM database you could use the below:

print("Start database connection ")

local ci_query = "SELECT COUNT(*) rowct FROM CM_COMPUTER_SYSTEM;"
local row = nil
local count = 0
RETRY = 3

local rc2 = database.open("provider=nis;database=nis;driver=none")
print(rc)

   -- Retry the database connection if it fails
while rc2 ~= NIME_OK and count < RETRY do
      print("Database Connection failed")
      rc2 = database.open(conn_str, false)
      count = count + 1
end
      print(rc2)
if rc2 == NIME_OK then
      -- database connection ok
      print("Database Connection ok")
      print(ci_query)
      local rs = database.query(ci_query)
      print(rc2)
      row = rs[1]
      print("Column A has: " .. rs[1].rowct)
else
      -- database connection not ok raise alarm
      local alarm_txt = "Failed to connect to database"
      nimbus.alarm(4, alarm_txt, "CSM Reporting Database Connection")
end
database.close()

I am trying to setup a connection to a Oracle database using a thin client but that is proving more challenging

On a windows 2012 server to get this to work I had to do the following

1) download and install the Oracle ODAC 64 ODBC drivers.

2) I downloaded and installed the second download package from the below link.

64-bit Oracle Data Access Components (ODAC) for Windows 

ODAC122010Xcopy_x64.zip

3) in the 64 bit ODBC section of windows I could then see a new driver called

Oracle in OraClient12Home1

4)  I could then use the below script to connect and read information from the oracle database.

print("Start database connection 1")
-- Connect to the database
local oracle_server = "<Servername>"
local oracle_user = "<UserName>"
local oracle_pwd = "<Userpassword>"
local oracle_service = "<ServiceName>"
local oracle_port = "1521"
local oracle_database= "CA_UIM"
local database_driver ="Oracle in OraClient12Home1"

if oracle_pwd ~= "" then
    -- Build connection string
       local cs = "Driver={"..database_driver.."};Dbq=//"..oracle_server..":"..oracle_port.."/"..oracle_service..";Uid="..oracle_user..";Pwd="..oracle_pwd..";"
      print (cs)
    local rc = database.open(cs,false)
      print(rc)
    if rc == NIME_OK then
        local rs = database.query ("SELECT * FROM CA_UIM.CM_COMPUTER_SYSTEM")
        printf ("Number of QoS objects: %d",#rs)
            for i=1,#rs do
               print("Name Column has: " .. rs[i].NAME .. ", CS_ID Column has: " .. rs[i].CS_ID)
            end

    else
        print ("Failed to open database using the provided connection string!")
    end

    database.close()
end

Next I will check and see if I can reproduce this  on a Linux box.

I have create the below a RHEL Machine that was setup using an oracle.

Also returning the data needed to be done a little different than on windows.

print("Start database connection 1")
-- Connect to the database
local oracle_server = "<ServerName>"
local oracle_user = "<OracleUser>"
local oracle_pwd = "<UserPassword>"
local oracle_service = "<OracleServiceName>"
local oracle_port = "1521"
local oracle_database= "CA_UIM"
local database_driver ="Oracle in OraClient12Home1"

function getRow(dataTable, rowNum, isNIS)
local realRow
if (isNIS) then
realRow = dataTable[rowNum]
else
realRow = {}
for key, val in pairs(dataTable[rowNum]) do
realRow[key] = val
realRow[string.lower(key)] = val
realRow[string.upper(key)] = val
end
end

return realRow
end

if oracle_pwd ~= "" then
    -- Build connection string
    local cs = "Provider=Oracle;Data Source="..oracle_server..";Database="..oracle_database..";User ID="..oracle_user..";Password="..oracle_pwd..";ServiceName="..oracle_service..";Port="..oracle_port..";"
    print (cs)
    local rc = database.open(cs,false)
      print(rc)
    if rc == NIME_OK then
        local rs = database.query ("Select * from CA_UIM.cm_computer_system order by CS_ID")
        printf ("Number of QoS objects: %d",#rs)

            for I=1,#rs do
            nextRow = getRow(rs, I, false)
            print("For Row " .. I .. " NAME is " .. nextRow.NAME .. " and CA_ID is " .. nextRow.CS_ID)
            end

            for i=1,#rs do
               print("Name Column has: " .. rs[i].NAME) -- .. ", CS_ID Column has: " .. rs[i].CS_ID)
            end

    else
        print ("Failed to open database using the provided connection string!")
    end

    database.close()
end

Thank you Gene for your help. This time, it is working as expected. With the example you provided, I am able to test the DB connection as below.

print ("Start database connection 1")
-- Connect to the database
local oracle_server = "oracleServer.abc.com"
local oracle_user = "uimadmin"
local oracle_pwd = "DBPASWD"
local oracle_service = "CAUIM"                   -- SERVICE_NAME from the TNS Entry/connection string
local oracle_port = "1521"
local oracle_database= "CAUIM"                --DBNAME
-- local database_driver ="Oracle in OraClient12Home1" --In my case, it is working without this as I am on RHEL

if oracle_pwd ~= "" then
-- Build connection string
local cs = "Provider=Oracle;Data Source="..oracle_server..";Database="..oracle_database..";User ID="..oracle_user..";Password="..oracle_pwd..";ServiceName="..oracle_service..";Port="..oracle_port..";"
print (cs)
local rc = database.open(cs,false)
print(rc)
if rc == NIME_OK then
local rs = database.query ("Select * from nas_alarms;")
printf ("Number of QoS objects: %d",#rs)
for i=1,#rs do
print (rs[i].message)
end
else
print ("Failed to open database using the provided connection string!")
end
database.close()
end

I have removed the function block "function getRow(dataTable, rowNum, isNIS)" as we are not using the returned value anywhere.

Even, the earlier suggested one also working fine (database.open("provider=nis;database=nis;driver=none")).

Really helpful. Thank you for your time in assisting me on this.

Thank you.

Rajashekar