EEE Monitoring

From EnablerWiki

Created: 28 September 2007. Last Reviewed: 27 January 2009

EEE Monitoring2

EEE Monitoring

Overview

The intention of the EEE monitoring is to ensure that all transactions occurring in stores are correctly processed in EEE with no errors, and also to provide an alert of problems that may have occurred. EEE monitoring is an ongoing function that should be performed each day to ensure any problems that occur are not compounded by a lack of immediate action.

If you are performing your own EEE Monitoring, it is essential that these procedures be followed to ensure your EEE environment contains accurate data. Failure to perform the monitoring procedures could leave you in a situation where your EEE data is not correct or a hardware/software failure occurs and you are unable to recover your data!

All the examples shown in this document relate to SQL Server 2005.

The alerts and weekly reports can be automatically emailed to one or more email addresses, and it is recommended that a minimum of 2 email addresses are alerted to provide coverage in the event of staff being absent.

Alert email addresses need to be configured in both EEE and SQL Server.

EEE

The email addresses can be configured from within EEE from the Utilities->Company Preferences menu as shown below.

SQL Server

For each EEE database in your SQL Server, you will have a EEE operator, as shown on the screen below

To change the email address for the EEE Operators, highlight the operator (in the example above it is EEE_Operator), and then click on Properties. You will see a screen similar to the one below.

You can change email addresses by adjusting the E-Mail name field as shown above. Email addresses should be separated by a semi colon (;).

The main components providing the EEE Monitoring function are:-

Weekly reports. These reports provide various statistics on the EEE environment.
EEE Error logs. Whenever more than 10 errors are logged in the EEE errorlog, and alert email will be generated which will include a file detailing all the errors that have occurred. These should be attended to immediately.
EEE Data Loader. Various circumstances can cause the EEE dataloader to suspend processing, and when this occurs an email alert is generated.
Disk space Alerts. EEE can be configured to generate email alerts when the free space on nominated disk drives falls below a specified level.
Unacknowledged Data threshold. – When data has been exported to stores and EEE hasn’t received an acknowledgement for that data within 7 days, an alert email is generated.

Data Loader errors

Probably the most important area of EEE Monitoring is checking the EEE Error log for any records that have been rejected by EEE. Failure to monitor the EEE Error Log and fix any rejected records will lead to inaccurate data in EEE, resulting in incorrect sales information, etc.

It is most important that the EEE Error Log is monitored on a regular basis.

The EEE Web application is used to access the EEE Error Logs. They can be found from Utilities->Data Interchange Management->Data Loader Error Log and also Utilities->Data Interchange Management->Data Loader Activity Log, as shown below.

Data Loader Activity Log

The Data Loader Activity Log shows the details of every file processed by Data Loader. The defaults for this screen are shown below. You can also filter the activity log so that only files containing errors are shown.

A record that has a Y in the “Contains errors” column allows you to drill through to the error log records associated with the activity record.

Data Loader Error Log

There are many types of data loader errors that can appear in the error log. It is not possible to discuss resolutions for all error types, so the most common errors have been addressed below.

It is extremely important that errorlog records are investigated ASAP. If the problem can’t be resolved, it should be referred to Magenta Retail Support (support@magentaretail.com.au) for assistance in resolving the problem.

Timeout Expired

The example below shows a number of timeout errors. These can occur when the SQL Server is very busy with other tasks such as running queries etc, and the timeout period for a transaction to occur is exceeded, so the transaction is aborted and the details of the record being inserted is added to the EEE errorlog table. To fix these types of errors you simple need to reprocess the records.

