Operations Manager and Crystal Reports

[chriskranz]

I'm about to start reading through http://media.netapp.com/documents/tr-3690.pdf but I wanted to ask quickly on here to see if anyone has done this before.

I have a customer wanting some custom Crystal Reports based on Operations Manager (or the DFM database to be precise). Can this be done by connecting directly to the Sybase database, or does the data need to be exported first? If we wanted to run regular reports for instance, this could make scheduling a bit tricky.

Any pointers would be much appreciated. I know I can do a lot with Operations Manager custom reports anyway, but the customer already has a lot of Crystal Report writers and they can make short work of the reports for management summaries once they get the database structure and connections all sorted.

Cheers...

[raja · Accepted solution]

You do not need to export the database. Crystal Reports can connect to DFM database directly to get the data over ODBC.

In the TR, you can look at Section 3.5 - Case A:

Case A: Storage administrator wants to access directly the DataFabric Manager database views from third-party reporting tools such as Crystal Report to generate customized storage capacity reports.

Thanks,

Raj.

[chriskranz]

Cheers Raj! Sorry that was a bit lazy of me posting the document that answered my own question!

But thanks for the pointers. Has anyone set this up? Any pointers or gotchas? I'm trying to see if I can get a copy of Crystal and testing it out before letting the customer loose on it.

[adaikkap]

Yes can directly connect to the db over odbc.

Create a db user to dfm using the following command.

dfm database user create -u <user-name> [ -p <password> ]

dfm database query run [ -F <output-format> ] <select-query>

Regards

adai

[cat_towerbe]

I apologize for beating a dead horse, but I cannot figure out how to connect to the database backend.  I understand what I'm being asked to do, but I cannot find the Adaptive Server Anywhere 9.0 ODBC driver (in fact the one on my Operations Manager server doesn't even work).  I've created many ODBC connections in the past, so that's not the issue.

The technical document doesn't explain how to use the JAR files to create a JDBC connection. I can't seem to find any documentation on how to use these files.

Can anyone give me some guidance, please?

[abhit]

If trying to setup a DSN from a windows system other than the Operations Manager server system, the ODBC driver files need to be copied to the server from OM server.

On 3.7, the following three files copied from "<dfm-install-dir>\Sybase\ASA\win32"

dbodbc9.dll
dblgen9.dll
dbcon9.dll

Follow the instructions in these two pages:

• ODBC driver required files - http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbpgen9/00000657.htm
• Configuring the ODBC driver - http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbpgen9/00000658.htm

On 3.8, the following three files copied from "<dfm-install-dir>\Sybase\ASA\win32"

dbodbc10.dll
dblgen10.dll
dbcon10.dll

Follow the instructions in these two pages:

• ODBC driver required files - http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1000/en/html/dbpgen10/pg-odbc-driver-deploy.html
• Configuring the ODBC driver - http://www.ianywhere.com/developer/product_manuals/sqlanywhere/1001/en/html/dbpgen10/pg-configuring-driver-client-deploy.html

Then configure the DSN using the installed driver.

The documentation above will give you directions for the ASA driver.

Regards

Abhi

[cat_towerbe]

Abhi,

Wonderful write up and a great answer!!!!

Thanks so much for replying with this information.  I'm now connected and the world is good.

bt

[arthursc0]

Hi abhi,

Great reply but my scenario is slightly different.

My DFM is linux based and I am trying to create a pivot table in Excel to connect to DFM to reports on aggr/vol/luns for users.

You detailed on how to copy the necessary .dll for windows, but what is required for the linux sybase version?

Failing that, can I import or migrate linux DFM to windows DFM without losing the data?

regards

Colin

[adaikkap]

Failing that, can I import or migrate linux DFM to windows DFM without losing the data?

Yes, Take a backup of dfm using the command dfm backup create in the linux server.

Restore this backup on the new Windows sever where you have installed dfm, using dfm backup restore.

Regards

adai

[david_wallis]

Funnily enough I was just looking at this last night, I tried the same copying the dll's and it didnt work, so I downloaded the ianywhere v10 developer edition and installed very little, just the client tools, it then worked. However I was messing about for ages before I realised that I had created a 64bit ODBC connection.

So running

%WINDIR%\SysWOW64\odbcad32.exe worked

rather than running the 64bit version which is:

%WINDIR%\System32\odbcad32.exe

I have created an ODBC connection as I am looking at pulling the data from the ops mgr database into SQL using an SSIS package so that I can get my monthly capacity report to pull the aggr and vol stats for some volumes.

David

[cat_towerbe]

Any chance you know where to find the manuals listed in your previous message now?  The links provided no longer work.

[cat_towerbe]

And I just found it myself.

http://infocenter.sybase.com/help/topic/com.sybase.dbpgen_1001/pdf/dbpgen10.pdf?noframes=true

Starts on page 806 of the above PDF.