DB2 FAMILY NEWS SEPTEMBER 1996 .pa Integrated DB2 Support Now Available for OnCmd xBase for OS/2 Your corporate mandate is to make the most of industrial-strength DB2 for all applications. The problem? Youþve got legacy departmental applications implemented in dBASE, Fox, or Clipper. Some users still insist on implementing ad hoc applications that may not offer the best protection for corporate data. Or perhaps your DB2 databases are distributed on multiple dissimilar platforms. If this describes you, check out OnCmd xBase for OS/2, a veteran OS/2 application development environment from On-Line Data, which has now been enhanced to support SQL in DB2 via ODBC connectivity. OnCmd protects your xBase investment in both legacy code and programmer skills. You can migrate existing xBase source code from DOS/Windows environments to native OS/2 applications, or develop or prototype new applications using OnCmdþs 500-plus functions and commands. OnCmd is a powerful, modular programming language combining high-level batch-oriented simplicity with low-level, high-performance, event-driven control constructs like interrupts, codeblocks, and pointers. Interactive utilities include an application design/builder, report writer, project manager, source debugger, and a migration utility for Fox/dBASE/Clipper. The main metaphor to understand is that an xBase database opened by a þUSEþ statement is directly mapped onto a DB2 cursor. Reading and updating fields are automatically translated into the xBase syntax with no changes. The xBase SKIP is mapped onto the SQL FETCH NEXT call. If you require positioning to a row, a WHERE clause would be added to the USE statement instead of using the xBase SEEK command. You can also embed native SQL functions. One use of this would be to create a DB2 þVIEWþ that you would subsequently þUSEþ as a þpseudo-xBaseþ table. How to Migrate Legacy Applications Your migration path for an existing xBase application would typically proceed as follows. Copy and reindex your xBase þ.dbfþ databases. Compile existing xBase þ.prgþ program source. Then analyze the errors. (Many common errors can be eliminated with OnCmd þMIGRATEþ utility.) Correct, recompile, and test the application; the interactive source debugger can be invoked to assist. At this point, your old þtext-modeþ applications automatically have a Presentation Manager look and feel, including mouse navigation. Next, enable your applications for DB2 by simply addin SQLConnect statement(s) to get a handle to your engine(s), and modify the USE statement. You will then code the required workarounds if your application uses index expressions or dynamic row positioning. Invoking COMMIT and ROLLBACK commands at the appropriate point would be an obvious enhancement to the integrity of your xBase application. You may then apply OS/2 enhancements such as buttons and DDE inherent to OnCmd. New Development or Prototyping New code can be created from scratch, or built with the assistance of the Design/Build and Project Manager utilities. OnCmd allows you to fully utilize PM controls (for example, buttons/sliders/listboxes), colors, and fonts. You can enable your application to use OS/2 multimedia, Rexx, Pen, or even Voice recognition, which are supported as native OnCmd function calls. With built-in named pipe and TCP/IP support, an xBase programmer can write client/server intranet or internet applications. Retrieve or post a web page, or use the þnetþ as your client/server backbone. Use built-in DDE and SOM support to exchange data with OS/2 or WINOS2 applications. Call functions written in OnCmd, Rexx, or SQL in the same program. If that is not extensible enough, call external functions in a DLL utilizing any OS/2 supported language. Get the Benefits Now! The benefits of DB2, speed, client/server, multiuser, and full Presentation Manager Support are now available to xBase programmers. Runtime and Server licensing is available. Full details are available in a demo disk that is downloadable from the On-Line Data web site. Written by John Chambers, Vice President R&D, On-Line Data .pa How to Avoid Overhead at OPEN CURSOR in DB2 for MVS: Answer to Every Question How can I keep DB2 from building a results table at OPEN CURSOR? Answer 1: Often, application logic requires only one of many qualifying rows. The programmer codes a DECLARE CURSOR, an OPEN CURSOR, and then FETCHes just one of the many rows qualified by the WHERE clause. An example of this is an application that needs the most current purchase order for a particular customer. The SQL reads RWHERE CUSTOMER_NBR = :HV ORDER BY ENTRY_DATE DESC. The first FETCH returns an order with the most current date. However, the customer has thousands of purchase orders on the TABLE, and the response time is much longer than you would like. You want DB2 to find and read just the most current row, not the entire results table. Letþs start at the beginning. DB2 does NOT build a results table at OPEN CURSOR. If DB2 is qualifying thousands of rows at the OPEN, the reason is that the access path chosen by the OPTIMIZER involves a sort. Running EXPLAIN reveals that DB2 is using an index on CUSTOMER_NBR, PO_NBR and doing a sort to satisfy the ORDER BY AN ENTRY_DATE. DB2 is not building a þresultsþ table at OPEN CURSOR. DB2 is creating a SORTOUT file at OPEN CURSOR so that, at first FETCH, the data will be in DESCending order by ENTRY_DATE, allowing you to FETCH the rows in the order you requested. The real problem is the sort. So why donþt we change the question to: How can I avoid a sort at OPEN CURSOR? Answer 2: This is a little more complicated. The answer depends upon the SQL that you have coded, the indexes on the table and the statistics in the catalog. Letþs look at the SQL in Question 1 and consider solutions. If we create an index on CUSTOMER_NBR, ORDER_DATE DESCENDING, then the optimizer would have the option of choosing that index to avoid the sort. No sortout file would be built at OPEN, and the first FETCH would find the row you want with minimal overhead. But what if you run EXPLAIN and DB2 has ignored the new index and is still using the old index and sorting? This leads us to Question 3: Why wonþt DB2 use an index that would avoid a sort at OPEN CURSOR? Answer: The index was probably rejected because of a low CLUSTERRATIO; that is, the data in the table is in a different sequence than the order of the index. The optimizer decided that the path using your preferred index is more costly than the path that uses the other index nd then sorts. You may be able to change the optimizerþs mind by adding the clause þOPTIMIZE FOR 1 ROWþ to your SQL. This tells DB2 that you only plan to read one row and encourages DB2 to pick the best path for finding that one, first row. This would make your new index much more attractive. If the OPTIMIZE clause is not supported by your coding language and you need just the row(s) with the largest order date, an alternative is to code the SQL as follows: WHERE CUSTOMER_NBR = :HV AND ORDER_DATE = (SELECT VALUE(MAX (ORDER_DATE), DATE(R0001-01-01S)) FROM CUSTOMER_ORDER WHERE CUSTOMER_NBR = :HV). Of course, if you have Version 4 Release 1 installed, you may substitute the SQL standard keyword COALESCE for the keyword VALUE. The inner SELECT quickly finds the maximum date using your new index, and the VALUE/COALESCE clause allows the outer SELECT to match on both columns of the new index to quickly find the qualifying row(s). You test this code and you now have subsecond response time, but other critical applications are slower because of the overhead of maintaining your new index. This leads us to our final question: Can I get the response time I need without adding a new index? Answer: Maybe. Try the SQL coded in the question above. To satisfy the inner SELECT, DB2 could use the old index to read the customerþs rows and find the maximum date. To satisfy the outer SELECT, DB2 would then read through the customerþs rows again to find the additional column detail for the row(s) with that maximum date. However it is very likely that the pages needed would still be in the BUFFERPOOL, requiring no additional reads to DASD. This access path may give your application the response time needed without the overhead of another index. You can also learn more about reducing SORT overhead and improving SQL by enrolling in a DB2 system performance course. The DB2 performance curriculum includes such standards as: DB2 for MVS System Performance & Tuning (U4077) and DB2 for MVS Advanced Recovery Workshop (U4074) ...plus a selection of new courses including: DB2 for MVS SQL Performance & Tuning (CF980), DB2 for MVS Locking for Application Developers (CF970) and DB2 for MVS Application Database Recovery Workshop (CF980). by Bonnie Baker, DB2 Services Specialist, IBM. .pa IBM delivers new DB2 products, faster and better than ever before I hope that you had a good summer and a chance to take some time off to enjoy it. Summer in Toronto never seems long enough, but it's truly a beautiful time of the year. It's also been a very busy time for the DB2 team as we continue to deliver products at an accelerated pace. Over the last few months, we delivered DB2 Version 2.1.1 on SCO OpenServer, the Integrated DB Server featuring DB2 2.1.1 for Windows NT, new releases of DataJoiner, DataPropagator, and DB2 WWW Connect on a number of platforms. We delivered the initial version of DataBasic, providing you with the capability of writing DB2 Stored Procedures and User-Defined Functions in Visual Basic. We are distributing DataBasic on the Web, so check it out. In June, we announced and shipped DB2 Parallel Edition Version 1.2 with improved performance, enhanced function, and a set of monitoring and administration tools. The number of DB2 Parallel Edition customers continues to grow as we meet customer requirements for large scale data-warehousing and data-mining solutions. In June, IBM was the first to deliver integrated access and content-based search capabilities for traditional business data, combined with text, image, video, and audio with the DB2 Multi-Media Extenders. IBM was the first to deliver Object-Relational extensions in DB2 Version 2 last July, and we are taking advantage of these extensions, along with sophisticated content-based search capabilities, to bring true integration to you. The value of this is not in the storing of these complex data types, but in the integrated content-based search capability. The Multi-Media Extenders are available with the DB2 Cross Platform Developers Kit at no additional charge to you. On July 19, the DB2 Family went to the 1996 Atlanta Olympics. These Olympic Games were run on IBM hardware and software, and DB2 was used throughout _ from the results systems, where DB2 for OS/2 was used at the sports venues, to the Commentator and Information Systems, where DB2 for MVS and DB2 for AS/400 were used. It was an exciting time for all of us as we watched DB2 go live around the world in perhaps the most visible mission-critical event ever. Last month we began using DB2 on AIX with the Text Extender and DB2 WWW Connect to better communicate with all of you on the internet. We are making all of our technical publications libraries, as well as other technical support information, available on the Internet under DB2 Product and Service Technical Library. I hope that you will check it out and give us some feedback. Using DB2 for this application gives us a great ability to analyze your comments and interests faster and more easily, thus helping us to better respond to your needs. Once again, thank you for your commitment to the DB2 Family. We couldn't do it without you. Janet Find the DB2 Product and Service Technical Library at http://www.software.ibm.com/datadb2 The web site for DataBasic is http://www.software.ibm.com/ad/vabasic/vabasic.htm .pa 1996 IDUG AWARD FOR INFORMATION EXCELLENCE And the winner is ... Mr. Anthony Flora, Senior Vice President, Technical Services Group, NationsBanc Services, Inc. in Charlotte, NC. NationsBanc Services provides the data processing required by all the NationsBank locations throughout the United States. NationsBank Corporation is the fifth largest financial institution in the United States. In an awards ceremony held at the recent IDUG Conference in Dallas, PLATINUM technology, inc. and KPMG Peat Marwick presented Anthony Flora with the IDUG Award for Information Excellence. Of particular note are his achievements at NationsBank within the Relational Database Management Services Group. Flora implemented DB2, Sybase, and Oracle databases throughout NationsBank, supporting over 85 OLTP, DSS, and OLAP systems. His implementation also supports six DB2 version subsystems and over 70 host/SQL gateways providing client/server access to both legacy and relational data to all NationsBank branches. Runners Up * Ms. Kathy Lennon, director of Data and Technology for Source Informatics/PMSI, Phoenix, AZ * Mr. Al Stambaugh, senior DBA at Baltimore Gas and Electric, Baltimore, MD .pa IDUG is Meeting your Changing Needs The International DB2 Users Group (IDUG) reaches a wide community of DB2 DBAs in the MVS environment all over the world. In fact, the growing DB2 Product Family has reached a level that makes the management of heterogeneous environments possible and feasible. You can learn more about the DB2 Product Family and the management of heterogeneous environments when you attend the IDUG 5th Annual European Conference, October 21 to 24, 1996, at the RAI Congress Center in Amsterdam, The Netherlands. Through its annual conferences on three continents, IDUG helps users tackle technical problems and find manageable solutions for their business requirements. By opening IDUG for discussions about cross-platform connectivity, we are giving you the opportunity to review established solutions and reposition yourselves for the new engines the DB2 Common Servers have to offer. More than 40 technical sessions will be presented by DB2 experts and IBM developers at the IDUG Conference in Amsterdam. Some of this year's topics include: * DB2 and the World Wide Web * Common Server * Data Warehousing * Migrating to DB2 Version 4 * SQL Enhancements IDUG's keynote presentations are delivered by highly regarded DB2 professionals on subjects that are at the forefront of the DB2 industry. Colin White of DataBase Associates will present Turning Data into Information: Exploiting the Power of the Data Warehouse; Steve Mills of IBM Software Solutions will present Network-Centric Computing: A New Vision for Client/Server; and Craig Mullins of PLATINUM technology, inc. will present Database Management 2001: Objects, Fuzziness, Relational, and More. IDUG also offers several opportunities for you to network with the other attendees, including Open Forum Discussion Groups and the popular DB2 Experience and IBM Panel Question & Answer Sessions. The panel members of the Question & Answer Sessions discuss questions that are submitted by conference attendees, offering their insights and expertise on DB2. With the expansion of IDUG's vendor exhibition and vendor-sponsored presentations to cover heterogeneous environments, you'll see solutions that work with DB2 as well as other database engines. Nowhere else in Europe can you meet all of the industry's leading vendors, view live product demonstrations, and learn about the newest tools and solutions for the DB2 Product Family. The IDUG exhibition gives you a chance to meet the vendors one-on-one and carefully analyze the new products and services that will help you manage the Information Revolution with DB2. Through vendor-sponsored presentations, you can also learn what the vendors are planning for the coming months and years to support the DB2 Product Family. Finally, you can round out your DB2 education by attending a comprehensive one-day seminar on Friday, October 25th. These seminars are designed to provide detailed information on the topics that provide the greatest challenges to DB2 users today. This year's one-day seminars include Bonnie Baker of IBM Corporation presenting DB2 EXPLAIN Explained; Joyce Bischoff of Bischoff Consulting, Inc. presenting Building a Data Warehouse; and Bjarne Nelson, Independent Consultant, presenting DB2 Version 4.1 -- The Biggest Release Ever Since 1.1! Let's go distributed, let's go object oriented, let's go WWW and multimedia! It's a complex goal, but you can find all of these solutions at the IDUG 5th Annual European Conference. For conference registration and information call 312-644-6610 or e-mail: 72410.531@compuserve.com. For your convenience, all conference materials are available on our Web home page: www.idug.org. .pa DataJoiner Announces... DataJoiner for AIX Version 1.2 and Support for HP-UX With DataJoiner, you can access and join data from different data sources with a single Structured Query Language (SQL) statement and a single interface. That single interface hides all the differences you struggle with when accessing information from different vendor databases on different vendor platforms. With DataJoiner, there is one interface to one database image_making it appear as if you had accessed a single database. DataJoiner for AIX Announces Native Support for IMS and VSAM Now with DataJoiner for AIX Version 1.2, you can access this single database with even more application platforms like MVS, VM, and OS/400. How? DataJoiner for AIX now can be a Distributed Relational Database Architecture (DRDA) Application Server. So your CICS applications or other DRDA Application Requestor applications can have access to heterogeneous data via DataJoiner. In addition, your single connected database can be even more connected because DataJoiner can natively access IMS and VSAM data on MVS via the new DataJoiner Classic Connect. And DataJoiner for AIX V1.2 can now natively access and support relational data from Informix. DataJoiner for AIX continues to support an extensive list of client platforms including Windows, OS/2, Macintosh, AIX, HP-UX, and the Solaris Operating Environment. Supported data sources include the DB2 family, Oracle, Sybase, and Microsoft SQL Server. DataJoiner Announces Support for HP-UX DataJoiner now supports HP-UX Version 1.1 as its second server platform. (AIX Version 1.1 was the first server platform.) DataJoiner for HP-UX Version runs on a server with the HP-UX operating system and supports an extensive list of clients and data sources. Supported clients include AIX, OS/2, DOS, Windows/95, Solaris Operating Environment, and HP-UX. Supported data sources include the DB2 family, Oracle, Sybase, IMS, VSAM, and Microsoft SQL Server. Written by Susan Fisher, Database Management Solutions, IBM Santa Teresa Laboratory. She can be reached at (408) 463-4667 or by e-mail at sfisher@vnet.ibm.com. Written by Jacqueline Bloemen, PASS Consulting, and Detlef Felser, UFD GmbH, IDUG European Conference Planning Committee Members. .pa DB2 for MVS/ESA Version 4 Functions Two significant enhancements are now available for DB2 for MVS/ESA Version 4.1 and can be obtained for immediate use. You do not have to wait for DB2 for MVS/ESA Version 4 Release 2 to take advantage of these two new enhancements. 1. New Object Language Support New application development and stored procedures options are now available with the added support of object-oriented languages COBOL and C++. This means precompiler support for these two languages and the ability to use them to write stored procedures. 2. Availability Enhancement A new locking isolation level called "Read Stability (RS)". This is in addition to repeatable read (RR), cursor stability (CS) and uncommitted read (UR). This new isolation level offers much of the control of repeatable read but with greater concurrency. Read stability allows other application processes to insert rows into an answer set before the original application commits or rolls back its work. These new functions are delivered by the following APARS: PN78797 - for object-oriented languages (COBOL and C++) PN75407 - for read stability isolation level .pa New DB2 for MVS/ESA Release -- PREVIEW Announcement IBM recently announced DB2 for MVS/ESA Version 4 Release 2 as a Preview Announcement to give insight into IBM plans and direction for this product. General availability, prices, ordering information, and terms and conditions will be provided when the product is formally announced. DB2 for MVS/ESA Version 4 Release 2 provides additional enhancements to make DB2 for MVS/ESA a more flexible client/server database solution that increases both transaction and query processing capacity and provides a scalable solution capable of managing very large databases. It also delivers functions to improve application design decisions and ease DB2 data and system administration. These new enhancements make DB2 for MVS/ESA an even more effective platform for a large data warehouse. The new release delivers more client/server function, more support for open industry standards, improved performance and availability. Other functions give greater user productivity. DB2 for MVS/ESA continues to build its leadership in parallel database technology by enhancing DB2 data sharing functions with S/390 Parallel Sysplex technology. Major enhancements include: Network Computing and Client/Server o Native TCP/IP Support for DRDA for DB2 for MVS/ESA Clients o Support for High-Volume Data Transfer (Asynchronous Transfer Mode) o X/Open and ODBC Call Level Interfaces o Enhanced Stored Procedures (Results Sets, Multiple Address Spaces) o Improved DRDA Performance, ASCII Server Support, Reduced Network Traffic o Distributed Computing Environment (DCE) Security o Internet Access With DB2 WWW Connection for MVS/ESA (new product) Performance, Capacity and Availability o Online Reorganization of Table Spaces and Indexes for Improved Availability o Query Parallelism within a DB2 Data Sharing Environment (Sysplex) o Authorization Caching for Packages o Data Sharing Enhancements o Very Large Table Support (1 Terabyte) o More and Larger Partitions (254 Partitions Per Table, up to 4GB Each) o More Rows Per Page (Maximum is 255) o Utility Performance and Usability Enhancements o Caching Dynamic SQL Statements User Productivity o SQL CASE Expressions o Temporary Tables o Standards Compliance: SQL 92 Entry Level o Workstation-Based Install and Tuning Option o Database Server Support for OS/390 Two related products will be upgraded to support DB2 for MVS/ESA 4.2: o DB2 Estimator for Windows 4.2 o DB2 Performance Monitor for MVS/ESA 4.2 New product for Internet access to DB2 for MVS/ESA data: o DB2 World Wide Web Connection Version 1 for MVS/ESA New documentation: o GK2T-2373 - DB2 for MVS/ESA 4.2 Portfolio (includes): o GC26-8260 - DB2 for MVS/ESA 4.2 Fact Sheet o GC26-8206 - DB2 for MVS/ESA What's New o GC26-8630 - DB2PM for MVS/ESA 4.1 Fact Sheet o GC26-8800 - DB2PM for MVS/ESA 4.2 Fact Sheet o GC26-8631 - DB2 Goes Parallel with Version 4 for MVS/ESA o GC26-8542 - Database Parallelism - Unparalleled Performance o GC26-8808 - DB2 Estimator for Windows 4.2 Fact Sheet o GC26-8752 - DB2 World Wide Web Connection Fact Sheet The IMS Quarterly Newsletter Many IMS customers have asked if there is a way to get more news about IMS development activities and plans on a regular basis. IBM understands how important IMS is to your company and wants to make sure that IMS development communicates with you effectively. We have created a newsletter to give you regular updates on the activities going on in the IMS development and user communities. We intend to have regular news articles about new IMS developments, user experiences, plans and late-breaking news, and other interesting topics. We hope you will find our IMS Newsletter informative and valuable. The IMS Quarterly Newsletter is produced by the IMS development organization at IBM's Santa Teresa Laboratory in San Jose, CA. To subscribe to this publication, please give us your name, title, company and complete address and send it in one of the following ways: Internet: ims@vnet.ibm.com IBM VNET: stlvm6(imsmkt) FAX: (408) 463-4101 Mail: IMS Quarterly c/o IMS Product Manager 555 Bailey Avenue San Jose, CA 95141 We also solicit your input on content or material for future issues. If you would like to get a copy of any of the three previous issues, mention this in your communications to us. .pa Data Replication (DPROPR) Expands Reach to Windows NT Exciting news about Data Replication! The new release of the DB2 Data Replication Starter Kits (DataPropagator Relational) expands the reach of client/server environments by adding new data sources and targets. The Replication Starter Kits provide simplified packaging for data replication in a relational environment. The Starter Kits contain DataPropagator Relational (DPROPR) and the administrative control point component of DataHub. There are Starter Kits available for Intel-based platforms (OS/2 and Windows NT) and UNIX-based platforms (AIX, HP-UX, and the Solaris Operating Environment). Each Replication Starter Kit license includes three components: - The Control Point, which consists of the DataHub for OS/2 Workstation and the administrative component for DataPropagator Relational - One DataPropagator Relational Capture server - One DataPropagator Relational Apply server Multiple Capture and Apply components are available through additional licenses. The latest news about Release 2 For the DB2 Data Replication Starter Kit (DPROPR) for OS/2 and Windows NT: Capture for Windows NT and Apply for Windows NT are now available in addition to Capture for OS/2 and Apply for OS/2. For the DB2 Data Replication Starter Kit (DPROPR) for UNIX Operating Systems: Capture for HP-UX and Capture for the Solaris Operating Environment are now available in addition to Capture for AIX. Apply components for AIX, HP-UX, and Solaris were available in Release 1. The Capture components for both Starter Kits provide log-based change capture, enabling replication source support from DB2 Version 2.1.1 databases for their respective platforms. The Apply components provide replication target support for DB2 Version 2.1.1. databases for their respective platforms. New GUI functions In addition to the new Capture and Apply components, IBM has acted on customer suggestions by adding new GUI functions that give users control over their target tables. Now users have the option to build their own target tables if they choose not to utilize DataPropagator Relational's automatic table-building function. In addition, users can specify whether or not to drop a target table while cancelling a subscription. Just the beginning of a total replication solution DataPropagator Relational Version 1.2.1, packaged as the DB2 Data Replication Starter Kit(s) Version 1.2.1 for easy ordering and simplified pricing, focuses on replication in a relational environment. It is just one part of IBM's Data Replication Solution. When fully configured, the Data Replication Solution delivers replication technology for IMS, VSAM, the DB2 family, and multi-vendor environments. In addition to DataPropagator Relational, the Replication Solution is composed of the following products: - DataPropagator NonRelational (replicates data from IMS to DB2) - DataRefresher (offers full refresh of MVS sources to the DB2 family) - DataJoiner (enables multi-vendor replication) Only DataPropagator Relational is packaged as a Starter Kit. Submitted by Susan Fisher, Database Tools Brand Manager, IBM Santa Teresa Laboratory. She can be reached at (408) 463-4667. .pa Monitoring DB2 for OS/400 Database Performance Do you need help tuning your DB2 for OS/400 database? Do you find yourself asking questions like: * What queries are the most time consuming and which user is running these? * What queries were prevented from running by the query governor? * What queries are creating temporary indexes over the largest table in your database? * What queries could perform faster if indexes suggested by the query optimizer were created? The new 64-bit RISC version of the DB2 for OS/400 database includes a tool to meet those needs -- the Database Monitor. This query performance monitor helps you further analyze the performance and system impacts of queries executing on a system. It also complements existing AS/400 tools that analyze performance at a system-wide level: CPU Utilization, Disk I/O etc. You can use the STRDBMON command to start the collection of database performance statistics for a specific job or all jobs on the system. With this flexibility, you can drill down to a specific user who is impacting database performance. After analyzing the database workload at a system level, you can then just collect statistics on the user's queries instead of all the queries on the system. The ENDDBMON command is used to end collection of the database performance statistics. All the information collected by the database monitor is recorded into a single database table. A single query generating multiple rows in this table is common because the monitor tracks details on table locks, index creations, temporary results, query sorts, and many other items. You can then define various views and joins to produce simple reports for analyzing query performance. Suggested views are defined in the performance section of the DB2 for OS/400 Database Programming (SC41-4701) and DB2 for OS/400 SQL Programming (SC41-4611) manuals. Submitted by Kent Milligan, DB2/400 Development, IBM Rochester, AS/400 Division. He can be reached by phone at (507) 253-5301 or by e-mail at kmill@rchland. .pa Smart Database Administration with SQL Master SQL Master, IBM's database administration and automation tool for DB2/VM databases, has been helping DBAs for over six years. It simplifies database administration; reduces the amount of time required; implements consistent, repeatable processes, reducing errors in job preparation and execution; and augments your already overworked DBA staff. In March of this year, SQL Master for VSE became available. Now DB2/VSE shops have no excuse not to begin saving time and money by implementing smart database administration with SQL Master. Why Do You Need SQL Master? * DBA's life is anything but dull. It's a combination of business systems analysis, application development and systems programming. When there is only one DBA in the shop, it's extra challenging. * DBA's work is never done. During the day, you support online applications and query users. At night, you support batch applications. Weekends you spend generating databases or doing reorganizations. But DBAs also need to spend time designing databases, tuning applications, and supporting end users. They need SQL Master to help them make their databases available, efficient, recoverable, and secure. In this article, we will look at four specific ways SQL Master can help. DDL Generation DDL can be quite complex. A single DBSPACE can contain several tables, each with associated primary keys, foreign keys, comments, indexes, grants, and views. To recreate a dropped DBSPACE, you need DDL for all of these objects. * DDL can be tricky You can't just code it in any order. To properly recreate authorizations, you must grant them in the original sequence by the IDs who originally granted them. But what if those IDs are gone? There's lots of stuff to remember, too. Like altering PCTFREE after a reload so that updates and inserts can take advantage of free space. And rebinding packages that have been marked invalid because a DBSPACE they were dependent on was dropped. * DDL can be lengthy Want to hone your typing skills? Enter the DDL required to define Public.Sample, the DBSPACE that comes with SQL/DS. You'll need at least 44 DDL statements, requiring roughly 100 lines of code. * DDL can be a pain to manage Not only do you have to spell everything right and put it in the right syntax, you have to figure out what to do with it and where to keep it. In VSE, you could put it in an ICCF library. In VM, a minidisk. But how do you name it? ICCF only allows 8-character member names. Ownership is also a problem. Who owns the "official" copy of the DDL? Age is another problem. How do you know the DDL you are looking at is accurate? Maybe someone changed it and forgot to update the DDL in the library. This could be a big problem. * The DDL is in your database All the information needed to recreate it is in the system catalog System.Sysaccess contains information about your packages. System.Sysdbspaces has information about your DBSPACES. System.Sysusage records the relationships between packages and DBSPACES. * Generate your DDL with SQL Master SQL Master generates DDL that is complete, consistent, well documented _ and it works. The SQL Master developers have years of experience as SQL/DS DBAs. They know what makes DDL complete, and that knowledge is built into SQL Master. To generate DDL with SQL Master, select DBSPACE REORGANIZATION from the SQL Master Main Menu (Figure 1). Note that the name of the default or last-used application server is displayed. Enter another name to point to a different application server. To generate DDL, simply enter an OWNER, a DBSPACE name, OPTION 1, and press ENTER from the DBSPACE Reorganization Screen (Figure 2). Set REBIND PACKAGE to 2 if you don't want rebind package statements. Set ALLSTATS to 1 if you want to update ALL statistics. When you press ENTER, the DBSPACE Reorganization Submit Screen is displayed (Figure 3). Enter a jobname and press ENTER to submit the DDL generation job. SQL Master generates complete, accurate, ready-to-run DDL. It generates DDL to recreate a DBSPACE and all of the tables, indexes, views, comments, referential constraints, and authorizations associated with it. All the DDL is placed in the VSE/POWER punch queue. From there, you can import it into a library or clone it to create new tables, indexes, or views. You can use the rebind package statements to see which programs use which objects in the DBSPACE. Figure 4 contains actual DDL generated by SQL Master to recreate the PUBLIC.SQMHELP DBSPACE. This DBSPACE contains help information for SQL Master online transactions. Note the Update Statistics and Rebind Package statements that were generated. DDL Generation Summary DDL can be complex, tricky, lengthy, and a pain to manage. But it's in your database. Work smart _ manage your DDL with SQL Master. DBSPACE Reorganization Periodic reorganization of transaction databases is essential for good performance. Reorganization allows data to be reclustered. Overflow pages can be eliminated and indexes rebuilt. Reorganization also allows for the redistribution of data to reduce hot spots on DASD. Some DBSPACE characteristics such as size and PCTINDEX are fixed when a DBSPACE is acquired. Reorganization allows you to change these values. Reorganization is also a good method for changing a DBSPACE from public to private or moving it from a non-recoverable to a recoverable storage pool. What's the best way to reorganize? A combination of unload, Delete *, and reload is an option. It's easy and your DDL is left intact. The problem is it's expensive, slow, and incomplete. Each delete is logged, and every index is maintained both for the delete and the reload. With this method, you can't change DBSPACE characteristics. Drop Table is another option. It's a little faster than Delete * because associated indexes are dropped. You avoid the overhead of index maintenance. The problem is, it's still slow because all deleted rows are logged. Plus, you've lost all your indexes, views, and comments, and you still can't change DBSPACE characteristics. Drop DBSPACE is the way to reorganize. It's fast because deletes are not logged and shadow pages aren't used. It's efficient because DBSPACE pages are returned to the storage pool, and you can rebuild the entire DBSPACE in the most optimum sequence. The problem is that it's fairly complex, and you must have DBA authority and a complete set of DDL. SQL Master DBSPACE Reorganization This is the smart way to do DBSPACE Reorganizations. Drop DBSPACE makes them run fast. Separate reload table commands maintain clustering sequence. SQL Master also gives you total flexibility. You can change a DBSPACE's name or characteristics. You can move a DBSPACE to a different storage pool or a completely different database. And SQL Master provides safety. Before anything else happens, complete, accurate DDL is generated. Then your data is unloaded to disk or tape. Regardless of the media you choose, a recovery mechanism is always provided in case of disaster. To reorganize a DBSPACE with SQL Master, invoke the DBSPACE Reorganization Utility from the SQL Master Main Menu. Enter an OWNER, and DBSPACE name as shown in Figure 5 and press ENTER. Figure 6 shows the jobstream, submitted by SQL Master, to reorganize the PUBLIC.SQMHELP DBSPACE. Note how complete and well documented each step in the reorganization is. Aren't you glad SQL Master generated this jobstream instead of you having to type it? Reorganization Summary Reorganization is absolutely critical to providing optimum performance in a dynamic database environment. Reorganization can be used to manage DASD utilization and a changing database environment. Reorganize with SQL Master. It's fast, efficient, flexible, safe, and comprehensive. Archiving and Recovery An archive is a copy of a database. A log archive is a copy of active log pages containing database changes. A database archive is a complete copy of the database, including the directory and dbextents. The value of an archive is that it allows you to recover a database at a given point in time. Recovery is the process of rebuilding a database after a failure or loss of data. It requires that at least one archive has been taken. Why Archive? DBAs archive regularly to protect against accidental (or intentional) loss of data. An archive can be used to clone a database or to move to new hardware or software. Archiving is also a great way to avoid having to update your resum! * Archive Considerations There are many variables involved in archiving, including logmode, type, mode, media, and frequency. Logmode: Determines the type of archive that will be initiated and when it will occur. With logmode Y, no implicit archive occurs when the log is full. Instead, the log "wraps" back to the beginning and database processing continues. Logmode A causes a database archive to occur when the log fills. Logmode L causes a log archive to occur when the log fills. WARNING! An unexpected implicit archive can have less than desirable effects when it occurs in the middle of the online day. Type: There are two archive types: SQL/DS archives and user archives. SQL/DS archives are taken with SQL/DS archive facilities. A User archive is taken with a non-SQL/DS facility such as DDR or IDCAMS. Mode: (Database up or down) can be determined by the type of archive you choose. User archiving requires that the database be brought down prior to initiating the archive. For database archives, LUW processing differs depending on database mode. Media: Archive media is a consideration for VM. Disk is faster than tape, as long as you have enough, aren't worried about head crashes, and have a mechanism for assigning and recording the information needed for recovery. If you archive to tape, you need to make sure information describing the archive is physically written on the tape or recorded in your tape management system. SQL Master Archiving and Recovery SQL Master for VM fully supports database and log archiving and recovery. All logmodes are supported as well as any processing required to change logmode. Both SQL/DS and User archive types are supported. SQL Master manages archive invocation with the database up or down and can head off implicit archives initiated by SQL/DS when the log reaches ARCHPCT, by initiating an archive at a time you specify. All tape mount requests and responses to SQL/DS prompts are handled automatically. SQL Master manages log and database archives to disk. It also handles all aspects of tape use, including interfacing with tape managers, tape mounts, scratch tape vs. pre-allocated tapes and tape series rotation. SQL Master maintains tape and disk usage information for reference and recovery. The SQL Master Archiving Menu (Figure 7) provides several functions associated with database archiving. You can initiate an archive immediately or schedule it for later execution. You can choose to view a history of previous archives or a log of the last completed archive. You can also view or modify the tape catalog, acquire scratch tape, and view the job schedule for this database. SQL Master Recovery The first screen you see when you initiate a recovery is the Recovery Selection screen shown in Figure 8. Your current logmode is displayed. Press Enter to see all of the restore sets that are available for this database. Figure 9 shows an example of a restore set report. This report provides the DBA with information about the sets of archive tapes and disks that areavailable for recovery of the database. This facility greatly eases the process of recovering a database from system, application, or user error. Archiving and Recovery Summary An archive is an external copy of your database, usually on tape, but sometimes on disk. Recovery is recreating a database from an external copy. With very few exceptions, every database should be regularly archived. There are many considerations you must take into account when erecting an archiving and recovery strategy. Be smart. Manage your archiving and recovery with SQL Master. It's automatic, expert, and safe. Object Management Objects such as tables, indexes, and views make up a relational database. They are the things DBAs work with every day. Objects are related: Tables are stored in DBSPACES. Indexes are built on tables. Packages are dependent on tables and indexes. Objects have characteristics that change over time. To achieve maximum performance, they require maintenance and the attention of a DBA. To manage an object, you first have to find it. To determine the relationships and dependencies that exist between objects, you need to know where they are used. DBAs end up writing many queries to get this information. Once you decide an object requires maintenance, how do you do it? Many DBAs have no other choice than to code up a batch DBSU job. SQL Master Object Management The SQL Master Object Search and List Tools are an integrated set of applications that query the system catalogs for the information most DBAs are looking for. They alleviate DBAs from having to code, manage, and correctly spell a ton of queries. The Object Search and List Tools allow you to navigate through a database from DBSPACE to table to index to column. This eliminates the need to interpret the relationships that exist between objects in the database. The Object Search and List Tools allow you to execute specific utilities against the objects displayed on your screen. Not only can you look at a list of the tables in a DBSPACE, you can invoke SQL Master jobs to reorganize, drop, or update all statistics for them. The first screen you see when you enter the Object Search and List facility is the DBSPACE List Screen (Figure 10). You can search for DBSPACES by OWNER or DBSPACE name. You can use the wildcard character (%) to select DBSPACES with similar OWNER or DBSPACE names. Specifying characteristics such as pages, type, or storage pool number is another way to select which DBSPACES are displayed. To list all DBSPACES, simply press ENTER. You will see a display like the one shown in Figure 11. Commonly asked for information is displayed for each DBSPACE. A selection field on each line allows you to invoke the utilities listed at the bottom of the screen. PF keys offer additional functionality. PF4 displays maintenance information. You use PF9 through PF12 to sort the display by DBSPACE name, number, pages or storage pool. In addition to DBSPACES, you can use the Object Search and List Tools to display tables, packages, views, and columns. Information about each object type is displayed. Selection fields allow invocation of utilities, and PF keys provide additional functionality. Figure 12 is an example of the View Package information provided by the Object Search and List Tool. This information is extracted from the table in which the package is stored. It contains information such as preprocessing parameters and the actual SQL statements in the package. Every DBA has at one point wished he/she could see this information. Object Management Summary A typical SQL/DS database contains thousands of objects. Many of them are related to many others. Object management requires knowledge of object location and the relationships that exist between them. It requires skill with coding SQL and DBSU commands. Let SQL Master supply the knowledge and skill you need to manage your SQL/DS objects. A Tough Job Made Easier Database administration is a tough job. It requires knowledge, experience, and skill. SQL Master, IBM's SQL/DS administration and automation tool can help. It improves SQL/DS availability, performance, and efficiency, minimizes errors, and frees you to do other things. Smart database administration is easy with SQL Master for VSE & VM! For more information about SQL Master, contact your IBM representative or call IBM Direct at 800-IBM-CALL. Written by Andy Schmersahl, the IBM SQL Master Technical Lead and an Advisory Programmer with Lockheed Martin Federal Systems. He has over 15 years of experience in application development, systems programming, and database administration on VSE and VM. He can be reached at (301) 240-7607 or by e-mail at andys@lfs,loral.com .pa FRANK'S HINTS & TIPS DRDA and DDCS Working for You Picture this: A university that gives students access to their academic records via the World Wide Web. A ticketless airline reservation system. A local government providing access to legacy data. These projects are all currently or soon-to-be underway - and they all use Distributed Relational Database Architecture (DRDA). Let's take a look at each for ideas about how DRDA's enabling technology _ Distributed Database Connection Services (DDCS) _ might be useful in your enterprise. The University "Intranet" is the hottest buzzword in the industry right now. It is the successor to "client/server" and a direct descendent of the "paperless office". We'll have to see if the Intranet model delivers - or goes straight to the "Hype Hall of Fame" along with its predecessors. For now, though, it provides a useful way for one institution of higher learning to reduce costs and enable more responsive customer service. Traditionally, students needed to fill out a form to request grade and transcript information. The request was manually processed by a clerk using a heritage DB2 for MVS mainframe application. This produced a hardcopy listing that had to be picked up in person. The whole loop might take hours or days. It could only occur when university offices were open. And it required a liveware interface (the clerk). The school's MIS staff added a DB2 for OS/2 and DDCS for OS/2 DRDA gateway. They also installed Web server software on the same machine. By imbedding Structured Query Language (SQL) to access the DB2 for MVS data in HyperText Markup Language (HTML) code using the DB2 World Wide Web Connection (DB2 WWW), they created an application that permitted any student with a Web browser to acquire their own academic records from the MVS host. In minutes, day or night, by themselves. User authorization is performed at the target database (DB2 for MVS), so everyone needs a RACF userid and password. The Web server can be configured to only permit the university's internal Internet Protocol (IP) addresses to gain access to the application. This architecture provides a relatively secure and cost-effective way to address a common customer requirement. The Airline Airline reservations and credit card authorizations are the two most common illustrations of "24 x 7 gotta-be-there" applications. One company is building redundant databases in DB2 for MVS and DB2 for NT. Its ticketless reservation system is being coded to look to DB2 for MVS as the primary system-of-record. If it receives an error message that indicates that the database is unavailable or the communication link is down, it will automatically switch to the DB2 for NT backup. DataPropagator-Relational (DPropR), IBM's data replication solution, is used to synchronize the databases when the connection is re-established. DDCS for NT provides the pipeline for the application when DB2 for MVS is the target and for DPropR after the fault has been corrected. While things are running smoothly, DDCS and DPropR are constantly at work keeping the secondary DB2 for NT database current on a near-real-time basis. Note: It hasn't been determined how the initial baseline copy of the data will be captured for DB2 for NT. The County Local governments across the country are faced with the contradictory challenge of increasing services while reducing taxes. By giving county employees desktop productivity tools and by providing access to host DB2 for MVS data via DDCS for HP/UX, this county will enable a fixed number of workers to meet a growing demand for information and analysis in a timely manner. The county's decision support system is a custom-developed, proprietary application that uses the Open Database Connectivity (ODBC) API bundled with Client Application Enabler (CAE). CAE from IBM is installed on each user's PC running Windows version 3.x to establish the connection to the DDCS gateway. We've looked at three different organizations (not-for-profit, for-profit, and government) using three different operating systems (OS/2, Windows NT, and HP/UX). All three use a common architecture (DRDA) and robust technology (DDCS). In closing, some quick points: 1) The Beta program of DDCS Version 2.4 does not require an intervening gateway server, but can communicate directly from Windows Version 3.1, Windows For Workgroups, Windows 95, and Windows NT clients to DB2 for MVS, DB2 for VSE&VM, and DB2 for OS/400 hosts. In the examples above, the University and the Airline both needed the gateway for the Web server and the workstation database respectively. The County, however, might forgo DDCS for HP/UX and place DDCS on each client. The determining factors are software and installation/configuration costs versus the need for another platform and the cost of its care and feeding. 2) IBM has designated Lotus Approach as its "skinny" client software recommendation. Approach is currently (or will soon be) available on the various flavors of Windows, OS/2, and AIX providing a cross-platform solution for organizations with a heterogeneous client base. Unfortunately, Approach _ like Microsoft Access and some other desktop productivity databases _ chokes on IBM DB2 DATE, TIME, and TIMESTAMP datatypes. A workaround is to create a VIEW and use the CHAR scalar function to convert the offending column to a character representation. For example: CREATE VIEW my_view ( col1, char_date, col3 ) AS SELECT col1, CHAR(col2_date), col3 FROM my_table. You won't be able to update this view, but it should work fine for data retrieval. P.S. In the June 1996 issue of DB2 family news, I referred to the DB2 for MVS/ESA v4.2 Preview Announcement as it related to DRDA. Even though you won't need a gateway server to run DDCS, you'll still need the protocol conversion, session management, and authentication services that DDCS provides. Education & Training 1996 Schedule DB2 for VSE & VM (SQL/DS) All courses in the IBM DB2 for VSE&VM (SQL/DS) curriculum are now compliant with SQL/DS Version 3 Release 5 functionality. The classes include: Database Administrator (DBA) track U4501 - "SQL/DS System Administration - Basic" U4502 - "SQL/DS System Administration - Advanced" U4505 - "SQL/DS Performance Monitoring and Tuning" Application Developer track U4503 - "SQL/DS for Application Designers" U4500 - "SQL/DS Application Programming Workshop" U4510 - "SQL/DS Application Designer and Programmer Workshop" To get a current schedule for these offerings, call the IBM Fax Service at 800-IBM-4FAX (800-426-4329) and request document number 2179. The Fillmore Group is also planning a standalone workshop to detail the new SQL/DS Version 3 Release 5 features and functions and provide insights into DB2 for VSE&VM trends and directions. If you are interested in attending, send an Internet e-mail note to: workshop@ws1.tfg-rdbms.com. .pa Join us in Miami for the Premier Database Technical Conferences of 1996! DB2 Technical Conference October 14 to 18 Fontainebleau Hotel Miami Beach, Florida Data Warehouse Technical Conference October 14 to 16 Fontainebleau Hotel Miami Beach, Florida DB2 Technical Conference * Learn what's new in DB2 for MVS, including enhancements to the Version 4.2 release for MVS and MVS Version 4 data sharing capabilities. * Gain the very latest in-depth knowledge of the powerful Common Server member of the DB2 family. It runs on OS/2, AIX, NT, HP-UX, Sun Solaris and more. * Learn about the awesome capabilities of Common Server Parallel Edition. This one is hot. * Learn about the strategic merger between IBM and Lotus. Find out how it will affect you. * Gain the knowledge you need to implement a data warehouse and add data mining to your list of skills. * Examine the hottest topics: data mining, DB2 for OS/400, VM and VSE, plus DataHub, DataJoiner, Visual Explain, Visual Warehouse, Jodax, OS/390, and replication with DataPropagator. Get even more from your educational investment. Those who attend the DB2 Technical Conference will also be able to attend any session offered at the 1996 Data Warehouse Technical Conference running concurrently at the Fontainebleau Hilton. Data Warehouse Technical Conference * Build skills that will help you transform data into decision-making information that can be accessed and exploited to maximize your return on investment. * Increase your understanding of the latest products, including IBM's hot new DataHub, DataJoiner, and DataPropagator that help you maximize current database systems, master and control emerging technologies, and preserve your legacy investment. * Find all the IBM solutions in one place. Explore data warehouse and decision-support answers that boost performance while improving the availability, security, and recoverability you're used to with your data stores. * Explore the hottest topics and issues, including; data mining, OnLine Analytical Processing (OLAP), multi-dimensional database approaches, replication, mining, VLDBs (Very Large Databases), and network computing. You can access an up-to-date agenda for both conferences via internet on IBM's gopher server (gopher://gopher.ibmlink.ibm.com) or via the IBM Fax Service by calling 1 800 IBM-4FAX (outside the U.S., call 001.415.855.4329). Request document # 4993 for the DB2 Conference agenda and document # 4994 for the Data Warehouse Conference agenda. IBM Education: Call 1-800-IBM-TEACH (426-8322) in North America to enroll in a course, get up-to-the-minute schedules or inquire about a private class at our place or yours. Outside the U.S., please call 001.602.248.7319 from 8 a.m. to 8 p.m. EST. You can also learn more about IBM's education offerings as follows: * By internet on the World Wide Web at http://www.training.ibm.com/usedu * By fax at 1-800-IBM4FAX (001-415-855-4329) and selecting document number 0007 for an education index. CALL NOW! Don't miss these conferences and the opportunity to boost your database performance for years to come. To register or request a conference brochure, call 1-800-IBM-TEACH (1-800-426-8322), and ask for "Conferences". If you are calling from outside the U.S.A. or Canada, please call our international number 001.602.248.7319 from 8 a.m. to 8 p.m. EST. .pa PLATINUM technology, inc. Announces... Partition Expert Speeds Up Partition Maintenance PLATINUM technology, inc. has added Partition Expert to its DB2 for MVS Utilities product line. A partition maintenance tool, it allows DBAs to quickly redistribute data among tablespace partitions, as well as roll data off the front or back of the tablespace. Partition Expert is another tool in PLATINUM's suite of utilities that maximizes the availability of DB2 application. "With Partition Expert's advanced technology, PLATINUM is providing DBAs with a tool that completes partition maintenance activities, such as smoothing, quickly and easily with minimal application downtime," said Scott Jesse, Director Oakbrook Utilities Lab, PLATINUM's Database Management Solutions Division. "Processes that may have taken DBAs days to complete in the past can now be accomplished in just a few hours." Features * Forward or Backward Roll - DBAs can roll off data in the first or last partition, and either delete it or archive it to sequential files. Partition Expert then logically moves each partition's data over to the next partition, allowing new data to be entered into the empty partition. * Eliminates Hot Spots - Working with PLATINUM's Fast Load and Unload, Partition Expert cools off hot spots by redistributing data, averting a potential outage. Hot spots are eliminated without dropping and re-creating tablespaces; only the affected partitions are changed. Partition Expert is fully integrated with Fast Load, Fast Unload, and Fast Index Utilities for maximum application availability. DBVision for DB2: Database Management for Large-Scale Environments PLATINUM technology, inc. now provides enterprise-strength performance management for DB2 Common Server databases with the beta release of PLATINUM DBVision for DB2 Common Server 2.1. DBVision collects detailed performance statistics that help DBAs identify problem areas, and potential problem areas, in their databases. This release of DBVision is a performance management and monitoring tool designed especially for IBM's DB2 Common Server databases in large-scale, distributed computing environments. PLATINUM also announced that a version of DBVision that supports DB2 Parallel Edition is in development, with a beta release expected in the third quarter of 1996. "Giving customers the ability to monitor and tune their database activity with operational tools is key to the deployment of business-critical applications," said Janet Perna, Director of Database Technology for IBM Software Solutions. "IBM has worked closely with a number of customers and software vendors worldwide to provide an extensive set of monitoring interfaces in DB2 Common Server which enable a wide range of system management capabilities. We are delighted that PLATINUM chose to work with us on the definition of these interfaces. We're very excited that PLATINUM has made DBVision available for DB2 Common Server." Database Monitoring, Alarm, and Corrective Actions DBVision can monitor databases 24 hours a day, 7 days a week, and collect detailed statistics on performance. The collected data is stored in DB2 tables or nonrelational Fast Archiver, providing a self-maintained, complete, historical record of all activity occurring in the monitored DB2 databases. DBVision functions are available at installation _ no customization is required. Database statistics can be displayed in easy-to-read strip charts, 3-D or 2-D bar graphs, and tables. Online Advisor text explains how to interpret the data the graphic shows. Alarms are generated when problematic conditions arise, and corrective actions can be automatically initiated to deal with them. DBRunner Version 2 for Faster, Easier Migrations PLATINUM technology, inc. announces Version 2 of PLATINUM DBRunner, a GUI-based migration tool that automates the difficult and time-consuming task of keeping DB2 for OS/2 databases consistent across an enterprise. DBRunner Version 2.0 features support for DB2 for OS/2 Version 2.0. It provides the capability to perform comparisons between a source database and one or more target databases simultaneously, increasing both the speed and reliability of migrations. "DBRunner allows database administrators and application developers to perform efficient migrations among DB2 for OS/2 databases using simple point-and-click commands," said Pete Peterson, director of development, database administration tools for PLATINUM technology. "It provides a simple solution to the problems DBAs and developers face when they want to, for instance, move test databases into production. It also speeds the replicating of database changes to multiple servers, both local and remote, running DB2 for OS/2." Features * The ability to review, modify, and approve changes before they are applied, providing increased reliability * The ability to perform migrations between DB2 for OS/2 and DB2 for AIX * New migration commands that provide administrators and developers with enhanced control and flexibility when migrating database objects. About DBRunner DBRunner enables DBAs and developers to efficiently execute four key functions when managing DB2 for OS/2 and DB2 for AIX databases in distributed, multiple-server environments: * synchronizing versions * merging changes * migrating database structures from one database to another * moving test databases into production. With DBRunner, users can choose to migrate entire database structures or single objects, such as a table with related indexes. Users also have the option of migrating any corresponding data. Platform Support DBRunner Version 2.0 requires DB2 for OS/2 Version 2.0 and OS/2 Version 2.1 or above running on any IBM-compatible PC. For access to remote servers, DBRunner requires Communications Manager/2 Version 1.1 for APPC support, or Novell Requestor Version 2.1 or above for IPX/SPX support. Submitted by Richard Dobinski, PLATINUM technology, inc. He can be reached at (708) 691-0657 or by e-mail at dobinski@platinum.com For more information... Sales information can be obtained by contacting PLATINUM at 1-800-442-6861 or 1- 708-620-5000; or info@platinum.com .pa IBM and Baan Provide High-Performance Business Solutions for Enterprise Customers On June 26, 1996, IBM and The Baan Company of Berlin, Germany announced that the two corporations are extending Baan's enterprise business applications, BAAN IV, to IBM's DATABASE2 (DB2) for AIX. Baan and IBM are also engaged in a joint project to port BAAN IV to the IBM System/390 platform so they can deliver the database server in the fourth quarter of 1996 and the application server in 1997. BAAN IV provides a dynamic enterprise modeling framework that enables companies to adapt their business applications to changing organizational structures and business and operational processes. Baan's three-tiered client/server architecture enables the application and database components and user interface of BAAN IV to run on different platforms, including IBM's System/390 and RISC System/6000 servers. By integrating Baan's advanced solutions with DB2, users throughout an enterprise can benefit from its high performance and reliability. "IBM and Baan share a common goal of global leadership in delivering open architecture solutions," says Doug Sallen, vice president, Corporate Marketing, The Baan Company. "BAAN IV customers with complex, multi-site requirements will be able to leverage the scalability and replication strengths of DB2 and IBM's hardware platforms to achieve exceptional information management across the supply chain. Baan sees System/390 as critical for managing information across the supply chain as it provides the capability to have a super server as a network solution." "Baan is an innovative company. It understands today's users are not bound by geography, time or technical constraints and designs its solutions accordingly," says Tim Negris, vice president, Marketing, IBM Software. "IBM's customers will benefit from Baan's multinational approach to the business of doing business." Dynamic Enterprise Modeling The BAAN family includes feature-rich applications for manufacturing, distribution, finance, service, and project management, as well as solutions for speedy implementation and continuous improvement of the applications. Baan's key differentiator is Orgware, which enables companies to dynamically model these applications to reflect changing business processes across the enterprise. For example, a manufacturing company may want to start a second or third shift. Baan software can help ensure raw inventory is available 24 hours per day, without significant cost increases. It can also ensure that each shift is appropriately staffed with employees and managers and that adequate transportation exists to take finished goods to distribution points. DB2 for AIX Integration DB2 for AIX brings the power and scalability of IBM's flagship relational database system to one of the most pervasive UNIX platforms on the market today. With DB2 for AIX, customers can choose between running business applications in a client/server environment or seamlessly integrating these applications with their existing host systems. As an early customer for BAAN IV on DB2 for AIX, UARCO Incorporated's technology group is impressed with the products and level of commitment of IBM and Baan. "IBM is leading its competition in the relational database arena," says Steve Balow, senior vice president, Technology, UARCO. "DB2 has features that are important to our business, including platform compatibility and the ability to handle images and other data types. IBM and Baan have done a great job developing this solution." UARCO, based in Barrington, Illinois, is a leading provider of paper-based and electronic systems to improve business processes and reduce document costs. BAAN IV on DB2 for AIX is expected to be available in the fourth quarter, 1996. Pricing will be announced at the time of general availability. Baan on System/390 IBM and Baan are working together to extend BAAN IV to DB2 for MVS/ESA on OS/390, which is the strategic operating system for the System/390 platform. BAAN IV on System/390 will allow customers to take advantage of and leverage Baan's client/server business applications with DB2 and System/390's continuous availability, high scalability, and system management strengths. The companies currently intend to deliver BAAN IV's database server on System/390 in the fourth quarter, 1996, with a port of the application server to OS/390's UNIX interfaces to follow in 1997. "Our customers are asking for open, high-performance business solutions for the enterprise. BAAN IV is one of the most important. The System/390 platform is open to give our customers the greatest array of software choices," says Hermann Lamberti, vice president, Marketing, System/390. Submitted by Carol Yuen, Information Development, IBM Software Solutions Toronto Laboratory. She can be reached at (416) 448-3831. The Baan Company is a leading provider of enterprise-wide business software applications and consulting services. Companies in the hybrid manufacturing, automotive, electronics, project, and process industries use its products to streamline processes across the enterprise. The company offers the Baan family of integrated, open-systems based applications and tools developed for the client/server architecture. .pa Benchmark News * DB2 for AIX Benchmark Certified for SAP R/3 SAP AG of Walldorf, Germany has certified the benchmark tests for DB2 for AIX for fast performance (measured in response time and for support of large numbers of concurrent users) in the Sales & Distribution component of their award-winning R/3 Client/Server software, IBM recently announced. The tests showed that DB2 for AIX supports 550 concurrent Sales & Distribution (SD) Users with an average response time of 930 Msec. SAP has a number of different types of users. The SD User is considered to be a high volume user, and SAP typically extrapolates one SD user to equal three Finance component Users (FI). In addition, one concurrent user is equal to four to five SAP named users. SAP requires that a database execute a response back to a user in less than two seconds. DB2 for AIX demonstrated that its excellent response time has been engineered into the Version 2 code with the addition of new features such as Dynamic caching of the SAP ABAP code and research developed algorithms for executing instructions on SMP-based systems. This new IBM DB2 for AIX benchmark supports 20 percent more users than any of the other databases certified by SAP on the same RS/6000 systems for the IBM AIX Platform. Response time is significantly faster by almost 100 percent than on other vendor database systems. The benchmark was performed on the following IBM System: Database Server = RS/6000 Model J30 8 way with 2 gig of memory. Software = AIX 4.1.4, DB2 for AIX V2.1.1 with service pack U444032. SAP R/3 version 2.2G. Lan Streamer Token Ring cards were used on the J30 - #2972 Adapter Cards. SAP App Svrs to the Database = 33 RS/6000 Mod 39H with 256 K memory. SAP Application Servers were on six Token Ring LANs. Submitted by Michael J. Swift, IBM Santa Teresa Laboratory. He can be reached at (408) 463-4105. .pa Corrective Service & Customer Support With the passing of each month, DB2 is being ported to additional platforms. To date, Corrective Service is available for OS/2, Windows 3.1, Windows 95, Windows NT, AIX, and Macintosh. FixPaks are also available for Database Server for OS/2 Warp and Database Server for AIX. Corrective Service DB2 for OS/2 To reduce the size of this FixPak, you must have DB2 for OS/2 FixPak 8090 (syslevel 8090) installed. This FixPak will update the following products to the latest level of code: * Database 2 for OS/2 (DB2 for OS/2) * Software Developer's Kit for OS/2 (SDK/2) * DB2 Administrator's Toolkit for OS/2 * Distributed Database Connection Services for OS/2 (DDCS for OS/2) * Client Application Enabler for OS/2 (CAE/2) For a list of APARs and installation instructions, refer to the files APAR.LST and README located on diskette #1. The RETAIN PTF numbers for each country produced are listed in the chart. Software Developers Kit and Client Application Enabler for Windows 3.1 To reduce the size of this FixPak, you must have SDK for Windows with FixPak 8092 (syslevel 8091) installed. This FixPak will update the following products to the latest level of code: * Software Developer's Kit for Windows (SDK/Win) * Client Application Enabler for Windows (CAE/Win) For a list of APARs and installation instructions, refer to the files APAR.LST and README located on diskette #1. The RETAIN PTF numbers for each country produced are listed in the chart. Originally, these fixes were shipped as FixPak 8091. A few weeks after it was available, an update to the package was required. Subsequently a second RETAIN PTF number (8092) was created, making 8091 obsolete. The package names of the FixPaks that are available electronically (8091) are unchanged. However, diskettes ordered from IBM will be labeled WR08092. DB2 for Windows 95/ Windows NT The FixPaks for Windows 95 and Windows NT will upgrade the products from Version 2.1.0 to Version 2.1.1. FixPak WR08096 will update the following products: * Database 2 for Windows NT (DB2 for Windows NT) * Distributed Database Connection Services for Windows NT (DDCS for Windows NT) * Software Developer's Kit for Windows 95 & Windows NT (SDK/95, SDK/NT) FixPak WR08097 will update: * Client Application Enabler for Windows 95 & Windows NT (CAE/95, CAE/NT) For a list of APARs and installation instructions, refer to the files APAR.LST and README located on diskette #1. The RETAIN PTF numbers for each country produced are listed in the chart. SDK/CAE for Macintosh This FixPak will update the product to the latest level of code. For a list of APARs, refer to the APAR.LST file located in the DB2 folder after the installation has been completed. The RETAIN PTF numbers for each country produced are listed in the chart. DB2 for AIX This FixPak will update the following products to the latest level of code: * Database 2 for AIX (DB2 for AIX) * Software Developer's Kit for AIX (SDK/AIX) * Distributed Database Connection Services Gateway for AIX (DDCS for AIX) * Client Application Enabler for AIX (CAE/AIX) For a list of APARs and installation instructions, refer to the files lpp.fixinfo and README. The RETAIN PTF numbers for each country produced are listed in the chart. Database Server FixPaks are available for Database Server on the AIX and OS/2 platforms. Because of the integration testing that is performed with the other Software Servers, the latest PTF listed may not be Generally Available at the time of this newsletter. They should be available shortly thereafter. These FixPaks will update the following products to the latest level of code: * DB2 Server (DB2 for AIX, OS/2 Version 2.1.1) * DB2 World Wide Web Connection * Distributed Database Connection Services Multi-User Gateway (DDCS Multi-User Gateway Version 2.3.1) For a list of APARs and installation instructions, refer to the files lpp.fixinfo and README for AIX. For OS/2, please refer to the files APAR.LST and README. Customer Support These FixPaks can be downloaded electronically from the following locations: (1) Internet - FTP to anonymous server ftp.software.ibm.com (previously known as ps.boulder.ibm.com) at 198.17.57.66 - Then go to ps/products/db2/fixes// where is (for example, english-us, spanish, german, etc.) is the product name/version (for example, db22v21, db2aixv21, etc.) - Use a World Wide Web (WWW) browser to connect to the DB2 Service and Support Page. (http://www.software.ibm.com/data/db2/db2tech/index.html) (2) CompuServe - Execute GO IBMDB2 - Then go to the DB2/OS2 library to find the DB2/OS2 FixPak - OR go to the CLIENTS library to find the SDK/Win FixPak. (3) IBM PCC BBS (in US) - Call (919) 517-0001 (in Raleigh, North Carolina) - Then type "db2" on the main menu to find the FixPaks The above are the primary locations where these FixPaks are uploaded. They may also be available on other Bulletin Boards (such as the Talklink OS/2 BBS in the US). If you do not have access to any of the above locations, please call 1-800-992-4777 to request these FixPaks be sent to you in the mail. For AIX platform FixPaks, please call 1-800-237-5511. For countries other than U.S. and Canada, please look at your local IBM OS/2 BBS or call your local DB2 Customer Service number for assistance in obtaining them. Submitted by Gavin Letham, IBM Software Solutions Toronto Laboratory. He can be reached by e-mail at gletham@vnet.ibm.com or by phone at (416) 448-2543. PTF Product WR08095 Database Server for OS/2 - all languages WR08096 DB2 for Windows 95 and Windows NT, FixPaks WR08096,WR08097 - all languages WR08110 DB2 for OS/2 - FixPak WR08110 - all languages WR08111 SDK/CAE for Windows 3.1 - FixPak WR08111 - all languages WR08112 Database Server for OS/2 - all languages WR08090* DB2 for OS/2 - FixPak WR08090 - English only WR08092* SDK/CAE for Windows 3.1 - FixPak WR08092 - English only WR08093* DB2 for OS/2 code level WR08090 - all languages WR08094* SDK/CAE for Windows 3.1 code level WR08092 - all languages U444032 DB2 for AIX U444159 Database Server for AIX U443307* Database Server for AIX .pa DB2 Worldwide Events DB2 Technical Conference October 14 to 18, 1996 Miami Beach, Florida Data Warehouse Technical Conference October 14 to 16, 1996 Miami Beach, Florida IBM VSE and VM Conference October 14 to 18, 1996 LaHulpe, Belgium IDUG European Conference October 21 to 24 Amsterdam, The Netherlands IDUG Asia Pacific November 13 to 15 Melbourne, Australia .pa NEWSLETTER INFORMATION This quarterly newsletter is produced by the Software Marketing Centre at the IBM Software Solutions Toronto Laboratory. For further information on any of the products mentioned, contact your local IBM office or an authorized IBM Business Partner. Don't hesitate to contact us about newsletter content, subscriptions, or article ideas in one of the following ways: INTERNET: db2news@vnet.ibm.com COMPUSERVE: Enter, GO IBMDB2 IBM VNET: TOROLAB2(DB2NEWS) FAX: (905) 316-4733 .pa DO YOU HAVE PRODUCT ENHANCEMENT IDEAS? There are two ways to let us know! * Discuss your requirements with your IBM representative, and have him or her submit your requirements to the Database Technology group at TOROLAB2(DBMREQ). * Or, send your detailed requirements to IBM using the Reader's Reply form. INTERESTED IN RECEIVING BACK COPIES OR A SUBSCRIPTION TO THE NEWSLETTER? * Indicate your interest on the Reader's Reply form and mail or fax it back to us. Sample code is provided for information purposes only, and is used by readers at their risk. IBM makes an effort to provide accurate and safe code examples, but does not guarantee their correctness. copyright IBM Corporation 1996.