SQL Examples

Three SQL examples are provided to help explain the structure of the CWM database and to help you develop customized applications. These examples are intended to convey information about the interrelation of objects within the database. The three SQL examples perform the following tasks:

Retrieve bytes transmitted statistics for a specified Frame Relay connection
Retrieve cells received statistics for a specified ASI port
Retrieve a list of all nodes in the network and related information

The three examples included here do not do all the necessary validity and error checking required in a production environment since this level of detail would obscure the objective of the examples. None of the examples write or modify the database so there is little risk of data loss if these examples are used. These examples are unsupported, but have been tested with no negative results.

Note These examples are probably not the optimal or recommended way of extracting database information, but do demonstrate how you can navigate the tables to retrieve the desired information.

You can download the examples from http://stratasphere.cisco.com/SQLExamples.htm (to avoid keystroke errors).

Frame Relay Bytes Transmitted

This example program extracts all of the bytes transmitted statistics for a specific frame relay private virtual circuit (PVC) from the CWM Informix database.

#include <stdio.h>
#include <time.h>
$include sqlca;
 
#define SQL_STATUS sqlca.sqlcode
 
#define OUTPUT_ON
 
/* Network and endpoint definitions for a frame relay connection */
/* from rio:11.4.200 to hongkong:9.4.100 in the "Demolab" network */
 
$define NETWORK         "Demolab";
$define LOCAL_NODE      "rio";
$define REMOTE_NODE     "hongkong";
$define LOCAL_SLOT      11;
$define REMOTE_SLOT     9;
$define LOCAL_CHAN      4;
$define REMOTE_CHAN     4;
$define LOCAL_DLCI      200;
$define REMOTE_DLCI     100;
 
/* Statistic type and measurement interval (bucket) definitions */
$define BYTES_TRANS     11;             /* Bytes transmitted */
$define BUCKET                  30;             /* 30 minute measurement interval */
 
 
main()
 
{
$short p_netw_id=-1;
$int p_l_node_id=-1;
$int p_r_node_id=-1;
$int p_con_obj_id=-1;
$int p_totald=-1;
$int p_peak=-1;
$int p_timestamp=-1;
 
time_t timest;
 
        /* Choose the stratacom database */
        $database stratacom;
 
        /* Retrieve network ID based upon network name */
        $select netw_id into $p_netw_id from network where netw_name=NETWORK;
 
        if( SQL_STATUS < 0) exit(1);
 
        /* Retrieve LOCAL PVC endpoint node ID based upon network ID and node name */
        $select node_id into $p_l_node_id from node where (netw_id=$p_netw_id and node_name=LOCAL_NODE);
 
        if( SQL_STATUS < 0) exit(1);
 
        /* Retrieve REMOTE PVC endpoint node ID based upon network ID and node name */
        $select node_id into $p_r_node_id from node where (netw_id=$p_netw_id and node_name=REMOTE_NODE);
 
        if( SQL_STATUS < 0) exit(1);
 
 
        /* Retrieve connection ID based upon node ID, slot, port, DLCI for endpoints and network ID */
        /* Channel numbers are zero-based so have to subtract 1 from them */
        $select con_obj_id into $p_con_obj_id from connection 
                where l_network_id=$p_netw_id                                                           /* Check for the correct network and */  
           and r_network_id=$p_netw_id                                                          /* node for each endpoint            */
                and l_node_id=$p_l_node_id
        and r_node_id=$p_r_node_id
        and l_slot=LOCAL_SLOT                                                                           /* Local (owner) slot */
                and r_slot=REMOTE_SLOT                                                                          /* Remote slot */
        and l_channel=LOCAL_CHAN-1                                                              /* Local (owner) channel (minus 1) */
        and r_channel=REMOTE_CHAN-1                                                             /* Remote channel (minus 1) */
        and l_dlci=LOCAL_DLCI                                                                           /* Local (owner) DLCI */
        and r_dlci=REMOTE_DLCI;                                                                         /* Remote DLCI */
 
        if( SQL_STATUS < 0) exit(1);
 
 
#ifdef OUTPUT_ON
        printf("Network ID: %d\n",p_netw_id);
        printf("Local Node ID: %d\n",p_l_node_id);
        printf("Remote Node ID: %d\n",p_r_node_id);
        printf("Connection Object ID: %d\n",p_con_obj_id);
#endif
 
        /* Retrieve statistics from the connection statistics data table */
 
        /* First declare a cursor as there may be several rows of stats that match the criteria */
        $DECLARE stats CURSOR FOR
                select timestamp, totald, peak into $p_timestamp,$p_totald,$p_peak from connection_data 
                        where con_obj_id=$p_con_obj_id
                        and l_node_id = $p_l_node_id
                        and stat_type = BYTES_TRANS                             
                        and bucket_type = BUCKET;
 
        /* Open the cursor */
        $OPEN stats;
 
        /* While no error occurs, fetch the stats a row at a time and print them */
        while (SQL_STATUS == 0) {
 
                /* Fetch a row of data */
                $FETCH stats;
        if(SQL_STATUS == 0) {
 
                        /* Put the timestamp retrieved into a time_t variable for the ctime function */
                        timest = p_timestamp;
#ifdef OUTPUT_ON
                        printf("Time:%s Total:  %d,  Peak:  %d (-1 indicates not collected)\n", ctime(&timest), p_totald, p_peak);
#endif
                }
 
        }
 
        /* Close the database cursor */
        $close stats;
 
}

