Gentle Listers,
 
Recently, I was confronted with a situation dealing with attached databases.
 If  this is not of interest to you, please read no further.
 
One of the products I sell is a 3 day on-site consulting gig which I call an
IMAGE/SQL Client/Server Kick start.  This is where I go on site and go
through the details of IMAGE/SQL, how it is attached, how to manage it and
then we attach one of the customer s databases and we start doing access to
the data with ISQL, load up the ODBC API and then use shrink-wrap software
and Visual BASIC.   One of the issues we cover during the administration
phase is the need for detach and attach, and how it relates to the database
tool at that location. And here is where I encountered the problem.  First
let me set this up for you.
 
One of the recent gigs I did was at a manufacturing company, which uses their
IMAGE databases to hold manufacturing and shipping data.  As with most
databases used for these purposes, they have compound items declared in the
schema.  Moreover these compound items are of type P.  The data contained in
the P fields is dollar amounts and quantity.  The granularity of the data is
to 3 decimal places.
 
When we performed the attach, IMAGESQL.PUB.SYS generated a number of new
columns for the compound items:
 
QTY   12P8    became  QTY_1 DECIMAL(8,0)
                                   QTY_2 DECIMAL(8,0)
                                     .
                                     .
                                   QTY_12 DECIMAL(8,0)
 
So far so good.  Now for the sake of the users of the shrink-wrap software
and the VB applications and anyone else accessing the data through the SQL
interface, we decided to SPLIT the items giving them the proper decimal
places so that everyone accessing the data would have the same mask over the
values.
 
Sensible enough, but it can get tedious to do all the splits, especially when
it gets to compound items.  You have to issue the command for each sub item:
 
SPLIT DATASET.QTY_1 INTO QTY_1:P8:DECIMAL(8,3);
SPLIT DATASET.QTY_2 INTO QTY_2:P8:DECIMAL(8,3);
.
.
SPLIT DATASET.QTY_12 INTO QTY_12:P8:DECIMAL(8,3);
 
Another reason for splitting a compound item is that the newly generated
column name can be quite cryptic, after all it is the original column name to
which is appended an underscore followed by the index of the item.  With
judicious use of the SPLIT command, the sage DBA can transform an
incomprehensible name to something that the end user with a shrink-wrap tool,
such as MS Word can understand and use.
 
SPLIT DATASET.ADR_4 INTO CITY:X30:CHAR(30)
 
Trust me it is much more user friendly to use City rather than ADR_4.
 
As you do all these splits, you keep telling yourself that you only need to
do this once, and that everyone else will benefit.
 
Well, I hoped that we would only do it once.  We had to change an item, so we
used a common database tool to effect the changes.  The tool detected that
the IMAGE database was attached and promptly detached it.  Then it went on to
flawlessly effect the structural changes and finally it stated that it was
now re-attaching the database.  I was hoping the tool was smart enough to
capture the splits and to re-apply them.  During the adrenaline-generating
phase of the attach, I figured out that whilst it may have detected the
splits, it was applying them at the wrong time.  IMAGESQL.PUB.SYS was invoked
by the tool and proceeded to generate a truckload of errors.  My heart sank
as I realized that the SPLITs were all lost.
 
To add insult to injury the tool did not update the creator to mode 1.
 
It is now apparent to me that any database with compound items is at risk of
not being resynchronized properly when a mainstream database restructuring
tool is used.
 
The problem goes deeper.  If you start creating views to access the data in
the attached IMAGE datasets, these views disappear when the database is
detached.  The current versions of the mainstream database tools do not save
the views.
 
If you want to use MS Access attached todetail datasets you will need to use
views (more on this in another post), these views will disappear.
 
Now, I may be making a big deal out of nothing, but I want people to be
forewarned and not be bitten by the same problem in the database tools as I
was.
 
Kind regards,
 
Denys. . .
 
P.S. Part Deux :-) coming soon.