1. Alarms and Maintenance Guide
  2. Query Server Maintenance Procedures

E Query Server Maintenance Procedures

This appendix contains detailed, step-by-step query server procedures, as well as information about the automated system check feature, the query server error log, and how to retrieve information from the LNP database fields.

Introduction

LSMS Maintenance Procedures

Figure E-1 illustrates a query server configuration scenario depicting how the LSMS might be directly connected to a query server, or indirectly connected to daisy-chained query servers. Refer to this figure when performing the maintenance procedures described in this section.

This scenario includes the following:

  • One master (LSMS)
  • One remote system
  • Five query servers:
    • One directly connected slave (Query Server A)
    • One directly connected master/slave (Query Server B)
    • Two daisy-chained slaves (Daisy-chained Query Servers C and E)
    • One daisy-chained master/slave (Daisy-chained Query Server D)

Client applications on each query server represent a Service Provider application that queries the replicated LSMS LNP databases using supported MySQL database APIs.

Note:

Process all updates to the query server database through the master.

Figure E-1 Query Server Configuration Scenario


img/c_lsms_maintenance_procedures_mm-fig1.jpg

Automatic Monitoring of Query Servers

The lsmsQueryServer script monitors the connectivity and status of each directly connected query server to ensure that it is replicating the LSMS LNP database. During failure and recovery of the connection with the query servers, the LSMS does one or more of the following:

  • Displays a notification on the graphical user interface (GUI notification)

  • Posts a Surveillance notification at five-minute intervals to Serial Port 3 used by Surveillance

  • Sends a trap to a Network Management System (NMS) if the optional Remote Monitoring feature is installed

For information about the notifications posted, see 8098 and 8099.

Note:

The LSMS does not monitor the connectivity or status of the daisy-chained query servers.

Modify the MySQL Port for Query Servers

Since the MySQL port is a well-known port, for security purposes you can use the LSMS GUI to change the configured MySQL port for a query server.

Note:

To avoid database replication issues, configure the same MySQL port for LSMS that you configure for the query server. For information about configuring the MySQL port for LSMS on the MySQL Port submenu, refer to the Configuration Guide.

For the MySQL Port for Query Servers, there are four options under Admin, and then QS MySQL Port as shown:

Figure E-2 Change configured QS MySQL Port


img/c_modifying_ports_ip_address_query_servers_mm_fig1.jpg
  • Create

    Choosing Create displays the Create QS MySQL IP:Port menu.

    Figure E-3 Create QS MySQL IP:Port


    img/c_modifying_ports_ip_address_query_servers_mm_fig2.jpg

    To start, all IP addresses that are configured to use default port 3306 are selectable in the QS MySQL IP field. Select an IP address, add the desired port in the QS MySQL Port field, click OK (or Apply followed by OK), and a Create Successful message is displayed.

    The valid QS MySQL Port range is 1024-65535. An error message is displayed if a port outside of this range is entered.

    After Create is used, the IP/Port combination is stored in a hidden file on LSMS, /usr/TKLC/lsms/tools/.qs.mysql.port. IP addresses that are assigned to particular ports are not displayed the next time Create is used.

  • Modify

    Choosing Modify displays the Modify QS MySQL IP:Port menu.

    Figure E-4 Modify QS MySQL IP:Port


    img/c_modifying_ports_ip_address_query_servers_mm_fig3.jpg

    The QS MySQL IP field displays the IP addresses that were configured using Create and saved in /usr/TKLC/lsms/tools/.qs.mysql.port. You can modify the port associated with an IP address, click OK (or Apply followed by OK), and a Modify Successful message is displayed.

    After Modify is used, /usr/TKLC/lsms/tools/.qs.mysql.port is updated with the modified IP/Port combination.

  • View

    Choosing View displays the View QS MySQL IP:Port menu.

    Figure E-5 View QS MySQL IP:Port


    img/c_modifying_ports_ip_address_query_servers_mm_fig4.jpg

    The QS MySQL IP field displays the IP addresses that are stored in /usr/TKLC/lsms/tools/.qs.mysql.port, and the QS MySQL Port field lists the corresponding port for each IP address.

  • Delete

    Choosing Delete displays the Delete QS MySQL IP:Port menu.

    Figure E-6 Delete QS MySQL IP:Port


    img/c_modifying_ports_ip_address_query_servers_mm_fig5.jpg

    Use this option to delete an IP/Port combination from /usr/TKLC/lsms/tools/.qs.mysql.port. Deleted IP/Port entries result in that LSMS subsequently connecting using default port 3306.

Check Connection Status of Directly Connected Query Servers

You can check the connection status of query servers that are directly connected to the LSMS. The connection status for each query server (denoted by hostname and IP address) is displayed as "Connected", "Disconnected" or "Not Reachable."

To check the connection status, use the following procedure:

  1. Log into the active server as the lsmsadm user.
    (For information about logging in, see “Logging In to LSMS Server Command Line”.)
  2. Enter the following command:
    $ lsmsdb -c queryservers

    Output similar to the following displays:

    
10.25.60.32 (10.25.60.32) Disconnected
 Failed to connect to 10.25.60.12 database
    In this example, two query servers have been configured to directly connect to the LSMS; the first is currently disconnected and the second failed to connect.

Maintain the Binary Log on Query Servers

Use this procedure to purge the binary log of a query server platform that will have one or more daisy-chained query servers. (Perform this procedure on Query Servers B and D, as shown in Figure E-1.)

Note:

Monitor the binary log size and delete unnecessary binary log files on a weekly basis, following the steps listed below.
  1. Use the following commands to identify what log each daisy-chained query server is replicating from and record your findings:
    # cd /opt/mysql/mysql/bin

    # mysql -u root -p

    Enter password:

    <daisy-chained Query Server’s MySql root user password

    >mysql> SHOW SLAVE STATUS \G;

    mysql> exit;

  2. From Step 1, find the earliest log among all the daisy-chained query servers (if all the daisy-chained query servers are up-to-date, this is the last log on the list).
    On the query server that has query servers daisy-chained from it, list the binary log files. The target log is the one just before the earliest one in use.

    # cd /opt/mysql/mysql/bin

    # mysql -u root -p

    Enter password:

    <Query Server’s MySql root user password>

    mysql> SHOWMASTERLOGS;
  3. Use the following command to purge the master binary log files on the query server (that has one or more query servers daisy-chained from it) excluding the target log:
    mysql> PURGE MASTER LOGS TO '<binary_log_file>';

Check MySQL Replication Status on Query Servers

Use the following procedure to check MySQL replication status on query servers:

  1. Start the MySQL command-line utility on the slave server:

    # cd /opt/mysql/mysql/bin

    # mysql -u root -p

    Enter password:

    <Query Server’s MySql root user password>

  2. Check the replication status using the show slave status command (the status of the slave server is conveyed by the Slave_IO_Running and Slave_SQL_Running column values):
    
mysql> SHOW SLAVE STATUS \G;

Start MySQL Replication on Query Servers

Use the following procedure to start MySQL replication on the query server:

  1. Start the MySQL command-line utility on the query server:

    # cd /opt/mysql/mysql/bin

    # mysql -u root -p

    Enter password:

    <Query Server’s MySql root user password>

  2. Start MySQL replication:
    When the replication operation resumes, the slave server should connect to the master and catch up on any updates that occurred since the replication operation was terminated.

    mysql> start slave;

  3. Verify that MySQL replication is running using the show slave status command (ensure the Slave_IO_Running and Slave_SQL_Running column values are set to Yes, and ensure that the log file has a name).
    mysql> SHOW SLAVE STATUS \G;
  4. Exit the MySQL command-line utility:
    mysql> exit;

Stop MySQL Replication on Query Servers

Use the following procedure to stop MySQL replication on the query server:

  1. Start the MySQL command-line utility on the query server:

    # cd /opt/mysql/mysql/bin

    # mysql -u root -p

    Enter password:

    <Query Server’s MySql root user password>

  2. Stop MySQL replication: (When replication is off, the slave server data is not updated and is not kept in synchronization with the master server.)
    mysql> stop slave;
  3. Verify that MySQL replication is no longer running using the show slave status command (ensure the Slave_IO_Running and Slave_SQL_Running column values are set to No).
    mysql> SHOW SLAVE STATUS \G;
  4. Exit the MySQL command-line utility:
    mysql> exit;

Check for Running Backups

Both database backups and query server snapshots use the same file space on the LSMS. Before creating a snapshot on the LSMS, perform the procedure described in “Checking for Running Backups” to ensure that no database backups are running.

Caution:

If you attempt to create a snapshot while a backup is running, the backup will be interrupted, and the next time a backup is performed, it will take much longer to complete.

The following tasks also use temporary file space (as does a snapshot), so you may run out of file space if you attempt to create a snapshot after you have started but not yet finished any of these tasks:

  • Starting the standby server (changing its state from UNINITIALIZED "INHIBITED" to STANDBY) using the procedure described in “Starting a Server”
  • Running the import command
  • Running the quickaudit command

Reload a Query Server Database from the LSMS

This procedure reloads a corrupted or backlevel query server's database by copying the LSMS LNP database. If the LSMS is configured with multiple query servers, reload a query server from another query server (that is currently synchronized with the LSMS) to prevent NPAC-to-network element traffic from being interrupted (see “Reload a Query Server Database from Another Query Server”).

Note:

The following method of reloading a query server may briefly interrupt provisioning on the LSMS while a snapshot of the LNP database occurs. Therefore, choose this method only when other methods for synchronizing the query server are not feasible. The time required to accomplish this procedure depends on the bandwidth of the customer's network and the amount of data to be reloaded. To minimize service interruption, perform this procedure during a scheduled maintenance period.
  1. Log into the active server as root.
    If you are already logged into the active server as a different user, enter the following command:

    $ su - root

    When prompted, enter the root password.
  2. Enter both of the following commands to remove all existing snapshots as well as the snapshot information file:
    # rm /var/TKLC/lsms/free/mysql-snapshot*

    # rm /var/TKLC/lsms/free/snapinfo.sql

  3. Ensure that no database backups are in progress by performing the procedure described in “Check for Running Backups”.
  4. Enter the following command to create a snapshot of all the LSMS data.

    Caution:

    Do not create a snapshot while a database backup is occurring. To ensure that a database backup is not occurring, perform the procedure described in “Check for Running Backups”.
    In addition, do not create a snapshot while any of the following processes are also running: backups, starting a standby node (to change its state from UNINITIALIZED "INHIBITED" to STANDBY), running the import command, or running the lsmsdb quickaudit command, all of which use temporary storage space. If you try to create a snapshot while any of these processes are running, you may not have enough disk space to complete the process.

    Note:

    GNU tar (gtar) must be installed on the Query Server prior to any single region exceeding 60 million TNs.

    # lsmsdb -c snapshot

    The following output displays: 
    
