Transactional access to Btrieve files

[Database Administrator]

Hi Btrievians!

We’ve got an application primarily accessing data files via Btrieve interface. For some reasons it’s necessary to get read access to _the_same_ ("living") files via transactional interface (SQL SELECT statements).
Fortunately the exact structure of Btrieve files is known and Pervasive supports reusing of files by "in dictionary" and "using" directives. So I can build SQL "files" with something like

CREATE TABLE myTable IN DICTIONARY USING '..\myDir\myTable.btrv' (…)

and

CREATE INDEX myIndex IN DICTIONARY ON myTable (…)

As a matter of principle that works fine so far. But I’ve got the following two problems:

The first problem is that Btrieve application normally runs 24 hours on 7 days a week. Up to now nobody was able to tell me definitively, whether creating SQL "files" while running Btrieve application could cause problems or is safe. Now I had the idea to set Btrieve file to Continuous Operation Mode before performing the above statements. Does anyone (definitively) know whether that would be a safe way to create SQL "file" while Btrieve application is running?

The second problem is that SQL "files" for some of our Btrieve files can’t be created (e.g. because they contain to many columns). In principal that is not a big problem (these files are not so important), but trying to perform the above statements causes bad conditions. In most cases it takes Pervasive a long time to determine, that table can’t be created. That would be acceptable, but sometimes Pervasive really crashes (the complete computer must be restarted). In environment with the need of running Btrieve application all the time this is a very big problem!
Therefore my question: Is there any opportunity to check whether statement will work _before_ performing it with the risk of system crash?

Thanks.

Michael

[Database Administrator]

1) When you run the CREATE TABLE IN DICTIONARY statements, the IN
DICTIONARY is the key clause -- only the dictionary is altered, and no
Btrieve-level conflicts will occur. I do have a better idea, though,
from my own testing. Instead of your method, create a new dictionary in
an empty directory and use "regular" CREATE TABLE statements and CREATE
INDEX statements. You can then compare the new files created to your
production files (via BUTIL -STAT) and if they match, then you are good
to go. To use the new DDF's, simply copy them into your production data
directory.

2) As for this issue, note that PSQL2000i and newer support up to 1500
fields per table. As such, you must have REALLY big tables if you are
exceeding that. You can take the above suggestion one step further, of
course -- instead of doing the work in a separate directory, you can
install a second engine (either another Server Engine or a cheap
Workgroup Engine) on a "work" machine and do all your DDF work there.
You'll be completely stand-alone while working on the DDF's, and
deployment is still as simple as copying over the files...
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach (From Honolulu, HI)
BillBach@goldstarsoftware.com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - Nov. 2008 ***


Michale Schwarz wrote:
> Hi Btrievians!
>
> We’ve got an application primarily accessing data files via Btrieve
> interface. For some reasons it’s necessary to get read access to
> _the_same_ ("living") files via transactional interface (SQL SELECT
> statements).
> Fortunately the exact structure of Btrieve files is known and Pervasive
> supports reusing of files by "in dictionary" and "using" directives. So
> I can build SQL "files" with something like
>
> CREATE TABLE myTable IN DICTIONARY USING '..\myDir\myTable.btrv' (…)
>
> and
>
> CREATE INDEX myIndex IN DICTIONARY ON myTable (…)
>
> As a matter of principle that works fine so far. But I’ve got the
> following two problems:
>
> The first problem is that Btrieve application normally runs 24 hours on
> 7 days a week. Up to now nobody was able to tell me definitively,
> whether creating SQL "files" while running Btrieve application could
> cause problems or is safe. Now I had the idea to set Btrieve file to
> Continuous Operation Mode before performing the above statements. Does
> anyone (definitively) know whether that would be a safe way to create
> SQL "file" while Btrieve application is running?
>
> The second problem is that SQL "files" for some of our Btrieve files
> can’t be created (e.g. because they contain to many columns). In
> principal that is not a big problem (these files are not so important),
> but trying to perform the above statements causes bad conditions. In
> most cases it takes Pervasive a long time to determine, that table can’t
> be created. That would be acceptable, but sometimes Pervasive really
> crashes (the complete computer must be restarted). In environment with
> the need of running Btrieve application all the time this is a very big
> problem!
> Therefore my question: Is there any opportunity to check whether
> statement will work _before_ performing it with the risk of system crash?
>
> Thanks.
>
> Michael

