JDBC data collection (for oracle database) not working

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

JDBC data collection (for oracle database) not working

BeeJay

 

I’m trying to set up jdbc data collection for an oracle database.

 

I’ve got a query returing a single value for the database that is working.

 

Now I’m trying to set up a query that returns multiple rows, and I’m getting the following error in the collectd.log

 

2017-03-20 15:47:04,352 WARN  [Collectd-Thread-21-of-50] o.o.n.c.JdbcCollector: There was a problem executing query 'tablespace_PctUsed' Please review the query or configuration. Reason: Invalid column name

 

The query definition from jdbc-datacollection-config.xml:

 

           <query name="tablespace_PctUsed" ifType="ignore" instance_column="TABLESPACE" resourceType="tablespace_PctUsed">

             <statement>

               <queryString>select VALUE2 as TABLESPACE, VALUE1 as USED from zen_tab_5min where name = 'tablespace_%used'</queryString>

             </statement>

             <columns>

               <column name="TABLESPACE" alias="dbTableSpace" data-source-name="TABLESPACE" type="string"/>

               <column name="USED" alias="dbTSpctUsed" data-source-name="USED" type="gauge"/>

             </columns>

           </query>

 

 

And in the datacollection-group definition I’ve added:

 

   <resourceType name="tablespace_PctUsed" label="Tablespace Percent Used"

          resourceLabel="TableSpace ${dbTableSpace} (index:${index})">

     <persistenceSelectorStrategy

          class="org.opennms.netmgt.collection.support.PersistAllSelectorStrategy"/>

     <storageStrategy

          class="org.opennms.netmgt.collection.support.IndexStorageStrategy"/>

   </resourceType>

 

 

I havn’t yet got around to setting up the snmp-graph.properties configuration, as I’m still trying to get the RRD data to generate.

 

What am I missing in my configuration?

 

Regards,

John.

 

 

 

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: JDBC data collection (for oracle database) not working

David Hustace

> On Mar 19, 2017, at 11:10 PM, JohnD Blackburn <[hidden email]> wrote:
>
> 2017-03-20 15:47:04,352 WARN  [Collectd-Thread-21-of-50] o.o.n.c.JdbcCollector: There was a problem executing query 'tablespace_PctUsed' Please review the query or configuration. Reason: Invalid column name

Reviewing the code:


    } catch(SQLException e) {
    // Close the statement but retain the connection, log the exception and continue to the next query.
    LOG.warn("There was a problem executing query '{}' Please review the query or configuration. Reason: {}", query.getQueryName(), e.getMessage());


You can see that the *reason* is printed from the SQL exception.  That means that the DB parser rejected the SQL with the error: “Invalid column name”.
 


> The query definition from jdbc-datacollection-config.xml:
>  
>                <queryString>select VALUE2 as TABLESPACE, VALUE1 as USED from zen_tab_5min where name = 'tablespace_%used'</queryString>
>              </statement>

Just reviewing the SQL, I see that you’ve specified the column names in the column selection as upper case and the column name in the criteria (where clause) as lower case.  I’m unsure whether your DB parser is case sensitive or not.

For example, with PostgreSQL, if you use "" around your column names, the DB parser will treat them as case sensitive.  When you don’t do this in PostgreSQL, VALUE1 and value1 are treated the same:

VALUE1 == value1
VALUE1 == Value1
"VALUE1" != "value1"
"VALUE1" != "Value1"


Either that or you have incorrectly specified a column name.



David Hustace
The OpenNMS Group, Inc.

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: JDBC data collection (for oracle database) not working

BeeJay

Thanks for your response David.

 

 

I have 2 queries currently defined in jdbc-datacollection-config.

 

    <jdbc-collection name="ABBCS-Oracle">

        <rrd step="300">

                  <rra>RRA:AVERAGE:0.5:1:2016</rra>

                  <rra>RRA:AVERAGE:0.5:12:1488</rra>

                  <rra>RRA:AVERAGE:0.5:288:366</rra>

                  <rra>RRA:MAX:0.5:288:366</rra>

                  <rra>RRA:MIN:0.5:288:366</rra>

        </rrd>

        <queries>

 

           <query name="DaysSinceLastBackedUp" ifType="ignore" >

             <statement>

               <queryString>select VALUE1 as Days from zen_tab_5min where name='days_without_backup' </queryString>

             </statement>

             <columns>

               <column name="Days" alias="Days" data-source-name="Days" type="gauge"/>

             </columns>

           </query>

 

           <query name="tablespace_PctUsed" ifType="ignore" instance_column="dbTableSpace" resourceType="tablespace_PctUsed">

             <statement>

               <queryString>select VALUE2 as dbTableSpace, VALUE1 as dbUsed from zen_tab_5min where name = 'tablespace_%used'</queryString>

             </statement>

             <columns>

               <column name="dbTableSpace" alias="dbTableSpace" data-source-name="dbTableSpace" type="string"/>

               <column name="dbUsed" alias="dbTSpctUsed" data-source-name="dbUsed" type="gauge"/>

             </columns>

           </query>

 

        </queries>

    </jdbc-collection>

 

 

The second one is the one I posted previously.   As the 1st query is working successfully, I've updated the 2nd query to match the 1st in terms of matching the case.  I also changed the "as TABLESPACE" to "as dbTableSpace" in case there was an issue with "TABLESPACE" being considered a reserved word by the db parser.

 

I'm still getting the same warning in the collectd.log file:

 

 

 

2017-03-21 10:23:57,924 INFO  [Collectd-Thread-2-of-50] o.o.n.c.CollectableService: run: starting new collection for 17/10.165.9.4/ABBCS-Oracle-devdb01/ABBCS-Oracle-devdb01

2017-03-21 10:23:57,932 INFO  [Collectd-Thread-2-of-50] o.o.n.c.j.JdbcAgentState: Loaded JDBC driver

2017-03-21 10:23:57,932 DEBUG [Collectd-Thread-2-of-50] o.o.n.c.j.JdbcAgentState: JDBC url: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.165.9.4)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=devdb01)))

