Failed to access OM3.7 database view meta data of through jdbc

[achase]

OM3.7 provides database views which can be accessed through both ODBC and JDBC.

I am trying to access database view through JDBC connection, but I get an SQL Exception

when I am trying to get view's column type by calling getColumnTypeName(1):

Error:

JZ0SJ: Metadata accessor information was not found on this database.

Please install the required tables as mentioned in the jConnect documentation.

....

The error occurs when the sybase ASA server has not been enbled for JDBC. So, the question is:

Is embeded database disabled for JDBC access by default for OM3.7? Is that way to enable it somewhere?

[shridhar]

Hi Andrew,

I am able to access database view through JDBC connection and retrieve metadata of the DataFabric Manager views successfully.

I was able to use 'getColumnTypeName()' api without any issue.

Please find attached a sample Java code (dfmAccess.java) which retrieves metadata of the DataFabric Manager views.

The output for hostView looks like this:

******************   View - hostView   ******************

hostId(unsigned int)    hostType(varchar)       hostPrimaryAddress(varchar)     hostProductId(varchar)  hostPingStatus(varchar) hostPingTimestamp(timestamp)    hostDownTimestamp(timestamp)    hostClusterId(unsigned int)     hostMode(varchar)

78              Clustered Filer         1.0.0.1            0111111111              Up              2009-05-25 15:04:15.000         2009-05-25 14:22:44.000         78              node

79              Clustered Filer         1.0.0.2            01201168088              Up              2009-05-25 15:03:17.000         2009-05-25 14:23:24.000         79              node

My Setup Details are as follows:
DFM server - OEL 4.0
Windows client : Win 2003 64 bit

Following were the steps followed by me:

1. Install latest Java Development Kit on the Windows system

2. Copy jconn2.jar and jodbc.jar from <DFM install-dir>/misc/dbconn folder to
   C:\Sun\SDK\jdk\lib directory on the Windows system

3. Ensure that the environment variables 'JAVA_HOME' and 'CLASSPATH' are set.

   The variable "CLASSPATH" should be created in 'User variables for Administrators" section in 'Environment Variables' wizard
   Example: The value of this variable could be like this
        C:\Sun\SDK\lib;C:\Sun\SDK\class\..;C:\Sun\SDK\jdk\bin;C:\Sun\SDK\jdk\lib\..;C:\Sun\SDK\jdk\lib\jconn2.jar;C:\Sun\SDK\jdk\lib\jodbc.jar

4. Create a database user on the DFM server by using the cli 'dfm database user create'.

5. Enable database access to the user by using the cli 'dfm database access enable'

6. From the Windows system create a DSN to the remote DFM server database using the ODBC Administrative tool.

    Use the user credentials created above
    On Windows 64 bit servers, the odbcad32.exe is available in C:\WINDOWS\SysWOW64 folder

7. Edit the java source file to reflect this database user and its password. Also specify the name of the DSN created.
   Copy the java source file to C:\Sun\SDK\jdk\bin directory

8. Compile and execute the java file.

Regards,

Shridhar

P.S: Please note that code iterates through all the DFM views and prints its metadata as well as the values. You may want to modify the code to print

       single view information at a time

[achase]

Yes, I am be able to connect to DFM database through the iAywhere JDBC drive with DSN specified. That means each client has to have jdbc drive (dbodbc9.dll) loaded, which doesn't have by default on window system. And we have to manually create DSN on each client.  Right now, I have trouble to get the metadata by connecting  to DFM database through JConnect driver, which is pure Java. So qeustion is: Does OM3.7 fully support JConnect drive?  Or Is that possible to use DSN-less jdbc connection?

[raja]

Andrew,

You are right, the stored procedures required by Sybase to provide metadata information are not installed on Operations Manager DB.

Could you please open a support case for this issue ?

I installed the stored procedures on a test system, and still not able to get the metadata. Will work with Sybase to resolve that.

I have attached a sample java code where i tried two methods for getting the metadata using the JConnect driver (jconn2.jar installed with DFM).

Method - 1: Using getColumnTypeName() returned null after installing the stored procedures.

Method - 2: Using sp_columns() stored procedure to get the columns and data type.

Please let me know if the second method will work for you. I have attached the sample code (you will have to comment method-1's code), and here's the output:

Connected to Monitordb using JConnect.
Details of obectView (using JConnect):
    objId (null)
    objName (null)
    objFullName (null)
    objDescription (null)
    objType (null)
    objStatus (null)
    objPerfStatus (null)

Details of obectView (using stored procedures):
    objId (unsigned int)
    objName (varchar)
    objFullName (varchar)
    objDescription (varchar)
    objType (varchar)
    objStatus (varchar)
    objPerfStatus (varchar)

Thanks,

Raj.

[achase]

Thanks for the quick response. I can't open a support issue because we are using  a  trail version of the OM3.7, which don't have a valid serial number.

[raja]

No problem. I have opened a bug and a support case with Sybase.

Thanks,

Raj.

[achase]

Hi Raj,

Has this issue been resolved in a patch for OM v3.7?   Do you know if it remains an issue for OM v3.8?

Thnaks

Andrew