OS/2 eZine - http://www.os2ezine.com
Spacer
16 November 2001
 
Douglas Clark is a program management consultant who first started using OS/2 version 1.3. He's married, with 2 girls, and is old enough to remember when 4 color mainframe terminals were a big thing.

If you have a comment about the content of this article, please feel free to vent in the OS/2 eZine discussion forums.

There is also a Printer Friendly version of this page.

Spacer
Previous Article
Home
Next Article


NetDrive for OS/2. Seamless access to FTP, NFS, VFAT, Psion PDA and more...


Visualizer Query

The Workplace Shell (WPS) is the defining feature of OS/2. It is brilliant design, combining an object oriented desktop with a class library built on SOM, that (theoretically) allows an application written in any language to sub-class desktop objects for use in the application, even to the extent of modifying the behavior of the desktop itself. The intent was that the Workplace Shell, along with the new (for version 2.0) Presentation Manager components, would become the application framework that developers used to build new OS/2 applications. As Gregory Bourassa pointed out at Warpstock 2001, "The Workplace Shell is the killer application for OS/2."


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. Then:

  1. Download the file VISPE.ZIP from ftp://ftp.astrac.com/fromhere/Vis/ENGLISH/ - you have to click the reload button to get the files to appear. While BMT Micro is the place to purchase Visualize they don't have the file on their server. And despite what the BMT Micro catalog says, the VISPE.ZIP file does contain a 60 day demonstration version.
  2. Unzip VISPE.ZIP into a temporary directory (say X:\TEMPDIR), making sure to preserve the directory structure in the zip file.
  3. Change to the X:\TEMPDIR\QUERY and run INSTALL.CMD. This will modify your CONFIG.SYS file and then prompt you to reboot. The prompt is somewhat confusing because you are prompted to press a key to end the Rexx install program, but you have to manually reboot your machine.
  4. Reboot and wait; the second half of the install procedure runs automatically after rebooting.

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:

  • The SQL Database object is a container that stores DB2 tables and views from a database - called SQL Tables and SQL Views.
  • SQL Table objects and SQL View objects represent tables and views in a DB2 database.

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.

  • The Visualizer Table object is used to create a table in Visualizer's local "desktop" database, as opposed to creating a table in DB2. The Visualizer Table is intended, among other things, to be used to store data for disconnected use; for instance a laptop user that needs to access DB2 data for reports while disconnected from the database.
  • The Visualizer Query object is used to create a "query". A query is essentially the same thing as a view in SQL, except the query is stored in Visualizer and a view is stored in the database. Just like a view in SQL, a Visualizer Query can be made up of multiple tables (via a join of some type) - either from a single DB2 database or from a combination of DB2 tables and Visualizer Tables.
  • Report and Chart objects are used to create tabular and chart (pie line and bar graph type) reports.
  • The SQL Statement is used to store a SQL Statement that needs to be repeatedly executed, either for database maintenance purposes or to create complex SQL statements for reports that are not easy to create in a graphic query builder.

We will discuss each of these objects below.

SQL Database Containers

SQL Database objects are Workplace Shell containers that hold SQL Table and SQL View objects. The SQL Table and SQL View objects represent DB2 database tables and views. SQL Table and SQL View objects can only live in a SQL Database folder; likewise the only objects that can exist in a SQL Database folder are SQL Tables and SQL Views. The intent of the SQL Database folder and associated objects is to provide you a way of viewing and working with DB2 databases, tables and views using normal WPS desktop actions, e.g. drop a table object on the shredder to delete it, on a printer object to print it, etc.

SQL Database containers can be created three ways:

  1. By the installation program when you first install Visualizer
  2. By the VISDBC.CMD file/object in the Visualizer - Tools folder. This program creates a SQL Database object for each DB2 database cataloged on your system. The databases are either "local" databases, if you are working on a DB2/2 server machine, or remote databases accessed through DB2/2 CAE (client) software, or both.
  3. By you - when you "tear off" a SQL Database object from the template and assign the folder to a database. We will look at creating a folder from the SQL Database template shortly.

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:

  1. Drag a SQL Database template object to a folder.
  2. Change the name of the new SQL Database object by Alt-left clicking on the object's title and typing a new name.
  3. Double click the object, or select properties from the object's pop-up menu and assign the object to a DB2 database from the list of databases. If you are working on a DB2/2 server and you want to create a new DB2 database, or you want to catalog an existing database that is not already in the DB2 catalog you can do that here by pressing the New button. Otherwise you must pick an already cataloged database from the list.

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:

  • Dragging a table or view icon to a printer object prints the contents of the table or view - although normally you will want to use the Report object in order to have more control over how the data is formatted.
  • Dragging a table or view object to the shredder deletes the object from the container.
  • Right-clicking on a table or view icon gets you a pop-up menu with options for importing and exporting data, along with copying the table definition and opening the table editor to view the data.
  • Each table and view object has Properties that you can use to modify the table or view (within the limits of DB2) or view information about the table/view.
  • Double-clicking on a table or view object displays the data in the table or view in a spreadsheet like format in the Table Viewer. You can edit the data if you have permissions.