2017-03-21 10:23:58,173 DEBUG [Collectd-Thread-2-of-50] o.o.n.c.JdbcCollector: Checking availability of group DaysSinceLastBackedUp

2017-03-21 10:24:01,106 DEBUG [Collectd-Thread-2-of-50] o.o.n.c.JdbcCollector: Group DaysSinceLastBackedUp is  available

2017-03-21 10:24:02,008 DEBUG [Collectd-Thread-2-of-50] o.o.n.c.DefaultCollectionAgentService: getStorageDir: isStoreByForeignSource = false, foreignSource = null, foreignId = null, dir = 17

2017-03-21 10:24:02,008 DEBUG [Collectd-Thread-2-of-50] o.o.n.c.s.AbstractCollectionResource: Adding attribute org.opennms.netmgt.collectd.jdbc.JdbcCollectionAttribute: JdbcCollectionAttribute Days=9999 to group AttrGroupType[name=DaysSinceLastBackedUp, ifType=ignore] for [node]@17

2017-03-21 10:24:02,056 DEBUG [Collectd-Thread-2-of-50] o.o.n.c.JdbcCollector: Checking availability of group tablespace_PctUsed

2017-03-21 10:24:02,333 DEBUG [Collectd-Thread-2-of-50] o.o.n.c.JdbcCollector: Group tablespace_PctUsed is  available

2017-03-21 10:24:06,470 WARN  [Collectd-Thread-2-of-50] o.o.n.c.JdbcCollector: There was a problem executing query 'tablespace_PctUsed' Please review the query or configuration. Reason: Invalid column name

2017-03-21 10:24:06,637 INFO  [Collectd-Thread-2-of-50] o.o.n.c.a.AbstractPersister: Persisting data for resource Node[17]/type[node]

2017-03-21 10:24:06,688 DEBUG [Collectd-Thread-2-of-50] o.o.n.c.a.AttributeGroup: Visiting Group AttrGroupType[name=DaysSinceLastBackedUp, ifType=ignore] for [node]@17

2017-03-21 10:24:06,688 DEBUG [Collectd-Thread-2-of-50] o.o.n.c.a.AttributeGroup: AttrGroupType[name=DaysSinceLastBackedUp, ifType=ignore] for [node]@17.shouldPersist = true

2017-03-21 10:24:06,688 DEBUG [Collectd-Thread-2-of-50] o.o.n.c.s.AbstractCollectionAttribute: Visiting attribute JdbcCollectionAttribute Days=9999

2017-03-21 10:24:06,688 DEBUG [Collectd-Thread-2-of-50] o.o.n.c.a.AbstractPersister: Persisting JdbcCollectionAttribute Days=9999

2017-03-21 10:24:06,688 DEBUG [Collectd-Thread-2-of-50] o.o.n.c.a.AbstractPersister: Storing attribute JdbcCollectionAttribute Days=9999

2017-03-21 10:24:06,688 DEBUG [Collectd-Thread-2-of-50] o.o.n.r.j.JRobinRrdStrategy: createDefinition: filename [/opt/opennms/share/rrd/snmp/17/Days.jrb] already exists returning null as definition

2017-03-21 10:24:06,688 DEBUG [Collectd-Thread-2-of-50] o.o.n.r.j.JRobinRrdStrategy: createRRD: skipping RRD file

2017-03-21 10:24:06,688 INFO  [Collectd-Thread-2-of-50] o.o.n.c.p.r.RrdPersistOperationBuilder: updateRRD: updating RRD file /opt/opennms/share/rrd/snmp/17/Days.jrb with values '1490055847:9999'