Click on the 1^st record to be re-processed. The record ID number will be populated in the From ID and To Id fields towards the bottom of the screen. In the example below, the ID number is 26539.
Replace the number in the To ID box with the record ID number of the last record to be re-processed. In the example below, type 26553
Click on the REPROCESS button in the bottom right corner of the screen. A file will be created containing all these records in the Reprocess sub-folder of the data loader import folder (something like d:\eeedata\import\reprocess.
The records written to the reprocess file will then be flagged in the errorlog table as re-processed

After the reprocess button is clicked, and the errorlog records updated, the above records will be removed from the screen as shown below.

Transaction Deadlocked

Deadlocked transactions occur when an attempt is made to update the same record by 2 or more SQL processes as the same time. One of the updates will be selected as the deadlocked record, and the record will be written to the errorlog.

Deadlocked transactions should be reprocessed as with timed out records above.

Foreign Key Constraint errors

Foreign Key constraint errors occur when a record contains key values such as product code, customer code, branch code, etc, that do not exist in EEE. Under normal circumstances, these shouldn’t occur so further investigation is required. Some instances that may cause these errors include:-

Product code created in EEE and then deleted after it had been exported to stores. The store performs a transaction with that product code before it received the deletion.
Customer code is missing. Customer was created in Enabler, but a communications failure prevented the file containing the new customer being correctly sent to Head Office. The customer then makes a purchase in the store and that transaction is sent to Head Office causing a Foreign Key error as the customer is not in EEE.

The screen below is an example of a number of Foreign Key Constraint error records.

If you click on an error record so it is highlighted, and then click on the DETAIL button, you are given further information on the error as shown below.

In the example above, you can see that the record hasn’t been processed because of a conflict in the productcode column of the Product table. This means that the product code in this record (93281355156943) is not in the product table.

This may have occurred because the product code was created in store instead of in EEE or the code may have been created in EEE and then deleted, and the deletion hasn’t been sent to the store as yet.

This record could be processed by replacing the invalid product code with the correct product code.

ESC from the above screen, and then click on EDIT.
You are then shown the original record, with the highlighted area of the screen to make any changes as shown below.
Replace the product code with the correct code and then click Resubmit.
The record is then re-processed and removed from the error log screen.

Primary Key Constraint errors

Primary Key constraint errors occur because the data that is being inserted violates a primary key setting for the table. Primary key violations stop duplicate data being processed by EEE, and also prevent duplicate items such as duplicate gift voucher numbers being entered.

In the example below, a gift voucher transaction has generated a Primary Key violation

Clicking on the Detail button gives the information about the error as shown below. This indicates that EEE cannot insert a duplicate key (voucher number) in the Voucher table

The above situation can occur if Enabler is configured to issue manual gift voucher numbers and the same number is issued twice. A common scenario for this is the store redeems part of a gift voucher, and issues a gift voucher as change, but they reissue the same gift voucher number.

The Primary Key violation example below relates to duplicate transactions being rejected by EEE. If you look at the Error Record column on the right hand side, you will see the transactions occurred on 20/02/2007 but the Date column indicates that these errors were generated on 5/03/2007. These types of primary key errors generally indicate that something unusual such as database corruption, has occurred in Enabler on the store computer.

Primary Keys prevent the same record being processed more than once. As an example, on the Saleline table, primary keys exist on the fields saledate, saleline, reference2, and kititemnumber. As Enabler can’t generate duplicate combinations of these fields in one transaction, if a combination of these fields already exists in EEE, this will be a duplicate record and EEE will not process the duplicate record and will insert the record in the errorlog table.

With the above error records, you should first attempt to send acknowledgements back to the store which will hopefully stop these records being resent by Enabler. If the same records appear in the errorlog a few days later, you will need to contact Magenta Retail Support (support@magentaretail.com.au) for further assistance.

To acknowledge these records, do the following.

Click on the 1^st record to be acknowledged. The record ID number will be populated in the From ID and To Id fields towards the bottom of the screen.
Replace the number in the To ID box with the record ID number of the last record to be acknowledged. Use the arrows to move between screens if there is more than one screen of errors.
Click on the ACKNOWLEDGE button. Acknowledgement files are then created for sending back to the stores. Acknowledgement records when processed by Enabler, indicate that the record has been processed by EEE, so doesn’t need to be sent again by Enabler. After these records have been acknowledged, you need to flag them as ignored so they don’t appear in the errorlog.
Click on the 1^st record to be ignored. The record ID number will be populated in the From ID and To Id fields towards the bottom of the screen.
Replace the number in the To ID box with the record ID number of the last record to be ignored. Use the arrows to move between screens if there is more than one screen of errors.
Click on the IGNORE button. The records will all be flagged as ignored and not shown on the errorlog screen.

Weekly emails

Each week, a scheduled task is run that produces a number of reports. These reports are emailed to the configured email addresses. The subject of these emails is “SQL Server Report”, and they will usually contain a zip file called WeeklyReportFiles.zip. Contained in this zip file will be:-

dbcc_out.txt
dbmaint.txt
errorlog.txt
mag_erlg.dat
sqlbackup.txt

The text of the email will look like:-

The email should contain the

drive sizes,

database sizes and

the latest errorlog.

companyname

--------------------------------------------------

Your Company - New Zealand

(1 rows affected)

The contents of each file is explained below.

dbcc_out.txt

This document is usually very large and contains information about performing an indexdefrag on all table indexes in EEE. This report is mainly for informational purposes only.