HSBC Insurance Speeds Reorganizations and Batch Jobs with CoSort
Reviewer
Luiz Contin, IT manager of development systems, with contributions from HSBC analysts Gilmar Pinto, Luciano Panini and Franciane Conrad, and Renata Binotto, press assessor.
Background
HSBC Seguros (Insurance) is a subsidiary of HSBC Bank Brasil S.A. – Banco Múltiplo, and it is part of an integrated financial services business. HSBC Seguros manufactures life, personal accident policies, capitalization and pension plans, sold through the HSBC Brazil branch network and independent brokers. At the end of 2008, the company had 886 employees and recorded a net profit of U.S. $224 million, an increase of 99.5 percent over the prior year.
Platform
Sybase 12.5.4, CoSort 9, and HP-UX 11 running on multi-CPU HP 9000/RP7420 development and production systems.
Problem Solved
The HSBC Seguros IT department updates Sybase with text and binary data for billing, life insurance and capitalization systems. Customer data comes in from branches which sell or update insurance contracts, record payments and change beneficiary clauses. We have to process and upload these files every day and perform a major update every month. We needed an efficient, scalable way to update the database without impacting online users; i.e., a fast, external way to capture changed data and reorg the database. Prior to CoSort, we used complex C programs for data preparation (and two in-house sort routines) that were limited in their ability to process large data volumes and were difficult to maintain. They were also slow - one sort alone took an hour. Fortunately, switching our processes to use CoSort was easy, and we like that fact that CoSort can integrate and transform our large binary and text files in easy 4GL. That same sort now takes five minutes, and Sybase loads the changed, presorted data much faster. CoSort improved our average overall system performance by 60 percent (and in some cases more than 80 percent), removed the data volume ceiling, reduced complexity and freed the database for its intended purpose – queries.
Product Functionality
High volume transformations done in the database can be done faster, outside the database, with CoSort. The 4GL in CoSort (called SortCL) provides the same aggregate, scalar, string, sort, join and math functions as SQL. CoSort can run these tasks in against many large input and output files – along with reformatting, conversion and protection functions – all in the same job script and I/O pass. Our project analysts consider job volume and design when choosing which jobs are best suited for either SQL or CoSort (SortCL), allowing us to apply the right solution in context. CoSort's ability to perform calculations, joins and aggregations with binary double and other floating point data is particularly valuable to us.
Strengths
After five years of throwing CoSort against large daily transaction files without errors or support calls, our users are certain they adopted the best solution for HSBC's data processing needs. We see CoSort's stability and performance as its main strengths. We also appreciate the ease of coding in and maintaining the product's metadata infrastructure. Data definitions and job specifications are held in simple, open-text repositories that we can easily modify, reuse and share with other applications.
Weaknesses
IRI's CoSort-compatible Fast Extract (FACT) tool, which works in Oracle and DB2 environments, is not currently available for Sybase. This would speed unloads and facilitate a piped reorg process through CoSort. IRI indicates that FACT and/or CoSort will have Sybase connections in the future.
Selection Criteria
We evaluated several available solutions on their speed, usability, ease of integration, stability, price and support. Performance in our procedural environment was the primary consideration, but CoSort was the clear choice in every category.
Deliverables
CoSort has no limits on input size, delivers uniformly superior performance across all volume levels and gives us the comfort of a stable solution with expert technical support. Its price was viable, and we were able to adopt the solution into our daily and monthly routines quickly.
Vendor Support
We have been very satisfied with the level of help and software updates our local CoSort agency has provided us over the years and the responsiveness that IRI developers have shown to new feature requests. HSBC uses CoSort in other departments, as do many sites in Brazil; our agent also helps us leverage this collective experience.
Documentation
The CoSort user manual is large, but not often needed. Its directions for declaring our binary types in the tool were sufficient and clear.