How to run sql query for Partition dependencies

[ppellettiere]

I get this error when I try to delete a Partition for CCM 8.0. There are no dependencies shown.

"The  pkid column in the routepartition table in the database is being referenced from another table".

How can I run a query to see where the dependencies are?

Thank You.

[Carlo Poggiarelli]

Hi.

For partition dependency,I suggest you to use dependency records in cucm admin pages.

HTH

Regards

Carlo

Sent from Cisco Technical Support iPhone App

[eric.butcher]

Well, there might be no simple way to check all tables, but you could get a list of the tables that contain "fkroutepartition" from the data dictionary, and then use an SQL query for each table in the following format:

select * from where fkroutepartition=''

You need one of those queries for each table containing fkroutepartition.  Your best bet is numplan, so:

select * from numplan where fkroutepartition=''

You can get the PKID you're looking for with the following query:

select pkid from routepartition where name=''

Hope this helps you.

[ppellettiere]

Thank you that helps....

[William Bell]

I think Carlo's answer is spot on, but I also like Eric's approach (as I am a fan of direct querying of the db). So, +5 to Eric and Carlo.

To riff off of Eric's approach, you can find the tables which have links to the routepartition table using the following query:

run sql select t.tabname,c.colname from systables t, syscolumns c where t.tabid=c.tabid and c.colname='fkroutepartition'

You can actually run this query for any table. The conventioin to keep in mind is that when one table references the unique key of another table (pkid column) the reference column name is always fk. If you are referencing a type table, then the reference column name is always tk.

The query I have above is actually querying the Informix db system tables. This is a good way to get at key schema information without opening up that monster PDF (not devaluing the PDF, it is good - but when you understand the linkage - running a query to refresh yourself is way faster).

I put a blog together on this topic here:

http://ucguerrilla.com/2012/03/cucm-sql-queries-supplemental-informix.html

As Eric noted, once you identify the table relationships, then you can query the tables individually.

Using your example:

admin:run sql select s.tabname,c.colname from systables s, syscolumns c where s.tabid=c.tabid and c.colname='fkroutepartition'

tabname                  colname

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

callingsearchspacemember fkroutepartition

numplan                  fkroutepartition

cdr_deltab_000342        fkroutepartition

cdr_deltab_000398        fkroutepartition

We see that the PT is in some CDR tables (irrelevant here), callingsearchspacemember (the table that maps PT to CSS), and numplan (stores all patterns).

If you were interested in partition "My_PT" then your query to check the two relevant tables would be:

sql select n.dnorpattern from numplan n inner join routepartition rp on n.fkroutepartition=rp.pkid where rp.name='My_PT'

and on the CSS member table:

sql select css.name from callingsearchspace css inner join callingsearchspacemember csm on csm.fkcallingsearchspace=css.pkid inner join routepartition rp on csm.fkroutepartition=rp.pkid where rp.name='My_PT'

Now, going back to the original question. The dependency records feature is ideal because it is doing all of this query work for you.

I have a running series on various SQL queries at http://ucguerrilla.com/. Just in case you are interested.

HTH.

-Bill (http://ucguerrilla.com)

[Leonardo Santana]

Hello William,

Great info (+5)

Congrats for the UCGuerrilla ís very good!

Regards

Leonardo Santana

[eric.butcher]

The original post does say that it isn't showing up in dependency records.  This suggests that it is in fact a corrupt or improperly created row in the numplan table that is causing the problem.  I've seen phones do the same thing... they don't show up in dependency records or device searches because they're missing a device name, but they create a database dependency.

The solution is to query the database directly for the PKID, and once you have the PKID you can open the corrupt object in the relevent field by pasting the PKID up in the URL bar as a field.