LMS 4.0.1 cannot access rmeng database via Open Database Schema Support

[vladakoci]

Hi,

we are running LMS 3.2 and I am using Open Database Schema Support to transfer the data from cmf, ani and rmeng databases.

We now want to migrate to  LMS 4.0.1 and I am trying to use the same scripts to do the same. I am using iAnyWhere JDBC Access Method, the same one I use successfully with LMS 3.2. Can access the tables in cmf and ani databases, but cannot the tables in rmeng database. I do not think my scripts are wrong, I more think enabling access to databases does not work correctly.

I tried to run dbaccess.pl in debug mode and can see there are errors for example while enabling access to the table  NETWORK_DEVICES in rmeng, please see the log below.

We tried to restart LMS and also the whole server ( it is MS Windows 2008 R2 on VMware ), but this did not help.

Appreciate if anyone has an idea what could be wrong.

Thanks,

Vlad

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

D:\BAT>%cwbin%perl  %cwbin%dbaccess.pl install debug
Enter new Password for database view user [lmsdatafeed]:
Re-enter new Password:
Executing on cmf...
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatafeed' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).

Executing on ani...
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatafeed' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).

Executing on dfmFh...
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatafeed' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).

Executing on rmeng...
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatafeed' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: User ID 'lmsdatagrp' does not exist (DBD: execute failed).
   WARN: Table 'SYSLOG_TODAY' not found (DBD: execute failed).
   WARN: Table 'SYSLOG_LASTDAY' not found (DBD: execute failed).
   WARN: Table 'NETWORK_DEVICES' not found (DBD: execute failed).
   WARN: External login for server 'CMFSA' could not be found (DBD: execute fail
ed).
   WARN: Remote server 'CMFSA' could not be found (DBD: execute failed).
   WARN: Unable to connect to server 'CMFSA': [Microsoft][ODBC Driver Manager] T
he specified DSN contains an architecture mismatch between the Driver and Applic
ation (DBD: execute failed).
   WARN: Table 'NETWORK_DEVICES' not found (DBD: execute failed).
   WARN: Table 'NETWORK_DEVICES' not found (DBD: execute failed).
   WARN: Table 'NETWORK_DEVICES' not found (DBD: execute failed).
   WARN: Table 'NETWORK_DEVICES' not found (DBD: execute failed).
   WARN: Table 'NETWORK_DEVICES' not found (DBD: execute failed).
   WARN: Table 'NETWORK_DEVICES' not found (DBD: execute failed).
   WARN: Table 'NETWORK_DEVICES' not found (DBD: execute failed).
   WARN: Table 'NETWORK_DEVICES' not found (DBD: execute failed).
   WARN: Table 'NETWORK_DEVICES' not found (DBD: execute failed).
   WARN: Table 'NETWORK_DEVICES' not found (DBD: execute failed).
   WARN: Table 'NETWORK_DEVICES' not found (DBD: execute failed).
   WARN: Table 'Network_devices' not found (DBD: execute failed).
   WARN: Table 'NETWORK_DEVICES' not found (DBD: execute failed).
   WARN: Table 'DEVICE_INVENTORY' not found (DBD: execute failed).
   WARN: Table 'MEMORY_INVENTORY' not found (DBD: execute failed).
   WARN: Table 'DEVICE_CREDENTIAL_STATUS' not found (DBD: execute failed).
   WARN: Table 'PROCESSOR_INVENTORY' not found (DBD: execute failed).
   WARN: Table 'DEVICE_INVENTORY_COLLECTION_STATUS' not found (DBD: execute fail
ed).
   WARN: Table 'DEVICE_CONFIG_ARCHIVE_STATUS' not found (DBD: execute failed).
   WARN: Table 'CHANGE_AUDIT_HISTORY' not found (DBD: execute failed).
   WARN: Table 'MODULE_INVENTORY' not found (DBD: execute failed).
   WARN: Table 'PORT_INVENTORY' not found (DBD: execute failed).
   WARN: Table 'DEVICE_ENERGYWISE' not found (DBD: execute failed).
   WARN: Table 'PORT_ENERGYWISE' not found (DBD: execute failed).
   WARN: Table 'SYSLOG_TODAY' not found (DBD: execute failed).
   WARN: Table 'SYSLOG_LASTDAY' not found (DBD: execute failed).

[Joe Clarke]