WARNING: This command may cause a brief interruption in traffic being sent from the NPAC to connected network elements and local LSMS provisioning may be INTERRUPTED.
Do you want to continue? [Y/N] Y
  5. Type Y and press Enter.

    Note:

    This input is case-sensitive. Be sure to type a capital Y.
    Output similar to the following displays (the line .......: in the example output below represents many lines of information that are displayed about each of the databases that is included in the snapshot). 
    
Creating snapshot of the database partition, please wait...
lvcreate -- WARNING: the snapshot will be automatically disabled once it gets full
lvcreate -- INFO: using default snapshot chunk size of 64 KB for "/dev/vgapp/dbbackup"
lvcreate -- doing automatic backup of "vgapp"
lvcreate -- logical volume "/dev/vgapp/dbbackup" successfully created
The database is available to the application again.
Disk snapshot created successfully.
mount: block device /dev/vgapp/dbbackup is write-protected, mounting read-only
Snapshot mounted successfully.
Created snapinfo.sql file successfully
............
lvremove -- doing automatic backup of volume group "vgapp"
lvremove -- logical volume "/dev/vgapp/dbbackup" successfully removed
    When the last two lines shown above (which start with lvremove), the snapshot is complete. However, the database is available to the application before the snapshot is complete, as indicated by the line shown in bold in the example output above. During the creation of a snapshot of the LSMS data, the following occurs:

    • A read lock is obtained

    • Table information is flushed

    • A snapshot is created

    • The read lock is released

    Caution:

    If the snapshot fails or is interrupted, perform the procedure described in “Clean Up After Failed or Interrupted Snapshot” to clean up the file space where snapshot information is temporarily stored. If you do not clean up this file space, future snapshots will fail.
    If the compressed snapshot is successfully created, the LSMS data is stored in the following files in the /var/TKLC/lsms/free directory:
    • mysql-snapshot-supDB.tar.gz
    • mysql-snapshot-<region>DB.tar.gz
    • snapinfo.sql
  6. Use the file transfer protocol (FTP) to move the snapshot data of the master server into the/usr/mysql1 directory on the query server:
    # cd /var/TKLC/lsms/free

    # ftp <IP address of the Query Server>

    ftp> cd /usr/mysql1

    ftp> bin

    ftp> prompt

    ftp> mput mysql-snapshot*.tar.gz snapinfo.sql

    ftp> bye

  7. Shut down the MySQL server on the query server (if it is running):
    # cd /opt/mysql/mysql/bin

    # ./mysqladmin -u root -p shutdown

    Enter password:

    <Query Server’s MySQL user root password>

  8. On the query server, extract the snapshot data from the archive tar files, /usr/mysql1/mysql-snapshot-<db>.tar.gz of the master server’s data.
    Make sure that the privileges on the files and directories are correct. The user that MySQL runs as needs to be able to read and write to them, just as on the master.

    # cd /usr/mysql1

    # gunzip -c mysql-snapshot-supDB.tar.gz | tar -xvf -

    # rm mysql-snapshot-supDB.tar.gz

    Now, extract the data for the snapshot files for each of the LSMS regions starting with the largest regions first. Replace <regionDB> with the regional database name (for example, CanadaDB, MidwestDB, and so forth). Be sure to remove the compressed snapshot files after each database is extracted to guarantee that sufficient disk space is available for all databases.

    # gunzip -c mysql-snapshot-<regionDB>.tar.gz | tar -xvf -

    # rm mysql-snapshot-<regionDB>.tar.gz

  9. Start the MySQL daemon on the query server:
    # cd /opt/mysql/mysql/bin

    # ./mysqld_safe --skip-slave-start &

    Note:

    It is important to start the daemon with the --skip-slave-start option so that replication does not start automatically.
  10. On the query server, start the MySQL command line utility:
    # ./mysql -u root -p
  11. On the query server, reset the configuration information:

    mysql> reset slave;

    mysql> reset master;
  12. Configure the query server to start replication from the correct position on the master.
    This information is stored in the snapinfo.sql file.

    mysql> source /usr/mysql1/snapinfo.sql

  13. Start replication:
    mysql> start slave;

    The query server should connect to the master and catch up on any updates that occurred since the snapshot was taken. When a query server has started replicating, a master.info file is stored in the same directory as the error log (for information about where the error log is stored, see Query Server Error Log).

    Caution:

    Do not remove or edit the master.info file. This file is used by the query server to keep track of how much of the master’s binary log it has processed.

Reload a Query Server Database from Another Query Server

This procedure reloads a corrupted or backlevel query server's LNP database by copying another query server's LNP database. If the LSMS is configured with multiple query servers and at least one is currently synchronized, it is recommended to reload a query server from another query server (instead of from the LSMS) to prevent NPAC-to-network element traffic from being interrupted.

Note:

Replication on the query server may be interrupted while a snapshot of the LNP database occurs. The time required to accomplish this procedure depends on the bandwidth of your network and the amount of data to be reloaded.

Note:

1 through 10 pertain to the query server that is directly connected to the LSMS. 11 through 14 pertain to the query server being reloaded.
  1. Start the MySQL command-line utility on the query server that is directly connected to the LSMS:
    # cd /opt/mysql/mysql/bin

    # ./mysql -u root -p

    Enter password:

    <Query Server’s MySQL user root password>

  2. Stop MySQL replication: (When replication is off, the query server data is not updated and is not kept in synchronization with the LSMS.)
    mysql> stop slave;
  3. Obtain a read lock and flush table cache information:
    (The flush writes changes to tables on disk. The read lock prohibits changes to be made to tables but continues to allow other threads to read from them.)

    mysql> FLUSH TABLES WITH READ LOCK;

  4. Display the file name and current position of the binary log:

    mysql> SHOW MASTER STATUS;

    Output similar to the following displays: 
    
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         | manual,mysql     |
+---------------+----------+--------------+------------------+
  5. Record the values in the File and Position columns, which display the file name and current position of the binary log, respectively.
    In the example above, the file name is mysql-bin.003, and the current position is 73. These values are necessary to properly start the slave process on the query server that is being reloaded.
  6. Exit the MySQL command-line utility:
    mysql> exit;
  7. Shutdown the MySQL server on the query server that is directly connected to the LSMS:
    # ./mysqladmin -u root -p shutdown

    Enter password:

    <Query Server’s MySql root user password>

  8. Remove all existing compressed snapshot files (if any):
    # rm /usr/mysql1/mysql-snapshot*
  9. Create a snapshot of the query server’s copy of all the LSMS data.
    Create a compressed snapshot file for the Supplemental database:

    Note:

    GNU tar (gtar) must be installed on the Query Server prior to any single region exceeding 60 million TNs.

    # tar -cvf - /usr/mysql1/supDB/* | gzip > /usr/mysql1/mysql-snapshot-supDB.tar.gz

    Create compressed snapshot files for each of the regional databases. Replace <regionDB> with the regional database name (for example, CanadaDB, MidwestDB, and so forth).

    # tar -cvf - /usr/mysql1/<regionDB>/* | gzip > /usr/mysql1/mysql-snapshot-<regionDB>.tar.gz

  10. At the query server that is directly connected to the LSMS, restart the MySQL daemon:
    # cd /opt/mysql/mysql/bin

    # ./mysqld_safe &

  11. Shut down the MySQL server on the query server being reloaded:
    # ./mysqladmin -u root -p shutdown

    Enter password:

    <Query Server’s MySQL user root password>

  12. From the master query server, use the file transfer protocol (FTP) to move the snapshot data of the master server into the/usr/mysql1 directory on the query server being reloaded:
    # cd /usr/mysql1

    # ftp <IP address of the Query Server being reloaded>

    ftp> cd /usr/mysql1

    ftp> bin

    ftp> prompt

    ftp> mput mysql-snapshot*.tar.gz

    ftp> bye

  13. On the query server being reloaded, extract the snapshot data from the archive tar file of the directly connected query server's data.
    Ensure that the privileges on the files and directories are correct. The user which MySQL runs as needs to be able to read and write to them, just as on the master. Perform the following commands:

    # cd /usr/mysql1

    # gunzip -c mysql-snapshot-supDB.tar.gz | tar -xvf -

    # rm mysql-snapshot-supDB.tar.gz

    Now, extract the data for the snapshot files for each of the LSMS regions starting with the largest regions first. Replace <regionDB> with the regional database name (for example, CanadaDB, MidwestDB, and so forth). Be sure to remove the compressed snapshot files after each database is extracted to guarantee that sufficient disk space is available for all databases:#

    # gunzip -c mysql-snapshot-<regionDB>.tar.gz | tar -xvf -

    # rm mysql-snapshot-<regionDB>.tar.gz

  14. Start the MySQL daemon on the query server being loaded.

    # cd /opt/mysql/mysql/bin

    # ./mysqld_safe --skip-slave-start

    Note:

    It is important to start the daemon with the --skip-slave-start option so that replication does not start automatically.
  15. Start the mysql command-line utility on the query server that is being loaded:
    # ./mysql -u root -p
  16. Set the binary log position using information that you recorded in 5.
    mysql> CHANGE MASTER TO

    MASTER_LOG_FILE='<recorded_log_file_name>',

    MASTER_LOG_POS=<recorded_log_position>;

    For <recorded_log_file_name>, use the value you recorded for the file name in 5, and for , use the value you recorded for the binary position in 5. For example, using the values shown in the example in 4, enter the following command to set the binary log position:

    mysql> CHANGE MASTER TO

    MASTER_LOG_FILE='mysql-bin.003',

    MASTER_LOG_POS=73;

  17. Start replication on the query server that has been loaded:

    mysql> start slave;

    The query server should connect to the master server (LSMS or another query server) and catch up on any updates that occurred since the snapshot was taken.

Clean Up After Failed or Interrupted Snapshot

If a snapshot fails or is interrupted, the /dev/vgapp/dbbackup volume will remain in the file space that is temporarily used by both backups and snapshot creation. If this volume is present when another snapshot is attempted, the new snapshot will fail.

If a snapshot fails, perform the following procedure to clean up the file space that is used for temporarily storing snapshot information. If this file space is not cleaned up, any future snapshot attempts will fail.

  1. If a snapshot has failed, first ensure that no backup is already running by performing the procedure described in “Check for Running Backups”.
    • If a backup is running, DONOT perform this procedure. Wait until the backup is complete and retry the snapshot.
    • If a backup is not running, proceed to the next step.
  2. Log into the active server as root.
    If you are already logged into the active server as a different user, enter the following command:

    $ su - root

    When prompted, enter the root password.
  3. Enter the following commands:
    # /bin/umount /mnt/backup

    # /usr/sbin/lvremove -f /dev/vgapp/dbbackup

    The following output will display: 

    
