SQLSCHEMA utility is extended to support the following schema definitions in multi-tenant database:

  • Writing schema definitions for domains
  • Writing schema definitions for tenants
  • Writing schema definitions for shared and multi-tenant tables
  • Writing schema definition for shared and multi-tenant sequences

The following example illustrates the extended support by SQLSCHEMA for multi-tenancy:

Example: SQLSCHEMA to write schema definitions for a domain

The following example directs the SQLSCHEMA utility to write schema definitions for the domain OpenEdgeA.

sqlschema -u mtuser@mtdomain -a mtuser -d OpenEdgeAprogress:T:localhost:2222:sportsdb

Example: SQLSCHEMA to write schema definitions for two domains

The following example directs the SQLSCHEMA utility to write schema definitions for two domains OpenEdgeA and OpenEdgeB respectively.

sqlschema -u mtuser@mtdomain -a mtuser -d OpenEdgeA,OpenEdgeB   progress:T:localhost:2222:sportsdb

Example: SQLSCHEMA using a % operator

The following example directs the SQLSCHEMA utility to write schema definitions for domains with Access as its last 6 characters.

sqlschema -u mtuser@mtdomain -a mtuser -d  %Access  progress:T:localhost:2222:sportsdb

Example: SQLSCHEMA using the operator _ (underscore)

The following example directs the SQLSCHEMA utility to write schema definitions for domains with OpenEdge as its first 8 characters followed by a single character. 

sqlschema -u mtuser@mtdomain -a mtuser -d OpenEdge_ progress:T:localhost:2222:sportsdb

Example: SQLSCHEMA to write schema definitions for a tenant

The following example directs the SQLSCHEMA utility to write schema definitions for a tenant.

sqlschema -u mtuser@mtdomain -a mtuser -n T1 progress:T:localhost:2222:sportsdb

Example: SQLSCHEMA to write schema definitions for two tenants

The following example directs the SQLSCHEMA utility to write schema definitions for tenants ten1 and ten2 respectively.

sqlschema -u mtuser@mtdomain -a mtuser -n ten1,ten2 progress:T:localhost:2222:sportsdb

Example: SQLSCHEMA to write schema definition for tenants whose names start with Test

The following example directs the SQLSCHEMA utility to write schema definitions for the tenants which start with ‘Test'.

sqlschema -u mtuser@mtdomain -a mtuser -n  Test%  progress:T:localhost:2222:sportsdb

Example: SQLSCHEMA to write a schema definition for tenant whose name starts with T followed by a single character

The following example directs the SQLSCHEMA utility to write schema definitions for the tenants which start with a ‘T' followed by a single character.

sqlschema -u mtuser@mtdomain -a mtuser -n T_ progress:T:localhost:2222:sportsdb

The following example directs the SQLSCHEMA utility to write the schema definitions for a multi-tenant table.

sqlschema -u mtuser@mtdomain -a mtuser -t pub.acct_payable progress:T:localhost:2222:sportsdb

The following example directs the SQLSCHEMA utility to write schema definitions for a sequence, seq1.

sqlschema -u mtuser@mtdomain -a mtuser -Q seq1 progress:T:localhost:2222:sportsdb

The following example directs the SQLSCHEMA utility to write schema definitions for the two sequences seq1 and seq2.

sqlschema -u mtuser@mtdomain -a mtuser -Q seq1,seq2 progress:T:localhost:2222:sportsdb

The following example directs the SQLSCHEMA utility to write schema definitions for sequences that start with ‘Test'.

sqlschema -u mtuser@mtdomain -a mtuser -Q Test%  progress:T:localhost:2222:sportsdb

The following example directs the SQLSCHEMA utility to write schema definitions for sequences that start with seq followed by a single character.

sqlschema -u mtuser@mtdomain -a mtuser -Q seq_ progress:T:localhost:2222:sportsdb

The following example illustrates the use of -z command line option in the SQLSCHEMA utility invocation.

Assuming the database sportsdb is empty, -z option indicates that the dumped definitions for CREATE TENANT statements do not have over-ridden area information for their corresponding tables.

sqlschema -u mtuser@mtdba -a mtuser -Z -t pub.customer,pub.acct_payable -n TN1 progress:T:localhost:2222:sportsdb

The -z command line option is ignored if the database in not empty.

The following example directs the SQLSCHEMA utility to write the schema definitions for a multi-tenant group group1.

sqlschema -u mtuser@mtdba -a mtuser -r group1 progress:T:localhost:2222:sportsdb