A Configuring the Query Server

This appendix provides overview information as well as detailed, step-by-step configuration procedures to get the query server up-and-running.

Overview of the Query Server Package

The optional LSMS Query Server Package enables customers to access real time LNP data—automatically—using a standard API. Customers can perform customized, high volume automated data queries for use by internal office and support systems such as systems for service assurance, testing, service fulfillment, and customer care.

The Query Server Package provides a query server database which consists of replicated copies of the LSMS LNP databases, as shown in Table A-1 through Table A-2. The provision of this database enables customers to write applications, using SQL, ODBC, or JDBC interfaces, to access the data in the database. The query server supports direct query of objects and attributes in the database. The user has the flexibility to customize SQL queries in order to create new queries. No predefined queries are provided with this feature.

The query server resides on a separate platform from the LSMS, and maintains a separate and distinct copy of the LNP data. Customers must provide their own hardware system that is consistent with the platform specifications provided by Oracle Communications. Hosting a copy of the LSMS database on this separate platform provides the following benefits:

• High volumes of customized queries can be performed without processing impact on the LSMS. These queries are standard, non-updating SQL queries.

• Live backups of the database can be accomplished by performing a backup on the query server.

Note:

For purposes of quantifying the number of EAGLE nodes supported by the LSMS (so that the maximum number of supported EAGLE nodes is not exceeded), each query server supported must be counted as one EAGLE node. For example, if the LSMS is configured to support 8 pairs of EAGLE, each query server constitutes one EAGLE node (half of a pair).

If additional query servers are desired after the maximum number of supported EAGLE is reached, customers can daisy-chain additional query servers from a query server that is directly-connected to the LSMS. However, the LSMS cannot monitor connectivity to, or status of, daisy-chained query servers.

This feature includes the complete software package as well as information about notifications, the automated system check feature, configuration, maintenance, platform requirements and recommendations, the LSMS command line utility and command summary, and the query server error log.

Note:

Installation and configuration of software at the query server and the LSMS are supported. The feature provides for the replication of the data to the query server. Applications, network configuration to the query server, and development of interfaces to the query server database are the responsibility of the customer. For information about the database structure to be used to develop customer-provided applications, refer to the Alarms and Maintenance Guide.

Enable Query Server Feature

To enable this feature, perform this procedure:

1. Login to the LSMS as lsmsadm.
2. Issue the command dbcfginternal QUERY_SERVER <Y|N>.

   Use the value Y to enable the feature and N to disable the feature.

The Query Server can now be configured according to procedures contained in this Appendix.

Enable ResyncDB Query Server Feature

The ResyncDB Query Server feature enables the LSMS to directly host the ResyncDB Query Server. To enable this feature, perform this procedure:

1. Login to the LSMS as lsmsadm.
2. Issue the command dbcfginternal RESYNCDB_QUERY_SERVER <Y|N>.

   Use the value Y to enable the feature and N to disable the feature.

3. The value of RESYNCDB_QUERY_SERVER will be updated in the database.

After setting the values to "Y," the ResyncDB Query Server can now be configured according to procedures contained in the Query Server Feature Technical Reference, TR005579.

Overview of Database Replication

The query server system is provisioned from the Oracle Communications LSMS using database replication techniques provided by MySQL, as illustrated in Figure A-1. The one-way replication functionality is based on a master-slave relationship between two or more servers, with one (the LSMS) acting as the master, and others (query servers) acting as slaves. The LSMS keeps a binary log of updates (creates, modifies, deletes, etc.) that is made available to one or more query servers.

The query servers run on separate hardware, connected by the network. Each query server, upon connecting to the LSMS, informs the LSMS where it left off since the last successfully propagated update, synchronizes itself by reading the LSMS’s binary log file and executing the same actions on its copy of the data, then blocks and waits for new updates to be processed.

