documentsn.com

Chapter 2Plan Files and TablesPlan TablesThere are two types of .txt format plan files: Complete—Contains all of the schema of tables and columns described in External Tables. Scriptswritten on complete .txt format plan files do not have to verify columns and tables are present beforeoperating on them. Simple—Contains a subset of the complete format, eliminating the following unnecessary tables andcolumns:– All empty tables.Example: LSPs table with no entries.– All columns that contain only default values.Example: Empty Protected values in the Nodes table default to F. So if all values in thecolumn are F, it is not included in the .txt format plan.This format is smaller and easier to edit manually. However, it can be more difficult for a scriptto parse because the script must check for missing tables and columns. We do not recommendthis format when using scripts.Convert Between Plan File FormatsThe GUI and CLI use the .pln and .txt formats interchangeably. When you save a file from the GUI, the.pln format is the default. To save to a different format, choose File Save as.From the CLI, the format of a saved file depends on the extension of the output filename. A .plnextension produces a .pln format file and a .txt extension produces a .txt format file. A command-lineargument (-simple-txt-out-file) specifies the simple .txt format plan.To convert one format to another, use the mate convert CLI tool. This tool can convert formats withinthe current software version, or from an earlier version to the current version.Plan TablesAll aspects of a plan are defined using a collection of tables. Developers can access and modify all tablesusing command-line tools. For a complete listing of tables and their columns, see the CARIDEN HOME/docs/table schema.html file.Plan Table ColumnsThe columns in a plan file table contain data in one of the following categories: Key columns—Columns that uniquely identify the rows of the table. Each table has one or more keycolumns. For example, the Nodes table has one key column, Name, which is the unique name ofthe node. The Interfaces table has two key columns: Node and Interface. This pair must (jointly)be unique for all entries in the table. Another example is the Demands table, which has keycolumns: Name, Source, Destination, and ServiceClass. If there are two demands from the samesource to the same destination with the same service class, they must have different names.In the table schema.html file, key columns are highlighted in orange. Plan columns—Columns that define or configure properties of entries in a table. For example, in a Nodes table, the Site column specifies the site that contains the node, and is therefore a plancolumn. Key columns are always plan columns.Cisco WAE Design 7.0.1 Integration and Development Guide2-2

Chapter 2Plan Files and TablesPlan TablesIn the table schema.html file, plan columns are highlighted in blue. Derived columns—Columns that provide information that is derived from the plan columns in thesame table or a different table. These are not stored in plan files, but are generated by the GUI whenthe tables are displayed, or by table extract when the tables are extracted from the plan file. Forexample, Remote Node in the Interfaces table is derived by looking up the remote node for theinterface as defined in the Circuits table. Some derived information can be more complex toobtain. For example, the Traff Sim column is a derived column that is the result of a simulationperformed on the network.The entries in tables generated in the GUI and from table extract can depend on somepre-specified parameters. For example, the Interfaces table Traff Meas column is the measuredtraffic on that interface for a specified traffic level. For a particular QoS selection the column can beoverall (undifferentiated) traffic, traffic on a particular queue, or traffic for a particular service class.In the table schema.html file, derived columns are highlighted in gray.Table ObjectsObjects in WAE Design are represented by rows in tables, and object properties are represented bycolumn entries in that table, or by entries in tables of related objects. For example, LSP objects aredefined in the LSPs table. Columns in this table, such as Setup BW, define properties of each LSP. Thepaths of each LSP are also properties of the LSP, but those LSP paths are defined as objects in separateLSP Paths, Named Paths, and Named Path Hops tables.Table 2-1 lists the notation that WAE Design uses to specify a plan object when the type of object is notknown from the context. Except for the IP address, these notations have a one-to-one mapping with keycolumns for each object.Table 2-1Single Object NotationObjectFormatASAS{ASN}Circuitct{NodeA InterfaceA NodeB InterfaceB}Example: ct{atl POS1/1/1 sjc POS1/10}External endpointEP{Endpoint}Example with a member: EP{100}:cr1.chiInterfaceif{Node Interface}IP addressip{ipaddress}Can reference multiple objects of the same or different type. WAE first tries to find a node with thisloopback IP. If it is not found, WAE looks for a matching interface. If there are multiple matches, WAEuses the first one it finds.Layer 1 circuitl1ct{L1NodeA L1NodeB Name}Layer 1 circuitpathl1ctp{L1NodeA L1NodeB L1Circuit PathOption}Layer 1 linkl1lnk{L1NodeA L1NodeB Name}Layer 1 nodel1nd{Name}Layer 1 portl1pt{L1Node L1Port}Nodend{Name}Cisco WAE Design 7.0.1 Integration and Development Guide2-3

