Return Home

Last updated 12/11/2004

The following descriptions are incomplete, and not all projects are listed. See full resume for details.

Jeffrey Benner, DB2 DBA - Projects

DB2 UDB Database Administrator AIX/EEE

Involved in regular day to day administrative tasks, supporting developers, Production support, complete Database Design and Modeling for a Specialty Pharmacy Internal and External Data Marts. (Specialty Pharmacy handles very high unit cost and low volume special-needs pharmacy sales.)

Installed DB2 UDB 7.1 Fixpack 5 on AIX. Created new instances and databases. Worked with Unix team to upgrade AIX release level.

Tuned database and Database manager configuration for optimum performance. Experimented with changing bufferpool and tablespace page size to improve time taken by ETL to build data marts. Experimented with indexes, and employed Index Advisor and Visual Explain, to both improve user report performance and speed ETL run times. Employed REORGCHK, REORG TABLE and RUNSTATS to further performance goals. Developed new tools for performance metrics data gathering. Analyzed effects of iterative changes on performance via these metrics.

Worked with developers to improve their report SQL (generated by Microstrategy 7i) through analysis of access paths. Supported developers in the development of very large SQL statements involving in some cases ten or more table joins with left outer joins, nested SQL, and other high performance features. Worked with them on indexes, database configuration, and SQL rewrites. Used db2audit to develop reports on kinds of SQL run on database.

Supported SQL included large joins, global temporary session tables, mass updates, left and full outer joins, nested queries, insert triggers, subqueries, common table expressions, materialized query tables (summary tables), mass updates, case expressions, high volume IMPORT INSERT and LOAD.

Worked heavily with data movement utilities IMPORT, LOAD, and EXPORT under high volume conditions.

Developed groups of VIEWs organized by multiple table schema which allow different sets of users to selectively access different subsets of data by different naming conventions, but allow all users access to the same underlying physical data. Supported this on the security level by developing GRANT scripts to allow only appropriate users access to certain VIEWs.

Developed new data model for Internal Data Mart. Developed specifications for ETL interface to new Internal Data Mart.

Developed and implemented BACKUP and RECOVERY strategy for Data Marts. Also designed and implemented database monitoring to alert DBAs via email to potential errors such as instance crash, log directory out of space, and tablespace containers out of space.

Supported and administered ETL interface between Specialty Pharmacy data marts and both Oracle Rdb database and main Prescription Benefit Management data warehouse.

Opened PMRs on multiple database problems including tablespace checksum errors, database crashes, and lost connection states. Supported 24/7 troubleshooting and problem resolution for the databases.

Responsible for mirroring Development database to Production to support development efforts. Supported Backup and recovery of development environments. Responsible for moving Development database objects to production.

Environment:

The Specialty data marts are comprised of four databases on four instances sharing a single machine. Production and development databases share 16 8 Gbyte disks with the four databases consuming a total of about 35 Gbytes of tablespace container space. Databases use SMS tablespaces defined with 16K, 8K and 4K pages.

IBM 7025 F80, 4-processor machine running with 8 Gbytes of physical memory. The F80 has 14 LVD SCSI physical disk drives, an assortment of both 9 Gbytes and 16 Gbytes, all mirrored.

DB2 UDB 7.1, AIX 4.3, Microstrategy 7i, Windows NT, Powerhouse.

ETL uses Korn/awk/sed script programming with DB2 command line processor (CLP) automation with FTP scripts for file transfer. cron for scheduling.

Solaris DB2 UDB Data Administrator in Securities Lending Data Warehouse

DBA providing performance and tuning support and recommendations for a conversion of a formerly Sybase-hosted (Sybase version 11.9.2) Securities Lending datamart to DB2 UDB for Solaris.

The datamart, which had a variety of frontends including PowerBuilder, Weblogic, Passport and DB2 OS/390 interface, and supported ad hoc queries via Access, supports the analysis and management of lending of securities to trading areas for management of large security portfolios. Interfaces employed ODBC, Merant ODBC, DirectConnect and SQLink.

