Excellent. I also wanted to take this opportunity to remind everyone who uses
IMAGE/SQL via the SQL interface (ODBC included,) to periodically perform an
UPDATE STATISTICS, especially on the tables, which map out to heavily used
datasets.
Unlike IMAGE, ALLBASE does not keep track of the number of rows in a table.
This will negatively impact the performance of accesses into the table.
You can use the freeware utility I wrote years back called DBTUNE/SQL. It can
be downloaded from the Hicomp website: http://www.hicomp.com/dbtune.htm
It has a facility to do mass UPDATE STATISTICS.
Kind regards,
Denys. . .
Denys Beauchemin
HICOMP
(800) 323-8863 (281) 288-7438 Fax: (281) 355-6879
denys at hicomp.com www.hicomp.com
-----Original Message-----
From: EBEN YONG [SMTP:[log in to unmask]]
Sent: Tuesday, March 13, 2001 2:17 PM
To: [log in to unmask]; [log in to unmask]
Subject: RE: ImageSQL and DBERR 2502
Denys,
Your suggestion gave me an idea... I performed a "SELECT * FROM
SYSTEM.TABLE" and found that one of the the columns was actually called
DBEFILESET. The values contained in this column were either SYSTEM or
IMAGESQL. It occurred to me that it might be possible to actually add a
DBEFILE to the SYSTEM DBEFILESET. I am a bit sheepish here because that's
exactly what the message manual says regarding actions to be taken for DBERR
2502 (although the 'ol canuckle did not register this at the time). My
previous actions were to try adding a DBEFILE of type MIXED to the IMAGESQL
DBEFILESET. This did not reap any benefits, however just a moment ago, I
added a DBEFILE of type MIXED to the SYSTEM DBEFILESET and the query worked!
The entire purpose of this experiment was to connect via ODBC (the SE
version that comes with MPE 6.5) to a couple of IMAGE DB's on the e3000
using PERL on the PC. I'm glad to report that it works!
Eben
#!/perl/pub/perl
use DBI;
open(OUTFILE,">flatfile.txt");
$dbh = DBI->connect('dbi:ODBC:WOODY_HLTHDBE');
$dbh->{RaiseError} = 1; # do this, or check every call for errors
$sth = $dbh->prepare("select a.prov#, count(a.ccn#), b.firstname, b.lastname
from online.drug_claim_load a, health.provider b where b.prov# = a.prov#
group by a.prov#, B.firstname, B.lastname");
$sth->execute;
$rv = $sth->bind_columns(\($prov, $count, $firstname, $lastname));
while ($sth->fetch) {
print OUTFILE '"';
print OUTFILE "$prov";
print OUTFILE '",';
print OUTFILE "$count,";
print OUTFILE '"';
print OUTFILE "$firstname";
print OUTFILE '",';
print OUTFILE '"';
print OUTFILE "$lastname";
print OUTFILE '"';
print OUTFILE "\n";
}
close OUTFILE;
-----Original Message-----
From: Denys Beauchemin [mailto:[log in to unmask]]
Sent: Tuesday, March 13, 2001 10:31 AM
To: [log in to unmask]
Subject: Re: ImageSQL and DBERR 2502
Something just occurred to me. The SYSTEM DBEFILESET is populated with a
bunch
of things, amongst which you will find the views for the various IMAGE/SQL
tables created during the ATTACH command.
What you could do is the following:
Using ISQL, connect to the DBE and then do:
SELECT * FROM SYSTEM.TABLE WHERE DBEFILESET = 'SYSTEM';
Scan the list to see where the owner will be the IMAGE/SQL database name and
you will see all these views. You could delete all the views dealing with
automatic masters and then selectively delete the views for users that would
never access through ISQL or SQL.
You see, when you attach a database, all datasets in that database create a
view for each and every password level, in the SYSTEM DBEFILESET. The vast
majority of these views are useless and should be purged. Please remember
they
will come back when you next detach and attach!
Kind regards,
Denys. . .
Denys Beauchemin
HICOMP
(800) 323-8863 (281) 288-7438 Fax: (281) 355-6879
denys at hicomp.com www.hicomp.com
-----Original Message-----
From: EBEN YONG [SMTP:[log in to unmask]]
Sent: Monday, March 12, 2001 8:29 PM
To: [log in to unmask]
Subject: ImageSQL and DBERR 2502
This question goes out to the ImageSQL gurus. Notice below the DBERR 2502
when the SQL statement is attempted:
isql=> CONNECT TO 'HLTHDBE';
isql=> select a.prov#, count(a.ccn#), b.firstname, b.lastname
> from online.drug_claim_load a, health.provider b
> where b.prov# = a.prov#
> group by a.prov#, b.firstname, b.lastname;
MIXED space exhausted in DBEFileSet SYSTEM. (DBERR 2502)
isql=>
>From the ALLBASE/SQL Message Manual:
*-----------
2502
MESSAGE ! space exhausted in DBEFileSet !. (DBERR 2502)
CAUSE This message can have several meanings as follows:
All the space is exhausted in a DBEFileset if no expandable DBEFiles are
available.
All fixed-length DBEFiles in the DBEFileSet have run out of space, and all
expandable DBEFiles have been fully expanded.
The first parameter is the setting for the DBEFile's TYPE attribute. Valid
settings are INDEX, TABLE, and MIXED.
ACTION Add new DBEFiles of the appropriate type to the DBEFileSet. If
space has been exhausted in the System.DBEFileSet during a query, try adding
more tempspace and more contiguous physical disk space.
*-----------
I have attempted to accomplish the recommended action. From SQLGEN, here
are the statements I have used in an attempt to add DBEfiles to my DB
Environment:
*-----------
/* This file was created with a user language environment of */
/* NATIVE-3000 */
CREATE DBEFILESET HLTHMFS;
CREATE DBEFILESET IMAGESQL;
CREATE DBEFILE HLTHMIX2
WITH PAGES = 10000,
NAME = 'HLTHMIX2',
TYPE = MIXED;
ADD DBEFILE HLTHMIX2 TO DBEFILESET HLTHMFS;
CREATE DBEFILE HLTHMIX3
WITH PAGES = 100000,
NAME = 'HLTHMIX3',
TYPE = MIXED;
ADD DBEFILE HLTHMIX3 TO DBEFILESET HLTHMFS;
CREATE DBEFILE HLTHMIX1
WITH PAGES = 3000,
NAME = 'HLTHMIX1',
TYPE = MIXED;
ADD DBEFILE HLTHMIX1 TO DBEFILESET IMAGESQL;
COMMIT WORK;
*-----------
We have noticed that the error pertains to running out of SYSTEM space. But
ISQL will not allow me to add a mixed type dbefile to the system dbefileset
(IMAGESQL) of more than 3000 pages.
In any event, if anyone can shed some light on this issue, I would really
appreciate it. Thanks.
Eben Yong
Health Plan of San Mateo
|
