POL00397163
POL00397163
[e) [TITLE \* MERGEFORMAT ] e
FUJITSU [SUBJECT \* MERGEFORMAT ] &
Document Title: [TITLE \* MERGEFORMAT ]
Document Reference: [DOCPROPERTY “Reference Number" \* MERGEFORMAT ]
Document Type: [DOCPROPERTY “Document Type" \* MERGEFORMAT ] (LLD)
Release: [DOCPROPERTY Release \* MERGEFORMAT ]
Abstract: [COMMENTS \* MERGEFORMAT ]
Document Status: [DOCPROPERTY Status \* MERGEFORMAT ]
Author & Dept: [AUTHOR \* MERGEFORMAT ]
External Distribution: None
Approval Authorities:
e Role Si,
Gill Jackson Development
Note: See Royal Mail Group Account HNG-X Reviewers/Approvers Role Matrix (PGM/DCM/ION/0001) for
guidance.
‘©Copyright Fujitsu Services Ltd 2007 [SUBJECT \" MERGEFORMAT ] Ref: DEV/APP/LLD/0092
Version: V0.1
“ Date: 22-Jun-07
UNCONTROLLED IF PRINTED [KEYWORDS! ' MERGEFORMAT I Page No: 1 of 16
POL-BSFF-0223833
POL00397163
POL00397163
[e) [TITLE \* MERGEFORMAT ] 4
FUJITSU [SUBJECT \* MERGEFORMAT ] &
0 Document Control
0.1 Table of Contents
[ TOC \o "1-3" \h \z \t "POA Appendix Heading 1,1,POA Appendix Heading 2,2" ]
‘©Copyright Fujitsu Services Ltd 2007 [SUBJECT \" MERGEFORMAT ] Ref: DEV/APP/LLD/0092
Version: V0.1
“ Date: 22-Jun-07
UNCONTROLLED IF PRINTED [KEYWORDS! ' MERGEFORMAT I Page No: 2of 16
POL-BSFF-0223833_0001
FUJITSU
[TITLE \* MERGEFORMAT ]
[SUBJECT \* MERGEFORMAT ]
POL00397163
POL00397163
0.2 Document History
Version No, Date
‘Summary of Changes and Reason for Is:
Associated Change -
CP/PEAK/PPRR
Referen
0.1 20/06/2007
For review
0.3 Review Details
Review Comments by
Monday, 02 July 2007
Review Comments to
Mandatory Review
Role
[ HYPERLINK “mailto:peter jennings{
“mailto:rmgadocumentmanagementi_
Name
] &[ HYPERLINK
]
Solution Design/Infrastructure Design
Roger Barnes
System Test Harjinder Hothi
SSC Mik Peach
Optio
Role Name
Security Bill Membery
Business Continuity Tony Wicks
Service Support Peter Thompson
HNG-X Service Transition
Steve Godson
Data Centre Migration
Andy Tait & Brett Martin
SV&l Manager
Sheila Bamber
Tester Hamish Munro
RV Manager James Brett (POL)
VI Manager Peter Rickson
Development Host Team Manager
David Harrison
Development Host Team Leader
David Pooley
Development Host Team Member
Anona Stevens
Development Host Team Member
Vishnuvardhan Ramachandran
Development Host Team Member
Mia Brittain
Development Host Team Member
Duncan Brown
Development Host Team Member Steve Goddard
Development Host Team Member Tatter Bandna
Development Host Team Member Wing Pang
Reference Data Team David Wilcox
‘©Copyright Fujitsu Services Ltd 2007 [SUBJECT \" MERGEFORMAT I Ref: DEVIAPP/LLD/0092
Version: V0.1
UNCONTROLLED IF PRINTED [KEYWORDS! ' MERGEFORMAT] Page No: 30f16.
POL-BSFF-0223833_0002
POL00397163
POL00397163
[e) [TITLE \* MERGEFORMAT ] 4
FUJITSU [SUBJECT \* MERGEFORMAT ] &
Reference Data Team Kevin McKeown
Issued for Information — Please restrict this
distribution list to a minimum
Position/Role Name
(*) = Reviewers that returned comments
0.4 Associated Documents (Internal & External)
Reference Version Date Title Source
PGM/DCM/TEM/0001 I 2.0 16/04/2007 Fujitsu Services Post Office Account Dimensions
(D0 NOT REMOVE) HNG-X Document Template
DES/APP/HLD/0027 0.3 21/05/2007 TPS High Level Design Dimensions
DES/APP/HLD/0020 I 0.3 08/06/2007 aaah DATABASE High Level Dimensions
Unless a specific version is referred to above, reference should be made to the current approved
versions of the documents.
0.5 Abbreviations
Abbreviation Definition
BRDB Branch Database
HLD High Level Design
HNG-X Horizon Next Generation — Plan-X
PAN Primary Account Number
PCI Payment Card Industry
POL Post Office Limited
POL FS Post Office Limited Financial System
TPS Transaction Processing Service
XML eXtensible Mark-up Language
0.6 Glossary
Term Definition
Hashed PAN An obfuscation of a PAN based on a one-way hash algorithm. The first six and last
four digits of a PAN are not changed by this obfuscation. All hashed PAN values are
19 characters in length , use base-64 characters and have a non-numeric character in
‘©Copyright Fujitsu Services Ltd 2007 [SUBJECT \* MERGEFORMAT ] Ref: DEV/APP/LLD/0092
Version: V0.1
“ Date: 22-Jun-07
UNCONTROLLED IF PRINTED [KEYWORDS! ' MERGEFORMAT I Page No: 4 of 16
POL-BSFF-0223833_0003
POL00397163
POL00397163
[e) [TITLE \* MERGEFORMAT ] 4
FUJITSU [SUBJECT \* MERGEFORMAT ] &
Term Definition
the 7" character position.
Hydra Phase covering the dual running of Horizon and HNG-X
PCI Compliance The data held within the system conforms to the requirements of the
Payment Card Industry.
0.7 Changes Expected
ae
0.8 Accuracy
Fujitsu Services endeavours to ensure that the information contained in this document is correct but, whilst every
effort is made to ensure the accuracy of such information, it accepts no liability for any loss (however caused)
sustained as a result of any error or omission in the same.
0.9 Copyright
© Copyright Fujitsu Services Limited 2007. Alll rights reserved. No part of this document may be reproduced, stored
or transmitted in any form without the prior written permission of Fujitsu Services.
‘©Copyright Fujitsu Services Ltd 2007 [SUBJECT \* MERGEFORMAT ] Ref: DEV/APP/LLD/0092
Version: V0.1
“ Date: 22-Jun-07
UNCONTROLLED IF PRINTED [KEYWORDS! ' MERGEFORMAT I Page No: 5 of 16
POL-BSFF-0223833_0004
POL00397163
POL00397163
[e) [TITLE \* MERGEFORMAT ] °
FUJITSU [SUBJECT \* MERGEFORMAT ] &
1. Introduction
1.1 Summary
The Complexity of Migration of a major application such as TPS necessitates an up to date and accurate
detail of all tasks required in terms of order, precedence and completeness.
Database object changes will cause database dependencies to become invalid such as runtime views,
stored and packaged procedures etc.
Code and data changes will necessarily mean Low level designs will need to be updated and test plans
created.
The aim of this document is to act as an establishment of scope and an attempt to minimise the risk
inherent in migration and help guide the production of test plans and more importantly provide a sense of
control over the period leading up to and indeed during Migration.
Five work areas can be readily identified from the TPS High Level Design and tasks listed beneath each
area of work in order of chronological sequence required to complete.
1.2 Scope
The purpose of this document is to implement the application changes specified by Transaction
Processing System High Level Design for HNG-X; reference DES/APP/HLD/0027.
The diagram below shows the scope of the Transaction Processing Service consisting of the TPS Host
System, the Branch Database and a FTMS File Transfer System.
3rd Party Systems FRTS
ry
POL POL Client HR SAP’ POLIS =
Settlement
v r 4
APS Summary HR SAP Files TAN Files Bureau Files
SHREAP_CUTPUT SFRTS_OUTPUT
‘$CTS_OUTPUT $TIP_OUTPUT 7
Fujitsu
SPOLF:
FOL FS ¥ ~ S-SHARE Wes)
SPOLFS_INPUT_SHARE(NFS) ©) TPS. ‘$MIS_OUTPUT(NFS) DWH
TXN Corrections
e r
os
go
wes
TXN Corrections’ Transactions
APS Branch
Database
SCopyright Fujitsu Services Ltd 2007 [SUBJECT \ MERGEFORMAT I Ref DEVIAPP/LLD/0092
Version: V0.1
Date: 22-Jun-07
[KEYWORDS \* MERGEFORMAT ]
UNCONTROLLED IF PRINTED Page No: 6 of 16
POL-BSFF-0223833_0005
o
FUJITSU
[TITLE MERGEFORMAT ]
[SUBJECT \* MERGEFORMAT ]
POL00397163
POL00397163
There are five main areas of work required:
1.3
Riposte Name Removal
PCI Support
Application Components
Migration
Rollback Segments.
Assumptions
It is assumed that all environment variables for the UNIX user tps are set as for Horizon with the
exception of PATH and LD_LIBRARY_PATH which should reference Oracle10g libraries / directories.
It is assumed that the TPS database has been migrated to Oracle10g on Solaris10.
2
2.1
Riposte Name Removal
To comply with copyright requirements for HNG-X the name RIPOSTE_NUM is replaced with
JOURNAL_SEQ_NUMBER.
Tables
The following are the schema changes to replace any occurance of RIPOSTE_NUM or
RIPOSTE_MESSAGE_NUMBER with JOURNAL_SEQ_NUMBER,
RIPOSTE_MESSAGE_PART_1_OF_2 and RIPOSTE_MESSAGE_PART_2_OF_2 with
TXN_MESSAGE_PART_1_OF_2 and TXN_MESSAGE_PART_2_OF_2 respectively
Task #
Task
Action
Replace the use of the word Riposte in
database field names
HLD 4.1.11
The name RIPOSTE_NUM is replaced with
JOURNAL_SEQ_NUNM in the following tables:
TMS_RX_APS_TRANSACTIONS_pX
TMS_RX_BDC_TRANSACTIONS_pX
TMS_RX_CUT_OFF_SUMMARIES_pX
TMS_RX_EFT_TRANSACTIONS_pX
TMS_RX_EPOSS_EVENTS_pX
TMS_RX_EPOSS_TRANSACTIONS_pX
TMS_RX_NWB_TRANSACTIONS_pX
TMS_RX_CASH_ACCOUNTS_px"
TMS_RX_OBCS_TRANSACTIONS_pX
TMS_RX_OBCS_STATUSES_pX
TMS_RX_STOCK_HOLDINGS_pX'
The name RIPOSTE_MESSAGE_NUMBER is
replaced with JOURNAL_SEQ_NUM in the
following tables:
TMS_RX_COFA_TRANSACTIONS_pX'
TMS_RX_COUNTER_TRAN_ERRORS
TMS_RX_COUNTER_TRAN_ERRORS_ARC
The name
RIPOSTE_MESSAGE_PART_1_OF_2 and
Copyright Fujitsu Services Ltd 2007
UNCONTROLLED IF PRINTED
[KEYWORDS \* MERGEFORMAT ]
Version: V0.1
Date: 22-Jun-07
PageNo: 7 of 16
[SUBJECT \" MERGEFORMAT ] Ref: DEV/APP/LLD/0092
POL-BSFF-0223833_0006
o
FUJITSU
[TITLE MERGEFORMAT ]
[SUBJECT \* MERGEFORMAT ]
POL00397163
POL00397163
RIPOSTE_MESSAGE_PART_2_OF _2is
replaced with TXN_MESSAGE_PART_1_OF_2
and TXN_MESSAGE_PART_2_OF_2 in the
following tables:
TMS_HARVESTER_EXCEPTIONS_p*
TMS_HARVESTER_EXCEPTIONS_ARC
Where
pis either 1 to 65, 6SRC
ort 1 to 64
X is ‘A’ to ‘B’ (in all cases where p = 1 to 64).
run tps_riposte_app.sh
This script removes the occurrance of the label
Riposte changes to HNGX
Example:
alter table tms_rx_aps_transactions_1A
rename column riposte_num to
journal_seq_num;
Create Special Agent ‘H’ views and
Synonyms for all tables where
RIPOSTE_NUM is replaced with
JOURNAL_SEQ_NUM to be accessed
by Horizon Agents
HLD 4.1.11.1
CREATE OR REPLACE the following scripts:
tun tps_riposte_h_views.sh
This script creates special agent 'H' views and
synonyms
Example:
Create or replace view
h_tms_rx_aps_transactions_1A select
journal_seq_num alias riposte_num,
from tms_rx_aps_transactions_IA;
Create or replace public synonym
tms_rx_aps_transactions_1A for
ops$tps.h_tms_rx_aps_transactions_1A;
Truncate redundant Tables
HLD 4.1.4
TRUNCATE the following Tables:
TMS_RX_CASH_ACCOUNTS_px'
TMS_RX_OBCS_TRANSACTIONS_pX
TMS_RX_OBCS_STATUSES_pX
TMS_RX_STOCK_HOLDINGS_pX'
TMS_RX_COFA_TRANSACTIONS_pX!
TMS_RX_COFA_SUMMARIES_pX"
Where
pis either 1 to 65, SRC
or! 1 to 64
X is ‘A’ to ‘B' (in all cases where p = 1 to 64).
tun tps_truncate_tables.sh
This script truncates selected tablenames.
Example:
truncate table
tms_rx_cofa_transactions_1A;
4
Recreate database views and
dependencies
CREATE OR REPLACE the following views:
©Copyright Fujitsu Services Ltd 2007
UNCONTROLLED IF PRINTED
[KEYWORDS \* MERGEFORMAT ]
Version: V0.1
Date: 22-Jun-07
PageNo: 8 of 16
[SUBJECT \* MERGEFORMAT ] Ref: DEV/APP/LLD/0092
POL-BSFF-0223833_0007
[TITLE \* MERGEFORMAT ]
FUJITSU [SUBJECT \* MERGEFORMAT ]
POL00397163
POL00397163
XXX_RX_APS_TRANSACTIONS_p
X0OXRX_APS_TRANSACTIONS
XXX_RX_BDC_TRANSACTIONS_p
XXXRX_BDC_TRANSACTIONS
XXX_RX_CUT_OFF_SUMMARIES_p
XXX_RX_CUT_OFF_SUMMARIES
X0RX_EFT_TRANSACTIONS_p
XXX_RX_EFT_TRANSACTIONS
XXX_RX_EPOSS_EVENTS_p
X0XRX_EPOSS_EVENTS
XXX_RX_EPOSS_TRANSACTIONS_p
XXX_RX_EPOSS_TRANSACTIONS
XXX_RX_NWB_TRANSACTIONS_p
XXX_RX_NWB_TRANSACTIONS
XXX RX_CASH_ACCOUNTS_p
XXX_RX_CASH_ACCOUNTS
XXX_RX_OBCS_TRANSACTIONS_p
XXX_RX_OBCS_TRANSACTIONS.
XXX_RX_OBCS_STATUSES_p
XXX_RX_OBCS_STATUSES
XXX_RX_STOCK_HOLDINGS_p
XXX_RX_STOCK_HOLDINGS
X0XRX_COFA_TRANSACTIONS_p
XXX_RX_COFA_TRANSACTIONS
XXX_RX_TRANSACTIONS
TMS_HARVESTER_EXCEPTIONS
Where
XXX is TMS, SAV
pis 1to64
tun tps_riposte_p_views.sh
This script re-creates views and synonyms for all
p (partitioned) tables that have been redefined
by RIPOSTE replacement
run tps_riposte_o_views.sql
This script recreates the o(verall) views and
synonyms for which their underlying table
definitions have changed.
Therefore total views:
2°64 +2 = 130 views per table * 12 +2+1= 1564
Example (pseudo):
SELECT table_name_p FROM user_tables
WHERE table name_p LIKE ‘TMS RX_%’
CREATE OR REPLACE VIEW SAV_table_name_p
AS SELECT * from table_name_pA or B
where tps_system_parameters.table_set is
Aor B
CREATE OR REPLACE VIEW TMS_table_name_p
AS SELECT * from table_name_pA or B
where tps_system parameters. table_set is
BorA
5 I Recompile PL/SQL Modules and
dependencies
ced $ORACLE_HOME/rdbms/admin
sqlplus “/ as sysdba”
@utlrp.sql
©Copyright Fujitsu Services Ltd 2007 [SUBJECT \* MERGEFORMAT ] Ref: DEV/APP/LLD/0092
Version: V0.1
* Date: 22-Jun-07
UNCONTROLLED IF PRINTED [KEVWORDS! \' MERGEFORMAT I Page No: 9 of 16
POL-BSFF-0223833_0008
POL00397163
POL00397163
[e) [TITLE \* MERGEFORMAT ] °
FUJITSU [SUBJECT \* MERGEFORMAT ] &
Note: There should be no other DDL on the
database while running the script. Not following
this recommendation may lead to deadlocks.
6 _I Replace the use of the word Riposte inI The name
Pro*C Modules RIPOSTE_MESSAGE_PART_1_OF_2 and
RIPOSTE_MESSAGE_PART_2_OF_2is
replaced with TXN_MESSAGE_PART_1_OF_2
and TXN_MESSAGE_PART_2_OF_2 inthe
following source files:
TPSC209.h
Create Special Agent ‘H’ views and Synonyms
for all tables where RIPOSTE_NUM is replaced
with JOURNAL_SEQ_NUM to be accessed by
Horizon Agents in the following source files:
TPSC209.pc
The name RIPOSTE_NUN is replaced with
JOURNAL_SEQ_NUM in the following source
files:
TPSC253.h
TPSC254.h
TPSC253.pc
TPSC254.pc
7 Recompile Pro*C Modules Compile the following modules:
TPSC209
TPSC253
TPSC254
2.2 Shell Script - tps_riposte_app.sh
The shell script, tps_riposte_app.sh removes the occurance of the label Riposte by performing the
following operation:
1. scan all the tables with RIPOSTE_NUM, RIPOSTE_MESSAGE_NUMBER,
RIPOSTE_MESSAGE_PART_1_OF_2 and RIPOSTE_MESSAGE_PART_2_OF_2.
2. rename RIPOSTE_NUM, RIPOSTE_MESSAGE_NUMBER to JOURNAL_SEQ_NUMBER,
RIPOSTE_MESSAGE_PART_1_OF_2 and RIPOSTE_MESSAGE_PART_2_OF_2 with
TXN_MESSAGE_PART_1_OF_2 and TXN_MESSAGE_PART_2_OF_2 respectively.
2.3 Shell Script - tps_riposte_h_views.sh
The shell script, tps_riposte_h_views.sh creates special agent 'H' views and synonyms for all tables
where RIPOSTE_NUM has been replaced with JOURNAL_SEQ_NUM (to be accessed by Horizon
Agents). It performs the following:
1. scan all the tables with JOURNAL_SEQ_NUM
2. for each table found create an 'H' view and synonym
Note: run tps_riposte_app.sh first
‘©Copyright Fujitsu Services Ltd 2007 [SUBJECT \" MERGEFORMAT ] Ref: DEV/APP/LLD/0092
Version: V0.1
Date: 22-Jun-07
[KEYWORDS \* MERGEFORMAT ] Page No 40 of 16
UNCONTROLLED IF PRINTED
POL-BSFF-0223833_0009
POL00397163
POL00397163
[e) [TITLE \* MERGEFORMAT ] °
FUJITSU [SUBJECT \* MERGEFORMAT ] &
2.4 Shell Script - tps_truncate_tables.sh
The shell script, tos_truncate_tables.sh truncates selected tablenames. It performs the following:
1. scan all the tables for selected patterns of table names
2. for each table found - truncate it
2.5 Shell Script - tps_riposte_p_views.sh
The shell script, tps_riposte_p_views.sh re-creates views and synonyms for all p (partitioned) tables
that have been redefined by RIPOSTE replacement. It performs the following:
1. read the current Table set A or B
2. scan all the tables where RIPOSTE has been changed
3. for each table found recreate views and synonyms using current table set
Note: run tps_riposte_app.sh first
2.6 SQL Script - tps_riposte_o_views.sql
The SQL script, tps_riposte_o_views.sqI recreates the overall views for which their underlying table
definitions have changed
Note: run tps_riposte_p_views.sh first
3 PCI Support
The current TPS will be updated to meet the requirements for the Payment Card industry (PCI). The
PAN field is replaced with a obfusgated value which is 19 alphanumeric characters in length.
3.1 Tables
The following are the schema changes to replace any occurance of PAN stored as NUMBER with a data
type of VARCHAR2 (19).
Task# I Task Action
1 Change the datatype of the PAN The numeric datatype of PAN is changed to
database field alphanumeric VARCHAR2 in the following tables:
HLD 4.1.9 TMS_RX_EFT_TRANSACTIONS_pX
TMS_RX_NWB_TRANSACTIONS_pX
Where
pis 1 to 65, 65RC
X is ‘A’ to ‘B' (in cases where p = 1 to 64).
tun tps_pci_app.sh
‘©Copyright Fujitsu Services Ltd 2007 [SUBJECT \" MERGEFORMAT ] Ref: DEV/APP/LLD/0092
Version: V0.1
Date: 22-Jun-07
[KEYWORDS \* MERGEFORMAT ] Page No: 11 of 16
UNCONTROLLED IF PRINTED
POL-BSFF-0223833_0010
o
FUJITSU
[TITLE MERGEFORMAT ]
[SUBJECT \* MERGEFORMAT ]
POL00397163
POL00397163
This script prepares PC! application changes to
HNGX
Example:
alter table tms_rx_eft_transactions_1A
add(new_pan varchar2(19));
update tms_rx_eft_transactions_1A set
new_pan = to_char (pan) ;
commit;
alter table tms_rx_eft_transactions_1A
set unused column pan;
alter table tms_rx_eft_transactions_1A
drop unused columns; — ~
alter table tms_rx_eft_transactions_1A
xename column new_pan to pan;
2 I Recreate database views and
dependencies
CREATE OR REPLACE the following scripts:
XXX_RX_EFT_TRANSACTIONS_p
XXX_RX_EFT_TRANSACTIONS
XXX_RX_NWB_TRANSACTIONS_p
XXX_RX_NWB_TRANSACTIONS
XXX_RX_TRANSACTIONS
Where
XXX is TMS, SAV
pis 1 to 64
Therefore total views:
2°64 +2 = 130 views per table * 2 + 2 = 262
tun tps_riposte_p_views.sh
Tun tps_riposte_o_views.sql
3 Recompile PL/SQL Modules and
dependencies
ed $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba”
@ utlrp.sql
Note: There should be no other DDL on the
database while running the script. Not following
this recommendation may lead to deadlocks.
4 I Change the datatype of PAN in Pro*C
Modules
The numeric datatype of PAN is changed to
alphanumeric char pan[20] in the following source
files:
TPSC270.h
TPSC270.pe
tip.pe
5 Recompile Pro*C Modules
Compile the following modules:
TPSC270
TPSC287
3.2 Shell Script - tps_pci_app.sh
The shell script, tos_pci_app.sh performs the following operation:
1. Get the column_name, table_name from the user_tab_columns for the column name = ‘PAN' and
the data_type='NUMBER’ AND data_precision=19.
©Copyright Fujitsu Services Ltd 2007 [SUBJECT \" MERGEFORMAT I Ref: DEV/APP/LLD/0092
Version: V0.1
“ Date: 22-Jun-07
UNCONTROLLED IF PRINTED [KEYWORDS! ' MERGEFORMAT I Page No: 12 of 16
POL-BSFF-0223833_0011
POL00397163
POL00397163
[TITLE \* MERGEFORMAT ] &
[SUBJECT \* MERGEFORMAT ]
o
FUJITSU
Add a new colume ‘pci_pan' with the data type of varchar2(19) to the table as found in (1).
assign the column value of column_name as in (1) to the new colume ‘pci_pan’
Drop the column_name as in (1).
a PF ON
rename the pci_pan column to the column_name as in (1)
4 Application Components
To Comforn to PCI Compliance the PAN value must NOT be held in clear form in any of the database.
Task# I Task Action
1 PAN - zeroise it to obfusgate in Pro*C
Modules
The pan number is zeroised by adding function
ZeroPan, it zeroises all digits after the first 6,
it should retain the last 4 in the following
source files:
tip.h
tip.pe
Compile the following modules:
TPSC287
2 Recompile Pro*C Modules
5 Migration
During the migration from Horizon to HNG-X, Hydra, the Horizon Agents provide data to the Branch
Database via tables held in TPS. Transaction data from Horizon counters is required in the Branch
Database so that when the counter migrates to HNG-X the historical data is available for reports and
summaries.
In order to distinguish between transaction data harvested from the Branch Database and that from the
Agent Harvesters a new field is added to each of the relevant tables. This field defaults to ‘N’ so that any
records created by the Agent Harvesters are distinguishable from records created by the Branch
database which will set it to ‘Y’. The Branch database can then copy all records which are set to the
default value of ‘N'. The data is transferred to the counter either by Agent processes or via the Branch
Database..A new field ACTIONED_IND_HNGx is required for the Branch Database to set once the
record has been transferred.
The TPS Agent Harvester populates tables TMS_RX_HNGX_MIGRATION_DAY,
TMS_RX_HNGX_MIGRATION_PREP with the Current Stock position of the Branch so that the Branch
Database has the correct values at the point of migration to HNG-X.
Task# I Task Action
Add the alphanumeric ACTIONED_IND_HNGX
column and the alphabetic (‘Y’ or ‘N’)
1 Add the two new fields, transaction
corrections Indicator and transaction
Indicator Flag to database fields (to be
used by interfaces from BRDB)
HLD 4.1.10.1, 4.1.10.2
TRANSACTION_IND_FLAG column to the
following tables:
TMS_RX_APS_TRANSACTIONS_pX
TMS_RX_BDC_TRANSACTIONS_pX
TMS_RX_EFT_TRANSACTIONS_pX
TMS_RX_EPOSS_EVENTS_pX
TMS_RX_EPOSS_TRANSACTIONS_pX
TMS_RX_NWB_TRANSACTIONS_pX
Where
‘©Copyright Fujitsu Services Ltd 2007 [SUBJECT \" MERGEFORMAT ] Ref: DEV/APP/LLD/0092
Version: V0.1
“ Date: 22-Jun-07
UNCONTROLLED IF PRINTED [KEYWORDS! ' MERGEFORMAT I Page No: 13 of 16
POL-BSFF-0223833_0012
o
FUJITSU
[TITLE MERGEFORMAT ]
[SUBJECT \* MERGEFORMAT ]
POL00397163
POL00397163
pis 1 to 65, 65RC
X is ‘A’ to ‘B’ (in cases where p = 1 to 64).
run tps_mig_trans.sh
This script modifys selected tablenames
Example:
alter table tms_rx_eft_transactions_1A
add (actioned ox
varchar2 (1) ,transaction_ind flag
vwarchar2(1) default ‘N’);
2 Recreate database views and
dependencies
CREATE OR REPLACE the following scripts:
XXX_RX_APS_TRANSACTIONS_p
XXX_RX_APS_TRANSACTIONS
XXX_RX_BDC_TRANSACTIONS_p
XXX_RX_BDC_TRANSACTIONS
XXXCRX_EFT_TRANSACTIONS_p
XXX_RX_EFT_TRANSACTIONS
XXX_RX_EPOSS_EVENTS_p
XXXRX_EPOSS_EVENTS
XXX_RX_EPOSS_TRANSACTIONS_p
XXX_RX_EPOSS_TRANSACTIONS
XXX_RX_NWB_TRANSACTIONS_p
XXX_RX_NWB_TRANSACTIONS
XXX_RX_TRANSACTIONS
Where
XXX is TMS, SAV
pis 1 to64
Therefore total views:
2°64 +2 = 130 views per table * 6 + 2 = 782
run tps_riposte_p_views.sh
run tps_riposte_o_views.sql
3 Recompile PL/SQL Modules and
dependencies
cd $ORACLE_HOME/rdbms/admin
sqlplus ‘/ as sysdba”
@uthrp.sql
Note: There should be no other DDL on the
database while running the script. Not following
this recommendation may lead to deadlocks.
4 I Create database XML stock tables,
users, roles, privileges
CREATE TABLES.
TMS_RX_HNGX_MIGRATION_DAY,
TMS_RX_HNGX_MIGRATION_PREP
tun tps_create_stock_tables.sq]
This script creates the XML stock tables
Example:
create table tms_rx_hngx_migration_day
(group_id number (6),
trading date date,
insert _date date,
migration_date,
LOB (migration data) STORE AS clob store
Copyright Fujitsu Services Ltd 2007
UNCONTROLLED IF PRINTED
[SUBJECT \" MERGEFORMAT ] Ref:
[KEYWORDS \* MERGEFORMAT ]
Version: v0.1
Date: 22-Jun-07
Page No: 14 of 16
DEV/APP/LLD/0092
POL-BSFF-0223833_0013
POL00397163
POL00397163
[e) [TITLE \* MERGEFORMAT ] °
FUJITSU [SUBJECT \* MERGEFORMAT ] &
C
TABLESPACE raw_data
STORAGE (INITIAL 100K NEXT 100K
PCTINCREASE 0)
CHUNK 4
PCTVERSION 10
INDEX clob_index (
TABLESPACE raw_index))) ;
5.1 Shell Script - tps_mig_trans.sh
The shell script, tps_mig_trans.sh modifys selected tablenames. It performs the following:
1. scan all the tables for selected patterns of table names
2. for each table found modify it - add two new transaction column names
5.2 SQL Script - tps_create_stock_tables.sql
The SQL script, tps_create_stock_tables.sqI creates the XML stock tables
6 Rollback Segments
The rollback of database transactions will use Oracle 10g AUTO Undo management, instead of Rollback
Segments. This section describes and implements the steps to convert the rollback segments to AUTO
Undo management.
1. Find all the rollback segments.
Drop all the rollback segments except for system
drop rollback tablespace
create new undo tablespace
a PF ON
add undo_management = AUTO, undo_tablespace=UNDO_DRS to inittps.ora and comment out
the rollback_segs param
6. bounce the database
‘tps_app_ug.sh’ shell script will perform 1 to 4 of the above steps and used the existing rollback segment
datafiles for undo_tablespace
Task# I Task Action
1 The rollback of database transactions —_I Prepare application upgrade to HNGX 1) remove
will use Oracle 10g AUTO Undo all rollback segments 2) drop the rollback segment
management — Check and fix Oracle _I tablespace3) create Undo segment without
initialisation parameters, remove ‘old’ I datafile sizing (raw files):
style rollback segment usage and
Pro*C Modules usage
HLD 4.13 This script prepares Undo Management to HNGX
tun tps_app_ug.sh
Check spfiletps.ora (init.ora) settings:
undo_management=AUTO
undo_tablespace=UNDO_TPS
restart database
‘©Copyright Fujitsu Services Ltd 2007 [SUBJECT \" MERGEFORMAT ] Ref: DEV/APP/LLD/0092
Version: V0.1
“ Date: 22-Jun-07
UNCONTROLLED IF PRINTED [KEYWORDS! ' MERGEFORMAT I Page No: 15 of 16
POL-BSFF-0223833_0014
POL00397163
POL00397163
[e) [TITLE \* MERGEFORMAT ] °
FUJITSU [SUBJECT \* MERGEFORMAT ] &
remove EXEC SQL SET TRANSACTION USE
ROLLBACK SEGMENT from the following source
files:
TPSC207.pc
TPSC280.pc
TPSC281.pc
2 Recompile Pro*C Modules
Compile the following modules:
TPSC207
TPSC280
TPSC281
6.1 Shell Script - tps_app_ug.sh
The shell script, tps_app_ug.sh prepares automatic undo management upgrade to HNGX.
It performs the following:
1. removes all rollback segments
2. drop the rollback segment tablespace
3. create Undo segment
Copyright Fujitsu Services Ltd 2007
UNCONTROLLED IF PRINTED
[SUBJECT \" MERGEFORMAT ] Ref: DEV/APP/LLD/0092
Version: V0.1
Date: 22-Jun-07
[KEYWORDS \* MERGEFORMAT ] Page No 46 of 16
POL-BSFF-0223833_0015