lvremove -- doing automatic backup of volume group "vgapp"
lvremove -- logical volume "/dev/vgapp/dbbackup" successfully removed
When the last line in 3 displays, you have completed this procedure.

Automated System Check

The automated system check feature (syscheck) detects, diagnoses, and displays a summary of the overall health of the LSMS server. An LSMS application-specific module, qs_app (System Class) reports on the status of query server direct connections with the LSMS. The status of each connection is displayed on the screen as "OK", "WARNING", or "FAILURE".

Manually Checking Query Server Status

Although syscheck runs automatically and records output in the syscheck log, users can run the syscheck command to check query server status. To manually check query server status, perform the following procedure:

  1. Log into the active server command line as root.
    (For information about logging in, see “Logging In to LSMS Server Command Line”).
  2. Enter the following command:
    # syscheck system qs
    The possible output examples are explained below.

Automated System Check OK Status

When syscheck detects no problems with query server direct connections, output similar to the following appears.

Figure E-7 Automated System Check Output Example - OK


img/c_automated_system_check_ok_status_mm-fig1.jpg

Automated System Check FAILURE Status

When syscheck detects one or more of the following failures, output similar to the following appears.

  • The LSMSDB tool, which is utilized to obtain connection status, does not exist.
  • The LSMSDB tool fails to connect to the database server.
  • The query server hostname is not associated with corresponding Internet Protocol (IP) addresses in the /etc/hosts file.
  • The platform hosting a query server could not be pinged (Not Reachable). The hostname of the query servers that fail the ping check is reported.

Figure E-8 Automated System Check Output Example - FAILURE


img/c_automated_system_check_failure_status_mm-fig2.jpg

Automated System Check WARNING Status

When syscheck detects that one or more query servers are not connected and replicating the LSMS database, output similar to the following appears. The hostname of the query servers that fail the connections check is reported.

Figure E-9 Automated System Check Output Example - WARNING


img/c_automated_system_check_warning_status_mm-fig3.jpg

Query Server Error Log

The query server error log (see the example shown in Figure E-10) contains the following information, if applicable:

  • When mysqld was started and stopped
  • Critical errors found when running mysqld
  • Replication errors and warnings
  • Warnings if mysqld detects a table that needs to be automatically checked or repaired

The query server error log is assigned a name based on the name of your host and appended with a .err extension (for example, <hostname>.err) and is located in one of the following directories:

  • On the LSMS, in /var/TKLC/lsms/db
  • On a query server, in /usr/mysql1. On a Windows machine, mysqld writes this log directly to C:\mysql\data\mysql.err.

    Note:

    Because the query server error log continuously increases in size, it is the user's responsibility to monitor it. To manually delete the log, first shut down the server. Alternatively, execute the mysql-log-rotatescript inside your crontab.

Figure E-10 Query Server Error Log Example


img/c_automated_system_check_warning_status_mm-fig2.jpg

Retrieving Information from LNP Database Fields

The LNP database is in table format. Following are the characteristics of the table rows and columns:

  • Each column contains a value for each row.

  • The table does not contain gaps or short columns.

  • Each row is a single entity, and the columns describe the attributes of those entities.

  • Each column has a name and a type, such as a string or a number. (See Table E-1 throughTable E-3 for the LNP database table names and associated fields.)

    To obtain information (name, type, if field contains a Null, key fields, default value, and so forth) for each field of a table on the query server, perform the following steps:

    Note:

    Example output follows the command syntax in 2 through 5
  1. Start the mysql command-line utility on the query server using the following command:
    # cd /opt/mysql/mysql/bin

    # mysql -u root -p

    Enter password:

    <Query Server’s MySQL user root password>

  2. List the names of the databases on the query server using the following command:
    mysql> SHOWDATABASES; 
    
mysql> SHOW DATABASES;
+-----------+
| Database  |
+-----------+
| ResyncDB  |
| WesternDB |
| mysql     |
| supDB     |
+-----------+
4 rows in set (0.09 sec)
  3. Select the name of the database that contains tables from which you want to retrieve information using the following command:
    mysql> USE <database>;

    where <database> is one of the following: supDB, CanadaDB, MidAtlanticDB, MidwestDB, NortheastDB, SoutheastDB, SouthwestDB, WestCoastDB, WesternDB 

    
mysql> USE WesternDB
Database changed
  4. List the names of the tables in the selected database using the following command:
    mysql> SHOW TABLES; 
    
mysql> SHOW TABLES;
+----------------------+
| Tables_in_WesternDB  |
+----------------------+
| NumberPoolBlock      |
| ServiceProvLRN       |
| ServiceProvNPA_NXX   |
| ServiceProvNPA_NXX_X |
| ServiceProvNetwork   |
| SubscriptionVersion  |
+----------------------+
6 rows in set (0.01 sec)
  5. Retrieve column and field information of a database table using the following command:
    mysql> DESCRIBE <table>;

    where <table> is the name of the database table from the list of tables displayed in 4

    mysql> describe SubscriptionVersion;

Field                Type                Null Key Default      Extra 
--------------------------------------------------------------------                  
versionId            int(11)             NO   PRI
tn                   char(10)            NO   UNI     
lrn                  char(10)            NO   MUL     
newCurrentSp         char(4)             NO   MUL 0000 
activationTimestamp  char(14)            NO       00000000000000      classDPC             char(9)             NO   
classSSN             char(3)             NO
lidbDPC              char(9)             NO
lidbSSN              char(3)             NO
isvmDPC              char(9)             NO
isvmSSN              char(3)             NO
cnamDPC              char(9)             NO
cnamSSN              char(3)             NO
endUserLocationValue char(12)            NO
endUserLocationType  char(2)             NO
billingId            char(4)             NO
lnpType              tinyint(3) unsigned NO       0 
downloadReason       tinyint(3) unsigned NO       0         
wsmscDPC             char(9)             NO 
wsmscSSN             char(3)             NO 
svType               tinyint(4)          NO       -1 
alternativeSPID      char(4)             NO

22 rows in set (0.00 sec)

LNP Database Tables and Fields

The Query Server database consists of replicated copies of the LSMS LNP database tables listed in Table E-1, Table E-2, and Table E-3.

Note:

In the table below, names of regional LNP database tables and fields may be split between lines. This does not imply a space in the name of the table or field.

Table E-1 Regional Database Tables and Fields

Regional (<Region>) DB) LNP Database Tables Fields
SubscriptionVersion versionID tn lrn newCurrentSp
classDPC classSSN lidbDPC lidbSSN
isvmDPC isvmSSN cnamDPC cnamSSN
wsmscDPC wsmscSSN LnpType billingId
endUserLocation Value endUserLocation Type activation Timestamp downloadReason
SVType alternativeSPID    
NumberPoolBlock blockId npanxx_x lrn newCurrentSP
classDPC classSSN lidbDPC lidbSSN
isvmDPC isvmSSN cnamDPC cnamSSN
wsmscDPC wsmscSSN activationTimestamp downloadReason
SVType alternativeSPID    
ServiceProvLRN serviceProviderId id lrn creationTimeStamp
downloadReason      
ServiceProv NPA_NXX serviceProviderId id npanxx creationTimeStamp
effectiveTimeStamp downloadReason    
ServiceProv NPA_NXX_X serviceProviderId id npanxx_x creationTimeStamp
effectiveTimeStamp modifiedTime Stamp downloadReason  
ServiceProvNetwork serviceProvId serviceProvName serviceProvType  
Where <Region> is one of the following: Canada MidAtlantic Midwest Northeast
Southeast Southwest WestCoast Western

Table E-2 Supplemental Database Tables and Fields (Part 1)

Supplemental (supDB) LNP Database Tables Fields
DefaultGtt groupName npanxx spid  
ain_set ain_tt ain_dpc ain_ssn
ain_xlat ain_ri ain_ngt ain_rgta
in_set in_tt in_dpc in_ssn
in_xlat in_ri in_ngt in_rgta
class_set class_tt class_dpc class_ssn
class_xlat class_ri class_ngt class_rgta
lidb_set lidb_tt lidb_dpc lidb_ssn
lidb_xlat lidb_ri lidb_ngt lidb_rgta
isvm_set isvm_tt isvm_dpc isvm_ssn
isvm_xlat isvm_ri isvm_ngt isvm_rgta
cnam_set cnam_tt cnam_dpc cnam_ssn
cnam_xlat cnam_ri cnam_ngt cnam_rgta
wsmsc_set wsmsc_tt wsmsc_dpc wsmsc_ssn
wsmsc_xlat wsmsc_ri wsmsc_ngt wsmsc_rgta
OverrideGtt groupName lrn spid  
class_set class_tt class_dpc class_ssn
class_xlat class_ri class_ngt class_rgta
lidb_set lidb_tt lidb_dpc lidb_ssn
lidb_xlat lidb_ri lidb_ngt lidb_rgta
isvm_set isvm_tt isvm_dpc isvm_ssn
isvm_xlat isvm_ri isvm_ngt isvm_rgta
cnam_set cnam_tt cnam_dpc cnam_ssn
cnam_xlat cnam_ri cnam_ngt cnam_rgta
wsmsc_set wsmsc_tt wsmsc_dpc wsmsc_ssn
  wsmsc_xlat wsmsc_ri wsmsc_ngt wsmsc_rgta
NpaSplit oldNpa newNpa nxx startPDP
endPDP region status  
LsmsServiceProvider spid description contactInfo  

Table E-3 Supplemental Database Tables and Fields (Part 2)