Chapter 2Plan Files and TablesWorking with TablesTable 2-1Single Object Notation (continued)ObjectFormatPortpt{Node Port}Port circuitpct{NodeA PortA NodeB PortB}Sitest{Name}SRLGsrlg{Name}Table Schema and Plan TablesIn a text plan file, each table starts with TableName to identify the name of the table; for example, Nodes .The first row of the table body contains the column headings, followed by rows that describe the tableentries. The order of the columns is irrelevant, and only the key columns must be present.Each plan table is defined using an excerpt from the database schema, the part that defines that table. Forexample, Table 2-2 lists a table schema excerpt for the NamedPaths table. Table 2-3 shows anexample of a NamedPaths table that has been populated within a plan file.In Table 2-3, the first column is Name, which is described in the first row of Table 2-2. In this case, theName of the named path (PathA or PathB) is the same in the plan file and GUI, it’s the name of the path,has a data type of text, and is a table key. Being a key means the Name column is among the columnsthat uniquely define a row. In this case, the Name and Source together define a unique row.Table 2-2NamedPaths Table SchemaPlan FilenameUser Interface Name DescriptionData TypeCategoryNameNameName of the pathtextkeySourceSourceName of the source of the NamedPathtextkeyActiveActiveIs the path active?booleanplanResolvedResolvedT if all hops in path are resolved in plan, F if not, na if nohops.booleanderivedTable 2-3 NamedPaths TPathBRouter1.AcmeOTTWorking with TablesThis section describes how to modify plan tables from the CLI or GUI, or how to use table files asarguments for command-line tools.Cisco WAE Design 7.0.1 Integration and Development Guide2-4

Chapter 2Plan Files and TablesWorking with TablesEdit Plan File Tables Using CLI ToolsYou can add, modify, or delete tables in a plan file. Basically, you extract the desired table, edit theextracted file, then replace the table in the plan. Changes to columns of type Derived are ignored whenreplacing a table. Only columns of type Plan are relevant for changes.The replacement tool does not validate the changed table, so the resulting plan could be incomplete orincorrect. Such problems are flagged the next time the plan is opened in WAE Design.Add or Delete Table in a Plan Add the table to the plan using table replace. This overwrites an existing table if one exists. Delete a table from a plan using table delete.Change Table in a PlanStep 1Extract the table from the plan using table extract and save it to a .txt format file.Step 2Change the data in the table using in one of the following tools:Step 3 Apply changes with a simple SQL statement by using table edit. Apply changes with a complex SQL statement by using mate sql. Apply changes manually using Excel or a text editor.Replace the table in a plan file with the modified table by using table replace.You normally replace the table in the original plan file, but the result of table replace can be a differentfile or even a different plan format. For example, you could replace a table in a text format plan and savethe result as a binary format file (.pln), leaving the original file unchanged.Example of a Table Modification Using CLI ToolsThis example shows how to increase the IGP Metric for interfaces on core routers in the us wan plan,which is in the CARIDEN HOME/samples directory. Figure 2-1 shows an excerpt from the original planfile. The Interfaces table has three columns: Node, Interface, and IGPMetric, which are all either “key”or “plan” columns. You only need the “key” values to uniquely define an interface.Figure 2-1Interfaces Table in us wan.txtIn this simple example, you could manually update the IGPMetric column, using Excel or another texteditor. To script the process, use CLI tools.Cisco WAE Design 7.0.1 Integration and Development Guide2-5

Chapter 2Plan Files and TablesWorking with TablesStep 1Extract the Interfaces table from the plan file to a temporary file, if-table.txt.table extract -plan-file us wan.txt -out-file if-table.txt -tables InterfacesFigure 2-2Extracted Interfaces TableFigure 2-2 shows an excerpt from the extracted Interfaces table. The interesting part of the extractedtable is that it contains more than just “key” and “plan” columns, it also has “derived” columns, whichWAE Design creates when it reads a plan file or performs simulations. These extra columns make it iseasier to identify the core routers in this example because there is now a Function column.Step 2The next step is to increase the IGP Metric for core router interfaces. The command below reads theextracted Interfaces file, if-table.txt, finds the IGPMetric column of the Interfaces table, filters therows to those with ‘core’ in the Function column, adds 100 to the IGPMetric in the filtered rows, andsaves the result to a new file named if-table-edited.txt.table edit -plan-file if-table.txt -out-file if-table-edited.txt -table Interfaces -columnIGPMetric -rowfilter "Function 'core'" -value IGPMetric 100Figure 2-3Interfaces Table After ReplacementFigure 2-3 shows an excerpt from the updated file, if-table-edited.txt, which has the expected resultin the IGPMetric column: the four core routers (nodes) shown have their IGP metrics increased by 100,and the edge router metric is unchanged.Step 3The last step is to update the original plan file, or to create a new one. In this case, the example commandcreates a new plan file, one that uses the .pln format. Changing the file format in this example shows theflexibility of the tools.table replace -table-file us wan.txt -replace-table-file if-table-edited.txt -out-fileus wan.plnStep 4To verify the updates, open the us wan.pln file. The GUI table contains the information shown inFigure 2-3.Cisco WAE Design 7.0.1 Integration and Development Guide2-6

