sibDDLGenerator command

sibDDLGenerator command

A messaging engine needs data definition language (DDL) statements to create the DBMS (Database Management System) resources. These DBMS resources are generated by the sibDDLGenerator command.

Syntax
>>-sibDDLGenerator--+---------------------+--------------------->
                    '- -system--DBMS name-' 

>--+-------------------------+--+---------------------------+--->
   '- -version--DBMS version-'  '- -platform--DBMS platform-' 

>--+-----------------------+--+-------------------+------------->
   '- -schema--schema name-'  '- -user--user name-' 

>--+----------+--+----------------------+----------------------->
   +- -create-+  '- -database--database-' 
   '- -drop---'                          

>--+-------------------------------+---------------------------->
   '- -storagegroup--storage group-' 

>--+---------------------------------+-------------------------->
   '- -catalog--high level qualifier-' 

>--+---------------------------+-------------------------------->
   '- -bufferpool--buffer pool-' 

>--+-------------------------------+--+----------------+-------->
   '- -statementend--statement end-'  '- -nolinebreaks-' 

>--+----------------+--+-------------------------+-------------->
   '- -noblanklines-'  '- -firstline--first line-' 

>--+-----------------------+--+---------------------+----------->
   '- -lastline--last line-'  '- -permanent--number-' 

>--+---------------------+-------------------------------------><
   '- -temporary--number-' 
A messaging engine needs DBMS resources, such as database tables, which it can create when starting. If our installation has a policy that only a database administrator has the authority to create database tables, use the sibDDLGenerator command to enable the database administrator to create the DBMS resources that the messaging engine needs. The sibDDLGenerator command generates the DDL statements that the database administrator can save, and later process, to create the DBMS resources listed in Data store tables.

The command also generates DDL statements that grant the appropriate authorities to allow a messaging engine use these tables.

(iSeries) To access the IBM i command line, use the STRQSH command to start a Qshell session. (iSeries) See Configure Qshell to run WebSphere scripts .

The sibDDLGenerator command is able to generate the DDL statements for creating or dropping all of the data store tables. It is less well suited for adding more item tables to an existing data store. However, it is possible to reissue the DDL statements to create existing tables without harming the data store.

Optional parameters

Tip: The sibDDLGenerator command is case-sensitive. For example, the parameter value Oracle is not the same as the parameter value oracle.
Some parameters apply to a specific DBMS only, as indicated in the parameter list; for example: -database [Applies only to DB2 for z/OS]

-system

Identifies the type of DBMS on which the administrator will process the DDL statements. Valid values are:

db2
oracle
sqlserver
sybase
informix
derby
If we do not supply a -system parameter, the default value is derby.

-version

The version number of the DBMS identified by the -system parameter. The following table shows the default value used if we do not supply a -version parameter.
If we receive a CWSIS1595E or CWSIS1503E error when running the tool, consult the notes after the table to determine whether an alternative -version parameter value can be used for our database version.

-system parameter value -platform parameter value Default value for the -version parameter Other accepted -version parameter values
db2 zos 7.1 8.1, 9.1 (see Note 1)
db2 unix, windows 8.1 9.1 (see Note 1)
db2 iseries 5.2 (see Note 2) 5.3 (see Note 2)
oracle
8i 10g, 11g (see Note 3)
sqlserver
2000 2005
sybase
12.0 12.5, 15.0
informix
9.3 7.3, 9.4, 10.0, 11.0 (see Note 4)
derby
10.1

The DDL generated for DB2 for UNIX or Windows using -version parameter values 8.1, and 9.1 is identical. The DDL generated for DB2 for z/OS using -version parameter values 7.1, 8.1, and 9.1 is identical.
For IBM i, the DB2 database is part of the operating system, so the version number given is for the OS/400 version.
The DDL generated for Oracle using -version parameter values 10g and 11g is identical.
The DDL generated for Informix using -version parameter values 10.0, and 11.0 is identical.

-platform

The operating system platform on which the DBMS runs. Valid values are:

iseries
unix
windows
zos

-schema

The name of the schema containing all the objects used by the messaging engine. If we do not supply a -schema parameter, the default value is IBMWSSIB.

