Data definition syntax - version changes, multitenancy, TDE

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,

>  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,

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 :)