I get the same warnings.  They are not critical.  I can confirm that I can access the tables.  What are the SQL queries you're using?

[vladakoci]

Hello Joseph,

thanks for the reply. I was confused because I am not getting any such errors on LMS 3.2 server.

I am using many Java programs that work well with LMS3.2, but let me mention one that is probably the best to demonstrate the issue I have with the new version. It works well on LMS3.2 with all databases. On LMS4.0.1 it works well with cmf and ani, but cannot dig any data from rmeng.

Here is the batch I run on the LMS 4.0.1 server ( it is not the whole file as it is much larger ):

=================================================================================
@echo *************************************
@echo Starting Creating MySQL Mirror file
@echo *************************************
set classpath=d:\progra~2\CSCOpx\lib\classpath\;d:\bat\
@echo Common Services db, Network_Device view
java CW_Java_CreateSQL cmf password Network_Devices CW_Network_Devices CW_Network_Devices.txt Device_Id Device_Display_Name Management_IpAddress Host_Name Domain_Name sysObjectID Device_Category Device_Series Device_Model UDF0 UDF1 UDF2 UDF3 UDF4 UDF5 UDF6 UDF7 UDF8 UDF9 UDF10 UDF11 UDF12 UDF13 UDF14 UDF15
goto jv1_%errorlevel%
:jv1_0
@echo Campus Manager db, End Hosts view
java CW_Java_CreateSQL ani password End_Hosts CW_End_Hosts CW_End_Hosts.txt HostName UserName IPAddress SubnetMask MACAddress DeviceName Device LastSeen Port VLAN Subnet Notes PrefixLength PortDuplex VTPDomain VlanId PortName IPv6Address dot1xEnabled associatedRouters
goto jv2_%errorlevel%
:jv2_0
@echo Resource Manager Essentials db, Device_Inventory view
java CW_Java_CreateSQL rmeng password Device_Inventory CW_Device_Inventory CW_Device_Inventory.txt Device_Id Device_Display_Name Device_Description Device_Location Device_Contact Device_SW_Version User_Defined_Serial_Number
goto jv3_%errorlevel%
:jv3_0
@echo Resource Manager Essentials db, Module_Inventory view
java CW_Java_CreateSQL rmeng password Module_Inventory CW_Module_Inventory CW_Module_Inventory.txt Device_Id Device_Display_Name Module_Name Vendor_Type SW_Version FW_Version Slot_Number Oper_Status Admin_Status
goto jv4_%errorlevel%
:jv4_0
@echo Resource Manager Essentials db, Port_Inventory view
java CW_Java_CreateSQL rmeng password Port_Inventory CW_Port_Inventory CW_Port_Inventory.txt Device_Id Device_Display_Name Port_Name Port_Desc Port_Type Port_Admin_Status Port_Oper_Status Port_Speed Port_Duplex_Mode Is_Link_Port Is_Access_Port Is_Trunk_Port Is_Port_Channel Vlan_Name VlanId VTPDomain Neighbour_Type
goto jv5_%errorlevel%
:jv5_0
@echo Resource Manager Essentials db, Processor_Inventory view
java CW_Java_CreateSQL rmeng password Processor_Inventory CW_Processor_Inventory CW_Processor_Inventory.txt Device_Id Device_Display_Name Processor_model_name Processor_vendor_type Processor_manufacturer serialnumber
goto jv6_%errorlevel%
:jv6_0
@echo *************************************
@echo Finished Creating MySQL Mirror file
@echo *************************************
=================================================================================

Here is the log:

=================================================================================

*************************************
Starting Creating MySQL Mirror file
*************************************

D:\BAT>set classpath=d:\progra~2\CSCOpx\lib\classpath\;d:\bat\
Common Services db, Network_Device view

D:\BAT>java CW_Java_CreateSQL cmf password Network_Devices CW_Network_Devices CW_Ne
twork_Devices.txt Device_Id Device_Display_Name Management_IpAddress Host_Name D
omain_Name sysObjectID Device_Category Device_Series Device_Model UDF0 UDF1 UDF2
UDF3 UDF4 UDF5 UDF6 UDF7 UDF8 UDF9 UDF10 UDF11 UDF12 UDF13 UDF14 UDF15

D:\BAT>goto jv1_0
Campus Manager db, End Hosts view

