Please Mike, You're Making Us Blush
Testing, Testing. . .1,2,3, Testing
tRelational Puts on a New Face
TRIM ADASMP Procedure Correction
Quotes from Our Satisfied TSI Customers
When looking for someone to help us spread the word about DPS (Data Propagation System), we didn't have to look any farther than the folks in the Department of Social and Health Services for the State of Washington. Mike Hauser, Computer Information Specialist of the Division of Information Services (DIS), and Tom Johnson, Oracle DBA for the Department of Social and Health Services (DSHS) of the State of Washington, were kind enough to grant Rich Jacobson, Roy Parkinson, and Joe Brady a phone interview about their experiences using DPS. Their comments on Treehouse Software and DPS were, to say the least, very favorable. The questions and answers from the interview follow:
How long ago did the State start talking about data warehousing?
We began in January of 1996. DSHS contacted DIS to assist in looking into data warehousing. At that time, our end users were complaining about system performance. They were finding it difficult getting to the data they entered into the system. They wanted a tool that would help them better access their own data.
What is the application?
The application is called CAMIS (Case And Management Information System). Approximately 2500 - 3000 users enter and access the data on the mainframe, with 700 - 1000 concurrent users. A subset of the data is in the warehouse with activity picking up daily.
The warehouse application has been in production for DSHS since June of 1997. It is in its first of three phases. The first and current phase is the Referral Phase. Data comes in from individuals who are reporting or alleging abuses. These abuses could be against children, and occur in day care centers, foster homes, or group homes. Other abuses could be by welfare recipients. The information is referred to Child Protective Services, Child Welfare Services, and Family Reconciliation Services for follow-up.
Who are the end users?
Supervisors and executive management have access to the warehouse data.
What was the planned hardware and software configuration?
DSHS wanted to install the warehouse on a UNIX server running a relational database management system.
What RDBMS did you choose?
We narrowed our selection down to five database vendors after researching trade journals, viewing various publications, and talking to the Gartner Group. After numerous meetings with these vendors, we trialed ORACLE, Informix, and Red Brick. All three vendors were very good and provided on-site support staff for the trials. Oracle provided six people; Informix, four people; and Red Brick, one person. All three systems met the requirements of the warehouse.
Price was the determining factor and Oracle won.
How would you rate Oracle support?
Oracle provides very good support.
Was there anything significant that Oracle did to help you?
Initially we used NATURAL programs to extract the data from the ADABAS files. In the prototype phase, Oracle tried several new products for data transformation. Oracle told us that if the new products didn't work they would either write the software to do migration/propagation or work with one of their partners to do it.
What did you try for data migration and propagation before DPS, and how did you come to choose DPS?
We looked into Passport and ETI. ETI was too expensive and had a huge learning curve. We chose Passport because we thought it was the only tool available to do some of what we wanted. Passport was a good product that worked pretty much as advertised, but it only did the extraction portion. In hindsight, I think we underestimated extraction, thinking all we had to do was bring in a tool and let it do its thing. But it turned out to be a much bigger issue than we had anticipated.
While we were struggling with data extraction for the warehouse project, I talked to Dave Denbow of Treehouse Software, telling Dave about the problems. Dave mentioned DPS, thinking Treehouse had a solution for me. I felt we were too far along in the project to consider switching to a new product, and I was determined at that point to make what we had work. So that conversation on DPS went nowhere.
Some time later, Dave called again and during the conversation, I repeated my frustrations with extraction. Dave said he still thought DPS was the solution and it would be very worthwhile to take a look at it. Again, I dismissed DPS because it only supported ADABAS. Though ADABAS is 90% of our source information, I saw the potential in the future for this to change. I saw this as a major deficiency in DPS since Passport supported ADABAS and other sources. However, with Passport we were still without propagation and we were looking at writing our own COBOL programs to try to do it.
I told Dave that I would now consider another product. I asked Dave to send me a DPS tape, thinking that I'd at least have it here in case I decided to try it. A couple of days later I received the tape, pulled it out of the box, took a look at it, and saw the installation was very straight forward. To make a long story short, we installed it, and I did a very quick extract from the employee file all on my own. I didn't need any support from Treehouse to get to this point. I just read what was sent to me, and was so impressed with DPS that I started to think seriously about getting it. Remember that Oracle was our selected database vendor, and they were very strong on being able to propagate the warehouse versus rebuilding it all the time.
I took DPS to our technical group for the warehouse and explained that it did extraction and propagation. The technical group decided, although DPS had the limitation of extracting only from ADABAS, ADABAS was, for the most part, what they were going to be extracting data from for the data warehouse. So, with that in mind, along with the propagation feature, the decision was made to go with DPS.
How often did you plan to propagate and how often are you propagating?
Our initial plan was to migrate (a complete build of the warehouse) quarterly and propagate (using protection logs to apply the changes) weekly. We are currently migrating monthly. This is a full extract of data every second Monday of the month. All changes for that week from Monday through Friday at 5:00 p.m. are then applied to the full extract.
Do you have DPS migration and propagation performance times which you are willing to share?
Yes we do. The times were taken from an IBM 9021-982. The partition that DPS ran on was allocated about 300 mips.
DPS migration takes approximately 3 hours to complete. This breaks down to the following:
40 minutes for MXTR, which examines 22.5 million records, extracting 10.5 million from 20 ADABAS files.
80 minutes for LTR, which builds 35 tables for Oracle.
11 minutes for SPLID, to split out the Control and Data files to DASD.
50 minutes to FTP to a Sun Server.
DPS propagation takes approximately 10 minutes to complete. This breaks down to the following:
3 minutes for EXTR, which examines approximately 2.6 million PLOG records, extracting approximately 160,000 PLOG records.
3 minutes for LTR, which builds the SQL statements.
1 minute to FTP.
Is the amount of time it takes to propagate and migrate data using DPS about what you expected? How does it compare with other methods you have tried?
We didn't really know what to expect, but when I ran the jobs, I thought they were fast.
Another thing we like about DPS is that it runs against the PLOGs or backup cartridges. Passport ran against active ADABAS files. Since our computer charges are by ADABAS commands, DPS is actually less expensive for us to use.
I can't compare DPS to Passport because we never got to a point with Passport where it was doing anywhere near what we were doing with DPS.
Do you use any DPS External Transformation Routines? How?
Yes we do. We have routines that were written for us by Treehouse that convert the internal format of NATURAL dates to ORACLE date format.
Are the end users satisfied with the results? Do the results meet or exceed their expectations?
Yes, the end users like the system. They were satisfied enough to generate more than 15 requests for new features in DPS. The more they are using DPS, their expectations are changing and they want more and more features.
Were implementation dates met?
Yes. Spring of 1997 was when we planned to have user access. Final implementation was in June of 1997.
How accurate was the amount budgeted for data warehousing?
We stayed within our budget.
How would you rate Treehouse Software support?
Treehouse support was excellent. I want to acknowledge here that Dave Denbow was a great asset to this project. Dave was very fast with zaps. Most of the time, I would get the zap the same day I called. 99% of the time the zaps corrected the situation the first time with no problems.
Another very nice thing that occurred was the synergy between Oracle and Treehouse in working with us to get the best relational structure for ORACLE, based on what the Oracle consultant saw coming through from DPS.
I'd also like to add that I have talked to two other State departments about DPS. They are interested, and may be calling you in the near future.
Thanks to Mike Hauser and Tom Johnson for taking the time to answer these questions. This information will enable us to continue to improve our products and better serve our customers.
Return to the Table of Contents
Is much of your programming effort spent fixing production bugs? Is your change enhancement list keeping you from building those new applications? It may be that your organization should be spending more time and effort on QA, before the applications get to production.
When is testing complete?
Errors are inevitable when humans create software. Humans make mistakes caused by the limits of their short-term memory. When we get too much on our minds, we forget to test things. If no errors are found during testing, it is often assumed that there are no errors in the software. However, it is more likely that testing was insufficient or incomplete. Many testers feel they know what to test just as a matter of skill or experience. Research studies show that this approach may result in very low levels of test coverage. Testing strategy cannot be based on experience alone.
We all know that a bug found during unit or system testing can be fixed much more quickly than one found during QA testing or after production release. A methodical and comprehensive testing approach should be taken as early as possible during any application change or enhancement and should be maintained during the whole system life cycle. This approach will reduce system costs in the long run and will improve the stability and quality of your production systems.
How much testing is enough?
Testing is a qualitative process that has quantitative attributes. It is important to plan during the testing process and to design good tests. Appropriate measures should be established early in the testing process. The Institute of Electrical and Electronic Engineers (IEEE) developed a standard requiring that test exercises be run for every instruction in a program at least once. This is referred to as 100% statement testing.
The most common testing measure is the one that requires that every segment in a program be exercised at least once. Another common requirement is that every decisional outcome should be exercised at least once and every condition should be evaluated at least once for each possible value and outcome. Loop processing within a program should generally be tested for a specified number of iterations.
Given the size of current application systems and the speed with which they are developed, it is inconceivable that some of these measurements could be made manually during the testing process. Some form of testing automation or automatic measurement of testing is required if test coverage is to be quantified.
Why is testing often postponed or abandoned?
Testing is usually tedious and time-consuming. It is very often put off until the last minute or else not done at all. There is natural tendency to avoid testing, since we are looking for something we don't want to find. By looking for defects and finding them, our confidence is destroyed. The paradox of software testing is that the best way to build confidence in the software is to try to destroy it.
NATURAL allows software to be developed so quickly that system testing and QA must be performed better and faster than ever before. The heavy workload for QA personnel means testing can become a bottleneck in the systems life cycle. Testing tools are often used to maximize testing coverage within available personnel resources. One such tool is a run-time "profiler", a tool that captures the run-time behavior of a program.
What is a "profiler"?
As with every other aspect of information technology, profiling has evolved over time. First generation profiling (1GP) is equivalent to embedding 'WRITE' statements into relevant parts of a program. Second generation profilers (2GP) are often packaged into more manageable tools which insert 'probes' into executable programs. Third generation profilers (3GP) determine the actual statements that are executed by interfacing with the operating environment. Source code and executable code are not changed. 3GPs are often packaged as tools with sophisticated management and reporting functions. 3GPs constitute a more general and more complete solution than earlier approaches.
Will a testing tool help?
A comprehensive testing program clearly requires that some form of automation be introduced into the testing cycle. Conducting tests without automated tools is like finding your way around your house blindfolded. You know where you need to go, but to get there involves a lot of stumbling around. And the next time, you will probably forget the exact path you took the last time.
The task of testing is at best viewed as an onerous ordeal and is often taken as punishment. Only a few psychologically imbalanced people actually enjoy it (just kidding!). To be effective, quality assurance and testing techniques must be used consistently in an organization. The earlier your testing begins in the systems life cycle, the better the quality of your production systems will be. The best way to encourage your application developers to test thoroughly is to make the task as easy and friendly as possible.
Your developers can use a third generation profiler for simple debugging. They will become familiar with the 3GP's sophisticated management and reporting functions, and they will see how it can automate and quantify their existing unit and system testing activity. Using the 3GP, they will find more of the bugs before the QA team gets involved, and they will fix the bugs faster because of the reduced time delay. Before too long, the QA workload will drop, production system quality will improve, and a significant amount of time, effort, money, and reputation will be saved. So what's not to like?
Read on for information on a QA tool that can help.
Return to the Table of Contents
TSI is finalizing Version 4.0.0 of PROFILER for NATURAL, with a planned release date of June 1998. PROFILER can be used on NATURAL applications for quality assurance testing, application testing and debugging, and performance analysis.
PROFILER allows you to collect statistics about which statements of NATURAL programming objects are executed. Collection of statistics can be limited to just one object on one library for one user, or can be as general as all objects on all libraries for all users, or any other variation. Statistics can be collected just once, repeated at different times, or collected continuously over an extended period of time. Execution statistics can be shown in aggregate, or by object, library, and user, or by other variations. PROFILER is flexible enough to find a problem statement in one object, or collect QA statistics for many applications over time. In summary, PROFILER is a very powerful, adaptable tool for collecting execution statistics about NATURAL applications.
PROFILER Version 4.0.0 has been revised to give full Year 2000 compliance (when you are using the Year 2000 compliant releases of ADABAS and NATURAL) and is Year 2000 compatible with earlier versions of ADABAS and NATURAL. PROFILER Version 4.0.0 will also be compatible with ADABAS V6 and NATURAL 2.3.
A New User Interface
The PROFILER user interface has been revised with Version 4.0.0. A "session list" and "action list" style of interface has been developed.
PROFILER organizes statistics collection into "sessions". You can define a new session or copy an existing session. A session has a name (and an optional description) and records which objects and libraries will have their execution statistically monitored. Some other "actions" applicable to a session include DIsplay (… definition details), MOdify (… definition details), ACtivate (begin collecting statistics), DeActivate (stop collecting statistics), STatistics (report on statistics collected), and PUrge (delete statistics).
There are two types of sessions: "profile" sessions and "trace" sessions. A "profile" session can be used many times by many users and can be used to gather execution statistics. A "trace" session can only be used once by one user and only shows the actual statements executed (in sequential order) while the session is active.
With PROFILER Version 4.0.0, "trace" sessions have a name (and an optional description). Previously, trace sessions were only identified by a number and had no associated description. Trace sessions can be listed on the same "session list" with Profile sessions, or you can choose to view only Trace sessions or only Profile sessions. You can also choose to view sessions owned by a certain user and/or sessions starting from a certain point.
New Profile Report Features
For PROFILER Version 4.0.0, the on-line report for Profile sessions has been revised. Additional "summary" report formats allow comparison of counts, percentages, and percentage graphs.
In the "object listing" report, up to 20 executed statements can now be shown on one screen, and the first 45 characters of these statements are shown. (Previously only 10 statements of 29 characters were displayed.) New functions "scan for text" and "show executed / un-executed / non-executable" have also been added.
New Trace Report Features
For PROFILER Version 4.0.0, the on-line report for Trace sessions has been revised. Up to 20 executed statements can now be shown on one screen, and the first 72 characters of these statements are shown (previously only 10 statements of 41 characters were displayed). A "scan for text" function has also been added.
Contact TSI For More Information
If you would like to receive more information about PROFILER for NATURAL, or a free trial, contact TSI.
Return to the Table of Contents
tRelational Puts on a New Face
A Preview of the tRelational V3.0.0 GUI Prototype
by Mindy Sawyer
TSI developers are in the midst of an extensive effort to evaluate various technological platforms by which a Graphical User Interface (GUI) can be developed for tRelational, as well as other TSI products.
tRelational is an ADABAS C data analysis tool which analyzes ADABAS C file structures, data, and usage, to aid in improving design and performance of ADABAS C databases. tRelational also assists with logical modeling and mapping of ADABAS C data into a relational format for use with a relational database management system (RDBMS). tRelational supports the TSI Data Propagation System (DPS) by generating all necessary parameters and mappings required for materializing and propagating ADABAS C data.
The tRelational development team has been busy developing a Version 3.0.0 GUI prototype. The prototype, due for completion during first quarter of 1998, includes Java and Visual C++ Application interfaces developed in the Windows environment. The thrust of the prototype is to provide a "rich" graphical environment which allows the user to build complex diagrams using drag-and-drop.
The NATURAL based tRelational product is being restructured to include interface, processing, and database access "layers". Object oriented programming standards are being used to develop these "layers". The interface layer is being written for the mainframe and client platforms, with the processing and database access layers to be "shared" by both client and mainframe implementations.
The prototype includes a processing logic (PL) layer implemented as "stubs" on the client that sends call requests to a server on the mainframe. This server will make remote procedure calls (RPCs) to a NATURAL PL layer program, which, in turn, calls one or more programs at the Database Access (DA) layer.
By providing a new mainframe interface and a GUI for tRelational, we hope to better serve the needs of all of our customers. The product is planned for release by the end of the year. Future releases of other TSI products are planned with similar technological implementations.
Following is a sneak peek at what the tRelational GUI prototype will look like. The sample provided here shows how an ADABAS file layout is used to create a graphical representation of a Relational Database Management System (RDBMS) Data Model.
The user will have some means of indicating which attributes are primary and foreign keys. The tool bar includes buttons labeled 'Define Primary Key' and 'Define Foreign Key'. When pressed, these buttons allow primary and foreign key attributes to be designated.
The primary key components are prefixed with a 'P' while foreign key components are designated with an 'F'. In our example above, the ADDRESS_LINE_MU entity has a primary key consisting of GOVT_ID, DEPT-ID, INDIV_ID, ADDRESS-LINE, and ROW_COUNT.
Highlighting an 'implemented' field on the left side of the window will automatically highlight any attribute on the right side of the window that was derived from that field.
Watch for the next issue of TREETIPS which will include the findings of TSI's evaluation effort and more details on the new tRelational V3.0.0.
Return to the Table of Contents
TSI will soon announce the general availability of DELPHI 2.4.0, the latest release of the Treehouse Oracle for OS/390 Performance Monitor. DELPHI 2.4.0 will be automatically shipped to all customers with current maintenance. DELPHI 2.4.0 contains all zaps and has improved installation scripts for the Data Collector. DELPHI 2.4.0 also contains the following new features:
Shared Pool Displays
To avoid fragmentation in the Oracle Shared Pool, large PL/SQL objects (packages, procedures, triggers, etc.) should be pinned. The procedure DBMS_SHARED_POOL.KEEP() is used to mark objects as kept, which is referred to as pinning. The DELPHI Shared Pool Displays, which are new in 2.4.0, allow the DBA to identify candidate objects for pinning, identify objects that are pinned, and monitor Oracle Shared Pool usage to ensure that the Shared Pool is large enough.
Rollback Segment Displays
The DELPHI Rollback Segment Displays have been enhanced for 2.4.0. In addition to showing rollback segment activity, new displays show rollback segment sizing and active transactions. For each rollback segment the Rollback Segment Sizing Display shows, the tablespace in which it resides, initial, next and max extents, the optimal size, and status (online or offline). The Rollback Segment Active Transactions Display shows all active transactions for each rollback segment, including the owner.
Data Collector ROLLBACKSEG parameter
A new parameter, ROLLBACKSEG=rollback segment name, has been added to the DELPHI Data Collector to allow the DBA to identify a rollback segment to be used for Data Collector transactions. Oracle allocates rollback segments to transactions (units of work between COMMITs) on a round-robin basis from all rollback segments currently active. Because transactions in the Data Collector may be long, the assignment of a rollback segment intended for long running transactions will minimize the impact of the Data Collector on on-line transactions, which are typically short.
Year 2000 Compliance
All dates in Delphi 2.4.0 contain the century in the year. All date math operations consider the century in the year as well.
Return to the Table of Contents
The January, 1998 TREETIPS Issue #24 contained an article on TRIM and ADASMP entitled TRIM ADASMP Procedure. This article outlined a method for directing the TRIM RTM to a selected ADASMP Nucleus by issuing operator commands to ADASMP which would isolate the Nucleus that the RTM was to be directed to. The procedure to isolate NUC02 found on page 11, created the condition for response code 148 for anyone attempting to issue ADABAS commands. This is due to having closed all Nuclei. The procedure should have issued the two operator commands:
/F ADASMI,CL01
/F ADASMI,OP02
in reverse order:
/F ADASMI,OP02
/F ADASMI,CL01
This would always leave at least one Nucleus open to process commands.
Return to the Table of Contents
New Equipment at TSI
TSI is pleased to announce that we have installed a new IBM Multiprise 2003 server. This server replaces our previous mainframe system (IBM 9370), which has served us well. This upgrade includes converting from VM to OS/390. In addition to the changes to our production processing, we are also installing the latest releases of ADABAS, NATURAL, and PREDICT to run under OS/390.
TSI is a member of the IBM Developers Association and, as such, has access to the latest versions of OS/390, as well as information on IBM's directions for OS/390. It is our intent to continue to work with IBM to maintain our products so they work in the OS/390 environment. As a current customer of Software AG, we will continue to increase our participation in SAGGROUP at both the local and North American levels.
TSI Visits International Affiliates
by Lori Falbo
The early days of February found TSI staff members visiting and working with many of our international affiliates. The folks at MaK DATA SYSTEM in Kiel, Germany hosted a visit by Lori Falbo, Manager of External Relations; Amber Ray, Marketing Coordinator; Ed Wolfe, Product Development; and Karen Dunlap, Product Development. While Ed and Karen spent the majority of their time testing TSI products on the latest versions of NATURAL, ADABAS, and PREDICT, Lori and Amber had the opportunity to learn a great deal about the continued growth at MaK and the current SAG market in Germany. We would like to thank Martin Lochte-Holtgreven, Jorg Dunkel, Eggert Gotthard, Hans-Peter Will, and Matthias Werner for their gracious hospitality and earnest preparation for our visit!
Lori and Amber then hopped a train to beautiful Brussels, Belgium. Thanks to the efforts of Leo Van Dongen and Carine Goosenarts, we spent two very productive days with our friends at Metastore in Antwerpe. While much time was spent discussing the SAG market in the Benelux area, Leo did make arrangements for us to visit with a long-time TSI/Metastore customer, Kas Associatie N.V. in Amsterdam.
Gerard Brul, DBA, and others at Kas met with Leo, Lori and Amber to discuss a variety of issues including their current use of TSI products, their Y2K activity, and preliminary interest in other TSI solutions. Kas Associatie currently uses TRIM V6.1.0 and PROFILER V3.2.0. The TRIM monitor, running in all four databases, generates statistics that are used to compile spreadsheets on ADABAS I/O response, CPU usage versus Duration, and Calls versus I/O. In addition, Kas Associatie has activated the tRelational logging with TRIM, thus making it very easy for them to determine which descriptors on files are unnecessary because they are never used.
Kas Associatie's development department of 40 individuals (designers, programmers, DBAs, etc.) is divided into five groups which consist of four development groups and one support group. Of the four development groups, PROFILER is actively used in two. One group limits PROFILER's use to the testing phase, while the second development group is not permitted to put a program into production when it has not been tested with PROFILER. Gerard's goal is to one day have all developers using PROFILER in the latter capacity. Gerard states, "I think it's a wonderful product and you should use it whenever possible."
After a wonderful stay in Belgium, Lori and Amber then traveled to Paris, France to visit with our friends at Fairware. Although Didier Dupeux and Hilario Medina were busy packing the office and planning for their recent move to larger quarters, they were gracious hosts and were very well prepared to discuss current business opportunities within the French market.
Treehouse would like to thank the affiliates who made our travels to Europe as productive and enjoyable as possible. We sincerely hope to have the opportunity to visit with more of our international business partners in the coming months.
New Faces in Our Growing Family
TSI is pleased to welcome Antonette Roppo and Scott Katkin to our staff. Antonette is part of the TSI development team and Scott is in charge of our computer operations.
Return to the Table of Contents
TSI Customers
Thanks Support Team
"I've found the folks at the Treehouse help desk quite responsive.
I was able to get specialty diagnostic programs e-mailed directly to me and return the results for analysis the same day."
ADAREORG is the Tool
"Treehouse has an excellent solution-ADAREORG is the tool, allows you to do just about any reformatting of a file that you may want."
"It has been a life saver many times in the recent development of a massive new application."
Return to the Table of Contents