Supplemental (supDB) LNP Database Tables Fields
GttGroup name description    
ain_set ain_tt ain_dpc ain_ssn
ain_xlat ain_ri ain_ngt ain_rgta
in_set in_tt in_dpc in_ssn
in_xlat in_ri in_ngt in_rgta
class_set class_tt class_dpc class_ssn
class_xlat class_ri class_ngt class_rgta
lidb_set lidb_tt lidb_dpc lidb_ssn
lidb_xlat lidb_ri lidb_ngt lidb_rgta
isvm_set isvm_tt isvm_dpc isvm_ssn
isvm_xlat isvm_ri isvm_ngt isvm_rgta
cnam_set cnam_tt cnam_dpc cnam_ssn
cnam_xlat cnam_ri cnam_ngt cnam_rgta
wsmsc_set wsmsc_tt wsmsc_dpc wsmsc_ssn
wsmsc_xlat wsmsc_ri wsmsc_ngt wsmsc_rgta
EmsInterface clli emsType primaryAddress secondaryAddress
mateClii pointCode matePointCode capabilityPoint Code
gttGroup tnFilter ownerSpid componentInfo
contactInfo dcmAddress retryinterval retryCount
pingMethod      
TnFilter spid name description filterType
regions npanxxType npanxxs  
NpacRegion region npacSmsName lsmsPsel lsmsSsel
lsmsTsel lsmsNsap primaryNpacPsel primaryNpacSsel
primaryNpacTsel primaryNpacNsap primaryNpac FtpAddress secondaryNpac Psel
secondaryNpacSsel secondaryNpacTsel secondaryNpac Nsap secondaryNpac FtpAddress
active componentInfo contactInfo lastChanged Timestamp
currentNpac      
<Region>Npac Measurements yyyydddhh Binds SuccessOps FailedOps
<Clii>Eagle Measurements yyyydddhh      
updTnSuccess updTnFail DelTnSuccess DelTnFail
updDGttSuccess updDGttFail DelDGttSuccess DelDGttFail
updOGttSuccess updOGttFail DelOGttSuccess DelOGttFail
updSplitSuccess updSplitFail DelSplitSuccess DelSplitFail
  Binds LsmsRetries NERetries  
<Region>PublicKey id listId keyId status
exponent modulus    
<Region>PrivateKey id listId keyId status
keyval      
LsmsUser name golden groupName inactivityTimeout
LsmsUserSpid lsmsUser spid    
Where <Region> is one of the following: Canada MidAtlantic Midwest Northeast
Southeast Southwest WestCoast Western
Where <Clii> is the Common Language Location Indicator of the EMS/EAGLE to which that LSMS is connected.

Note:

In Table E-3, by default, the following Supplemental (SupDB) LNPDatabase Tables are not replicated. To replicate these tables, refer to the Note in Step 1 of the topic, “MySQL Replication Configuration for Query Servers” in Appendix A of the Configuration Guide.

  • <Region>PublicKey

  • <Region>PrivateKey

  • LsmsUser

  • LsmsUserSpid

    To replicate these tables, refer to the Note in Step 1 of the topic, “MySQL Replication Configuration for Query Servers” in Appendix A of the Configuration Guide.

Query Server Database Structure

This section contains the database structure needed to develop customer-provided applications on the query servers.

--

-- Create NpacRegion table

--

-- One NpacRegion defines the configuration of the primary and secondary 
NPAC.

--

CREATE TABLE  NpacRegion

(

    -- Region name

    region               VARCHAR(40)  NOT NULL,



    -- SMS Name defined by NPAC

    npacSmsName          TINYBLOB,



    -- OSI address of LSMS

    lsmsPsel              TINYBLOB,

    lsmsSsel              TINYBLOB,

    lsmsTsel              TINYBLOB,

    lsmsNsap              TINYBLOB,



    -- OSI address of primary NPAC

    primaryNpacPsel       TINYBLOB,

    primaryNpacSsel       TINYBLOB,

    primaryNpacTsel       TINYBLOB,

    primaryNpacNsap       TINYBLOB,



    primaryNpacFtpAddress TINYBLOB,



    -- OSI address of secondary NPAC

    secondaryNpacPsel     TINYBLOB,

    secondaryNpacSsel     TINYBLOB,

    secondaryNpacTsel     TINYBLOB,

    secondaryNpacNsap     TINYBLOB,



    secondaryNpacFtpAddress TINYBLOB,



    -- Region is active

    active               BOOL        NOT NULL DEFAULT 0,



    -- Component Info (stored as CSV string)

    componentInfo        BLOB        NOT NULL, 

    -- Contact Info (stored as CSV string)

    contactInfo        BLOB          NOT NULL, 



    -- Last changed timestamp set by npacagent

    lastChangedTimestamp CHAR(14)    NOT NULL, -- Default now



    -- Current npac in use set by npacagent

    currentNpac          ENUM("Primary", "Secondary") DEFAULT "Primary",



    -- Region name is primary key

    PRIMARY KEY (region)

)

TYPE = MyIsam;



