Thursday, June 19, 2014

Using SQL to Reconfigure a Dial Plan - Updating Directory Numbers

I have had this blog entry in the draft folder for quite some time. I decided to dust it off and bring it to the front of the queue after receiving the following query on Twitter:


@ucguerrilla got one for you.  Trying to update 1xxxx and 3xxxx in pt-Internal to 401xxxxx and 403xxxxx... any idea of sql query? :)
Can you accomplish this via SQL? Why, yes you can. About a year ago I completely rebuilt a customer's dial plan using 100% SQL. While I won't be discussing the ins and outs of all of that in this entry. I do plan on getting into the mechanics of doing broad changes to digit patterns using SQL.

Primer

A brief primer is provided in the first blog of this series.

Disclaimer

That's right, there is a definite need to provide a disclaimer upfront. The stuff we are going to get into in this blog entry and others associated with this "sub-series" affect the entire system. If you do not know what you are doing or have any doubts in what is actually happening then you should consider pulling someone in who does have the appropriate comfort level. You could really foul up your system if you aren't careful and, as nice as I am, I am not going to claim responsibility if you do foul up your system. That will be 100% on you. 

If you aren't comfy with the queries we are going to crack open here then you can always rely on the Bulk Provisioning capabilities in UCM (particularly 7x and later). 

That said, if you have a good handle on what is happening then the methods I am going to discuss are very, very efficient. 

This Weeks Query

As I mentioned in the intro, I used an approach based 100% on SQL to convert a customer's dial-plan (8,000 phones / 16,000 DNs). We can't cover all of that in this one entry. So, we are going to jump right into how one can modify digit patterns on a global scale. We are going to use the example I received from Twitter as a base but will add a few other examples to provide some additional context.

The Moving Parts

If you have followed the SQL Query Series then you are probably familiar with the tables we are going to leverage:

  • Numplan: This table contains ALL digit patterns that can be programmed on the system. This includes directory numbers, route patterns, translation patterns, hunt pilots, etc.
  • RoutePartition: This table contains route partitions (go figure). This is a table referenced directly from numplan that can be used to help you narrow your SQL update command.
  • DeviceNumPlanMap: This table maps the device table to the numplan table. We will use this in a workaround to a common problem with the Informix Update syntax.
  • Device: This table contains all of the information concerning devices provisioned on the system. We will use this in a workaround to a common problem with the Informix Update syntax. Caution: This table contains more than phones, gateways, etc.

Measure Twice, Cut Once

If you have ever done work in a trade industry then it is likely that you have heard the term "measure twice, cut once". It means that if you make a cut without being positive about your measurements then you may have to throw away a whole lot of material. 

The same logic applies with the queries we are going to discuss. It is always a good idea to use a "select" query to double check your data set before you execute an update. You may need to run a couple of different "select" queries so that you are sure you can cleanly and uniquely quantify the dataset you want to update. In general, the from and where clause from the "select" queries you use to measure the dataset will likely be used when you use the "update" query(ies).

Limitations to Be Aware Of

Update queries are a little picky. Let's clear up one of the first issues you may come across when attempting to do complicated "update" queries. You may be inclined to use syntax similar to the following:

UPDATE table1
SET a.column1 = 'value'
FROM table1 a, table2 b, table3 c
WHERE a.key = b.key AND a.column1 like 'value%'


The objective being to use a composite dataset to uniquely identify the records you want to update. This will NOT work. The reason is due to the fact that the Informix DB used by Cisco is not the Extended Parallel Server (XPS) edition. The XPS edition does allow you to use the "from" clause in the manner shown. This would be really handy but, alas, it is not available to you. However, there are ways to reference table values. Though, they are not as clean and you have to be extra careful. We'll cover an example later.

The second thing to be aware of is that there is an upper limit to the number of records you can modify using an "update" query. Here is where I fail you because I don't know exactly where the cap is. I know that I was running an update query which should have affected 2,541 records and the query timed out with an error.  I have ran update queries for chunks slightly larger than 900 records without issue. What I haven't done is try to narrow it down. As a rule of thumb, I'd say that if the "select" query you use to measure is >1,000 records then you will want to come up with a way to deal with the data in smaller chunks. We'll cover this, too.


Example: Update as a Function of Route Partition

Going back to the tweet I mentioned at the beginning of this article we see that the request is to update patterns in a specific partition. In subsequent tweets exchanged with my colleague it became clear that there were actually multiple partitions that were of interest, the patterns had a predictable length, and the patterns all started with the same digit pattern. 