[Database Administrator]

Hi BtrieveBill,

thank You for Your fast and qualified answer! First I have to say I
meant relational access instead of transactional access, sorry.
> 1) When you run the CREATE TABLE IN DICTIONARY statements, the IN
> DICTIONARY is the key clause -- only the dictionary is altered, and no
> Btrieve-level conflicts will occur. I do have a better idea, though,
> from my own testing. Instead of your method, create a new dictionary
> in an empty directory and use "regular" CREATE TABLE statements and
> CREATE INDEX statements. You can then compare the new files created
> to your production files (via BUTIL -STAT) and if they match, then you
> are good to go. To use the new DDF's, simply copy them into your
> production data directory.
That means performing the operations would be safe as well with as
without continuous operation mode?
> 2) As for this issue, note that PSQL2000i and newer support up to 1500
> fields per table. As such, you must have REALLY big tables if you are
> exceeding that.
Yes, because of historical database design we have.
> You can take the above suggestion one step further, of course --
> instead of doing the work in a separate directory, you can install a
> second engine (either another Server Engine or a cheap Workgroup
> Engine) on a "work" machine and do all your DDF work there. You'll be
> completely stand-alone while working on the DDF's, and deployment is
> still as simple as copying over the files...
Yes, some time ago I randomly found that solution (I copied a complete
project including database from one developer engine to another and just
after creating database in control centre all my tables appeared
instantly), so I used this work around. But I hoped there would be a
more elegant solution...

Best regards,
Michael Schwarz

> Goldstar Software Inc.
> Pervasive-based Products, Training & Services
> Bill Bach (From Honolulu, HI)
> BillBach@goldstarsoftware.com
> http://www.goldstarsoftware.com
> *** Chicago: Pervasive Service & Support Class - Nov. 2008 ***
>
>
> Michale Schwarz wrote:
>> Hi Btrievians!
>>
>> We’ve got an application primarily accessing data files via Btrieve
>> interface. For some reasons it’s necessary to get read access to
>> _the_same_ ("living") files via transactional interface (SQL SELECT
>> statements).
>> Fortunately the exact structure of Btrieve files is known and
>> Pervasive supports reusing of files by "in dictionary" and "using"
>> directives. So I can build SQL "files" with something like
>>
>> CREATE TABLE myTable IN DICTIONARY USING '..\myDir\myTable.btrv' (…)
>>
>> and
>>
>> CREATE INDEX myIndex IN DICTIONARY ON myTable (…)
>>
>> As a matter of principle that works fine so far. But I’ve got the
>> following two problems:
>>
>> The first problem is that Btrieve application normally runs 24 hours
>> on 7 days a week. Up to now nobody was able to tell me definitively,
>> whether creating SQL "files" while running Btrieve application could
>> cause problems or is safe. Now I had the idea to set Btrieve file to
>> Continuous Operation Mode before performing the above statements.
>> Does anyone (definitively) know whether that would be a safe way to
>> create SQL "file" while Btrieve application is running?
>>
>> The second problem is that SQL "files" for some of our Btrieve files
>> can’t be created (e.g. because they contain to many columns). In
>> principal that is not a big problem (these files are not so
>> important), but trying to perform the above statements causes bad
>> conditions. In most cases it takes Pervasive a long time to
>> determine, that table can’t be created. That would be acceptable, but
>> sometimes Pervasive really crashes (the complete computer must be
>> restarted). In environment with the need of running Btrieve
>> application all the time this is a very big problem!
>> Therefore my question: Is there any opportunity to check whether
>> statement will work _before_ performing it with the risk of system
>> crash?
>>
>> Thanks.
>>
>> Michael

[Database Administrator]

I understood anyway. ;-)

