Initializes a new database with an optional user-defined
collating sequence, creates the three initial table spaces, creates
the system tables, and allocates the recovery log.
Scope
In a partitioned database environment,
this API affects all database partition servers that are listed in
the db2nodes.cfg file.
The database partition
server from which this API is called becomes the catalog partition
for the new database.
Authorization
Note: The effective user ID of the calling application or process
is used for client-side authorization.
Required connection
Instance. To create
a database at another (remote) node, it is necessary to first attach
to that node. A database connection is temporarily established by
this API during processing.
API include file
sqlenv.h
API and data structure syntax
SQL_API_RC SQL_API_FN
sqlecrea (
char * pDbName,
char * pLocalDbAlias,
char * pPath,
struct sqledbdesc * pDbDescriptor,
SQLEDBTERRITORYINFO * pTerritoryInfo,
char Reserved2,
void * pDbDescriptorExt,
struct sqlca * pSqlca);
SQL_API_RC SQL_API_FN
sqlgcrea (
unsigned short PathLen,
unsigned short LocalDbAliasLen,
unsigned short DbNameLen,
struct sqlca * pSqlca,
void * pReserved1,
unsigned short Reserved2,
SQLEDBTERRITORYINFO * pTerritoryInfo,
struct sqledbdesc * pDbDescriptor,
char * pPath,
char * pLocalDbAlias,
char * pDbName);
sqlecrea API parameters
- pDbName
- Input. A string containing the database name. This is the database
name that will be cataloged in the system database directory. Once
the database has been successfully created in the server's system
database directory, it is automatically cataloged in the system database
directory with a database alias identical to the database name. Must
not be NULL.
- pLocalDbAlias
- Input. A string containing the alias to be placed in the client's
system database directory. Can be NULL. If no local alias is specified,
the database name is the default.
- pPath
- Input. On Linux and UNIX systems,
specifies the path on which to create the database. If a path is not
specified, the database is created on the default database path specified
in the database manager configuration file (dftdbpath parameter).
On the Windows operating system, specifies the letter
of the drive on which to create the database. Can be NULL.
Note: For
partitioned database environments, a database should not be created
in an NFS-mounted directory. If a path is not specified, ensure that
the dftdbpath database manager configuration parameter is not set
to an NFS-mounted path (for example, on UNIX based
systems, it should not specify the $HOME directory of the instance
owner). The path specified for this API in a partitioned database
environment cannot be a relative path.
- pDbDescriptor
- Input. A pointer to the database description block that is used
when creating the database. The database description block can be
used by you to supply values that are permanently stored in the configuration
file of the database.
The supplied values are a collating sequence,
a database comment, or a table space definition. The supplied value
can be NULL if you do not want to supply any values. For information
about the values that can be supplied through this parameter, see
the SQLEDBDESC data structure topic.
- pTerritoryInfo
- Input. A pointer to the sqledbterritoryinfo structure, containing
the locale and the code set for the database. Can be NULL. The default
code set for a database is UTF-8 (Unicode). If a particular code set
and territory is needed for a database, the desired code set and territory
should be specified via the sqledbterritoryinfo structure. If this
field is NULL, then one of the following is allowed as a collation
value for the database (sqlcode 1083): NULL, SQL_CS_SYSTEM, SQL_CS_IDENTITY_16BIT,
SQL_CS_UCA400_NO, SQL_CS_UCA400_LTH, SQL_CS_UCA400_LSK, or SQL_CS_UNICODE.
- Reserved2
- Input. Reserved for future use.
- pDbDescriptorExt
- Input. This parameter refers to an extended database description
block (sqledbdescext) that is used when creating the database. The
extended database description block controls automatic storage for
a database, chooses a default page size for the database, and specifies
values for new table space attributes that have been introduced. If
set to null or zero, a default page size of 4 096 bytes is chosen
for the database and automatic storage is not enabled.
- pSqlca
- Output. A pointer to the sqlca structure.
sqlgcrea API-specific parameters
- PathLen
- Input. A 2-byte unsigned integer representing the length of the
path in bytes. Set to zero if no path is provided.
- LocalDbALiasLen
- Input. A 2-byte unsigned integer representing the length of the
local database alias in bytes. Set to zero if no local alias is provided.
- DbNameLen
- Input. A 2-byte unsigned integer representing the length of the
database name in bytes.
Usage notes
CREATE DATABASE:
- Creates a database in the specified subdirectory. In a partitioned
database environment, creates the database on all database partition
servers listed in db2nodes.cfg, and creates a $DB2INSTANCE/NODExxxx
directory under the specified subdirectory at each database partition
server, where xxxx represents the local database partition server
number. In a single-partition environment, creates a $DB2INSTANCE/NODE0000
directory under the specified subdirectory.
- Creates the system catalog tables and recovery log.
- Catalogs the database in the following database directories:
- Creates a system or a local database directory if neither exists.
If specified, the comment and code set values are placed in both directories.
- Stores the specified code set, territory, and collating sequence.
A flag is set in the database configuration file if the collating
sequence consists of unique weights, or if it is the identity sequence.
- Creates the schemata called SYSCAT, SYSFUN, SYSIBM, and SYSSTAT
with SYSIBM as the owner. The database partition server on which this
API is called becomes the catalog partition for the new database.
Two database partition groups are created automatically: IBMDEFAULTGROUP
and IBMCATGROUP.
- Binds the previously defined database manager bind files to the
database (these are listed in db2ubind.lst). If one or more of these
files do not bind successfully, sqlecrea returns a warning in the
SQLCA, and provides information about the binds that failed. If a
bind fails, the user can take corrective action and manually bind
the failing file. The database is created in any case. A schema called
NULLID is implicitly created when performing the binds with CREATEIN
privilege granted to PUBLIC, if the RESTRICTIVE option
is not selected.
- Creates SYSCATSPACE, TEMPSPACE1, and USERSPACE1 table spaces.
The SYSCATSPACE table space is only created on the catalog partition.
All database partitions have the same table space definitions.
- Grants the following:
- DBADM, CONNECT, CREATETAB, BINDADD, CREATE_NOT_FENCED, IMPLICIT_SCHEMA,
and LOAD authorities to the database creator
- CONNECT, CREATETAB, BINDADD, and IMPLICIT_SCHEMA authorities to
PUBLIC
- USE privilege on the USERSPACE1 table space to PUBLIC
- SELECT privilege on each system catalog to PUBLIC
- BIND and EXECUTE privilege to PUBLIC for each successfully bound
utility
- EXECUTE WITH GRANT privilege to PUBLIC on all functions in the
SYSFUN schema.
- EXECUTE privilege to PUBLIC on all procedures in SYSIBM schema.
Note: If the
RESTRICTIVE option is present,
it causes the RESTRICT_ACCESS database configuration parameter to
be set to YES and no privileges or authorities are automatically granted
to PUBLIC. For more detailed information, see the
RESTRICTIVE option
of the CREATE DATABASE command.
With dbadm authority, one can grant these privileges
to (and revoke them from) other users or PUBLIC. If another administrator
with sysadm or dbadm authority over the database revokes these privileges,
the database creator nevertheless retains them.
In a partitioned
database environment, the database manager creates a subdirectory,
$DB2INSTANCE/NODExxxx, under the specified or default path on all
database partition servers. The xxxx is the node number as defined
in the db2nodes.cfg file (that is, node 0 becomes
NODE0000). Subdirectories SQL00001 through SQLnnnnn will reside on
this path. This ensures that the database objects associated with
different database partition servers are stored in different directories
(even if the subdirectory $DB2INSTANCE under the specified or default
path is shared by all database partition servers).
On Windows and AIX® operating
systems, the length of the code set name is limited to a maximum of
9 characters. For example, specify a code set name such as ISO885915
instead of ISO8859-15.
The sqlecrea API accepts a data structure
called the Database Descriptor Block (SQLEDBDESC). You can define
your own collating sequence within this structure.
Note: You can only
define your own collating sequence for a single-byte database.
To
specify a collating sequence for a database:
- Pass the desired SQLEDBDESC structure, or
- Pass a NULL pointer. The collating sequence of the operating system
(based on the current locale code and the code page) is used. This
is the same as specifying SQLDBCSS equal to SQL_CS_SYSTEM (0).
Execution of the CREATE DATABASE command
will fail if the application is already connected to a database.
If
the database description block structure is not set correctly, an
error message is returned.
The most prominent value of the
database description block must be set to the symbolic value SQLE_DBDESC_2
(defined in sqlenv). The following sample user-defined collating sequences
are available in the host language include files:
- sqle819a
- If the code page of the database is 819 (ISO Latin/1), this sequence
will cause sorting to be performed according to the host CCSID 500
(EBCDIC International).
- sqle819b
- If the code page of the database is 819 (ISO Latin/1),this sequence
will cause sorting to be performed according to the host CCSID 037
(EBCDIC US English).
- sqle850a
- If the code page of the database is 850 (ASCII Latin/1), this
sequence will cause sorting to be performed according to the host
CCSID 500 (EBCDIC International).
- sqle850b
- If the code page of the database is 850 (ASCII Latin/1), this
sequence will cause sorting to be performed according to the host
CCSID 037 (EBCDIC US English).
- sqle932a
- If the code page of the database is 932 (ASCII Japanese), this
sequence will cause sorting to be performed according to the host
CCSID 5035 (EBCDIC Japanese).
- sqle932b
- If the code page of the database is 932 (ASCII Japanese), this
sequence will cause sorting to be performed according to the host
CCSID 5026 (EBCDIC Japanese).
The collating sequence specified during database
creation cannot be changed later. It determines how character strings
are compared. This affects the structure of indexes as well as the
results of queries. In a Unicode database, the catalog tables and
views are always created with the IDENTITY collation, regardless of
the collation specified in the create database call. In a non-Unicode
database, the catalog tables and views are created with the database
collation.
Use sqlecadb to define different
alias names for the new database.
The Configuration Advisor
is called by default during the database creation process unless specifically
told not to do so.
REXX API syntax
CREATE DATABASE dbname [ON path] [ALIAS dbalias]
[USING CODESET codeset TERRITORY territory]
[COLLATE USING {SYSTEM | IDENTITY | USER :udcs}]
[NUMSEGS numsegs] [DFT_EXTENT_SZ dft_extentsize]
[CATALOG TABLESPACE <tablespace_definition>]
[USER TABLESPACE <tablespace_definition>]
[TEMPORARY TABLESPACE <tablespace_definition>]
[WITH comment]
Where <tablespace_definition> stands for:
MANAGED BY {
SYSTEM USING :SMS_string |
DATABASE USING :DMS_string }
[ EXTENTSIZE number_of_pages ]
[ PREFETCHSIZE number_of_pages ]
[ OVERHEAD number_of_milliseconds ]
[ TRANSFERRATE number_of_milliseconds ]
REXX API parameters
- dbname
- Name of the database.
- dbalias
- Alias of the database.
- path
- Path on which to create the database. If a path is not specified,
the database is created on the default database path specified in
the database manager configuration file (dftdbpath configuration parameter).
Note: For partitioned database environments, a database should not
be created in an NFS-mounted directory. If a path is not specified,
ensure that the dftdbpath database manager configuration parameter
is not set to an NFS-mounted path (for example, on UNIX based
systems, it should not specify the $HOME directory of the instance
owner). The path specified for this API in a partitioned database
environment cannot be a relative path.
- codeset
- Code set to be used for data entered into the database.
- territory
- Territory code (locale) to be used for data entered into the database.
- SYSTEM
- For non-Unicode databases, this is the default option, with the
collating sequence based on the database territory. For Unicode databases,
this option is equivalent to the IDENTITY option.
- IDENTITY
- Identity collating sequence, in which strings are compared byte
for byte. This is the default for Unicode databases.
- USER udcs
- The collating sequence is specified by the calling application
in a host variable containing a 256-byte string defining the collating
sequence.
- numsegs
- Number of directories (table space containers) that will be created
and used to store the database table files for any default SMS table
spaces.
- dft_extentsize
- Specifies the default extent size for table spaces in the database.
- SMS_string
- A compound REXX host variable identifying one or more containers
that will belong to the table space, and where the table space data
will be stored. In the following, XXX represents the host variable
name. Note that each of the directory names cannot exceed 254 bytes
in length.
- XXX.0
- Number of directories specified
- XXX.1
- First directory name for SMS table space
- XXX.2
- Second directory name for SMS table space
- XXX.3
- and so on.
- DMS_string
- A compound REXX host variable identifying one or more containers
that will belong to the table space, where the table space data will
be stored, container sizes (specified in a number of 4KB pages) and
types (file or device). The specified devices (not files) must already
exist. In the following, XXX represents the host variable name. Note
that each of the container names cannot exceed 254 bytes in length.
- XXX.0
- Number of strings in the REXX host variable (number of first level
elements)
- XXX.1.1
- Type of the first container (file or device)
- XXX.1.2
- First file name or device name
- XXX.1.3
- Size (in pages) of the first container
- XXX.2.1
- Type of the second container (file or device)
- XXX.2.2
- Second file name or device name
- XXX.2.3
- Size (in pages) of the second container
- XXX.3.1
- and so on.
- EXTENTSIZE number_of_pages
- Number of 4KB pages that will be written to a container before
skipping to the next container.
- PREFETCHSIZE number_of_pages
- Number of 4KB pages that will be read from the table space when
data prefetching is being performed.
- OVERHEAD number_of_milliseconds
- Number that specifies the I/O controller overhead, disk seek,
and latency time in milliseconds.
- TRANSFERRATE number_of_milliseconds
- Number that specifies the time in milliseconds to read one 4 KB
page into memory.
- comment
- Description of the database or the database entry in the system
directory. Do not use a carriage return or line feed character in
the comment. Be sure to enclose the comment text in double quotation
marks. Maximum size is 30 characters.