Create Schema

Create database schema

What is database schema?

A database schema is similar to a library on IBM i platforma and and it has some additional objects like journal, journal receiver, SQL catalog, and an optional data dictionary. Once the schema is created, we can store all the objects within it same as we keep in the library.

Create schema

To create a schema, use the CREATE SCHEMA command from the STRSQL session or the ACS Run SQL Script. For Example:Create a schema named TEST

CREATE SCHEMA TEST

If we create a schema whose name is longer than 10 characters then a system name will be generated for schema. Both Longer name and the generated system name can be used in the SQL statements to access the schema once it is created. Please note that the longer name is only recognized by DB2 and the system name can be used in the IBM i environment.

For schema name longer than 10 characters and an ordinary identifier, the 10 characters system schema name will be generated as follows:

First five characters of the name

5 digit unique number

For Example: the system schema name for LONGNAMESCHEMA would be LONGN00001.

If a schema name is a delimited identifier and longer than 10 characters, a 10 character system name will be generated as follows:

The first four characters from within the delimiter will be used as the first characters of the system name schema and a 4 digit number is appended if not all upper case letters, digits, underscores. For Example:

The system name for "longnameschema" would be "long0001"

If the first four characters are all upper case letters, digits, underscores, an underscore and a five digit unique number is appended. For Example:

The system name for "LONGNameschema" would be LONG_00001