TSP Lab	The IMSI-MSISDN Database Loader	Sga-IMSIMSISDN / Database Loader

The IMSI-MSISDN Database Loader module
`("Sga-IMSIMSISDNDB_Loader")`

Features
User interface
The bottom status-line
The Database
Logging
Configuration

Features

Periodically scans the working directory for Sga-8004.xxx files, created by the Monitor Poller module
Processes the IMSI-MSISDN pairs and loads them into the in-memory database
Maintains the database (drops entries exceeding the preset lifetime, checks inconsistency at every database load)
Can synchronize the database from a remote site
Provides an interface for query clients

User interface

The main menu

Menu	Menuitem	Shortcut(s)	Meaning
[Log]
	Details		Detail level of logging (Off, Normal, Detailed, or Debug)
	Find line containing...	(Ctrl-F)	Finds text in log window
	Find next matching line	(F3)	Finds next occurrence of text in log window
	Auto scroll	()	Allow automatic scrolling of the log screen
[Actions]
	Go on / Pause	(Ctrl-P or )	Starts or pauses the processing of the Sga-8004 files.
	Re-enter client password		The password used for client authentication can be re-entered
	Flush status line counters now!		The status line counters can be flushed
	Flush & zero status line counters now!		The status line counters can be flushed and cleared, too
	Save IMSI-MSISDN record pairs		Dumps in-memory records into a backup file
	Drop old IMSI-MSISDN record pairs		Deletes old record pairs
	DEBUG: Drop very old IMSI-MSISDN record pairs		Not available!
	DEBUG: List IMSI-MSISDN record pairs (max. 100)		Dumps at most 100 records pairs into the log screen / log file
	Load an 'Sga-8999.zip' file		Loads IMSI-MSISDN pair information from an 'Sga-8999.zip' file
	Save settings		Save current GUI settings into the configuration (INI) file

The bottom status-line

Uptime: Time elapsed since module start
Idle./Loading... The state of input data processing

IMSI-MSISDN record pairs
- processed: Number of processed IMSI-MSISDN pairs
- queried: Number of requests received from query clients
  - hit/missed: Number of successful/unsuccessful requests
Long IMSI/MSISDN: Number of cases when the IMSI or the MSISDN is longer than 16 digits

Input: Name of the input file currently being read

IMSI (or MSISDN) index:
- alloc'd: Number of in-memory IMSI (or MSISDN) index records (usage of index memory given in percentage)
- overflown: Number of dropped records due to the index memory is full
- forgotten: Number of index records which are dropped due to getting too old

IMSI-MSISDN record pairs:
- alloc'd: Number of in-memory record pairs (usage of memory given in percentage)
- overflown: Number of dropped record pairs due to the record pair memory is full
- forgotten: Number of record pairs which are dropped due to getting too old

Clients: Number of clients currently connected

Queries:
- AllChanges: Number of requests querying all record pairs for an index (an IMSI or an MSISDN) with their timestampFirstSeen
- +LastUpdateInfo: Number of requests querying all record pairs for an index (an IMSI or an MSISDN) with their timestampFirstSeen and timestampLastSeen
- OnlyLast: Number of requests querying the last record pair for an index (an IMSI or an MSISDN) with its timestampFirstSeen
- +LastUpdateInfo: Number of requests querying the last record pair for an index (an IMSI or an MSISDN) with its timestampFirstSeen and timestampLastSeen
- Answers: Number of answers sent to the clients

The Database

Database structure

There are two different entry types in the database:

index - Containing: IMSI values (or MSISDN values, according to the value of INI/[Advanced]/XXXX configuration entry which can be "IMSI" or "MSISDN")
record - Containing: an MSISDN value belonging to an IMSI index (or an IMSI value belonging to an MSISDN index) + timestampFirstSeen + timestampLastSeen

The database has the following structure:

Index1, Record11, Record12, Record13
Index2, Record21, Record22
Index3, Record31, Record32, Record33, Record34, Record35, Record36
Index4, Record41, Record42, Record43
...

Deletion strategy

The deletion process goes through the indexes and checks all the records for each index. A record will be deleted when the last seen timestamp of it is older than the setting value (dwRecordpairMinLifeSec) AND

the IMSI was seen with another MSISDN later, OR
the IMSI value is a roamer one, OR
the IMSI is a local one and is older than the life time threshold setting for local subscribers (dwHomeRecordpairLifeSec).

An index will be deleted when the last record of it is deleted.