Chapter 2Plan Files and TablesWorking with TablesEdit Plan File Tables Using the GUIAlthough you can edit plan files in the WAE Design GUI, we recommend that you edit them in a texteditor or spreadsheet so that you can easily make global changes. A spreadsheet, such as Excel, is usefulbecause it clearly shows table columns in a tabular format.Any time you make a change in the WAE Design GUI and save the plan file, you are, in effect, changingthe tables in the plan. Additionally, you can choose Edit Plan Tables as Database in the WAE DesignGUI to view and edit tables directly in the plan file. In this dialog box, you can select one or more rowsto modify attribute values in the columns. You can specify the desired selection from the Advanced Filtertab, by entering SQLite queries, and specify value changes in the Set Value tab, also using SQLitesyntax.Values defined in one table might be referenced in other tables. Be careful when changing a value thatoccurs in multiple places. For example, the Nodes table identifies the sites in which nodes reside. If youchange a site name in the Sites table, you must also change it in the Nodes table and all other instances.Step 1Choose Edit Plan Tables as Database. The Plan Table Database Editor opens, showing all possibletables in the left pane.Step 2In the left pane, select the desired table. If the table already exists in the plan, it is displayed.Step 3Optional: Filter the table entries by using the Filter control menu or by entering text in the Search field(top right).Step 4Click the field you want to change and then edit the information, or choose one of these more advancedediting options. Click the Set Value tab, choose the Column to modify, enter an SQL Lite expression that specifiesthe change, and click Set. Click the Advanced Filter tab, enter an SQL Lite expression that specifies the change, and clickFilter.Step 5Visually verify that the change is correct.Step 6To open the new plan, click Open in WAE Design. To save the new plan without opening it, click Saveas File.Cisco WAE Design 7.0.1 Integration and Development Guide2-7

Chapter 2Plan Files and TablesWorking with TablesError Handling When Opening Edited PlansBecause plan files can be edited directly by the user, errors must be handled when opening plan files.The default behavior for table errors is to log a warning and ignore any row of any table that isinconsistent with information already processed. For example, if the Nodes table lists two nodes withthe same name (which is a key column) the second row is ignored.To simplify user editing of .txt format plan files, WAE Design gracefully handles common problemsencountered during the opening process. For example, although the simplest plan can contain nodes,interfaces, and circuits, there are certain situations in which only an Interfaces table need be specified.WAE Design makes the following changes when opening a plan with missing or incomplete information: Omitted tables are assumed to be present with no entries. Key columns are required for each row in a table. Other columns can be entirely omitted, and defaultvalues are assumed for all their entries. The default values are listed in the Table Format Reference. Individual entries that are left blank (in non-key columns) are filled in with default values. Any nodes created in the Node column of the Interfaces table are created in the Nodes table ifthey do not already exist. The columns Remote Node and Remote Interface in the Interfaces table are derived columns,which are normally ignored when WAE Design opens a plan. However, in this case, when the Node,Interface, Remote Node, and Remote Interface unambiguously identify another interface as theremote interface for this circuit, and the two interfaces are not already defined as belonging to anycircuit in the Circuits table, then a circuit connecting these two interfaces is created. In the Nodes table, if the site entry is blank, a new site is created for this node with the same nameas the node. Sites referenced in the Nodes table are added to the Sites table if not alreadypresent. If Node and Interface in the InterfaceTraffic table are both blank, and the (derived) columnIPAddress in the table contains a valid reference to an interface in the Interfaces table, then(Node, Interface) for that interface is entered and the traffic is associated with that interface. Thisallows an InterfaceTraffic table to be added to the plan that references traffic measurements onlyby IP Address. If Source in the LSPTraffic table is blank, and the (derived) column SourceIP contains a validreference to a node, and this Node and the Name entry uniquely identify an LSP, then that node nameis entered into the Source column and the traffic is associated with that LSP.Tables as Command ArgumentsMany CLI tools take arguments that require the specification of a set of plan objects. For example,merge nodes takes a nodes-table parameter that specifies the list of nodes to merge. Such a list can bespecified in a file containing a table in the WAE Design table format. Tables used as arguments mustcontain at least the key columns defined for that table for the tool to uniquely match the objects in theimport table with the objects in the plan.To create tables for use as command-line tools arguments, follow these steps:Step 1Extract the full table of objects from the plan that is the target of the command-line tool, usingtable extract.Cisco WAE Design 7.0.1 Integration and Development Guide2-8

