MARCH 1996 DB2 FAMILY NEWS .pa DB2 TEXT EXTENDER NOW AVAILABLE WITH DB2 VERSION 2.1.1 Find what you're looking for with the DB2 Text Extender, which uses the built-in DB2 Version support for LOBs, User-Defined Types, Functions, and Triggers. The DB2 Text Extender allows your applications to access and retrieve complex documents in a variety of ways. The DB2 Text Extender offers DB2 users and application programmers a fast, versatile, and intelligent method of searching through text documents. Text Extender's strength lies in its ability to search through many thousands of large text documents at high speed, finding not only what you directly ask for, but also word variations and synonyms. Text Extender can access any kind of text document - including word-processing documents in their original, native form - and offers a rich set of retrieval capabilities including word, phrase, wildcard, and proximity searching using Boolean logic. At the heart of Text Extender is IBM's high-performance linguistic search technology. It allows your applications to access and retrieve text documents in a variety of ways: * Searches for documents that contain specific text; synonyms of a word or phrase; or sought-for words in proximity, such as in the same sentence or paragraph; * Does wildcard searches, using front, middle, and end masking, for word and character masking; * Searches for documents of various languages in various document formats. You can integrate your text search with business data queries. For example, you can code an SQL query in an application to search for text documents that are created by a specific author, within a range of dates, and containing a particular word or phrase. Using the Text Extender programming interface, you can also allow your application users to browse the documents. By integrating full-text search into DB2 SELECT queries, you can have a powerful retrieval function that combines attribute and full-text search. The following SQL statement shows an example: SELECT * FROM MyTextTable WHERE version = '2' and CONTAINS ( DB2BOOKS, '"authorization" IN SAME PARAGRAPH AS "table" AND SYNONYM FORM OF "delete"') = 1 CONTAINS is one of several Text Extender search functions. DB2BOOKS is the name of a column containing the text documents to be searched. The remainder of the statement is an example of a search argument that looks for "authorization", "table", and "delete", or any of its synonyms, occurring in one paragraph. Submitted by May L. Gee, Data Management Solutions Planning, IBM Santa Teresa Laboratory. She can be reached at (408) 463-2734. Try it for yourself.... The DB2 Text Extender is available with DB2 Version 2.1.1 for AIX, and supports an AIX server and OS/2 and AIX clients. The DB2 Version 2.1.1 refresh is available now. Further extenders are being developed that will let you access a combination of image, video, voice, and fingerprint data types in one SQL query. Current customers of DB2 Version 2 for AIX can obtain the DB2 Text Extender by calling the IBM Support Line. Just request the DB2 Text Extender CD-ROM that comes with the DB2 V2.1.1 service refresh. Installation instructions and documentation are included with the CD-ROM. .pa ANNOUNCING SQL TRANSPARENCY FOR DB2/VSE VSAM/VSE users, we can take you to DB2 for VSE, a relational database. There is no longer a need to maintain the same data in VSAM as in SQL. With SQL Transparency for DB2/VSE, VSE/VSAM users will be able to run VSAM programs and access SQL/DS tables effortlessly. The burden of maintaining two sets of data will be gone forever! SQL Transparency for DB2/VSE provides the data definition model and programs required to make the VSAM access transparent in SQL/DS. Users will run their VSAM programs as they always have, but the access will actually be translated into SQL/DS calls. SQL Transparency for DB2/VSE performs efficiently because access is static, resulting in low overhead. Also, the four gigabyte restriction of manipulating VSAM data in VSE is completely removed, so you do not have to maintain multiple data-bases if you are a large user. Why wait? Reduce your workload by at least 50 percent, simply by obtaining SQL Transparency for DB2/VSE. For more information on SQL Transparency, contact the marketing department at: The PARAGON Collection, Ltd. 4676 Admiralty Way, 3rd Floor Marina del Rey, California 90292 Phone (310) 574-5370 Fax (310) 574-5371 .pa AN EXCITING START TO 1996 1996 promises to be every bit as exciting as 1995. As I've met with many DB2 users at conferences, at your locations, and at the IBM laboratories, there is definitely a common set of interests and challenges that you face. You are being asked to provide the IT infrastructure to help your enterprises make better use of operational data to drive new products and services. You are being asked to help the businesses get products to market faster and to reduce cost _ and there is the constant focus on customers and achieving the highest levels of customer satisfaction for your products and services. These are common themes in every corner of the world and in every industry. While data management is not the "be all and end all" to providing the solutions that will make this happen, it is a fundamental underpinning of the IT infrastructure that will enable it. It is through the distribution, organization, and analysis of data that we will be able to help achieve these goals. It is by enabling new types of applications, new ways of getting information into the hands of those who need it, and by providing the business solutions while driving the total cost of ownership down that we will help our businesses achieve their strategic objectives. In this issue of DB2 family news, we will focus on some of the key enabling technologies that IBM is delivering to help you achieve these objectives. Data Warehousing solutions for the DB2 Family of products, solutions for managing heterogeneous databases and system environments, and capabilities that will enable you to extend your investment in relational databases with new data types. We will also give you an update on our new DB2 VM and VS2 product (SQL/DS 3.5) and how some new application partners are joining the DB2 Family. There are also a number of other hints and tips along the way for assisting you in your DB2 Family implementations as you deliver new applications within your existing infrastructure. It's a great kick-off to what promises to be a great year. Happy reading. Janet .pa 1996 DB2 TECHNICAL CONFERENCE Mark your calendar for the 1996 DB2 Technical Conference, scheduled for October 14-18 in Miami Beach at one of Florida's premiere resorts, the Fountainebleau Hilton. This conference will help you tackle your specific database management issues through an exploration of a wide variety of technical sessions that fall into three distinct topical tracks: DB2 for MVS, DB2 Common Server, and Data Warehousing. You can focus on your individual interest area or you can move from track to track. Tracks will address such technical issues as: * Parallel Edition and Sysplex * Data Mining * Database in a Network Centric World * DB2 Everywhere (AS/400, Windows NT, SUN Solaris, HP-UX, etc.) * Performance and Tuning * Tools and Support Suites * Application and Administration Tips and Tricks * DB2 Futures No other conference gives you the depth of DB2 product information or allows you to talk directly with more key IBM database developers and educators. To be added to the 1996 DB2 Technical Conference mailing list, please call 1-800-IBM-TEACH (1-800-426-8322), and ask for "Conferences". If you are calling from outside of the U.S.A., please call our international number 001-520--574-4500. We look forward to seeing you in Miami Beach! Submitted by Amiet Goldman, Senior Marketing Strategist, IBM Education and Training, New York, NY. She can be reached at (212) 745-3824. .pa COMMON EUROPE 1996 An Association of IBM Computer Users Congress Jerusalem, Israel June 2 to 6, 1996 Plan to attend this Europe-wide conference. Meet fellow users and learn what's new! Main Topics: * AIX * AS/400 * Client/Server * Communications * EIS * Imaging * Labs * MIS * Multimedia * Networking Systems * Object-Oriented Technology * Open Systems * Personal Computing * RS/6000 For further information, please contact: The Secretariat Common Europe 1996 c/o Kenes - Organizers of Congresses and Tour Operators, Ltd. P.O. Box 50006 Tel Aviv 61500 ISRAEL Tel: (+972) 3 514 0000 Fax: (+972) 3 517 5674 / 514 0077 .pa IBM's HETEROGENEOUS DATA MANAGEMENT FAMILY With its new focus on heterogeneity, the IBM data management family boasts several useful products. DataHub allows you to manage heterogeneous databases, while the Data Replication Solution allows you to replicate data to and from heterogeneous databases. DataJoiner is middleware that provides transparent access to heterogeneous data sources, supporting a variety of multivendor clients and servers. DataHub DataHub is IBM's strategic solution for managing complex relational database environments. DataHub can: * Simplify many management tasks with an intuitive task-oriented graphical user interface at a single workstation point of control; * Automate database and system operations using business policies and a rule-based approach. DataHub for OS/2 has a control point on OS/2 and supports DB2 for MVS, OS/2, OS/400, VM and AIX. The new DataHub for UNIX provides a control point on UNIX, which manages DB2 for AIX, Oracle and Sybase on AIX, HP-UX or Sun Solaris. The Data Replication Solution IBM's Data Replication Solution delivers legacy data from operational systems to users across an enterprise. Included in this solution are DataPropagator Relational, DataPropagator NonRelational and DataRefresher. Replication administration is provided by tools that integrate with DataHub. With this solution, you can create informational data that can be accessed by decision-support applications without affecting your operational environment. The Data Replication Solution: * Replicates only the data required, subsetting and enhancing as needed; * Allows you to control the currency between source and target data; * Supports both point-in-time copies and data histories; * Supports relational and nonrelational sources and targets via a data staging area; * Includes support for multivendor databases via DataJoiner. DataJoiner DataJoiner provides users with access to live data residing in various databases through a standardized SQL interface, masking the differences in various vendors' SQL dialects and optimizing the SQL across servers. Among the advantages of DataJoiner: * Transparency _ All data sources appear to be local. Differences in SQL dialects, networking protocols and operating systems are hidden. * Heterogeneous join _ Multiple data tables across multiple servers can be joined with a single SQL statement. * Global optimization _ Highly efficient distributed SQL processing is provided through advanced optimization techniques across servers. Client platforms supported are DOS, Windows, AIX, OS/2, HP-UX and Solaris. Data sources include DB2 for MVS, VSE, VM, OS/400, AIX, OS/2, HP-UX, Solaris Operating Environment, DB2 Parallel Edition, Oracle, Sybase, IMS and VSAM files. DataJoiner works with EDA/SQL and CrossAccess to provide support for more types of data sources. Want to learn more? To help you learn more, IBM offers the following DataHub, Data Replication and DataJoiner courses: * Course Code U3950: DataHub LIVE! Installation and Connectivity * Course Code U4251: Using DataHub for UNIX Operating Systems * Course Code U4265: Data Replication: DPropR Implementation Workshop * Course Code U4253: Using DataJoiner * Course Code U4254: DataJoiner Administration For enrollment information, check out the methods listed below. How to enroll 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 l01-520-574-4500 from 8am to 8pm EST. You can also learn more about our 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 Submitted by Amiet Goldman, Senior Marketing Strategist, IBM Education and Training, New York, NY. She can be reached at (212) 745-3824. .pa WHAT'S NEW ON THE DB2 BOOKSHELF Using the New DB2: IBM's Object-Relational Database System by Don Chamberlin published by Morgan Kaufmann Publishers, San Francisco ISBN 1-55860-373-5 This comprehensive user's guide to DB2 Version 2.1 for OS/2, Windows NT, AIX, and other UNIX platforms begins with an overview of the basic features of Version 2.1, then extensively reviews the system's advanced features. Hundreds of tested examples are included, along with lots of practical tips for writing applications. Written in an easy-to-read style, this book is a valuable resource for DB2 application developers, end users, database designers, and administrators. Readers who simply want to know more about database systems can learn much from this book. About the author Don Chamberlin is a member of the research staff at the IBM Almaden Research Center, and an adjunct professor of computing engineering at Santa Clara University. An ACM fellow, Chamberlin is one of the original developers of SQL. He is currently working on object-oriented extensions to relational database systems, and is a member of the DB2 development team. DB2 2.1 for OS/2 Made Easy by V. Mitra Gopaul published by International Thomson Computer Press ISBN 185032-2015 This two-part book - tutorial and reference - explains basic concepts of a relational database management system (RDBMS) step by step. The reference explains commonly used DB2 commands, SQL statements, and built-in functions. Lots of examples illustrate complex ideas and act as models. About the author V. Mitra Gopaul has extensive design and programming experience, specializing in software development for OS/2, UNIX, and MVS systems using DB2 and ORACLE. A consultant for many major companies, he has authored several programming books. .pa THE TECHNICAL MANAGER'S SURVIVAL GUIDE For some Technical Managers, the people management side of the job is where all the fun is. To me, the technical side of being a Technical Manager is what "floats the boat." The danger for most Technical Managers is that the people management side of the job can easily become overwhelming, to the detriment of the technical side. Over time, many Technical Managers get sucked into the abyss of people management, forever enslaved to a world of budgets, staffing estimates, status reports and personnel reviews. My advice to every Technical Manager is keep your technical skills sharp. The better your technical skills, the more likely the corporation is to call on those skills; and the more likely the corporation is to give the people management tasks (that is, staffing estimates by phases of the moon) to another manager. But how does a Technical Manager, with a limited amount of time, stay up-to-date on a variety of technical topics? My secret is IDUG. If you've attended any of the previous IDUG conferences, you'll know why I feel this way. If you haven't attended an IDUG conference, you can't appreciate how important IDUG is to keeping your technical skills sharp. Let me give you a taste of what your life would be like if you had attended the last IDUG Conference (June 11 to 15, 1995). Let's start with a typical Monday morning. You walk into your boss' office. As always, your boss is looking for information. She needs to know the number of consultant hours charged to the department, sorted by month, consultant company and eye color. (You know better than to ask why.) Your boss also needs an overview of DB2 V4 features. You're sharp enough to volunteer for the V4 assignment, which leaves the consultant hours for the next sucker _ oops! _ I mean, manager who walks into her office. The list of V4 features is easy for you _ you just go to your notes from Richard Yevich's "Version 4 Transition" Seminar (6/11/95, 8:30am). You also pull some information from Gerri Lloyd's "DB2 for MVS Version 4 Announcements" (6/12/95, 09:45am). You were not able to attend Gerri's presentation, but the presentation's foils and notes are in the conference proceedings sitting on your desk. Later that day, a fight breaks out between two of your technical gurus, over the details of implementing a data warehouse in a client/server environment. You have a twisted sense of humor, so you let them go a couple of rounds before breaking it up. You've been through Joyce Bischoff's "Developing a Data Warehouse Architecture for Client/Server" (6/12/95, 02:20pm), so you're able to straighten them both out. After the dust settles, you suggest that they both fix their broken glasses and check out your copy of the IDUG conference proceedings. Wednesday's lunch conversation once again turns to DB2 Utilities. (You obviously lunch with a real fun crew.) Everyone is guessing at IBM's future plans for their DB2 utilities... everyone, that is, except you. At the IDUG Conference, you got a pretty good idea about IBM's direction from Roger Miller, IBM's lead DB2 strategist. You received this insight during Roger's "DB2 Version 4 SQL and Administration Enhancements" presentation (6/13/95, 8:15am). And the questions that were not answered during the presentation were answered by Roger Miller after the presentation, during an informal question-and-answer session. Your lunch companions are impressed, but you might have overdone it by referring to Mr. Miller as "my bud Rog." During this same lunch conversation, questions also come up about some of the third-party vendor tools. You have no answers, but do know where to find them. You first review the literature you received from the vendors during your visit to the IDUG Exhibit Hall. In the literature, you find some of the answers you are looking for. Your next step is to reach out to some of the DBAs you met at one of the IDUG Vendor Receptions. On Thursday, you get into a discussion with your boss about the importance of understanding how other companies deal with today's technical challenges. You know the best way to keep current on what's going on. You suggest that your company join one of the local Regional User Groups (RUGs), and commit to sending at least one person to each meeting. While at IDUG, you collected some information about a local RUG. You know when the local RUG meets, where it usually meets, and what the agenda usually looks like. Your boss is very impressed. You also suggest that your company attend the Hawaiian DB2 RUG. (You're guessing that they're doing fantastic things in Hawaii.) Your boss thinks that's even a better idea. Unfortunately, your boss feels that the Hawaiian DB2 RUG assignment requires upper management attention, so she attends to it personally. Late Friday afternoon you're faced with a big problem. One of the DBAs had been working all day on a performance problem without any success. He is wired, and is now taking his frustrations out on one of the DAs. You're told that the DBA had suggested a new location for the storage of the Data Model, something involving the DA. After reviewing the problem, you get everyone to agree that there is nothing wrong with the database design. You then present some ideas for better performance that you picked up from other DBAs at the Large DB2 Users SIG (6/14/95, 5:25pm). You and the DBA also take a look at your notes from Chris Skelnik's "A DBA's Survival Guide in the Jungle of Application Performance Problems" (6/14/95, 11:25am). The DBA and you agree that the next step is to review the DB2 performance reports. No one is more relieved than the DA. I've touched on just a few examples of how an IDUG Conference can help with the technical challenges you face each day. The key point is that, for Technical Managers to be effective, they must keep current on a variety of technical topics. The real challenge is staying up on technology, and doing so when your day is filled with time-consuming people-management tasks. It's a big challenge, but one that can be met. The trick is to be smart about the education you choose to spend time and money on. Choose IDUG! Written by Robert Omerza, UPS, Mahwah, New Jersey .pa VISUAL WAREHOUSE: A CUSTOM DATAMART MADE EASY Designed for the workgroup environment, IBM's newly released Visual Warehouse is ideal for business organizations wanting to build a data warehouse, often called a datamart. Its low start-up cost and ease of use allows a workgroup to quickly build and easily manage its custom datamart. And Visual Warehouse can integrate with a larger, more centralized warehouse as the needs of the workgroup grow. Other important aspects of Visual Warehouse: * Data warehouse definitions, called Business Views, control the extraction and transformation of data into meaningful business information. * Data is extracted, transferred, transformed and refreshed into the warehouse automatically. * Data can also be directly accessed from the source database if needed. * Built-in administration capabilities are provided. * Metadata is used to track where your data comes from and how it is transformed in the warehouse. Through DataGuide, the metadata is made available to end users. * Warehouses can be built on DB2 for OS/2, AIX or OS/400 by retrieving data from the DB2 family, VSAM, IMS, flat files (MVS, VM AIX, OS/2, etc.), Oracle, Sybase, and binary large objects (BLOBs). Visual Warehouse works in combination with DataGuide and Visualizer to catalog and locate data and to gather, analyze and present information. Other vendors' tools can also be used. Easy to use and full of function, Visual Warehouse puts a custom datamart at your fingertips. Written by Linnette Bakow, Data Management Solutions Planning, IBM Santa Teresa Laboratory. She can be reached at (408) 463-3939. .pa BUILDING A DATA WAREHOUSE USING DB2 VERSION 2 Visual Warehouse makes it easy to build a datamart, or a distributed data warehouse, with DB2 for OS/2, AIX, Windows NT, HP UX, Sun Solaris or OS/400. Designed for the workgroup environment, IBM's newly released Visual Warehouse is ideal for business organizations wanting to build a data warehouse. Its low start-up cost and ease of use mean a workgroup can quickly build and easily manage its custom datamart. And Visual Warehouse can integrate with a larger, more centralized warehouse as workgroup needs grow. Before you can build your data warehouse, you need to understand your data. Metadata, or data about your data, helps you to see how data is related and what it means within your business. A customer record can be saved in many different places throughout your business applications, and it will have different meanings within those applications. Visual Warehouse is a metadata-driven system. Metadata is imported or entered into the system to define the data sources and is then mapped through business views to the data warehouse. This lets you track where the data comes from to help you in making your business decisions. Visual Warehouse comes with DataGuide to give end users access to the Visual Warehouse metadata. With DataGuide, they can browse or search through the metadata to find information they need. DataGuide, while not a prerequisite, gives the business user a view of the data in the warehouse. An added benefit of DataGuide is its ability to launch defined reports and queries to materialize the data or business object. Other important aspects of Visual Warehouse: * Business Views Data warehouse definitions of metadata, called business views, control the extraction and transformation of data into meaningful business information. You can define aggregations, derivations, histories and summaries. Once business views are defined within the Visual Warehouse, no programming is needed. The Visual Warehouse engine completely drives the data retrieval and data warehouse creation. Visual Warehouse also provides retry capability when a network link is down. * Data Staging Data can be retrieved from local or remote data sources. It can then be aggregated into other business views, or accessed directly by end-user applications. * Data Editioning Business-view editioning allows for the storage of multiple instances of remote data. Data can be stored up to a user-defined limit of editions. (For example, sales information could be stored in four editions, for the creation of quarterly sales reports). * Data Scheduling Data can be retrieved at specific times or in conjunction with other previously defined business views. This is especially useful when data is recurring (daily, weekly) or when it is required as of a fixed date, such as quarter end. * Data Cascades/Triggers Business views can be executed based on previously executed views or trigger programs. Trigger programs can execute a business view to pull down data to the warehouse, and based on return code values, trigger another business view to execute. * Business View Access Business users can access data stored as business views transparently. Through a variety of client access features, this data can be accessed without modifications to end-user applications. * Data Security Visual Warehouse can control user access to business and operational data. This control is provided by user groups, defined as a collection of users with similar data access requirements. Additional security is available through user types and user functions. Enterprise users demand better access to their data because it's stored in operational databases, without easy access. Users need better information, in a timely and usable manner, so they can make better decisions. IBM's Visual Warehouse meets these demands to enhance business operations in a competitive world. Written by Linette Bakow, Data Management Solutions Planning, IBM Santa Teresa Laboratory. She can be reached at (408) 463-3939. .pa DB2 FOR OS/400 DATA WAREHOUSING Data warehousing is an increasingly popular and powerful concept of applying information technology to solve business problems. In the last issue of DB2 family news, we promised to take a closer look at how the AS/400 and DB2 for OS/400 provide solutions for each of the main data warehousing components. Transforming Operational Data into Informational Data Operational data is the data you use to run your business. It is typically stored, retrieved, and updated by your On-line-Transactional Processing (OLTP) system. Systems could be reservations systems, accounting applications, human resource applications, and so on. Operational data is typically stored in a relational database, but may be in legacy or flat file formats as well. Informational data is typically stored in a format that makes analysis easier. Its data is created from the wealth of operational data that exists in your business and makes up the data warehouse. Creating the informational data (data warehouse) from the operational system(s) is a key component of the overall data warehousing solution. Because operational data is stored in a format appropriate for recording business transactions, analyzing the data in this format can be a time-consuming effort. A transformation is needed. Building the informational database is done with the use of Transformation, or Propagation, tools. These tools not only move the data from multiple operational systems, but often manipulate the data into a more appropriate format for the warehouse. This could mean creating new fields that are derived and/or summarized from the operational data. The complexity of loading the warehouse can vary depending on a number of factors, including compatibility of databases and systems, amount of data cleansing needed, and data refresh intervals. The AS/400 operating system, OS/400, provides a number of facilities that can be used to propagate data between AS/400 systems and other systems. Electronic movement of data can be achieved through object distribution components of a SNADS network. File Transfer over TCP/IP is built into the operating system, providing heterogeneous communications. Distributed Relational Database Architecture (DRDA) and Distributed Data Management (DDM) provide facilities to distribute data across multiple systems. However, these utilities primarily provide simple file transfer facilities and do not support features such as deriving and summarizing new fields. DataPropagator/400 is an IBM solution for moving data into the data warehouse. DataPropagator/400 will move data from AS/400 operational systems as well as any other DB2 family (DB2 for MVS, DB2 for OS/2, DB2 for AIX) database. DataPropagator allows you full SQL capability as you move the data _ an important feature for creating summarized data, derived fields, and multiple summarized datamarts. Being able to capture the data from the operational systems, and propagate it on a timed basis, is another benefit of DataPropagator. Once you have configured DataPropagator, the data warehouse is refreshed automatically. Numerous third-party solutions can be used to propagate databases from one system to another. Middleware can be used in conjunction with the propagation products to link different types of databases to each other. Techgnosis, ShowCase, Trinzic, and Information Builders, Inc. are just some of the companies that can help load your DB2 for OS/400 data warehouse. The Database Engine There are four major requirements of a data warehouse database engine: performance, capacity, scalability, and openness. With the new features of DB2 for OS/400 (V3R1), all of these items have been provided, making DB2 for OS/400 an excellent choice for a data warehousing solution. Customers should evaluate their specific needs in each of these areas, but in general, improvements like parallel processing, partitioned table support, industry standard SQL, distributed data access, and optically connected servers were introduced to meet customer needs in the data warehousing environment. The performance of the engine and hardware must be good enough to support users' performance expectations as they do their analyses. The warehouse may be very big. It is not uncommon for a warehouse to have over 100GB of data, with fairly complex analysis tasks being executed against the database. Parallel computing technologies become quite important once the warehouse solution starts to grow. In 1995, parallel database features for OS/400 were announced that allow DB2 for OS/400 to process massive (terabytes) of data very rapidly. These announcements included symmetric multiprocessing support (SMP) and loosely coupled parallel database support that, together, provide virtually unlimited performance. (The DB2 SMP for OS/400 license feature number is 1698.) The combination of SMP with loosely coupled support allows for extremely large data warehouse implementations. You could have as many as 128 CPUs (32 coupled systems times 4 CPUs on each system), all working together to retrieve the query result (not including the system I/O processors). The capacity of the engine and hardware must be enough to support your entire data warehouse while still providing a seamless single-system view of the data. DB2 for OS/400 supports a capacity of 520 gigabytes per server and 16.8 terabytes in a loosely coupled environment. Scalability of the engine and the hardware must be such that your choice of engine can handle the requirements of an ever-growing amount of data in the data warehouse. The new AS/400 and DB2 for OS/400 features, with horizontal clustering of servers and table partitioning across nodes, provide for growth from small datamarts to enterprise-wide warehouses. Multidimensional Databases define a data structure pertinent to data-warehousing and data-analysis applications. They add multiple dimensions or views to the data typically stored in a two-dimensional relational format. But business analysts want more dimensions when analyzing business data. For example, with a three-dimensional database, a user can see product sales by region by year. Multidimensional databases provide rapid data access within the "cube" for powerful data analysis. Multidimensional databases are implemented in different ways: over a relational model using multiple indexes and summarizations (for rapid drill-down capability), or via On-line Analytical Processor (OLAP) products. OLAP databases are special databases specifically designed for data-warehousing applications. The AS/400 supports both relational and OLAP implementations of dimensional databases. The fully relational DB2 for OS/400 comes standard with OS/400. There are several products that enhance DB2 for OS/400 capabilities by implementing multidimensional databases and OLAP analytical processing support. Products specifically designed for data warehousing on the AS/400 include Hoskyns AMIS/400 and Silvon Sales Tracker & Data Tracker along with IBM's Visual Warehouse. End-User Analysis * Data Analysis and End-User Tools The open interfaces of DB2 for OS/400, combined with the high performance programming interfaces in Client Access/400, give you the base you can use to select any number of analysis tools. These are typically PC-based tools that access the AS/400 through the Client Access product. The choice of tools includes report writers, query tools, EIS tools, spread sheets, and client/server programming tools. AS/400 helps users by providing a Client Series set of products that includes hundreds of leading-edge tools for data analysis. Certified Client Server end-user products include IBM's Visualizer, Showcase's VISTA products, Cognos' Impromptu and Powerplay, and BrioQuery. Several of these tools have specific features that leverage data-warehousing applications by providing rapid data transformation, built-in historical analysis features and convenient drill-down query facilities. * Data Mining With traditional Decision Support (DSS) and Executive Information (EIS) systems, the user forms a hypothesis and uses the query tools and report generators to verify or reject the hypothesis. With data mining, the system researches the data and determines patterns, relationships and associations. Then the analyst determines what to do with the results. For example, data mining has been successful in the retail industry to analyze consumer buying patterns and in the finance industry for risk assessment and portfolio management. DSS/EIS and data-mining applications can coexist to form a total Business Intelligence Software solution on the AS/400. DSS/EIS checkpoints a business' critical success factors, while data mining discovers unknown problems or data relationships. Neural Network Utility/400 (NNU) is an AS/400 product that provides a method to find patterns and understand relationships in your business data. The NNU family of products from IBM gives you a complete set of development tools that combine neural network and fuzzy-rule-system technologies to create leading-edge data mining solutions. Managing Information about the Warehouse In combination with DataGuide/2 and IBM's Visualizer family of products, Visual Warehouse gives business users a complete data-to-information solution. Visual Warehouse shares metadata with DataGuide/2, enabling business users to locate data in their data warehouse. Visual Warehouse helps you to understand your data. You can see how data is related and what it means in your business. AS/400: The Logical Choice Data warehousing implementations do not have to be complex. The AS/400 servers can meet the demands of data warehousing, while you focus your energies on running your business _ not on running your computer. The AS/400 server models build on the strengths of the AS/400 line of computers: ease of use, advanced architecture, open interfaces, scalability and investment protection. The affinity you have with your operational systems make the AS/400 Server the most logical choice for the data warehouse. Both data transformation and systems administration are made significantly easier. .pa CYBORG SYSTEMS OFFERS ADDITIONAL SUPPORT FOR THE DB2 PRODUCT FAMILY Cyborg Systems, the only leading software vendor with a sole focus on providing client/server-based Human Resource Management Software (HRMS) solutions, now provides support for the DB2 (SQL/DS) VM/VSE platforms. Cyborg's client/server HRMS, The Solution Series/ST, offers state-of-the-art technology and flexible human resource management, payroll processing, benefits administration and time & attendance functionality for several members of the DB2 product family _ including DB2 for MVS, VM/VSE and DB2 for OS/400. "At Cyborg, we don't place limitations on the type of hardware that you can use with The Solution Series/ST," says Richard Skibski, Cyborg's vice president of research and development. "Instead, our HRMS has been designed to operate on any UNIX, IBM mainframe or AS/400 series computer. Our recent move to support SQL/DS for VM/VSE provides added flexibility for DB2 users." Cyborg Systems gives organizations the freedom to choose the technology mix that best fits their information environment and business requirements. In addition to comprehensive application functionality, it offers a wide choice of "Selectable Technology" options including support for Windows and Macintosh GUIs, all popular relational database architectures, application partitioning strategies, network operating systems and communications protocols. "As DB2 is IBM's premier database product, it was important for Cyborg to provide the added support for VM/VSE," says Mr. Skibski. Cyborg Systems, Inc. was founded in 1974 and is headquartered in Chicago, IL. Its global operations located in Africa, Asia, Australia, Canada, Latin America, South America, the U.K., and the U.S.A. serve over 1,200 clients in a wide variety of industries. Cyborg's commitment to providing the highest quality products and services has resulted in ISO 9001 registration of its quality management system _ the most comprehensive level of ISO 9000 certification. Cyborg's product and service information can be located on the World Wide Web at http://www.cyborg.com. Submitted by Angelia Darnbrough, Cyborg Systems, Inc. She can be reached by phone at (312) 454-1865 ext. 305, by fax at (312) 930-1033 or e-mail: angelia_darnbrough@cyborg.com. .pa SQL/DS PRODUCTS OFFERED BY THE PARAGON COLLECTION The PARAGON Collection, Ltd. is pleased to announce that all of its SQL products support the recently announced SQL/DS 3.5. Its family of products includes: * BackStore for SQL/DS: Allows users to back up their SQL/DS databases running under either VM or VSE in a short period of time. It brings the added advantage of allowing for table level recovery. If a table is damaged, there is no need to restore the entire database from the last archive. All that is necessary is to reload the damaged table(s). This recovery is performed online and with ease. * SQL/REORG: The process of reorganizing a database can be tedious and error prone if the organizer is not careful. SQL/REORG takes the second guessing out of reorganizing by automating the entire process. It will perform an analysis to see which dbspaces need reorganizing and then will develop all necessary jobs to perform a flawless reorganization. * SQL/TUNE: An essential tool for every SQL/DS installation. SQL/Tune will analyze your SQL statements and determine if they can be optimized to save CPU and I/O cycles. You can do "what if" types of analysis by dynamically modifying your SQL statements online to see the potential gain in resources. Once you are satisfied with the results of the tuning effort, you can then apply the changes to ensure the most optimized application statements. * SQL/Editor: Gives the user the ability to update data in SQL/DS databases without the need to use SQL/DS resources. The user can display and update SQL data in a manner similar to what is used in an electronic spreadsheet on a personal computer. These and other products are available from: The PARAGON Collection, Ltd. 4676 Admiralty Way, 3rd Floor Marina del Rey, CA 90292 Phone: (310) 574-5370 Fax: (310) 574-5371 Submitted by Earle Charles of The PARAGON Collection, Ltd. .pa PLATINUM TECHNOLOGY ANNOUNCES SUPPORT FOR IBM'S DB2 PARALLEL EDITION On February 6, PLATINUM technology, inc. announced support for IBM's industry leading parallel RDBMS _ DB2 Parallel Edition running on RS/6000 SP. "By providing DB2 PE optimized PLATINUM products, we will allow our users to take full advantage of the DB2 PE's unparalleled scalability and performance," said Paul Humenansky, executive vice president of development and COO of PLATINUM technology, inc. "System management is a critical requirement for our customers in today's highly competitive parallel computing environment." "Today's customers can use PLATINUM tools to manage DB2 databases on a number of platforms including MVS, AIX, and OS/2," said Janet Perna, director of database technology, IBM Software Solutions. "Extending this support to DB2 Parallel Edition will provide them not only scalability within the DB2 Family, but also leverages their investment in system management tools." The PLATINUM products that will support DB2 Parallel Edition are: * PLATINUM AionDS - Application development environment for business applications that employ complex logic. * PLATINUM Apriori - Automated problem resolution system, for use in help desk, customer service, and technical support environments. * PLATINUM DBVision - Monitors and manages performance of Informix, Oracle and Sybase databases in distributed UNIX environments. * PLATINUM Desktop DBA - Windows-based, multi-server data administration and migration tool for Informix, Oracle, Sybase, and Microsoft SQL Server databases. * PLATINUM Enterprise DBA - An industrial tool for database administration, alteration and migration in heterogeneous RDBMS environments. * PLATINUM Forest & Trees - GUI-based reporting, query, and analysis tool for executive and management end users. * PLATINUM InfoPump - Middleware for bi-directional data replication and movement within data warehousing and client/server environments. * PLATINUM InfoReports - GUI-based reporting, query, and analysis tool for enterprise-level end users. * PLATINUM InfoTransport - Moves large volumes of data from mainframe databases (DB2 for MVS, IMS, VSAM, and sequential files) to client/server databases (DB2 for OS/2, DB2 for AIX, Oracle, Sybase, and Microsoft SQL Server). * PLATINUM ObjectPro - Object-oriented application development tool for client/server environments. * PLATINUM Repository - A true client/server repository toolset for managing open enterprise metadata. * PLATINUM SQL Spy - Windows-based, cross-platform performance monitoring tool for DB2 for AIX, Oracle, Sybase, and Microsoft SQL Server databases. Availability The release of PLATINUM's DB2 PE optimized products will occur in several phases over the course of the next 12 months. Sales information can be obtained by contacting PLATINUM at 1-800-442-6861 or 1-708-620-5000; or info@platinum.com. About IBM IBM's Software Solutions Division provides data management, application development and workgroup solutions for mission-critical applications on personal computers, workstations, LANs, and host systems. For Internet users, IBM offers complete information about the company, its products, services and technology on the World Wide Web. The IBM home page is accessible via http://www.ibm.com. The fastest, easiest way to get information about IBM software is to go to the IBM Software home page at http://www.software.ibm.com. About PLATINUM PLATINUM technology, inc. delivers the payoff on information technology (IT) investments by providing tools that enable organizations to efficiently manage next-generation computing solutions while leveraging existing and legacy systems. The company provides application development, business intelligence, database administration, data warehousing, and systems management software solutions for the Open Enterprise Environment (OEE) _ the networked computing environment that incorporates mainframe, desktop, and open systems. .pa REXXTOOLS FOR VSE/ESA: SQL/DS SERVICES NOW AVAILABLE Open Software Technologies (OST), a member of the System/390 Developers Association, announces the general availability of REXXTOOLS for VSE/ESA: SQL/DS Services. This product enables REXX/VSE programmers to access SQL/DS databases by embedding standard SQL statements within their REXX-based applications. Data Manipulation Language (DML) verbs such as INSERT, UPDATE, DELETE, and SELECT are supported, as are Data Definition Language (DDL) verbs (for example, CREATE, ALTER, DROP, GRANT, REVOKE). From the REXX perspective, the SQL statements are treated as host commands under the OST-supplied SQL host command environment. Programmers who are familiar with application programming in other SQL/DS-supported languages will require minimal training to use the interface. Through the use of proprietary techniques, REXXTOOLS presents the programmer with the familiar static SQL programming interface, even though all statements can be constructed dynamically, and do not require preprocessing or compilation. Programs, like the one shown below, can simply be written and executed. /* REXX */ parse upper arg userid password lastname tabname = 'SQLDBA.EMPLOYEE' address SQL "CONNECT :userid IDENTIFIED BY :password" "DECLARE SAL VARIABLE DECIMAL(9,2)" "DECLARE EMPCURSOR FOR SELECT FIRSTNME,LASTNAME,SALARY FROM", tabname "WHERE LASTNAME LIKE :lastname", "FOR UPDATE OF SALARY" "OPEN EMPCURSOR" "FETCH EMPCURSOR INTO :fn, :ln, :sal:salind" do while rc = 0 sal = sal * 1.1 say 'New Salary for' fn ln 'is' d2pic(sal,'$$$,$$9.99') "UPDATE" tabname "SET SALARY = :sal WHERE CURRENT OF EMPCURSOR" "FETCH EMPCURSOR INTO :fn, :ln, :sal:salind" end "CLOSE EMPCURSOR" "COMMIT WORK RELEASE" exit All SQL/DS data types are supported, with automatic bidirectional conversion between SQL/DS binary numeric types and REXX decimal. All SQL/DS status information from the SQL Communications Area (SQLCA) and the SQL Descriptor Area (SQLDA) are returned (after appropriate conversions) in REXX stem variables. This last feature permits the SQL/DS Services user to construct highly flexible applications and utilities because their application logic does not need to know in advance what types of operations will be performed, or what types of data will be manipulated. To enhance performance, REXXTOOLS SQL/DS Services automatically caches PREPAREd statements for reuse. Whenever a statement is found to match one in the cache, an expensive PREPARE operation is bypassed. As a result, long-running applications can have performance profiles very close to those implemented with static SQL. REXXTOOLS SQL/DS Services can be used to perform many common programming tasks quickly and easily. You can, for example, use REXXTOOLS to: * Write batch reports. REXX/VSE and REXXTOOLS let you skip the usual preprocess-compile-and-link cycle. With REXXTOOLS, you can make quick work of both scheduled and ad hoc reporting. * Automate Database Administration and SQL/DS-related system administration. The combination of REXX/VSE and REXXTOOLS gives you programmatic access to essential system services and data. * Create custom utilities to fill in functional gaps between IBM and third-party vendor products. REXXTOOLS SQL/DS Services is shipped with its separately priced companion product, REXXTOOLS for VSE/ESA: Basic Services. The Basic Services package includes a REXX-to-VSAM interface that supports all VSE/VSAM data set organizations and all modes of access. In addition, many REXX enhancement functions, such as the D2PIC function _ which brings COBOL-style numeric data editing to REXX (see example program) _ are included in the Basic Services package. Permanent licenses for REXXTOOLS for VSE/ESA components start at under $3000(US). Leasing terms and special pricing for PC Server 500 systems are also available. Address all inquiries to: Open Software Technologies, Inc. 1230 Douglas Ave., Suite 300 Longwood, FL 32779 Phone: 407-788-7173 Fax: 407-788-8494 Email: 72262.2741@compuserve.com Submitted by Earl Hodil, Open Software Technologies, Inc. .pa FRANK'S HINTS & TIPS INSERT INTO mylocal.table1 SELECT * FROM remote.table1 With such an innocuous SQL construct, a terrific row began on the DB2-L Bitnet Listserv. And its fruits just might interest every DB2 user who accesses more than one DB2 Family member. [Note: Regular readers of this column will begin to realize that the Internet is the beta site for these articles.] Some background is in order. A common requirement in multiple-database environments is to copy data from Here to There. (In this context, a database is a DB2 instance, subsystem, or service machine.) A correspondent wanted to know if the above SQL statement would allow him to copy data from a table in DB2 for MVS to DB2 for OS/2. The answers are: a) No, but there are ways to accomplish the same thing; and b) Sure, no problem. Both are correct. The reason for the "No" response is that naming two or more databases in the same SQL statement requires DRDA Distributed Request (DR) support. DRDA DR would allow you to join, say, Oracle, Sybase, and DB2 tables in the same SELECT statement or issue a Format 2 INSERT like the one above. IBM has not yet delivered DRDA DR for any of its DB2 Family of products. To date, DB2 users have employed two unsatisfactory work-arounds: 1) A combination of EXPORT/IMPORT commands. The problem is that the entire process is serial. The EXPORT from the source database must be completed before the IMPORT can begin. Moreover, the full complement of data will exist in at least three places: the source database, the flat-file to which the data has been exported, and the target database _ at least for a while. 2) Write two host-language programs, one with a connection to the source database and a SELECT cursor and the other with a connection to the target, and either an INSERT cursor (if supported) or an INSERT/VALUES statement. Use Inter-Process Communications (IPC) to exchange data between the two. While this method eliminates the shortcomings of #1 above, its difficulty is evident in its definition: "write two host-language programs...". That's a lot of work. Plus, IPC is not trivial. Isn't there an easier way? There are general-purpose utilities to copy data from Here to There. IBM's DataPropagator Relational is a sophisticated example of the genre. For an ongoing data replication requirement, give this product a look. On the other hand, you might need a simple means of executing a bulk data transfer to copy a subset of data from your production to test environment. Before I show you how to do that, you need a glossary: DB2/CS: DB2 Common Server products: DB2 for AIX, DB2 for OS/2, DB2 for HP/UX, DB2 for Sun OS, DB2 for NT, et al. All are assumed to be at Version 2.1. DRDA: The protocol that supports heterogeneous database access among DB2 for MVS, DB2 for VSE&VM, and DB2 for OS/400, and between those platforms and DB2/CS. JRA: This is the protocol, analogous to DRDA, used among and between DB2/CS products. The following pseudo-code will copy data from Here to There. Connect to Here database: open select cursor fetch do while connect to There database insert connect to Here database fetch end close If your Here and There databases are both DB2/CS, use the CONNECT 2 SYNCPOINT TWOPHASE prep options. When you are copying data between DRDA and JRA (DB2/CS) databases, the following restrictions apply: 1) Use the CONNECT 2 SYNCPOINT ONEPHASE prep options 2) Open the SELECT cursor FOR FETCH ONLY. These restrictions will be eliminated with the implementation of SNA LU6.2 Syncpoint Manager. This will allow DB2/CS databases to fully participate in DRDA Distributed Unit of Work (DUoW) transactions and support two-phase commits across a DDCS gateway. Unofficial delivery date is the first quarter of 1996. Didn't I also say "Sure, no problem"? INSERT INTO mylocal.table1 SELECT * FROM remote.table1 ...will work as written if you use an IBM middleware product called DataJoiner. Since DRDA has not been universally embraced by other database vendors (*sigh*), IBM has implemented DRDA Distributed Request functionality _ outside of DRDA _ in DataJoiner: heterogeneous database joins, Format 2 INSERTs between other-vendor databases, and more! In summary, to copy data from Here to There, you can: 1) EXPORT/IMPORT 2) Code IPC programs 3) Use DataPropagator Relational (DPropR) to replicate between DB2 Family members (with some restrictions) 4) Code DUoW programs 5) Use DataJoiner to support Format 2 INSERTs 6) Use DataJoiner and DPropR for an extravagant, any-to-any heterogeneous replication environment (with few restrictions) .pa TAKE CONTROL OF DB2 VERSION 2 WITH THE DATABASE DIRECTOR Part 1 - Configuring DB2 Instances and Redirected Restore Have you made the move to DB2 Version 2? How often do you receive the following error? SQL0104N An unexpected token 'xxxxx' was found following 'xxxxx'. Expected token may include 'xxxxx'. SQLSTATE=42601 This message is usually caused by mistyping SQL statements or DB2 commands. So how can you avoid making this error? DB2 Version 2 provides an alternative to the Command Line Processor with the database director. The database director is a graphical facility that you can use to perform many common administration tasks, including making backup images of databases for recovery purposes. In this article, we will examine some of the more interesting capabilities of the database director. Getting Started with the Database Director DB2 for OS/2 V1.x contained administration tools, including recovery, directory and configuration tools. The functionality of these tools is available via the database director in Version 2. DB2 for AIX V1.x also contained a graphical administration tool, which was invoked using db2adm. The database director is provided as a single administration tool for both AIX and OS/2. You invoke the database director from the DB2 folder when you are using OS/2, or you can invoke it by typing db2dd at a command line. There are two methods for viewing the DB2 objects within the database director: tree view and list view. The default view is the tree view. You can modify the view by changing the settings of the database director. The initial screen will show a list of configured DB2 instances. The database director can perform the following tasks: 1. Configure DB2 instances 2. Configure DB2 database directories (local, node, dcs) 3. Create/Drop/Alter table spaces 4. Backup/Restore table spaces 5. Create/Drop/Alter databases 6. Backup/Rollforward/Restore databases 7. Monitor DB2 objects 8. Display Visual Explain snapshots Configuring DB2 Instances Managing multiple instances from a single interface can save you the frustration of determining the currently active instance using the DB2INSTANCE environment variable. The process of creating a DB2 instance involves using the command: db2icrt . When you are creating a new DB2 instance (keeping in mind that you must be a member of the SYSADM_GROUP to do so), the database director does not automatically display the new instance. The director uses the node directory to determine the available instances. In Figure 1, there are three instances: DB2, INSTB, and SERV1. The DB2 and INSTB instances are local to the DB2 server where the database director was started, and the SERV1 instance is a remote instance. The database director uses the new ATTACH command when it accesses DB2 resources within an instance. When you are attaching to an instance, the instance must have been previously started (using the db2start command). If the instance was not started, you will likely receive a SQL01032N error. As mentioned above, our example (Figure 1) has three instances: DB2, INSTB, and SERV1. To change any of the instance level resources, simply open the settings for the instance and choose the option "Configure...". Remember that you must restart the instance before any DBM configuration settings take effect. The instance (DBM) configuration parameters are grouped according to their type. The categories/parameters for instance configuration are: Environment - numdb, cpuspeed Diagnostic - diaglevel, diagpath Monitor - default monitor switches, sqlstmtsz Administration - sysadm_group, sysctrl_group, sysmaint_group, dftdbpath, authentication Instance memory - mon_heap_sz, udf_mem_sz, backbufsz, restbufsz, sheapthres agent_stack_sz (OS/2), min_priv_ mem (OS/2) Communication memory - aslheapsz, rqrioblk, query_heap_sz, drda_heap_sz, dos_rqrioblk Applications - agentpri, maxagents, maxcagents, max_idleagents DARI - keep_dari, maxdari Recovery - indexrec Transactions - tm_database, resync_interval spm_name, spm_log_file_sz, spm_max_resync Protocols - nname, svcename, tpname, dft_account_str fileserver, objectname, ipx_socket Distributed Services - dir_path_name, dir_obj_name If you want to monitor DB2 resources at the instance level, you can invoke the snapshot monitor from the settings menu of the instance by selecting the option "Start monitoring". (Note: This is a recent feature of the database director.) Redirected Restore Table spaces provide much more flexibility in data placement in Version 2. Two types of table spaces are available: DMS - Database Managed Storage SMS - System Managed Storage There are many differences between DMS and SMS table spaces. One significant difference is in their method of disk allocation to store database objects. A DMS table space requires that each container be a specific size. An SMS table space does not have a specific size for each container. A DMS table space pre-allocates its disk space for each of its containers at table-space creation time. SMS will only allocate disk space when it is required. When a DMS table space is reaching its maximum capacity, you can simply add a new container using the ALTER TABLESPACE .... command. You cannot use this command to add a new container to an existing SMS table space, but you can use the database director to increase the size of an SMS table space during a RESTORE operation. Let's assume that your database contains a table, which is defined within an SMS table space, growing beyond the capacity of its current containers. Therefore, you must spread this table across more containers. The database director allows you to redefine container definitions for SMS or DMS table spaces during a database restore. To change the container definitions, you must initiate a restore by invoking recover from the database object within the database director. Provide the source (previously backed up database image) location, and ensure that the "Pause to allow table space container redefinition" check box is selected. When the restore procedure starts, you will be shown a dialog box (DBA2281W) asking you if you want to change the definition of the containers for the table spaces that the backup image will be restored into. (See Figure 2.) This feature of the database director can help you avoid the time-consuming process of exporting the tables, recreating the SMS table space, and then importing all of the tables. And that's just the beginning! Other time-saving uses of the database director will be discussed in the next issue of the DB2 family newsletter. .pa USER AUTHENTICATION UNDER DB2 FOR WINDOWS NT In a Windows NT LAN environment, a user can be authenticated at either a primary or secondary domain controller. This feature is very important in large distributed LANS with one central primary controller and one or more backup controllers at each site. Users can then be authenticated at their site on the backup controller instead of requiring a call to the primary controller for authentication. However, in the GA level of DB2 for Windows NT, this feature was not accounted for. Any authentication call would subsequently make a call to the primary domain controller. With APAR JR08770, DB2 for Windows NT will now authenticate at a backup domain controller under the following conditions: 1) The DB2/NT Server is installed on the backup domain controller. 2) The DB2DMNBCKCTLR system environment variable is set appropriately. If the DB2DMNBCKCTLR system environment variable is not set or is set to a blank, DB2 for Windows NT will perform its authentication as it does today and will make all authentication requests at the primary domain controller. If the DB2DMNBCKCTLR system environment variable is set to a "?" (that is, DB2DMNBCKCTLR=?), DB2 for Windows NT will perform its authentication on the backup domain controller under the following conditions: 1) The Cached Primary Domain under the registry editor is the domain that the machine is located on. (HKEY_LOCAL_MACHINE->Software->Microsoft->WindowsNT->Current Version->WinLogon) 2) Server manager shows the backup domain controller as active and available. (That is, the icon for this machine is blue, not greyed out.) 3) The DB2 for Windows NT server is a backup on the cached primary domain. Under normal circumstances, the above method of setting DB2DMNBCKCTLR=? will work; however, it will not work in all environments. The information supplied about the servers on the domain is dynamic. The Computer Browser must be running to keep the information accurate and current. In large LANS, the customer may not be running the Computer Browser. Therefore, the Server Manager's information may be incorrect so there is a second method to tell DB2 for Windows NT to authenticate at the backup domain controller. By setting DB2DMNBCKCTLR= XXX, where "XXX" is the machine name of the backup domain controller and DB2/NT server, the authentication will occur on the backup controller under the following conditions: 1) The Cached Primary Domain under the registry editor is the domain that the machine is located on. (HKEY_LOCAL_MACHINE->Software->Microsoft->WindowsNT->Current Version->WinLogon) 2) The machine is configured as a backup domain controller. If the machine is set up as a backup domain controller, but for another domain, the above method will result in an error. This APAR will be available in the first service pack for DB2 for Windows NT and in DB2 for Windows NT V2.11. If the fix is required immediately, please open a PMR with our service group, who will arrange for you to get the fix. Written by Dwaine Snow, Workstation Database Service and Support, IBM Software Solutions Toronto Laboratory. He can be reached at (416) 448-2607 or by e-mail at dsnow@vnet.ibm.com. .pa DB2 FOR VM VMDSS - WHY IS THE TARGETWS VALUE FREQUENTLY EXCEEDED? An oft-asked question is: "Why doesn't the TARGETWS value specified always limit the amount of real storage actually used by the database when it is using VM Data Space Support (VMDSS)?" To understand why the value is exceeded and what affects it, let's look at how the TARGETWS mechanism operates, as well as how VM manages real storage. First, remember that CP is the ultimate arbiter of real storage. VMDSS has some influence over how CP manages the real storage that VMDSS uses (via TARGETWS value, REFPAGE macro and RELPAGE macro), but CP has the final word. Second, VMDSS does not remember all of the pages that are or have been in real storage. This is partly a trade off. If VMDSS remembered everything, a lot of CPU time would be spent keeping track _ a dubious benefit. As well, it is simply not possible for VMDSS to actually know everything that is in real storage. VMDSS does not know what CP is doing on behalf of the database machine. CP may be stealing away pages before VMDSS can release them. This is usually not a problem, and will tend to reduce the database's storage usage. There is another CP effect that is frequently overlooked: when VMDSS references a data-space page that is not in real storage, CP will bring in that page from DASD, and it may also bring in other pages in a block. In some cases, these are extra pages that the database expects will be needed and has told CP to hold together in a block via the REFPAGE macro. However, CP may bring in extra pages based on its own estimation of previous page usage. In both of these cases, VMDSS does not know if CP brought in extra pages or not. Even if the database gives CP some REFPAGE information, CP will use the current system load to decide if it will bring in extra pages or not, and if so, how many. What this really means is that VMDSS does not know exactly what real storage is being used for which data space pages at any moment in time. Third, VMDSS has only one method of reducing the usage of real storage: releasing pages via the RELPAGE macro. The RELPAGE macro tells CP that a specific data-space page (or range of pages) is no longer needed and that CP can immediately reuse the real storage frame that currently holds that virtual data-space page (assuming, of course, that CP has not already stolen that page frame). Also remember that VMDSS does RELPAGEing at specific points when using data space pages. The most important point, for this discussion, is the point when a data-space page is moved from the data space into a database local buffer. Once the page's contents are copied into the local buffer, the page is eligible to be released. This implies that a page will not be released unless it is "used"; that is, moved into a local buffer. Also (ignoring the extra complications caused by the Working Storage Residency Priorities), page releasing also only occurs when the TARGETWS setting is exceeded. Finally, let's take a look at a VMDSS usage scenario and see how both VMDSS and CP react to changing circumstances. Assume that the system is initially lightly loaded. There is an abundance of real storage available for use, and the database is brought up. Of course, the database storage usage will start small _ less than the TARGETWS value. At this point, let us assume that there is light usage of the database. So, data space pages will be referenced, paged in, moved to the local buffers and not be released (assuming that the Working Storage Residency Priorities are all 3). As time passes, the storage size of the database increases. In this unconstrained environment, CP will likely bring in extra pages (beyond those "requested" by the database). CP will not be stealing pages; there's still lots of free real storage and no contention for it. Eventually, the database storage size exceeds the TARGETWS value. Now VMDSS will begin to release new data-space pages as they are used, but it cannot release any of the previously used pages. Note that, at this point, the database storage size still exceeds the TARGETWS, and nothing will be done (yet) to reduce this. VMDSS cannot release old pages, CP is not stealing them, and CP may still be bringing in extra pages. (There is still lots of free real storage.) So the storage size continues to increase and continues to exceed the TARGETWS. Now, eventually, the amount of free real storage will become scarce, and this will cause CP to do two things. First, CP will reduce, and finally stop, bringing in extra pages when a page is requested by VMDSS (and will also begin ignoring REFPAGE requests by VMDSS). Second, CP will begin stealing pages away from the database, so that real storage page frames can be reused by other data-space pages needed by the database. (Remember, we're still dealing with a lightly loaded database and VM system.) However, these actions all have no overall effect on the database storage size. It still greatly exceeds the TARGETWS value, even though VMDSS has been doing RELPAGEs for quite a while now. In general, VMDSS is releasing pages as fast as it requests them, and CP is only stealing away pages as fast as the database needs new ones. The net effect is to simply maintain the database storage size at a constant value, which still exceeds the TARGETWS. Now, for a change of pace. Let's assume that the VM load starts to increase. More real storage will be needed for other users besides the database. CP will begin stealing away more and more of the database's storage for use by other users. Thus, the database storage size finally begins to decrease. CP is stealing pages, and VMDSS is releasing pages faster that VMDSS is requesting them. Eventually, the database storage size will decrease until it is less than the TARGETWS. At this point, VMDSS stops releasing pages. Now we have a situation where VMDSS is requesting pages and CP is stealing others. If the VM system load, at this point, remains relatively constant, the database storage size will hover relatively close to (but usually exceeding slightly) the TARGETWS, because CP will be stealing pages about as fast as VMDSS can request them. However, if the VM system load continues to increase, CP will be stealing away pages faster the VMDSS can request them. The database storage size will continue to decrease and will remain below the TARGETWS, because VMDSS cannot request pages faster than CP can steal them. Eventually, some minimum database storage size will be reached where the rate of CP stealing again equals the rate of VMDSS page requests, which will be below the TARGETWS. In certain storage-constrained environments, CP never seems to be able to prevent VMDSS from taking "too much" storage. In this context, "too much" simply means that other VM users must wait for storage and thus their response time suffers. In these cases, the only solution (without adding extra real storage) is to use the "CP SET SRM MAXWSS nn%" command to set a system-wide storage-size restriction. I hope this helps to explain some of the interactions between the database running VMDSS and CP. Written by Tuna Foulds, SQL/DS Development and Service, IBM Software Solutions Toronto Laboratory, (416) 448-2050. .pa REFRESHES AND FIXPAKS AVAILABLE FOR DB2 CLIENT SERVER PRODUCTS DB2 Client Server V2.1.1 Refreshes We refreshed all DB2 Client Server products in January 1996. These refreshes (V2.1.1) contain fixes for problems found in V2.1.0 and new features that were not available in V2.1.0. Please note that the January '96 refreshes are available in English only. In March '96, V2.1.1 will be available in Japanese, Spanish, French, German, Italian, Brazilian, Portuguese, Korean, and Simplified Chinese. In May '96, V2.1.1 will be available in Swedish, Norwegian, Finnish, and Danish. Along with these refreshes, we also have FixPaks/ServicePaks for customers who are currently running DB2 Client Server V2.1.0 products to upgrade to V2.1.1. If you have a DB2 Client Server product V2.1.0 installed on your systems, we would strongly recommend you upgrade to V2.1.1 as soon as possible to take advantage of the new features and fixes. Listed below are the PTF numbers for the English FixPaks/ServicePaks to upgrade V2.1.0 to V2.1.1: PTF Products WR08080 DB2/OS2, CAE/2, SDK/2, and DDCS/2 WR08081 CAE/Win and SDK/Win U441267 DB2/AIX, CAE/AIX, SDK/AIX, and DDCS/AIX The above PTFs are available electronically and on CDs (free of charge). How to get DB2 Client Server FixPaks/ServicePaks DB2/OS2 and SDK/Win FixPaks can be downloaded electronically from the following locations: (1) 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. (2) 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 the country's language (for example, english-us, spanish, german, etc.), and is the product name and version (for example, db22v21, db2winv21, etc.) to find the FixPaks. (3) IBM PCC BBS (in US) - Call (919) 517-0001 (in Raleigh). - Then type "db2" on the main menu to find the FixPaks. The above are the primary locations where these FixPaks are. They may also be available on other Bulletin Boards (such as Talklink OS/2 BBS in the US and OS/2 BBS in Canada) and other Internet sites (such as software.watson.ibm.com). 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 countries other than US and Canada, please look at your local IBM OS/2 BBS or call your local DB2 Customer Service number for assistance in obtaining them. DB2/AIX ServicePaks: They can be downloaded electronically from the following Internet location: - 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 the country's language (for example, english-us, spanish, german, etc.), and is the product name and version (for example, db2aixv21, db2pev11, etc.) to find the ServicePak. The above is the primary location of this ServicePak. It may also be available on other Internet sites (such as software.watson.ibm.com). If you do not have access to any of the above locations, please call 1-800-237-5511 to request this ServicePak be sent to you in the mail. For countries other than US and Canada, please call your local DB2 Customer Service number for assistance in obtaining this ServicePak. Submitted by Kevin Hoang, DB2 Customer Service, IBM Software Solutions Toronto Laboratory. He can be reached at (416) 448-2828. .pa Look for DB2 at these upcoming events! IDUG North America June 2 to 6, 1996 Dallas, Texas IBM VM and VSE Technical Conference June 3 to 7, 1996 Orlando, Florida IBM VSE and VM Conference October 14 to 18, 1996 LaHulpe, Belgium IDUG Europe October 21 to 24, 1996 Amsterdam, The Netherlands .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. * If you are an IBM employee, you can obtain a copy by requesting the DB2-NEWS PACKAGE on the MKTTOOLS disk. The next newsletter will be available in June 1996. 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.