Before we dissect this further, I'd like to make a comment about "good design". It is a good thing that the requestor is able to easily articulate what parameters should be used to uniquely identify a set of patterns. Usually, this isn't the case and you may need to run several different "update" queries to get the job done. So, having a well thought out dial plan (not just numeric patterns but partitions, css, etc.) can go a long way to optimizing the number of moves you need to make. Food for thought.

Anyway, for our example let's assume the following:

  • The patterns we care about are 5-digits in length
  • The patterns start with either 1xxxx or 3xxxx
  • The patterns are in one of the following partitions: HQ_Lines_PT, DC_Lines_PT, or NYC_Lines_PT 
Measure

Let's "measure" this first by using a select query:


select d.name, n.dnorpattern
from device d
     inner join devicenumplanmap dmap on dmap.fkdevice=d.pkid
     inner join numplan n on dmap.fknumplan=n.pkid
where LENGTH(n.dnorpattern) = 5 and n.dnorpattern MATCHES '[13]*' and n.tkpatternusage=2 and n.iscallable='t'
order by d.name, n.dnorpattern

This is just one way to measure. My logic here is that I want to see the output and pay particular attention to the device names that are dumped. Let's say, for instance, that the DNs I care about are assigned to CTI Ports or CTI Route Points for use with a contact center application. I should recognize patterns that deviate from that device naming convention. If I see other devices (like real IP phones) in my query then I know that my selection criteria is flawed and I should not use it to do an update. 

Note that I am using two additional parameters in my "where" clause: tkpatternusage and iscallable. The former is a good way to avoid updating patterns for things like route patterns, MWI, translations, etc.. A tkpatternusage of "2" means that the pattern is assigned to a device. The "iscallable" means that the directory number is marked active. Not critical just a point of interest. 

Another measurement:
select d.name, n.dnorpattern
from device d
     inner join devicenumplanmap dmap on dmap.fkdevice=d.pkid
     inner join numplan n on dmap.fknumplan=n.pkid
     inner join routepartition rp on n.fkroutepartition=rp.pkid
where LENGTH(n.dnorpattern) = 5 and n.dnorpattern MATCHES '[13]*' and rp.name in ('HQ_Lines_PT', 'DC_Lines_PT', 'NYC_Lines_PT')
order by d.name, n.dnorpattern

Here we are also checking the partition assignments. Based on our assumed criteria, the above should basically be the dataset we are interested in updating. Again, I am showing the device to see if there are any anomalies. 

Other ways to measure would be to use a couple of "select" queries where we are dumping the record counts. This is handy when you are trying to optimize your query (i.e. remove unnecessary criteria in the where clause). You can also use the expected device values for the patterns you are interested in to see if you have something other than expected. For instance, let's say that all of the patterns you are interested in are assigned to CTI ports or CTI route points. If this were the case then you can use the following to check your data set.


/*First, get a count of patterns matching the criteria*/
select Count(n.pkid)
from device d
     inner join devicenumplanmap dmap on dmap.fkdevice=d.pkid
     inner join numplan n on dmap.fknumplan=n.pkid
     inner join routepartition rp on n.fkroutepartition=rp.pkid
where LENGTH(n.dnorpattern) = 5 and n.dnorpattern MATCHES '[13]*' and rp.name in ('HQ_Lines_PT', 'DC_Lines_PT', 'NYC_Lines_PT')

/*Next, get a count with the same query but focus on CTI devices*/
select Count(n.pkid)
from device d
     inner join devicenumplanmap dmap on dmap.fkdevice=d.pkid
     inner join numplan n on dmap.fknumplan=n.pkid
     inner join routepartition rp on n.fkroutepartition=rp.pkid
where LENGTH(n.dnorpattern) = 5 and n.dnorpattern MATCHES '[13]*' and rp.name in ('HQ_Lines_PT', 'DC_Lines_PT', 'NYC_Lines_PT') and (d.tkmodel=72 or d.tkmodel=73)

/*Finally, let's use a query that is more in-line with our anticipated Update query syntax*/
select Count(n.pkid)
from numplan n
 inner join routepartition rp on n.fkroutepartition=rp.pkid
where LENGTH(n.dnorpattern) = 5 and n.dnorpattern MATCHES '[13]*' and rp.name in ('HQ_Lines_PT', 'DC_Lines_PT', 'NYC_Lines_PT')


Basically, we are looking for anomalies in the counts. Especially in the last query which is "closest" to what we want to use when performing the actual update. There are many ways you can measure your dataset. Some general rules of thumb:

  • Try to find a query that has the fewest number of inter-table dependencies. For instance, in our last "Count" query we are only focusing on numplan and routepartition.
  • When dealing with route partitions, make sure you don't have patterns that are assigned to the "none" partition. Inner joins will exclude these patterns (which means you should probably check specific route partitions in your queries).
  • Similar to the 2nd rule, inner joins assume there are reference values shared between the joined tables. A pattern not assigned to a device won't show up in an inner join. Which means you could accidentally update it in an "update" query (where you can't rely on inner join to protect you).