-user

The name of the user ID used to interact with the DBMS. If we do not supply a -user parameter, the default value is IBMUSER.

-create | -drop

Indicates whether the DDL statements create the DBMS resources or delete them. If we do not supply either parameter, the default value is -create.

-database [Applies only to DB2 for z/OS]

The name of the database that is allocated for the messaging engine tables. If we do not supply a -database parameter, the default value is SIBDB.

-storagegroup [Applies only to DB2 for z/OS]

The name of the storage group that is allocated for the messaging engine tables.

If we supply both the -storagegroup and the -catalog parameters, the sibDDLGenerator command includes both values in the CREATE STOGROUP statement.
If we supply only a -storagegroup parameter, the sibDDLGenerator command uses the storage group name in other statements but does not create a CREATE STOGROUP statement.
If we supply only a -catalog parameter, the sibDDLGenerator command displays the usage statement and then terminates.
If we omit both parameters, the sibDDLGenerator command uses the default value SIBSG for the storage group name in other statements but does not create a CREATE STOGROUP statement.

-catalog [Applies only to DB2 for z/OS]

The name of the high level qualifier for the storage group that is allocated for the messaging engine tables. For information about defaults, refer to the -storagegroup parameter.

-bufferpool [Applies only to DB2 for z/OS]

The name of the buffer pool that is allocated for the messaging engine tables. If we do not supply a -bufferpool parameter, the default value is BP1.

-statementend

Appends statement_end to each DDL statement. For example, we can use ; to append a semicolon to each DDL statement. By default, the sibDDLGenerator command appends nothing to each statement.
Tip: On UNIX platforms, escape the semicolon to prevent the shell from interpreting it.

-nolinebreaks

Places each statement on a single line, with no line breaks. By default, the sibDDLGenerator command breaks statements across lines to improve readability.

-noblanklines

Omits blank lines between each statement. By default, the sibDDLGenerator command inserts a blank line between each statement to improve readability.

-firstline

Generates first_line as the first line of output. For example, we can use first_line to identify the target database. By default, the sibDDLGenerator command does not generate a first line.

-lastline

Generates last_line as the last line of output. For example, we can use last_line to invoke a command that executes the commands in the script. By default, the sibDDLGenerator command does not generate a last line.

Tip: The optional parameters that control the format of the DDL statements, for example -statementend, allow us to generate output that is suitable for particular scripting tools, for example the DB2 CLP. By default, the sibDDLGenerator command generates blank lines between each DDL statement but does not append a semicolon at the end of each DDL statement.
The following two optional parameters are used for spreading the data store across multiple tables:

-permanent

The number of permanent tables, with

Default value: 1
Minimum value: 1
Maximum value: see Note

-temporary

The number of temporary tables, with

Default value: 1
Minimum value: 1
Maximum value: see Note

The maximum number of SIBnnn tables that can be used by a messaging engine is 32. This includes all stream, permanent and temporary tables.

Examples

sibDDLGenerator -system db2 -version 8.1 -platform zos
Generates DDL statements for DB2 8.1, running on z/OS, with a default schema, user ID, database, storage group, and buffer pool.

sibDDLGenerator -system db2 -version 8.1 -platform windows -statementend ;
Generates DDL statements for DB2 8.1, running on Windows, with a default schema, user ID, and database. We can input the statements directly to the DB2 CLP, which requires that each statement is terminated with a semicolon.

sibDDLGenerator -system oracle -version 8i -schema SIB -user fred
Generates DDL statements for Oracle 8i.

sibDDLGenerator -system oracle -schema SIB -user fred
A concise version of the preceding example.

-system parameter value	-platform parameter value	Default value for the -version parameter	Other accepted -version parameter values
db2	zos	7.1	8.1, 9.1 (see Note 1)
db2	unix, windows	8.1	9.1 (see Note 1)
db2	iseries	5.2 (see Note 2)	5.3 (see Note 2)
oracle		8i	10g, 11g (see Note 3)
sqlserver		2000	2005
sybase		12.0	12.5, 15.0
informix		9.3	7.3, 9.4, 10.0, 11.0 (see Note 4)
derby		10.1