GSE CICS and IMSDB2working group

1
GSE CICS and IMS/DB2-working group

• Nexxar Group Business Case
• October 11, 2007

Prepared by, Eddy Tiels DBA Nexxar
Group Email etiels_at_senditresources.com Phone (32
-2) 467.35.04
2
Agenda

• Who are we?
• Old architecture
• Bussines needs
• New architecture
• DB2 implementation

3
Agenda

• Who are we?
• Old architecture
• Bussines needs
• New architecture
• DB2 implementation

4
Who are we?

• A financial services company providing global
  payments and transaction processing services to
  under-banked and underserved ethnic markets
• Products
• Money Transfer
• Money Orders
• Check Cashing
• Bill Payment
• SendIT Resources is processor of financial
  transactions for Nexxar Group and provides
  Private Label offering

• A financial services company providing global
  payments and transaction processing services to
  under-banked and underserved ethnic markets
• Products
• Money Transfer
• Money Orders
• Check Cashing
• Bill Payment
• With 800 employees, Nexxar Group operates in 105
  countries

• A financial services company providing global
  payments and transaction processing services to
  under-banked and underserved ethnic markets
• Products
• Money Transfer
• Money Orders
• Check Cashing
• Bill Payment
• Business model involves acquiring and
  integrating smaller firms to accelerate footprint
  and growth

5
Agenda

• Who are we?
• Old architecture
• Bussines needs
• New architecture
• DB2 implementation

6
Old architecture

• Each of the six companies had its own
• IT infrastucture
• In total more then 80 x86 servers
• Applications
• developed in different languages
• Seperate features
• Different operating systems (Windows, Unix)
• Different DBMS (SQL Server, Sybase)

7
Agenda

• Who are we?
• Old architecture
• Bussines needs
• New architecture
• DB2 implementation

8
Bussines needs (1)

• The integration platform will be build from
  scratch and must include all compliance issues
• Browser based applications worldwide available
  running on any device
• Cost effective
• Scalability
• must run the business of the six companies
  together with new companies that will be aquired
• Cope for large difference in workload, ie Mothers
  day, back to school and Christmas

9
Bussines needs (2)

• Security
• In and between the application(s)
• DRP
• Flexibility
• Maintainability
• Timing Short and strict time frame due to
  several complience regulations, ie anti-money
  laundering programs

10
Agenda

• Who are we?
• Old architecture
• Bussines needs
• New architecture
• DB2 implementation

11
New architecture

• Design
• Proof of concept
• Implementation
• Integration

12
New architectureDesign

• Java J2EE application
• WebSphere Application Server on Linux
• z/VM to run Linux partitions
• PKI
• DB2 Connect, JAVA Universal driver
• z/OS.e
• DB2 for z/OS

13
New architectureProof of concept

• Development of the application on windows server
  with DB2 for LUW
• Conduct proof of concept in the IBM Montpellier
  center of excellence
• Setup of complete z/series architecture
• Port application and data to DB2 for z/OS
• Run tests of application
• Stress test of application for scalability
• Based on these results it was decided to go
  forward with the new architecture

14
New architectureImplementation (1)

• Hardware
• z9 BC Q02
• 2 CP, 346 mips 47 MSU
• 2 IFL
• 1 Cryptographic Processor
• 32 Gb Memory
• gt 1 GB HSA
• (4GB z/OS PROD 2 GB z/OS TEST)
• Rest for z/VM
• Hipersockets
• Disk storage DS8100
• Tape Storage

15
New architectureImplementation (2)

• Software (1)
• Rational (Java development)
• z/VM 5.2
• SUSE Linux 9.3
• Was 6.0
• PKI

16
New architectureImplementation (3)

• Software (2)
• z/OSe 1.7
• For new workloads such as Java, C/C, Web-based
  data transaction processing,
• will not execute CICS, IMS, COBOL, or FORTRAN
  applications, with some exceptions
• Reduced pricing for same code base as z/OS
• New name for z/OS.e is System z New Application
  License Charges (zNALC)
• IBM DB2 JDBC Universal Driver Architecture 2.9.31
• DB2 for z/OS 8.1
• Tools Tivoli Omegamon, DB2 ADMIN, EZ-DB2

17
New architectureImplementation (4)
NexxarPresentation.pdf
18
New architectureIntegration

• Import of 5 years of data from the old IT
  infrastructure
• Interface between old and new applications
• Start of Pilot agents to test
• new application
• new compliance infrastructure
• reporting
• Conversion of all companies, should be finished
  by the end of October 2007

19
Agenda

• Who are we?
• Old architecture
• Bussines needs
• New architecture
• DB2 implementation