The slave servers mirror these changes a short time after they occur on the LSMS. Other than the brief periods when query servers are synchronizing, each query server mirrors the LSMS. If the LSMS becomes unavailable or the query server loses connectivity with the master, the query server tries to reconnect every 60 seconds until it is able to reconnect and resume listening for updates. The amount of time a query server can be disconnected (not replicating) from the LSMS before it can no longer reconnect and resume replication and must be completely reloaded is dependent only on the availability of the binary log files on the LSMS. The LSMS application actively manages the number of binary logs available on the server, always keeping the ten most recent binary log files (up to 10 GB worth of updates).

The purging of binary logs may occur. If there is some connectivity issue between the Query Server and the LSMS, the binary logs will not be removed. In this case, logs are forcefully removed if BIN_LOG_THRESHOLD parameter is set.

If the query server database becomes corrupted or back-level such that it cannot be automatically resynchronized, you can reload it from either the LSMS or from another query server (for more information, refer to the Alarms and Maintenance Guide).

Query servers connect to the LSMS application using a VIP (virtual IP) address on the application network. The VIP address ensures that query servers are constantly connected to the active server. In the event of an application switch over in which the active LSMS server changes (for instance, from server A to server B), the query servers follow the active server and reconnect automatically to the new active LSMS server.

To enable this capability, the LSMS application actively manages the binary logs on both servers to ensure they remain synchronized. It is important that the binary logs on the LSMS servers are not removed or reset except by the LSMS application, because this change could negatively impact the database replication occurring between the two LSMS servers as well as the query servers.

Figure A-1 LSMS Query Server Overview

LNP Data Replicated on the Query Server

The LSMS supports replication of the following LNP data to a local or remote query server:

• Telephone Number (Subscription Version) (NPAC data)

  • Version ID
  • TN
  • LRN
  • Service Provider
  • CLASSDPC, SSN
  • CNAMDPC, SSN
  • ISVMDPC, SSN
  • LIDBDPC, SSN
  • WSMSCDPC, SSN (if optional feature is provisioned)
  • LNP type
  • Billing ID
  • End User Location
  • End User Value
  • Activation Timestamp
  • Download reason
  • SV Type
  • Alternative SPID

• Number Pool Block (NPAC data)

  • Block ID
  • NPA-NXX-X
  • LRN
  • Service Provider
  • CLASSDPC, SSN
  • CNAMDPC, SSN
  • ISVMDPC, SSN
  • LIDBDPC, SSN
  • WSMSCDPC, SSN (if optional feature is provisioned)
  • Activation Timestamp
  • Download reason
  • SV Type
  • Alternative SPID
• NPAC network data (for example, LRN, NPA-NXX) (NPAC data)
• Default GTT (locally provisioned data)
• Override GTT (locally provisioned data)
• NPA Split information (locally provisioned data)
• TN filters (locally provisioned data)

The Query Server database consists of replicated copies of the LSMS LNP database tables as shown below.

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 A-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	activation Timestamp	downloadReason
	SVType	alternativeSPID
ServiceProvLRN	serviceProviderId	id	lrn	creationTimeStamp
	downloadReason
ServiceProv NPA_NXX	serviceProviderId	id	npanxx	creationTimeStamp
	effective TimeStamp	downloadReason
ServiceProv NPA_NXX_X	serviceProviderId	id	npanxx_x	creationTimeStamp
	effective TimeStamp	modified TimeStamp	downloadReason
ServiceProv Network	serviceProvId	serviceProvName	serviceProvType
Where <Region> is one of the following:	Canada	MidAtlantic	Midwest	Northeast
	Southeast	Southwest	WestCoast	Western

Below is detailed information about the Regional Database table and fields.

Note:

The following information was taken from actual source code. It may contain irrelevant data, such as comments.

--
-- 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)
    
    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;

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 A-2 Supplemental Database Tables and Fields

Supplemental (supDB) LNP Database Tables	Fields
AlarmFilter	eventNumber	activateSurvFiltering	filterType	timeStamp
	timeout	counter
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
LsmsService Provider	spid	description	contactInfo
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	capabilityPointCode
	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	secondaryNpacPsel
	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:

By default, the following Supplemental (SupDB) LNPDatabase Tables are not replicated:

• <Region>PublicKey

• <Region>PrivateKey

• LsmsUser

• LsmsUserSpid

  To replicate these tables, see the Notes in unresolvable-reference.html#GUID-1436BF91-AFDF-47BF-9628-EF683225C509__V998816 of the topic unresolvable-reference.html#GUID-1436BF91-AFDF-47BF-9628-EF683225C509.

Below is detailed information about the Supplemental Database tables and fields.

Note:

The following information was taken from actual source code. It may contain irrelevant data, such as comments.

--
-- Create NpacRegion table
--
-- One NpacRegion defines the configuration of the primary and secondary NPAC.
--
-- Revision History
--
-- 19-Dec-03  Groff  Feature 53384: Customizable Login Message.
-- 14-Jul-06  FSS    Feature 103276: Password Expiration.
-- 14-may-07  ARICENT  Feature 110663: NANC 399
--
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",
            "psel", "ssel", "", "000000000000",
            "cw7", "cw7", "", "000000000000",
            "0.0.0.0",
            "", "", "", "000000000000",
            "0.0.0.0",
            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',
            '"Lsms Admin","[e-mail]","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1234","9195551234"',
            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),
           ("MidAtlantic", "Mid-Atlantic Regional NPAC SMS",
            "psel", "ssel", "", "000000000000",
            "cw1", "cw1", "", "000000000000",
            "0.0.0.0",
            "", "", "", "000000000000",
            "0.0.0.0",
            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',
            '"Lsms Admin","[e-mail]","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1234","9195551234"',
            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),
           ("Midwest", "Midwest Regional NPAC SMS",
            "psel", "ssel", "", "000000000000",
            "cw0", "cw0", "", "000000000000",
            "0.0.0.0",
            "", "", "", "000000000000",
            "0.0.0.0",
            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',
            '"Lsms Admin","[e-mail]","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1234","9195551234"',
            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),
           ("Northeast", "Northeast Regional NPAC SMS",
            "psel", "ssel", "", "000000000000",
            "cw2", "cw2", "", "000000000000",
            "0.0.0.0",
            "", "", "", "000000000000",
            "0.0.0.0",
            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',
            '"Lsms Admin","[e-mail]","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1234","9195551234"',
            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),
           ("Southeast", "Southeast Regional NPAC SMS",
            "psel", "ssel", "", "000000000000",
            "cw3", "cw3", "", "000000000000",
            "0.0.0.0",
            "", "", "", "000000000000",
            "0.0.0.0",
            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',
            '"Lsms Admin","[e-mail]","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1234","9195551234"',
            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),
           ("Southwest", "Southwest Regional NPAC SMS",
            "psel", "ssel", "", "000000000000",
            "cw4", "cw4", "", "000000000000",
            "0.0.0.0",
            "", "", "", "000000000000",
            "0.0.0.0",
            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',
            '"Lsms Admin","[e-mail]","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1234","9195551234"',
            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),
           ("WestCoast", "West Coast Regional NPAC SMS",
            "psel", "ssel", "", "000000000000",
            "cw6", "cw6", "", "000000000000",
            "0.0.0.0",
            "", "", "", "000000000000",
            "0.0.0.0",
            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',
            '"Lsms Admin","[e-mail]","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1234","9195551234"',
            DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")),
           ("Western", "Western Regional NPAC SMS",
            "psel", "ssel", "", "000000000000",
            "cw5", "cw5", "", "000000000000",
            "0.0.0.0",
            "", "", "", "000000000000",
            "0.0.0.0",
            '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"',
            '"Lsms Admin","[e-mail]","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1234","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(64) NOT NULL,

    -- The assigned inactivity timeout
    inactivityTimeout    CHAR(11) NOT NULL DEFAULT '-1',

    -- The user level password timeout
    UsrPwdExpInterval SMALLINT NOT NULL DEFAULT -1,

    -- The first logon flag
    FirstLogonFlag    BIT NOT NULL DEFAULT 0,

    -- The password changed date
    LastUpdDate       DATE NOT NULL DEFAULT '1970-01-01',

    -- Primary key is the user name
    PRIMARY KEY (name)
)
TYPE = MyIsam;
-- Create default 'golden' users
INSERT INTO LsmsUser (name, golden, groupName)
            VALUES('lsmsadm',1,'lsmsadm'),
                  ('lsmsuser',1,'lsmsuser'),
                  ('lsmsview',1,'lsmsview'), 
                  ('lsmsall',1,'lsmsall'),
                  ('lsmsuext',1,'lsmsuext'),
                  ('command-line',1,'lsmsadm');
--
-- 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(11)   NOT NULL,

    emsType             ENUM("OAP", "MPS", "TEKPATH") 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(11)   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 TABLE IF NOT EXISTS PrivateKeyModel
(
    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 PrivateKeyModel;

-- Create NortheastPrivateKey table
CREATE TABLE  NortheastPrivateKey
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PrivateKeyModel;

-- Create MidAtlanticPrivateKey table
CREATE TABLE  MidAtlanticPrivateKey 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PrivateKeyModel;

-- Create MidwestPrivateKey table
CREATE TABLE  MidwestPrivateKey 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PrivateKeyModel;

-- Create SoutheastPrivateKey table
CREATE TABLE  SoutheastPrivateKey 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PrivateKeyModel;

-- Create SouthwestPrivateKey table
CREATE TABLE  SouthwestPrivateKey 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PrivateKeyModel;

-- Create WestCoastPrivateKey table
CREATE TABLE  WestCoastPrivateKey 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PrivateKeyModel;

-- Create WesternPrivateKey table
CREATE TABLE  WesternPrivateKey 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PrivateKeyModel;

--
-- Create "Model" PublicKey table
--
CREATE TABLE IF NOT EXISTS PublicKeyModel
(
    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 PublicKeyModel;

-- Create NortheastPublicKey table
CREATE TABLE  NortheastPublicKey
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PublicKeyModel;

-- Create MidAtlanticPublicKey table
CREATE TABLE  MidAtlanticPublicKey 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PublicKeyModel;

-- Create MidwestPublicKey table
CREATE TABLE  MidwestPublicKey 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PublicKeyModel;

-- Create SoutheastPublicKey table
CREATE TABLE  SoutheastPublicKey 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PublicKeyModel;

-- Create SouthwestPublicKey table
CREATE TABLE  SouthwestPublicKey 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PublicKeyModel;

-- Create WestCoastPublicKey table
CREATE TABLE  WestCoastPublicKey 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PublicKeyModel;

-- Create WesternPublicKey table
CREATE TABLE  WesternPublicKey 
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) SELECT * FROM PublicKeyModel;

--
-- Create one measurements table for each region
--
-- Create "Model" NpacMeasurements table
CREATE TABLE IF NOT EXISTS NpacMeasurementsModel
(
    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 NpacMeasurementsModel;

-- Create NortheastNpacMeasurements table
CREATE TABLE  NortheastNpacMeasurements
(
    PRIMARY KEY (yyyydddhh)
) SELECT * FROM NpacMeasurementsModel;

-- Create MidAtlanticNpacMeasurements table
CREATE TABLE  MidAtlanticNpacMeasurements
(
    PRIMARY KEY (yyyydddhh)
) SELECT * FROM NpacMeasurementsModel;

-- Create MidwestNpacMeasurements table
CREATE TABLE  MidwestNpacMeasurements
(
    PRIMARY KEY (yyyydddhh)
) SELECT * FROM NpacMeasurementsModel;

-- Create SoutheastNpacMeasurements table
CREATE TABLE  SoutheastNpacMeasurements
(
    PRIMARY KEY (yyyydddhh)
) SELECT * FROM NpacMeasurementsModel;

-- Create SouthwestNpacMeasurements table
CREATE TABLE  SouthwestNpacMeasurements
(
    PRIMARY KEY (yyyydddhh)
) SELECT * FROM NpacMeasurementsModel;

-- Create WestCoastNpacMeasurements table
CREATE TABLE  WestCoastNpacMeasurements
(
    PRIMARY KEY (yyyydddhh)
) SELECT * FROM NpacMeasurementsModel;

-- Create WesternNpacMeasurements table
CREATE TABLE  WesternNpacMeasurements
(
    PRIMARY KEY (yyyydddhh)
) SELECT * FROM NpacMeasurementsModel;

--
-- 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"),
    ("Canada",      "NPAC_BIND_TIMEOUT",      "Bind Timeout with NPAC", "15"),
    ("Canada",      "BIND_RETRY_INTERVAL",    "Retry seconds for Bind requests", "120"),
    ("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"),
    ("MidAtlantic", "NPAC_BIND_TIMEOUT",      "Bind Timeout with NPAC", "15"),
    ("MidAtlantic", "BIND_RETRY_INTERVAL",    "Retry seconds for Bind requests", "120"),
    ("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"),
    ("Midwest",     "NPAC_BIND_TIMEOUT",      "Bind Timeout with NPAC", "15"),
    ("Midwest",     "BIND_RETRY_INTERVAL",    "Retry seconds for Bind requests", "120"),
    ("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"),
    ("Northeast",   "NPAC_BIND_TIMEOUT",      "Bind Timeout with NPAC", "15"),
    ("Northeast",   "BIND_RETRY_INTERVAL",    "Retry seconds for Bind requests", "120"),
    ("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"),
    ("Southeast",   "NPAC_BIND_TIMEOUT",      "Bind Timeout with NPAC", "15"),
    ("Southeast",   "BIND_RETRY_INTERVAL",    "Retry seconds for Bind requests", "120"),
    ("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"),
    ("Southwest",   "NPAC_BIND_TIMEOUT",      "Bind Timeout with NPAC", "15"),
    ("Southwest",   "BIND_RETRY_INTERVAL",    "Retry seconds for Bind requests", "120"),
    ("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"),
    ("WestCoast",   "NPAC_BIND_TIMEOUT",      "Bind Timeout with NPAC", "15"),
    ("WestCoast",   "BIND_RETRY_INTERVAL",    "Retry seconds for Bind requests", "120"),
    ("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"),
    ("Western",     "NPAC_BIND_TIMEOUT",      "Bind Timeout with NPAC", "15"),
    ("Western",     "BIND_RETRY_INTERVAL",    "Retry seconds for Bind requests", "120"),

    ("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", "COMMAND_CLASS", "Enable Command Class Managment feature",         "N" ),
    ("Internal", "NANC_3_2_ENHANCEMENTS",  "Enable NANC 3.2 enhancements feature",  "N" ),
    ("Internal", "NPAC_RECOVERY_PERIOD", "NPAC Download Request Time Period",       "60" ),
    ("Internal", "LOGIN_MSG",     "Enable Customizable Login Message",              "N" ),
    ("Internal", "INACTIVITY_TIMEOUT", "Gui and Shell inactivity timeout feature",  "N" ),
    ("Internal", "SYSTEM_INACTIVITY_TIMEOUT", "System wide GUI and Shell inactivity timeout value", "15" ),
    ("Internal", "LOG_EAGLE_SUCCESS_RESP", "Log time for successful Eagle response", "N" ),
    ("Internal", "RESYNCDB_QUERY_SERVER", "Enable ResyncDB Query Server feature",    "N" ),
    ("Internal", "HSOP_BUNDLING", " Enable HSOP bundling feature",                   "Y" ),
    ("Internal", "NPAC_HEARTBEAT_TIMEOUT", "Timeout seconds for NPAC heartbeat","60" ),
    ("Internal", "NPAC_HEARTBEAT_RETRY_NUMBER", "Retry times for NPAC heartbeat","3" ),
    ("Internal", "NPAC_HEARTBEAT_QUIET_PERIOD_TIMEOUT", "Timeout seconds for NPAC heartbeat quiet period","900" ),
    ("Internal", "NPAC_HEARTBEAT_QUIET_PERIOD_TIMEOUT_CANADA", "Timeout seconds for NPAC heartbeat quiet period (Canada)","100000" ),
    ("Internal", "DEFAULT_PASSWORD_TIMEOUT", "System wide GUI and Shell password timeout", "0" ),
    ("Internal", "NANC_3_3_FEATURE_SET", " Enable the support of NANC 3.3 Feature Set","N" ),
    ("Internal", "SERVICE_PROV_TYPE", " Enable the support of Service Provider Type","N" ),
    ("Internal", "SWIM_RECOVERY", " Enable the support of SWIM Recovery Feature","N" ),
    ("Internal", "CANADA_SPID_RECOVERY", " Enable the support of Canada SPID Recovery","N" ),
    ("Internal", "ERROR_CODES_FOR_ACTIONS", " Enable the support of Action Error Codes","N" ),
    ("Internal", "ERROR_CODES_FOR_NON_ACTIONS", " Enable the support of Non-Action Error Codes","N" ),
    ("Internal", "SV_TYPE", " Enable SV Type feature","N" ),
    ("Internal", "ALT_SPID", " Enable Alternative SPID feature","N" ),
    ("Internal", "SURV_OK_TRAP", "Send SNMP trap every 5 minutes that Surveillance is running","N" ), ("Internal", "SERVDI_ENABLED", "Enable SERVDI feature","N" ), ("Internal", "ALARM_FILTERING", " Enable LSMS Alarm Filtering Feature","N" ), ("Internal", "MYSQL_PORT", " Enable LSMS Configurable MySQL Port Feature","N" ), ("Lsms", "LNP_QTY_THRESHOLD", "Configurable percent","80" ), ("Internal", "BINLOGS_THRESHOLD", "Threshold for forceful purging","98" ),
    ("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","[e-mail]","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1234","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 Authorization table
--
CREATE TABLE Authorization
(
    -- The group (Primary Key)
    groupName  CHAR(64) NOT NULL,

    -- The function (Primary Key)
    function   CHAR(64) NOT NULL,

    -- Whether this function may be performed by members of this group.
    authorized  BOOL     NOT NULL DEFAULT 0,

    -- Force the group plus the name to be unique
    PRIMARY KEY (groupName, function)
)
TYPE = MyIsam;

--
-- Create default non-configurable user authorizations
--
-- Insert lsmsadm default data for table `Authorization`

Query Server Maintenance

Following is a list of ways to monitor and determine the status of the query server:

• The LSMS monitors the connectivity with each directly-connected query server. GUI messages, surveillance messages, and SNMP traps are generated at the LSMS for failure and recovery of the connection to the query server.

• The LSMS enables customers to check the connection status of directly-connected query servers.

• Instructions are provided to enable customers to determine the status of the replication of LNP data at the query server (refer to “Check MySQL Replication Status on Query Servers” in the Alarms and Maintenance Guide).

Additionally, detailed instructions and procedures are provided to enable customers to perform initialization and recovery procedures in the event of a failure.

For more information, refer to Alarms and Maintenance Guide.

Query Server Requirements

The platform that is used to host a query server must meet the minimum requirements shown in the following tables in order to meet performance requirements.

Table A-3 Query Server Platform Requirements for Solaris 10

Component	Minimum Requirement
Operating System	N/A
Processor	333 Mhz
Memory	256 Megabytes
Minimum Disk Space (in partition containing /usr/mysql1) See Note 1.	10 GB (for up to 48 million TNs) 20 GB (for up to 96 million TNs) 25 GB (for up to 120 million TNs) 40 GB (for up to 192 million TNs) 48 GB (for up to 228 million TNs) 80 GB (for up to 384 million TNs) 95 GBs (for up to 504 million TNs) 480 GBs E5-APPB-02 cards (for up to 756 million TNs)

Note:

• The partitioning and setting up of the /usr/mysql1 file system with the minimum required disk space are the responsibility of the customer.
• The /opt/ file system on the Query Server must contain enough free space to store the MySQL binary executables (325 MB for MySQL 5.6).
• The executable gzip version 1.2.24 cannot decompress files larger than 2 GB. NPAC regions with databases greater than 59 million records require a version of gzip capable of supporting compressed files larger than 2 GB. For this reason, Oracle Communications recommends using gzip version 1.3 or greater.

Table A-4 Query Server Platform Requirements for Linux

Component	Minimum Requirement
Operating System	Oracle Linux Server
Release	7.2
Architecture	X86_64
Processor	Intel(R) Xeon(R) CPU E5-2699 v4 @ 2.20GHz
Memory	4 Gigabytes
Minimum Disk Space	250 Gigabytes

Interface Support

The Query Server supports automated database access using standard interfaces described in this section.

MySQL provides support for various Application Programming Interfaces (APIs) that can be used to create clients to directly query objects and attributes in the LSMS LNP database replica on the query server.

Note:

Because customers have the flexibility to customize SQL queries in order to create new queries, Oracle Communications does not provide “canned queries” with this platform.

ANSI SQL Standard Support

MySQL provides support for the ANSI (American National Standards Institute) SQL Standard (Entry-level SQL92). The MySQL server includes a command-line option for turning on ANSI mode. This mode changes some of MySQL's behavior to better accept SQL statements that are valid according to the SQL-92 standard.

For more information, refer to the section “Running MySQL in ANSI Mode” in the MySQL Reference Manual, available at www.mysql.com.

ODBC Support

MySQL provides support for ODBC (Open Data Base Connectivity) by means of the MyODBC program. MyODBC is a 32-bit ODBC (2.50) level 0 (with level 1 and level 2 features) driver for connecting an ODBC-aware application (such as Microsoft Access, Microsoft Excel, and Crystal Reports) to MySQL.

For more information about how to install and use MyODBC, refer to the section “MySQL ODBC Support” in the MySQL Reference Manual, available at www.mysql.com.

JDBC Support

MySQL supports the following JDBC (Java Data Base Connectivity) driver:

• The MySQL Connector/J driver. You can find a copy of the MySQL Connector/J driver at http://dev.mysql.com/downloads/connector/j/5.6.html

For more information, consult any JDBC documentation and the driver's own documentation for MySQL-specific features.

C, C++, Eiffel, Java, Perl, PHP, Python, and Tcl Support

MySQL provides APIs for C, C++, Eiffel, Java, Perl, PHP, Python, and Tcl. Reference “MySQL APIs” section in [3] for all the APIs available for MySQL, where to get and how to use them.

For more information about where to get one of these APIs and how to use it, refer to the section “MySQL APIs” in the MySQL Reference manual, available at www.mysql.com.

LSMS Query Server Configuration Scenario

Figure A-2 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. 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 non-Oracle Communications provided 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 A-2 LSMS Query Server Configuration Scenario

Query Server Installation and Configuration

Before you use the query server feature, you must perform the following procedures:

1. MySQL Replication Configuration for LSMS
2. MySQL Installation/Upgrade for Query Server Platform
3. MySQL Replication Configuration for Daisy-Chained LSMS Query Servers

MySQL Replication Configuration for LSMS

Use the following procedure to configure the LSMS to support one or more directly-connected query servers.

Note:

Perform all the steps in the following procedure the first time you configure the LSMS system and Linux platform to support the Query Server Package, or to verify that you previously performed all configuration correctly.

Caution:

The following procedure may briefly interrupt traffic being sent to EAGLE from the NPAC and from local LSMS provisioning. The time required to accomplish this procedure depends on the bandwidth of the customer's network and the amount of data to be reloaded. It is recommended that this procedure be performed during a scheduled maintenance window.

1. Activate the LSMS Query Server Package:
   The Query Server Package is an optional feature that must be activated at the LSMS. To activate the Query Server Package, contact the Customer Care Center.
2. Log into the active server as root, and continue with the following steps.
3. Associate the names of the query server hosts with their Internet Protocol (IP) addresses:
   To do this, add an IP address and hostname pair for each query server to the /etc/hosts file on both the primary and secondary LSMS servers. The hostname of the query server will be used to identify each query server when reporting on its status.
4. Setup a special replication user (for each query server) on the LSMS with privileges and permissions that a query server can use to access the LSMS to perform database replication:
   # lsmsdb -c addrepluser -h <hostname> -p <password>

   Note:

   The combination of username and password is unique to replication use and provides read access only to the binary log on the LSMS system. Additionally, access to this user account is restricted to the hostname specified.
5. Remove all (if any) existing snapshots to ensure that a sufficient amount of disk space is available for creating new snapshots of the LSMS data.
   If an alternative location is specified to store the snapshot files, remove all snapshot files from that directory (instead of the default, /var/TKLC/lsms/free):

   # rm /var/TKLC/lsms/free/mysql-snapshot*

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

6. Create a compressed 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 Appendix E of the Alarms and Maintenance Guide.

   Note:

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

   lsmsdb -c snapshot

   During the creation 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

   If you successfully create the snapshot, the LSMS data is captured and stored in the following files in /var/TKLC/lsms/free:

   • mysql-snapshot-supDB.tar.gz
   • mysql-snapshot-<regionalDB>.tar.gz(one file for each region present)
   • snapinfo.sql

You have now completed this procedure.

MySQL Installation/Upgrade for Query Server Platform

For details related to MySQL installation and upgrade for query server platform, refer to LSMS Query Server on Linux Installation and Upgrade Guide.

MySQL Replication Configuration for Daisy-Chained LSMS Query Servers

Use this procedure to configure each query server platform that will have one or more directly-connected daisy-chained query servers. (Perform this procedure on Query Servers B and D, as shown in Figure A-2).

1. Start the MySQL command-line utility on the query server that is directly-connected to the LSMS:
   # cd /usr/mysql1/bin

   # mysql -u root -p

   Enter password:

   <Query Server’s MySql root user password>

2. Set up a special replication user on the slave query server with the FILE privilege and permission that all slaves can use to access the query server from any host:
   mysql> GRANT REPLICATION SLAVE, FILE ON *.* TO '<username>'@"%" IDENTIFIED BY '<password>';

   where <username> and <password> are the replication user's name and password (optional).

   Confirm the slave settings are correct:

   mysql> show GRANTS for 'username' ;

3. 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;

4. Obtain a read lock and flush table cache information:
   The flush writes changes to indexes to the table. The read lock does not allow changes to be made to tables but continues to allow other threads to read from them.

   mysql> FLUSH TABLES WITH READ LOCK;

5. Exit the MySQL command-line utility:
   mysql> exit
6. Shutdown the MySQL server:
   #./mysqladmin -u root -p shutdown

   Enter password: <Query Server’s MySql root user password>

7. Create a snapshot of all the LSMS data.
   Remove all existing compressed snapshot files (if any):

   rm /usr/mysql1/mysql-snapshot*

   Create a compressed snapshot file for the LSMS Supplemental database:

   # tar -cvf - /usr/mysql1/supDB/* | gzip >

   /usr/mysql1/mysql-snapshot-supDB.tar.gz

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

   Note:

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

   # tar -cvf - /usr/mysql1/<regionDB>/* | gzip >

   /usr/mysql1/mysql-snapshot-<regionDB>.tar.gz

8. Add the log-bin, log-slave-updates, and binlog-format=ROW options to the [mysqld] section of the my.cnf option file on the query servers if you plan to daisy-chain one or more query servers from the directly-connected query server.
   This option tells the query server to log the updates from the slave thread to the binary log that daisy-chained query servers use to synchronize their data.

   log-bin=mysql-bin

   log-slave-updates

   binlog-format=ROW

9. Restart the MySQL daemon on the query server that is directly-connected to the LSMS:
   # cd /usr/mysql1/bin

   # ./mysqld_safe &

You have now completed this procedure.