/ New Global Telecom
ftp access of ngt website setup.doc
Craig Heckman
1998-12-15

Title:CDR Extraction, Transfer, Parsing and Loading

Description:System descriptions of getting TDM switch call records from Ericsson AXE or Harris to NGT custom billing system

Author:Craig Heckman

Change Tracking:

Date / Modifier / Description of Change
12-15-98 / Craig Heckman / Original Draft
8/30/2000 / Bill Coulam / Revision 1 (everything’s different now)

Process/Problem:

To highlight the path that a call record takes from a TDM switch to our billing system.

Steps/Solution:

A call record is created for all completed calls and a percentage of incomplete calls in the TDM switch. The call record, called a CDR is created at the end of a call. If a call is going to be passed to another of our switches to be routed to its final destination, this is known as an IMT call and will be composed of multiple CDR records from 2 or more switches.

Ericsson Switches

The Ericsson switches writes these records to two files on the internal hard drive, called file 0 and file 1. It writes 2040 characters sequentially to each file (13 calls based on current CDR length). This is called a block. As a block is filled in file 0 the switch then writes 2040 characters to file 1, etc. The switch writes 200 blocks to what is called a subfile within each file. These subfiles are known as the TTFILEs in the switch. The TTFILEs are written sequentially with call records and numbered from 0001 through 9999. New Global adds a -0 or -1 to each file name to differentiate between the 0 and 1 file on the internal switch hard drive.

As the TTFILEs are filled with 200 blocks, 2600 CDRs they are closed in pairs, a -0 and -1 file. The Perl script tr_eric.pl running on the FTAM1 and FTAM2 computers in the switch site is responsible for downloading these TTFILEs from the switch. This script checks for the existence of the -0 TTFILE on the switch hard drive. A negative response, i.e., file not found, means that the subfile is being written by the switch and the script will wait for three minutes and try again. When the file is found, the script executes a NCOPY command. This command is FTAM specific and will download the TTFILE from the switch to the local hard drive on the PC. After this transfer is complete, the script copies the -1 file as well and then increments itself by 1 and starts over. The script also performs a PKZIP on the pair of TTFILEs which creates a xxxx.zip file where xxxx is the TTFILE number.

When the TTFILEs have been zipped, the script copies them over the network to a remote box in the Golden computing center. Currently this is Gandalf on the F: drive, but will soon be changed to bombur in the loading area. A cron job runs on bombur every 10 minutes looking for the arrival of new TTFILE zip files. If found, it calls various perl and ksh scripts to unzip them, parse them (takes the data stream from the switch and inserts a carriage return at the end of each CDR), load them into a temp table in Oracle, and then call an Oracle PL/SQL procedure called drive.load_cdr which ensures that each record is massaged into a common CDR format, loaded into the CDR table, and then rated.

Harris Switches (currently only in HK)

This system consists of 3 main parts: Switch Extraction, Transportation, and Loading.

Switch Extraction

Switch extraction consists of a Java program that communicates with the server. Java makes socket level communication easy and robust, and errors are handled quite easily. The program sends a UDP packet to the switch to let it know that the program is ready to receive. The switch responds with a message that contains the TCP port number the program can connect to. The program connects to that port and receives the first TCP packet message. The message has a unique identifier to make sure messages are not repeated. The program decides whether this message is a new message. If it is, the program writes the message to a file. It responds to the switch over the TCP socket to acknowledge the receipt. This acknowledgment contains the unique number that is contained in the CDR. If there is no CDR for a certain period of time (like 3 seconds) the program will send a message to the switch to let it know that it is still alive. If the switch doesn’t get any communication with the program for several seconds, it will kill the TCP socket and queue up messages until it connects again. This continues until a set number of packets are received (a packet may have several CDR in it) then closes the file and kicks off the process for transportation.

Transportation

The transportation part of the process is written in Perl. Perl makes running system processes easier than Java, allows for simple error handling, and has an easy high-level interface for ftp processing.

