Procedure To Process Windows Database Rejects

(created: 08/21/09, last revised: 05/24/10)

Purpose:

To process windows level database loader rejects, regardless of whether or not the rejected data will be replayed.

Procedure:

1) Identify and document scope of problem(s)

Database Loader Monitor

Ø Note the following:

o The database loader(s) involved.

o The server(s) that the loader(s) runs on.

o The number of rejects associated with the loader(s).

Confirm control room has notified floor staff of rejects and potential for data to be missing in client queries.

2) Reformat reject files and

Clear the reject counts from the database loader monitor(s) involved.

Tell the control room you are about to stop/restart the affected database loaders.

2a) Stop database loaders involved

NTM Control Utility- Service Control – Process Controller

Ø Filter process list by desired application group (Note: all database loader groups are prefixed by DL)

Ø Select database loaders involved (in running processes column)

Ø Right click and select stop process.

Ø Confirm processes appear as stopped in the stopped processes column

2b) Reformat reject files (can’t be done until database loaders are stopped in step 2a)

NTM Control Utility- Utilities – Database Reject Reformat

Ø Select files involved (Note: it takes awhile for tool to find all reject files with size)

Ø Right click and select Create Reload File.

Ø Confirm list of files is emptied (Note: it takes awhile for tool to complete this step)

NOTE: This function performs the following actions:

1) Creates *reload.log files with reformatted reject messages

2) Renames *rejects.log files to *rej.log_save files

2c) Confirm database loaders involved did not switch to manual mode and prepare to process any additional rejects if it did.

(Note: If process switched to manual mode, the process remained up but stopped processing messages. There may be more rejects to process, but we’ll need to stop/restart database loader to do so. This step will help minimize the number of times you might need to do this.)

OC – Persistence Perspective

Ø Query entire day for “failure count” in the body column.

Ø If no messages were seen, skip to step 2d (Start database loaders)

Ø If messages were seen (relevant to the event being processed), call IT Dev to help with the following steps:

Windows Explorer - \\xmlconfig\data\xmldatabase\

Ø Find appropriate xml config file containing configuration information for the database loader

Ø Make copy of file before making any changes. (This file needs to be put back after procedure is complete)

Ø In xml file, find reference to database loader and change MaxTransactionFailure to higher number. Ie.100, 500, etc.

Ø Save file change

2d) Start database loaders involved

NTM Control Utility- Service Control – Process Controller

Ø Filter process list by desired application group (Note: all database loader groups are prefixed by DL)

Ø Select database loaders involved (in stopped processes column)

Ø Right click and select start process.

Ø Confirm processes appear as running in the running processes column

2e) Confirm no further rejects are seen.

Database Loader Monitor

Ø Confirm no further rejects were seen.

Ø IF REJECTS ARE SEEN, THE ENTIRE PROCEDURE MUST BE FOLLOWED FROM STEP 1.

WAIT UNTIL FIRST SERIES OF REJECTS ARE PROCESSED BEFORE RE-STARTING PROCEDURE AT STEP 1!!

3) Determine if files can be replayed or not.

Windows Explorer

Ø Map to the appropriate server and location of the reject files. (Note: All reject files should be in the folder: D:\chx\data.)

Ø Find the reject file associated with the loader. (Note: These have been renamed to *rej.log_save files)

Ø Using wordpad, edit file and confirm the reason for the rejects.

Ø If the reason DOES NOT involve invalid data, then skip to step 4 (replay data).

o In general, if the reason for the reject is related to an Oracle or Table deficiency, then the rejects don’t involve invalid data and can be replayed after the deficiency has been resolved.

o If the reason for the reject is for missing data, or values too large, etc, then there is likely a data issue and developers will need to get involved to try and assess how/if the data can be replayed.

Ø If the reason involves invalid data (ie. value too large, etc.), contact IT Dev to help determine proper course of action.

Ø If data needs to change, modify the reformatted files created earlier using wordpad. DO NOT modify the saved reject files.

o If IT Dev determines that data can’t be replayed, notify control room that users will not see data, ever, through clients.

Hopefully, this will never be the case.

4) Replay data and confirm integrity.

Reject Database Loader Monitor

Ø Confirm that none of the reload files have been processed yet. (No green bars in monitor)

Note: If files have been processed, work with others to make sure you don’t try and duplicate reload.

Tell the control room you are about to start the affected rejected database loaders.

4a) Start reject database loaders involved

NTM Control Utility- Service Control – Process Controller

Ø Filter process list by reject database loader group (Note: DLRJ)

Ø Select database loaders involved (in stopped processes column)

Ø Right click and select start process.

Ø Confirm processes appear as running in the running processes column

4b) Confirm no further rejects are seen.

Reject Database Loader Monitor

Ø Confirm no further rejects were seen.

Ø IF REJECTS ARE SEEN, THE ENTIRE PROCEDURE MUST BE FOLLOWED FROM STEP 1.

WAIT UNTIL FIRST SERIES OF REJECTS ARE PROCESSED BEFORE RE-STARTING PROCEDURE AT STEP 1!!

4c) Stop reject database loaders involved

NTM Control Utility- Service Control – Process Controller

Ø Filter process list by reject database loader group (Note: DLRJ)

Ø Select database loaders involved (in running processes column)

Ø Right click and select stop process.

Ø Confirm processes appear as stopped in the stopped processes column

5) Rename all processed files so they don’t impede any subsequent efforts to replay an potential further rejects.

(Note: If subsequent rejects occur for the same process in a given day, the automated functions will try and work with files of the same exact names utilized the first time. This step will help minimize the number of problems anyone might have trying subsequent replays.)

Windows Explorer

Ø Map to the appropriate server and location of the reject files. (Note: All reject files should be in the folder: D:\chx\data.)

Ø Find all processed files involved and rename the following:

o Rename *-rej.log_save; files to –rej.log_save_1 (if associated with first replay effort, _2 if second, _3 if third, etc)

o Rename *-reload.log; files to –reload.log_1 (if associated with first replay effort, _2 if second, _3 if third, etc)

o Rename *RELOAD*-rejects.log; to *.log_1 (if associated with first replay effort, _2 if second, _3 if third, etc)

o Rename *.pos; files to *.pos_1 (if associated with first replay effort, _2 if second, _3 if third, etc)

6) Replay any further rejects seen, not yet processed.

(Note: This procedure must be started from step 1 for any reject seen, regardless if this is the first or a subsequent effort to replay rejects.)

Ø If no further rejects need to be processed, skip to step 7. (Confirm any changes made to xml config files are backed out)

Ø If further rejects need to be modified, go back to step 1 and start procedure again.

7) Confirm any changes made to xml config files are backed out.

(Note: This might’ve occurred in step 2c. If changes were made, we need to revert them back to their original state)

Windows Explorer - \\xmlconfig\data\xmldatabase\

Ø Find appropriate xml config file.

Ø Rename file to *.yyyymmdd_temp

Ø Rename previously copied version to original name.

Ø Edit file in wordpad and confirm changes are backed out.

8) Follow up with control room.

Tell the control room you are done, and work with them and user community to confirm there are no problems. Ie. Everyone should be able to view their data in client queries now, etc.

Make sure the control room knows the reason for the rejects, the status of the replay efforts and any outstanding issues regarding the event, so that these may be logged in production reports for historical/future reference.