DB2 Version 10.1 for Linux, UNIX, and Windows

sqlecrea API - Create database

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

One of the following authorities:

SYSADM
SYSCTRL

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 Linux and UNIX 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 required code set and territory should be specified via the sqledbterritoryinfo structure. If this field is NULL, then one of the following options is allowed as a collation value for the database (sqlcode 1083): NULL, SQL_CS_SYSTEM, SQL_CS_IDENTITY_16BIT, or SQL_CS_UNICODE.
Important: Collations based on the Unicode Collation Algorithm of the Unicode Standard version 4.0.0 have been deprecated in Version 10.1 and might be removed in a future release. For more information, see Collations based on the Unicode Collation Algorithm of the Unicode Standard version 4.0.0 have been deprecated.
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 4096 bytes is chosen for the database and the default storage group, IBMSTOGROUP, is not created.
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:
- server's local database directory on the path indicated by pPath or, if the path is not specified, the default database path defined in the database manager system configuration file. A local database directory resides on each file system that contains a database.
- server's system database directory for the attached instance. The resulting directory entry will contain the database name and a database alias.
  If the API was called from a remote client, the client's system database directory is also updated with the database name and an alias.
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 authorities:
- 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 DBAD, 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 required 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 Linux and UNIX operating 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 usage, 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.