ASI Port Cells Received

This example program extracts all of the cells received statistics for a specific BPX ASI port from the CWM Informix database.

#include <stdio.h>
#include <time.h>
$include sqlca;
 
#define SQL_STATUS sqlca.sqlcode
 
#define OUTPUT_ON
 
/* Network and endpoint definitions for a frame relay connection */
/* from rio:11.4.200 to hongkong:9.4.100 in the "Demolab" network */
 
$define NETWORK         "Demolab";
$define LOCAL_NODE      "rio";
$define REMOTE_NODE     "hongkong";
$define LOCAL_SLOT      11;
$define REMOTE_SLOT     9;
$define LOCAL_CHAN      4;
$define REMOTE_CHAN     4;
$define LOCAL_DLCI      200;
$define REMOTE_DLCI     100;
 
/* Statistic type and measurement interval (bucket) definitions */
$define BYTES_TRANS     11;             /* Bytes transmitted */
$define BUCKET                  30;             /* 30 minute measurement interval */
 
 
main()
 
{
$short p_netw_id=-1;
$int p_l_node_id=-1;
$int p_r_node_id=-1;
$int p_con_obj_id=-1;
$int p_totald=-1;
$int p_peak=-1;
$int p_timestamp=-1;
 
time_t timest;
 
        /* Choose the stratacom database */
        $database stratacom;
 
        /* Retrieve network ID based upon network name */
        $select netw_id into $p_netw_id from network where netw_name=NETWORK;
 
        if( SQL_STATUS < 0) exit(1);
 
        /* Retrieve LOCAL PVC endpoint node ID based upon network ID and node name */
        $select node_id into $p_l_node_id from node where (netw_id=$p_netw_id and node_name=LOCAL_NODE);
 
        if( SQL_STATUS < 0) exit(1);
 
        /* Retrieve REMOTE PVC endpoint node ID based upon network ID and node name */
        $select node_id into $p_r_node_id from node where (netw_id=$p_netw_id and node_name=REMOTE_NODE);
 
        if( SQL_STATUS < 0) exit(1);
 
 
        /* Retrieve connection ID based upon node ID, slot, port, DLCI for endpoints and network ID */
        /* Channel numbers are zero-based so have to subtract 1 from them */
        $select con_obj_id into $p_con_obj_id from connection 
                where l_network_id=$p_netw_id                                                           /* Check for the correct network and */  
           and r_network_id=$p_netw_id                                                          /* node for each endpoint            */
                and l_node_id=$p_l_node_id
        and r_node_id=$p_r_node_id
        and l_slot=LOCAL_SLOT                                                                           /* Local (owner) slot */
                and r_slot=REMOTE_SLOT                                                                          /* Remote slot */
        and l_channel=LOCAL_CHAN-1                                                              /* Local (owner) channel (minus 1) */
        and r_channel=REMOTE_CHAN-1                                                             /* Remote channel (minus 1) */
        and l_dlci=LOCAL_DLCI                                                                           /* Local (owner) DLCI */
        and r_dlci=REMOTE_DLCI;                                                                         /* Remote DLCI */
 
        if( SQL_STATUS < 0) exit(1);
 
 
#ifdef OUTPUT_ON
        printf("Network ID: %d\n",p_netw_id);
        printf("Local Node ID: %d\n",p_l_node_id);
        printf("Remote Node ID: %d\n",p_r_node_id);
        printf("Connection Object ID: %d\n",p_con_obj_id);
#endif
 
        /* Retrieve statistics from the connection statistics data table */
 
        /* First declare a cursor as there may be several rows of stats that match the criteria */
        $DECLARE stats CURSOR FOR
                select timestamp, totald, peak into $p_timestamp,$p_totald,$p_peak from connection_data 
                        where con_obj_id=$p_con_obj_id
                        and l_node_id = $p_l_node_id
                        and stat_type = BYTES_TRANS                             
                        and bucket_type = BUCKET;
 
        /* Open the cursor */
        $OPEN stats;
 
        /* While no error occurs, fetch the stats a row at a time and print them */
        while (SQL_STATUS == 0) {
 
                /* Fetch a row of data */
                $FETCH stats;
        if(SQL_STATUS == 0) {
 
                        /* Put the timestamp retrieved into a time_t variable for the ctime function */
                        timest = p_timestamp;
#ifdef OUTPUT_ON
                        printf("Time:%s Total:  %d,  Peak:  %d (-1 indicates not collected)\n", ctime(&timest), p_totald, p_peak);
#endif
                }
 
        }
 
        /* Close the database cursor */
        $close stats;
 
}

