Data definition syntax - version changes, multitenancy, TDE - Forum - OpenEdge General - Progress Community

Data definition syntax - version changes, multitenancy, TDE

 Forum

Data definition syntax - version changes, multitenancy, TDE

This question is not answered

Anyone out there that remembers in which versions new features like multi-tenancy, TDE were added and would care to share some data definition samples where those are used? As far as I can see the TDE goes as incremental 'update table/index/field', how about multi-tenant and local/global indexes?

Other than that is there really any other way to change the database structure than by using data dictionary/database administration?

Thanks

Marian

All Replies
  • MT was added in 11.0, TDE in 10.2B.

    For TDE, you don't enable encryption by applying an incremental schema change.  At a high level, you do it by:

    - installing the TDE license (Enterprise only)

    - enabling the DB for encryption (creates key store, requires passphrases)

    - creating encryption policies (one per Type I area, or per storage object in a Type II area; doesn't encrypt existing data)

    - (optionally) encrypting the existing data

    When you dump the schema from the DB, you will see DDL statements re encryption, at the object level and in the trailer.  But as I said you can't make these changes by applying a .df.

    I haven't worked with MT but I believe it's a similar story, in that the schema may reflect your MT changes but they aren't applied to the database via a schema change.

    > Other than that is there really any other way to change the database structure than by using data dictionary/database administration?

    Changing the structure (i.e. changing areas) is done via prostrct.  Can you elaborate on what you mean by "changing the structure"?  Do you mean changing the assignment of objects to areas?

  • I'll see if I can dig up a sample of TDE statements in a .df.

  • Here is the relevant bit from the bottom of a .df from a TDE-enabled DB:

    (.df contains an ADD TABLE "cif-vendor-accounts" statement...)

    UPDATE TABLE "cif-vendor-accounts"

     ENCRYPTION YES

     CIPHER-NAME AES_CBC_128

    .

    PSC

    encpolicy=yes

    cpstream=ISO8859-1

    .

    0001419022

  • Thanks Rob,

    that’s indeed the kind of information I was looking for…

    For TDE I’ve already saw that in a .df file, having ‘update table’ statements in a file that is not incremental caught me by surprise but that’s fixed now… although as you say the 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? Is there a way to remove encryption on a table/lob/index, like using DELETE for triggers for instance???

    Area on the other hand is specified in the ‘add table’ statement, can that be changed from incremental df? I guess not, so probably ‘area’ is not valid in ‘update table’ statement. 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?

    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’? Can that be changed from incremental df or still just using command line dba scripts?

    There might be a BNF for DDL somewhere, Gus once said there is one for ABL but haven’t seen unicorns so far… what I’m looking for is being able to parse all data definition files to load that in a database model (diagram), for that every possible constructs need to be supported by the parser or the load will fail. Afterwards, when comparing two models we should be able to generate incremental definition file so again what can be changed from there and what not? It might be that Progress will simply ignore invalid options used in DDL statements in incremental df (like changing the area or encryption in an ‘update table’) but it might well not like those and stop loading changes.

    Not much of a dba, if there is a place in documentation where I can find those kind of answers just point me to the relevant rtfm section please :) 


    Marian Edu

    Acorn IT 
    +40 740 036 212

  • >  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.

  • Thanks Rob,

    so alternate buffer pools can be set with update (table/index) but areas not so much so would better just keep it out of incremental df… it does make sense in a way as not everything can be easy updated once set :)

    For global/local flag on indexes that seems to be available in data dictionary only it’s probably only enabled if the table is multi-tenant, will go ahead with an oesdk install and see how that works.

    Guess will just have to deal with those when we stumble upon new syntax, would e nice to know what to expect but we can also take the AI path and learn as we go ;)
     
    Marian Edu

    Acorn IT 
    +40 740 036 212

  • Global/local indexes are more likely to be tale-partitioning related.
  • Right you are Peter, was thinking about partitioning but got somehow confused about multi-tenant thing… this is also some form of partitioning after all, or maybe not :)

    Think it’s better we just start a beta program to try to test the parser agains as many different definition files as possible.
      
    Marian Edu

    Acorn IT 
    +40 740 036 212