We run SX Enterprise here on Progress version 10.1B03. I have a storage area for Supplier link called slink. The indexes are in slink_idx. It only stores the following tables SLEH,SLED,SLEDN and SLEDV. Those that are familiar the setups are in another storage area. We recently went to Linux and prior to this I was removing the slink area about every couple of months, so the data would not get out of control. I want to automate this process or at least in a nice a tidy shell script.
I have this issue: 1. How to remove the SLEH,SLED,SLEDN and SLEDV from that database after the areas have been truncated. Currently I go in thru the database and manually delete them.
Thanks in advance
Charles
Manually modifying the metaschema without using the provided Data Dictionary/Data Administration tools is potentially dangerous and therefore unsupported.
You need to make sure that all the _File table relations are deleted first.
Note below, I've included the _Partition-Set for example, which was introduced for Multi-Tennancy. Future feature enancements or core code updates may add further relations.
So if you're going to backend this venture, always make sure there is a valid backup of the database before doing so ...
FIND FIRST _File WHERE _File._File-name = "<mytablename>"
AND NOT _File._Frozen
AND NOT _File._Hidden EXCLUSIVE-LOCK.
FOR EACH _Constraint OF _File:
FOR EACH _Constraint-Keys WHERE _Constraint-Keys._Con-Recid = RECID(_Constraint):
DELETE _Constraint-Keys.
END.
DELETE _Constraint.
END.
FOR EACH _Index OF _File:
FOR EACH _Index-field OF _Index:
DELETE _Index-field.
END.
DELETE _Index.
END.
FOR EACH _File-trig OF _File:
DELETE _File-trig.
END.
FOR EACH _Field OF _File:
FOR EACH _Field-trig OF _Field:
DELETE _Field-trig.
END.
DELETE _Field.
END.
FOR EACH _Partition-Set WHERE _Partition-Set._Object-number = _File._File-Num:
FOR EACH _Partition-Set-Detail OF _Partition-Set:
DELETE _Partition-Set-Detail.
END.
DELETE _Partition-Set.
END.
DELETE _File.
RELEASE _File.
If the table is in it's own area, I'd recommend truncating the area and then re-activating the table's indexes over messing with the metaschema - it'll be a lot faster and it'll be supported.
I guess you think this code actually works then, eh?
I've used code like this in the past, and it has "worked". There are a couple of drawbacks to it though:
1) It's unsupported. If one uses this and something goes wrong, then PSC won't be nearly as sympathetic as they would be if this was done in a supported manner. As such, this kind of code should only be used on db's where data loss doesn't matter - like test environments or on throw-away data areas similar to what the OP is doing.
2) An area truncate and index activate is a lot faster, as well as being officially supported. Deleting tables and indexes by nuking metaschema records deletes all the records as one honking big transaction, which means lots of .bi activity and it's associated disk activity. Area truncates don't have that issue, and it's a method PSC supports.
I know this is an old thread but... I'm confused.
What is the relevance of having recently moved to Linux? Did that move also involve changing the storage areas so that you no longer have an area dedicated to these tables and indexes and so now you can no longer simply "truncate area"?
If that is the problem then the solution is (relatively) easy -- move the tables and indexes back to dedicated areas.
Why do you want to remove the definitions of the table after the area has been truncated? You're just going to start adding data back to them again so what is the point? Or has your requirement been misunderstood?
If you really must remove the schema the supported way to do it would be to create a delta df that removes the tables. You could script that just like any other schema change. If you're really removing these all the time then you must also be adding them back so you also presumably have a df file for doing that.