Network Nodes List

This example program extracts all of the node entries and related node information for the specified network from the CWM Informix database.

#include <stdio.h>
#include <time.h>
$include sqlca;
 
#define SQL_STATUS sqlca.sqlcode
 
#define OUTPUT_ON
 
/* Network and endpoint definitions for a frame relay connection */
/* from rio:11.4.200 to hongkong:9.4.100 in the "Demolab" network */
 
$define NETWORK         "Demolab";
$define LOCAL_NODE      "rio";
$define REMOTE_NODE     "hongkong";
$define LOCAL_SLOT      11;
$define REMOTE_SLOT     9;
$define LOCAL_CHAN      4;
$define REMOTE_CHAN     4;
$define LOCAL_DLCI      200;
$define REMOTE_DLCI     100;
 
/* Statistic type and measurement interval (bucket) definitions */
$define BYTES_TRANS     11;             /* Bytes transmitted */
$define BUCKET                  30;             /* 30 minute measurement interval */
 
 
main()
 
{
$short p_netw_id=-1;
$int p_l_node_id=-1;
$int p_r_node_id=-1;
$int p_con_obj_id=-1;
$int p_totald=-1;
$int p_peak=-1;
$int p_timestamp=-1;
 
time_t timest;
 
        /* Choose the stratacom database */
        $database stratacom;
 
        /* Retrieve network ID based upon network name */
        $select netw_id into $p_netw_id from network where netw_name=NETWORK;
 
        if( SQL_STATUS < 0) exit(1);
 
        /* Retrieve LOCAL PVC endpoint node ID based upon network ID and node name */
        $select node_id into $p_l_node_id from node where (netw_id=$p_netw_id and node_name=LOCAL_NODE);
 
        if( SQL_STATUS < 0) exit(1);
 
        /* Retrieve REMOTE PVC endpoint node ID based upon network ID and node name */
        $select node_id into $p_r_node_id from node where (netw_id=$p_netw_id and node_name=REMOTE_NODE);
 
        if( SQL_STATUS < 0) exit(1);
 
 
        /* Retrieve connection ID based upon node ID, slot, port, DLCI for endpoints and network ID */
        /* Channel numbers are zero-based so have to subtract 1 from them */
        $select con_obj_id into $p_con_obj_id from connection 
                where l_network_id=$p_netw_id                                                           /* Check for the correct network and */  
           and r_network_id=$p_netw_id                                                          /* node for each endpoint            */
                and l_node_id=$p_l_node_id
        and r_node_id=$p_r_node_id
        and l_slot=LOCAL_SLOT                                                                           /* Local (owner) slot */
                and r_slot=REMOTE_SLOT                                                                          /* Remote slot */
        and l_channel=LOCAL_CHAN-1                                                              /* Local (owner) channel (minus 1) */
        and r_channel=REMOTE_CHAN-1                                                             /* Remote channel (minus 1) */
        and l_dlci=LOCAL_DLCI                                                                           /* Local (owner) DLCI */
        and r_dlci=REMOTE_DLCI;                                                                         /* Remote DLCI */
 
        if( SQL_STATUS < 0) exit(1);
 
 
#ifdef OUTPUT_ON
        printf("Network ID: %d\n",p_netw_id);
        printf("Local Node ID: %d\n",p_l_node_id);
        printf("Remote Node ID: %d\n",p_r_node_id);
        printf("Connection Object ID: %d\n",p_con_obj_id);
#endif
 
        /* Retrieve statistics from the connection statistics data table */
 
        /* First declare a cursor as there may be several rows of stats that match the criteria */
        $DECLARE stats CURSOR FOR
                select timestamp, totald, peak into $p_timestamp,$p_totald,$p_peak from connection_data 
                        where con_obj_id=$p_con_obj_id
                        and l_node_id = $p_l_node_id
                        and stat_type = BYTES_TRANS                             
                        and bucket_type = BUCKET;
 
        /* Open the cursor */
        $OPEN stats;
 
        /* While no error occurs, fetch the stats a row at a time and print them */
        while (SQL_STATUS == 0) {
 
                /* Fetch a row of data */
                $FETCH stats;
        if(SQL_STATUS == 0) {
 
                        /* Put the timestamp retrieved into a time_t variable for the ctime function */
                        timest = p_timestamp;
#ifdef OUTPUT_ON
                        printf("Time:%s Total:  %d,  Peak:  %d (-1 indicates not collected)\n", ctime(&timest), p_totald, p_peak);
#endif
                }
 
        }
 
        /* Close the database cursor */
        $close stats;
 
}

Table of Contents

SQL Examples

SQL Examples

Frame Relay Bytes Transmitted

ASI Port Cells Received

Network Nodes List