INSERT INTO NpacRegion 

    (region, npacSmsName,

     lsmsPsel, lsmsSsel, lsmsTsel, lsmsNsap,

     primaryNpacPsel, primaryNpacSsel, primaryNpacTsel, primaryNpacNsap, 
primaryNpacFtpAddress,

     secondaryNpacPsel, secondaryNpacSsel, secondaryNpacTsel, 
secondaryNpacNsap, secondaryNpacFtpAddress,

     componentInfo, contactInfo, lastChangedTimestamp)

    VALUES ("Canada", "Region8 NPAC Canada",

            "cw7", "cw7", "", "rk6",

            "", "", "", "0x00000000",

            "0.0.0.0",

            "", "", "", "0x00000000",

            "0.0.0.0",

            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',

            '"Lsms Admin","admin@tekelec.com","5200 Paramount 
Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1
234","9195551234"',

            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),

           ("MidAtlantic", "Mid-Atlantic Regional NPAC SMS",

            "cw1", "cw1", "", "rk6",

            "", "", "", "0x00000000",

            "0.0.0.0",

            "", "", "", "0x00000000",

            "0.0.0.0",

            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',

            '"Lsms Admin","admin@tekelec.com","5200 Paramount 
Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1
234","9195551234"',

            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),

           ("Midwest", "Midwest Regional NPAC SMS",

            "cw0", "cw0", "", "rk6",

            "", "", "", "0x00000000",

            "0.0.0.0",

            "", "", "", "0x00000000",

            "0.0.0.0",

            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',

            '"Lsms Admin","admin@tekelec.com","5200 Paramount 
Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1
234","9195551234"',

            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),

           ("Northeast", "Northeast Regional NPAC SMS",

            "cw2", "cw2", "", "rk6",

            "", "", "", "0x00000000",

            "0.0.0.0",

            "", "", "", "0x00000000",

            "0.0.0.0",

            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',

            '"Lsms Admin","admin@tekelec.com","5200 Paramount 
Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1
234","9195551234"',

            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),

           ("Southeast", "Southeast Regional NPAC SMS",

            "cw3", "cw3", "", "rk6",

            "", "", "", "0x00000000",

            "0.0.0.0",

            "", "", "", "0x00000000",

            "0.0.0.0",

            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',

            '"Lsms Admin","admin@tekelec.com","5200 Paramount 
Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1
234","9195551234"',

            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),

           ("Southwest", "Southwest Regional NPAC SMS",

            "cw4", "cw4", "", "rk6",

            "", "", "", "0x00000000",

            "0.0.0.0",

            "", "", "", "0x00000000",

            "0.0.0.0",

            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',

            '"Lsms Admin","admin@tekelec.com","5200 Paramount 
Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1
234","9195551234"',

            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),

           ("WestCoast", "WestCoast Regional NPAC SMS",

            "cw6", "cw6", "", "rk6",

            "", "", "", "0x00000000",

            "0.0.0.0",

            "", "", "", "0x00000000",

            "0.0.0.0",

            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',

            '"Lsms Admin","admin@tekelec.com","5200 Paramount 
Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1
234","9195551234"',

            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),

           ("Western", "Western Regional NPAC SMS",

            "cw5", "cw5", "", "rk6",

            "", "", "", "0x00000000",

            "0.0.0.0",

            "", "", "", "0x00000000",

            "0.0.0.0",

            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',

            '"Lsms Admin","admin@tekelec.com","5200 Paramount 
Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1
234","9195551234"',

            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s"));



--

-- Create  LsmsServiceProvider table

--

CREATE TABLE  LsmsServiceProvider

(

    -- The service provider id (Primary Key)

    spid        CHAR(4)   NOT NULL,



    -- Description of the service provider

    description CHAR(80)  NOT NULL,



    -- Contact Info (stored as comma separated value string)

    contactInfo BLOB NOT NULL,



    -- Primary key is the spid

    PRIMARY KEY (spid) 

)

TYPE = MyIsam;



--

-- Create  LsmsUser table

--

CREATE TABLE  LsmsUser

(

    -- The user name (Primary Key)

    name    CHAR(64) NOT NULL,



    -- Description of the service provider

    golden  BOOL     NOT NULL DEFAULT 0,



    -- The Assigned permission group

    groupName    CHAR(4) NOT NULL, 



    -- The assigned inactivity timeout

    inactivityTimeout    CHAR(11) NOT NULL DEFAULT '-1',



    -- Primary key is the user name

    PRIMARY KEY (name)

)

TYPE = MyIsam;

-- Create default 'golden' users

INSERT INTO LsmsUser (name, golden)

            VALUES('lsmsadm',1), ('lsmsuser',1), ('lsmsview',1), 

                  ('lsmsall',1), ('lsmsuext',1);

--

-- Create  GttGroup table

--

CREATE TABLE  GttGroup

(

    -- The group name (Primary Key)

    name        CHAR(64)   NOT NULL,



    -- Description of the GttGroup

    description CHAR(80)  NOT NULL,



    -- Services in GttGroup are for storing default TT/SSN values

    -- AIN Service

    ain_set  BOOL    NOT NULL DEFAULT 0,

    ain_tt   TINYINT UNSIGNED NOT NULL,

    ain_dpc  CHAR(9) NOT NULL,

    ain_ssn  CHAR(3) NOT NULL,

    ain_xlat TINYINT UNSIGNED NOT NULL,

    ain_ri   TINYINT UNSIGNED NOT NULL,

    ain_ngt  TINYINT UNSIGNED NOT NULL,

    ain_rgta BOOL    NOT NULL,

    -- IN Service

    in_set  BOOL    NOT NULL DEFAULT 0,

    in_tt   TINYINT UNSIGNED NOT NULL,

    in_dpc  CHAR(9) NOT NULL,

    in_ssn  CHAR(3) NOT NULL,

    in_xlat TINYINT UNSIGNED NOT NULL,

    in_ri   TINYINT UNSIGNED NOT NULL,

    in_ngt  TINYINT UNSIGNED NOT NULL,

    in_rgta BOOL    NOT NULL,

    -- CLASS Service

    class_set  BOOL    NOT NULL DEFAULT 0,

    class_tt   TINYINT UNSIGNED NOT NULL,

    class_dpc  CHAR(9) NOT NULL,

    class_ssn  CHAR(3) NOT NULL,

    class_xlat TINYINT UNSIGNED NOT NULL,

    class_ri   TINYINT UNSIGNED NOT NULL,

    class_ngt  TINYINT UNSIGNED NOT NULL,

    class_rgta BOOL    NOT NULL,

    -- LIDB Service

    lidb_set  BOOL    NOT NULL DEFAULT 0,

    lidb_tt   TINYINT UNSIGNED NOT NULL,

    lidb_dpc  CHAR(9) NOT NULL,

    lidb_ssn  CHAR(3) NOT NULL,

    lidb_xlat TINYINT UNSIGNED NOT NULL,

    lidb_ri   TINYINT UNSIGNED NOT NULL,

    lidb_ngt  TINYINT UNSIGNED NOT NULL,

    lidb_rgta BOOL    NOT NULL,

    -- ISVM Service

    isvm_set  BOOL    NOT NULL DEFAULT 0,

    isvm_tt   TINYINT UNSIGNED NOT NULL,

    isvm_dpc  CHAR(9) NOT NULL,

    isvm_ssn  CHAR(3) NOT NULL,

    isvm_xlat TINYINT UNSIGNED NOT NULL,

    isvm_ri   TINYINT UNSIGNED NOT NULL,

    isvm_ngt  TINYINT UNSIGNED NOT NULL,

    isvm_rgta BOOL    NOT NULL,

    -- CNAM Service

    cnam_set  BOOL    NOT NULL DEFAULT 0,

    cnam_tt   TINYINT UNSIGNED NOT NULL,

    cnam_dpc  CHAR(9) NOT NULL,

    cnam_ssn  CHAR(3) NOT NULL,

    cnam_xlat TINYINT UNSIGNED NOT NULL,

    cnam_ri   TINYINT UNSIGNED NOT NULL,

    cnam_ngt  TINYINT UNSIGNED NOT NULL,

    cnam_rgta BOOL    NOT NULL,

     -- WSMSC Service

    wsmsc_set  BOOL    NOT NULL DEFAULT 0,

    wsmsc_tt   TINYINT UNSIGNED NOT NULL,

    wsmsc_dpc  CHAR(9) NOT NULL,

    wsmsc_ssn  CHAR(3) NOT NULL,

    wsmsc_xlat TINYINT UNSIGNED NOT NULL,

    wsmsc_ri   TINYINT UNSIGNED NOT NULL,

    wsmsc_ngt  TINYINT UNSIGNED NOT NULL,

    wsmsc_rgta BOOL    NOT NULL,



    -- Primary key is the group name

    PRIMARY KEY (name)

)

TYPE = MyIsam;



--

-- Create GttGroupSpid table 

--

-- This table is used to associate a GttGroup to an authorized 

-- LsmsServiceProvider. The many-many relationship between the two 

-- is stored by this table a group-spid combinations.

--

CREATE TABLE  GttGroupSpid

(

    -- Group name

    gttGroup    CHAR(64) NOT NULL,   



    -- Spid

    spid        char(4) NOT NULL,



    -- Force GttGroup,LsmsServiceProvider combinations to be unique

    PRIMARY KEY (gttGroup, spid),



    -- Not used by MySql but included for documentation

    FOREIGN KEY (gttGroup) REFERENCES GttGroup(groupName),

    FOREIGN KEY (spid) REFERENCES LsmsServiceProvider(spid)

)

TYPE = MyIsam;



--

-- Create LsmsUserSpid table 

--

-- This table is used to associate a LsmsUser to an authorized 

-- LsmsServiceProvider. The many-many relationship between the two 

-- is stored by this table a group-spid combinations.

--

CREATE TABLE  LsmsUserSpid

(

    -- User name

    lsmsUser    CHAR(64) NOT NULL,   

    

    -- Spid

    spid        CHAR(4) NOT NULL,



    -- Force LsmsUser,LsmsServiceProvider combinations to be unique

    PRIMARY KEY (lsmsUser, spid),



    -- Not used by MySql but included for documentation

    FOREIGN KEY (lsmsUser) REFERENCES LsmsUser(name),

    FOREIGN KEY (spid) REFERENCES LsmsServiceProvider(spid)

)

TYPE = MyIsam;



--

-- Create DefaultGTT Table

--

CREATE TABLE  DefaultGtt

(

    -- The group this DefaultGtt belongs to

    groupName  CHAR(64) NOT NULL, -- Foreign key



    -- NPA-NXX of the DefaultGtt

    npanxx     CHAR(6) NOT NULL,



    -- The SPID that created the DefaultGtt

    spid       CHAR(4) NOT NULL,



    -- AIN Service

    ain_set  BOOL    NOT NULL DEFAULT 0,

    ain_tt   TINYINT UNSIGNED NOT NULL,

    ain_dpc  CHAR(9) NOT NULL,

    ain_ssn  CHAR(3) NOT NULL,

    ain_xlat TINYINT UNSIGNED NOT NULL,

    ain_ri   TINYINT UNSIGNED NOT NULL,

    ain_ngt  TINYINT UNSIGNED NOT NULL,

    ain_rgta BOOL    NOT NULL,

    -- IN Service

    in_set  BOOL    NOT NULL DEFAULT 0,

    in_tt   TINYINT UNSIGNED NOT NULL,

    in_dpc  CHAR(9) NOT NULL,

    in_ssn  CHAR(3) NOT NULL,

    in_xlat TINYINT UNSIGNED NOT NULL,

    in_ri   TINYINT UNSIGNED NOT NULL,

    in_ngt  TINYINT UNSIGNED NOT NULL,

    in_rgta BOOL    NOT NULL,

    -- CLASS Service

    class_set  BOOL    NOT NULL DEFAULT 0,

    class_tt   TINYINT UNSIGNED NOT NULL,

    class_dpc  CHAR(9) NOT NULL,

    class_ssn  CHAR(3) NOT NULL,

    class_xlat TINYINT UNSIGNED NOT NULL,

    class_ri   TINYINT UNSIGNED NOT NULL,

    class_ngt  TINYINT UNSIGNED NOT NULL,

    class_rgta BOOL    NOT NULL,

    -- LIDB Service

    lidb_set  BOOL    NOT NULL DEFAULT 0,

    lidb_tt   TINYINT UNSIGNED NOT NULL,

    lidb_dpc  CHAR(9) NOT NULL,

    lidb_ssn  CHAR(3) NOT NULL,

    lidb_xlat TINYINT UNSIGNED NOT NULL,

    lidb_ri   TINYINT UNSIGNED NOT NULL,

    lidb_ngt  TINYINT UNSIGNED NOT NULL,

    lidb_rgta BOOL    NOT NULL,

    -- ISVM Service

    isvm_set  BOOL    NOT NULL DEFAULT 0,

    isvm_tt   TINYINT UNSIGNED NOT NULL,

    isvm_dpc  CHAR(9) NOT NULL,

    isvm_ssn  CHAR(3) NOT NULL,

    isvm_xlat TINYINT UNSIGNED NOT NULL,

    isvm_ri   TINYINT UNSIGNED NOT NULL,

    isvm_ngt  TINYINT UNSIGNED NOT NULL,

    isvm_rgta BOOL    NOT NULL,

    -- CNAM Service

    cnam_set  BOOL    NOT NULL DEFAULT 0,

    cnam_tt   TINYINT UNSIGNED NOT NULL,

    cnam_dpc  CHAR(9) NOT NULL,

    cnam_ssn  CHAR(3) NOT NULL,

    cnam_xlat TINYINT UNSIGNED NOT NULL,

    cnam_ri   TINYINT UNSIGNED NOT NULL,

    cnam_ngt  TINYINT UNSIGNED NOT NULL,

    cnam_rgta BOOL    NOT NULL,

     -- WSMSC Service

    wsmsc_set  BOOL    NOT NULL DEFAULT 0,

    wsmsc_tt   TINYINT UNSIGNED NOT NULL,

    wsmsc_dpc  CHAR(9) NOT NULL,

    wsmsc_ssn  CHAR(3) NOT NULL,

    wsmsc_xlat TINYINT UNSIGNED NOT NULL,

    wsmsc_ri   TINYINT UNSIGNED NOT NULL,

    wsmsc_ngt  TINYINT UNSIGNED NOT NULL,

    wsmsc_rgta BOOL    NOT NULL,

 

    -- DefaultGtt npanxx's are unique within each group

    PRIMARY KEY (groupName, npanxx),



    -- Not used by MySql but included for documentation

    FOREIGN KEY (groupName) REFERENCES GttGroup(name)

)

TYPE = MyIsam;



--

-- Create OverrideGtt Table

--

CREATE TABLE  OverrideGtt

(

    -- The group this OverrideGtt belongs to

    groupName  CHAR(64) NOT NULL, -- Foreign key



    -- LRN of the OverrideGtt 

    lrn        CHAR(10) NOT NULL,



    -- The SPID that created the OverrideGtt

    spid       CHAR(4)  NOT NULL,



    -- CLASS Service

    class_set  BOOL    NOT NULL DEFAULT 0,

    class_tt   TINYINT UNSIGNED NOT NULL,

    class_dpc  CHAR(9) NOT NULL,

    class_ssn  CHAR(3) NOT NULL,

    class_xlat TINYINT UNSIGNED NOT NULL,

    class_ri   TINYINT UNSIGNED NOT NULL,

    class_ngt  TINYINT UNSIGNED NOT NULL,

    class_rgta BOOL    NOT NULL,

    -- LIDB Service

    lidb_set  BOOL    NOT NULL DEFAULT 0,

    lidb_tt   TINYINT UNSIGNED NOT NULL,

    lidb_dpc  CHAR(9) NOT NULL,

    lidb_ssn  CHAR(3) NOT NULL,

    lidb_xlat TINYINT UNSIGNED NOT NULL,

    lidb_ri   TINYINT UNSIGNED NOT NULL,

    lidb_ngt  TINYINT UNSIGNED NOT NULL,

    lidb_rgta BOOL    NOT NULL,

    -- ISVM Service

    isvm_set  BOOL    NOT NULL DEFAULT 0,

    isvm_tt   TINYINT UNSIGNED NOT NULL,

    isvm_dpc  CHAR(9) NOT NULL,

    isvm_ssn  CHAR(3) NOT NULL,

    isvm_xlat TINYINT UNSIGNED NOT NULL,

    isvm_ri   TINYINT UNSIGNED NOT NULL,

    isvm_ngt  TINYINT UNSIGNED NOT NULL,

    isvm_rgta BOOL    NOT NULL,

    -- CNAM Service

    cnam_set  BOOL    NOT NULL DEFAULT 0,

    cnam_tt   TINYINT UNSIGNED NOT NULL,

    cnam_dpc  CHAR(9) NOT NULL,

    cnam_ssn  CHAR(3) NOT NULL,

    cnam_xlat TINYINT UNSIGNED NOT NULL,

    cnam_ri   TINYINT UNSIGNED NOT NULL,

    cnam_ngt  TINYINT UNSIGNED NOT NULL,

    cnam_rgta BOOL    NOT NULL,

     -- WSMSC Service

    wsmsc_set  BOOL    NOT NULL DEFAULT 0,

    wsmsc_tt   TINYINT UNSIGNED NOT NULL,

    wsmsc_dpc  CHAR(9) NOT NULL,

    wsmsc_ssn  CHAR(3) NOT NULL,

    wsmsc_xlat TINYINT UNSIGNED NOT NULL,

    wsmsc_ri   TINYINT UNSIGNED NOT NULL,

    wsmsc_ngt  TINYINT UNSIGNED NOT NULL,

    wsmsc_rgta BOOL    NOT NULL,

   

    -- OverrideGtt lrns are unique within each group

    PRIMARY KEY (groupName, lrn),



    -- Not used by MySql but included for documentation

    FOREIGN KEY (groupName) REFERENCES GttGroup(name)

)

TYPE = MyIsam;



--

-- Create EmsInterface table. A row in the EmsInterface table can 
represent

-- either a MpsInterface or a OapInterface object

--

CREATE TABLE  EmsInterface

(

    -- The CLLI (Primary Key)

    clli                CHAR(10)   NOT NULL,



    emsType             ENUM("OAP", "MPS") NOT NULL,



    -- The IP address of the primary interface

    primaryAddress      TINYBLOB   NOT NULL,



    -- The IP address of the secondary interface

    secondaryAddress    TINYBLOB   NOT NULL,



    -- The method to use to verify the presence of the MPS

    pingMethod          ENUM("PING", "SSH", "NONE") NOT NULL,



    -- The mate CLLI 

    mateClli            CHAR(10)   NOT NULL,



    -- Point code

    pointCode           CHAR(9)    NOT NULL,



    -- Point code of the mate 

    matePointCode       CHAR(9)    NOT NULL,



    -- Capability point code

    capabilityPointCode CHAR(9)    NOT NULL,



    -- GttGroup assigned to the EmsInteraface

    gttGroup            CHAR(64)   NOT NULL DEFAULT ""

        REFERENCES GttGroup(name),



    -- TnFilter assigned to the EmsInteraface

    tnFilter            CHAR(64)   NOT NULL DEFAULT ""

        REFERENCES TnFilter, -- via FOREIGN KEY (ownerSpid, tnfilter)



    -- ServiceProvider to which this EmsInterface is assigned

    ownerSpid           CHAR(4)    NOT NULL DEFAULT ""

        REFERENCES LsmsServiceProvider(spid),



    -- Component Info (stored as CSV string)

    componentInfo        BLOB        NOT NULL, 

    -- Contact Info (stored as CSV string)

    contactInfo        BLOB          NOT NULL, 



    -- The last fields are only used when the row represents a 

    -- OAP interface. The row is used to construct both OapInterface 

    -- objects and MpsInterface objects which are subclasses of 
EmsInterface



    -- OAP dcmAddress

    dcmAddress        TINYBLOB NULL DEFAULT NULL,



    -- OAP retry interval

    retryInterval     INTEGER  NULL DEFAULT NULL, 



    -- OAP retry count

    retryCount        INTEGER  NULL DEFAULT NULL,



    -- Primary key is the CLLI name

    PRIMARY KEY (clli),



    -- Not used by MySql but included for documentation

    FOREIGN KEY (ownerSpid, tnFilter) REFERENCES TnFilter

)

TYPE = MyIsam;



--

-- Create TnFilter table. A row in the EmsInterface table can represent

-- either a RegionTnFilter or a NpaNxxTnFilter object

--

CREATE TABLE  TnFilter

(

    -- The LsmsServiceProvider this TnFilter belongs to

    spid           char(4)    NOT NULL,   -- Foreign key



    -- The name of the TnFilter

    name           CHAR(64)   NOT NULL,



    -- Description of the TnFilter

    description CHAR(80)  NOT NULL,



    -- The filter type (NpaNxxTnFilter or RegionalTnFilter)

    filterType     ENUM("Regional", "NpaNxx") NOT NULL,



    -- If RegionalTnFilter, the region to send

    regions        SET("Not Used", "Canada", "MidAtlantic", "Midwest", 
"Northeast", 

                       "Southeast", "Southwest", "WestCoast", "Western") 
NOT NULL,



    -- If NpaNxxTnFilter, the filter type

    npanxxType     ENUM("Include", "Exclude") NOT NULL,



    -- If NpaNxxTnFilter, the npa-nxxs to send

    npanxxs        LONGBLOB NOT NULL,



    -- TnFilter names are unique within LsmsServiceProvider

    PRIMARY KEY (spid, name),



    -- Not used by MySql but included for documentation

    FOREIGN KEY (spid) REFERENCES LsmsServiceProvider(spid)



)

TYPE = MyIsam;



--

--  Create private and public key tables

--

--  The first four fields define a base class Key in the object interface 

--

--         +-- PrivateKey

--  Key <--|

--         +-- PublicKey 

--

-- Each subclass and table has the key values for the key type.

--



--

-- Create "Model" PrivateKey table

--

CREATE TEMPORARY TABLE IF NOT EXISTS TempPrivateKey

(

    listId      INT UNSIGNED,

    keyId       INT UNSIGNED,

    status      ENUM("Expired", "Valid", "InUse"),

    keyval      BLOB -- Max length 1024

)

TYPE = MyIsam;



-- Create CanadaPrivateKey table

CREATE TABLE  CanadaPrivateKey

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPrivateKey;



-- Create NortheastPrivateKey table

CREATE TABLE  NortheastPrivateKey

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPrivateKey;



-- Create MidAtlanticPrivateKey table

CREATE TABLE  MidAtlanticPrivateKey 

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPrivateKey;



-- Create MidwestPrivateKey table

CREATE TABLE  MidwestPrivateKey 

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPrivateKey;



-- Create SoutheastPrivateKey table

CREATE TABLE  SoutheastPrivateKey 

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPrivateKey;



-- Create SouthwestPrivateKey table

CREATE TABLE  SouthwestPrivateKey 

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPrivateKey;



-- Create WestCoastPrivateKey table

CREATE TABLE  WestCoastPrivateKey 

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPrivateKey;



-- Create WesternPrivateKey table

CREATE TABLE  WesternPrivateKey 

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPrivateKey;



--

-- Create "Model" PublicKey table

--

CREATE TEMPORARY TABLE IF NOT EXISTS TempPublicKey

(

    listId      INT UNSIGNED,

    keyId       INT UNSIGNED,

    status      ENUM("Expired", "Valid", "InUse"),

    exponent    TINYBLOB, -- Max length 3

    modulus     TINYBLOB  -- Max length 256

)

TYPE = MyIsam;



-- Create CanadaPublicKey table

CREATE TABLE  CanadaPublicKey

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPublicKey;



-- Create NortheastPublicKey table

CREATE TABLE  NortheastPublicKey

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPublicKey;



-- Create MidAtlanticPublicKey table

CREATE TABLE  MidAtlanticPublicKey 

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPublicKey;



-- Create MidwestPublicKey table

CREATE TABLE  MidwestPublicKey 

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPublicKey;



-- Create SoutheastPublicKey table

CREATE TABLE  SoutheastPublicKey 

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPublicKey;



-- Create SouthwestPublicKey table

CREATE TABLE  SouthwestPublicKey 

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPublicKey;



-- Create WestCoastPublicKey table

CREATE TABLE  WestCoastPublicKey 

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPublicKey;



-- Create WesternPublicKey table

CREATE TABLE  WesternPublicKey 

(

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,

    PRIMARY KEY (id)

) SELECT * FROM TempPublicKey;



--

-- Create one measurements table for each region

--

-- Create "Model" NpacMeasurements table

CREATE TEMPORARY TABLE IF NOT EXISTS TempNpacMeasurements

(

    yyyydddhh   INT UNSIGNED NOT NULL,

    Binds       INT UNSIGNED NOT NULL DEFAULT 0,

    SuccessOps  INT UNSIGNED NOT NULL DEFAULT 0,

    FailedOps   INT UNSIGNED NOT NULL DEFAULT 0,



    PRIMARY KEY (yyyydddhh)

)

TYPE = MyIsam;



-- Create CanadaNpacMeasurements table

CREATE TABLE  CanadaNpacMeasurements

(

    PRIMARY KEY (yyyydddhh)

) SELECT * FROM TempNpacMeasurements;



-- Create NortheastNpacMeasurements table

CREATE TABLE  NortheastNpacMeasurements

(

    PRIMARY KEY (yyyydddhh)

) SELECT * FROM TempNpacMeasurements;



-- Create MidAtlanticNpacMeasurements table

CREATE TABLE  MidAtlanticNpacMeasurements

(

    PRIMARY KEY (yyyydddhh)

) SELECT * FROM TempNpacMeasurements;



-- Create MidwestNpacMeasurements table

CREATE TABLE  MidwestNpacMeasurements

(

    PRIMARY KEY (yyyydddhh)

) SELECT * FROM TempNpacMeasurements;



-- Create SoutheastNpacMeasurements table

CREATE TABLE  SoutheastNpacMeasurements

(

    PRIMARY KEY (yyyydddhh)

) SELECT * FROM TempNpacMeasurements;



-- Create SouthwestNpacMeasurements table

CREATE TABLE  SouthwestNpacMeasurements

(

    PRIMARY KEY (yyyydddhh)

) SELECT * FROM TempNpacMeasurements;



-- Create WestCoastNpacMeasurements table

CREATE TABLE  WestCoastNpacMeasurements

(

    PRIMARY KEY (yyyydddhh)

) SELECT * FROM TempNpacMeasurements;



-- Create WesternNpacMeasurements table

CREATE TABLE  WesternNpacMeasurements

(

    PRIMARY KEY (yyyydddhh)

) SELECT * FROM TempNpacMeasurements;



--

-- Create DbConfig table

--

CREATE TABLE  DbConfig

(

    keyType     ENUM("Canada", "MidAtlantic", "Midwest", "Northeast",

                     "Southeast", "Southwest", "WestCoast", "Western",

                     "R9", "R10", "R11", "R12", "R13", "R14",

                     "R15", "R16", "R17", "R18", "R19", "R20", -- Future 
Regions

                     "Internal", "Ebda", "Lsms") NOT NULL,

    keyName     TINYBLOB NOT NULL,            -- Max length 256

    description TINYBLOB NOT NULL DEFAULT "", -- Max length 256

    value           BLOB NOT NULL DEFAULT "", -- Max length 64K



    -- keyName is unique within keyType

    PRIMARY KEY (keyType, keyName(255)) 

)

TYPE = MyIsam;



INSERT INTO DbConfig (keyType, keyName, description, value) 

    VALUES

    ("Canada",      "REQUEST_RETRY_NUMBER",   "Retry times for NPAC 
requests",   "3"),

    ("Canada",      "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC 
requests", "2"),

    ("Canada",      "RECOV_RETRY_NUMBER",     "Retry times for NPAC 
recovery requests",   "3"),

    ("Canada",      "RECOV_RETRY_INTERVAL",   "Retry mintues for NPAC 
recovery requests", "5"),

    ("MidAtlantic", "REQUEST_RETRY_NUMBER",   "Retry times for NPAC 
requests",   "3"),

    ("MidAtlantic", "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC 
requests", "2"),

    ("MidAtlantic", "RECOV_RETRY_NUMBER",     "Retry times for NPAC 
recovery requests",   "3"),

    ("MidAtlantic", "RECOV_RETRY_INTERVAL",   "Retry mintues for NPAC 
recovery requests", "5"),

    ("Midwest",     "REQUEST_RETRY_NUMBER",   "Retry times for NPAC 
requests",   "3"),

    ("Midwest",     "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC 
requests", "2"),

    ("Midwest",     "RECOV_RETRY_NUMBER",     "Retry times for NPAC 
recovery requests",   "3"),

    ("Midwest",     "RECOV_RETRY_INTERVAL",   "Retry mintues for NPAC 
recovery requests", "5"),

    ("Northeast",   "REQUEST_RETRY_NUMBER",   "Retry times for NPAC 
requests",   "3"),

    ("Northeast",   "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC 
requests", "2"),

    ("Northeast",   "RECOV_RETRY_NUMBER",     "Retry times for NPAC 
recovery requests",   "3"),

    ("Northeast",   "RECOV_RETRY_INTERVAL",   "Retry mintues for NPAC 
recovery requests", "5"),

    ("Southeast",   "REQUEST_RETRY_NUMBER",   "Retry times for NPAC 
requests",   "3"),

    ("Southeast",   "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC 
requests", "2"),

    ("Southeast",   "RECOV_RETRY_NUMBER",     "Retry times for NPAC 
recovery requests",   "3"),

    ("Southeast",   "RECOV_RETRY_INTERVAL",   "Retry mintues for NPAC 
recovery requests", "5"),

    ("Southwest",   "REQUEST_RETRY_NUMBER",   "Retry times for NPAC 
requests",   "3"),

    ("Southwest",   "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC 
requests", "2"),

    ("Southwest",   "RECOV_RETRY_NUMBER",     "Retry times for NPAC 
recovery requests",   "3"),

    ("Southwest",   "RECOV_RETRY_INTERVAL",   "Retry mintues for NPAC 
recovery requests", "5"),

    ("WestCoast",   "REQUEST_RETRY_NUMBER",   "Retry times for NPAC 
requests",   "3"),

    ("WestCoast",   "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC 
requests", "2"),

    ("WestCoast",   "RECOV_RETRY_NUMBER",     "Retry times for NPAC 
recovery requests",   "3"),

    ("WestCoast",   "RECOV_RETRY_INTERVAL",   "Retry mintues for NPAC 
recovery requests", "5"),

    ("Western",     "REQUEST_RETRY_NUMBER",   "Retry times for NPAC 
requests",   "3"),

    ("Western",     "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC 
requests", "2"),

    ("Western",     "RECOV_RETRY_NUMBER",     "Retry times for NPAC 
recovery requests",   "3"),

    ("Western",     "RECOV_RETRY_INTERVAL",   "Retry mintues for NPAC 
recovery requests", "5"),



    ("Internal", "MAX_SPIDS",     "Maximum Service Providers allowed.",             
"32"), 

    ("Internal", "EDR",           "Enable Efficient Data Reperesentation 
(EDR).",   "N" ),

    ("Internal", "SNMP",          "Enable SNMP Agent.",                             
"N" ),

    ("Internal", "AFT",           "Enable Automatic File Transfer.",                
"N" ),

    ("Internal", "WSMSC",         "Enable wireless service feature.",               
"N" ),

    ("Internal", "WSMSC_TO_EAGLE","Enable sending of WSMSA service to 
Eagle.",      "N" ),
    ("Internal", "IP_GUI",        "Enable Web based ip gui.",                      "N" ), 
    ("Internal", "SPID_SECURITY", "Enable SPID based security.",                    
"N" ),

    ("Internal", "MAX_USERS",     "Maximum Number of Users",                        
"8" ),

    ("Internal", "ENHANCED_FILTERS","Enable Group and Regional filter 
creation.",   "N" ),

    ("Internal", "MAX_EAGLES",    "Maximum number of eagles.",                      
"16"),

    ("Internal", "REPORT_GEN",    "Enable report generator.",                       
"N" ),

    ("Internal", "REPORT_GEN_QUERY_ACTIVE","Report generator pid field",            
"0" ),

    ("Internal", "QUERY_SERVER",  "Enable Query Server feature",                    
"N" ),

    ("Internal", "INACTIVITY_TIMEOUT", "Gui and Shell inactivity timeout 
feature",  "N" ),

    ("Internal", "SYSTEM_INACTIVITY_TIMEOUT", "System wide GUI and Shell 
inactivity timeout value", "15" ),



    ("Ebda", "CMD_ARGS", "EBDA command line arguments", ""),



    ("Lsms", "NPAC_SPID", "Spid used to connect to NPAC", ""),

    ("Lsms", "CONTACT_INFO", "Spid used to connect to NPAC", '"Lsms 
Admin","admin@tekelec.com","5200 Paramount 
Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1
234","9195551234"'),

    ("Lsms", "COMPONENT_INFO", "Spid used to connect to NPAC", 
'"LSMS","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"');



-- 

-- Create NpaSplit table

-- 

CREATE TABLE  NpaSplit

(

    -- The old npa

    oldNpa         char(3)    NOT NULL,



    -- The new npa

    newNpa         CHAR(3)    NOT NULL,



    -- The nxx

    nxx            CHAR(3)    NOT NULL,



    -- The start of the permissive dialing period 

    startPDP       CHAR(8)    NOT NULL, 



    -- The end of the permissive dialing period 

    endPDP         CHAR(8)    NOT NULL,



    -- The region the split belongs to

    region      ENUM("Canada", "MidAtlantic", "Midwest", "Northeast",

                     "Southeast", "Southwest", "WestCoast", "Western",

                     "R9", "R10", "R11", "R12", "R13", "R14",

                     "R15", "R16", "R17", "R18", "R19", "R20"), -- Future 
Regions

    

    -- The status of the npa split

    status      ENUM("NotSet", "Pending", "Active", "Error"),



    -- Old npa, new npa and nxx form primary unique key

    PRIMARY KEY (oldnpa, newnpa, nxx)

)

TYPE = MyIsam;



--
-- Create SubscriptionVersion table
--
-- The Fields are defined in the order and format that are defined by the
-- NPAC bulk data file. This allows the SQL LOAD DATA command to be used
-- to load tables which is extremely fast.
--
-- Revision History
-- 15-may-07  ARICENT  Feature 110663: NANC 399
--
CREATE TABLE  SubscriptionVersion
(
    -- Required field (Primary key)
    versionId            INT              NOT NULL,

    -- Required field (10 numeric character unique key)
    tn                   CHAR(10)         NOT NULL,

    -- Optional field (10 numeric characters, Empty string means not present)
    lrn                  CHAR(10)         NOT NULL DEFAULT "",

    -- Required field (1-4 characters) 
    newCurrentSp         CHAR(4)          NOT NULL DEFAULT "0000",

    -- Required field (14 characters "YYYYMMDDHHMMSS") 
    activationTimestamp  CHAR(14)         NOT NULL DEFAULT "00000000000000",

    -- Optional field (9 characters, Empty string means not present)
    classDPC             CHAR(9)          NOT NULL DEFAULT "",

    -- Optional field (1-3 characters, Empty string means not present)
    classSSN             CHAR(3)          NOT NULL DEFAULT "",

    -- Optional field (9 characters, Empty string means not present)
    lidbDPC              CHAR(9)          NOT NULL DEFAULT "",

    -- Optional field (1-3 characters, Empty string means not present)
    lidbSSN              CHAR(3)          NOT NULL DEFAULT "",

    -- Optional field (9 characters, Empty string means not present)
    isvmDPC              CHAR(9)          NOT NULL DEFAULT "",

    -- Optional field (1-3 characters, Empty string means not present)
    isvmSSN              CHAR(3)          NOT NULL DEFAULT "",

    -- Optional field (9 characters, Empty string means not present)
    cnamDPC              CHAR(9)          NOT NULL DEFAULT "",

    -- Optional field (1-3 characters, Empty string means not present)
    cnamSSN              CHAR(3)          NOT NULL DEFAULT "",

    -- Optional field (1-12 numeric characters, Empty string means not present)
    endUserLocationValue CHAR(12)         NOT NULL DEFAULT "",

    -- Optional field (2 numeric characters, Empty string means not present)
    endUserLocationType  CHAR(2)          NOT NULL DEFAULT "",

    -- Required field (1-4 characters, Empty string means not present)
    billingId            CHAR(4)          NOT NULL DEFAULT "",

    -- Required field (lspp(0), lisp(1), pool(2))
    lnpType              TINYINT UNSIGNED NOT NULL DEFAULT 0,

    -- Required field (new(0), delete(1), modified(2), audit-descrepancy(3)
    downloadReason       TINYINT UNSIGNED NOT NULL DEFAULT 0,

    -- Optional field (9 characters, Empty string means not present)
    wsmscDPC             CHAR(9)          NOT NULL DEFAULT "",

    -- Optional field (1-3 characters, Empty string means not present)
    wsmscSSN             CHAR(3)          NOT NULL DEFAULT "",

    -- Optional field  (wireline(0), wireless(1), voIP(2), voWiFi(3), sv_type_4(4), sv_type_5(5), sv_type_6(6) )
    svType TINYINT NOT NULL DEFAULT -1,

    -- Optional field (1-4 CHARACTERS)
    alternativeSPIDCHAR(4)         NOT NULL DEFAULT "",
  
    -- Primay key is the Npac SubscriptionVersion id
    PRIMARY KEY (versionId),

    -- TN must be indexed and unique
    UNIQUE KEY tn (tn),

    -- Index lrn, for LSMS Subscription Version by LRN reports
    INDEX (lrn),

    -- Index lrn, for LSMS Subscription Version by SPID reports
    INDEX (newCurrentSp)

)
TYPE = MyIsam;

--
-- Create NumberPoolBlock table
--
-- The Fields are defined in the order and format that are defined by the
-- NPAC bulk data file. This allows the SQL LOAD DATA command to be used
-- to load tables which is extremely fast.
--
CREATE TABLE  NumberPoolBlock
(
    -- Required field (Primary key)
    blockId              INT              NOT NULL,

    -- Required field (7 numeric characters, Unique key)
    npanxx_x             CHAR(7)          NOT NULL,

    -- Optional field (10 numeric characters, Empty string means not present)
    lrn                  CHAR(10)         NOT NULL DEFAULT "",

    -- Required field (1-4 characters) 
    newCurrentSp         CHAR(4)          NOT NULL DEFAULT "0000",

    -- Required field (14 characters "YYYYMMDDHHMMSS") 
    activationTimestamp  CHAR(14)         NOT NULL DEFAULT "00000000000000",

    -- Optional field (9 characters, Empty string means not present)
    classDPC             CHAR(9)          NOT NULL DEFAULT "",
    -- Optional field (1-3 characters, Empty string means not present)
    classSSN             CHAR(3)          NOT NULL DEFAULT "",

    -- Optional field (9 characters, Empty string means not present)
    lidbDPC              CHAR(9)          NOT NULL DEFAULT "",
    -- Optional field (1-3 characters, Empty string means not present)
    lidbSSN              CHAR(3)          NOT NULL DEFAULT "",

    -- Optional field (9 characters, Empty string means not present)
    isvmDPC              CHAR(9)          NOT NULL DEFAULT "",
    -- Optional field (1-3 characters, Empty string means not present)
    isvmSSN              CHAR(3)          NOT NULL DEFAULT "",

    -- Optional field (9 characters, Empty string means not present)
    cnamDPC              CHAR(9)          NOT NULL DEFAULT "",
    -- Optional field (1-3 characters, Empty string means not present)
    cnamSSN              CHAR(3)          NOT NULL DEFAULT "",

    -- Optional field (9 characters, Empty string means not present)
    wsmscDPC             CHAR(9)          NOT NULL DEFAULT "",
    -- Optional field (1-3 characters, Empty string means not present)
    wsmscSSN             CHAR(3)          NOT NULL DEFAULT "",

    -- Required field (new(0), delete(1), modified(2), audit-descrepancy(3)
    -- Changed DEFAULT from "" to 0 when migrated MySQL from 4.1.11 to 5.0.37
    downloadReason       TINYINT UNSIGNED NOT NULL DEFAULT 0,

    -- Optional field  (wireline(0), wireless(1), voIP(2), voWiFi(3), sv_type_4(4), sv_type_5(5), sv_type_6(6) )
    svType TINYINT NOT NULL DEFAULT -1,

    -- Optional field (1-4 CHARACTERS)
    alternativeSPID     CHAR(4)         NOT NULL DEFAULT "",

    -- Primay key is the Npac NumberPoolBlock id
    PRIMARY KEY (blockId),

    -- TN must be indexed and unique
    UNIQUE KEY npanxx_x (npanxx_x),

    -- Index lrn, for LSMS Number Pool Block by LRN reports 
    INDEX (lrn),

    -- Index lrn, for LSMS Number Pool Block by SPID reports
    INDEX (newCurrentSp)
)
TYPE = MyIsam;

--
-- Create ServiceProvNetwork table
--
-- The Fields are defined in the order and format that are defined by the
-- NPAC bulk data file
--
CREATE TABLE ServiceProvNetwork
(
    -- Required field (Primary key)
    serviceProvId    CHAR(4)       NOT NULL,

    -- Required field (1 - 40 characters)
    serviceProvName  CHAR(40)      NOT NULL DEFAULT "",

     -- Service Provider type
    serviceProvType  ENUM("wireline", "wireless", "non_carrier", "sp_type_3", 
"sp_type_4", "sp_type_5") NULL DEFAULT NULL,

    -- Prmary key is the Service Provider ID
    PRIMARY KEY (serviceProvId)
)
TYPE = MyIsam;

--
-- Create ServiceProvLRN table
--
-- The Fields are defined in the order that are defined by the
-- NPAC bulk data file
--
CREATE TABLE ServiceProvLRN
(
    -- Foreign key -> ServiceProvNetwork
    serviceProvId     CHAR(4)   NOT NULL,      

    -- Required field (Primary key within each ServiceProvNetwork)
    id                INT       NOT NULL,

    -- Required field (10 numeric characters)
    lrn               CHAR(10)  NOT NULL,

    -- Required field (14 characters "YYYYMMDDHHMMSS") 
    creationTimeStamp CHAR(14)  NOT NULL DEFAULT "00000000000000",

    -- Required field (new(0), delete(1), modified(2), audit-descrepancy(3)
    downloadReason    TINYINT   NOT NULL DEFAULT 0,

    -- Primary key is the Npac id within each ServiceProvNetwork
    PRIMARY KEY (serviceProvId, id),

    -- Lrn is unique key within each ServiceProvNetwork
    UNIQUE KEY lrn (serviceProvId, lrn),

    -- Index lrn
    INDEX (lrn),

    -- Not used by MySql but included for documentation
    FOREIGN KEY (serviceProvId) REFERENCES ServiceProvNetwork(serviceProvId)
)
TYPE = MyIsam;

--
-- Create ServiceProvNPA_NXX table
--
-- The Fields are defined in the order defined by the NPAC bulk data file
-- but the npac file formats the npanxx as 'npa-nxx'.
--
CREATE TABLE ServiceProvNPA_NXX
(
    -- Foreign key -> ServiceProvNetwork
    serviceProvId      CHAR(4)  NOT NULL,   

    -- Required field (Primary Unique Key)
    id                 INT      NOT NULL,

    -- Required field (6 numeric characters)
    npanxx             CHAR(6)  NOT NULL,

    -- Required field (14 characters "YYYYMMDDHHMMSS") 
    creationTimeStamp  CHAR(14) NOT NULL DEFAULT "00000000000000",

    -- Required field (14 characters "YYYYMMDDHHMMSS") 
    effectiveTimeStamp CHAR(14) NOT NULL DEFAULT "00000000000000",

    -- Required field (new(0), delete(1), modified(2), audit-descrepancy(3)
    downloadReason     TINYINT  NOT NULL DEFAULT 0,

    -- Primary key is the Npac id within each ServiceProvNetwork
    PRIMARY KEY (serviceProvId, id),

    -- NpaNxx is unique key within each ServiceProvNetwork
    UNIQUE KEY npanxx (serviceProvId, npanxx),

    -- Index npanxx
    INDEX (npanxx),

    -- Not used by MySql but included for documentation
    FOREIGN KEY (serviceProvId) REFERENCES ServiceProvNetwork(serviceProvId)

)
TYPE = MyIsam;

--
-- Create ServiceProvNPA_NXX_X table
--
-- The Fields are defined in the order defined by the NPAC bulk data file
-- but the npac file formats the npanxx as 'npa-nxx-x'.
--
CREATE TABLE ServiceProvNPA_NXX_X
(
    -- Foreign key -> ServiceProvNetwork
    serviceProvId      CHAR(4)  NOT NULL,

    -- Required field (Primary Unique Key)
    id                 INT      NOT NULL,

    -- Required field (7 numeric characters)
    npanxx_x           CHAR(7)  NOT NULL,

    -- Required field (14 characters "YYYYMMDDHHMMSS") 
    creationTimeStamp  CHAR(14) NOT NULL DEFAULT "00000000000000",

    -- Required field (14 characters "YYYYMMDDHHMMSS") 
    effectiveTimeStamp CHAR(14) NOT NULL DEFAULT "00000000000000",

    -- Required field (14 characters "YYYYMMDDHHMMSS") 
    modifiedTimeStamp  CHAR(14) NOT NULL DEFAULT "00000000000000",

    -- Required field (new(0), delete(1), modified(2), audit-descrepancy(3)
    downloadReason     TINYINT  NOT NULL DEFAULT 0,

    -- Primary key is the Npac id within each ServiceProvNetwork
    PRIMARY KEY (serviceProvId, id),

    -- NpaNxx is unique key within each ServiceProvNetwork
    UNIQUE KEY npanxx_x (serviceProvId, npanxx_x),

    -- Index npanxx_x
    INDEX (npanxx_x),

    -- Not used by MySql but included for documentation
    FOREIGN KEY (serviceProvId) REFERENCES ServiceProvNetwork(serviceProvId)
)
TYPE = MyIsam;





-- Create Eagle Measurements Table

-- $S is replaced by CLLI for EMS in Table Name (ie, 
STPAEagleMeasurments)

CREATE TABLE $SEagleMeasurements (

        yyyydddhh   INT UNSIGNED NOT NULL, 

        UpdTnSuccess    INT UNSIGNED NOT NULL DEFAULT 0, 

        UpdTnFail       INT UNSIGNED NOT NULL DEFAULT 0, 

        DelTnSuccess    INT UNSIGNED NOT NULL DEFAULT 0, 

        DelTnFail       INT UNSIGNED NOT NULL DEFAULT 0, 

                         

        UpdDGttSuccess  INT UNSIGNED NOT NULL DEFAULT 0, 

        UpdDGttFail     INT UNSIGNED NOT NULL DEFAULT 0, 

        DelDGttSuccess  INT UNSIGNED NOT NULL DEFAULT 0, 

        DelDGttFail     INT UNSIGNED NOT NULL DEFAULT 0, 

                        

        UpdOGttSuccess  INT UNSIGNED NOT NULL DEFAULT 0, 

        UpdOGttFail     INT UNSIGNED NOT NULL DEFAULT 0, 

        DelOGttSuccess  INT UNSIGNED NOT NULL DEFAULT 0,  

        DelOGttFail     INT UNSIGNED NOT NULL DEFAULT 0, 

                        

        UpdSplitSuccess INT UNSIGNED NOT NULL DEFAULT 0, 

        UpdSplitFail    INT UNSIGNED NOT NULL DEFAULT 0, 

        DelSplitSuccess INT UNSIGNED NOT NULL DEFAULT 0, 

        DelSplitFail    INT UNSIGNED NOT NULL DEFAULT 0, 

        Binds           INT UNSIGNED NOT NULL DEFAULT 0, 

        LsmsRetries     INT UNSIGNED NOT NULL DEFAULT 0,  

        NERetries       INT UNSIGNED NOT NULL DEFAULT 0, 

        

        PRIMARY KEY (yyyydddhh) 

) 

TYPE = MyIsam;";