You must make sure that all users are out of the database to replace the
DDF's. This cannot be done via continuous operations mode, and it WOULD
require a downtime window.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach (From Honolulu, HI)
BillBach@goldstarsoftware.com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - Nov. 2008 ***


Michale Schwarz wrote:
> Hi BtrieveBill,
>
> thank You for Your fast and qualified answer! First I have to say I
> meant relational access instead of transactional access, sorry.
>> 1) When you run the CREATE TABLE IN DICTIONARY statements, the IN
>> DICTIONARY is the key clause -- only the dictionary is altered, and no
>> Btrieve-level conflicts will occur. I do have a better idea, though,
>> from my own testing. Instead of your method, create a new dictionary
>> in an empty directory and use "regular" CREATE TABLE statements and
>> CREATE INDEX statements. You can then compare the new files created
>> to your production files (via BUTIL -STAT) and if they match, then you
>> are good to go. To use the new DDF's, simply copy them into your
>> production data directory.
> That means performing the operations would be safe as well with as
> without continuous operation mode?
>> 2) As for this issue, note that PSQL2000i and newer support up to 1500
>> fields per table. As such, you must have REALLY big tables if you are
>> exceeding that.
> Yes, because of historical database design we have.
>> You can take the above suggestion one step further, of course --
>> instead of doing the work in a separate directory, you can install a
>> second engine (either another Server Engine or a cheap Workgroup
>> Engine) on a "work" machine and do all your DDF work there. You'll be
>> completely stand-alone while working on the DDF's, and deployment is
>> still as simple as copying over the files...
> Yes, some time ago I randomly found that solution (I copied a complete
> project including database from one developer engine to another and just
> after creating database in control centre all my tables appeared
> instantly), so I used this work around. But I hoped there would be a
> more elegant solution...
>
> Best regards,
> Michael Schwarz
>
>> Goldstar Software Inc.
>> Pervasive-based Products, Training & Services
>> Bill Bach (From Honolulu, HI)
>> BillBach@goldstarsoftware.com
>> http://www.goldstarsoftware.com
>> *** Chicago: Pervasive Service & Support Class - Nov. 2008 ***
>>
>>
>> Michale Schwarz wrote:
>>> Hi Btrievians!
>>>
>>> We’ve got an application primarily accessing data files via Btrieve
>>> interface. For some reasons it’s necessary to get read access to
>>> _the_same_ ("living") files via transactional interface (SQL SELECT
>>> statements).
>>> Fortunately the exact structure of Btrieve files is known and
>>> Pervasive supports reusing of files by "in dictionary" and "using"
>>> directives. So I can build SQL "files" with something like
>>>
>>> CREATE TABLE myTable IN DICTIONARY USING '..\myDir\myTable.btrv' (…)
>>>
>>> and
>>>
>>> CREATE INDEX myIndex IN DICTIONARY ON myTable (…)
>>>
>>> As a matter of principle that works fine so far. But I’ve got the
>>> following two problems:
>>>
>>> The first problem is that Btrieve application normally runs 24 hours
>>> on 7 days a week. Up to now nobody was able to tell me definitively,
>>> whether creating SQL "files" while running Btrieve application could
>>> cause problems or is safe. Now I had the idea to set Btrieve file to
>>> Continuous Operation Mode before performing the above statements.
>>> Does anyone (definitively) know whether that would be a safe way to
>>> create SQL "file" while Btrieve application is running?
>>>
>>> The second problem is that SQL "files" for some of our Btrieve files
>>> can’t be created (e.g. because they contain to many columns). In
>>> principal that is not a big problem (these files are not so
>>> important), but trying to perform the above statements causes bad
>>> conditions. In most cases it takes Pervasive a long time to
>>> determine, that table can’t be created. That would be acceptable, but
>>> sometimes Pervasive really crashes (the complete computer must be
>>> restarted). In environment with the need of running Btrieve
>>> application all the time this is a very big problem!
>>> Therefore my question: Is there any opportunity to check whether
>>> statement will work _before_ performing it with the risk of system
>>> crash?
>>>
>>> Thanks.
>>>
>>> Michael