ddlgen

Description

A Java-based tool that generates definitions for server- and database-level objects in Adaptive Server.

You must have either an sa_role or sso_role to generate DDL for:

• Encryption keys

• Logins

• Roles

For all other objects, users do not need any specific permissions or roles to generate DDL.

The command-line version of ddlgen is llocated in:

• (UNIX) $SYBASE/$SYBASE_ASE/bin.

• (Windows) %SYBASE%\%SYBASE_ASE%\bin.

Syntax

ddlgen 
	-Ulogin
	-Ppassword
	-S[[ssl:]server | host_name : port_number]
	[-I interfaces_file]
	[-Tobject_type]
	[-Nobject_name]
	[-Ddbname]
	[-Xextended_object_type]
	[-Ooutput_file]
	[-Eerror_file]
	[-Lprogress_log_file]
	[-Jclient_charset]
	[-LC -N logical_cluster_name
	-F[ % | SGM | GRP | USR | R | D | UDD | U | V | 
		P | XP | I | RI | KC | TR | PC ]

Or

ddlgen -v

Parameters

Examples

Example 1

(Archive database) To generate DDL for all archive databases, use the extended filter option “OA.”

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TDB -N% -XOA

To generate DDL for a single archive database, use the syntax for normal databases. This example creates DDL for the archive database adb1.

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TDB -Nadb1

Example 2

(Caches) Generates DDL for a cache called default data cache on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TC -N"default data cache"

To generate DDL for all caches:

ddlgen -Ulogin -Ppassword -Sserver:port -TC -N%

Example 3

(Databases) Generates DDL for a database called pubs2 on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TDB -Npubs2

If you do not specify a dbname, ddlgen generates DDL for the default database of login:

ddlgen -Ulogin -Ppassword -Sserver:port

If you do not use the -T parameter, ddlgen generates DDL for a default-type database:

ddlgen -Ulogin -Ppassword -Sserver:port -Ndbname

To generate DDL for all databases:

ddlgen -Ulogin -Ppassword -Sserver:port -TDB -N%

Example 4

(Defaults) Generates DDL for a default called “phondflt” owned by jones in the pubs2 database on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TD -Njones.phonedflt -Dpubs2

Alternatively, because ddlgen allows you to use a fully qualified name in the -N flag, omit the -Ddbname and include the database name in the -N option:

ddlgen -Ulogin -Ppassword -Sserver:port -TD -Ndbname.owner.defaultname

To generate DDL for all defaults in a database owned by “owner”:

ddlgen -Ulogin -Ppassword -Sserver:port -TD -Nowner.% -Ddbname

Example 5

(Database device) Generates DDL for a database device called master running on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TDBD -Nmaster

To generate DDL for all database devices:

ddlgen -Ulogin -Ppassword -Sserver:port -TDBD -N%

Example 6

(Dump device) Generates DDL for a dump device called tapedump1 running on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TDPD -Ntapedump1

To generate DDL for all dump devices:

ddlgen -Ulogin -Ppassword -Sserver:port -TDPD -N%

Example 7

(Encrypted keys) Generates system encryption passwords along with DDLs for encryption keys when you include the extended option -XOD. The output generates the sp_encryption statement followed by DDL statements for all encrypted keys. This generates DDL for the login “george” on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TEK -Ngeorge -XOD

To generate DDL for all the encrypted keys in the authors database on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TEK -Nauthors.dbo.%

Example 8

(Encrypted keys) Generates DDL for all encryption keys in the accounts database on a machine named “HARBOR” using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TEK
    -Naccounts.dbo.%

Alternatively, you use the -D option to specify the database name.

Example 9

(Encrypted keys) Generate DDL for an encryption key “ssn_key” in a the SampleKeysDB database:

ddlgen -Usa -P -Sserver -TEK -NSampleKeysDB.dbo.ssn_key

Example 10

(Encrypted keys) Uses -FEKC to avoid creating DDL for key copies when generating DDL for the “ssn_key” encryption key:

ddlgen -Usa -P -Sserver -TEK -NSampleKeysDB.dbo.ssn_key      -FEKC

Example 11

(Encrypted keys) Generates DDL for “ssn_key” and its key copies:

ddlgen -Usa -P -Sserver -TEK -NSampleKeysDB.dbo.ssn_key

Example 12

(Encrypted keys) Issues a ddlgen command on a table called employee, which has an “ssn” column that is encrypted with encryption key “ssn_key,” and a decrypt default value that is set to 100:

ddlgen -Usa -P -Sserver -TU -N db1.dbo.employee

Example 13

(Engine groups ) Generates DDL for an engine group called LASTONLINE running on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TEG -NLASTONLINE

To generate DDL for all engine groups:

ddlgen -Ulogin -Ppassword -Sserver:port -TEG -N%

Example 14

(Execution class) Generates DDL for an execution class called EC2 running on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TEC -NEC2

To generate DDL for all execution classes:

ddlgen -Ulogin -Ppassword -Sserver:port -TEC -N%

Example 15

(Extended stored procedures) Generates DDL for the xp_cmdshell extended stored procedure in the pubs2 database, owned by Jones and running on a machine named HARBOR using port 1955, by using the fully qualified dbname.owner.extendedstoredprocedure format with the -N option:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TXP -Npubs2.jones.xp_cmdshell

Alternatively, use the -D option instead of using the fully qualified name:

ddlgen -Ulogin -Ppassword -Sserver:port -TXP 
    -Nowner.extendedstoredprocedure -Ddbname

To generate DDL for all extended stored procedures:

ddlgen -Ulogin -Ppassword -Sserver:port -TXP -Ndbname.owner.%

Example 16

(Filters) Generates DDL for the authors table in the pubs2 database, filtering for all indexes (I), and referential integrity constraints (RI), primary and unique key constraints (KC), triggers (TR), and partition condition (PC) from the DDL of a table:

ddlgen -Uroy -Proy123 -TU -Nauthors -Dpubs2 -F%

Alternatively, specify each of the filters individually:

ddlgen -Ulogin -Ppassword -TU -Ndbname.owner.table
    -FI,RI,KC,TR

This generates the definition of table_name while filtering out foreign keys and primary-unique keys:

ddlgen -Ulogin -Ppassword -TU -Ntable_name -Ddbname 
    -FRI,KC

Both of these generate foreign keys for a specified user in the entire database:

ddlgen -Ulogin -Ppassword -TRI -N%.%.% -Ddbname

Or:

ddlgen -Ulogin -Ppassword -TRI -Ndbname%.%.%

Both of these generate DDL for the primary and unique keys of all the tables in a database that begin with “PK”:

ddlgen -Ulogin -Ppassword -TKC -Ndbname.%.%.PK%

Or:

ddlgen -Ulogin -Ppassword -TKC -N%.%.PK% -Ddbname

This generates schema-only definition of a database:

ddlgen -Ulogin -Ppassword -Sserver:port -TF -Ndbname -F%

Alternatively, specify each of the filters individually:

ddlgen -Ulogin -Ppassword -Sserver:port -TDB -Ndbname 
    -FSGM,GRP,USR,R,D,UDD,V,P,XP,I,RI,KC,TR

This generates the database DDL skipping the compiled object:

ddlgen -Ulogin -Ppassword -Sserver:port -TDB -Ndbname 
    -FTR,D,XP,V,R

This generates database definition without a table definition:

ddlgen -Ulogin -Ppassword -Sserver:port -TDB -Ndbname 
    -FU

Example 17

(Groups) Generates DDL for a group called “public” in the pubs2 database, running on a machine named HARBOR using port 1955, by using the fully qualified dbname.groupname format in the -N option:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TGRP -Npubs2.public

Alternatively, use the -D option to specify the dbname:

ddlgen -Ulogin -Ppassword -Sserver:port -TGRP -Ngroupname -Ddbname

To generate DDL for all groups:

ddlgen -Ulogin -Ppassword -Sserver:port -TGRP -Ndbname.%

Example 18

(In-memory databases) Generates DDL for an in-memory database:

 ddlgen -Uroy -Proy123 -SHARBOR:1955 -TDB -Nimdb_1

Example 19

(In-memory databases) Generates DDL for an in-memory temporary database:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TDB -Nimdb_temp1

Example 20

(Indexes) Generates DDL for an index called au_lname for the table authors owned by dbo, in the pubs2 database:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TI -Ndbo.authors.au_lname -Dpubs2

Alternatively, because ddlgen allows you to use a fully qualified name in the -N flag, omit the -Ddbname and include the database name in the -N option:

ddlgen -Ulogin -Ppassword -Sserver:port
    -TI -Ndbname.owner.tablename.indexname

If you use a fully qualified name, you may omit the -D option.

To generate DDL for all indexes for a single table:

ddlgen -Ulogin -Ppassword -Sserver:port -TI 
    -Ndbname.owner.tablename.%

To generate DDL for all indexes of all tables in a database:

ddlgen -Ulogin -Ppassword -Sserver:port -TI 
    -Ndbname.%.%.%

For example, this generates DDL for all indexes for all tables in the pubs2 database:

ddlgen -Usa -P -SHARBOR:1955 -TI -Npubs2.%.%.%

Example 21

(Keys) Both of these generate DDL for the primary and unique keys of all the tables in a database that begin with “PK”:

ddlgen -Usa -P -TKC -Ndbname.%.%.PK%

Or:

ddlgen -Usa -P -TKC -N%.%.PK% -Ddbname

Example 22

(Logical cluster) Generates DDL for “my_lcluster” on server “ase1”, enter:

ddlgen -Usa -P -Sase1 -TLC -Nmy_lcluster

Example 23

(Logical cluster) Generates DDL for all logical clusters on server “ase1”, enter:

ddlgen -Usa -P -Sase1 -TLC -N%

Example 24

(Logical keys) LK generates logical keys of table defined by sp_primarykey, sp_commonkey, sp_foreignkey statements. Since these keys do not have a name, the name of the object in this case would be the name of the table. This example generate a DDL for logical keys of table authors in database pubs2 running on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TLK -Npubs2.dbo.authors

To generate DDL for all logical keys in database pub2 use:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TLK -Npubs2.%.%

To filter out logical keys definition from DDL of table authors use LK in -F argument, use:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TLK -Npubs2.dbo.authors -FLK

Example 25

(Logins) TL generates DDL for one or all logins. This example generates DDL for all logins on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TL -N%

Alternatively, specify an individual login by using -Nusername instead of -N%:

ddlgen -Ulogin -Ppassword -Sserver:port -TL -Nusername

If server-wide password complexity options have been specified for the login or logins, all sp_addlogin and sp_loglogin DDL statements are generated first, followed by DDL statements for the password options. This example generates DDL for the login “george” on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TL -Ngeorge

Example 26

(Remote servers) Generates DDL for a remote server called ORANGE on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TRS -NORANGE

To generate DDL for all remote servers:

ddlgen -Ulogin -Ppassword -Sserver:port -TRS -N%

Example 27

(Roles) Generates DDL for the sa_role on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TRO -Nsa_role

To generate DDL for all roles:

ddlgen -Ulogin -Ppassword -Sserver:port -TRO -N%

Example 28

(Rules) Generates DDL for all rules associated with authors on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TR -Nauthors.dbo.%

The % symbol tells ddlgen to create DDLs for all rules that exist on the server.

You can also give the fully qualified name of the rule:

ddlgen -Ulogin -Ppassword -Sserver:port -TR -Ndbname.owner.rulename

Alternatively, also use the -D parameter:

ddlgen -Ulogin -Ppassword -Sserver:port -TR -Nowner.rulename -Ddbname

Example 29

(Segments) Generates DDL using the fully qualified dbname.segmentname format in the -N option for a segment called logsegment for the pubs2 database, on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TSGM -Npubs2.logsegment

Alternatively, specify the dbname using the -D option:

ddlgen -Ulogin -Ppassword -Sserver:port -TSGM -Nsegmentname -Ddbname

To generate DDL for all segments:

ddlgen -Ulogin -Ppassword -Sserver:port -TSGM -Ndbname.%

Example 30

(SQLJ functions) Generates DDL for a SQLJ function named region_of owned by dbo in database master:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TF –Nmaster.dbo.region_of 

Alternatively also use the -D parameter:

ddlgen -Ulogin -Ppassword -Sserver:port -TF –Ndbo.region_of –Dmaster

To generate DDL for all SQLJ functions in a database, use object type F:

ddlgen -Ulogin -Ppassword -Sserver:port -TF –Ndbname.owner.%

Example 31

(SQLJ procedures) Are a kind of stored procedure. You generate DDL for SQL procedures along with DDL for stored procedures. This generates DDL for all stored procedures—including SQLJ procedures—owned by dbo in the master database:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TP –Nmaster.dbo.% 

To generate DDL for all SQLJ procedures that are only owned by dbo in the master database, use this, where the extended type OD refers to SQLJ procedures:

ddlgen -Ulogin -Ppassword-Sserver:port -TP –Nmaster.dbo.% -XOD

To generate DDL for all procedures except SQLJ procedures owned by dbo in the master database, use this, where the extended type OU refers to all stored procedures except SQLJ procedures:

ddlgen -Ulogin -Ppassword-Sserver:port -TP –Nmaster.dbo.% -XOU

Example 32

(Stored procedures) Generates DDL for the sp_monitor stored procedure for the pubs2 database on a machine named HARBOR using port 1955, using the fully qualified dbname.owner.procedure_name format for the -N option:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TP -Npubs2.dbo.sp_monitor

Alternatively, specify the dbname using the -D option:

ddlgen -Ulogin -Ppassword -Sserver:port -TP -Nowner.procedurename -Ddbname

To generate DDL for all stored procedures:

ddlgen -Ulogin -Ppassword -Sserver:port -TP -Ndbname.owner.%

Example 33

(SSL-enabled servers) Generates DDL for objects in the pubs2 database for an SSL-enabled Adaptive Server running on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -Sssl:HARBOR:1955 -TDB -Npubs2

Example 34

(Tables) Generates DDL for all user tables in the pubs2 database owned by “dbo” and running on a machine named HARBOR using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TU -Ndbo.% -Dpubs2

You can also use the -N parameter to give the fully qualified name of the table:

ddlgen -Ulogin -Ppassword -Sserver:port -TU
    -Ndbname.tableowner.tablename

Alternatively, also use the -D parameter to specify the database:

ddlgen -Ulogin -Ppassword -Sserver:port -TU
    -Ntableowner.tablename -Ddbname

To generate DDL for all proxy tables, which uses the value OD, use -XOD instead, where X is the extended type, and OD denotes proxy tables:

ddlgen -Ulogin -Ppassword -Sserver:port -TU 
    -Ntableowner.% -Ddbname -XOD

To generate DDL for all user tables, which uses the value OU, use -XOU instead, where X is the extended type, and OU denotes user tables:

ddlgen -Ulogin -Ppassword -Sserver:port -TU 
    -Ntableowner.% -Ddbname -XOU

To generate DDL for all tables, including user tables and proxy tables:

ddlgen -Ulogin -Ppassword -Sserver:port -TU -Ndbname.tableowner.%

Example 35

(Temporary databases) Generates DDL for all databases, including tempdb:

ddlgen -Ulogin -Ppassword -Sserver:port -TDB -N%

To generate DDL for all temporary databases, use the OD extended database type:

ddlgen -Ulogin -Ppassword -Sserver:port -TDB -XOD -N%

Although you can use the OD extended type in Adaptive Server versions 12.5.0.3 and later, versions earlier than 12.5.0.3 issue warning messages. Safely ignore this message; ddlgen continues processing the command.To generate DDL for all databases except temporary databases, use the OU extended type:

ddlgen -Ulogin -Ppassword -Sserver:port -TDB -XOU -N%

This generates DDL for a temporary database named tempdb1:

ddlgen -Ulogin -Ppassword -Sserver:port -TDB -Ntempdb1

The output includes:

• A create temporary database statement:

  create temporary database tempdb1 on master = 4, 
      asdas = 2
  go
  

• An sp_tempdb bind statement where the isql application is bound to tempdb1:

  sp_tempdb 'bind','ap', 'isql', 'DB', 'tempdb1'
  go
  

Example 36

(Triggers) Generates DDL for the trigger checksum for the pubs2 database on a machine named HARBOR using port 1955, using the fully qualified dbname.owner.trigger_name format for the -N option:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TTR -Npubs2.dbo.checksum

Alternatively, specify the database_name using the -D option:

ddlgen -Ulogin -Ppassword -Sserver:port -TTR 
    -Nowner.triggername -Ddbname

You can also generate DDL for a trigger for a table, using:

-Ndb_name.table_owner.table_name.trigger_name

To generate DDL for all triggers of a database:

ddlgen -Ulogin -Ppassword -Sserver:port -TTR -Ndbname.owner.%

You can also use this format to generate DDL for all triggers of a table:

-Ndb_name.table_owner.table_name.%

Example 37

(User-defined datatypes) Generates DDL for the user-defined datatype “Identype” for the pubs2 database on a machine named HARBOR using port 1955 using the fully qualified dbname.userdefined_datatype format for the -N option:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TUDD -Npubs2.Identype

Alternatively, use the -D option to specify the dbname:

ddlgen -Ulogin -Ppassword -Sserver:port -TUDD 
    -Nuserdefined_datatype -Ddbname

To generate DDL for all user-defined datatypes:

ddlgen -Ulogin -Ppassword -Sserver:port -TUDD -Nbname.%

Example 38

(Views) Generates DDL for a view named retail owned by Miller in the pubs2 database running on a machine named HARBOR using port 1955, by using the fully qualified dbname.owner.viewname format with the -N option:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TV -Npubs2.miller.retail

Alternatively, use the -D option instead of using the fully qualified name:

ddlgen -Ulogin -Ppassword -Sserver:port -TV -Nowner.viewname -Ddbname

To generate DDL for all views:

ddlgen -Ulogin -Ppassword -Sserver:port -TV -Ndbname.owner.%

Example 39

(User-defined Web services) Generates DDL for a named user-defined Web service, sp_who_service, in the pubs2 database running on a machine named HARBOR using port 1995, by using a fully qualified dbname.username.webservice_name format with the -N and -T options:

ddlgen -Uroy -Proy123 -SHARBOR:1995 -TWS 
    -Npubs2.dbo.sp_who_service

The syntax for generating DDL for a named user-defined Web service is:

ddlgen -Ulogin -Ppassword -Shost_name:port -TWS -Ndbname.owner.webservice_name

To generate DDL for all user-defined Web services owned by all users in database dbname:

ddlgen -Ulogin -Ppassword -Shost_name:port -TWS -Ndbname.%.%

Example 40

(Users) Generates DDL for a user named Smith in the pubs2 database running on a machine named HARBOR using port 1955, by using a fully qualified dbname.username format with the -N option:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TUSR -Npubs2.smith

Alternatively, use both the -N and -D options instead of using a fully qualified name in -N:

ddlgen -Ulogin -Ppassword -Shost_name:port -TUSR -Nusername -Ddbname

To generate DDL for all users:

ddlgen -Ulogin -Ppassword -Sserver:port -TUSR -Ndbname.%

Usage

• ddlgen does not identify existing sequences within views, stored procedures or triggers. For this reason, when generating DDL for a database, first run ddlgen on those views, stored procedures and triggers that are independent, before running ddlgen on those with dependencies. For example, if view B depends on view A, first run ddlgen on view A, before running it on view B.

• The default information for ddlgen is:

  Option	Parameter	Required	Default
  -U	username	Yes	None
  -P	password	Yes	None
  -S	host_name:port_number	Yes	None
  -T	object_type See the -T parameter description for a list of valid object types	No	Database
  -N	object_name	Yes, if object_type for -T is not DB (database)	Default database name of username, if -Tobject_type is db or if -T is not specified
  -D	database_name	No	Default database of username
  -X	extended_object_type Options are: OU – for user tables, user databases (excluding temporary databases), and stored procedures (excluding SQLJ procedures). OD – for proxy tables, temporary databases, and SQLJ procedures. Use only when the object_type for -T is: U (user table) P (procedure) DB (database)	No	None
  -O	output_file_name	No	Standard out
  -E	error_file_name	No	Standard out
  -L	log_file_name	No	None
  -V	version_number of ddlgen	No	None

• At the command line, invoke ddlgen using the ddlgen shell script file (ddlgen.bat for Windows), included in your Adaptive Server installation. The main class in DDLGen.jar is com.sybase.ddlgen.DDLGenerator.

• To start ddlgen in the Sybase Central plug-in for Adaptive Server:

  1. Right-click on the object for which you want to generate DDL.

  2. Select Generate DDL.

• In the output DDL of create table, bind statements are generated as independent DLL instead of dependent DLL.

• The PN type allows you to generate DDL for tables with partition names. Use partition names and the optdiag utility to analyze optimizer behavior by creating empty partitioned tables with simulated metadata.

  Use this synax to generate names for local index partitions

  ddlgen - TU -XPN
  

  Use this syntax to generate DDL for all user tables with partition names:

  ddlgen  -TU -XPN,OU
  ddlgen -TU -XOU,PN
  

When you issue the ddlgen utility in a UNIX command-line environment, other users on that UNIX machine can see your ddlgen command—including its password—if they issue the ps process management command, which shows the status of processes that are running on that machine.

The ddlgen -P password parameter option lets you to invoke ddlgen from a script so that the password is hidden from other users.

To achieve this, set the $PSWD environment variable to point to your Adaptive Server login password, and include the string “ext” in the -P parameter. ext acts as a pseudo password, allowing you to supply the actual password in the next line. Set $PSWD at the command line or a Bourne shell script, but not from the C-shell.

To run ddlgen using $PSWD:

1. Set the $PSWD environment variable:

   setenv PSWD pass_word
   

2. Run ddlgen:

   ddlgen -Ulogin -Pext -Sserver:port -Ttype -Nname << END
   $PSWD 
   END 
   

If you prefer to keep your password in a file, replace $PSWD with 'cat filename', where filename is the location of your password file. For example:

ddlgen -Ulogin -Pext -Sserver:port -Ttype -Nname << END
'cat filename'
END

You can use the ddlgen utility with encrypted columns.

• The ddlgen utility supports pre-15.0.2 encryption. Pre-15.0.2 ddlgen support includes generating DDL for an encryption key in a database, and generating DDL to synchronize encryption keys across servers.

  If you use ddlgen to generate DDL for encryption keys on Adaptive Server version 15.0.2 or later, the DDL may cause errors on a pre-15.0.2 version Adaptive Server, specifically if an encryption key is encrypted by a user specified-password or has key copies.

• The type EK, used for encryption key, generates the DDL to create an encryption key and to grant permissions on it. ddlgen generates encrypted column information and a grant decrypt statement, along with the table definition.

• If you do not specify the -XOD option, and the key to be migrated has been created in the source database using the with passwd clause, ddlgen generates a create encryption key command with password as its explicit password.This is similar to what ddlgen does for roles and login passwords.

• The -XOD generates the create encryption key that specifies the key’s encrypted value as represented in sysencryptkeys. Use the -XOD to synchronize encryption keys across servers for data movement.

  ddlgen -XOD generates DDL that includes a system encryption password (if it was set and DDL is generated for a key encrypted with a system encryption password) and DDL for keys.

If you do not specify the -XOD flag in ddlgen, and you:

• Did not specify a password when the encryption key was created – ddlgen generates DDL with no password.

• Specified a password when the encryption key was first created – ddlgen generates the default password of ‘password’. This is similar to what ddlgen does for roles and login passwords, and its output looks similar to:

  -------------------------------------------------------------------
  -- DDL for EncryptedKey 'ssn_key'
  -------------------------------------------------------------------
  print 'ssn_key'
  
  --The DDL is generated with a default password – 'password' as
  --a password was specified when this key was created.
  
  create encryption key SampleKeysDB.dbo.ssn_key for AES
  with keylength 128 
  passwd 'password'
  init_vector random 
  go
  

When you specify the-XOD flag in ddlgen, ddlgen generates DDL that includes a system encryption password (if it has been set and DDL is generated for a key encrypted with a system encryption password) and DDL for keys.

Use this syntax to generate a system encryption password:

ddlgen -Usa -P -Sserver -TEK -NsampleKeysdb.dbo.ek1 -XOD

The output would look like:

-- System Encryption Password

use SampleKeysDB
go 

sp_encryption 'system_encr_passwd',
'0x8e050e3bb607225c60c7cb9f59124e99866ca22e677b2cdc9a4d09775850f4721',
NULL, 2, 0
go 

-----------------------------------------------------------------------
-- DDL for EncryptedKey 'ek1'
-----------------------------------------------------------------------

print '<<<<< CREATING EncryptedKey - "ek1" >>>>>'
go 

create encryption key SampleKeysDB.dbo.ek1 for AES
with keylength 128 
passwd 0x0000C7BC28C3020AC21401
init_vector NULL 
keyvalue 0xCE74DB1E028FF15D908CD066D380AB4AD3AA88284D6F7742DFFCADCAABE4100D01 
keystatus 32 
go 

The ddlgen utility also generates DDL for key copies along with the DDL for base key. For example, this syntax would generate DDL for “ssn_key” and its key copies:

ddlgen -Usa -P -Sserver -TEK -NSampleKeysDB.dbo.ssn_key

The output from ddlgen would look like:

-----------------------------------------------------------------------------
-- DDL for EncryptedKey 'ssn_key'
-----------------------------------------------------------------------------
print 'ssn_key'

--The DDL is generated with a default password – 'password' as 
--a password was specified when this key was created.

create encryption key SampleKeysDB.dbo.ssn_key for AES
with keylength 128 
passwd 'password'
init_vector random 
go 

print 'Key Copies for ssn_key'

-- Generating DDL for Key Copies for 'ssn_key'

alter encryption key 'ssn_key'
with passwd 'password'
add encryption with passwd 'passwd'
for user 'dbo'.

If you include the -XOD flag, the DDL for key copy would look like:

alter encryption key SampleKeysDB.dbo.ssn_key add encryption 
with keyvalue
0x84A7360AA0B28801D6D4CBF2F8219F634EE641E1082F221A2C58C9BBEC9F49B501 
passwd 0x000062DF4B8DA5709E5E01 
keystatus 257 
for user 'user1' 
go

The ddlgen utility supports the EKC (encryption key copy) extended type for its -F filter argument, to suppress the generation of key copies for encryption keys.

This example uses -FEKC to avoid creating DDL for key copies when generating DDL for the “ssn_key” encryption key:

ddlgen -Usa -P -Sserver -TEK -NSampleKeysDB.dbo.ssn_key  -FEKC

The output from ddlgen would look like:

--------------------------------------------------------------------------
-- DDL for EncryptedKey 'ssn_key'
--------------------------------------------------------------------------
print 'ssn_key'

--The DDL is generated with a default password – 'password' as 
--a password was specified when this key was created.

create encryption key SampleKeysDB.dbo.ssn_key for AES
with keylength 128 
passwd 'password'
init_vector random 
go

ddlgen can generate decrypt_default statements (if set for an encrypted column) along with DDL of a table.

This example issues a ddlgen command on a table called employee which has an “ssn” column that is encrypted with encryption key “ssn_key,” and a decrypt default value that is set to “100”:

ddlgen -Usa -P -Sserver -TU -Nemployee

The DDL output would look like:

create table employee (
  ssn            int       not null  encrypt with ssn_key decrypt_default 100 ,
  last_name      int       not null ,
  first_name     int       not null
)
lock allpages
 on 'default'
go

Permissions

Users must have either sa_role or sso_role to generate DDL for:

• Encryption keys

• Logins

• Roles

For all other objects, users do not need any specific permissions or roles to generate DDL.

See also