Issue adding an extent to a v9 database

[TomScott]

I'm working on a v9 progress db that's running out of room in the db. I've been researching extents but am unsure of if my understanding is correct and if it is, why my syntax isn't working.

Here is my .st file:
#
b E:\System\D\testdb.b1
#
d "Schema Area":6,32 E:\System\D\testdb.d1
d "Schema Area":6,32 E:\System\D\testdb.d2

I've just recently added the .d2 line.

After this, I then try to run the prostrct add command:

c:\dlc91e\bin\prostrct add testdb E:\System\d\testdb.d2 300000

----

I've tried various entries for the 300000, the 6,32 which includes different sizes, 6,31;1 along with other options I've found on progress talk, but I'm getting a: syntax error in structure file - line should start with.... But, i don't think there's an issue with my st file. I've also tried to create a special new .st file just for the new extent but this leads to many questions.

My goal is to have more capability in my database. My default plan is to add a new db and to just add that in the appbuilder, but this isn't ideal. This extent option sounds promising, but I'm missing something (or a lot).

Any help would be appreciated.

Thanks for the time.

 

[TomBascom]

To add an extent you want to create a structure file that only has new extents in it, you don't list the old ones in there. The new extent # needs to be one greater than the last existing extent. So if you end with .d1, you will be adding .d2. Something like this:

# add.st
#
d "Schema Area":6,32 E:\System\D\testdb.d2

Then run:

prostrct add testdb add.st

Run "prostrct list testdb" to get an up to date structure file reflecting what you really have setup. So, assuming the structure shown above for your testdb is actually:

#
b E:\System\D\testdb.b1
#
d "Schema Area":6,32 E:\System\D\testdb.d1

The old .d1 extent will be converted to a fixed extent at its current size and the new .d2 extent will be a variable extent, If you would like to add a fixed and a variable then add.st would be:

# add.st
#
d "Schema Area":6,32 E:\System\D\testdb.d2 3000000
d "Schema Area":6,32 E:\System\D\testdb.d3

(I picked 3000000 because you had that in your example. That works out to 3GB which would only work if you have "large files" enabled and I don't remember if that was even possible with v9 on Windows -- that was 30 years ago when my hair was a lot darker and there was more of it... Change it to 2000000 if 3GB doesn't work.)

Also, you really don't want to be stuffing all of your data into the schema area. That's a "worst practice". You will need to dump & reload to fix that and if you're going to do that you should also take a look at moving to the modern world and OpenEdge 12.8.

 

[TomScott]

Thanks @TomBascom. The separate file is what did it. We're actually creating these databases from another system so we don't really have an option to create less data unless we remove system files. Now upgrading might make sense, but this was helpful for now. Hope ypu have a good one.

 

[Rob Fitzpatrick]

Also, you really don't want to be stuffing all of your data into the schema area. That's a "worst practice".

We're actually creating these databases from another system so we don't really have an option to create less data unless we remove system files.

@TomScott I think you misunderstood. The suggestion from @TomBascom was not to load less data, but rather about how you structure it. Specifically, your application data should be in separate storage areas, and never in the Schema Area. In Progress 9.x and later, you are not limited to just using the Schema Area (area number 6).

You can add your own storage areas, and adjust the application schema prior to the data load, to assign each of the schema objects to the desired area. And if an existing database is already structured with everything in the Schema Area, you can get to a new structure by creating a new version of the database via a dump and load, as Tom said above.

A better final structure could look something like this:

#
b E:\System\D\testdb.b1
#
d "Schema Area":6 E:\System\D\testdb.d1
#
# your tables are assigned here
d "Data":7,128 E:\System\D\testdb_7.d1 f 2000000
d "Data":7,128 E:\System\D\testdb_7.d2 f 2000000
d "Data":7,128 E:\System\D\testdb_7.d3 f 2000000
d "Data":7,128 E:\System\D\testdb_7.d4 
#
# your indexes are assigned here
d "Index":8,1 E:\System\D\testdb_8.d1 f 2000000
d "Index":8,1 E:\System\D\testdb_8.d2 
#
# adjust the numbers and sizes of extents to suit your needs

In this hypothetical database, the Schema Area only needs one small extent because all it contains are the meta-schema and other system tables and indexes. In this example we also have a Data area with 6 GB of pre-allocated space in fixed extents, plus a variable-sized extent, and an Index area with 2 GB of pre-allocated space and a variable.

All of the application tables would be assigned to the Data area (area number 7), and their indexes assigned to the Index area (area number 8). You can create lots of storage areas; there are various good reasons to do so and schools of thought on how to configure them. For example, when a database has a very large table, a customary practice is to create a separate storage area just for that one table, and another area just for its indexes. Having a well defined (and written!) structure-design policy for your databases, and maintaining this discipline as you make future schema changes, makes database maintenance much easier, especially as your databases grow.

Here is an example, from the character-mode Data Dictionary, of adding a table and an index to application storage areas (note the "Area:" textboxes):




Note: your UI will look different because this I did this in release 12.8.

Note the AREA "<area name>" qualifiers on the ADD TABLE and ADD INDEX statements in the dump of the schema:

Knowing this syntax allows you to make wholesale changes to the assignment of your schema objects to areas by editing the schema defintion file (testdb.df) prior to loading your schema and data, without having to fiddle with the Data Dictionary UI for each object.

 

[TomScott]

@Rob Fitzpatrick well, you've pretty much blown my mind with this. Thank you for this write-up and I apparently have some research to do. I hope you have a great day.