SQL Tables & Views

SQL Table and SQL View objects represent tables and views in a DB2 database. The objects can only live in a SQL Database folder, since the folder defines the database to which the table/view belongs. You can create a new table or view by dragging the Table or View template to a Database folder. Likewise you can delete a table or view by dragging the object to the shredder.

Properties

Each object has properties that describe the object. For SQL Views the properties include the SQL statement that defined the view, and the authorizations that apply to the view; the properties for a view are shown below.

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.

Table Viewer

Double-clicking a table or view object, or using the Open As option from the pop-up menu opens the table viewer with that object's data. The viewer can display data in either spreadsheet format or a "form" like format. The viewer is really meant for ad-hoc, quick viewing of a table or view's data and is pretty basic. For instance you cannot format the data values. And while editing data is allowed you cannot edit the data while it is sorted.

SQL Statement

The SQL Statement object is intended for statements that are repeatedly executed, or to create complex select statements for use in a report. You select a DB2 database from the drop down list, enter a SQL statement in the MLE window and press the execute button to send the SQL statement to DB2. If the statement returns values a Table Viewer is opened to display the results; otherwise you just get a message box when executing the statement. You can change the default action (what happens when you double click the object) for the object from opening the statement window to executing the statement. This allows you to create SQL Statement objects with actions you need to do on a routine basis, put them in their own folder, and double-click the objects/statements when you need to execute them.

Visualizer Table

A Visualizer Table is a table of data stored in Visualizer's own database. These tables can be used to temporarily store copies of DB2 data for mobile users when they are disconnected from the database, or they can be used as tables in a database in their own right. You can create a Visualizer Table by importing the definition and data from an exiting table, or by creating a new definition and entering data. If you are importing an existing table you can import from DB2, dBase, CSV and DIF files, and DB2 IXF files.

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.

Visualizer Query

The Visualizer Query object is cross between a funky GUI SQL builder and a simple heterogenous database joiner mechanism. Its main purpose is to provide a GUI mechanism for specifying SQL select statements for use in the Report and Chart objects. In this capacity you could use either the Visualizer Query object to graphically build an SQL select statement or you could use the SQL Statement object to type out a SQL select statement. Both objects are interchangeable as data sources in the Report and Chart objects.

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.

Visualizer Report

The Visualizer Report object is used to create tabular or listing type reports. While the user interface is somewhat clunky it is also very easy to use and powerful enough to create most any kind of reasonable columnar format you can envision. The easiest way to describe what the Report object can do is to show how to create a report and then to look at some of the dialog screens for modifying the report format.

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.

Visualizer Chart

You create a Visualizer Chart object by dragging a template object to a folder or the desktop. You can select your source data either from the menu or by dropping a Visualizer object on the open Chart window.

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.

Batch Processing

Visualizer objects can be manipulated from the OS/2 Command Line, which means that they can be exercised in batch mode. Probably the most interesting way of manipulating Visualizer objects from a batch file is to print reports and charts that you have created, thus allowing for a production-ization of report generation. Create the reports and charts that need to be executed on a routine basis, create a batch file that sends each of those reports and charts to the printer, or that exports the reports and charts to HTML/GIF formats, and run the batch file. You could for example create a batch file for updating reports that are posted to a web site, and then execute that batch file with a cron type program (DragText adds a cron feature to Workplace Shell objects) to make the web updates automatic.

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.

Comments and Conclusions

I really like this product, but I am also at the same time frustrated by it. The Chart object is exceptional. The Report object is clunky but good. The SQL Database folder is a little buggy but great. The table that displays table data, on the other hand is almost unusable for editing data. There are almost no real-life situations where you can successfully edit data that has not been sorted. And SQL databases (read here DB2) by definition return data in a random order without an order clause in the SQL statement. The "feature" in the table viewer that makes ordering the data and updating the data mutually exclusive renders the table viewer pretty much useless for updating database tables. (Surprisingly Visualizer isn't the only one with this problem. It is very easy in Delphi to build a table type data control that allows the underlying data to be edited until you want the data sorted - then it becomes very difficult to handle edits to the data.) Plus you really need to be able to format data that appears in the columns in the table viewer, i.e. specify date formats and numeric output format.

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.

Other Books/References

Since we talked about the Workplace Shell, this book might be interesting:

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).

Previous Article
Home
Next Article

Copyright (C) 2001. All Rights Reserved.