20
DB2 implementation

• Unicode
• Dynamic Statement Cache
• LOB
• DRP
• CSV
• Bufferpool PGFIX
• Future projects

21
DB2 implementationUnicode

• Web application used in different languages
• English
• Spanish
• Portugese
• Vietnamese (Double byte)
• Complete JAVA application defined in Unicode
• All Data is stored in unicode ? no codepage
  translation between DB2 and application
• A field of 50 characters might have a length of
  more then 50 in the database

22
DB2 implementationDynamic Statement Cache

• 99 of all SQL is dynamic
• Only SQL in SQL Stored Procedures is Static
• parameter markers
• For Java Beans the SQL is always generated with ?
• For Java code only ? when coded by developer,
  Literal when SQL is based on selection on the
  screen
• EXPLAIN STMTCACHE ALL
• Fills DSN_STATEMENT_CACHE_TABLE with a lot of
  execution information

23
DB2 implementationLOB

• BLOB used to contain images from
• Passport
• Legal documents
• Scanner attached to the PC running the WEB
  application
• Customised settings to reduce size of BLOB

24
DB2 implementationDRP (1)

• Daily full volume DUMP of DB2 and non DB2 data,
  last version on Disk, copy on tape
• SET LOG SUSPEND
• FLASH COPY of volumes
• SET LOG RESUME
• DB2 Image Copy
• Weekly Full IC
• Daily Incremental IC
• Archive log every 30 min FTP to FTP server

25
DB2 implementationDRP (2)

• Restore all volumes at DRP site, contains
• z/OS DB2 and Non DB2 data
• z/VM
• Linux WAS
• Restore Archive logs and BSDS with FTP
• Flash copy Restored volumes
• Update BSDS information
• Restore system logonly
• Check if other recovery needed
• Flash copy Recovered volumes

26
DB2 implementationCSV (1)

• CSV is used to transfer data between old and new
  applications and XLS input
• How to transfer PC data to z/OS.e
• data containing no special characters ? standard
  text FTP
• data containing special characters ? first
  convert to UNICODE
• UNICODE data ? binary FTP
• Proces FTP file to remove 3 byte header and split
  records when CRLF (x 0D0A) is found

27
DB2 implementationCSV (2)

• CSV LOAD statement
• LOAD DATA INDDN SYSREC00 LOG YES RESUME YES
• UNICODE CCSID(00367,01208,01200)
  
• FORMAT DELIMITED COLDEL X'3B' CHARDEL X'22'
  DECPT X'2E'
• INTO TABLE NXS102.BENEF_REFRESH_INFO
  
• ( BNFNUMERO POSITION() INTEGER
  
• , ID_NUMBER POSITION() VARCHAR MIXED
  
• , CPF_NUM POSITION() VARCHAR MIXED
  
• , ADDRESS POSITION() VARCHAR MIXED
  
• , CITY POSITION() VARCHAR MIXED
  )
• VARCHAR MIXED must be used for unicode double
  byte data to prevent data coruption
• For unicode data you need to specify the
  delimiters as the LOAD statement is in EBCDIC
• X'3B'
• X'22'
• X'2E' .
• Be current on maintenance

28
DB2 implementationBufferpool PGFIX (1)

• Test with table containing 1.000.000 rows in
  21240 pages with Average row length of 83
• 500 page bufferpool
• 20 Selects with TBSCAN on 3 fields returning 26
  rows
• GETPAGES 416700
• SYNCHRONOUS READ 20
• SEQ. PREFETCH REQS 26080
• PAGES READ ASYNCHR. 417200

29
DB2 implementationBufferpool PGFIX (2)

• PAGEFIXNO ? CP CPU TIME 15.721567
• PAGEFIXYES ? CP CPU TIME 15.400481
• 2 less CPU ? as we have no swapping PAGEFIXYES
  for bufferpools with high I/O rate

30
DB2 implementationWLM

• Priority by Application based on the primary
  authorization id
• Priority in an application based on Workstation
  information
• WLM fields
• AI Accounting info ? CLIENT_ACCOUNTING_INFO
• PC Application name ? CLIENT_APPLICATION_NAME
• SPM contains both enduser enduser workstation
  ? CLIENT_ID CLIENT_LOCATION

31
DB2 implementationFuture projects

• DRP add Storage box in DRP site with PPRC
  synchrounus copy of all data
• Row level security
• Ziip
• DB2 V9
• Enhanced backup/recovery (Object-level recovery)
• New SQL Stored Procedures
• DECIMAL FLOAT
• Peoplesoft migration from LUW to z/OS
• Data sharing