D:\BAT>java CW_Java_CreateSQL ani password End_Hosts CW_End_Hosts CW_End_Hosts.txt
HostName UserName IPAddress SubnetMask MACAddress DeviceName Device LastSeen Por
t VLAN Subnet Notes PrefixLength PortDuplex VTPDomain VlanId PortName IPv6Addres
s dot1xEnabled associatedRouters

D:\BAT>goto jv2_0
Resource Manager Essentials db, Device_Inventory view

D:\BAT>java CW_Java_CreateSQL rmeng password Device_Inventory CW_Device_Inventory C
W_Device_Inventory.txt Device_Id Device_Display_Name Device_Description Device_L
ocation Device_Contact Device_SW_Version User_Defined_Serial_Number
Exception in thread "main" java.sql.SQLException: [Sybase][ODBC Driver][SQL Anyw
here]Table 'Device_Inventory' not found
        at ianywhere.ml.jdbcodbc.IIStatement.executeQuery(Native Method)
        at ianywhere.ml.jdbcodbc.IStatement.executeQuery(IStatement.java:201)
        at CW_Java_CreateSQL.main(CW_Java_CreateSQL.java:102)

D:\BAT>goto jv3_1
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Error Creating MySQL Mirror file
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

=================================================================================

The line 102 in Java program is this line:

rs = stmt.executeQuery(query);

Attached is the full Java program - CW_Java_CreateSQL.java

Thank you,

Vlad

[vladakoci]

The above example is maybe too complicated to go through.

Here is much simpler example.

I've slightly modified the Cisco perl example from Open Database Schema Support LMS 4.0:

use DBI;
my ($dbh, $cur, $sqlstr);
my $connString = "dsn=rmeng;uid=lmsdatafeed";
$dbh = DBI->connect('DBI:ASAny:', $connString, 'password', {PrintError => 1});
if ($dbh) {
my $sqlstr = "SELECT Device_Id, Device_Display_Name, Device_Description FROM
Device_Inventory";
my $cur = $dbh->prepare($sqlstr);
$cur->execute if ($cur);
while (my $rs = $cur->fetchrow_arrayref)
{
my ($devId, $dispName, $ip,) = @$rs;
print "Device_Id: $devId\n";
print "Device_Display_Name: $dispName\n";
print "Device_Description: $ip\n";
print "--------------------------------------------\n";
}
$cur->finish;
$dbh->disconnect();
}

and here is what I got:

D:\BAT>%cwbin%perl test.pl
DBD::ASAny::st execute failed: Table 'Device_Inventory' not found (DBD: describe
output failed) at test.pl line 9.
DBD::ASAny::st fetchrow_arrayref failed:  at test.pl line 10.

If I try with cmf ( the original perl script ) database it works well.

[Joe Clarke]

I tested with your code, and it works on my 4.0.1 server.  I suspect there is a problem with your installation.  Post the NMSROOT/databases/rmeng/dbviews.txt file.

[vladakoci]

Attached is NMSROOT/databases/rmeng/dbviews.txt file.

[Joe Clarke]

I went back and looked at your dbaccess.pl errors, and I missed the critical one.  There is an architecture mismatch between the database and and librairs being used on your remote Windows box.  What DLLs did you copy from the LMS 4.0 server to this remote machine?

[vladakoci]

There is no remote box. I run all the scripts directly on the server with LMS 4.0. I did not install LMS 4.0 by myself, it was done by colleague of mine. He did not have any troubles during installation and did not get any error messages or warnings.

The server is MS Windows 2008 R2 running on VMware.

Attached are the application versions of the LMS4 server.

[Joe Clarke]

I can reproduce on my Windows 2008 server.  I am not seeing a problem on Solaris.  Can you confirm if this is 64-bit Windows?

[Joe Clarke · Accepted solution]

Found out that this is a bug, CSCto44917.  The workaround is to edit the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\cmf, and change the Driver key.  Where it says "win32" replace that with "x64".  Then re-run the dbaccess.pl script.

[vladakoci]

Hello Joseph,

thank you.

I did and it resolved the problem. I am now able to work with the tables in rmeng database.

CSCto44917 is maybe Cisco internal only as I cannot find it.

Would you recommend  replacing win32 with x64 also in other registry subfolders, like for example rmeng as there seems to be win32 in the driver path too ?

Thank you,

Vlad

[Joe Clarke]

You can do this, but it is not required.