2017-03-21 10:24:06,688 DEBUG [Collectd-Thread-2-of-50] o.o.n.c.p.r.RrdPersistOperationBuilder: updateRRD: RRD update command completed.

2017-03-21 10:24:06,746 INFO  [Collectd-Thread-2-of-50] o.o.n.c.CollectableService: run: finished collection for 17/10.165.9.4/ABBCS-Oracle-devdb01/ABBCS-Oracle-devdb01

 

 

The query works ok if I run it directly from SQL*Plus:

 

sqlplus> select VALUE2 as dbTableSpace, VALUE1 as dbUsed from zen_tab_5min where name = 'tablespace_%used';

 

DBTABLESPACE                            DBUSED

---------------------------------------------- ----------

BKPUSER_TAB                                0

ELLPRF_IDX                             25

SYSAUX                                      13

XMLDB_TBS                            0

STATSPACK_DATA                              16

USERS                                1

ELLPRF_TAB                             63

INJRPT_IDX                           0

SYSTEM                                      14

TOOLS                                  31

INJRPT_TAB                           0

 

11 rows selected.

 

 

What does the “instance_column” value in the query tag need to match?

 

I’ve even tried putting quotes around the dbTableSpace, so the query looks like:

select VALUE2 as “dbTableSpace”, VALUE1 as “dbUsed” from zen_tab_5min where name = 'tablespace_%used'

 

But I still get the same error.

 

Is it possible to increase the amount of debug output so I can see more details about what column name it thinks is invalid?

 

 

 

 


                                                                                                       

 

 

ABB logo

John Blackburn

 

Solution Architect - Cloud Services

 

Enterprise Software

 

 

 

Level 1, 757 Ann Street

 

Fortitude Valley, Queensland, 4006

AUSTRALIA

Phone: +61 7 33033219

 

Mobile: +61 403 344 524

E-Mail:  [hidden email]

www.abb.com

 

 

 

-----Original Message-----
From: David Hustace [mailto:[hidden email]]
Sent: Tuesday, 21 March 2017 1:51 AM
To: DiscussionList List <[hidden email]>
Subject: Re: [opennms-discuss] JDBC data collection (for oracle database) not working

 

 

> On Mar 19, 2017, at 11:10 PM, JohnD Blackburn <[hidden email]> wrote:

>

> 2017-03-20 15:47:04,352 WARN  [Collectd-Thread-21-of-50] o.o.n.c.JdbcCollector: There was a problem executing query 'tablespace_PctUsed' Please review the query or configuration. Reason: Invalid column name

 

Reviewing the code:

 

 

    } catch(SQLException e) {

    // Close the statement but retain the connection, log the exception and continue to the next query.

    LOG.warn("There was a problem executing query '{}' Please review the query or configuration. Reason: {}", query.getQueryName(), e.getMessage());

 

 

You can see that the *reason* is printed from the SQL exception.  That means that the DB parser rejected the SQL with the error: “Invalid column name”.

 

 

> The query definition from jdbc-datacollection-config.xml:

>                <queryString>select VALUE2 as TABLESPACE, VALUE1 as USED from zen_tab_5min where name = 'tablespace_%used'</queryString>

>              </statement>

 

Just reviewing the SQL, I see that you’ve specified the column names in the column selection as upper case and the column name in the criteria (where clause) as lower case.  I’m unsure whether your DB parser is case sensitive or not.

 

For example, with PostgreSQL, if you use "" around your column names, the DB parser will treat them as case sensitive.  When you don’t do this in PostgreSQL, VALUE1 and value1 are treated the same:

 

VALUE1 == value1

VALUE1 == Value1

"VALUE1" != "value1"

"VALUE1" != "Value1"

 

 

Either that or you have incorrectly specified a column name.

 

 

 

David Hustace

The OpenNMS Group, Inc.

 

------------------------------------------------------------------------------

Check out the vibrant tech community on one of the world's most

engaging tech sites, Slashdot.org! http://sdm.link/slashdot

_______________________________________________

Please read the OpenNMS Mailing List FAQ:

http://www.opennms.org/index.php/Mailing_List_FAQ

 

opennms-discuss mailing list

 

To *unsubscribe* or change your subscription options, see the bottom of this page:

https://lists.sourceforge.net/lists/listinfo/opennms-discuss


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: JDBC data collection (for oracle database) not working

Seibold, Michael-2

Hmmm… might be some problem with the percent sign and xml encoding?

 

               <queryString>select VALUE2 as dbTableSpace, VALUE1 as dbUsed from zen_tab_5min where name = 'tablespace_%used'</queryString>

 

-Michael

 


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Loading...