The perl process begins by individually processing each file that ends in “.TT”. If it is not a zero-length file it is zipped up in a .zip file that uses the same name as the .tt file. Then the .zip file is send through ftp to the unix server, and put in directly in the directory that will process it. Then copies of the .zip and .tt files are saved in different directories. Then files older than a specified number of days in each of those directories are deleted to save space on the remote PC.

Loading

Once the files are on the UNIX machine, we load them in a process that is almost identical to the Ericsson loader. Each file is unzipped, concatenated into one big file, and loaded into the database. The zip files are copied to the zipped_archive directory, the unzipped fiels are removed, and the output for the process is saved in the log directory in the file that is marked for that day. Once in the database, calls are rated and stored.

Troubleshooting

There are several points at which you can quickly check the CDR process to monitor its health. I usually begin at the end of the process and work my way back to the beginning.

  1. Make sure the server is up. Telnet to Bombur-ora. Su to root. Run hs. It should give you a table with four rows. One will have bombur-ora as the group and bombur as the system. The end of this line should say ONLINE. The end of the Thorin-ora and thorin line should say ONLINE. If either of these say PARTIAL, FAULTED, or OFFLINE there is a problem with the Veritas instance.
  2. Make sure there is enough disk space. From the command line do “df –k” This will show all the disks and the percent they are full.
  3. Make sure the loader process is working. Look in the /b10/oracle/appl/loader/harris_hk directory. If there are more than 1 data####.txt file, the database is behind. It is a database problem. If there are several .zip files, the scripts are not running. It is a script problem.
  4. For more information, you can look in the log directory for the file for that day. If there are no .zip or data####.txt files, look in the log file, go to the end, and look backwards to make sure the zip files are getting to the Unix server. If there is no mention of files with long numerical filenames getting unzipped, there are no files getting to the server. It could be a link problem if the communication has failed from HK to Golden. To check this try and ping or pcanywhere to the Hong Kong CDR machines (CDR1=172.18.2.15 CDR2=172.18.2.16). If you get no response from one but you do get a response from the other it is a PC hardware problem. If you cannot connect to either it is a communications problem.
  5. If you can connect to one of the PCs you should see a DOS window with writing on it that looks like this:

cdrHeader 0=2 1=0 2=87 3=0 4=117 5=3

tmpSeqNumber=885

lastSeqNumber=884

msgLen=87

Not Skipping. msgLength = 87

cdrHeader 0=2 1=0 2=87 3=0 4=118 5=3

tmpSeqNumber=886

lastSeqNumber=885

msgLen=87

Not Skipping. msgLength = 87

This indicates that the CDR extraction process is running. If you do not see it working on one machine check the other to make sure it is not running there. If it is not running on either machine, choose one to start it on, open a MS-DOS window, cd to d:\hkcdr and type java CDRRetrieve . This will start the process again. In a few seconds it should begin scrolling text as it looks above, but the tmpSeqNumber and lastSeqNumber will start at 0 and count up.

  1. If you find the process running, but it is stuck where it says something like “Starting zipnship.pl” that means it has gotten stuck on the Perl file that zips the data files and ftps them to the server. Kill the process in the MS-DOS window by typing CTRL-C into it. If it asks if you want to stop the batch process, enter y. You can look at the output of zipnship.pl in the zipnship.txt file in the same directory for clues. Make sure the D drive and the E drive have not filled up.

If the process is stuck, or it seems to spit out error messages and does not seem to work, it is either a communications problem with the switch or a problem in the switch. If you can ping the switch’s IP address it is probably not a communications problem. Then it is a switch problem.

Locations:

FTAM1 – use pcAnywhere to operate anything on this box. The username/password or JON/JON. Then double-click the run_loader.bat shortcut to start the tr_eric.pl script. If there are errors downloading TTFILEs from the ericsson switch, you may need to manually increment the number stored in the currnum.txt file.

ConfidentialPage 110/26/2018