Transcript Census Bureau

Census Bureau
System IPT
Decennial Systems Database Modernization Support
DRIS
Decennial Response Integration System
Date: 07/30/2007
Index

What have we done in past 3
months




Work done on DE101



dscmotd1 and DE101 compare
Moved to DE101 and then to BE101
And many more …
Scripts creation for load
Studying application for index
creation
UECT creation from UNM


Writing script based on state retrieval
Unit testing for UECT
2




Performance and tuning



Full load
Full test for UECT load- successful
Compare times with old machine
Partitioning tables
Comparing results for 2000
Change in retrieval criteria






Retrieval criteria to LCO basis
Changed scripts
Partitioned tables based on LCO
Unit testing and full testing
Taking advantage based on CPUs
Running scripts in parallel
3

Move DE101 to BE101



Data modeling




Move scripts from DE101 to BE101
Test all scripts for performance
Version difference
Production m/c
Work around/Solution
Reverse Engineering effort
4
What have we done in past 3 months



dscmotd1 and DE101 compare
Moved to DE101 and then to BE101
And many more …
dscmotd1


DE101
BE101
Got acceptable and best results on
DE101
Moved schema to DE101
5
Work done on DE101

Scripts created for




Study application to create proper
indexes



Data load
Indexes
analyzing table objects
Fast data retrieval
Fast data update
Test data load and compare with old
machine

Acceptable results

Data load in 1.5 hrs for whole country
compare to 30 hrs on old machine
6
UECT creation from UNM





Scripts based on state retrieval – 2000
Unit testing
Full load testing
Full test for UECT load- successful
Compare times with old machine
UNM table
process
UECT
UNM table
Retrieval based on state
7
Performance & Tuning



Results not up to the mark
P & T was needed
Data size, too high

table partitioning
process
UECT
Retrieval based on state
8
Change in retrieval criteria




Data retrieval based on LCO
and NOT on state based
Unit and full testing
Advantages of CPU bound
operations
Scripts run in parallel
UECT
process
Retrieval based on
LCO
9
Moving from DE101 to BE101





Got new host
DE101 - Production
BE101 – Beta/test
Same configuration
Whole testing to be done
10
Data Modeling Problems



Data repository was not compatible with Database
version
Could not do data modeling using Oracle designer
Work around using third party tool. No stoppage of
work
Oracle db
10g
Desktop
Data modeling
X
Versions not
compatible
Designer repository
6.x
DE101
11
Work Around

Work around using third party tool. No stoppage of
work
Desktop
Data modeling
Third party tool
Oracle db
10g
Versions compatible
Scripts creation/
execution
DE101
12
Reverse Engineering effort – not done
Oracle db
Desktop
Data modeling
DE101
Versions compatible
Designer repository
BE104
13
THE END
14