CUCM 6.13 - How to get list of phones on CallFwdAll

Version 1
    This document was generated from CDN thread

    Created by: Keith Lunn on 03-06-2009 12:18:35 AM
    Hello,
    We had  a sql we used to execute on 4.13 that gave us a list of phones on CallFwdAll and where they were call forwaded to.
    I packaged the sql request in the Java sql toolkit 6.13 and ran it but it gives a sytax error.
     
    The xml input file for the java sql toolkit:
     
    XML Input file:
    <?xml version="1.0" encoding="UTF-8"?>
    <!--DTD generated by XMLSPY v5 rel. 4 U (http://www.xmlspy.com)-->
    <!DOCTYPE data [
     <!ELEMENT data (sql+)>
     <!ELEMENT sql EMPTY>
     <!ATTLIST sql
     query CDATA #IMPLIED
      update CDATA #IMPLIED
    >
    ]>
    <data>
      <sql query="select n.dNorPattern as 'pattern',rp.name as 'routePartitionName',n.cFAVoiceMailEnabled as 'callForwardAll.forwardToVoiceMail',
     n.cFADestination as 'callForwardAll.destination'
      from NumPlan as n
     inner join RoutePartition as rp on rp.pkid = n.fkRoutePartition
     where (rp.name like 'CL_DNPT%') and ((n.cFAVoiceMailEnabled=1)or(n.cFADestination != ''))"/>
    </data>

    Here's my execution run:
     
    D:\Data\TekUCOMM\CUCM6x\Programming\axlsdk>java -cp .\classes;.\lib\saaj-api.jar
    ;.\lib\saaj-impl.jar;.\lib\mail.jar;.\lib\activation.jar;.\lib\jaxm-api.jar;.\li
    b\jaxm-runtime.jar;.\lib\xercesImpl.jar;.\lib\xml-apis.jar AxlSqlToolkit -username=****** -password=****** -host=somePub -input=phonescallFwdAll.xml

    select n.dNorPattern as 'pattern',rp.name as 'routePartitionName',n.cFAVoiceMail
    Enabled as 'callForwardAll.forwardToVoiceMail',  n.cFADestination as 'callForwar
    dAll.destination'   from NumPlan as n   inner join RoutePartition as rp on rp.pk
    id = n.fkRoutePartition   where (rp.name like 'CL_DNPT%') and ((n.cFAVoiceMailEn
    abled=1)or(n.cFADestination != ''))
    *****************************************************************************
    Sending message...
    ---------------------
    <SOAP-ENV:Envelope xmlns:SOAP-ENV="[url=http://schemas.xmlsoap.org/soap/envelope/"><S]http://schemas.xmlsoap.org/soap/envelope/"><S
    OAP-ENV:Header/><SOAP-ENV:Body><executeSQLQuery sequence="1243987881327"><sql>se
    lect n.dNorPattern as 'pattern',rp.name as 'routePartitionName',n.cFAVoiceMailEn
    abled as 'callForwardAll.forwardToVoiceMail',  n.cFADestination as 'callForwardA
    ll.destination'   from NumPlan as n   inner join RoutePartition as rp on rp.pkid
     = n.fkRoutePartition   where (rp.name like 'CL_DNPT%') and ((n.cFAVoiceMailEnab
    led=1)or(n.cFADestination != ''))</sql></executeSQLQuery></SOAP-ENV:Body></SOAP-
    ENV:Envelope>
    ---------------------
    ERROR: A syntax error has occurred.
    ---------------------
    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" SO
    AP-ENV:encodingStyle="[url=http://schemas.xmlsoap.org/soap/encoding/"><SOAP-ENV:Heade]http://schemas.xmlsoap.org/soap/encoding/"><SOAP-ENV:Heade
    r/><SOAP-ENV:Body><SOAP-ENV:Fault><faultcode>SOAP-ENV:Client</faultcode><faultst
    ring>A syntax error has occurred.</faultstring><detail><axl:Error xmlns:axl="htt
    p://www.cisco.com/AXL/API/1.0"><axl:code>-201</axl:code><axl:message>A syntax er
    ror has occurred.</axl:message><request>executeSQLQuery</request></axl:Error></d
    etail></SOAP-ENV:Fault></SOAP-ENV:Body></SOAP-ENV:Envelope>
    ---------------------
     
     
     
    Can anyone tell me why there is a syntax error? is it an issue with Informix sql? or is it because of table/field changes in 6.13?
    MY bottom line is I am trying to get a list of phones that are on Call FwdALL and what number they are on Call Forwarding to?
    Thanks for any help!!!
    K
     

    Subject: RE: CUCM 6.13 - How to get list of phones on CallFwdAll
    Replied by: David Staudt on 03-06-2009 04:30:05 AM
    Two big changes are effecting the query:
     
    - The underlying database has changed from MS SQL to IBM IDS - there are some SQL syntax differences http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?S_TACT=105AGX28&S_CMP=DLMAIN
    - The database schema changed significantly:  the cfa information was broken out into a callforwardynamic table for performance reasons
     
    I think the query you want would look something like this:
     
    select numplan.dnorpattern, routepartition.name, callforwarddynamic.cfavoicemailenabled, callforwarddynamic.cfadestination
    from numplan,routepartition,callforwarddynamic
    where routepartition.pkid = numplan.fkroutepartition and
        callforwarddynamic.fknumplan = numplan.pkid and
        routepartition.name like 'CL_DNPT%' and
        (callforwarddynamic.cfavoicemailenabled = 't' or callforwarddynamic.cfadestination <> '')

    Subject: RE: CUCM 6.13 - How to get list of phones on CallFwdAll
    Replied by: Keith Lunn on 03-06-2009 11:25:38 PM
    Thanks David!!!!!!!
    Is there docs that show the 6.13 schema and new table relationships with keys? I have looked everywhere on cisco.com and developer.cisco.com and as close to it I got was the 6.01 Data dictionary (which is not indexed correctly and almost impossible to navigate).
    Thx,
    Keith

    Subject: RE: CUCM 6.13 - How to get list of phones on CallFwdAll
    Replied by: David Staudt on 04-06-2009 12:13:42 AM
    The 6.0(1) Data Dictionary is what we have...can you elaborate on what better indexing would be?  Maybe an example from elsewhere..?

    Subject: RE: CUCM 6.13 - How to get list of phones on CallFwdAll
    Replied by: Keith Lunn on 04-06-2009 01:11:24 AM
    Hi David, actually I just checked, the Data Dictionary pdf doc i found was for 6.1.1. The problem with the pdf doc is that on the page 2 that lists all the tables, each table name reference is supposed to have a link so you can click on the table name and you are suppose to jump to the location in the doc that explains the table - however, I am finding that alot of the links don't work and the doc is over 600+ pages.....
    kinda a pain when I'm trying to look up information.
    Im also want to understand what fields are keys and how they are linked to other tables.
    Im sure the info is in their - its just not presented in a clear fashion (at least for me).
    Hey thx for the link to IBM IDS - Im looking at it right now and reviewing thier dialect of SQL!!!

    Keith