R
Rob Fitzpatrick
Guest
> TDE settings might not be changed from incremental .df, adding those statements there will render the incremental .df unusable or it will just be ignored? I confess I haven't messed around with doing things I shouldn't with TDE, so I'm not sure what would happen if you wander off the beaten path 
> Is there a way to remove encryption on a table/lob/index, like using DELETE for triggers for instance??? Yes, you can disable encryption for an object but again, it's like encrypting: via proutil -C epolicy. Think of the .df as a reflection of the end result, rather than a means to an end. > Area on the other hand is specified in the ‘add table’ statement, can that be changed from incremental df? Yes, you can use the AREA qualifier on ADD TABLE or ADD INDEX statements to direct where they go when creating new objects in the .df that you load. But once they're assigned, you can't move them from one area to another that way, at least not directly. The only avenue is either dump/drop/re-add/load or proutil -C tablemove/idxmove. > Alternate buffer pools seems to go the TDE route, found that in an ‘update table’ statement in regular .df file, so not in add table. Can it be changed by loading an incremental df, if so what’s the syntax there? Some good news here ABP has both area-level assignments and object-level assignments. The former are stored in _area, and are only done via offline proutil -C enableb2. The latter are stored in _storageobject, and can be done (somewhat awkwardly) via the data dictionary, or by applying an incremental .df (my preferred route). The .df can be applied online or offline. This does require that the objects in question are in Type II areas. Here is a sort of crude example of creating such a .df for a few tables and their indexes: def var v-tables as char. def var v-table as char. def var i as i. v-tables = "customer,order,order-line". output to value( ldbname( "dictdb" ) + "_abp.df" ). do i = 1 to num-entries( v-tables ): v-table = entry(i, v-tables). find dictdb._file no-lock where _file-name = v-table no-error. if not available _file then next. put unformatted "UPDATE TABLE " + quoter( v-table ) skip. put unformatted ' BUFFER-POOL "Alternate"' skip(1). for each dictdb._index no-lock of _file: put unformatted "UPDATE INDEX " + quoter( _index-name ) + " OF " + quoter( v-table ) skip. put unformatted ' BUFFER-POOL "Alternate"' skip(1). end. end. put "." skip. put "PSC" skip. put "bufpool=yes" skip. put "cpstream=ISO8859-1" skip. put "." skip. put "0000099999" skip. output close. > What I’m really missing is how local/global flag is used for indexes on multi-tenant tables, how the ‘add index’ syntax looks like… or it’s part of ‘update index’? Re MT: this is speculation on my part as I've done very little with MT, but I don't think you'll find that in the schema. My sense from MT and TP (Table Partitioning) is that it is more of a physical concept rather than a logical one. For example a customer table may have a lname-fname index, which logically is one object. But that object may comprise multiple b-trees (storage objects), one per table partition, each being global or local. My guess is that these aren't reflected in the .df but I could certainly be wrong. Sorry I can't be definitive. If you have an OEDK license, best bet is to install AEE and play with it. Or ask someone more knowledgeable > There might be a BNF for DDL somewhere, Gus once said there is one for ABL but haven’t seen unicorns so far… There's nothing I'm aware of in docs or KB to describe the DDL. That would be nice. I'll share it here if I ever stumble across such a thing.
Continue reading...
> Is there a way to remove encryption on a table/lob/index, like using DELETE for triggers for instance??? Yes, you can disable encryption for an object but again, it's like encrypting: via proutil -C epolicy. Think of the .df as a reflection of the end result, rather than a means to an end. > Area on the other hand is specified in the ‘add table’ statement, can that be changed from incremental df? Yes, you can use the AREA qualifier on ADD TABLE or ADD INDEX statements to direct where they go when creating new objects in the .df that you load. But once they're assigned, you can't move them from one area to another that way, at least not directly. The only avenue is either dump/drop/re-add/load or proutil -C tablemove/idxmove. > Alternate buffer pools seems to go the TDE route, found that in an ‘update table’ statement in regular .df file, so not in add table. Can it be changed by loading an incremental df, if so what’s the syntax there? Some good news here ABP has both area-level assignments and object-level assignments. The former are stored in _area, and are only done via offline proutil -C enableb2. The latter are stored in _storageobject, and can be done (somewhat awkwardly) via the data dictionary, or by applying an incremental .df (my preferred route). The .df can be applied online or offline. This does require that the objects in question are in Type II areas. Here is a sort of crude example of creating such a .df for a few tables and their indexes: def var v-tables as char. def var v-table as char. def var i as i. v-tables = "customer,order,order-line". output to value( ldbname( "dictdb" ) + "_abp.df" ). do i = 1 to num-entries( v-tables ): v-table = entry(i, v-tables). find dictdb._file no-lock where _file-name = v-table no-error. if not available _file then next. put unformatted "UPDATE TABLE " + quoter( v-table ) skip. put unformatted ' BUFFER-POOL "Alternate"' skip(1). for each dictdb._index no-lock of _file: put unformatted "UPDATE INDEX " + quoter( _index-name ) + " OF " + quoter( v-table ) skip. put unformatted ' BUFFER-POOL "Alternate"' skip(1). end. end. put "." skip. put "PSC" skip. put "bufpool=yes" skip. put "cpstream=ISO8859-1" skip. put "." skip. put "0000099999" skip. output close. > What I’m really missing is how local/global flag is used for indexes on multi-tenant tables, how the ‘add index’ syntax looks like… or it’s part of ‘update index’? Re MT: this is speculation on my part as I've done very little with MT, but I don't think you'll find that in the schema. My sense from MT and TP (Table Partitioning) is that it is more of a physical concept rather than a logical one. For example a customer table may have a lname-fname index, which logically is one object. But that object may comprise multiple b-trees (storage objects), one per table partition, each being global or local. My guess is that these aren't reflected in the .df but I could certainly be wrong. Sorry I can't be definitive. If you have an OEDK license, best bet is to install AEE and play with it. Or ask someone more knowledgeable > There might be a BNF for DDL somewhere, Gus once said there is one for ABL but haven’t seen unicorns so far… There's nothing I'm aware of in docs or KB to describe the DDL. That would be nice. I'll share it here if I ever stumble across such a thing.Continue reading...