Retail distributor Metcash, owner of IGA distribution and IGA Fresh, this week revealed it had migrated to Oracle's Database 11g from 10.2g earlier this year.
I thought it best to roll up the two testing cycles into one and move on to the latest Oracle software
Metcash's Carl Young
The upgrade decision came as part of a wider data warehouse hardware consolidation program in late 2008, according to Carl Young, Metcash's technical lead for the project, with the company ditching its HP Superdome Unix servers for IBM P6 servers running the AIX operating system.
"I thought it best to roll up the two testing cycles into one and move on to the latest Oracle software," Young said of Metcash's motivation to upgrade to Database 11g. "It takes considerable effort to do the testing, in particular ensuring the [output] results are the same from both systems."
The distributor's data warehouse currently holds around two Terabytes of data and 1 million lines of code, which are the basis for Metcash's area managers to provide IGA's independent store owners advice on popular inventory, optimal inventory levels at the warehouse, or for example, analyse loading times for trucks at the warehouse.
The migration to 11g took a total of 12 weeks, while testing in Metcash's production environment with the systems running in parallel took six weeks. Metcash's 11g went into production in February this year.
Metcash's use of Oracle's programming tool Warehouse Builder, which automatically regenerated code from the source 10.2g database to the target 11g database, meant programmers weren't required for the task, however it meant a greater emphasis on testing results.
"Reconciliation is not a very fulfilling job," said Young, "but if someone sees an error or spike or anomaly in the data, people lose faith in the information."
"The code that comes out might be quite different to the source so, really you want to be testing against the results," he explained.
Benefits to Metcash included reducing batch report production time, cutting out the use of Excel spreadsheets, and the ability to store more data online. Other performance benefits came from query caching, which helped avoid duplicating retrieval as well as the ability to make structural changes to the database without having to rebuild the "materialised database view aggregate" — a process that used to take 24 hours.
Although Young admits moving to 11g were a small change, the principle behind improvements to 10.2g were correct — lower processing loads required to generate information.
Metcash's legacy database, prior to moving to Oracle Database 10.2g in 2004, was Infomix XPS, which sat on a HP Superdome Unix server.