Chapter 2Plan Files and TablesWorking with TablesStep 2Use an editor to select the appropriate rows in the table, and delete the rest. Or, typically in a script, usemate select to select the rows using SQL syntax.LSP Mesh Creation ExampleTo create an LSP mesh between the core routers in sites ATL, MIA, and WDC of the us wan.txt plan,you must first create a file that contains the list routers in the mesh. The following example extracts theNodes table from the plan file, and then selects the desired core routers, and saves the result aslsp nodes.txt.Step 1Extract the Nodes table from the plan file.table extract -plan-file us wan.txt -out-file nodes-table.txt -tables NodesStep 2Select the desired core routers and save the result as lsp nodes.txt.mate select -table-file nodes-table.txt -out-file lsp nodes.txt -table nodes -filter"(Site LIKE 'ATL' OR Site LIKE 'MIA' OR Site LIKE 'WDC') AND Name REGEXP '.*cr.*'"-show-columns NameNoteThe SQL query uses LIKE, rather than , when selecting sites to avoid case-insensitivity problems.The resulting lsp nodes.txt file contains the following list of nodes: nodes Namecr1.atlcr1.miacr1.wdccr2.atlcr2.miacr2.wdcStep 3Create the LSP mesh by invoking the lsp mesh creator tool, specifying the lsp nodes.txt file as acommand-line argument.lsp mesh creator -plan-file us wan.pln -out-file us wan lsps.pln-source-nodes-table lsp nodes.txt -dest-equals-source trueSQL Queries in WAE DesignThree command-line tools in WAE Design use SQL syntax for filtering, summarizing, and manipulatingplan files and reports. mate select—Filterstables in the reports. mate summary—Summarizes tables in the reports, primarily to provide summary data for networkvisualization over time. mate sql—Anadvanced SQL query tool.WAE Design uses the SQLite implementation of the SQL language (see www.sqlite.org, especiallywww.sqlite.org/lang.html).Cisco WAE Design 7.0.1 Integration and Development Guide2-9

Chapter 2Plan Files and TablesUser-Defined Columns and TablesThe following operators have special meaning in WAE Design:REGEXP—Case-insensitivematching of regular expressions. For example, SQL expressionName REGEXP ' cr'Is true for Name equal to 'CR', 'Cr' or 'CR01'. (But not for Name equal to 'er.cr')MATCH—Some columns in the tables contain semicolon-delimited lists, for example a list of tags in theTags column of the Nodes and Interfaces tables, or the list of interfaces in the Actual Path column of theLSP table. The MATCH operator tests for membership in these lists. For example,Tags MATCH 'Europe'Is true for Tags equal to 'Asia;Europe', 'EUROPE', and so on. The matching is case-insensitive.The operator ' ' is case-sensitive in SQL. The operator LIKE, which is case-insensitive, is often moreuseful for plan schema tables because the case is never relevant. SUBNET—Selects rows if the fields look like IP addresses and are in a specific subnet.This function has the following syntax options:– SUBNET(Column Name, 'ip address/prefixlen')– SUBNET(Column Name, 'ip address/netmask')– SUBNET(Column Name, 'ip address', 'prefixlen')– SUBNET(Column Name, 'ip address', 'netmask')The following examples demonstrate usage of each syntax:– SUBNET(Column Name, '192.168.1.0/24')– SUBNET(Column Name, '192.168.1.0/255.255.255.0')– SUBNET(Column Name, '192.168.1.0', '24')– SUBNET(Column Name, 'ip address', '192.168.1.0, '255

.pln format is the default. To save to a different format, choose File Save as. From the CLI, the format of a saved file depends on the extension of the output filename. A .pln extension produces a .pln format file and a .txt extension produces a .txt format file. A command-line argument (-simple-txt-out-file) specifies the simple .txt format .