Temporary global (SESSION) tables were heavily used in SQL procedures ported from the Sybase environment. Summary (AST) tables were also used extensively. Jobs scheduled via Control-M were run which parsed mainframe ftp data input into temporary tables, which were then extracted to newly formatted intermediate data files. These files would then either be imported (preferred) or loaded into the data warehouse fact and dimension tables.

This team deployed in UDB DB2 version 7.2 fixpack 3 on hardware running Solaris 8. UAT was deployed on an Enterprise 10000, 4 processors, 400 MHz, and 6 GBytes of memory. The production instance was deployed on a Serengeti 12 processors, 750 MHz, 12 Gbytes of memory. I/O was managed via Storage Area Network (SAN). This site also uses Tivoli Storage Management archival software.

The database consists of 135 tables and data, index and temporary tablespaces which together consume about 500 Gbytes.

This site had acquired Quest Central, so a GUI monitor was available, but I also developed scripts to give the development team more robust text-based access to snapshots and event monitors, iostats, top and similar statistics. I also prepared space requirements reports, bufferpool analysis, full explains on SQL with index recommendations, database-level and instance-level configuration recommendations, training of application team in researching database problems, and recommendations on hardware (e.g. swap space, memory, and kernel configuration parameters).

I trained the team in analysis of Explains and guidelines for index creation and maintenance, as well as provide reorganization and clustering related index advice. I developed all indexes now used by the application for performance optimization. I provided space recommendations and database and instance configuration recommendations that are the basis of their current practice.

DB2 UDB Data Administrator in Heavy OLTP Environment

DBA for credit reporting company's billing system. This system, at 24,000 update transactions per minute, may be highest volume UDB OLTP in the world. System consists of two separate nodes and databases, one for batch processing and the other for online, linked by high-speed SP frame. The online environment uses Forte transaction processing front-end.

Using UDB EE version 5.2 and 6.1 and AIX 4.3.1 OS on servers. Company also makes heavy use of Data Propagator replication.

As the only UDB consultant DBA working with two employee DBA's, my responsibilities include database maintenance (loads, exports, backups, restores, index, table and RI creation, updates, and maintenance), database monitoring (using snapshots and monitors, also the Database Guys suite of perl-based monitoring and analysis tools), and 24x7 support. Troubleshooting of AIX/UNIX problems and web server problems. Help Billing analysts develop better performing dynamic SQL.

SQL under my review included heavily recursive queries and queries making heavy use of correlated subqueries. Dealt heavily with locking contention and ways to rewrite application code to make better concurrent use of resources and reduce size of units of work. Problems in the system also frequently resulted from either problems with statistics or inadequate indexes, which become clear from explains.

I also performed tablespace container and physical space allocation analysis for several databases under my supervision. I made database-level and instance-level configuration parameter recommendations as a result of analysis of performance issues.

My main responsibility between routine database maintenance periods was to standardize and improve dozens of korn shell scripts used throughout the system for scheduled maintenance, monitoring, and job execution. Shell scripts were heavily based upon JCL procs and used almost none of the unix tools effectively. I rewrote all TUBS scripts to use Unix utilities (particularly awk) effectively. I built a HTML web-based 24x7 reporting interface which puts Database Guys performance reports out continuously, giving us a running history of database performance, so that we can understand performance issues for which we had no advance notice.

I was also responsible for maintenance, troubleshooting and performance analysis of Fraud Detection, Web Database, Expert Advisor, Re-run environment, QUAL environment, and Inter-bureau applications. I had responsibility altogether for over a dozen separate host servers running fifteen separate UDB databases (which included redundant hot recovery servers).

Utilities used here particularly included NT Control Center (particularly graphical explain) and Granger SQL. I did most real work through a Unix command prompt and DB2 Command Line Processor (CLP) scripts.

