While the intent was certainly laudable, the reality has been less than outstanding simply because very few applications have been made that integrate completely into the Workplace Shell. But when those applications do come along they are a joy to use. Visualizer Query is just such an application: it integrates DB2 into the workplace shell. Or rather - it integrates the common tasks a "normal user" of DB2 needs to get his everyday jobs done into the Workplace Shell - normal user here is contrasted with a database administrator type user.
Like the Workplace Shell , Visualizer Query has been around a long time, since 1994 when I first used it. It was originally an IBM product but apparently never did very well, so the source code was licensed in 1997 to ASTRAC who took over development and support. Time has been very kind to this product: what was once just barely tolerable performance is now very snappy performance due to the processor speeds now in general use; and the almost un-installable setup that was used for Visualizer has been ditched for a newer, uglier but much more reliable install procedure. I liked Visualizer Query when I first saw it in 1994 but was put off by the high price and the extremely difficult (read almost impossible) installation. I forgot about the product until a couple of years ago when I ran across it again on BMT Micro's web site, now named Visualizer PE (Personal Edition.) I downloaded that version and tried to install it, but again I could not get it to install. So again I forgot about the product until a couple of months ago when I downloaded the latest version. This time it was pure joy. Whatever bugs existed in the install procedure seem to be completely squashed; I have installed it multiple times on three different machines and each time the install worked perfectly. And now the price is right: $95 from BMT Micro for the personal edition. Note: Visualizer Query is just one component in Visualizer PE, other components including Statistical Analysis, procedures for automating tasks, and application development for building applications from the other parts.
Let's get the install out of the way. First you have to have DB2/2 installed on your machine - either the CAE (client) or the server, which is covered in this months article on DB2. (page_8.html) Then:
Now you should have the Visualizer folder on your desktop that looks something like this. Your folder will probably look somewhat different because the install procedure will (optionally) create "SQL Database" objects (containers) for each DB2 database catalogued on your machine. I chose to create my own SQL Database objects and put them in their own folder, which I will explain below, so the folder above looks slightly different from what will appear on your machine.
To understand Visualizer we have to stop a moment and think about how a WPS application is designed, which is best done by quoting from the IBM Redbook Volume 3: Presentation Manager and Workplace Shell:
In the ideal, purely object-oriented user interface, there would no longer be anything that a user would recognize as a program - there would only be objects, all with their own unique behaviors and uses. As long as the user is provided with suitable tools (that is object classes), he can work out how to accomplish any particular task without having to learn to use an application program specifically designed for that task. What we might loosely call a Workplace Shell application is really no more than a collection of Workplace Shell object classes.
This exactly describes Visualizer - you don't have a group of programs installed on your machine (well you do but you don't see them), but rather you have a set of WPS classes or objects that you work with to accomplish your tasks. The meat of Visualizer then is in the templates it provides, because you create new Visualizer "objects" from the templates for accomplishing these tasks. The templates appear in the desktop Templates folder and are shadowed in the Shadows of Templates folder in the Visualizer folder.
The template objects and tasks are:
You use SQL Database objects to create and delete DB2 databases, and to work with tables and views in that database. You use SQL Tables and SQL Views to create new tables and views, delete existing tables and views, and work with tables and views, e.g. alter table definitions, change access authorities, create indexes, etc.
We will discuss each of these objects below.
SQL Database containers can be created three ways:
In the figure below you can see three SQL Database objects - GENERAL and MISC are DB2/2 v 5 databases on an OS/2 WSeB machine, SAMPLENT is a DB2 v6 database on Windows NT. The machine I am running Visualizer on for this article is an eComStation machine running DB2/2 v 6 CAE.
SQL Database objects are Workplace Shell containers that have two views: the Icon view shown below
and a Details view. (The Estimated Rows figure comes from the catalog statistics used by the DB2 query optimizer when choosing an access plan; it is generated and updated with the DB2 run statistics command which unfortunately is not one of the pop-up menu options for SQL Tables.).
You can determine which tables and/or views from the database are displayed in the folder with the folder's Properties. The default is to display all tables and all views with a schema (owner) that matches the ID you are logged on with. You define what is to be included in the folder in the Include page of the Properties notebook. In the example below I am limiting the folder to show only those tables and views "belonging" to the DB2 schema (user ID) ADMIN0.
There is actually a bug here: I had to log on as user ADMIN0 on my machine in order to be able to get all the tables to show up in the folder (ADMIN0 is the administration ID on the DB2/2 server machine containing the MISC database.) Since that is not my normal LAN log on ID I had to log on locally as ADMIN0 in addition to my network logon of REQ2. (ASTRAC was not able to duplicate this bug.) For the local logon to work you must do it before you open any Visualizer objects.
Note on OS/2 Log In and User IDs: OS/2 is somewhat strange in that you can be "logged on" to a single OS/2 machine as multiple user IDs at the same time. You can only log on to one domain (the LAN network) at a time with one user ID, but you can then log on "locally" to your own machine multiple times with different user IDs. The easiest way to logon locally is to open a Command Prompt window and type
logon
userid
/P:
password
/L
the /L means this is a local logon.
To create a new SQL Database object from a SQL Database template:
It is very likely that you will want to create multiple SQL Database objects that point to the same DB2 database with each SQL Database object filtered for just the tables in which you are interested, although you can have one SQL Database container show all the tables and views in the database.
You work with the objects in a SQL Database container with normal Workplace Shell actions:
Table properties include things that make sense for a table: columns and their definitions, constraints, primary keys and indexes, and authorizations. The Columns page is shown below.
The indexes page is shown below. This page lists all the indexes that belong to a table, along with the index "definition".
The Authorizations page is a nice way of viewing and changing the authorizations assigned to each user or group.
You can change certain properties for existing objects, mostly within the limits of what DB2 allows. For example you can add new columns to an existing table, but you cannot change an existing column. Which pretty much mirrors what DB2 allows.
Visualizer Tables are more sophisticated than dBase tables in that you can specify validation criteria which must be passed in order to change columns or enter new data for each column. This feature allows Visualizer Tables to be realistically used for simple off-line data entry - although you would have to manually create validation criteria that match DB2 database requirements for that column.
To create a Visualizer Query object you drag a template to a folder, open the Query object, and use the Data - Select Tables and Queries menu option to add tables to the Query. Then you link the tables together with the Open Link Settings dialog, and select which columns from the table(s) are to be included with the Query - Select Columns menu option. The figure below shows the Query screen: the top half displays the table(s) involved and their links, while the bottom half displays the columns that are included in the query. You can also create calculated columns where you specify the calculations in Visualizer syntax.
A single Visualizer Query object can only work with tables from either DB2 or Visualizer - you cannot mix the two sources in a single Query. However you can create a Query that consists of a DB2 table, and then use that Query object as a data source in another Query that also uses a Visualizer Table, thereby joining DB2 and Visualizer data together.
Reports are created by dragging a Visualizer Report template to a folder or the desktop. You then define the source tables either by using the Data - Select a Table or Query menu option, or by dropping a SQL Table, SQL View, Visualizer Query, Visualizer Table, or SQL Statement object on the report.
When you drop an object on the report defining the source table(s) you will get a preliminary screen where you pick the columns that are to appear in the report, along with a groups you may want to create. The order you select the columns determines the order they appear in the report, which is very easy but also makes it difficult on this screen to insert a column into the middle of the selection if you need to. You can however drag columns to new locations on a special "Customize" dialog screen. You can also select a subset of the rows contained in the source table(s) with this screen.
Once you have established the report columns and possibly report groups, the next steps are to format data in the report. This is accomplished through a main settings screen which provides access to lower level more detailed settings dialogs - by clicking the button next to the area you want to change.
The report title is controlled by the Top Title dialog. Each line at the top of the dialog represents a piece of text that appears in the title area. The line the element appears on, counting from the top of the page, is specified on the Show element on line control; the horizontal placement is controlled by selecting an alignment button. What appears in the title is determined by what you type on the line for that element. You enter text and variables, which are indicated by a number prefixed by an underscore; you can see variables on line 2 and line 3 in the figure below. Some variables are created for you, like _13 which is the current page number and _11 which is the current date.
The Column Heading dialog is where you control what heading text appears above a column.
Groups are formatted in this dialog.
The report below took me about 5-10 minutes to create, and this was the first time I really messed with the Report object in depth. The data displayed in this report comes from DB2/2 and from a dBase table that I imported into a Visualizer table and then linked together in a Visualizer Query object.
Reports can be exported in a number of formats, one of which is HTML, as shown below. The export to HTML has two different options that affect the formatting of the HTML file.
When you do that you are presented with a dialog where you choose the data columns you want to use as your X and Y axis. The Numeric and Date buttons at the top of the dialog determine which data columns are shown in the Columns list below.
Using the same data for this chart as I used for the Report, this is the Chart I created. It shows the quantity of videos rented by month. Selecting the Order Date column as the X axis automatically caused the Chart to summarize (group) the data by that column. This chart took me 2 minutes to create - this first time I ever used a Chart object. Pretty easy.
Switching the chart type to a pie chart, by clicking the pie chart button on the tool bar, results in this type of chart. Notice that the Chart object has automatically determined what percentage of the whole each month's quantity is in order to create the pie.
The chart obviously needs some adjustment to be readable. The Text - Pie Slice Labels menu option opens a dialog for changing the label configuration.
You can also include an image in the chart, either as the chart's background or as a movable object that appears on the chart.
Visualizer Charts, like Reports, can be exported or saved to another format, the most useful for charts probably being GIF since that is a common graphic file format for inclusion on web pages.
When I sent this article off to ASTRAC for comments it was pointed out to me that I had not even mentioned other parts of Visualizer, like a Vis Procedure component that is made for automating tasks (as an alternative to using batch files and Rexx), or the Vis Development that allows you to build entire applications based on the other components.
But what really frustrates me is that Visualizer also has the potential to be a fabulous killer application by including ODBC support. Imagine being able to do all that you can do now with Visualizer (assuming the table viewer worked correctly) with any ODBC data source. You could view and edit PostgreSQL data, produce reports on MS SQL Server information and output charts on Oracle data. Now take this one step further: imagine if ASTRAC implemented the feature where you could drag SQL Table objects from one Database folder to another. You could, say, copy dBase data into PostgreSQL by merely dragging a SQL Table from a dBase Database folder and dropping it on a PostgreSQL Database folder. I know of no other product on any other platform that provides that kind of visual manipulation of disparate data sources.
Do I recommend the product? Yes! (But I would also recommend contacting ASTRAC and asking them to fix the problems.) There aren't any other Workplace Shell applications for databases. And this packs a lot of value for the $95 price.
Please note that I have not tested this against DB2/2 version 7 and do not know if it will work. In theory it should, but I have other applications that work fine with earlier versions of DB2/2 but do not work correctly with later versions.
OS/2 Configuration Techniques: "Cracking" the Workplace Shell. IBM Redbook GG24-4201-00
This document describes interfaces to the Workplace Shell of OS/2 version 2.1. It provides a discussion and examples of using the CONFIG.SYS, .RC files, .INI files and programs to install, customize and distribute the OS/2 Workplace Shell in a stand-alone and distributed environment. It also talks about associations between files and shell objects.
I quoted from this book when describing a Workplace Shell application.
OS/2 Version 2.0 Volume 3: Presentation Manager and Workplace Shell. IBM Redbook GG24-3732-00
This document describes the Presentation Manager component of OS/2 version 2, along with the Workplace Shell - how to use it and how to develop applications for it. This document is one volume of a five volume set, the other volumes covering: the Control Program (GG24-3730), DOS and Windows environment (GG24-3731), the Print Subsystem (GG24-3775), and Application Development (GG243774).
This article is courtesy of www.os2ezine.com. You can view it online at http://www.os2ezine.com/20011116/page_4.html.