The following articles are in the September 1994 issue of DB2 family news. - Welcome from Janet Perna - The Promise of Online Help - IDUG's Third Annual European Conference - Need Customized Training? Call IBM! - DB2/2 Keeps Pace with the Fastest in the Indianapolis 500 - Performance and Tuning Technical Tips - IBM Delivers DB2 on Competitors' Platforms - Common Segment Problems with DB2/VM (SQL/DS) - DB2 for MVS/ESA V4 - Service for DB2/2 and DDCS/2 - DB2/2 V1 and DDCS/2 V2 Select and Service Paks - IBM Support for Customer Migrations to the DB2 Family - DB2/2 Help as Near as Your Telephone - Enabling PowerBuilder Version 3.0a for DB2/VSE & VM IBM - VisualGen Enables Rapid Application Development - DBA Tools Become More User Friendly - IBM Visualizer Product Family - What's New with DB2/VSE & VM (SQL/DS)? WELCOME TO THE SEPTEMBER EDITION OF DB2 FAMILY NEWS =================================================== I hope that you've had an opportunity to get away from work for a while and enjoy a little rest. With the coming of September, our thoughts are back to the business at hand and I want to share with you some of the accomplishments we made during the summer. Sales of DB2/2 and DB2/6000* are booming! As of June, more than 125,000 copies of DB2/2 have been sold, while over 1,000 DB2/6000 new users joined the family in 1994 alone! We re proud of our sales record and proud that you have trusted IBM and DB2 to provide your database solutions. In June, we released Version 1.2 of DB2/2. It continues our strategy of bringing you the greatest function for the least price. Now you can use the IPX/SPX communications protocol, Named Pipes, ODBC, and more. And you get a full set of softcopy publications (on the CD-ROM version only). In addition, we've shipped DB2 for HP-UX**, our first port to a non-IBM UNIX** system. And we began the beta testing of DB2 for Solaris** Operating Environment. In July, we released a refresh of DB2/6000 Version 1.0. More than just a refresh, the product has been enhanced so that you can now use SNA Server/6000 Version 2.1, Tightly-Coupled Transactions, New Country support, and much more. Just this week, we announced a new version of the granddaddy of the DB2 Family, DB2 for MVS. The Version 4 announcement is one of the most exciting we ve ever made for this product. Read about it, starting on page 1. This will be an exciting autumn. We are fully engaged in the beta testing of DB2/6000 Parallel Edition Version 1.0 and we re looking forward to starting the beta tests of DB2/6000 Version 2.0 in October. Throughout the fall, the DB2 Family of database products will be on display at various tradeshows and other group presentations. I invite you to attend these sessions, ask all the tough questions, challenge our competitors, and see if you agree that IBM is your supplier of choice. THE PROMISE OF ONLINE HELP ========================== Are you overwhelmed with books filling your office? Are you tired of looking through several manuals, only to be directed to other books that you may not even have anymore? If you feel like you've had enough of the printed page, you may want to think more about online help. The switch to online help is gaining momentum, and the reasons are not limited to the environmental problems of shrinking forests and overflowing landfill sites. With improved hardware, online help can be a better source of information than books. There are things you can do with online help that you simply cannot do with books, such as searching for a string of text or using hypertext links to access additional information on a topic. For example, linking to related information in another book could be as easy as clicking on a hypertext link much easier than looking for the other book and leafing through its pages. When enabled, online help appears with the press of a button or selection of a window control. In the past, your request for help meant waiting for the information to appear. All too often, help files took up much disk space on smaller machines. Fortunately, with today s powerful hardware, wait time is reduced, and help files now take up just a fraction of your hard drive. CD-ROM technology brings you tremendous volumes of information stored on one disk at less cost. As technology continues to expand, online help becomes more usable and more relevant. And with the use of high-resolution color monitors, online help is more readable and attractive. The advent of multimedia promises even more enhancements; the combination of sound, pictures, graphs, animation, and movies will help you better understand your software. By incorporating usability techniques, writers of online help are improving their product with meaningful tables of contents, better online navigation, and a writing style more geared to the online medium. This focus on usability has meant that information is increasingly targeted for your specific needs. Some products now have tutorials that allow interactive learning with their product. One day, applications may anticipate your actions providing suggestions as soon as you need them! Online help can be moved around or changed within one machine, or among several machines. As a basic example, the desktop clipboard can be used to copy and paste your favorite sections of help into a personal scrapbook for you and your colleagues. Another example is an option that lets you add your own notes to the help. As computers continue to be linked together by LANs and modems, there will be increasing opportunities for enhanced help systems. Already, you can link into information featuring the latest developments and software releases available in your field of expertise. You can even maintain up-to-date versions of your help by dialing in. (World Wide Web Mosaic currently pulls the latest information in from a server whenever you are connected and need help.) Hardcopy information will never die completely. Information on installation, for example, cannot easily be placed online if the machine is not yet functioning. Furthermore, some hardcopy software manuals will not lose their popularity. Educational books containing overall concepts, for instance, are convenient to learn from, and can be taken home. Although books like these will always be with us, many more books will become available online, where they are simply more convenient to use. I you tried using online help in the past and didn't like it, try again. As writers learn how to develop usable, meaningful help using new online media, you may find that online information is what you need to use your software quickly and effectively. Written by Steve Gaebel, Senior Associate Information Developer in the DB2 Workstation Development group. IDUG's THIRD ANNUAL EUROPEAN CONFERENCE ======================================= The world of information changes quickly. Having the most up-to-date information at your fingertips is critical to the success of your company and your career. At the next International DB2 Users Group conference, October 10 to 14 at the Nice Acropolis in Nice, France, you'll benefit from more than 40 leading-edge sessions addressing the entire DB2 Product Family and presented by knowledgeable, thought-provoking industry speakers. A wide variety of DB2 technical challenges and solutions for new, intermediate and advanced DB2 Product Family users are addressed by experts from around the globe. More than 500 IT professionals at all technical levels are expected to attend. Don t miss your chance to network with expert users, vendors and consultants from around the world. There are advanced sessions to meet the needs of experienced users, as well as many courses for those just starting out with DB2 or migrating from other DBMSs. A wide variety of leading-edge presentations have been carefully selected and address topics including: Application Development DB2/2 DB2/6000 Data Modeling CASE Client/Server (Distributed) Database Administration Systems Programming Management Emerging Technologies Meet and discuss problems, exchange ideas and share solutions during the Open Forum Discussion Groups. In-depth information on topics of general interest are offered, including: DB2 Enterprise Server, Richard Yevich, RYC, Inc. A Tour of Parallel, Don Haderle, IBM Software Solutions Division Client/Server: Myth, Magic and Reality, Brenda Castiel, Ernst & Young Be a part of two interactive panel discussions with IBM DB2 developers and DB2 Product Family users where you ll get answers to your tough technical questions. Each panel includes experts known for their high-level skills and unique experience. The IBM panelists are: Don Haderle, IBM Software Solutions Division Roger L. Miller, IBM Santa Teresa Lab Bill Wong, IBM Canada Ltd. Laboratory Bonnie K. Baker, IBM Application Solutions Consulting Jay Yothers, IBM New this year at IDUG s European Conference are three concurrent one-day seminars offering today s DB2 professionals the latest, in-depth analysis of the hottest relational technology topics, presented by the industry s leading instructors: Things I Wish Someone Had Told Me Eight Years Ago, Bonnie K. Baker, IBM Application Solutions Consulting DB2 in a Client/Server World, Dan Farrar, PLATINUM Education DB2 Version 3 Transition, Richard Yevich, RYC, Inc. These outstanding seminars are held Friday, October 14 from 8:30 a.m. to 4:30 p.m. (A separate registration fee is required and includes all program materials and meals offered throughout the day.) Act now to receive the early registration discount! NEED CUSTOMIZED TRAINING? JUST CALL IBM! ======================================== If saving time and money appeals to you, why not investigate customized education from IBM? Customized classes can tailor training to deliver just the education your company needs. Why should you pay for an off-the-shelf class containing material you re not interested in? asks Linda Stanislawski, an IBM custom education marketing rep. Customized classes allow our clients the flexibility to pick the content and format that best suit their employees needs. For example, when a company makes a major shift in technology, many employees need to be retrained, says Stanislawski. However, most public classes won t account for the employees previous knowledge or the company s special circumstances. In a customized class, we can eliminate information that everyone already knows, create modules appropriate for each skill level and audience, and go right to the heart of what each individual needs. It saves everyone time, and saves the client money. We can also make other accommodations to meet the company s needs, such as breaking classes into shorter segments, scheduling them after working hours or on weekends, says Nancy Pinkston, also an IBM custom education marketing rep. We've arranged training boot camps where we address a different topic each day. We can offer endless variations and suggestions to help make the training as convenient and meaningful as possible. One Call Does it All: 312-245-6691 IBM makes it easy to customize training. You simply call the IBM Custom Education Hotline (312-245-6691) and leave a message about the types of courses you re seeking. A custom education marketing rep will return the call within 24 hours to discuss the details. He or she will then send a proposal containing a training agenda, course price and date of delivery to you within a week. All of the custom education reps are ex-instructors, able to translate a customer s needs into training plans, says Pinkston. That s especially helpful when we've cut and pasted courses together we can arrange the pieces so they re presented logically. Our experience is also helpful in gathering the right instructors for each job. We have high expectations of what a good quality training offering should be, and by being involved in creating the education, we can ensure the best for each client. We're often asked to create entire training road maps to cover long-term education, Pinkston continues. For example, if a customer is making a big hardware or software investment, we can put together an education planning vehicle to cover the company s needs for months or even years. That s another instance where our background helps. Training by IBM It also helps to have training conducted by IBM. We have access to the people who wrote the code, says Stanislawski, and that s important to many customers. Because we re IBM, we can arrange for course participants to do things that our competitors can t offer. For example, for one customer, we arranged a conference hook-up with developers in our lab. That s what that customer needed and wanted most. My suggestion to those thinking about future training is to consider customization, she says. If you don t find exactly what you re looking for in a public class, just give us a call at 312-245-6691. DB2/2 KEEPS PACE WITH THE FASTEST IN THE INDIANAPOLIS 500 ========================================================= When Al Unser, Jr., crossed the finish line in May s Indianapolis 500 auto race, it was the IBM DATABASE 2* OS/2* (DB2/2*) database manager that tracked his performance for all 200 laps of the 500-mile race and clocked him in as the winner. This marks the first time the Indy 500 relied on database technology to track all of the statistics involved in this high-speed, high-stakes contest. The DB2/2 product is a 32-bit, OS/2-based relational database management system designed for customers in a client/server LAN environment who want to take full advantage of the IBM industry-leading database technology. Available from IBM s Software Solutions Division, DB2/2 runs on the IBM Personal System/2* (PS/2*) or compatible PC. Indy 500 officials used it to instantly obtain the actual times and speeds for any driver at each of 22 points along the 2.5-mile oval track. The DB2/2 database also recorded the vital statistics of each car and driver, including time spent in the pit, real-time race positions, and time intervals between the race leader and following cars. From each of the 22 points along the speedway, information was fed into the IBM DB2/2 database through an advanced cabling system embedded under the track. Every car carried a transmitter that was activated whenever the car passed one of the points. Within seconds after a car passed a point on the track, DB2/2 queries could generate a report. This allowed racing officials to monitor, for example, the order and speed of cars during a yellow flag caution period (when a speed limit is imposed and passing is prohibited because of a collision or debris on the track). Qualifying Runs For 16 days prior to the race, 50 drivers in 78 cars drove more than 17,000 practice and qualification laps, seeking enough speed to earn a race day starting position. Only the fastest 33 drivers in four days of official four-lap qualification runs would compete on race day. During each of the qualifying runs, the DB2/2 system collected, organized and provided details on each driver s lap times, maximum straight-away speed and average corner speed. Every day, all day long, the system generated reports that each team used strategically to improve the performance of car and driver. The IBM DB2/2 software ran without a glitch, said Art Graham, director of timing and scoring for the United States Auto Club (USAC), the governing body of the Indianapolis 500. W depend on the IBM database technology as a fundamental piece of our advanced race information system, which provides accurate and comprehensive car and driver information instantaneously. The Sport of Technology IBM has been a part of the Indianapolis 500 since 1927, when the company s founder, Thomas J. Watson, Sr., leased key punches and tabulators to USAC to track timing and scoring. IBM products have also played a big role in the Olympic Games for the last 60 years and have served as official scoring and information systems for the Professional Golfers Association (PGA) Tour, the National Basketball Association (NBA) and the U.S. Open tennis tournament. PERFORMING AND TUNING TECHNICAL TIPS: TOOLS FOR A DESERT ISLAND =============================================================== Q. If you were stranded on a desert island, with only two DB2 for MVS performance tuning tools, which two would you pick? A. Well, desert islands are always tough, but it would go a lot better if I had a way of monitoring and tuning my buffer pools . . . and then again, I'd just have to have the CREATE INDEX. The DB2 Version 3 product has some nice new enhancements to those very basic tools: buffer pools (now 120 of them!) are much easier to monitor and control with the powerful V3 DISPLAY and ALTER support. And CREATE INDEX has added a DEFER option that permits a quick CREATE followed by a RECOVER INDEX. First, consider the buffer pool enhancements. Of course buffer pools are a critical tuneable resource, and with as many as 120 of them ( Virtual Buffer Pools plus Hiperpools), there are many more opportunities for error. Happily, it s possible to get instant response both to the process of modifying them using ALTER, but also when interrogating their state using DIS BPOOL . Modifying bufferpools no longer requires STOPping then STARTing DB2 commands; the new ALTER capability is strictly online. Once again, the DB2 for MVS product is moving closer to 7x24 operation. The DISPLAY BPOOL command returns lots of good information: the size and type of your bufferpools, your page externalization and stealing thresholds, but also some very meaty measurements like the number and speed of I/Os, the number and type of thresholds hit, and the number of parallel I/O requests and degradations. DISPLAY provides output at both the buffer pool and pageset levels of granularity. It is so comprehensive that it s really a lot more like a powerful performance monitor than ordinary display output. And you don t even have to start performance trace to get it! The ALTER BPOOL command (not an SQL statement) dynamically modifies your bufferpools. For example, you can create, delete, shrink, or expand a buffer pool easily. There are keywords that modify the page externalization and stealing thresholds, and even controls whether MVS can discard data cached in hiperpools. Q. And what about the new DEFER option on CREATE INDEX? A. This is a nice V3 speed-up for CREATE INDEX. The DEFER option places the index definition into the catalog, the index in Recovery Pending (RECP) state and then stops. You proceed with a RECOVER INDEX, which has the advantage of using DFSORT to sort the index keys; DFSORT is faster than RDS sort for LARGE amounts of data. You can get more insights and technical tips through DB2 training offerings by IBM. For further information about a class at our place or yours, please call 1-800-IBM-TEACH (1-800-426-8322). On second thought, maybe you d have other things besides performance tuning tools on your mind if you were stranded on a desert island like food . . . and water? IBM DELIVERS DB2 ON COMPETITORS PLATFORMS ========================================= IBM is delivering its industry-leading DB2 technology on the Hewlett-Packard Company s HP 9000 family of business servers and workstations: DB2 for HP-UX. IBM also announced plans to move DB2 to Sun Microsystems Solaris Operating Environment. These new members of the DB2 family are functionally equivalent to the DB2/6000 relational database offering on the IBM AIX* platform. Customers using HP 9000 and Sun workstations and servers will be able to take advantage of the IBM proven relational database capability and operate easily with enterprise applications and data residing on other IBM platforms running DB2 products. Beta testing of DB2 for HP-UX has been conducted for the past several months at a number of customer sites. The DB2 products became available June 24, 1994 for HP 9000 series 700 and 800 workstations and servers running HP-UX Version 9.0. DB2 for the Solaris Operating Environment will support Sun workstations and servers running Sun s Solaris V2.3 operating system. It will be available to a selected set of customers as part of a First Customer Shipment (FCS) program in September. After customers confirm that IBM has achieved desired quality objectives, IBM will announce general availability. COMMON SEGMENT PROBLEMS WITH DB2/VM (SQL/DS) ============================================ Segment errors can cause a variety of error messages to be displayed. If you run using segments and suspect segment problems, there are a couple of simple tests you can perform. Most commonly, the problems are caused by an overlap in the segment addresses, although you can also have problems if the application of service has caused the code to increase in size to the point where it no longer fits in the defined segment. The following is a list of some common error messages that may indicate segment problems: Problem: Error loading segment - storage may be in use Message: SQLCODE -934 Problem: SQL/DS segment overlaps (or is too close to) CMS nucleus Message: DMSFRO159E INSUFFICIENT STORAGE AVAILABLE TO SATISFY FREE STORAGE REQUEST FROM VSTOR or DMSLIO105S ERROR 25 WRITING LOAD MAP A5 ON DISK or DMSLIO380E STORAGE AT ORIGIN VSTOR IN USE. MODULE NOT LOADED or ARI653E INCLUDE COMMAND ERROR RETURN CODE 104 (from ARISAVES EXEC) Problem: USE INST = YES and CMSINST segment overlaps SQL Message: ARI0609E ARISDBBT DSC/DBSS SAVED SEGMENT. RC=4600 Problem: Machine overlaps shared segment Message: DMKVMA456W SHARED PAGE ALTERED Other common problems include addressing, operation, and protection exceptions, and failures during segment creation. It is also possible to see addressing, operation, protection exceptions, or segment overlaps if segments become too small after applying service. How to Diagnose Segment Problems If you suspect a segment error, you can try to start up the database without using the segments. This is not difficult to do. Simple follow these steps: 1. Update the ARISSEGC MACRO (on the 195 (Q) disk) to change the SAVED SEG column to NO. 2. Run the SQLBOOTS EXEC - This will modify the default bootstrap modules (DCSSID=SQLDBA). 3. Reaccess the disk and rerun SQLINIT EXEC to pick up the changes. 4. Update the SQLDBN file on the 195 (Q) disk to change the DCSSID parameter to SQLDBA to use the default bootstraps. (These instructions can be found in the SQL/DS System Administration for IBM VM Systems manual; for version 3 release 4 they are on page 175.) If the error does not occur, then there is a problem with the segments. Make sure there are no overlaps and move or rebuild the segments as necessary. In the next section, there is a summary of which segments can and cannot overlap. To check for overlaps, use the operating system specific command to display the segment layout (such as Q NSS or Q SEGMENT) and look for overlaps in the specified addresses. You can also try to load the segments from CMS (such as using the SEGMENT LOAD command - the simplest syntax is SEGMENT LOAD segment_name). Be sure to load the segments in the same order as SQL/DS does: for the database machine, DBSS and then RDS; for the user, ISQL then RA. Once the segment is loaded, you can look at the storage to see if the code was in fact loaded successfully. What Can and Cannot Overlap? Here is a brief summary of which segments can overlap and which cannot: In MUM, RDS, DBSS, and CMS must NOT overlap in the database machine. In SUM, RA, RDS, DBSS, and CMS must NOT overlap in the database machine. In the user machine, ISQL, RA, ARIMS001 and CMS must NOT overlap. Any program products in segments that use SQL/DS (that is, CSP, QMF) must NOT overlap RA. Program products used within the same terminal session must not overlap. For QMF users: QMF, ISPF, GDDM, RA, and CMS must NOT overlap. ISQL and program products CAN overlap DBSS and RDS. In earlier releases of VM (that is, pre-ESA), the virtual machine size could not be greater than the lowest origin address of any loaded segment (in other words, segments could not be loaded within the virtual machine). Notes: CMS above includes the CMS nucleus and the Help, Inst, and VMLIB segments. If the CMS Inst segment is causing overlaps, you can re-ipl CMS without it using IPL CMS PARM NOSPROF INSTSEG NO . The message repository (ARIMS001) is loaded in a segment in the user machine only. In the database machine, it is always loaded into DMSFREE. In general, segments that are loaded in the database machine only (such as DBSS and RDS) can overlap segments that are loaded in the user machine only (such as ISQL and RA). Watch out for single user mode (SUM); when the database machine is run in SUM, the RA segment is loaded in the machine and must not overlap DBSS or RDS. What Can Be Loaded Above 16M? Of all the various SQL/DS saved segments, only the Resource Adapter segment can be loaded above the 16M line. (This applies only to SQL/DS R3.3 and later.) Here is a list of the SQL/DS segments: * Interactive SQL (ISQL) - SQLISQL - MUST be below 16M - does not support AMODE 31 * Resource Adapter or Resource Manager (RA / RM) - SQLRMGR - may be above 16M (except for SUM when machine in 370 mode) you may need two RA segments: one below 16M for 370 mode users and another above 16M for XA mode users * Database Storage System (DBSS) - SQLSQLDS - MUST be below 16M - does not support AMODE 31 * Relational Data System (RDS) - SQLXRDS - MUST be below 16M - does not support AMODE 31 * Message Repositories - ARIMxxxx - SQL/DS message repository, MUST be below 16M - ARIUME - added to CMS message repository, MUST be below 16M Note that the segment names included here are the defaults; you can specify different names for the segments if you wish. Note also that the RA segment can be above or below the 16M line, but must not cross it (that is, the segment must be all above 16M or all below; the CMS nucleus ALWAYS straddles the 16M line). IBM DB2 FOR MVS/ESA VERSION 4 ============================= IBM announces significant new function in the next version of DB2 for MVS/ESA*, the keystone of the DB2 family of products. By exploiting the processing power and coupling facility technology of the recently announced System/390 Parallel Sysplex, the DB2 Version 4 product delivers data sharing (giving customers the ability to benefit from improved data availability), system scalability, and reduced cost of computing in the MVS/ESA environment. By delivering support for stored procedures, the DB2 Version 4 product will give application programmers more flexibility in designing programs which benefit from DB2 server capabilities and which can deliver improved CPU usage and reduced network overhead in a client/server environment. Other support for distributed computing includes an increase in the distributed thread limit from 10000 to 25000, use of the improved DB2 MVS/ESA dispatch prioritization for DDF threads, and improved ability to timeout and cancel distributed threads. In addition to delivering implementations of exciting new technology, DB2 Version 4 enhancements deliver improved availability, operational performance, and the usability of a range of features. The DB2 product continues to approach 24 X 7 availability with data sharing on the S/390 Parallel Sysplex; a group of DB2 subsystems can have concurrent access to the same data without replication, and keep data available during both planned and unplanned outages. Other availability enhancements include row-level locking, reducing the opportunities for deadlocks; read-through locking, making uncommitted data available to read-only operations; and a new index manager, reducing index contention among utilities and queries. DB2 Version 4 performance improvements address increased throughput as well as better space management. Implementation of query CPU parallelism extends database support for parallel I/O processing to CPU-intensive queries. At the same time, catalog reorganization makes it possible to reclaim unused and fragmented space by reorganizing catalog and directory tablespaces, thereby improving performance. DB2 Version 4 usability enhancements deliver improved operations as well as increased compliance with SQL standards. Among several new features, outer join allows retrieval of unmatched rows in a JOIN operation, and table check constraints allow users to define a domain of values that table columns can contain, simplifying both application integrity and maintenance. For an extended description of DB2 Version 4 features and early support programs, look for the HONE announcement letter for product 5695-DB2. Customers planning to use the DB2 Version 4 product as a Parallel Database Manager for data sharing in the System/390 Parallel Sysplex environment should also review the associated announcement on Parallel Sysplex Extensions. All customers intending to take advantage of this System/390 Parallel Sysplex offering must go through a Systems Assurance Product Review (SAPR) until DB2 Version 4 has achieved General Availability. The DB2 Version 4 product also continues the commitment of IBM to the Quality Partnership Program (QPP) process. The QPP for the DB2 Version 4 product will begin in February 1995 and, when customer results indicate that it meets the criteria set for this QPP, IBM will announce product availability and ordering information. Availability improvements 1. DB2 data sharing allows a group of DB2 subsystems to have concurrent access to the same data at all times. 2. Locking and indexing enhancements provide a row-level locking option, read-through locks, index locking avoidance, and improved partition independence. 3. DFSMS concurrent copy function can now be invoked directly through the DB2 COPY utility. Client/server support 1. Stored procedures improve the performance and security of distributed applications by reducing network traffic. 2. Increased thread limit increases the number of distributed connections that a DB2 server can support from 10000 to 25000, with a limit of 2000 active distributed threads. Performance improvements 1. Query CPU parallelism extends DB2 support for parallel I/O to CPU-intensive queries. 2. Catalog reorganization makes it possible to reclaim unused space by reorganizing catalog tablespaces, thereby improving DB2 performance. Usability improvements 1. Outer join allows retrieval of unmatched rows in a JOIN operation. 2. Table check constraints allow users to define domain values that table columns can contain. 3. Default values allow users to define a default value for a table column if none is specified by the application. 4. Thread cancellation allows the user to cancel any DB2 allied thread. SERVICE FOR DB2/2 and DDCS/2 ============================ Call 1-800-992-4777 for information regarding free and chargeable service options or to receive assistance on: IBM DATABASE 2 OS/2 (DB2/2) Version 1.2 and Version 1.0 IBM Distributed Database Connection Services/2 (DDCS/2)* Version 2.2 and Version 2.0. Assistance is also available using the electronic options, OS2BBS and CompuServe Customer Support. Corrective Service & Customer Support Customer support is provided through a questions-and-answers (Q&A) forum provided by the DB2/2 Service Team. The forum is accessible through CompuServe,** IBMLink, and TALKLink. Corrective Service is provided in two forms Select Paks and Service Paks. A Service Pak contains all fixes that have accumulated since the product first became generally available. A Select Pak is a small group of fixes that have accumulated since the last Select or Service Pak and can be packaged by components within a product. Service Paks will be issued approximately every 6 to 8 months and Select Paks approximately every 3 to 4 months depending on the number, severity and visibility of fixes that have accumulated. If you cannot obtain these Select or Service Paks electronically please call 1-800-992-4777 to have them sent to you in the mail. Accessing Corrective Service and Customer Support 1. CompuServe - call 1-800-848-8199 in Canada or the US for membership information. - Execute GO IBMDB2. - Use DB2/2 section for questions and answers. - Use DB2/2 library for fixes and other files. 2. U.S. (IBMLink) TALKLINK OS/2 BBS - call 1-800-547-1283 for membership information. - Go to OS2BBS in TALKLINK. - Choose Service Package section (Corrective Service only). - Access DB2OS2 FORUM (Q&A and Technical Support). 3. Canadian IBMLink OS/2 BBS - call 905-474-6666 or 1-800-268-3100 for membership information. - Go to IBMLink from the IIN Menu. - Type ICONF on the IBMLink Desktop. - Select View from the Conference File Selection List. - Select Option 5 Select Files and type: WR* * (note: these two asterisks are wildcards) (Corrective Service only). - Select DB2OS2 FORUM (Q&A and Technical Support). The Select and Service Paks are also available on the Technical Connection CD-ROM. To order, call 1-800-992-4777. The following methods are available for Corrective Service only: 1. Internet -FTP to anonymous server software.watson.ibm.com (129.34.139.5). -Enter anonymous as the userid and your complete e-mail address as the password. -Go to pubs/db2/os2 directory. 2. Canadian IBM PS BBS - to dial into the BBS call 905-316-4255 Toronto, 416-492-1823 Toronto, 514-938-3022 Montreal, or 604-664-6464 Vancouver (8 databits, no parity, 1 stop bit). -Area 34 OS/2 Database and Host. 3. US IBM PCC BBS - to dial into this BBS, call 919-517-0001 Raleigh (8 databits, no parity, 1 stop bit). -Go to Directory 4 (the CSD Directory). DB2/2 V1 AND DDCS/2 V2 SELECT AND SERVICE PAKS AVAILABLE ======================================================== Select Paks are now available for the package names and RETAIN PTF numbers listed in the charts on pages 12 and 13. For installation instructions, please refer to the README on the first diskette of the Select Pak. Before trying to install any of these Select Paks, install DB2/2 V1.0 Service Pak WRx7015 or DDCS/2 V2.0 Service Pak WRx7016. Please refer to the March 1994 issue of DB2 family news for the package names and RETAIN PTF numbers for these Service Paks. In order to run with the new DDCS/2 fixes in WRx7024, you must install both the DB2/2 WRx7025 Select Pak and the DDCS/2 WRx7024 Select Pak. *(Notes related to PTFs 07026 and 07027 in charts) A few weeks after the US WR07025 and WR07022 were available, we updated the packages. Due to the update, we had to create a second RETAIN PTF number for the updated packages, making the old RETAIN PTF numbers obsolete. The package names of the Select Paks that are available electronically (WR07025 and WR07022) are unchanged, but if you order diskettes from IBM, you will receive diskettes labelled WR07026 and WR07027 instead of WR07025 and WR07022, respectively. If you are unsure whether you have the latest versions of WR07025 and WR07022 Select Paks (this applies for the U.S. version of the products only), then please check that the files listed below have the following dates. WR07025 DBM.CMD 5/16/94 DBME.CMD 5/16/94 DBMF.CMD 5/16/94 README.DB2 5/18/94 or 5/19/94 This defect was added to the Select Pak to fix a problem with the command line processor. As described in the README.DB2 file When DBMCOMMIT was not set or set to YES, operations performed through DBM -C UPDATE or DBM -C DROP on the database could not be rolled back. Now, operations done with DBM -C can always be rolled back regardless of what DBMCOMMIT is set to. WR07022 README.DB2 5/17/94 or 5/19/94 The README was updated with some missing APAR/Defect descriptions. If the above dates do not match the dates of your files, then you may want to obtain the newer versions of these Select Paks by: 1.Obtaining FIX7025.ZIP and FIX7022.ZIP which are available electronically; these contain the differences between the initial and final versions of WR07025 and WR07022. 2.For the latest versions of the diskettes, re-order the full set of diskettes from IBM for WR07026 and WR07027. The following is a list of APARs that have been fixed by these Select Paks: Select Pak WRx7025 APARs Database Engine & Database Administration Tools: JR00056, JR07267, JR07352, JR07464, JR07486, JR07504, JR07508, JR07513, JR07515, JR07516, JR07542, JR07577, JR07646, JR07663, JR07670, JR07688, JR07710, JR07757, JR07758, JR07770, JR07819, JR07868 Select Pak WRx7024 APARs Distributed Database Connection Services/2: JR07377, JR07710, JR07759 Select Pak WRx7023 APARs DOS and Windows Clients: JR07462, JR07728 Select Pak WRx7022 APARs Query Manager: JR07499, JR07742 DB2/2 V1.2 APAR JR08083 is available for DB2/2 V1.2 and will be included in the first Service Pak that will be issued before the end of this year. This APAR provides scalability for OS/2 Symmetrical Multiprocessing (SMP) and contains fixes for problems discovered in DB2/2 V1.2: - It now allows mixed case userid/passwords to be used on CAE/DOS clients when connecting via a DDCS/2 gateway to a host database. - It fixes a hang that occurred when stopdbm was invoked and old information from a previous startdbm/stopdbm was not cleaned up internally for this invocation. - It provides DB2/2 with the ability to scale with an increased number of processors in an SMP environment. - It gives performance improvements over DB2/2 V1.0 in SMP environments. DB2/6000 V1.1 Products Service Packages Available Service Package U427189: Service Package U427189 became available in March 1994. It requires the initial release of the DB2/6000 V1.1 products (01.01.0000.0000) to be installed before installing any fixes from it. The following products are updated by this Service Package: - DB2 Client Application Enabler/6000 - SDK/6000 - DB2/6000 - DDCS/6000 The following options can be installed with this Service Package: db2_01_01_0000.client 1.01.0000.0000.U427189 db2_01_01_0000.db2 01.01.0000.0000.U427189 db2_01_01_0000.info.En_US 01.01.0000.0000.U427189 db2_01_01_0000.msg.De_DE.client 01.01.0000.0000.U427189 db2_01_01_0000.msg.de_DE.client 01.01.0000.0000.U427189 db2_01_01_0000.msg.Es_ES.client 01.01.0000.0000.U427189 db2_01_01_0000.msg.es_ES.client 01.01.0000.0000.U427189 db2_01_01_0000.msg.Fr_FR.client 01.01.0000.0000.U427189 db2_01_01_0000.msg.fr_FR.client 01.01.0000.0000.U427189 db2_01_01_0000.msg.It_IT.client 01.01.0000.0000.U427189 db2_01_01_0000.msg.it_IT.client 01.01.0000.0000.U427189 db2_01_01_0000.msg.Ja_JP.client 01.01.0000.0000.U427189 The following APARs are fixed with this Service Package: IX41718, IX40594 For installation instructions, please refer to the lpp.README shipped with the Service Package. Service Package U430988: Service Package U430988 became available in June 1994. Before installing any fixes, the initial release of the DB2/6000 V1.1 products (01.01.0000.0000 or 01.01.0000.0000.U427189) or the refresh 01.01.0000.0001 versions of the products must be installed. The following products/options are installed by this Service Package: - DDCS/6000, option db2_01_01_0000.ddcs - CS/6000, option db2_01_01_0000.cs_sna This Service Package allows current customers to be able to install these options on a system that has SNA 1.2 or higher. (In the initial release of the code and first refresh, the installation was restricted to SNA 1.2 only.) The level of code in this Service Package is the same as refresh 01.01.0000.0001 for these options. Refresh 01.01.0000.0002 or higher will allow the install to occur on SNA 1.2 or higher, but if you have the initial release of the product (01.01.0000.0000) or refresh 01.01.0000.0001 and want to install on a machine with SNA 2.1, then use this Service Package to install the above two products/options. If you already have these products installed and want to upgrade from SNA 1.2 to SNA 2.1, you do not need this Service Package. The following APARs are fixed with this Service Package: IX43969, JR08025 For installation instructions, please refer to the lpp.README shipped with the Service Package. Service Package U430989: Service Package U430989 became available in July 1994. Before installing any fixes, the initial release of the DB2/6000 V1.1 products (01.01.0000.0000 or 01.01.0000.0000.U427189) or the refresh 01.01.0000.0001 versions of the products must be installed. This Service Package supersedes Service Package U427189. The following products are updated by this Service Package: - DB2 Client Application Enabler/6000 - SDK/6000 - DB2/6000 - DDCS/6000 The following options can be installed with this Service Package: db2_01_01_0000.client 01.01.0000.0001.U430989 db2_01_01_0000.sdk 01.01.0000.0001.U430989 db2_01_01_0000.db2 01.01.0000.0001.U430989 db2_01_01_0000.dba 01.01.0000.0001.U430989 db2_01_01_0000.ddcs 01.01.0000.0001.U430989 db2_01_01_0000.info.En_US 01.01.0000.0001.U430989 db2_01_01_0000.misc 01.01.00000001.U430989 db2_01_01_0000.msg.De_DE.client 01.01.0000.0001.U430989 db2_01_01_0000.msg.De_DE.dba 01.01.0000.0001.U430989 db2_01_01_0000.msg.de_DE.client 01.01.0000.0001.U430989 db2_01_01_0000.msg.de_DE.dba 01.01.0000.0001.U430989 db2_01_01_0000.msg.Es_ES.client 01.01.0000.0001.U430989 db2_01_01_0000.msg.Es_ES.dba 01.01.0000.0001.U430989 db2_01_01_0000.msg.es_ES.client 01.01.0000.0001.U430989 db2_01_01_0000.msg.es_ES.dba 01.01.0000.0001.U430989 db2_01_01_0000.msg.Fr_FR.client 01.01.0000.0001.U430989 db2_01_01_0000.msg.Fr_FR.dba 01.01.0000.0001.U430989 db2_01_01_0000.msg.fr_FR.client 01.01.0000.0001.U430989 db2_01_01_0000.msg.fr_FR.dba 01.01.0000.0001.U430989 db2_01_01_0000.msg.It_IT.client 01.01.0000.0001.U430989 db2_01_01_0000.msg.It_IT.dba 01.01.0000.0001.U430989 db2_01_01_0000.msg.it_IT.client 01.01.0000.0001.U430989 db2_01_01_0000.msg.it_IT.dba 01.01.0000.0001.U430989 db2_01_01_0000.msg.Ja_JP.client 01.01.0000.0001.U430989 db2_01_01_0000.msg.Ja_JP.dba 01.01.0000.0001.U430989 The following APARs are fixed with this Service Package: JR07757, JR07868, IX42175, IX42260, IX42311, IX42513, IX42276, JR08025, IX43969, JR07973, JR08020, JR08032, IX41718 For installation instructions, please refer to the lpp.README shipped with the Service Package. *Refreshes Refresh 01.01.0000.0001 for DB2/6000 V1.1 products was available in March 1994. It is equivalent code to installing the initial release of the DB2/6000 V1.1 products (01.01.0000.0000) with Service Package U427189. Refresh 01.01.0000.0002 for DB2/6000 V1.1 products was available in July 1994. It is equivalent code to installing: - initial release of the DB2/6000 V1.1 products (01.01.0000.0000) with Service Package U430989 OR - refresh 01.01.0000.0001 with Service Package U430989. IBM SUPPORT FOR CUSTOMER MIGRATIONS TO THE DB2 FAMILY ===================================================== Many customers have contacted IBM during the past few years saying they would like to move from non-IBM software platforms to the IBM software solution families, but they aren't sure how to go about this and could IBM help? The IBM U.S. Software Migration Project Office (SMPO) was formed in early 1993 to assist customers with such migrations. A major focus of the SMPO has been helping customers assess, plan for, and implement successful migrations to the DB2 Family. Much of the SMPO s early work was in assisting customers who are interested in moving from non-relational, pre-relational, or quasi-relational data management technologies to the IBM DB2 Relational family. Many of these customers realize that their older legacy business applications are not well positioned to take advantage of technological advances such as distributed data, client-server, and parallel processing. They see a move to true relational platforms as an essential stepping stone to gaining competitive business advantage by maximizing technology. The source data management environments currently supported by the SMPO include CA-IDMS**, CA-Datacom**, Adabas**, Total**, Mod204** and VSAM*. Target platforms can be any DB2 family member (DB2/MVS, DB2 VSE & VM,* DB2/400*, DB2/2 and DB2/6000). Indeed, we see many customers who want to target different DB2 family platforms for different business application areas, and manage data between these platforms using DRDA. The SMPO is also working with the Software Solutions Division to assess extending support to DB migrations from other relational DBMSs (including Oracle**, Sybase** and Ingres**). Major database migrations can be tricky, and if not approached wisely, they can represent a significant and often unmanageable cost and risk to the customer s business operations. The philosophy of the SMPO has been to exploit leading-edge third party automated conversion tools, cooperative customer-driven project planning and low-cost labor sourcing, where appropriate, in a concentrated effort to make migrations to the DB2 family more cost-effective and risk-contained. The level of automation which can be achieved and the cost of labor for the project are two of the factors which most affect cost and risk. The third major factor is regression (or function) testing , which can often represent 30 to 50 percent of the overall project cost. The SMPO also works with customers to devise or refine automated approaches to testing in an effort to keep costs down for this important phase of the migration. There are many tools and technologies in the market today which can be used in a DB migration project. These tools help with one or more of the three major dimensions of migration: 1. DB Design/Re-Design/Design-Translation. These tools include the well-known products from companies like Bachman** and Knowledgeware**. 2. Porting of the Business Application Logic. This dimension involves translating the business application logic, which is usually intimately tied into the old data manager, to run against the target SQL interfaces. It is generally the most costly and complex portion of the conversion, and where we find the greatest tool diversity. Many of the best application conversion tools are from small technology houses, with niche market expertise. The SMPO has aligned with five such conversion specialists, chosen from a candidate group of more than 10. Some of these conversion tools can translate COBOL calling older non-relational DBMSs to COBOL calling SQL at automation levels exceeding 98 percent. There are also tools which translate the older 4GLs often associated with pre-relational DBMSs to standard languages such as COBOL, often at automation levels over 90 percent. We are even looking into some tools which can translate some of these older proprietary 4GLs to IBM products such as the VisualGen* product. 3. Porting of the Production Data at cut-over time. Once the DB design issues and mappings have been taken care of, and the business application logic ported and stabilized on the target platform, tools can also be employed to port the production data from the source to the target environment. This usually takes place as a final step in preparation for production cut-over , or the phasing of the converted systems into production on the target platform. Some conversion tools will help in generating unloading and reloading drivers. But in other cases, it is preferable to work with a tool or services group dedicated to this task, as there are issues like special character editing to consider. The SMPO also has close working relationships with the TISL team in Bangalore, India. TISL is a 50-50 joint venture between IBM and the TATA Group. They have a re-engineering team that is trained in supporting some of the tools from our tool partners, and can be used in support of an overall project plan at very favorable labor rates. This allows key customer skills and the high-skill support needed from IBM Services and Consulting to be focused on critical project roles such as project management, database design, and DBA support. If your company has a substantial investment in legacy systems on non-relational platforms, and has considered or would like to evaluate the alternatives in migrating those applications and their data to the DB2 family, please give us a call. We can often help customers put together DB Migration projects that are much more cost-effective and realistic than they thought possible! This can put your business in a position to take full advantage of all that the DB2 family has to offer. For more information, please contact Dean A. Huchthausen, Business Opportunity Manager for DB Migrations in the US. (415)-545-3456. DB2/2 HELP: AS NEAR AS YOUR TELEPHONE! ====================================== IBM knows that both home and business users of the standalone version of the DB2/2 product need inexpensive end user support services. To meet this need, IBM announced two new end user support options for the DB2/2 product. A $2 per minute 1-900 service is now available for installation, usage and how to information for SQL, Query Manager, the Command Line Interface, Utilities and programming APIs. This service will be available to U.S. customers by calling 1-900-555-DB2/2 from 10 a.m. to 6 p.m. (EST), Monday through Friday, excluding U.S. national holidays. Please note, this is a test program so hours and days of support may be adjusted according to volumes and use. Also available is a free automated voice response system. This service provides answers to common questions and offers a fax service delivering important DB2/2 documents. You can access this system by calling the IBM Personal Software Solution Center at 1-800-992-4777 and asking for DB2 Support (press 1161 on a touch-tone phone). Or, you can call 1-800-668-2913 directly. ENABLING POWERBUILDER VERSION 3.0a FOR DB2/VSE & VM =================================================== Recently, a large international development agency in Washington, D.C. began an application development project with two clear objectives: 1. Make the most of the existing industrial-strength mainframe database (DB2 for VM) with features like universal access and centralized backup/recovery. 2. Use a GUI client-server development tool (PowerBuilder) to reduce charges for mainframe application development. The Setup The target host environment included an ES9000-class Amdahl** mainframe, VM/ESA*, DB2/VM, and the IBM Cross System Product (CSP), in which the current mainframe-centric applications were written. The distributed database infrastructure was established with DB2/2, DDCS/2, and Banyan Vines** (Netbios) LAN. The customer had also installed MDI Gateway**, but chose to use DDCS/2 because of its overall superior performance as well as its support for static SQL stored in packages and cursor processing. The DDCS/2 server is configured as a DELL** 486DX2 66Mhz with 16 megabytes of memory and a 1 gigabyte hard drive. Migration To offload the mainframe development cycles to the server, a full-volume snapshot of one medium-sized application (500Mb) was moved to the server. The migration was accomplished in a single afternoon using the SQLQMF facility that is shipped with DB2/2 Query Manager. First, table objects were exported from QMF on the host to a VM temporary minidisk. Then, the SQLQMF procedure was initiated from the DDCS/2 server to download the file, parse the table structure and data, create the table in DB2/2, and import the data. Additional DB2/VM DDL such as indexes, views, and grants were generated and downloaded from the DB2/VM catalog using SQLMaster/VM*. These were executed using a generalized REXX shell to complete the DB2/2 application development environment. Benefits and Drawbacks During this process, a few minor problems were encountered. First, the lack of DB2/2 support for the LNGVCHAR datatype was remedied by the fact that standard DB2/2 VARCHAR fields can range in size to almost 4 Kb. Second, certain scalar functions such as STRIP (required for some views) were not supported in the then-current release. The biggest challenge, however, was presented by PowerBuilder. Applications developed using manually coded embedded SQL worked without any problems. A substantial benefit of the PowerBuilder interface is the pop-up windows and pull-down lists of the database catalogs which show all available tables, the columns in those tables, and so forth. Unfortunately, the PowerBuilder driver was written specifically for DB2/MVS which has different system catalogs in name and structure from its DB2/VM counterparts. The solution? By parsing the five bind files that ship with PowerBuilder, we identified all required host catalog tables and columns. In true RDBMS fashion, we then created views that represented a DB2/MVS appearance to PowerBuilder. The view definitions follow: SYSIBM.SYSTABLES CONNECT SQLDBA IDENTIFIED BY ********; GRANT SELECT ON SYSTEM.SYSCATALOG TO SYSIBM WITH GRANT OPTION; COMMIT WORK; CONNECT SYSIBM IDENTIFIED BY ********; SET ERRORMODE CONTINUE; DROP VIEW SYSIBM.SYSTABLES; SET ERRORMODE OFF; CREATE VIEW SYSIBM.SYSTABLES ( NAME, CREATOR, TYPE, COLCOUNT, TID ) AS SELECT TNAME, CREATOR, TABLETYPE, NCOLS, TABID FROM SYSTEM.SYSCATALOG WHERE TABLETYPE = R ; COMMIT WORK; GRANT SELECT ON SYSIBM.SYSTABLES TO PBMAY94 WITH GRANT OPTION; GRANT SELECT ON SYSIBM.SYSTABLES TO PUBLIC; SYSIBM.SYSTABAUTH CONNECT SQLDBA IDENTIFIED BY ********; GRANT SELECT ON SYSTEM.SYSTABAUTH TO SYSIBM WITH GRANT OPTION; COMMIT WORK; CONNECT SYSIBM IDENTIFIED BY ********; SET ERRORMODE CONTINUE; DROP VIEW SYSIBM.SYSTABAUTH; SET ERRORMODE OFF; CREATE VIEW SYSIBM.SYSTABAUTH ( GRANTOR, GRANTEE, TCREATOR, TTNAME, ALTERAUTH, DELETEAUTH, INDEXAUTH, INSERTAUTH, SELECTAUTH, UPDATEAUTH, REFAUTH ) AS SELECT GRANTOR, GRANTEE, TCREATOR, TTNAME, ALTERAUTH, DELETEAUTH, INDEXAUTH, INSERTAUTH, SELECTAUTH, UPDATEAUTH, REFAUTH FROM SYSTEM.SYSTABAUTH; COMMIT WORK; GRANT SELECT ON SYSIBM.SYSTABAUTH TO PBMAY94 WITH GRANT OPTION; GRANT SELECT ON SYSIBM.SYSTABAUTH TO PUBLIC; SYSIBM.SYSDBAUTH CONNECT SQLDBA IDENTIFIED BY ********; GRANT SELECT ON SQLDBA.SYSUSERLIST TO SYSIBM WITH GRANT OPTION; COMMIT WORK; CONNECT SYSIBM IDENTIFIED BY ********; SET ERRORMODE CONTINUE; DROP VIEW SYSIBM.SYSDBAUTH; SET ERRORMODE OFF; CREATE VIEW SYSIBM.SYSDBAUTH (GRANTEE, DBADMAUTH) AS SELECT NAME, DBAAUTH FROM SQLDBA.SYSUSERLIST; COMMIT WORK; GRANT SELECT ON SYSIBM.SYSDBAUTH TO PBMAY94 WITH GRANT OPTION; GRANT SELECT ON SYSIBM.SYSDBAUTH TO PUBLIC; SYSIBM.SYSCOLUMNS CONNECT SQLDBA IDENTIFIED BY ********; GRANT SELECT ON SYSTEM.SYSCOLUMNS TO SYSIBM WITH GRANT OPTION; COMMIT WORK; CONNECT SYSIBM IDENTIFIED BY ********; SET ERRORMODE CONTINUE; DROP VIEW SYSIBM.SYSCOLUMNS; SET ERRORMODE OFF; CREATE VIEW SYSIBM.SYSCOLUMNS ( NAME, TBNAME, TBCREATOR, COLNO, COLTYPE, LENGTH, SCALE, NULLS ) AS SELECT CNAME, TNAME, CREATOR, COLNO, S.COLTYPE , SYSLENGTH / LENGTH_FACTOR , SYSLENGTH - ( SCALE_FACTOR * (SYSLENGTH / SCALE_FACTOR) ) , NULLS FROM SYSTEM.SYSCOLUMNS S, SYSIBM.SYSCOLTYPE I WHERE S.COLTYPE = I.COLTYPE; COMMIT WORK; GRANT SELECT ON SYSIBM.SYSCOLUMNS TO PBMAY94 WITH GRANT OPTION; GRANT SELECT ON SYSIBM.SYSCOLUMNS TO PUBLIC; GRANT SELECT ON SYSIBM.SYSCOLTYPE TO PBMAY94 WITH GRANT OPTION; GRANT SELECT ON SYSIBM.SYSCOLTYPE TO PUBLIC; SYSIBM.SYSVIEWS CONNECT SQLDBA IDENTIFIED BY ********; GRANT SELECT ON SYSTEM.SYSVIEWS TO SYSIBM WITH GRANT OPTION; COMMIT WORK; CONNECT SYSIBM IDENTIFIED BY ********; SET ERRORMODE CONTINUE; DROP VIEW SYSIBM.SYSVIEWS; SET ERRORMODE OFF; CREATE VIEW SYSIBM.SYSVIEWS ( NAME, CREATOR, SEQNO, TEXT ) AS SELECT VIEWNAME, VCREATOR, SEQNO, VIEWTEXT FROM SYSTEM.SYSVIEWS; COMMIT WORK; GRANT SELECT ON SYSIBM.SYSVIEWS TO PBMAY94 WITH GRANT OPTION; GRANT SELECT ON SYSIBM.SYSVIEWS TO PUBLIC; SYSIBM.SYSINDEXES CONNECT SQLDBA IDENTIFIED BY ********; GRANT SELECT ON SYSTEM.SYSINDEXES TO SYSIBM WITH GRANT OPTION; COMMIT WORK; CONNECT SYSIBM IDENTIFIED BY ********; SET ERRORMODE CONTINUE; DROP VIEW SYSIBM.SYSINDEXES; SET ERRORMODE OFF; CREATE VIEW SYSIBM.SYSINDEXES ( NAME, TBNAME, TBCREATOR, COLNAMES, UNIQUERULE, COLCOUNT ) AS SELECT INAME, TNAME, CREATOR, COLNAMES, INDEXTYPE, ( LENGTH(COLNUMBERS) / 2 ) - 1 FROM SYSTEM.SYSINDEXES; COMMIT WORK; GRANT SELECT ON SYSIBM.SYSINDEXES TO PBMAY94 WITH GRANT OPTION; GRANT SELECT ON SYSIBM.SYSINDEXES TO PUBLIC; SYSIBM.SYSRELS CONNECT SQLDBA IDENTIFIED BY ********; GRANT SELECT ON SYSTEM.SYSKEYS TO SYSIBM WITH GRANT OPTION; GRANT SELECT ON SYSTEM.SYSKEYCOLS TO SYSIBM WITH GRANT OPTION; COMMIT WORK; CONNECT SYSIBM IDENTIFIED BY ********; SET ERRORMODE CONTINUE; DROP VIEW SYSIBM.SYSRELS; SET ERRORMODE OFF; SYSTEM.SYSINDEXES; , DELETERULE, UPDATERULE, CREATE VIEW SYSIBM.SYSRELS ( TBNAME, CREATOR, RELNAME, COLCOUNT, REFTBNAME, REFTBCREATOR, DELETERULE, UPDATERULE, FKCOLNAMES ) AS SELECT K.TNAME, K.TCREATOR, K.KEYNAME, KEYCOLS, REFTNAME, REFTCREATOR, DELETERULE, R , CNAME FROM SYSTEM.SYSKEYS K, SYSTEM.SYSKEYCOLS C WHERE K.TNAME = C.TNAME AND K.TCREATOR = C.TCREATOR AND K.KEYNAME = C.KEYNAME AND K.KEYTYPE = F AND KEYORD = 1; COMMIT WORK; GRANT SELECT ON SYSIBM.SYSRELS TO PBMAY94 WITH GRANT OPTION; GRANT SELECT ON SYSIBM.SYSRELS TO PUBLIC; SYSIBM.SYSCOLTYPE CONNECT SYSIBM IDENTIFIED BY ********; SET ERRORMODE CONTINUE; DROP TABLE SYSIBM.COLTYPE; SET ERRORMODE OFF; CREATE TABLE SYSIBM.SYSCOLTYPE ( COLTYPE CHAR(8) NOT NULL, LENGTH_FACTOR SMALLINT NOT NULL, SCALE_FACTOR SMALLINT NOT NULL ) IN PUBLIC.********; COMMIT WORK; INSERT INTO SYSIBM.SYSCOLTYPE VALUES (CHAR, 1, 1); INSERT INTO SYSIBM.SYSCOLTYPE VALUES (DATE, 1, 1); INSERT INTO SYSIBM.SYSCOLTYPE VALUES (DBAHW , 1, 1); INSERT INTO SYSIBM.SYSCOLTYPE VALUES (DBAINT , 1, 1 ); INSERT INTO SYSIBM.SYSCOLTYPE VALUES (DECIMAL , 256, 256 ); INSERT INTO SYSIBM.SYSCOLTYPE VALUES ( FLOAT , 1, 1 ); INSERT INTO SYSIBM.SYSCOLTYPE VALUES (INTEGER , 1, 1 ); INSERT INTO SYSIBM.SYSCOLTYPE VALUES (LNGVCHAR , 1, 1 ); INSERT INTO SYSIBM.SYSCOLTYPE VALUES (SMALLINT , 1, 1 ); INSERT INTO SYSIBM.SYSCOLTYPE VALUES (TIME , 1, 1 ); INSERT INTO SYSIBM.SYSCOLTYPE VALUES (TIMESTMP , 1, 1 ); INSERT INTO SYSIBM.SYSCOLTYPE VALUES (VARCHAR , 1, 1 ); COMMIT WORK; CREATE UNIQUE INDEX SYSIBM.SYSCOLTYPEA ON SYSIBM.SYSCOLTYPE ( COLTYPE ); COMMIT WORK; GRANT SELECT ON SYSIBM.SYSCOLTYPE TO PBMAY94 WITH GRANT OPTION; Reflections on the Process The most exacting part of the process was correctly implementing the cascading authorizations. Although DB2/VM catalog tables are granted to PUBLIC, this is done without the WITH GRANT OPTION. In order to create the views and subsequently run application programs against them: The creator of the views (SYSIBM) must be delegated SELECT authority on the necessary SYSTEM tables by SQLDBA WITH GRANT OPTION; The creator of the packages (PBMAY94) must be delegated SELECT authority on the views by SYSIBM WITH GRANT OPTION; Developers must be granted SELECT authority on the VIEWS; and Developers must be granted RUN authority on the packages. In addition, an ancillary table (SYSIBM.SYSCOLTYPE) has to be created to correctly convert the LENGTH and SCALE values in SYSIBM.SYSCOLUMNS. This is accomplished by joining the ancillary table to SYSTEM.SYSCOLUMNS in the appropriate view. Finally, we decided to create views only for the tables required by PowerBuilder rather than emulate all of the DB2/MVS catalog tables under DB2/VM. We re confident that a future release of DB2/VM will contain this feature. Implementation PowerBuilder applications can be switched easily between the host and workstation databases by changing a single target database parameter. An entire Development DB2/VM database has been removed from the host; just the Quality Assurance and Production DB2/VM databases remain. The customer estimates a saving of over $3,000 per month in mainframe DASD and CPU charges for just this one application. Frank C. Fillmore, Jr. is principal in The Fillmore Group, Inc (TFG) an IBM Business Partner and independent consulting firm specializing in enterprise-wide relational database management systems (RDBMS) technology. TFG is about to finalize an agreement with IBM Education and Training (formerly Skill Dynamics) to publicly offer their DB2 For VSE & VM (formerly SQL/DS) curriculum nationwide. TFG anticipates publishing a schedule of classes in Fall 1994. IBM VISUALGEN ENABLES RAPID APPLICATION DEVELOPMENT =================================================== In the fight to enhance productivity while making the most of existing skills and resources, the new IBM VisualGen* product is a strong ally for enterprise customers who are building applications to run on a wide variety of workstation and host environments. A new OS/2-based client/server rapid application development product, the VisualGen product assists medium and large enterprise businesses in creating industrial-strength, distributed applications. VisualGen tool s sophisticated development environment effectively frees programmers from dealing with underlying system and subsystem complexities. At the same time, it facilitates the creation of complete robust client/server applications supporting multiple environments, including OS/2, MVS/ESA, VSE/ESA and Windows** 3.1. The product s unique drag and drop visual construction paradigm automatically generates code, making it easier to build distributed database client/server applications, while its object-based, state-of-the-art testing tools allow programmers to immediately test applications end-to-end. The VisualGen product is tightly integrated with the IBM DB2 family of relational database products. It will be integrated with the IBM LAN library and management facility and the IBM LAN data dictionary tools announced as part of the IBM application productivity family. DBA TOOLS BECOME MORE USER FRIENDLY =================================== IBM is announcing some exciting database administrative tools: DB2 Visual Explain and DB2 Performance Monitor. These tools are part of a new group of products called the DB2 Tools Family available for both the OS/2 and AIX platforms (and coming soon for other platforms). With these tools, database administrators and application developers can use graphical user interfaces for industrial-strength database management in the client/server environment. Tired of wading through Explain table output in an attempt to improve database performance? The DB2 Visual Explain tool shows you the access plans for a database transaction in an easy-to-use graphical display. You can change statistics in a model to assess the impact of environmental changes on SQL statements without committing the statistics to the database. When you are satisfied with the changes, you can commit them to the system tables, thereby tuning your databases and applications for better performance. The DB2 Performance Monitor tool lets you determine and analyze performance problems in a database and its applications. The tool also identifies exception conditions that you set, and responds to them by executing a predefined command or program, logging information on the conditions, and issuing optional beeps or warning messages. In addition, you can use the tool to log performance information that you can analyze later to spot trends. These new tools provide easy and intuitive graphical user interfaces, incorporating windows, reports, and graphs. Design of these interfaces has involved extensive customer review and feedback, including several stages of initial design reviews and prototype evaluations by over 20 different customers. Fine-Tuning Your SQL Statements for Optimal Performance When developing database applications, you may want to tune your database environment so that SQL statements perform with minimal response time. For example, you may want the SQL statements in a given application to have reasonable CPU and I/O costs that do not use costly database scans. Or you may want to investigate why a particular transaction is running excessively slowly. The DB2 Visual Explain tool helps you determine whether or not these performance criteria are met, and then it models changes to the database environment to ensure that they are met. When a data access problem is reported, typically you must review information about the access plan used by the SQL optimizer to determine if there are performance problems. When you review the access plan through DB2 Visual Explain, your job is easier. By viewing a graph showing a visual representation of the access plan (and providing quick access to additional detailed information), you can assess if a database change needs to be made (for example, if an index should be created). (See Figure 1) Once you decide to make a change, you can model the effects of the change on the environment to test whether the SQL statement actually performs better. You can then perform a dynamic explain to determine the effect of this change on other SQL statements in the environment. When you are satisfied with the way the model works, you can commit the changes. The DB2 Visual Explain tool also provides the option of comparing statistics at different points in time. For example, you can compare the database statistics at bind time with the current database statistics, and use this comparison to predict the effects of re-binding with current statistics. Monitoring Your Database Are you looking for a tool that helps you detect and fix performance problems before they threaten your database? For example, do you need a tool to monitor performance and warn you if something is going wrong, or a tool to monitor performance characteristics of applications? The DB2 Performance Monitor tool for AIX and OS/2 will answer many of these needs. It measures and analyzes performance characteristics of your applications, and monitors your database for exception conditions, with alerts issued and commands or programs run if any conditions are triggered. Use the DB2 Performance Monitor tool to capture and store performance information that highlights performance characteristics and trends. When a problem is detected in a test or production environment, use the tool to isolate and keep performance data related to the problem. Later, use this data to identify the cause of the problem and suggest corrective actions. Many database customers are using the power of client/server to distribute some of their data and processing to geographically remote branch offices. Typically, there are no technical skills available at these remote sites; therefore, the database servers operating there must run unattended as much as possible. When problems occur, the goal is to have the database server correct them without human intervention, notifying a central site if automated resolution is not possible. The DB2 Performance Monitor tool brings you a step closer to this goal, since the tool can be used in a production environment to provide a degree of unattended and remote operation. For example, particular thresholds can be set up on a monitored node, with the tool quietly running in the background. If a threshold is reached, a predefined command or program can be run, thus providing some degree of unattended, automated operation. If human intervention is required from a central site, operators can use the data stored by the tool to assess the problem and work toward a solution. A Client/Server Solution If your company is undergoing the shift to client/server technologies, the DB2 Visual Explain and DB2 Performance Monitor tools provide administrative support for the client/server environment that is similar to the support available for mainframe environments. With minimal training, you can move from your host database tools to manage, administer, and develop in a client/server database environment. Both products are integrated into DataHub, IBM s strategic control point for database systems management. Therefore, if you need to manage both host and client/server database environments, you can continue to do so from a single control point. Whatever your client/server needs, the DB2 Performance Monitor and DB2 Visual Explain tools provide solutions to some of your OS/2 and AIX database administration and management problems. Stay tuned for their official announcement! THE IBM VISUALIZER PRODUCT FAMILY ================================= The best way to get ahead and stay ahead in today s business environment is to access, analyze, and present information quickly and effectively. In addition, you need to develop applications and procedures that streamline your day-to-day operations and simplify routine tasks. At one time, making that happen required in-depth SQL knowledge, complex programming skills, and an understanding of host database systems, as well as familiarity with various software presentation packages. Often, these skills were spread throughout a company, with access to data and software hard to attain. Now, all that has changed. Everyone in your enterprise can access information and perform a wide range of tasks by using a single product family. The Visualizer* product enhances desktop data processing, integrating an easy-to-use graphical interface with the power of the IBM OS/2 operating system. Visualizer is a workstation-based product that provides desktop access to data from a variety of sources: DB2 for OS/2 (DB2/2); DB2 for AIX/6000; DB2 for OS/400*; DB2 for MVS; DB2 for VSE & VM; Oracle**; and Sybase**. With the Visualizer product you can access, manipulate and present data, and create applications and procedures by using a suite of related business objects. Visualizer Query for OS/2, the core Visualizer product, provides a strong foundation for your enterprise s query and reporting needs, and its base function can be easily extended by installing other members of the Visualizer family, including: Visualizer Charts provides the means for you to present and interpret your business data with a variety of charting options. Visualizer Procedures helps you automate repetitive tasks or design interactive business solutions without having to learn a programming language. Visualizer UltiMedia Query gives you access to multimedia data such as images, audio, video and text documents through DB2/2 tables. Now you can mix traditional data (such as numbers and text) with multimedia information and view it on your desktop. Visualizer Development allows solution providers to build and customize business application solutions for end users. It provides a comprehensive set of code-generating and programming tools to enable the production, implementation and rollout of specialized business solutions incorporating complex business logic. Visualizer Statistics enables users to analyze data and search for trends and patterns. It uses today s data to forecast tomorrow s trends, assisting corporations in making informed business decisions about future market needs. Visualizer Plans helps planners build multidimensional computer models suitable for solving a wide range of business and organizational problems. The Visualizer product uses the object interface of OS/2 to provide seamless support for querying and presenting information. With features like drag and drop support, automatic Dynamic Data Exchange (DDE) update capability, remote database access, a built-in filing system, and available SQL support, Visualizer provides easy-to-use yet flexible support for all your information processing needs, no matter what size your enterprise. You'll find it easy to drag and drop Visualizer tables onto a Visualizer query, then drop the query onto a Visualizer report. With DDE, whenever you update the data in the tables, the query and the report are automatically updated. If you prefer a more hands-on approach, you can update data by opening a table or view, and then directly entering new information, or deleting old information. You can group and analyze data in tables and reports via simple menu choices. Plus, it s easy to build expressions by selecting clauses and operations from lists, or by writing them yourself. With Visualizer, display the contents of local and remote DB2 databases with an SQL database object. Create indexes, primary keys, and constraints, or authorize others to access your relational tables and views without using SQL; or exercise the option to write and run SQL statements directly if you wish. Visualizer is available in nine languages, and includes comprehensive documentation, both printed and online, as well as extensive online help. Visualizer s support for workstation, LAN, and host databases, plus its flexibility for both information processing and application development, make it an ideal product for integrating and interpreting data. It is easy enough for the novice user, and powerful enough for the expert. The Visualizer product family was announced on May 24, 1994 and the OS/2 products are now generally available. Visualizer products for the Windows** environment will become available in 1995. WHAT'S NEW WITH DB2/VSE & VM (SQL/DS)? ====================================== During the past year we've talked to many of you about your plans for the future and you have told us that you want to make your businesses more productive, make decisions faster, and provide superior customer service for your products. Almost all of you have also told us that you believe the way to do this is with client/server implementations and have asked us to help you understand ways to exploit your existing investments while evolving you to the new model. We've chosen to use this newsletter to provide you with some of that information and will be providing you with a series of articles on implementing client/server solutions. We'll give you Hints & Tips along with real customer scenarios and we invite you to send us specific questions or to send us articles about the solution you've implemented. We'll cover topics that show you how you can extend your current environment to integrate client/server solutions into your enterprise with a minimum of effort and we'll show you how you can: Manipulate data in products like Lotus** 123**, IBM Visualizer and Cognos** Impromptu so you can make better decisions, faster. Use WordPerfect** InForms** to take customer orders over the phone and forward them directly to your fulfillment group cutting down order processing time and improving customer service. Build applications with IBM VisualAge*, IBM VisualGen*, Powersoft PowerBuilder** and Microsoft** VisualBasic** which help you do rapid application development for the client/server environment; (see Frank Fillmore s article in this issue on Enabling PowerBuilder Version 3.0a for DB2 VSE & VM.) Automate some activities with products like Forest and Trees** which let you set thresholds and act upon an event sequence. That means you can set a threshold on my order backlog and set an alarm to notify a supervisor to take some action if it starts to get too big customers will notice the faster service on their orders and you'll get a significant edge in today's fast-paced business world. Implementing a client/server environment is as simple as extending your current environment without causing disruption to the way you run your business today. Add DDCS/2 and a DB2/2 base license or DDCS/6000 to your existing SQL/DS Version 3 Release 3 or 4 environment and you can start prototyping your client/server solution immediately.