DB2 UDB Data Warehouse

DB2 DBA for a Silicon Valley software company which sells Internet architected (dot com, ecommerce oriented) intelligent customer interaction applications with real-time OLAP, campaign management and real-time customer personalization capabilities. E.Piphany's main product is known as E.4.

By the third quarter of 2000 this company had promised their customers that their data mart would be supported on DB2 UDB. It is currently supported on MS SQL Server 7.0 and on Oracle. I was brought in to assist in the conversion of database components of the existing product to UDB. This involved conversion of SQL, writing of new stored procedures, new user defined functions (UDF's), and extensive work on performance fine-tuning.

Programming required C using the xlc compiler, and used embedded SQL, CLI, and the DB2 Administrative API. Used mostly CLP and unix commandline for all development and administrative work while on Unix, while I used on NT command center, control center, and the command line processor environments. On NT I also used Microsoft SQL Server 7, mainly Enterprise Manager, Query Analyzer and Import and Export Data.

We were working on DB2 UDB 6.1 running on AIX 4.3.2. We were working on DB2 UDB 6.1 running on AIX 4.3.2. We were using a 4-way IBM F50 RS/6000 for testing.

Stored procedures used both CLI and the DB2 Administrative API. I used the GENERAL parameter calling convention though I am also familiar with DB2DARI. I tested stored procedures using an embedded SQL C program.

Performance issues studied included methods of forcing star joins; improvement of load speeds; impact of runstats; made extensive use of explains, dynexpln, db2expln, db2exfmt. Much work was done on improving performance of mass inserts, concentrated on extraction issues.

Examined a variety of database and database manager configuration parameters and their effects on performance. Examined and made recommendations regarding partitioning of tablespaces for large tables, use of containers, and other space management issues. Did buffer pool space analysis, also extended storage cache. SMS vs. DMS tablespaces. Effects of different extent and page sizes. Multi-page file allocation. Analysis of effects of non-default query optimization levels. Hash joins. Played with db2advis and explored various index scenarios to improve performance. Instance management. Examined effects of rollforward and ACTIVATE NOT LOGGED INITIALLY on datamart performance. Looked at effects of using intra-parallelism.

Converted Microsoft SQL Server Database and SQL to DB2 UDB; used SQL Server text file export and DB2 UDB Load to migrate databases between platforms.

Used Exceed X Windows emulator to work on AIX box from an NT machine.

Unix to Unix time clock conversion

Converted time and attendance system running at fourteen airports and servicing 12,000 union employees from a ten-year-old system running on NCR/600 and NCR/750 to new NCR/3000 machines. The conversion involved upgrades in Unix shell scripts and administration and in Micro Focus Cobol (from version 1.x to version 4.x). Converted an old time clock conversion servicing NCR 3760 Point of Reporting Terminal System (PORTS) Controller from NCR WSC and DCF to Focus eDCF software. Conversion involved Micro Focus Cobol conversions, writing and conversion of Bourne and Korn shell scripts, and testing, debugging and diagnosis of network communication control software for the time clocks.

I was involved in hardware configuration and installation as well as software development. Conversion also involved configuration and debugging of NCR Co-log PC-based controllers. Trained system administrators in use of new system. Supported staff in transition to new system.

Mainframe to Unix Conversion/Interface

Wrote conversion and interface programs for conversion of old MVS IMS and DB2 4.1 retail system to new AIX (Unix)-based PeopleSoft/Intrepid Evolution front-end using UDB 5.0 database and AIX Merant (Micro Focus) Object Cobol for conversion, data cleanup, and background processes. Performed MVS DB2 table loads. Analyzed and corrected problems with new UDB database and advised on design for database performance. Wrote DB2 to IMS interfaces. Set up Object Cobol on AIX, and trained personnel in their initial use of Object Cobol embedded SQL. Wrote UDB / AIX background processes using Object Cobol embedded SQL on AIX. Wrote PeopleSoft dynamic SQL calls using Object Cobol (ISPDYSQL). Supported staff with SQL queries against both DB2 and UDB. Built interfaces to IBM Data Propagator. Worked with IMS and IMS checkpoint restart capability.

LiveWire Web Application

Wrote web-based customer registration system for E-Clearing Corporation using Netscape Enterprise Server 3.0, LiveWire (server-side Javascript) 1.01, client-side Javascript, against an Informix database. Functioned as Webmaster, database admin, and Javascript developer on this project.

Distributed RACF Security Server

I have written a multi-workstation client/server security system using MVS RACF administration tied together with SNA APPC communications. This was done using entirely in C, utilizing APPC calls, and using no middleware products. This allowed central administration of many OS/2 workstations when no native security administration support was provided by OS/2. The system is being used on dozens of workstations at one of the world's largest financial exchanges.
We later ported this RACF client to the new NT environment, allowing RACF communication between NT applications for the purpose of verifying user security status.

Medical database interface

Wrote a gateway and six different interfaces from a TDS/Technicon medical database system to a variety of external patient care systems. This entailed writing code in COBOL/CICS to process medical data on the mainframe; dispatching these messages in a standard format to a gateway. I developed a message server (from scratch) which ran on an OS/2 workstation. Other client processes were written both to run in Sun Solaris unix/Sybase, DOS and OS/2 systems to communicate with the message server. This allowed the smooth conversion from a mainframe proprietary medical database to a Sybase-based patient database.

I also provided strong technical support for this hospital's initial conversion from DOS-based workstations to OS/2 and NT.

Trading systems

Have maintained and enhanced futures trading systems at the world's largest futures exchanges.

Internet email report routing

Have written a system which allows routing of MVS mainframe-based trading reports to internet email destinations. This was the first such system at this financial exchange, and entailed an interface being built between the exchange's MVS mainframe and their RS/6000 machine.

IEF Composer

IEF Composer has a user exit, known as an External Action Block (EAB), which allows sites to extend the function of Composer-generated applications. I have written (EAB) programs (functions written in C) to extend the capabilities of IEF Composer-generated applications. One of the extensions which I created was a call within a Composer application which allowed the application to call the site's RACF external database manager (which IEF Composer does not do natively).

OS/2 - > NT conversion technical support

Have provided programming support in the conversion of an all-OS/2 shop to an NT shop at the prominent financial exchange.

Native OS/2 programming

I have extended the standard OS/2 Rexx external function library at the financial exchange to provide them with the ability to further automate OS/2 workstation processes. While OS/2 was used at the exchange Rexx was used heavily to maintain the user's workstation environment.

Native word processor

Wrote a native word processing module with word wrap, text scroll, and many other basic features, in Microsoft Basic 7.0, from scratch. This was done for an automobile repair shop merchant system, marketed by a Chicago-area software business, whose entire system is written in Basic.

MVS COBOL batch programming

Have written a variety of batch processes over the last eight years, dealing primarily with the areas of Medicare claims processing, patient care systems, and financial clearing systems, with a good balance of CICS and batch programming. Am highly adept at JCL and MVS utilities such as IDCAMS.

Mainframe document generation

Developed new HIPAA (Healthcare Insurance Portability and Accountability Act) letter generation system for major insurance company, which generates 70,000 - 100,000 letters per year and feed from CNA Federal Markets Enrollee Eligibility system. Cobol, CICS, VSAM, Documerge. Wrote new Documerge VDR as part of this project. Also wrote provider data extract interface for Geoaccess Internet Access project. Completed full cycle of Y2K testing for Year 2000 compliance within CNA for the new HIPAA system.

Technical problems with this page? Contact the webmaster at: webmaster@ebenner.com, call me at 312-520-0090, or page me at 312-457-4315.

Go To Top

Return to eBenner.com Consulting, Inc. Home 

(last updated date: 12/11/2004)
© 2004 by Jeffrey Benner