×

Warning message

  • Cisco Support Forums is in Read Only mode while the site is being migrated.
  • Cisco Support Forums is in Read Only mode while the site is being migrated.

UCCX Database Empty

Unanswered Question
Apr 18th, 2012
User Badges:

Hello,


I've been trying to create a custom report and have reached the stage where a list of the stored procedures would be useful. I've downloaded and installed the Informix JDBC driver and integrated it with the Squirrel SQL client, but when I try to browse any part of the database it shows as being empty. I've tried two different operating systems using the same UCCX server and don't seem to be able to get around the problem. Any advice would be appreciated.


The guide I have been following is: https://supportforums.cisco.com/docs/DOC-16595


The system version is: 8.5(1)SU2, COP 2.


A screenshot of the Squirrel SQL client is attached.


squirrel-screenshot.png

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Loading.
Gajanan Pande Wed, 04/18/2012 - 20:11
User Badges:
  • Cisco Employee,

Hi There,


Not sure if you exported the UCCX DB to external Data Warehouse ?


If not, please do so as connecting directly to UCCX DB for custom reporting is not recommended & unsupported. 

Q. How to export Historical Data to your own Data Warehouse?

A. Use third-party database administration tools such as SQuirreL SQL Client or AGS Server Studio to export Unified CCX historical data to your own data warehouse. Use "uccxhruser" as the username to connect to db_cra database.


GP.


Pls rate helpful posts by clicking on the stars below the post !!

Aaron Harrison Thu, 04/19/2012 - 01:05
User Badges:
  • Super Bronze, 10000 points or more
  • Community Spotlight Award,

    Member's Choice, May 2015

Hi


Squirrel SQL doesn't cut the mustard I'm afraid.


In informix, 'stored procedures' are split into two types:

- stored procedures - SPs that don't return values

- fucntions - SPs that do return values


Squirrel only returns one of these types if you get it working properly - so you only see half the story.


Try:

IBM Data Studio - horrible, but free

Razor SQL, nicer, small cost, but not great for troubleshooting your SPs

AGS Server Studio - good, but expensive.


Regards



Aaron Harrison

Principal Engineer at Logicalis UK


Please rate helpful posts...

Gergely Szabo Thu, 04/19/2012 - 01:26
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

Hi,

Eclipse, with Data Tools Plugin

G.

Aaron Harrison Thu, 04/19/2012 - 05:27
User Badges:
  • Super Bronze, 10000 points or more
  • Community Spotlight Award,

    Member's Choice, May 2015

Gergely


OK, I'll give it a go... I normally use Netbeans... but just this once...

nowcommsupport Thu, 04/19/2012 - 06:40
User Badges:

Is the problem likely to be to do with Squirrel SQL then? Is it best to try using another editor?

Aaron Harrison Thu, 04/19/2012 - 08:10
User Badges:
  • Super Bronze, 10000 points or more
  • Community Spotlight Award,

    Member's Choice, May 2015

Well - do you see anything under 'tables'? It looks like you are connected. If you can see tables but not the procedures, then yes it's Squirrel that has lost it's nuts.


Aaron

nowcommsupport Sat, 04/21/2012 - 09:16
User Badges:

The problem seems to have been caused by the JDBC driver. Version 3.70.JC1 can retrieve the tables without problems! It's still not possible to see the stored procedures with Squirrel SQL though. Eclipse with Data Tools Plugin gets you slightly further, but it doesn't seem to display the stored procedures fully. I'm now trying AGS Server Studio.


Am I right in thinking that in order to create custom reports, you need to create your own stored procedures? Does this involve learning the Informix Stored Procedure Language, or is there a simpler way to do it?

Aaron Harrison Mon, 04/23/2012 - 02:33
User Badges:
  • Super Bronze, 10000 points or more
  • Community Spotlight Award,

    Member's Choice, May 2015

Hi


Yes and Yes.


You have to create a SP to return the data as Crystal/HRC require you to run one statement to return the data, and you can't just run a 'select' with lots of joins. So you have to create a procedure that is called in one with the paramters set in the HRC client (start, end dates, filtering etc) and returns a single table.


There's no simple way to do it, but I:


  • Use that IBM 'boulder' site that crops up when you google anything and informix for researching syntax etc
  • Look at the UCCX standard SPs to see how they work - check the .xml definition file for a report to see what procedure it uses, and work out what it's doing.
  • When writing your own, use the standard UCCX reports as a basis - e.g. you can reuse some of the calculations, filtering parameters and style of writing.


Aaron Harrison

Principal Engineer at Logicalis UK


Please rate helpful posts...

Actions

This Discussion