DECEMBER 1994 DB2 FAMILY NEWS DB2 VERSION 1: THE MOMENTUM CONTINUES - Janet Perna =================================================== It s been a busy fall as the momentum keeps rolling with DB2 installations. To date, there have been more than 200,000 installations of DB2/2, and there are 2,000 for DB2/6000 currently on order. Our DB2/x Software Vendor Program is also proving to be popular. It now boasts over 900 members and over 400 applications and tools delivered. In fact, a recent Vendor Workshop in Toronto saw 200 vendors learning more about DB2 Version 2. Speaking of Version 2 . . . we've begun our beta program for OS/2 and AIX and expect it to continue into the first half of 1995. We ve also been seeing a lot of interest in DB2 Parallel Edition for Power Parallel and have successfully completed a number of customer benchmarks with real customer data and real customer applications. (Beta tests are now in progress. For more information and their results, please contact your IBM representative. Let your rep know they are on the DB2PEPRF package found on DB2INFO at TOROLAB2.) We've cemented our commitment to supporting customer requirements for heterogeneous database management and access with three exciting announcements in October: * DataHub for UNIX - From a UNIX console, you can now manage DB2, Ingres, Oracle, and Sybase on RS/6000, HP and Sun platforms. *Two new tools supporting DB2 Version 2 - Visual Explain & Performance Monitor. Both will be integrated with DataHub. * DataJoiner provides customers with easy access and join information from heterogeneous data sources in multivendor, distributed data environments. While the snow may be falling as winter settles in, the DB2 Family of database products is always on the move. It will be on display at various tradeshows and other group presentations. Why don t you join us, ask questions, and see if there may be something that perfectly matches your requirements? Happy holidays! Janet Perna DB2 CLIENT APPLICATION ENABLER AND SDK NEWS =========================================== The DB2 team is pleased to announce the following new functions for DB2 Clients and Software Developer s kits. New Protocol Support for DB2 Client Application Enabler/DOS The Client Application Enabler/DOS 1.2 now offers several new choices for LAN protocol support: * Banyan Vines NetBIOS support We have successfully tested a Banyan** NetBIOS connection between Client Application Enabler/DOS and DB2/2*. For information on how to enable NetBIOS connectivity between a DOS client and DB2/2 in a Banyan Vines network, please see Learning More at the end of this article. * Beta Test: Increased TCP/IP Protocol Support for DB2 Client Application Enabler/DOS IBM is pleased to make available beta test code that extends TCP/IP support for Client Application Enabler/DOS 1.2 users. Customers willing to beta-test this code can evaluate these TCP/IP protocols in a native DOS (non-Windows) environment: -Novell** (LAN Workplace for DOS 4.1) -3 Com 3+Open** TCP -Beame & Whiteside** TCP/IP -D-Link** TCP/IP for DOS -Digital PATHWORKS** for DOS (TCP/IP) -HP ARPA** Services for DOS -Locus** TCP/IP -Microsoft** LAN Manager TCP/IP -NOV*IX for Netware** -Sun PC-NFS** -Ungermann-Bass** -Walker Richer and Quinn Reflection Network Series** -Wollongong** PathWay Access -Wollongong WIN/TCP for DOS You can request the beta-test code from CompuServe or your IBM Marketing Representative. Or you can get this package from CompuServe by downloading VSLCAE.EXE (for DB2 DOS Clients), or VSLSDK.EXE (for DB2 DOS SDK s) which can be found in the DB2 CLIENTS library (#5). IBMers can request the beta package for additional TCP/IP support by requesting either VSLCAE PACKAGE (for DB2 DOS Clients), or VSLSDK PACKAGE (for DB2 DOS SDK s) from the PCTOOLS disk. Although the beta-test code applies to native DOS clients only, any TCP/IP protocol above which conforms to the Winsockets interface should also work for a DOS/Windows client. IBM support for these extra TCP/IP protocols is limited to a best-effort basis only. All support questions should be handled in one of the following channels: -DB2CLNTS forum on CompuServe -DB2OS2 CFORUM for IBMLINK customers -DB2CLIEN FORUM for IBM internal users. Windows for Workgroups Support for DB2 Client Application Enabler/DOS The Client Application Enabler/DOS 1.2 has been successfully tested with Windows for Workgroups 3.11.** The communications protocol stacks shipped with Windows for Workgroups 3.11 are supported, specifically: -NetBEUI -IPX -Microsoft TCP/IP Memory Extenders Tested with DB2 Client Application EnablerDOS The following DOS memory extenders have been successfully tested with applications running on the Client Application Enabler/DOS: -XM (using applications written with Micro Focus** COBOL) -Blinker 3.01 -Phar Lap(R) 286 Dos-Extender To obtain the support necessary to run the DB2 DOS Client with a memory extender that supports DPMI (DOS protected mode interface), please contact IBM Service and Support and request ServicePak IPx20252 for the Client Application Enabler/DOS or IPx20253 for SDK/DOS (where x in IPx20252/3 is O=U.S., F=France, I=Italy, S=Spain, J=Japan, G=Germany). Microsoft Visual C++ 1.5 Supported by DB2 DOS Clients The Microsoft** Visual C++ (C compiler only) is now supported for developing applications with the DB2 Software Developer s Kit/DOS. The DB2 Software Developer s Kit/DOS (DB2 SDK/DOS) product provides programmers with an application development environment for building relational database applications in the DOS and Windows environments using the Microsoft Visual C++ V1.5 compiler, Professional Edition. The compiler can be used in C mode only. To get a list of considerations and recommendations when using this compiler with the DB2 SDK/DOS Version 1.2 product, please consult the file VISCPP.TXT which can be found in the DB2 CLIENTS library (#5) and the DB2OS2 library (#1). Learning More To get more information on Banyan or TCP/IP support, please specify which of the two topics interests you, and provide a fax number, or E-mail address to: Blair Kenneth Adamache 2R/110 IBM Toronto Software Solutions Laboratory 1150 Eglinton Avenue East North York, Ontario, Canada M3C 1H7 Phone: 416-448-4480 Fax: 416-448-4414 VNET: ADAMACHE@TOROLAB2 IBMMAIL: CAIBMKPW@IBMMAIL Internet: ADAMACHE@TOROLAB2. VNET.IBM.COM EMEA DB2 TECHNICAL CONFERENCE MARCH 6 to 9, 1995, Belgium ============================= The key to future business success, particularly for MIS professionals, is effective management of data throughout the enterprise. The 1995 DB2 Technical Conference is being held in La Hulpe, Belgium from March 6 to 19, 1995, to help anyone who works with DB2 products or applications to better understand the features, functions and capabilities of the various DB2 family products. The conference consists of a series of sessions that focus on data management, system management and application development. There are keynote presentations by IBM executives, in-depth technical seminars, elective sessions dealing specifically with management issues, and Q&A sessions with prominent DB2 experts. You can meet face-to-face with knowledgeable exhibitors and learn more about IBM and vendor products at DB2 EXPO. The conference will be led by top-notch educators, including members of the DB2 development teams from Toronto and Santa Theresa. Among the topics covered at the conference are: - DB2 family products on all IBM platforms - New features of the DB2 engines - Parallel databases - Middleware - Application development tools - Multimedia and object-oriented databases - Technology leadership - Industry trends and directions. Whether you re on the lookout for tips, hints, explanations, technical information, or product solutions, you'll benefit from the array of seminars, discussions and hands-on experiences at the 1995 DB2 Technical Conference. DB2 FAMILY EDUCATION: CLIENT/SERVER IMPLEMENTATIONS ============================= Making the most of distributed computing is a real challenge for MIS professionals. IBM has developed a series of courses aimed at helping technical personnel and developers increase their understanding of database design and application development in a workstation environment. The Distributed Database Design and Implementation Workshop (course no. U4140) is a three-day case study course teaching the design and implementation of database solutions in a client/server environment. It is a product-independent course, discussing implementations using the DB2 family, Oracle,** Sybase,** Ingres,** Informix** and others. The focus is on data placement in a distributed environment that meets access, availability, management and security requirements. (Attendees should be familiar with distributed database concepts and DRDA,* relational database design objectives and normalization.) DB2 Workstation Programming Fundamentals (course no. U4228) is a two-day, hands-on lab course that teaches database programmers the skills necessary to produce applications that manipulate DB2 databases in a UNIX** or PC environment. If you want to learn about static embedded SQL statements and preparing programs for execution, this course will be of tremendous benefit. You should be familiar with SQL and C programming languages before taking it. DB2 Workstation Intermediate Programming (course no. U4229) is a three-day, hands-on lab course that takes database programmers to a level beyond the fundamentals course, above. The techniques learned will enhance your ability to develop applications using static embedded SQL, plus teach and demonstrate the use of Call Level Interface (CLI), ODBC, Stored Procedures and Dynamic SQL. Other topics include Application Programming Interfaces (APIs), locking, and performance considerations. A prerequisite to this course is an ability to code embedded static SQL statements, use basic C language syntax components and an understanding of relational database concepts. All three courses are available as a public seminar or in private offerings for groups of up to 14 participants. Call 1-800-IBM-TEACH to enroll in a course, get up-to-the-minute schedules or inquire about a private class at your place or ours. Outside the U.S., please call (602) 629-2629 from 8 a.m. to 8 p.m., Eastern Standard Time, Monday to Friday, for inquiries and enrollment assistance. UPGRADING TO DB2 FOR MVS V4? ============================ If your company is planning to take advantage of the new features and enhancements in DB2 for MVS* Version 4, IBM can help you get the information and training you need to get the most out of this software upgrade with DB2 Version 4 Transition, course number U4225. Our three-day course explains what s new in Version 4, helps you to understand the impact of these changes on your environment and gets you ready for migration to DB2 Version 4. Even if you re just thinking about an upgrade from Version 3 to Version 4, this course can help you evaluate the applicability of the new functions and enhancements to your environment. Attendees should have a working knowledge of DB2 for MVS Version 3. Call 1-800-IBM-TEACH to enroll, get up-to-the-minute schedules or inquire about a private class at your place or ours. Outside the U.S., please call (602) 629-2629 from 8 a.m. to 8 p.m., EST, Monday to Friday, for inquiries and enrollment assistance. THE WAIT IS OVER: DB2 V4 IS ON THE WAY ======================================= There is good news in the air DB2 for MVS Version 4 will soon be generally available. V4 is the largest and most important release of DB2 since the very first release. What Will DB2 for MVS V4 Mean to You? -It means that the best is getting better. -It means that over 100 user requirements will be satisfied and some of those requirements are major, though they run the full gamut from major (e.g. massively parallel high speed data sharing across multiple systems - in UPDATE mode) to minor (a CANCEL THREAD command). (Though if you've ever really wanted to cancel a thread, maybe you wouldn't consider that so minor.) -It means as DB2 Planner Roger Miller states so eloquently in just one word 7by24BiggerBetterFasterCheaperEasierOpenClientServer! So, how can you position yourself to take advantage of V4? The time to begin planning is now. V4 should be generally available summer 1995. What should you be doing between now and then? For one thing, many of you haven t yet taken advantage of the many benefits of DB2 V3. First and foremost, you must migrate to V3 before migrating to V4. There will be no skip level migration path. We do that for two main reasons: (1) skip level migration (and fall back) is expensive to code, test, and document, and (2) you need to learn how to use and administer the (powerful, efficient, economical) V3 features before moving to V4; it s simply a matter of learning curve. So why not migrate to V3 now? Yes, there is a learning curve. Some of the new features and functions, for both V3 and V4, do require that you learn how to handle their control knobs. There is no better way to learn about the new capabilities than by taking our Transition Classes. Take U4090 to learn about V3; (and take advantage of the new lower price for this two-dayclass from $1200 to $750). To learn V4, take U4225, a three-day course. We think you'll benefit from these courses. Just a few of exciting DB2 Version 4 features: *Data sharing - DB2 s entry into the world of the new MVS Parallel SYSPLEX architecture. This will allow many DB2s, running on separate processors, to update the same page sets. The processing will be fast; SYSPLEX architecture allows enormous increases in processing speeds, and is extremely cost-effective. *Locking enhancements - With V4, there is a new kind of DB2 index, the Type II index. They enable row level locking and dirty reads (hey, you asked for them!). Type II indexes have the extraordinarily attractive property of not requiring locks. These locking improvements are a very high priority for many users. *Partition independence - Allows simultaneous SQL or utility operations on different partitions of partitioned table spaces; for V4, this extends to include even operations on non-partitioning indexes. Now, jobs will fit much better into the ever-shrinking batch window. *Stored procedures - Client/Server processing will be greatly enhanced by this crucial new feature. It will allow client programs to invoke procedures at server sites, leading to impressive improvements in the performance, security, and maintenance characteristics of distributed applications. *Inner, Outer, Right and Left Join - A hot button for many users. This is a robust implementation of the full range of possibilities for relational joins. *CPU parallelism - Again, DB2 is making great strides in the direction of increased exploitation of parallel processing. V3 added the capability of performing I/O operations in parallel; V4 adds the ability to process data for a single query simultaneously with many processors. And there are many other new features, ranging from the ability to define domains of permissible values for data within a table, to enhanced invocation of the super-fast DFSMS Concurrent Copy utility, to an improved ability to provide default values for data. Even DCLGEN has learned to provide indicator variables! DB2/400 RELATED PRODUCTS TO MAKE YOUR JOB EASIER ================================================ As the next evolution of the AS/400* integrated relational database management system (RDBMS), and as the newest member of the DATABASE 2* family of products, the DB2/400* database manager provides maturity, stability, and compatibility with previous releases of the AS/400 RDBMS. At the same time, it offers the open systems, standards-based technology required for today s heterogeneous computing environments. Now, you can make the most of the state-of-the-art functions provided in the DB2/400 database manager with two separate licensed program products: the Query Manager and SQL Development Kit and the Data Propagator Relational Capture and Apply. The Query Manager and SQL Development Kit (previously named SQL/400) provides an interactive query and report writing interface, as well as precompilers and tools to assist in writing SQL application programs in high-level programming languages. The Data Propagator Relational Capture and Apply family of products allow database changes to be automatically copied within and between the DB2, DB2/2, DB2/6000,* and DB2/400 family of products, making data available wherever and whenever you need it. DB2/400 Query Manager and SQL Development Kit This product offers these key features: *Interactive SQL A query environment allowing users and programmers to enter and execute Structured Query Language (SQL) interactively. *Query Manager An interactive query and report generator allowing users to define and run queries accessing DB2/400 databases. Data edit and report capabilities are also provided. *SQL Development Kit Provides precompilers and tools for processing embedded SQL statements in the C, RPG, COBOL, PL1, FORTRAN, and REXX programming languages. Support is now included for the Integrated Language Environment* (ILE*) compilers, as well. Why Use SQL ? As today s computing environments evolve, they are becoming more complex. The days when distributed computing meant sharing a printer or perhaps a file have been replaced with complex mission-critical applications spread across heterogeneous client-server platforms. With this change, the role of the database management system has become increasingly important in providing access to, and protection for, a business data. Throughout this evolution, SQL has gained importance as an industry-wide, standardized database access language. To remain competitive in this rapidly changing industry, application vendors and customers must be able to deliver database solutions with increasing flexibility and portability. As database vendors focus towards standards-conformant SQL implementations, applications using SQL offer richer possibilities for multi-vendor and multi-platform installations. The DB2/400 Query Manager and SQL Development Kit can greatly assist in this transition by offering an ANSI, ISO, and FIPS standards-conformant SQL implementation, allowing customers to leverage the industry s growing SQL skill base by writing database applications that are portable to many environments. This enables a greater number of applications to be run on a wider variety of computing platforms, reducing the costs of program recoding and maintenance. * Increased Performance A great deal of effort has been placed on improving SQL performance. In past releases, customers have experienced performance degradations when comparing SQL to native database access. While some disparity stills exists, the performance of SQL access is greatly improved and, in some cases, outperforms native access. * Easy Migration for Native Applications A major advantage of the DB2/400 database manager and this product is that DB2/400 SQL objects (tables, views, and indexes) are completely compatible with traditional OS/400* objects. Application developers have the choice of using SQL or OS/400 native database access languages for all objects on the system. This is especially useful for existing native access applications, which can be ported to SQL one piece at a time, rather than being completely rewritten. Furthermore, the Query Manager and SQL Development Kit is only required to create SQL applications and queries. It is not required to run them. Any program or query containing SQL, once created, can be run on any AS/400 because the DB2/400 database manager runtime support is provided directly by OS/400. * Interactive SQL The Interactive SQL environment allows users and programmers to enter SQL statements interactively. Full syntax prompting is available to assist in defining SQL statements. This environment is especially useful for three types of users: 1) New users Interactive SQL provides an ideal learning tool for those users learning SQL. Users can experiment with different aspects of SQL with the benefit of full-syntax assist menus and prompting. 2) Administrators Interactive SQL provides a quick and easy-to-use environment for database administration procedures that occur infrequently. For example, to give an existing user temporary read access to a file, an administrator can simply execute the SQL GRANT statement with the appropriate parameters. 3) Programmers Rather than using the results of an application program to verify complex SQL statements, Interactive SQL provides a convenient test environment to ensure SQL statement integrity prior to embedding in an application program. * Query Manager The Query Manager program is an interactive query and report generator that allows users to easily take advantage of new and existing DB2/400 database manager functions. Query Manager allows users two methods of query generation: SQL mode provides experienced users with the ability to define database queries directly using SQL. Prompted mode provides novice users the ability to define database queries without knowledge of SQL. Prompted mode queries can then be converted directly into SQL queries, if desired. The results of queries in either mode can be generated in report forms with a high degree of report layout flexibility. For example, the user can specify page headings and footings; the order, width, and indentation for any column; page breaks; and column summary information. Data edit capabilities are also provided to allow users to create, update, and add data to DB2/400 tables without additional application programs. Additional user profile support is also provided allowing administrators to tailor defaults, limits, and privileges for each user. * SQL Development Kit The SQL Development Kit provides precompilers and tools for processing embedded SQL statements in the C, RPG, COBOL, PL1, FORTRAN, and REXX programming languages. Support is provided for the following: * ANSI X3.135.1992, ISO 9075-1992 and FIPS 127-2 SQL * Referential integrity and stored procedures * Level 2 DRDA* and two-phase commit * System-wide database catalog Some highlights of the SQL Development Kit include: * SQL precompilers Provide preprocessor support for embedding static, dynamic, and extended dynamic SQL statements in application programs written in compiled programming languages. In addition to generating optimized database queries, an SQL flagger is included to warn programmers when non-standards conforming SQL is encountered. * Explain function Examines and reports on the access method used by individual SQL queries. The output can be analyzed to determine whether the access method generated for the query could be improved by query and/or database changes. For example, if Explain reports that a sort on a column is being performed, placing an index on that column might speed up the query by not requiring a processor intensive sort operation. * Scrollable cursors Allow applications to FETCH data in both forward and backwards cursor directions, allowing easy implementation of spreadsheet type applications. * Block INSERT and FETCH Allow applications to store and retrieve arrays of data, instead of processing data one row at a time, for faster database access. * Long names support Allows SQL objects to have names containing greater than 18 characters, providing compatibility with other DB2 family products. * Built-in math functions Increase programmer productivity by eliminating hand coding of many arithmetic operations. For example, instead of retrieving and adding all of the rows for a specific column, executing SELECT SUM(columnname) INTO :variable FROM tablename returns the desired result in one operation. * RUNSQLSTM Command Allows SQL statements to be grouped in a source file and be executed directly from the OS/400 command line for quick prototyping of complex SQL operations. DataPropagator Relational Capture and Apply/400 The Data Propagator* Relational Capture and Apply/400 family of products allow database changes to be automatically copied within and between the DB2, DB2/2, DB2/6000, and DB2/400 family of products using Distributed Relational Database Architecture* (DRDA*) protocols. Each database environment has a version of the DataPropagator Relational product available for it. With it, you can define, synchronize, automate, and manage time interval copy operations for database information from a single control point. Maintaining data copies in separate locations has many advantages. You increase data access and availability, especially in client-server environments where local copies reduce network traffic and associated data access delays. Maintaining copies can also allow you to preserve a version of the data for reference or reports by not allowing the copy to be updated, while the master database continues to be used and changed. * A Closer Look Data Propagator Relational Capture and Apply consists of three logical parts. The Administration Facility is the user interface. From it, you can register tables, making them available as sources of data, define target tables, called subscriptions, that receive the copied information, and perform authorization and performance commands. A graphical user interface can be run using OS/2 on a DataHub/2 terminal. Or, if your environment consists of only AS/400s, you can choose to use native AS/400 commands. The second part of this product is the Capture program. This program runs on the server containing the source database. When Capture is enabled, it keeps track of all changes made to the registered source tables by inserting rows into change tables. The third part is the Apply program, which runs on the servers containing the coied tables. This program connects to the source server to collect changes from the change tables updated by the Capture program. It then provides updates or refreshes to the subscribing database tables. Target tables can receive a full refresh or only the changed records at varying times, depending on the subscription definition. You can produce, for example, a point-in-time table, which provides a snapshot of the original database table at some defined point in time. Or you could choose to use Data Propagator to produce aggregate tables, to track trends over time. Flexible Options Several features of this product provide you with flexible copy operations. * Columns in the target tables may have different names than those in the source tables. * SQL commands can be used to produce computed columns in the target table from some of those in the source. * You can choose to have only a subset of the source rows or columns copied to the targets. * For those desiring to keep close track of changes, you can choose to record both before and after images of a row to the target table. By choosing to append data to targets rather than replacing, you can create a complete history of data changes. Changed data is staged; that is, changed rows are copied out of the source once, to a change table. Target tables get their information from this change table rather than continually accessing the source table, thereby reducing contention for the source database. Also, by only copying changes and not entire tables, network traffic is reduced. Adding a Pair of Aces to Your Hand The centralized computing environments of the past are rapidly evolving into more complex distributed and client/server environments. Applications critical to the success your business are being distributed across heterogeneous platforms and databases with complex communications networks. Having DB2/400 strengthens your hand in this tough game by providing a database server of unprecedented maturity and stability. And adding the Query Manager and SQL Development Kit and DataPropagator Relational products to your hand give you two key cards to win the client server game by giving you an industry standardized data access language, as well as the tools needed to ensure that your data is where you need it, when you need it. This article was written by Lance Amundsen, Staff Programmer, DB2/400 C/S Development and Pam Bowen, Associate Programmer, Integrated File Systems, both of the IBM Rochester Lab. HINTS & TIPS: DB2 FOR VSE AND VM (SQL/DS) ========================================== Implementing the OUTER JOIN Operation There may come a time when you want to combine two different-sized tables together into one answer set. Outer Join is the term for the SQL request that does this. It takes the two tables and combines them in such a way that no rows are lost from the larger table. Where there is no matching key in the smaller table, the SQL request fills in null values to ensure all the key values are represented by a row in the answer set. Outer Joins are usually needed after you have changed, or normalized, a table from the third normal form to the fourth normal form. A table is in third normal form (3NF) if each non-key column provides a fact that depends only on the key. A table is in fourth normal form (4NF) if each row contains only one fact about the entity where the entity is uniquely identified by the key. Let s look at an example involving tables that store customer information. Assume you keep track of your customers by assigning a customer number to them and then store the customer s company name and fax number with the customer number. You can store this information in a table called CUSTOMER where the customer number, company name, and fax number are columns in the table. This table is considered a 3NF table. Example 1 shows you how you can retrieve all of the data from the customer table. * Example 1 SELECT customer_number, company_name, fax_number FROM customer Both the company name and the fax number are dependent on the customer number but are not dependent on each other. This means you can normalize the CUSTOMER table to the 4NF because you can split the table into two tables such that each column from the first table can be keyed to the customer number in the new tables. You can have a CUSTOMER_COMPANY_NAME table consisting of the customer number and the company name columns plus a CUSTOMER_FAX_NUMBER table consisting of the customer number and the fax number columns. In some cases, it is easy to code an SQL request to generate data the same way that it would be generated in Example 1. Example 2 shows how you would rewrite the request in Example 1 as a Simple Join by joining the COMPANY_NAME table with the FAX_NUMBER table on the customer_number column. * Example 2 SELECT A.customer_number, A.company_name, B.fax_number FROM customer_company_name A, customer_fax_number B WHERE A.customer_number = B.customer_number However, there are cases where Example 2 will not give you results that are the same as those from the query on the CUSTOMER table in Example 1. Suppose some of the customers don t have fax machines. The customer table would have to have null values in the fax number column to reflect this fact. The null values would show up in the answer set for the SELECT statement in Example 1. Example 2 will not show these customers in the answer set unless you have explicitly entered the customer number with a null value for them because they will not have an entry in the FAX_NUMBER table if you haven t. (Usually you would not create an entry for something a customer does not have and so you would have tables of different sizes that you may want to join together.) The Outer Join operation resolves this situation so that your query always provides the same results as you would have received in Example 1. Example 3 shows you how you can implement the Outer Join operation by taking the query from Example 2 and unioning it with a second SQL statement. * Example 3 Select A.customer_number, A.company_name, A.fax_number From customer_company_name A, customer_fax_number B Where A.customer_number = B.customer_number UNION Select A.customer_number, A.company_name, C.null_value From customer_company_name A, null_value C Where Not Exists (Select 1 From customer_fax_number B Where B.customer_number = A.customer_number) The second SQL statement generates all of the rows from the CUSTOMER_COMPANY_NAME table that has a customer_number which doesn't exist in the CUSTOMER_FAX_NUMBER table. The query in Example 3 will always generate the same results as the query in Example 1. Please note: You need to have a third table to store the null value. If you don t already have one, you can create such a table as follows: Create null_value (null_value integer); Insert Into null_value values(NULL); While the Outer Join operation can be used with tables in all normal forms, it is designed for use with tables in the fourth normal form. If your application is designed primarily for transaction processing, you would want to consider having your tables in the fourth normal form so your changes are processed quickly. If your application is primarily designed for decision support, you would want your tables to be in the third normal form for faster access. Implementing Row Level Locking Row level locking can help address locking hot spots within DB2 VSE & VM. But make sure you implement it carefully. You can accidentally reduce your concurrency if you don t plan out the implementation of row level locking properly. Row level locking can help address lock waits on index pages and data pages. To do this, change LOCKLEVEL from PAGE to ROW for the dbspace where the lock contention is occurring. This will allow users to access different rows concurrently that are in the same page. However, there are some cases where row level locking will reduce your concurrency. These cases occur when your SQL request invokes a dbspace scan. A dbspace scan on a dbspace that is using row level locking will generate a table level lock for the duration of the logical unit of work. There will not be any row locks. There will only be short locks on the page to prevent page compaction while the user s agent is reading it. Remember, it is possible to have data from other tables on the page that is being read. The use of long table locks in a dbspace with row level locking is done for several good reasons: 1. to avoid lock escalations; 2. to eliminate locking overhead associated with row level locking; and 3. to improve concurrency by processing the dbspace scan quickly. If DB2 for VSE & VM acquired row locks for a dbspace scan, then the SQL request would lock every row in the dbspace. That would then cause other users to be locked out of every table residing in the dbspace. In addition, the user might then cause a lock escalation (where the user s locks would be escalated to a dbspace level lock) because the SQL request would have used up all of the lock request block storage (determined by NLRBU) available to them. As you can see from this explanation, acquiring row locks during a dbspace scan would create an unnecessary amount of lock contention. To gain the advantages of row level locking without the disadvantages: 1. Make sure your programs minimize the use of dbspace scans. Consider creating additional indexes and modifying some of your SQL requests to eliminate the dbspace scan. 2. Ensure you execute a COMMIT statement after each request that does generate a dbspace scan in order to release the lock on the table. This article was written by Amanda Bright, DB2 VSE & VM Marketing & Strategy Planner and Mark Bryski, DB2 VSE & VM Technical Marketing & Services, of the IBM Software Solutions Toronto Lab. MIGRATING APPLICATIONS FROM SYBASE TO DB2 ========================================= Understanding the API's of Sybase and DB2 With IBM s increased commitment to the DB2* product line and its proven reliability, many companies are deciding to migrate their current Sybase** applications to DB2. This article will address those developers who have existing Sybase applications and intend to port them to DB2 but don t yet have a full understanding of DB2. The API's of both products was a natural starting point because this is the first decision that has to be made before migration development can start. There are three different Sybase API's, all of which are Call Level Interface (CLI) API's. The main characteristics of a CLI API are its lack of an SQL precompiler stage and the dynamic execution of SQL statements. Building an application with a CLI API doesn t require any knowledge about the target database or schema. Because DB-Library is the oldest and most popular Sybase API, this article will focus on porting Sybase DB-Library applications. Sybase has attempted to correct the deficiencies of DB-Library by introducing a new API with System 10 called Client-Library. The other Sybase CLI API widely used among Windows applications is the Open Database Connectivity (ODBC) API; however, it is available only under Windows. While Sybase has its roots in a CLI API, DB2 has its roots in a precompiled API. Just recently, however, IBM decided to change its emphasis on precompiled SQL and has added a new ODBC-compliant CLI API. Moreover, this CLI API is ported across all of its client platforms not just Windows. The CLI API is an obvious choice when migrating Sybase applications to DB2 because it closely resembles Sybase DB-Library. The CLI API isn't the only way dynamic SQL can be executed with DB2. Dynamic SQL can also be executed using embedded SQL (dynamic embedded SQL). However, using dynamic embedded SQL is a bit more complicated and requires use of the precompiler, which means you must have a DB2 database online. Using dynamic embedded SQL can be tedious; therefore, many developers end up writing their own small API to execute dynamic SQL. This is precisely why the DB2 CLI API was offered -- to prevent developers from re-inventing the wheel. The best reason to use dynamic embedded SQL is to avoid the small amount of overhead imposed with the DB2 CLI API. Also, if you have an OS/2 application that must run on the same server machine as DB2/2, you won t be able to use the new CLI API. Until DB2/2 version 2.0 is released, the OS/2 CLI API and Client Application Enabler/2 cannot co-exist with DB2/2 version 1.2. Whatever Sybase or DB2 dynamic API interface you use, the limitations of both products must be examined. With Sybase DB-Library, the maximum number of connections per process is constrained only by database limits and client memory, but only one cursor (not to be confused with a DB2 cursor) can be active per connection at a time. The number of connections per process limitation in DB2 is a little more complex. The CLI API under DOS and Windows will support multiple connections constrained by client and database server resources; however, the CLI API on other platforms such as OS/2* and AIX* only supports one connection per process. Support for multiple connections per process in the CLI API will be added for all platforms under the upcoming DB2 release 2.0. Embedded SQL only supports one connection per process for all platforms and there are no plans to change this limitation for release 2.0, so this factor may influence your DB2 API decision. The limitation on the number of cursors is easier to explain for DB2. All of the DB2 API s support multiple cursors per database connection. Once again, this cursor limit is dependent on machine resources and configurations. Static embedded SQL, although not quite an API, is what DB2 developers are most familiar with while Sybase developers are not. The closest equivalent to static embedded SQL within Sybase is stored procedures. With Sybase, stored procedures are compiled with the database on-line and a stored procedure query plan is stored in the database. DB2-embedded SQL also forms query plans for each program module into files called bind files. This precompiling stage is performed when the database is online. The big difference with Sybase stored procedures is that the DB2 precompiling stage must be performed in conjunction with compiling a C or COBOL program. If you are planning to use embedded SQL, be prepared to have the database online with a loaded schema during the product build and integration phase. Most Sybase developers are not accustomed to performing this step. Still a major difference remains between Sybase stored procedures and DB2-embedded SQL. In Sybase stored procedures, the entire procedure is executed on the server machine, and all intermediate results are run local to the server machine, leaving only the final result to be returned to the client. This problem is solved with DB2 s answer to stored procedures, also known as Database Application Remote Interface or DARI. DARI, combined with DB2-embedded SQL, is a perfect mechanism for converting Sybase stored procedures to DB2 stored procedures. All DARI does is call a C-embedded SQL function on a remote server. DARI can be more complex to implement than Sybase stored procedures, so it is advisable to form an application framework to convert Sybase stored procedures. Once this framework is in place, Sybase stored procedure conversion can occur more rapidly. All Sybase stored procedures critical to the performance of the Sybase application should be converted to DARI-embedded SQL on DB2. Other Sybase stored procedures that are not executed as frequently or are not as time critical can simply be converted to use embedded SQL or even dynamic SQL. Many operations that are done with Transact-SQL (Sybase SQL dialect) or even manually in Sybase, are implemented as API calls with DB2. Table 1 on page 13 compares how these database operations are performed in Sybase and in DB2. For Sybase, servers and network communication components (NETLIBs) must be manually registered in interface files. These Sybase interface files are named differently and have different formats, depending on each operating system. In UNIX, the file is called interfaces and is in ASCII format. For OS/2, the interfaces file is call SQL.INI and is in OS/2 INI format. In Windows, the server/network information is registered in WIN.INI. DB2 drastically simplifies this myriad of files by supporting a couple of API's on all platforms designed to register the server node, network communications component, and database. DB2 offers several API functions like these that provide a more complete solution for developers and especially for VARs. Another major difference in the way the DB2-embedded API operates is how isolation levels are specified. Sybase contains only two lock isolation levels, repeatable read and cursor stability. If the optional Transact-SQL keyword HOLDLOCK is used after each table in the FROM clause, the query will be in repeatable read mode; otherwise, the default is to use cursor stability mode. On the other hand, DB2 supports three different isolation levels which are controlled by calling the appropriate embedded SQL. When bind files are created after the precompiling stage, they are bound to the database with a specified isolation level. This determines the isolation level for each embedded SQL C or COBOL module. Obviously, the Sybase Transact-SQL HOLDLOCK keyword is not used or recognized in DB2. Sybase API extensions not supported in DB2 are multiple result set API support and bulk copy functions. Also not supported by DB2 is Sybase s alternate column support functions. These functions are usually prefixed with dbalt...0 and are used when a SELECT statement is issued with a non-ANSI COMPUTE clause. DB2 supports large text and binary large objects up to 32 K, but, unlike Sybase, does not require the use of separate text/image handling functions. Also, there are no callback functions for error handling in DB2. In DB2, only one error is returned from each API call or embedded SQL statement. This error is contained in the SQLCA (SQL Communications Area). Detailed text about this message can be retrieved from another DB2 C function sqlaintp( ). Once you start migrating your application to DB2, there should only be a couple of differences in your compiling and linking. If you have any static or dynamic embedded SQL, you must use the precompiler, and the stack size requirements tend to be much higher in DB2. DB2 documents 32K stacks for its Windows and OS/2 examples. This should give you a better understanding of the API differences between Sybase and DB2. DB2 offers several API options and it is important to consider each of them before beginning to migrate your application to DB2. This article was written by John Voelk, a lead software developer at Saros Corporation based in Bellevue, Washington and part-time Sybase/DB2 database consultant. He can be reached at (206) 450-1417 or via the Internet at johnv@saros.com. CLIENT/SERVER USERS: TAKE A LOOK AT SQL/DS! ============================================ Since 1981, businesses have been using SQL/DS as their mainframe relational database management system under VSE and later, under VM. Many companies are staying with, or buying, SQL/DS because it gives them large database relational capabilities on two very cost-effective mainframe operating systems. Yet, many companies, whether current or new users of SQL/DS, have not considered it as a very powerful, cost-effective part of a robust client/server architecture. This is one in a series of articles which will focus on using SQL/DS as an integral part of an overall client/server strategy, whether as client, server or both. These articles will be for those just kicking-the-tires as well as existing customers of SQL/DS. We will first examine the basics about SQL/DS as a part of client/server, illustrating how it can be architected along with DB2, DB2/2, DB2/6000, ODBC, DOS, Windows and even multiple occurrences of itself. Next, we will touch on issues related to client/server that may affect your implementation of SQL/DS as a part of client/server. One caveat for native VSE/ESA* customers at this time, SQL/DS can only be a server in that environment. However, SQL/DS server capabilities under VSE/ESA will be discussed. If you feel a need for the client capability under native VSE, keep asking IBM for it! Before we talk about SQL/DS as client or server, let s establish some basics about client/server. The popular definition of client/server gives the impression that only microcomputers or other non-mainframe hardware are considered capable of client/server. With this definition floating around, it is no wonder that SQL/DS and often mainframes as a whole have often not been considered as part of a client/server solution. Actually, the client/server computing model does not specify platform it is really defined as a client (requester of service) computing platform asking for data or a resource of a server (the provider of service). With that in mind, SQL/DS under VM or VSE can fit very well as a server to smaller platforms. Of course, with VM, SQL/DS can be a client and a server to larger or smaller platforms. Our first look at SQL/DS in the client/server world will be at a high level with SQL/DS as the server. Figure 1 shows a sample SQL/DS configuration using an insurance company in the Eastern United States. This is a high-level view of their architecture. (Subsequent articles will examine the details of this example and the component operations.) SQL/DS is installed under VM/ESA as the Master Server to the clients existing on the LAN. This example could also be implemented in the VSE/ESA environment. As the master server connected to the LAN, SQL/DS acts as a central safe containing the companies very large and highly confidential customer insurance records. Utilizing the security facilities of VTAM* and SQL/DS, data access can be limited at many levels, from unauthorized LAN communications to specific data in tables. Therefore, the safe can be opened to those authorized and tightly closed to others. Besides good central control of data assets, good systems management or housekeeping is much easier and more reliable than with many smaller platforms. Mature tools allow SQL/DS to be easily managed from a central location with minimal staff. These tools provide inexpensive and easy management of jobs like backup, recovery and performance tuning. SQL/DS communicates to the LAN clients via a gateway computer. This gateway computer is running OS/2, DB2/2 and DDCS/2 and talks to SQL/DS via the Application Program-to-Program Communication (APPC) protocol. This protocol provides a sophisticated communication medium that ensures communication integrity and notification of failure to applications using the IBM Distributed Relational Database Architecture(DRDA) protocol. From the LAN side, the PCs on the LAN are running the DOS operating system, the OS/2 operating system and the Windows operating environment. The PC labelled ODBC Apps is, in this case, running the Windows operating environment with Microsoft s Access** database system and the Lotus 1-2-3** spreadsheet program. Microsoft Access and Lotus 1-2-3 are supporting legacy applications written when the company was smaller. Now, however, they have been easily integrated into the SQL/DS client/server world via the ODBC capabilities of DB2/2. The PCs labelled DOS and Windows are running customer order and insurance quote applications built with COBOL. The applications have since been modified for the new environment and now access DB2/2 and SQL/DS. The applications are built to take advantage of the distributed database architecture with some data in SQL/DS and some in DB2/2. DB2/2 is running on the PCs labelled OS/2. The OS/2 PC on the far right of Figure 1 is running IBM s Visualizer Query and Chart products to summarize and chart the insurance sales and customer demographics data contained in DB2/2 and SQL/DS. The OS/2 PC on the left, the gateway machine, is running DB2/2 but does not have any local databases. It could, however, be used as a gateway and a fully functional database server. In all cases, applications (whether packaged products like MS Access or custom built in COBOL) talk to the database gateway machine via NetBios or APPC protocols. In this case, the OS/2 PCs are using APPC and the DOS and Windows applications are using NetBios. This brief introduction sets the stage for a more detailed examination in a future issue of DB2 family news of SQL/DS in a client/server environment. NOTE: SQL/DS is now called DB2 for VSE & VM. This article was written by David G. Odom who has held positions at Horizon Corporation, IBM and the U.S. Army. Mr. Odom is founder and principal of DGO Enterprises, a Tucson, Arizona firm specializing in strategic business and technology consulting, distributed relational database design, decision support systems, software testing and training. He is an experienced programmer as well as an experienced trainer in the SQL language, SQL/DS, QMF, OS/2 Database Manager and major PC-DOS and Windows products. He has been an invited speaker at numerous professional organizations, universities, IBM and the U.S. Army. Mr. Odom can be reached at: DGO Enterprises 9330 East 27th Street Tucson, AZ 85710 (602) 722-5641 E-mail: 72642, 2713 on CompuServe or DGOEnter on America Online. THIRD PARTY SOFTWARE UPDATE =========================== An important component of IBM s DB2 strategy is centered on third-party software partners or Independent Software Vendors (ISVs). It is critical for IBM to attract all of the popular third-party tools and applications that run against DB2 for OS/2 and DB2 for UNIX. This work is done by a large worldwide team dedicated to recruiting ISVs to support DB2 for OS/2 and DB2 for UNIX. This group manages the DB2 Developer Assistance Program that provides free software and technical support to ISVs who are actively enabling to DB2 for OS/2 or DB2 for UNIX. Currently, there are more than 1,000 vendors enrolled in this program. This team also regularly publishes a Solutions Directory which details all of the applications enabled to one of DB2 for OS/2 or DB2 for UNIX. The August 1994 version of this directory contained 302 software products, up from 192 in the April 1994 edition. The key statistics are as follows: (Note that some applications fall into multiple categories) By Type of DB2 Server Supported: * 211 DB2 for AIX/6000 enabled products * 273 DB2 for OS/2 enabled products * 30 DB2 for HP** enabled products By Application Type: * 24 Database Administration Tools * 39 Client/Server Connectivity Tools * 87 Application Development Tools * 52 Cross-Industry Applications * 100 Industry Specific Applications This directory is available in the IBMDB2 forum on CompuServe or from any IBM representative (free of charge). The next version of the Solutions Directory is published in December and will list even more products. Recently, our focus has been on vertical industry applications. Watch for a series of announcements in the next few months from a number of software industry leaders regarding their enablement to DB2. We are anxious to hear about popular packaged application software that you would like to see in our Solutions Directory. Please send a fax to Fraser Ferguson at (416) 448-4414 with your suggestions for products that we should try to include. Our developer assistance activities in Europe are getting a big boost. An increased support infrastructure is now in place. Also, we conducted a major ISV roadshow, where we visited 15 European cities in November and December, 1994. As a result, we expect to see many more European entries in our Solutions Directory in the coming months. CORELDRAW! 5: DATABASE ACCESS WITH EASE ========================================= CorelDRAW! 5 combines the graphics power of CorelDRAW** and the advanced publishing capabilities of Corel VENTURA 5** within an integrated user interface. With a revolutionary color management system, major performance gains and hundreds of improvements, CorelDRAW 5 retains the ease of use for which Corel is renowned. Enhancements to all the modules include the addition of OLE 2.0 drag and drop capabilities, a new ribbon bar and tabbed dialogs for quick access to commonly used functions. From short design-intensive documents to long text-intensive publications, CorelDRAW 5 provides the ideal tools for illustration and graphic design, publishing, photo-retouching and bitmap-editing, presentations, charting and animation. Complete with six fully-featured applications CorelDRAW 5, Corel VENTURA 5, Corel PHOTO-PAINT 5,** CorelCHART 5, CorelMOVE 5 and CorelSHOW 5 and a host of useful utilities including an introduction to ARES FontMinder,** CorelTRACE, Corel MOSAIC, CorelQUERY, Candela s advanced resolution-enhancing technology for Photo-CDs and Pipeline s internal PostScript interpreter,** CorelDRAW 5 is the most exciting value-packed graphics and publishing software available. Also included are over 22,000 clipart images and symbols, over 825 fonts and 100 royalty free photos on CD-ROM! Database Connectivity CorelQUERY 5, CorelCHART 5, and Corel VENTURA 5 are enabled for database access using Microsoft s Open Database Connectivity (ODBC) interface on Microsoft s Windows workstations. Support for ODBC allows these applications to access data from many databases including: * IBM DB2/2 V1.2 * IBM DB2/6000 V.0 * Oracle** 7.0 * Microsoft Excel** V5.0 * Microsoft FoxPro** V2.5 and V2.6 * Microsoft Access** * Borland dBASE** IV * Borland Paradox** V3.5 and V4.0 * Other ODBC enabled Database Management Systems (DBMSs). Corel in the Client/Server World Since CorelQUERY 5, CorelCHART 5, and Corel VENTURA 5 are Microsoft Windows applications, these applications participate in the client/server world on Windows client workstations. In this environment, the database server (IBM DB2/2 and IBM DB2/6000 for example) would be installed along with Windows clients and communications software. When IBM s DB2 ODBC driver is configured as the Data Source in CorelQUERY, access to DB2/2 and DB2/6000 data is seamless and transparent. Similarly, CorelQUERY 5 can access other popular databases using the ODBC interface. CorelCHART 5 and Corel VENTURA 5, through CorelQUERY 5, allow users to access and manipulate data for use in business charts and documents. In addition to local databases, CorelQUERY can access corporate data for use throughout the entire enterprise. CorelQUERY 5 CorelQUERY 5 is a powerful tool that lets you extract database information for use directly in CorelCHART 5 or Corel VENTURA 5. Additional features include: * Add New or Select the ODBC data source * Create and modify SQL queries * Change font face, size, style and color * Support for Dynamic Data Exchange (DDE) allows you to set up links between spreadsheet applications and data tables * Support for Object Linking and Embedding (OLE); CorelQUERY acts as a server only. CorelCHART 5 CorelCHART 5 is a spreadsheet application with powerful tools for managing and manipulating data and representing that data in colorful graphical formats. With CorelCHART, you can represent your business data as visually exciting ideas that capture the imagination of your audiences. Additional features include: * 10 new chart types, including gantt, radar, polar and bubble charts, bringing the library total to 90 * Over 250 new spreadsheet functions, including mathematical, engineering, financial, logical, and statistical * Ability to apply styles when formatting spreadsheets. CorelCHART supplies 16 different styles * Direct access to CorelDRAW fills and outlines for charts and the data manager * Improved data import and export SQL through ODBC * Larger spreadsheet size features 16,384 rows and 256 columns. Corel VENTURA 5 Corel VENTURA 5 extends the boundaries of desktop publishing with an intuitive new interface and powerful architecture that supports both structured and freeform documents. Additional features include: * Corel VENTURA s interface now conforms to Windows and CorelDRAW standards * Redesigned Windows interface with new tabbed dialogue boxes and Roll-Ups * Support for OLE 2, including drg and drop and in-place editing * Improved text editing using standard Windows text-editing conventions * Irregular text-wrap around graphic objects * Rotation of text and graphics in 0.1 degree increments * Built-in story editor for faster word processing * Support for the full ANSI character set * Advanced color and prepress capabilities * Access to CorelDRAW fills and outlines * Improved import/export filters for text and graphics * Improved connectivity: SGML, ODBC, WORD 6 and WordPerfect 6 support * Improved font management with the elimination of font width tables * Enhanced file management. Corel VENTURA is now publication, instead of chapter, based. Users can apply smart copy and smart move to publications, and can load selective components of their publications. This article was written by Lou Pedron, Associate Product Manager at Corel Corporation in Ottawa, Canada. Lou was formerly a DB2 product planner at IBM and the managing editor and creator of DB2 family news. CORELWORLD CONFERENCE AND EXHIBITION ==================================== Corel Corporation will hold the CorelWORLD, a high technology conference and exhibition scheduled for June 1-3, 1995 at the Ottawa Congress Centre, Ottawa, Canada. Designed to coincide with Corel s $2 million World Design Contest and the launch of CorelDRAW 6, the conference will host approximately 600 participants. Industry writers, trainers, and past design contest winners will lead workshops and seminars on CorelDRAW, Corel VENTURA, and Corel PHOTO-PAINT. In addition, the conference will feature special hands-on computer training sessions for Corel products. The CorelWORLD exhibition is open to the general public and will showcase Corel s latest products. Corel is inviting other software and hardware manufacturers to demonstrate their latest products at this event. The exhibition will be held in conjunction with the Canadian Advanced Technology Association (CATA). A special customer service number has been set up for enquiries about CorelWORLD. Interested conference attendees should call (613) 728-3733 or FAX (613) 761-9176. For more information about CorelDRAW 5: COREL CORPORATION The Corel Building 1600 Carling Avenue Ottawa, Ontario Canada K1Z 8R7 In the USA and Canada: TEL: 1-800-778-3332 FAX: 1-408-644-2000 Worldwide: TEL: 1-613-728-3733 DB2 FASTSTART FOR ISVs: A RUNAWAY SUCCESS ========================================== The first DB2 Faststart conference for ISVs was held in Toronto from November 8 to 10 at the Inn On the Park. Almost 200 attendees, from over 123 companies, participated in this technical exchange. These companies create software for DBA tools, application generation, systems and performance management and for industries such as Banking, Finance, Manufacturing, and Health Care. The conference was organized to satisfy the demands of software vendors for information on Version 2 of DB2 for the OS/2 and UNIX1 operating systems. Technical education on the new and enhanced facilities gave software developers the information they need to enable their products in time for general availability. The event was an overwhelming success; many vendors said they planned to exploit the advanced features of Version 2 in their product. Janet Perna, Director of Database Technology, kicked off the event with a session on the success and acceptance of the DB2/2 and DB2/6000 in the marketplace. She discussed IBM s position as one of the largest software vendors and described our intent to maintain leadership in the relational database market across all platforms. During the three days of the conference, development team leaders covered in detail all new functions and major enhancements. Topics included the: * Performance improvement * Parallel database * Multimedia extensions * Enterprise wide transaction capability * New SQL * Enhanced systems management interfaces * Large database strategy * ODBC/CLI improvements. Presentations by the software developers and a panel discussion gave the attendees an opportunity to talk to their peers in IBM. Small breakout sessions on individual topics such as Object-Oriented Extensions, Performance, Porting,and DRDA focused on areas of common interest. Discussion and dialogue were intense. Featured at the conference were demonstrations of: * VisualAge* * Visualgen * DB2 Visual Explain * DB2 Performance Monitor * Visualizer * OS/2 WARP* * Datajoiner and DB2/6000 Version 2 Everyone had a chance to view the full range of IBM products for creating an integrated working environment. Demonstrators were kept over time to accommodate everyone who wanted to see the product line and to answer questions raised by the enthusiastic audience. The DB2 for OS/2 and UNIX systems demonstrations highlighted major new capabilities. For example, the multimedia demonstration replayed video and audio as recorded into the database. The recursive SQL feature showed how to solve a manufacturing bill of materials problem, and demonstrated a least-cost airline-routing simulation. A major conference theme was understanding the needs of the ISV community as they related to DB2. They gave our development and planning team invaluable information about their businesses. People expressed their delight about and excitement with our future direction and the opportunity to participate in it. DB2 developers were ecstatic at the positive response from their colleagues in other companies. Written by Jay Lennox and Raj Joshi, Vendor Relations Managers in the Toronto Laboratory. THE INTEGRATION OF NETWORK AND DATABASE MANAGEMENT ================================================== Have you ever wanted to monitor databases and system resources from the same console? Well, you will soon be able to do just that with Version 2 of DB2/2 and DB2/6000. In this version, IBM is introducing an SNMP Agent which will enable management of DB2 from an SNMP management product. A Brief Introduction to SNMP (Simple Network Management Protocol) SNMP is a standard protocol used for network and systems management well-accepted in the network management community, and many products from many vendors support it. For example, IBM s NetView* for AIX (formerly NetView/6000), IBM NetView for OS/2, HP OpenView,** and SunNet Manager** all use SNMP. In fact, SNMP has been adapted by the Internet Engineering Task Force (IETF) as RFC 1157. SNMP differs from communication protocols such as TCP/IP in that it is a management protocol. It allows a given managing node (for example, a system administrator s console) to communicate with any number of managed nodes. For example, the managing node can request the current status and level of activity from each managed node. Managed nodes can include hardware devices, such as network routers and hubs, and software, such as operating systems and now databases. How Will DB2/2 and DB2/6000 Fit with SNMP? As mentioned above, Version 2 of DB2/2 and DB2/6000 will include an SNMP Agent. This agent will allow a systems management product such as NetView for AIX to query and monitor database status, database configuration parameters, database performance information, and other database information. DB2 will also be able to send SNMP traps in the case of critical database errors. For example, from your NetView for AIX console, you will be able to monitor the size allocated and actual size used for given DB2/2 or DB2/6000 server(s). You will also be able to use the facilities of NetView to help you manage, interpret, and respond to the information fro the database agent (by graphing results, defining thresholds, or defining automated responses to exception conditions, for example). Database administration tools such as the new DB2 Performance Monitor for OS/2 and AIX will also be able to take advantage of the SNMP Agent. And DataHub,* IBM s strategic database systems management solution, will also provide integration with NetView. Advantages The DB2 SNMP Agent provides you with: * A single image of database and systems management. With the DB2 SNMP Agent and a management product such as NetView for AIX, you will be able to monitor the health of not only networks and operating systems, but databases as well. * Standards-based management of DB2/2 and DB2/6000. As mentioned above, SNMP is a standard in the network and systems management world. The DB2 SNMP Agent allows Version 2 of DB2/2 and DB2/6000 to participate in these SNMP frameworks, including but not limited to IBM NetView for AIX and IBM NetView for OS/2. In addition, the DB2 SNMP Agent is based on a relational database management standard drafted by a working group composed of IBM and other leading database vendors. This standard, the relational database (RDBMS) Management Information Base (MIB), has been accepted by the IETF as RFC 1697. The standard facilitates common SNMP management of databases regardless of vendor. Perfect Solution for the Client/Server Environment Our customers have indicated that integrating database management into your systems management environment is key to your ability to manage hundreds and thousands of remote servers in a client/server environment. The DB2 SNMP Agent continues IBM s leadership in providing database management solutions that are ready for prime-time, mission-critical applications. This article was written by Laurence Leong, Product Planner, IBM Software Solutions Toronto Lab. DB2/6000 and DB2/2 VERSION 2: SYSTEMS MANAGEMENT AND DATABASE ADMINISTRATION FACILITIES ============================================================== Ready to make the most of the many new and enhanced interfaces for systems management and database administration available on Version 2 of IBM DATABASE 2 AIX/6000 (DB2/6000) and IBM DATABASE 2 OS/2 (DB2/2)? To help you do just that, we will be publishing a series of articles in this and future editions of DB2 family news. The first article deals with the capabilities and improvements of backup and recovery, including maximizing data availability, table space backup, parallel processing, and HSM support. Backup and Recovery Backup and Recovery in Version 2 of DB2/6000 and DB2/2 gives you high-performance, and additional improvements to help you manage databases of hundreds of gigabytes. * Table Space Backup with Parallel Processing Table space backups are now available. You can back up one or more table spaces in a single backup run. The complete scope of backups, from table level to RI set, are supported. If your RI set spans multiple table spaces, they can be backed up in a single invocation. The table spaces remain online during the backup to allow continued access to the data. Our new parallel backup exploits multi-processors and multiple output devices, dramatically improving performance and reducing elapsed times. Through the use of multiple buffers and multiple agents, you can write in parallel to your output devices. For smaller configurations and for compatibility with Version 1, you still have full database backup, either online or offline. Full database backups also benefit from the parallel backup performance improvements, with a significant reduction of elapsed time. * Maximizing Data Availability For failures attributed to a particular table space, only that table space becomes unavailable. All other data remains online. Table spaces that are taken offline can be brought back online by doing a recovery, after the problem is corrected. Recovery can be as simple as a roll-forward recovery, or a restore of the table space followed by a roll-forward. In the event of a complete systems failure, such as a power outage, crash recovery brings the database back to a consistent state. RESTORE, like BACKUP, implements parallel processing. If a failure occurs and recovery is required, the duration of your outage is lessened. Recovery from full database backups continues to be supported, with shortened elapsed time when the new parallel capabilities are exploited. * Archive Logging To recover to the point of failure, you need to enable roll-forward recovery, and archive the full logs. These steps are particularly important when you are updating critical data. As an option, you can turn roll forward recovery off; crash recovery is still supported, and you can use offline backups to provide recovery to the time they were taken. * HSM Support and More You can utilize hierarchical storage management (HSM) systems in the new backup and restore. Version 2 supports ADSTAR* Distributed Storage Manager (ADSM), when installed on the database server. Vendors can enable their hierarchical backup and restore solutions by enabling to supplied APIs. These HSM systems can use the parallel aspects of backup and restore for improved performance. (For more on ADSM, please see ADSM: An Industrial-Strength Storage Management Solution, below.) A recovery history file keeps a record of all backup, recovery, load and unload activities. This will help you in recovery activities. You can use a supplied tool to query and manage this file. All backup and recovery commands can be executed from a location remote from the server. Now you can manage a network of databases on geographically dispersed servers from a single site. For point-in-time table-level recovery, use the UNLOAD - LOAD functions or the EXPORT - IMPORT functions. Automated Backup and Recovery Version 2 of DB2/6000 and DB2/2 makes major improvements in backup and recovery: * Table space backup and recovery * Parallel processing * High data availability * APIs that will allow hardware and media management (HSM) solutions to be integrated * A recovery history file to aid in recovery activities * Remote execution of backup and restore commands. With these improvements you, and vendors, can automate the backup and recovery solutions that you need, with reliability and integrity. This article was written by Jay Lennox, Advisory Planner and Brenda Lam, Team Leader, Backup and Restore, both of the IBM Software Solutions Toronto Lab. This article will also appear in an upcoming issue of Platinum Edge magazine. ADSM: AN INDUSTRIAL-STRENGTH STORAGE MANAGEMENT SOLUTION ========================================================= DB2/6000 and DB2/2 use ADSM (ADSTAR Distributed Storage Manager) to provide an industrial-strength storage management solution. ADSM, a client/server program product, is IBM s solution to enterprise-wide distributed storage management. It provides highly automated, centrally scheduled, network-based backup and archive functions for workstations and LAN file servers. ADSM supports a wide variety of IBM and non-IBM clients and servers, and addresses the need for customer asset protection and data availability for distributed environments. A variety of utilities can be used to back up databases. Many techniques for backing up databases require use of a combination of utilities, including software management products such as ADSM; the utilities that come with the database products themselves; and operating system utilities. Using ADSM (by itself or in conjunction with the RDBMS, AIX, or OS/2 utilities) provides storage management, repository management, platform independence, and support of heterogeneous data. ADSMs storage management includes the ability to define storage hierarchies (of disk, tape, and optical), to automatically migrate data through the storage hierarchy, and to automatically delete expired data and reclaim unused space. Repository management means that facilities are provided that allow the user to identify and locate backed up and archived copies of data. Platform independence means that ADSM can work on multiple platforms. The ADSM client runs on Macintosh,** OS/2, DOS, Windows, NetWare, AIX, SUN, HP-UX, SCO 386, and DEC ULTRIX.** The ADSM server runs on OS/2, AIX, VM, MVS, OS/400, and is announced for VSE/ESA, and has a statement of direction for HP and SUN. Finally, heterogeneous data allows for the back up of many different types of data. In fact, native ADSM (or through use of its API) can back up any type of data it is sent! DB2/6000 and ADSM In examining the specifics of how ADSM can be used to back up DB2/6000 and DB2/2, let us first consider DB2/6000. Figure 1, below, shows the recommended technique to back up DB2/6000 with ADSM. DB2/6000 uses the ADSM API which means that the user program (DB2/6000 in this case) can directly send information to ADSM to manage. The underlying physical structure of the database (JFS or raw devices) is handled by DB2/6000 and the type of backup (for example, online, offline, at the table space level) is also determined and controlled by DB2/6000. The DB2/6000 backup utility is integrated with ADSM. You can use either the DB2/6000 backup command line or graphical interface (or your own C Language program) to select ADSM as the destination for the backup image. Both online and offline backups can be performed with ADSM. With DB2/6000 Version 1, the backup utility backs up the entire database. Here is a sample DB2/6000 backup command that directs the output to ADSM. An online backup is performed for the IBMSAMPL database, and the resultant image file is sent to ADSM server storage. $ db2 backup database ibmsampl online use adsm Figure 2 shows a sample DB2/6000 backup operation using the graphical DBA interface. An online backup is selected for the IBMSAMPL database, and the resultant image file is sent to ADSM server storage. DB2/6000 Version 2 (not yet available at the time of writing) provides significant enhancements to its backup/restore utilities. With Version 2, databases can be partitioned into parts called table spaces, and backup and recovery can be performed at the table space level. If a table space contains a single table, the backup or recovery is equivalent to a table level backup or recovery. Table spaces can be online or offline during the backup process. During recovery, all table spaces, other than the one being recovered, can remain online. Another enhancement to the backup/recovery process is parallel backup and recovery. It is possible to perform the backup or recovery of a database or tablespace in parallel to or from multiple devices. This can drastically reduce the elapsed time requirements. DB2/2 and ADSM Let us now consider how ADSM can be used to back up DB2/2. As shown in Figure 3, there are several techniques that can be used to back up DB2/2 with ADSM. We offer techniques (1) and (2) because the current DB2/2 products (V1.1 and 1.2) do not use the ADSM API. DB2/2 Version 2, announced but not available at the time of this writing, will use the ADSM API, and therefore, technique (3) will then become the recommended technique, as it is with DB2/6000. Technique (1) uses the DB2/2 backup utilities and directs the output to a file; then ADSM is used to back up the file. Technique (2) uses ADSM to directly back up the files that make up the DB2/2 database. The advantage of technique (1) is the ability to perform backups at the level provided by the DB2/2 utilities; for example, we will describe a DB2/2 incremental backup and a special quiesce option. The advantage of technique (2) is that no intermediate file is created, but the database must be offline to ensure a consistent copy. DB2/2 Version 1.1 provides a utility called backup for offline backup. Two types of backup are provided: a full backup of the entire database and an incremental backup of only the changed files (called changes only). A user exit program can be used in conjunction with the backup utility. It allows DB2/2 to use OS/2 commands to interact with devices (or ADSM) directly. You cannot perform a changes only backup if you enable the user exit program. The changes only backup relies on OS/2 system commands to determine which files are changed and therefore should be backed up. If you enable the user exit program, you choose other ways to save your data, such as ADSM or the copy command. DB2/2 Version 1.2 provides a significant enhancement to the Version 1.1 backup utility. The DBA can perform a backup of a database with active connections, using a new quiesce option. This means that users, in addition to the DBA, remain connected to the database. You can think of this as somewhere in between an offline and online backup. The quiesce option ensures that all activity on a database has been brought to a halt before a backup is taken. The backup waits until current transactions complete and prevents any new transactions from starting. Once a quiesced state has been established, a backup can be taken. After the backup is complete, user transaction activity resumes. DB2/2 Version 2 provides significant enhancements to the backup/restore utilities. Version 2 adds the backup support already available in DB2/6000, including online backups and further integration with ADSM services by using the ADSM API. In addition, table space backups and parallel backup and recovery, as described earlier, are provided. Here is an example using technique (1) to back up DB2/2. Figure 4 shows the DB2/2 Backup Database panel; we have selected to back up the entire sample database and put the resultant files on the C drive. Two output files are created in the C:\backup directory: BACKUP.001 and CONTROL.001. Then we use the following ADSM command to back up the database image and control files (BACKUP.001 and CONTROL.001). C:> dsmc selective c:\backup\*.* -password=mars Trends and Directions DB2 Parallel Edition Version 1 is a new member of the IBM DB2 family. This new relational database for the AIX RISC System/6000, Scalable POWERparallel 2 systems and HACMP/6000 (IBM High Availability Option) systems extends database quality, functionality, reliability and robustness to parallel multi-node environments. DB2 Parallel Edition for AIX/6000* Version 1 is a portable SQL LAN server designed for the open systems marketplace. It obtains significant performance improvement as the number of processors increases. The initial release of DB2 Parallel Edition for AIX/6000 Version 1 focuses on databases implemented on configurations of up to 128 nodes. Since DB2 Parallel Edition for AIX/6000 Version 1 appears to users as a single database server, all the benefits of parallelism are transparent to the current SQL client API. Existing applications do not need to be re-written. Most client APIs are supported. ADSM backs up DB2 PE on a per-node basis. Restore involves restoring and rolling forward each node. Future ADSM enhancements would provide faster performance by having parallel ADSM servers. The first customer shipment of IBM DB2 Parallel Edition for AIX/6000 Version 1 will be by December 30, 1994, to a selected set of customers. Where to Get More Information The following are some of the publications which provide more information on ADSM: * Using ADSM to Back Up Databases, GG24-4335 * ADSM Presentation Guide, GG24-4146 * ADSM General Information, GH35-0114 * DB2/6000 Administration Guide SC09-1571 You can also contact your IBM marketing representative or your local IBM branch office. This article was written by Cyndie Behrens, Advisory Systems Engineer, International Technical Support Organization (ITSO), in San Jose, California.