Syntax of the dbschema command

The dbschema command displays the SQL statements (the schema) that are necessary to replicate a specified database object. The command also shows the distributions that the UPDATE STATISTICS statement creates.

Read syntax diagramSkip visual syntax diagram
>>-dbschema----------------------------------------------------->

>--+-| Table options |--| Database options |----| UDT options |---+-->
   '-+-----------+------------------------------------------------'   
     +- -V-------+                                                    
     '- -version-'                                                    

>--| Storage, space, and log options |--| No owner option |----><

UDT options

    (1)                         
|------+--------------------+-----------------------------------|
       +--- -u all----------+   
       '-+- -ua-+--udt_name-'   
         '- -ui-'               

Table options

|--+-+--------------------------------------+--+------------------+--+--------------------+-+-->
   | |                                  (2) |  |              (3) |  |                (4) | |   
   | '-| Tables, Views, or Procedures |-----'  '-| Synonyms |-----'  '-| Privileges |-----' |   
   '- -hd--+-all----------------+-----------------------------------------------------------'   
           |                (5) |                                                               
           '-| Table Name |-----'                                                               

>--+-------------------+----------------------------------------|
   |               (6) |   
   '- -r--+-role-+-----'   
          '-all--'         

Database options

|--+------+--+---------------------+-- -d--database------------->
   '- -ss-'  '- -seq--+-sequence-+-'                  
                      '-all------'                    

>--+---------------+--------------------------------------------|
   '- -w--password-'   

Storage space and log options

|--+------------------------------+-----------------------------|
   |                          (7) |   
   '- -c -+------+--file_name-----'   
          '- -ns '                    

No owner option

|--+------+-----------------------------------------------------|
   '- -nw '   

Notes:
  1. See User-defined and complex data types
  2. See Table, view, or procedure creation
  3. See Synonym creation
  4. See Privileges
  5. See Identifier.
  6. See Role creation
  7. See Storage space, chunk, and log creation
Element Purpose Additional Information
all Directs dbschema to include all the tables or sequence objects in the database, or all the user-defined data types in the display of distributions. None.
-c file_name Generates commands to reproduce storage spaces, chunks, physical logs, and logical logs. If you use the -c element without the -ns element, the database server generates SQL administration API commands.

If you use the -c element and also use the -ns element, the database server generates onspaces or onparams commands.

-d database Specifies the database to which the schema applies. The database can be on a remote database server. To use more than the simple name of the database, see Database Name .
filename Specifies the name of the file that contains the dbschema output. If you omit a file name, dbschema sends the output to the screen. If you specify a file name, dbschema creates a file named filename to contain the dbschema output.
-hd Displays the distribution as data values. If you specify the ALL keyword for the table name, the distributions for all the tables in the database are displayed.
-it Sets the isolation type for dbschema while dbschema queries catalog tables. Isolation types are:
  • DR = Dirty Read
  • CR = Committed Read
  • CS = Cursor Stability
  • CRU = Committed Read with RETAIN UPDATE LOCKS
  • CSU = Cursor Stability with RETAIN UPDATE LOCKS
  • DRU = Dirty Read with RETAIN UPDATE LOCKS
  • LC = Committed Read, Last Committed
  • RR = Repeatable Read
 
-l Sets the lock mode to wait number of seconds for dbschema while dbschema queries catalog tables. None.
-ns Generates onspaces or onparams utility commands to reproduce storage spaces, chunks, physical logs, and logical logs. The -c element must precede the -ns element in your command.
-nw Generates the SQL for creating an object without the specification of an owner. The -nw element is also a dbexport command option.
-q Suppresses the database version from the header. This optional element precedes other elements.
-r Generates information about the creation of roles. For details, see Role creation.
-seq sequence Generates the DDL statement to define the specified sequence object None.
-ss Generates server-specific information This option is ignored if no table schema is generated.
-si Excludes the generation of index storage clauses for non-fragmented tables This option is available only with the -ss option.
-sl length Specifies the maximum length, in bytes, of unformatted CREATE TABLE and ALTER TABLE statements. None.
-u all Prints the definitions of all user-defined data types, including all functions and casts defined over the types. Specify -u all to include all the user-defined data types in the list of distributions.
-ua udt_name Prints the definition of a user-defined data type, including functions and casts defined over an opaque or constructor type. None.
-ui udt_name Prints the definition of a user-defined data type, including type inheritance. None.
-V Displays the software version number and the serial number None.
-version Extends the -V option to display additional information about the build version, host, operating system, build number and date, and the GLS version. None.
-w password Specifies the database password, if you have one. None.

You must be the DBA or have the Connect or Resource privilege for the database before you can run dbschema on it.

Example

The following command generates the schema with all the tables or sequence objects in the customer database, but without the specification of an owner:

dbschema –d customer all -nw

Copyright© 2019 HCL Technologies Limited