A Java-based tool that generates definitions for server- and database-level objects in Adaptive Server. ddlgen supports Adaptive Server version 12.0 and later.
The command-line version of ddlgen is located in $SYBASE/sybcent41 (%SYBASE%\sybcent41 in Windows NT).
ddlgen -Ulogin -Ppassword -Shost_name : port_number [-Tobject_type] [-Nobject_name] [-Ddbname] [-Xextended_object_type] [-Ooutput_file] [-Eerror_file] [-Lprogress_log_file] [-Jclient_charset] -F[ % | SGM | GRP | USR | R | D | UDD | U | V | P | XP | I | RI | KC | TR]
Or
ddlgen -v
specifies a login name, and is case-sensitive.
specifies your password.
specifies the host name or IP address of Adaptive Server, as well as its port number. Separate host_name and port_number with a colon, without spaces before or after it.
You must use the -S option
because ddlgen does not connect to a default server.
specifies the type of object you are creating. If you do not use -T, ddlgen generates DDL for the default database of login. Table 8-3 lists object types for -T.
Object type |
Description |
|---|---|
C |
cache |
D |
default |
DB |
database |
DBD |
database device |
DPD |
dump device |
EC |
execution class |
EG |
engine group |
GRP |
group |
I |
index |
KC |
key constraints |
L |
login |
P |
stored procedure |
R |
rule |
RI |
referential integrity |
RO |
role |
RS |
remote server |
SGM |
segment |
TR |
trigger |
U |
table |
UDD |
user-defined datatype |
USR |
user |
V |
view |
XP |
extended stored procedure |
specifies the fully qualified name of the object you are creating, such as -Ndb_name.owner_name.table_name.object_name. The -N option:
is required if you specify any object_type other than DB (database) in the -T parameter.
accepts wildcards with the use of %.
generates DDL for all items of a specific object type on your server.
enforces strict order in which it parses the names in the -Ndb_name.owner_name.table_name.object_name format. If you only provide three arguments, ddlgen assumes they are owner_name, table_name, and object_name, in that order. Alternatively, you can also use -Nowner_name.table_name -Ddb_name. ddlgen does not impose this restriction if object_name is an index (I).
specifies the name of the database for the object you specify in the -N option. The default is the user’s default database.
differentiates the following:
user tables (OU) from proxy tables (OD) when you specify a table as your object type (-TU)
temporary databases (OD) from non-temporary databases (OU) when you specify database as your object type (-TDB)
SQLJ procedures (OD) from stored procedures (OU) when you specify procedure as your object type (-TP).
If object_type (-T) is U (table) and -X is not specified, ddlgen generates DDL for both user tables and proxy tables. To generate DDL only for:
user tables – use the OU extended object type with the -X option.
proxy tables – use the OD extended object type with the -X option.
ddlgen does not support system tables.
specifies an output file for the generated DDL. If you do not specify -O, the DDL you create appears in a console window.
specifies a log file for recording errors. If you do not specify -E, the generated errors appear in a console window.
specifies a log file for recording the progress of ddlgen. If you do not specify -L, the progress is not recorded.
specifies the character set to use on the client. -Jclient_charset requests that Adaptive Server convert to and from client_charset, the character set used on the client. A filter converts input between client_charset and the Adaptive Server character set.
Omitting -J sets the character set to a default for the platform. The default may not necessarily be the character set that the client is using.
filters out indexes, triggers, and constraints out of table and database definitions in the DDL of table- and database-level objects. The valid filters are:
For tables – [ % | I | RI | KC | TR]
For databases – [ % | SGM | GRP | USR | R | D | UDD | U | V | P | XP | I | RI | KC | TR]
The filter options are:
% – filters out everything, and retrieves the schema-only definition of a table.
SGM – filters out segments
GRP – filters out groups
USR – filters out users
R – filters out rules
D – filters out defaults
UDD – filters out user-defined datatypes
U – filters out user tables
V – filters out views
P – filters out stored procedures
XP – filters out extended stored procedures
I – filters out indexes
RI – filters out referential integrity constraints
KC – filters out primary- and unique-key constraints
TR – filters out triggers
displays the version and copyright message of ddlgen and returns to the operating system.
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%
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, you can 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
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%
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%
Temporary databases – Generates DDL for all databases, including tempdbs:
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. You can 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%
The following generates DDL for a temporary database named tempdb1:
ddlgen -Ulogin -Ppassword -Sserver:port -TDB -Ntempdb1
The output includes the following:
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
DDL for objects such as views, stored procedures, and
tables is not generated along with DDL for a temporary database
because these objects are temporary, and are re-created
when the server restarts.
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%
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%
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%
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, you can 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.%
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, you can 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.%
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, you can 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:
ddlgen -Ulogin -Ppassword -Sserver:port -TI -Ndbname.owner.tablename.%
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
Although you can normally generate all indexes only
for a table, the -T object type parameter
with the RI and KC value allows
you to generate foreign keys as well as primary and unique keys
for an entire database.
Logins – Generates DDL for all logins on a machine named HARBOR using port 1955:
ddlgen -Uroy -Proy123 -SHARBOR:1955 -TL -N%
The password in the DDL generated for all logins is “password”.
Alternatively, you can specify an individual login by using -Nusername instead of -N%:
ddlgen -Ulogin -Ppassword -Sserver:port -TL -Nusername
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%
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%
The password in the DDL generated for all roles is “password”.
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, you can also use the -D parameter:
ddlgen -Ulogin -Ppassword -Sserver:port -TR -Nowner.rulename -Ddbname
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, you can use 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.%
SQLJ functions – Generates DDL for a SQLJ function named region_of owned by dbo in database master:
ddlgen -Uroy -Proy123 -SHARBOR:1955 -TDB –Nmaster.dbo.region_of
Alternatively you can also use the -D parameter:
ddlgen -Ulogin -Ppassword -Sserver:port -TDB –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.%
SQLJ procedures – are a kind of stored procedure. You generate DDL for SQL procedures along with DDL for stored procedures. The following 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 the following, 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 the following, where the extended type OU refers to all stored procedures except SQLJ procedures:
ddlgen -Ulogin -Ppassword-Sserver:port -TP –Nmaster.dbo.% -XOU
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, you can use 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.%
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, you can 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.%
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, you can use specify the database_name using the -D option:
ddlgen -Ulogin -Ppassword -Sserver:port -TTR -Nowner.triggername -Ddbname
To generate DDL for all triggers:
ddlgen -Ulogin -Ppassword -Sserver:port -TTR -Ndbname.owner.%
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, you can 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.%
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, you can 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.%
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, you can 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.%
ddlgen does not identify existing sequences within views, stored procedures or triggers. For this reason, when generating DDL for a database, you must 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, you must 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 Table 8-3 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, and OD for proxy tables |
No; use only when the object_type for -T is U (user table), P (procedure), DB (database) |
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 |
To invoke ddlgen from a command line, you must install JRE 1.1.8 or higher, and have DDLGen.jar in your classpath.
At the command line, invoke ddlgen using the DDLGen.sh file (DDLGen.bat for Windows NT), 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:
Right-click on the object for which you want to generate DDL.
Select Generate DDL.
In the output DDL of create table, bind statements are generated as independent DLL instead of dependent DLL.
If you use an invalid filter paramter, ddlgen generates a warning, ignores that parameter, and continues with the rest of the valid parameters you specify.
If you specify % along with other filter parameters, ddlgen ignores all other filterable parameters, and only shows schema-only definitions. ddlgen then continues to evaluate the dependencies within the subset of the applied as the filterable parameters for the database.
Since ddlgen needs to obtain data from system catalogs, users must either be logged in as “dbo” or have select permissions on syscatalogs.
