Creating and Managing Tables

You can use PartitionDB to make tables management easier while working the same way as before. Create tables at the Gate, and publish them to increase performance. Publishing a table will replicate it to the partition databases. From then on, things stay as usual.

Let’s assume you create table1 (we use database TwoWestGate that is used along all this tutorial):

These two messages are generated after creating the table:

MSG-60151 Successfully Created Table dbo.table1 on TwoWestGate

MSG-70111 Warning – Table is not published, please run “exec PdbpublishTable ‘TwoWestGate’,’dbo’,’table1′, @TargetDatabaseName=”, @ColumnName=”, @IsEncrypt=0;”

First message notifies that table1 was created on TwoWestGate, and the second says that you may wish to publish it.

Publish your table

At this point, the table has been created at the gate, and is ready to to be used.

If you want to take an advantage of PartitonDB and replicate the table to the partition databases you should publish your table:

exec PdbpublishTable ‘TwoWestGate’,’dbo’,’table1′,@TargetDatabaseName=”,@ColumnName=’Id ‘,@IsEncrypt=0;

PdbpublishTable triggers two actions:

  • The table is created in all the partitions.
  • Partition View is created in the gate and manages all the tables in the partitions.

The message that is returned shows us were the table is replicated to:

MSG-61032 Successfully Published dbo.table1
MSG-60104 Successfully Created Table dbo.table1 on TwoWestGlobalDB
MSG-60104 Successfully Created Table dbo.table1 on SunsetDB
MSG-60104 Successfully Created Table dbo.table1 on VeniceDB
MSG-60104 Successfully Created Table dbo.table1 on WilshireDB

Note that the View has been created automatically after running this code, and that the table has been automatically inherited to the partitions. The screen below shows table1 at partition SunsetDB and the table view Pdbtable1 at the gate TwoWestGate:

Note that in this example we used Id column as as the partition column. Another option is to use PartitionDBType that was defined as a gate attributed (read more about gate attributes here). table2 is using this special type:

The only difference would be that we don’t need to pass the publish API the partition column, since it is already defined:

exec PdbpublishTable ‘TwoWestGate’,’dbo’,’table2′,@TargetDatabaseName=”,@IsEncrypt=0;

Now table2 is replicated to the partitions the same way as table1, and has a view Pdbtable2 at the gate:

Where will my tables be replicated to?

Both table1 and table2 include a partition column. For table1 it was passed to the publish command, while table2 has a column of type PartitionDBTyp;. Therefore, the tables were replicated to the children databases, based on this partition column (read more on partition column here). If your table does not have a column of a such a type, it will be replicated to CommonDB. Look at table3 in the screen below:

More options when publishing a table

You probably noticed that PdbpublishTable gives more options. Remember that these are not mandatory. You can always use the defaults.

DatabaseName

DatabaseName allows you to pass a specific database name to which you want to replicate the table in case you don’t want to replicate it to all partition databases. If you don’t select any database, the table will be replicated to all. This option is valuable in cases that you keep different versions on the partition databases, and need to update only specific database with a new table version.

IsEncrypted

There are cases in which we don’t want to expose tables to third party. In this case PdbpublishTable enables to encrypted the tables that resides at the children databases. As before, this parameter is optional, and if not passed, the tables will not be encrypted.

Table’s manipulation has not been changed!

There is no need to change your existing code. Here is a simple example of select insert update manipulation:

Insert query adds a new entry to the table; PartitionDB will store the entry on the right partition based on the Id. But you should not worry about it. It is done for you.Select query is done once from the gate and will list all the relevant entries from the partition databases (the example also shows ‘selecting’ from the children databases, to show that the results are the same (so you can access directly the partition database, but it is not necessary).

Change Schema

What if you want to make changes to the schema? Let’s say that you want to add a column. You probalby wish to call the alter table once, so just use the view of the table, which is the table with the chosen prefix (a pdb prefix in our case):

alter table pdbtable1 add newCol int

The schema will be changed in all partitions.

Note that the pdb prefix to the table is required for all schema changes, e.g. add indexes, foreign key, e.t.c.