Service Operations Insight

So, I have successfully connected to an internal MySQL database after installing the driver however, I am getting errors when trying to pull data.

Here is my XML adapter:

<?xml version="1.0" encoding="UTF-8"?>

<data_source_type xmlns="http://www.hyperformix.com/DataManager" version="4.5">

        <name>bcssi_san_data_db_v5</name>

        <author>Seth Taylor</author>

        <copyright>&#169; 2009 Hyperformix, Inc.</copyright>

        <description><![CDATA[This DGE supports gathering SAN data from the UNIX BCSSI MySQL database.]]></description>

        <!-- Omitting the package name from the engine implies com.hyperformix.DataGatherEngine.DataIntf.Engine. -->

        <data_aquisition_class name="ITM_DB_Engine"/>

        <server_list_query>select distinct hostname from hba_data</server_list_query>

        <map id="clariionLunMetricsMap">

                <source>

                        <select>select hostname, emcpseudo, lunsize, type, arrayid, lunid, resourcename, totalsize, current_timestamp from hba_data where hostname IN (DGE_SERVERS_LIST) AND type="CLARiion" </select>

                        <column name="hostname" type="string"/>

                        <column name="emcpseudo" type="string"/>

                        <column name="lunsize" type="string"/>

                        <column name="type" type="string"/>

                        <column name="arrayid" type="string"/>

                        <column name="lunid" type="string"/>

                        <column name="resourcename" type="string"/>

                        <column name="totalsize" type="integer"/>

                        <column name="timestamp" type="date" date_format="yyyy-MM-dd HH:mm:ss" optional="false"/>

                </source>

                <destinations>

                        <entity_metrics>

                                <required_fields>

                                        <entity_id source="lunid"/>

                                        <entity_name source="resourcename"/>

                                        <entity_type source="type"/>

                                        <metric_time source="timestamp"/>

                                        <gmt_offset source="DGE_GMT_OFFSET"/>

                                </required_fields>

                                <optional_fields>

                                </optional_fields>

                                <metrics>

                                        <metric name="LUN_TOTAL_SIZE_MB" unit="string" type="string" source="lunsize * 1024"/>

                                </metrics>

                        </entity_metrics>

                </destinations>

        </map>

</data_source_type>

And here is the error I'm recieving in catalina.out:

java.sql.BatchUpdateException: ORA-01400: cannot insert NULL into ("CCCUSER"."ENTITY_METRICS_STAGING"."METRIC_TIME")

The table I am trying to pull information from doesn't have a date, time, timestamp or anything of that nature so I was trying to get the CURRENT_TIMESTAMP as if I was running the MySQL command NOW(). I am by no means a sql expert and will gladly take criticism if it means it will help solve the problem.

Hi Seth

It is difficult to test without access to a live environment (which I don't have just now), but there are a couple of things I noted in your code above:

1. I assume from your statement 'I have successfully connected to an internal MySQL database after installing the driver' that you have also had the tweak to add MySQL as an option the DM drop-down for database type?
2. You did not specify a column name for the return of 'current_timestamp' - MySQL JDBC can sometimes be funky with column names and aliases - it is unlikely to match up with the '<column name="timestamp" ... />' definition.
3. Also the format of the column definition for timestamp looks far more like one to use for a CSV-based data adapter.  Typically for JDBC, nothing is 'optional' and we would usually have  'type = "sql_date_time" '

As I said, the code below is untested, but I hope will be closer to your needs - note the changes in red and the column name to match in blue:

        <map id="clariionLunMetricsMap">

                <source>

                        <select>select hostname, emcpseudo, lunsize, type, arrayid, lunid, resourcename, totalsize, cast(current_timestamp() as DATETIME) as timestamp from hba_data where hostname IN (DGE_SERVERS_LIST) AND type="CLARiion" </select>

                        <column name="hostname" type="string"/>

                        <column name="emcpseudo" type="string"/>

                        <column name="lunsize" type="string"/>

                        <column name="type" type="string"/>

                        <column name="arrayid" type="string"/>

                        <column name="lunid" type="string"/>

                        <column name="resourcename" type="string"/>

                        <column name="totalsize" type="integer"/>

                        <column name="timestamp" type="sql_date_time"/>

                </source>

                <destinations>

                        <entity_metrics>

                                <required_fields>

                                        <entity_id source="lunid"/>

                                        <entity_name source="resourcename"/>

                                        <entity_type source="type"/>

                                        <metric_time source="timestamp"/>

                                        <gmt_offset source="DGE_GMT_OFFSET"/>

                                </required_fields>

                                <optional_fields>

                                </optional_fields>

                                <metrics>

                                        <metric name="LUN_TOTAL_SIZE_MB" unit="string" type="string" source="lunsize * 1024"/>

                                </metrics>

                        </entity_metrics>

                </destinations>

        </map>

</data_source_type>

No criticism - just constructive (I hope) comments...

Let me know if this helps.

Regards,

Adrian

Seth......

If Adrian's suggestions don't do the trick........you might try adjusting the time format in the query itself.   Something like: 

select hostname, emcpseudo, lunsize, type, arrayid, lunid, resourcename, totalsize,MAX(DATE_FORMAT(sampletime, '%Y-%m-%d %H:%i:%s')) as timestamp from hba_data where hostname IN (DGE_SERVERS_LIST) AND type="CLARiion"

Ashby 

Adrian,

Yes, I had to add the row in the database to give the drop down option in DM.

But the changes made to the adapter worked! Thank you so much. So what I was doing was trying to was pull the timestamp from the table but it didn't exist there so casting the function like you suggested was exactly what I needed.

Again, thanks for the feedback!

Seth

You're welcome - I'm glad it worked

For completeness (in case you need to do this again) there was also a column alias name missing for the returned value - the 'as timestamp' was also an important part of the solution...

Cheers,

Adrian