Administrative API Reference

sqlecrea - 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 multi-node environment, this API affects all nodes that are listed in the $HOME/sqllib/db2nodes.cfg file.

The node from which this API is called becomes the catalog node for the new database.

Authorization

One of the following:

sysadm
sysctrl

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.

Version

sqlenv.h

C API Syntax

/* File: sqlenv.h */ /* API: Create Database */ /* ... */ SQL_API_RC SQL_API_FN sqlecrea ( char * pDbName, char * pLocalDbAlias, char * pPath, struct sqledbdesc * pDbDescriptor, struct sqledbcountryinfo * pCountryInfo, char Reserved2, void * pReserved1, struct sqlca * pSqlca); /* ... */

Generic API Syntax

/* File: sqlenv.h */ /* API: Create Database */ /* ... */ SQL_API_RC SQL_API_FN sqlgcrea ( unsigned short PathLen, unsigned short LocalDbAliasLen, unsigned short DbNameLen, struct sqlca * pSqlca, void * pReserved1, unsigned short Reserved2, struct sqledbcountryinfo * pCountryInfo, struct sqledbdesc * pDbDescriptor, char * pPath, char * pLocalDbAlias, char * pDbName); /* ... */

API 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.

pSqlca

Output. A pointer to the sqlca structure. For more information about this structure, see SQLCA.

pReserved1

Input. A spare pointer that is set to null or points to zero.

Reserved2

Input. Reserved for future use.

pCountryInfo

Input. A pointer to the sqledbcountryinfo structure, containing the locale and the code set for the database. For more information about this structure, see SQLEDBCOUNTRYINFO. For a list of valid locale and code set values, see one of the Quick Beginnings books. May be NULL.

pDbDescriptor

Input. A pointer to the database description block used when creating the database. The database description block may be used to supply values that are permanently stored in the configuration file of the database, such as collating sequence. Its structure is described in SQLEDBDESC. May be NULL.

pPath

Input. On UNIX based 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 OS/2 or the Windows operating system, specifies the letter of the drive on which to create the database. May be NULL.

Note: For MPP systems, 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 an MPP system cannot be a relative path.

pLocalDbAlias

Input. A string containing the alias to be placed in the client's system database directory. May be NULL. If no local alias is specified, the database name is the default.

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.

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 MPP systems, 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 an MPP system 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

Uses the collating sequence of the operating system based on the current country code.

IDENTITY

The collating sequence is the identity sequence, where strings are compared byte for byte, starting with the leftmost byte.

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 segment directories that will be created and used to store the DAT, IDX, and LF files.

dft_extentsize

Specifies the default extentsize 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 4KB 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.

Sample Programs

C: \sqllib\samples\c\dbconf.c
COBOL: \sqllib\samples\cobol\dbconf.cbl
REXX: \sqllib\samples\rexx\dbconf.cmd

Usage Notes

CREATE DATABASE:

Creates a database in the specified subdirectory. In an MPP system, creates the database on all nodes listed in db2nodes.cfg, and creates a $DB2INSTANCE/NODExxxx directory under the specified subdirectory at each node, where xxxx represents the local node number. In a non-MPP system, 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 server node on which this API is called becomes the catalog node for the new database. Two nodegroups are created automatically: IBMDEFAULTGROUP and IBMCATGROUP. For more information, see the SQL Reference.
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.
Creates SYSCATSPACE, TEMPSPACE1, and USERSPACE1 table spaces. The SYSCATSPACE table space is only created on the catalog node. All nodes have the same table space definitions.
Grants the following:
- DBADM authority, and CONNECT, CREATETAB, BINDADD, CREATE_NOT_FENCED, IMPLICIT_SCHEMA, and LOAD privileges to the database creator
- CONNECT, CREATETAB, BINDADD, and IMPLICIT_SCHEMA privileges 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.

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 an MPP environment, the database manager creates a subdirectory, $DB2INSTANCE/NODExxxx, under the specified or default path on all nodes. 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 nodes are stored in different directories (even if the subdirectory $DB2INSTANCE under the specified or default path is shared by all nodes).

CREATE DATABASE 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 (see SQLEDBDESC).

The "eye-catcher" 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 CREATE DATABASE cannot be changed later, and all character comparisons in the database use the specified collating sequence. This affects the structure of indexes as well as the results of queries.

Use sqlecadb to define different alias names for the new database.

See Also

sqlabndx - Bind

sqlecadb - Catalog Database

sqlecran - Create Database at Node

sqledpan - Drop Database at Node

sqledrpd - Drop Database.

[ Top of Page | Previous Page | Next Page ]