Cut

Enough of this damn measuring, let's get to cuttin'. For sake of argument, let's assume our measuring has proven that our assumptions are accurate. This means we only need to test for pattern length, the first digit of the pattern, and the partition assignments. The following gets us where we need to be:


UPDATE numplan 
SET dnorpattern = (CONCAT('24010', dnorpattern)) 
WHERE ((fkroutepartition IN (SELECT pkid FROM routepartition rp WHERE rp.name IN ('HQ_Lines_PT', 'DC_Lines_PT', 'NYC_Lines_PT')) AND (LENGTH(dnorpattern)=5) AND (dnorpattern MATCHES'[13]*'))

So, what is happening here. The "set" command is merely prefixing "24010" in front of the existing dnorpattern. So, a pattern of 10500 becomes 2401010500 and 30500 becomes 2401030500. That is the purpose of the CONCAT function it concatenates strings. The "where" clause is helping us control the dataset that gets updated. We are using the fkroutepartition field (which is in the numplan recordset) and are testing for a specific set of pkid values in the actual routepartition table. That is the function of "IN". It looks in a delimited set of values.

Things get weird here. We then have a select in the middle of a where clause. Totally permitted. Again, we are using the IN clause (cuz we are lazy) and the human readable names of the target partitions. We don't stop there because we have other criteria (hence the various "AND" operators). We are checking the length of dnorpattern because we were told that is somehow special and we are using the MATCHES operator to do a wildcard search. You could also use a logical OR with the LIKE operator, for example:  AND (dnorpattern LIKE '1%' OR dnorpattern LIKE '3%') .


Example: Update as a Function of a Device Field

As noted earlier, the version of Informix we are using on Cisco UCOS platforms does not have the XPS feature set. Therefore, "update" queries are unable to use a "from" clause. This handicaps us a bit. In most cases, I find that using the pattern length, pattern wildcard, and the routepartition covers most of my needs. However, sometimes it may be easier to use a field in a table that is not directly linked to the numplan table. For example, what if we wanted to use the device table for our criteria?

Measure

I am not going to go into the "measuring" approach here. The point should be solidly implanted in your noodle by now. Just make sure you use "select" queries to test your dataset before you update anything.

Cut

Let's continue with our example above but let's change the assumptions a bit:
  • The patterns we care about are 5-digits in length
  • The patterns start with either 1xxxx or 3xxxx
  • The patterns are all assigned to devices where the name starts with LCP555
A possible solution to this problem would be:

UPDATE numplan 
SET dnorpattern = (CONCAT('24010', dnorpattern)) 
WHERE (pkid IN (SELECT n.pkid FROM numplan n, devicenumplanmap dmap, device d WHERE n.pkid=dmap.fknumplan AND d.pkid=dmap.fkdevice AND d.name LIKE 'LCP555%')) AND (LENGTH(dnorpattern)=5) AND (dnorpattern MATCHES'[13]*')

Like the previous example, we are using the "IN" operator. Only this time we are checking the pkid from the numplan record against a list of numplan pkid values in a composite recordset. Probably not the fastest query in the world but still pretty efficient when compared to relying on the GUI.

You could also drop the pattern tests (length and matches) if you were 100% certain about the device name association. In the spirit of full disclosure, I have not used this particular method on a live data set (I have used it in lab clusters). So, I don't know if there is a different recordset size constraint than what I have previously seen. I find that I can usually leverage the routepartition table to get where I want to go.


Example: Large Dataset

Earlier I noted that I have ran into a ceiling when running update queries. The first time that happens I guarantee you that your inner Homer will scream "Doh!". If you are running this from the CLI, you will see a blinking cursor for what seams like forever. If you are using an application that leverages the AXL/SOAP API then you see whatever hourglass, beach ball, or other cute 'wait for it...' icon is displayed. Then you will see an error complaining about too many rows in resulting record set (or something like that). 

Measure

Let's emphasize a previous guideline: definitely check the count of the records returned by your "select" queries. Adjust if your recordset is larger than 1,000 records. Note that this count is just a "best guess" on my part (see my notes earlier in this article).

Cut

Not all is lost. There are many ways to break record sets into smaller chunks. In the dial plan change I am referencing, I had to modify every Directory Number from a 5-digit pattern to an E.164 pattern. The customer owned the entire 10,000 block of a DID range. 

One way to tackle the data is to go after the "even" numbers first and then the "odd" numbers:


/*EVENS*/
UPDATE numplan 
SET dnorpattern = (CONCAT('\+120255',dnorpattern)) 
WHERE dnorpattern like '5%' AND LENGTH(dnorpattern)=5 AND SUBSTRING(dnorpattern FROM 5) MATCHES '[02468]' AND tkpatternusage=2


/*ODDS*/
UPDATE numplan 
SET dnorpattern = (CONCAT('\+120255',dnorpattern)) 
WHERE dnorpattern like '5%' AND LENGTH(dnorpattern)=5 AND SUBSTRING(dnorpattern FROM 5) MATCHES '[13579]' AND tkpatternusage=2 AND iscallable='t'

If that doesn't get it for you then you can break it down by looking at the second digit. For instance (note that we are looking at patterns like '51%' in the following examples):

/*EVENS*/
UPDATE numplan 
SET dnorpattern = (CONCAT('\+120255',dnorpattern)) 
WHERE dnorpattern like '51%' AND LENGTH(dnorpattern)=5 AND SUBSTRING(dnorpattern FROM 5) MATCHES '[02468]' AND tkpatternusage=2


/*ODDS*/
UPDATE numplan 
SET dnorpattern = (CONCAT('\+120255',dnorpattern)) 
WHERE dnorpattern like '51%' AND LENGTH(dnorpattern)=5 AND SUBSTRING(dnorpattern FROM 5) MATCHES '[13579]' AND tkpatternusage=2


Basically, we are using the SUBSTRING function to look at the last digit in a 5-digit number. More accurately, we are looking at the last character in a 5 character string because that is how digit patterns are stored in the numplan table (as strings).


Closing Notes

One of the questions you may ask is "how long does this take". On my lab system, which is a UCS 200M1, record sets of 300 - 400 records take 1.5 - 2 minutes. Record sets of 800 - 900 records take about 8 minutes. I also tested on a UCS210M2 and the 300 - 400 records took about 1 minutes. The larger set (800 - 900) took about 4 minutes. 

The number of permutations around updating records in the manner described are many. So, you must be extremely careful before you pull the trigger on an update query. If at all possible, test your queries on a lab system until you are comfortable with what you are doing. When doing multiple changes (like updating an entire dial plan) I recommend having your queries thought out before hand. Then you can run them as a batch.



Thanks for reading. If you have time, post a comment!

7 comments:

  1. I have this exact issue at work right now. I've been asked to convert us from a 4 to 6-digit dial plan for about 2000 phones. I tried to use the old CSV -> Excel -> Access BAT method but I ended up with a CSV file with 1200 columns. Cisco really needs to address this.

    I haven't worked with SQL since college but this looks totally doable. I've got a lab setup I can use to do some testing but I think you might have solved my issue.

    ReplyDelete
    Replies
    1. Glad I could help. I have been pondering the idea of building a tool to do these as my company specializes in dial-plan design, redesign, and optimization. If you get to a point where you would like some assistance then let me know. My company does this a lot and we are very good at deploying new designs on live systems with minimal disruption.

      You can find my email on the "About Me" page.

      -Bill (@ucguerrilla)

      Delete
  2. Awhile ago I wrote .net sql tool so you can query and just paste in a whole list of queries and wait for it to complete. Learned some cool stuff from your entry. When I would do sql queries I always had a link for each DN I wanted to change. Good stuff here.

    @CollabSensei

    ReplyDelete
  3. Hi William,

    I am looking for a quick way to discover Partitions and CSS that are unused (Quicker than dependency records). I'm not familiar with SQL queries. Is that possible?

    Regards,

    ReplyDelete
    Replies
    1. You can get this information but not with a single query. These tables cross-referenced by many other tables. That said, since you are looking for unused, it may be worthwhile to create multiple queries that you run in batch and then use AXL/SOAP toolkit to run the queries.

      I'll think on this a little more but the above represents my current answer.

      -Bill

      Delete
  4. Hi Bill,

    Thanks for the great articles.

    Are you aware of any updates to the maximum records we can update per query? Is the limitation related to the number of records, or is it a time out issue?

    We have updated 800 DN's in about 20s. Looking to update 5k at at a time.

    ReplyDelete
  5. I am trying to update the calledpartytransformationmask of a specific translation pattern (17211). Is this how you would do the update?

    UPDATE numplan
    SET calledpartytransformationmask = (CONCAT('new number',calledpartytransformationmask))
    WHERE dnorpattern=17211 AND tkpatternusage=3

    ReplyDelete