 |
 |
Enterprise Transaction Logging Administrator's Guide
|
In-topic Links: Transaction Logging System Admin Responsibilities Maintain Companies (CC95) Purge Transaction Log (CC44) Transaction Log Reader (CC45) |
This guide provides sufficiently trained Enterprise System Administrators with information and help text on Enterprise's Transaction Logging & Replication System. It is not a substitute for consultation and training on the use of Enterprise's Transaction Logging & Replication system. You must consult with PointForce Client Services to configure, activate, troubleshoot or discontinue the use of Enterprise’s Replication systems.
This guide was last updated on 2002-11-20 and is applicable to PointForce Enterprise (WinSol) v3.1, v4.0, v5.x, v6.0.
Enterprise Transaction Logging
- Overview
The Enterprise Transaction Logging system monitors specific database tables for changes and store each change an insert, update, delete or command transaction in a sequential log file. The Transaction Log Reader (CC45) can be used to review detailed information about each transaction including:
Transaction Log Reader (CC45)
Transaction Message- Date & Time
- System Clock Date & Time
- Enterprise System Run Date
- Database table altered
- Company
- Name
- Location of database file
- Database record affected
- Transaction Type:
- Insert: A new record was inserted into the database table.
- Update: An existing record was altered.
- Delete: An existing record was removed from the database table.
- Command: A database table was redefined/etc.
- Data Record Changes:
- Insert: All data of the new record added to the database table.
- Update: Old and New values of fields changed on an existing record.
- Delete: Old values of record deleted from the table.
- Operator Identification Attributes:
- Enterprise Operator Name
- Network username of the Enterprise Operator's Workstation
- Network name of the Enterprise Operator's Workstation
- Network Name of the Server
- Server Username (*nix-based systems)
- Machine ID (e-Commerce-based Transaction Logging)
- Session ID (e-Commerce-based Transaction Logging)
- Program Identification Attributes:
- Enterprise Selector Code (e.g. IM13)
- Program Name
- Program Line Number
When Transaction Logging is enabled, the system creates a Replication Snapshots database – a duplicate copy of the Enterprise database – which is used by the Enterprise Replication option module to activate Replication Subscriber systems such as a Enterprise e-Commerce Replicated Web Site. The company-specific Replication Snapshot databases can be located on any network file-server accessible to the Enterprise Server. The size of the transaction log is control by the Purge Transaction Log (CC44) program which updates the Replication Snapshot databases, archives the transactions into date-based database files that can be moved offline or deleted and finally purges the transactions. The Transaction Log Reader (CC45) can be used to review the archived transaction logs.
The Enterprise Transaction Logging system is included as a basic feature in Enterprise 3.1.0 and can be enabled on request by PointForce. This powerful feature is used by Enterprise installations using the Enterprise e-Sales and Enterprise Replication option modules and has been designed to support custom client database projects. PointForce Client Services, Development and Support are also able to take advantage of Enterprise's Transaction Logging features to monitor database changes and verify that package or custom programs are correctly updating the Enterprise database.
- Date & Time
- Enterprise Systems Administrator's Responsibilities
Enterprise Transaction Logging adds the following responsibilities to the Enterprise Systems Administrator role:- Enterprise Server System Clock Time: The system administrator is responsible for ensuring that the Enterprise server's hardware and/or operating system clock time is accurate.
- File System Capacity Planning: The system administrator is responsible for ensuring that sufficient space exists for the growth of live transaction log file (data/00rptl) and running the Purge Transaction Log (CC44) program on a regular basis to control the size of the live transaction log file.
- Transaction Log Archives: The Purge Transaction Log (CC44) program archives transactions from the live transaction log table into individual date-based tables in the Transaction Log Archive directory. The systems administrator is responsible for removing archive files from the archive directory. The files are named 00rptl.YYYYMMDD where YYYYMMDD is the system clock date of the transactions contained in the archive file. The most recently dated file in the directory should not be removed as it is possible that the live transaction log still contains transactions for that date. The files can be stored offline if an audit trail is required or deleted.
Enterprise Replication adds the following responsibilities to the Enterprise Systems Administrator role:
- Replication Subscribers - the Replication Subscribers must be online on a regular basis to keep the Subscribers up to date. Where multiple subscribers exist any inactive subscriber that is not updated on a regular basis will prevent
Purge Transaction Log (CC44) program from archiving transactions that have not been replicated to that subscriber. Consult with PointForce Client Services about inactive or unnecessary Subscribers.
- Enterprise Subscriber System Clock Time - the systems administrator is responsible for ensuring that each Enterprise Subscriber system clock time is accurate.
- Replication Publishing - the Replication Server (RP10) must be run on a regular basis to keep the Subscribers up to date. RP10's Publish every X minutes until Y on Z option can be used to configure the replication process to run continuously.
- File System Capacity Planning - the Purge Transaction Log
(CC44) program updates Replication Snapshots with each transaction from the live transaction log prior to archiving. The Replication Snapshot tables will grow in parallel to the live database tables. The system administrator is responsible for ensuring that sufficient space exists for the growth of Replication Snapshots. The Replication Snapshots directory (CC95/Options/Transaction Logging) is company-specific and supports UNC paths (from the server's context) to support file system balancing across multiple devices and servers.
- Replication Subscriber Transaction Log - each time the Purge Transaction Log (CC44) program is started it writes a Transaction Log command that the Replication Server (RP10) will transmit to all Subscribers instructing them to archive and purge their local live Transaction Log. The systems administrator is responsible for connecting to each subscriber system on a regular basis to remove the archive files from the Subscriber's archive directory (client\rpsnapshots). The files are named 00rptl.YYYYMMDD where YYYYMMDD is the system clock date of the transactions contained in the archive file. The most recently dated file in the directory should not be removed as it is possible that the live transaction log still contains transactions for that date. The files can be stored offline if an audit trail is required or deleted.
- Recode/Merge Process - Enterprise's Recode/Merge programs alter the unique record identifiers of Customers, Products, Vendors, Suppliers and General Ledger codes. Enterprise Transaction logging will capture the database changes that the recode/merge process generates. Enterprise Replication will transmit the database changes to the Subscribers – but it has no control over the data on a Subscriber system between the time Publisher's database is altered and the Subscriber is updated with those changes. The Enterprise Systems Administrator is responsible for scheduling service periods during which all Subscriber systems will be in "Service Mode" (e.g., for e-Commerce Subscribers, the Site Service Manager (EC20) should be enabled.) before the recode/merge process is started and until the Replication Server (RP10) has replicated all recode/merge database changes to the Subscriber.
- Enterprise Server System Clock Time: The system administrator is responsible for ensuring that the Enterprise server's hardware and/or operating system clock time is accurate.
- Help Text
This section provides information from the online help for programs used in the transaction logging process. These programs include:Here are the excerpts from the online help:
Maintain Companies (CC95)
The Maintain Companies (CC95) window includes the following functionality:- Transaction Logging
The Transaction Logging window is found under the Options menu in CC95. The Enterprise Transaction Logging system is designed to capture changes in specific Enterprise database tables and store the changes as transactions in a common log table. The transaction log is processed by various applications such as the Enterprise e-Sales Replication system, and the Transaction Logging dialog provides controls to configure, plan, request start-up, and terminate Enterprise Transaction Logging.
The following list defines the fields/button(s) on the Transaction Logging window:
- UNC Path to RP Snapshots Directory - the RP Snapshots Directory refers to the location where the Replication System will store controlled copies of the Enterprise database tables that have been enabled for Replication Transaction Logging. Snapshots, along with the Transaction Log, are used during the activation of a Subscription to synchronize the databases.
The UNC Path to the RP Snapshots Directory must reference a directory that is accessible from the context of the Enterprise server hosting the installation. For Windows-based installations this can be any valid UNC path - even to another file server. For *nix-based installations it must be a local directory unless network file system software has been installed.
The Replication Snapshots directory is company-specific; all of the snapshots required for a company will be created and updated in that one location. Sufficient disk space must be provided in that location for the creation of the snapshots when Transaction Logging is enabled and for the growth of Snapshots which will parallel the growth of the live database as the Transaction Log is purged.
The Snapshots directory must be accessible to Enterprise for three processes:
- Enable Transaction Logging: When the Generate Files command of the Maintain Companies (CC95) program is used to enable Transaction Logging the Snapshots are created in the RPSnapshots directory.
- Activate Subscription: When the Activate command of the Maintain Replication Subscribers (RP01) program is used to synchronize databases between the Publisher and Subscriber, the Snapshots related to the Subscription are transmitted to the Subscriber.
- Purge Transaction Log: Before the Purge Transaction Log (CC44) program removes a transaction it is applied to Snapshot.
The Snapshots are not required to be online while the Replication Server (RPl0) is running.
- Enable Transaction Logging: When the Generate Files command of the Maintain Companies (CC95) program is used to enable Transaction Logging the Snapshots are created in the RPSnapshots directory.
- UNC Path to RP Archives Directory - the RP Archives Directory refers to the location where the Replication System will export Transaction Log records that are no longer required in the online Transaction Log. The Purge Transaction Log (CC44) program creates archive tables segmented by the transaction date. Once all of the transactions for a date have been purged the archive file can be moved from RP Archives Directory to offline storage or deleted.
The UNC Path to the RP Archives Directory must reference a directory that is accessible from the context of the Enterprise server hosting the installation. For Windows-based installations this can be any valid UNC path - even to another file server. For *nix-based installations it must be a local directory unless network file system software has been installed.
The Replication Archives directory is not company-specific; transactions are logged to and purged to a common database in the order the transactions occurred. Sufficient disk space must be provided in that location for the creation of the archive tables. A qualified operating system administrator will be required to periodically run the Purge Transaction Log program and remove the archived tables from the system to maintain free space.
The RP Archives directory must be accessible to Enterprise for two processes:
- Enable Transaction Logging: When the Generate Files command of the Maintain Companies (CC95) program is used to enable Transaction Logging following a Termination of Transaction Logging any transactions for the company in the online transaction log will first be archived then purged to tables with a suffix of .pur.
- Purge Transaction Log: The Purge Transaction Log (CC44) program purges a transaction from the online log by applying it to the snapshot, archiving it to a transaction date-based archive table and finally removing it from the online log.
The Archives are not required to be online while the Replication Server (RPl0) is running.
- Enable Transaction Logging: When the Generate Files command of the Maintain Companies (CC95) program is used to enable Transaction Logging following a Termination of Transaction Logging any transactions for the company in the online transaction log will first be archived then purged to tables with a suffix of .pur.
- Status - the Status display can be used to determine if Transaction Logging has been enabled for the selected company. The states one defined as:
- Directories Not Configured: The Snapshots and/or Archives directory has not been set.
- Not Requested: Transaction Logging has not been enabled for the selected company and has not been enabled prior to this.
- Start-up Requested: The start-up process for Transaction Logging has been requested and will be attempted the next time the Generate Files command is used while Enterprise is running in Single User Mode.
- Running: Transaction Logging is running for the selected company.
- Terminated: Transaction Logging was previously enabled for the selected company but has stopped.
- Terminated - Start-up Requested: Transaction Logging was previously enabled for the selected company, was stopped, start-up has since then been re-requested and will be attempted the next time the Generate Files command is used while Enterprise is running in Single User Mode.
- Directories Not Configured: The Snapshots and/or Archives directory has not been set.
- Request Start-Up - the Request Start-Up command will configure the currently selected company to start-up transaction logging the next time the Generate Files command is issued while Enterprise is running in Single-User Mode. After the start-up command is issued the Status will display Start-Up Requested until the Generate Files command enables transaction logging and sets the status to Running or the Terminate Transaction Logging command cancels the request.
- Terminate Transaction Logging - the Terminate Transaction Logging command serves two purposes: to cancel a start-up request or to stop transaction logging from running for the currently selected company.
When the command is issued and confirmed against a company already running transaction logging the company configuration will be updated to reflect the terminated status. All Enterprise processes for that company started after transaction logging was terminated will no longer be logged. Any Enterprise processes started before transaction logging was terminated will continue to be logged until those processes end or reload the company configuration.
The Terminate Transaction Logging command will break all Replication Subscriptions for the selected company.
When Transaction Logging is Terminated Enterprise does not update the companies database tables to remove the embedded transaction logging programs nor is the transaction log purged or the Replication Snapshots deleted. If necessary, to remove the embedded transaction logging programs use the Generate Files command while Enterprise is in Single-User Mode. The transaction log can be purged using the Purge Transaction Log (CC44) program. The Replication Snapshots prefixed by the company code can be removed from the snapshots directory manually by a qualified operating system administrator.
After transaction logging has been terminated, if another start-up is requested the Generate Files command will remove the old snapshots and purge the online transaction log to the archive with a suffix of .ter.
- Disk Estimate - the Disk Estimate command can be useful in determining how much disk space the Replication Snapshots for the currently selected company will consume initially. Sufficient disk space must be provided for the creation of the snapshots when Transaction Logging is enabled and for the growth of Snapshots which will parallel the growth of the live database as the Transaction Log is purged.
Transaction Logging must be enabled for the common company (00) before the Transaction Logging dialog can be assessed for a specific company.
The Disk Estimate command is not applicable for the common company (00).
- Close - the Close command will close the Transaction Logging dialog.
- UNC Path to RP Snapshots Directory - the RP Snapshots Directory refers to the location where the Replication System will store controlled copies of the Enterprise database tables that have been enabled for Replication Transaction Logging. Snapshots, along with the Transaction Log, are used during the activation of a Subscription to synchronize the databases.
- System ID
The System ID window is found under the Options menu in CC95. The System ID dialog provides access to controls for reviewing and editing local Replication system settings such as the Distributed System Number, Replication Username and Password.
The following list defines the fields/button(s) on the Transaction Logging window:
- Distributed System Number
- the Distributed System Number, or DSN, is a unique value assigned to each member system in a Enterprise Replication schema. Beginning with Enterprise 2.1.0, the host installation will acquire an initial DSN value of 0001. The host system, also known as the Replication Publisher, can retain this initial value if it will never need to replicate data with another independent Enterprise installation (e.g., a Purchasing Group's Enterprise installation replicating data with a Purchasing Group Member's Enterprise installation). Otherwise, the host's DSN must be assigned a unique valve within the group of Enterprise installations before any data is created.
Each Replication Subscriber is assigned a unique DSN when it is created using the Maintain Replication Subscribers (RP01) program. The Create Subscriber Install command will pass the DSN value into the new installation. If the Replication schema includes multiple Enterprise installations each one must have a unique DSN.
- In the Replication Publisher section:
- Username & Password - the Replication Username and Password control access to the local installation's Replication systems, including the e-Commerce-based XML Document gateway (post. xml).
Each Replication Subscriber is assigned a Username and Password when it is created using the Maintain Replication Subscribers (RP01) program. The Create Subscriber Install command will pass the Username and Password into the new installation.
- Username & Password - the Replication Username and Password control access to the local installation's Replication systems, including the e-Commerce-based XML Document gateway (post. xml).
- In the Replication Status section:
- Excluded Companies - Enterprise Transaction Logging can be configured to track changes in the Enterprise Database on a company by company basis. The list of Excluded Companies displays a chain of the company numbers that have not been enabled for transaction logging.
When a company enabled transaction logging is terminated the list of Excluded Companies is updated with that company number. This will prevent new processes from transaction logging even though the database triggers are still embedded in that company's tables until they can removed by the Generate Files command while Enterprise is running in single-user mode.
- Last Transaction ID - the Replication Status display provides a view of the local installation's position in the Replication Publisher's transaction log as well the Replication Publisher's position in the local transaction log. These values are initially set by the Maintain Replication Subscribers (RP01) program's Create Subscriber Install command and are thereafter updated by the Replication Server (RP10) after each transaction is confirmed.
- Excluded Companies - Enterprise Transaction Logging can be configured to track changes in the Enterprise Database on a company by company basis. The list of Excluded Companies displays a chain of the company numbers that have not been enabled for transaction logging.
- Distributed System Number
- the Distributed System Number, or DSN, is a unique value assigned to each member system in a Enterprise Replication schema. Beginning with Enterprise 2.1.0, the host installation will acquire an initial DSN value of 0001. The host system, also known as the Replication Publisher, can retain this initial value if it will never need to replicate data with another independent Enterprise installation (e.g., a Purchasing Group's Enterprise installation replicating data with a Purchasing Group Member's Enterprise installation). Otherwise, the host's DSN must be assigned a unique valve within the group of Enterprise installations before any data is created.
- Generate Files Button
The Generate Files command on the CC95 window serves several Transaction Logging functions:
-
Enabling Transaction Logging: Once transaction logging has been requested using the Options/Transaction Logging dialog the Generate Files command can enable transaction logging if CC95 is running in Enterprise Single-User Mode (see below). While Transaction Logging is being enabled a Replication Snapshot of each eligible database table is created in the snapshots directory and the transaction logging database triggers are embedded into the table. At the end of the Generate Files process if all eligible tables have been enabled successfully the Transaction Logging status of the company will be updated as Running and the company will be removed from the list of Excluded Companies. This process will require only a few minutes to complete for a new company that does not contain any data yet or for an existing company may require several hours.
- Enabling Transaction Logging on new custom database tables: Following the installation.
- Disabling Transaction Logging.
-
Enabling Transaction Logging: Once transaction logging has been requested using the Options/Transaction Logging dialog the Generate Files command can enable transaction logging if CC95 is running in Enterprise Single-User Mode (see below). While Transaction Logging is being enabled a Replication Snapshot of each eligible database table is created in the snapshots directory and the transaction logging database triggers are embedded into the table. At the end of the Generate Files process if all eligible tables have been enabled successfully the Transaction Logging status of the company will be updated as Running and the company will be removed from the list of Excluded Companies. This process will require only a few minutes to complete for a new company that does not contain any data yet or for an existing company may require several hours.
- Enterprise Single-User Mode
To achieve single-user mode in Enterprise only a single user process can be running.
- Log off all users.
- For Windows-based installations, shut down the NThost Server application. For *nix-based installations, locate and shutdown all pvx processes.
- Log in to Enterprise and run the Maintain Companies (CC95) program.
- Switch back to the Enterprise Program Menu used to run CC95 and close it.
- Only CC95 should be left running - if it is the only Enterprise process running then Enterprise is running CC95 in single-user mode and the Generate Files command will be able to enable or disable transaction logging. If any other Enterprise processes exist CC95 will report that Transaction Logging has been requested but that CC95 was not running in single-user mode.
Purge Transaction Log (CC44)
The Purge Transaction Log (CC44) program performs three functions:- Updates the Replication Snapshots by applying the Transaction Log to them.
- Copies transactions from the log to date-based files.
- Removes transactions from the log file.
Purge Transaction Log (CC44)The following list defines the fields that are available in CC44 for user input/interaction:
- Extra Log Days - the Extra Log Days defaults to seven days to allow for a small amount of history to be retained in the live Transaction Log. The Transaction Log Reader
(CC45) opens the live file by default but can also access information stored in archive after they have been purged by this program. Lowering the Extra Log Days will allow this program to purge more information from the live file but is only necessary if the installation generates an extremely large number of transactions per day; say greater than 500,000. You can monitor the number transactions in your live Transaction Log by accessing this program each day at approximately the same time for a period of a week and ONLY recording the value of the Records field. The incremental difference in Records each day is the number of records that were created within the past 24 hours; averaging this value over a number of days will provide an approximate number of transactions per day. If this average is approaching 500,000 you should consider reducing the Extra Log Days in the Job Stream run of CC44.
- Start - starts the purge process. Purging can be performed during normal business hours without adding excessive drag on a sufficiently powered disk sub-system's performance however it is recommended that CC44 be added as the last task in a daily Job Stream set to ensure that purging is performed on a regular basis. Remember: CC44 copies records from the live Transaction Log to daily Archive Logs prior to purging them from the live file; you are responsible for archiving or deleting the Archive files from the disk to maintain free space.
- Stop - the Transaction Log Purge process can be stopped at any time. When stopped it will complete the current record and stop.
Transaction Log Reader (CC45)
The Transaction Log Reader provides a view of database changes in the live Transaction Log (default file) or can be used to open and view a daily transaction log Archive.
Transaction Log Reader (CC45)The following list defines the fields/button(s) that are available in CC45 for user input/interaction:
- File Object, Date, Time - the File Object, Date & Time fields can be used limit the view of transactions to a specific database table, date and time.
- Edit Menu - the edit menu provides commands to copy the currently selected transaction log record identifier.
- View Message Button - the View Message command provides a human readable view of the database change. It decodes the transaction's XML message to display the Field, Field Names, Old Values and New Values. The Action & File fields at the bottom of the display identify what type of transaction (Insert, Update or Delete) was performed on what type of record.
Transaction Message - Transaction Logging