When dwHomeRecordpairLifeSec >= dwRecordpairMinLifeSec then roamers will be deleted from the database after dwRecordpairMinLifeSec and local subscribers will deleted after dwHomeRecordpairLifeSec.
When dwHomeRecordpairLifeSec < dwRecordpairMinLifeSec then dwRecordpairMinLifeSec applies for both types of subscibers.

Note: Deletion process is being executed once a day.

Synchronization

The "Sga-IMSIMSISDN / Database Loader" module writes the content of the database into a back up file at GMT midnight. This back up file is being transferred to an other server.
When the "Sga-IMSIMSISDN / Database Loader" module notices that there is a new back up file in the folder (sBackupFilesFromRemoteSitePath) then updates its database with the differences and eliminates inconsistency.

Logging

The module creates daily logfiles about its operation. The name of the logfile is "modulename_YYYYMMDD.log", reflecting the day when it is created.
The module can write information into the logfile and onto the screen at various detail levels, set by the [Log]/Details menu item. The logging level can be:

Off (0) - warnings/errors are only reported (minimal logfile size and CPU usage)
Normal (1) - information about normal operation are also written into the logfile/screen
Detailed (2) - every received and sent signaling messages are logged
Debug (3) - hexadecimal information of messages is recorded (largest logfile and CPU usage)

Status line counters are logged in every fifteen minutes irrespectively of logging level actually set.

The color coding of the log lines have the following meaning:

Code Color Usage
0 Black Compact data record
1 Red Errors
2 Green Open/close output file/stream
3 Blue Open/close input file/stream
4 Yellow Warnings
5 Light Detailed data (debug info)
6 Dark Grey Detailed data (user info)
7 Purple Operator intervention;
Errors with emphasis

Code	Color	Usage
0	Black	Compact data record
1	Red	Errors
2	Green	Open/close output file/stream
3	Blue	Open/close input file/stream
4	Yellow	Warnings
5	Light	Detailed data (debug info)
6	Dark Grey	Detailed data (user info)
7	Purple	Operator intervention; Errors with emphasis

Configuration (Sga-IMSIMSISDNDB_Loader.ini) file settings

Section	Entry	Example	Meaning
[Position]
	iLeft	140	Position of the program module on the display screen
	iTop	72	Position of the program module on the display screen
	iWidth	995	Position of the program module on the display screen
	iHeight	694	Position of the program module on the display screen
[Options]
	bAutoScroll	True	(Refer to the corresponding menu item!)
[Debug log]
	bInclude_Cleanup_ProblemType1	True	If true then database cleanup problems will be logged
	bInclude_Cleanup_ProblemType2	True	If true then database cleanup problems will be logged
	bInclude_Synch_ProblemType3	True	If true then database synchronization problems will be logged
	bInclude_Synch_ProblemType9	True	If true then database synchronization problems will be logged
[Advanced]
	wLogDetailLevel	3	(Equals to the listed order; refer to the corresponding menu item!)
	sClientPassword	xxx	Password used during query client authentication. When it is not set then a pop-up window will appear to enter a password when the module starts
	wMaxCPUUsagePercent	90	Maximum CPU usage in percent
	dwMaxLinesInLogWindow	1000	Number of lines in the log window on screen
	XXXX	IMSI	Defines the type of the index for the actual module. Possible values: "IMSI" or "MSISDN"
	dwXXXXcountMax	3500000	Defines the maximum amount of indexes
	dwRecordpairMaxCount	3500000	Defines the maximum amount of IMSI-MSISDN records pairs
	dwRecordpairMinLifeSec	31536000	Life time for IMSI-MSISDN record pairs
	dwHomeRecordpairLifeSec	31539600	Life time for local IMSI-MSISDN record pairs
	wLocalTCPport	7103	TCP port for the incoming connections
	sLogFilesPath	d:\LogFiles	Path of the LOG files.
	sBackupFilesPath	d:\Backup	Path for the saving of the content of the database in case of module exit
	sBackupFilesPath{2\|3}	d:\Backup{2\|3}	Optional paths for the saving of the content of the database for snychronization purposes
	sBackupFilesFromRemoteSitePath	d:\BackupFilesFromRemote	Path for input backup files from a remote site
	s8999zipFileToBeLoaded	d:\8999zipFiles\Sga-8999_001.gz	Path for zipped Sga-8999 files. For testing purposes.
[Exclude list]	This section can contain a list of IMSI's or MSISDN's (depends on the value of [Advanced]/XXXX) exlcuded from collecting then in the database. A maximum of 100 entries is possible
	216301234567890F	any comment	Equal sign and comment are optional, not required

The IMSI-MSISDN Database Loader module("Sga-IMSIMSISDNDB_Loader")

Table of Contents