CREATE DATALAKE SCHEMA statement

Invocation

This statement can only be executed as a dynamic statement using EXECUTE IMMEDIATE. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The owner of the associated Db2 schema can create a Datalake schema with any valid schema-name or authorization-name.

An authorization ID that holds DBADM authority can create a Datalake schema with any valid schema-name or authorization-name.

To use the LOCATION clause, the authorization ID of the statement must be authorized on the remote storage alias used in the LOCATION clause.

Syntax

Description

DATALAKE

This keyword is required when creating a Datalake schema

IF NOT EXISTS

Checks whether the specified Datalake schema already exists. If it exists, no error is returned.

schema-name

An identifier that names the schema over which the Datalake schema is created. The name must identify a schema already described in the catalog (SQLSTATE 42710). The name cannot begin with 'SYS' (SQLSTATE 42939).

LOCATION string-constant

Specifies the location of the schema. The location must be a DB2REMOTE identifier containing a storage access alias that connects to the remote storage where the schema is to reside. For more information, see Remote storage connectivity for Datalake tables. Access to the file path specified in string-constant can be subject to additional file permissions depending on the storage service being used.

Notes

• If the current schema, which was set using either the USE or SET SCHEMA command, does not exist in Db2 metastore, an attempt is made to automatically create the schema in Db2.
• If the LOCATION clause in a CREATE DATALAKE SCHEMA statement is omitted, Datalake tables created in that Datalake schema must provide a LOCATION clause in their CREATE DATALAKE TABLE statement.
• When creating a Datalake table in a Datalake schema with a location, the user can override the location by providing an explicit location clause in the CREATE DATALAKE TABLE statement.
• When the schema is explicitly created with the CREATE DATALAKE SCHEMA statement, the schema owner is granted CREATEIN, DROPIN, and ALTERIN privileges on the schema with the ability to grant these privileges to other users.
• The schema owner is assumed to be a user (not a group).
• The definer of any object created as part of the CREATE SCHEMA statement is the schema owner. The schema owner also controls privileges granted as part of the CREATE SCHEMA statement.

Examples

• Example 1: Datalake schema with a location.

  CREATE DATALAKE SCHEMA ON DB2 SCHEMA myschema LOCATION
  
  ‘DB2REMOTE://ODFDEFAULT/DEFAULT/MYSCHEMA’
  
  CREATE DATALAKE TABLE myschema.mytable(id int)

• Example 2: Datalake schema with a location and overridden with explicit location clause in CREATE DATALAKE TABLE.

  CREATE DATALAKE SCHEMA ON DB2 SCHEMA myschema LOCATION ‘DB2REMOTE://ODFDEFAULT/DEFAULT/MYSCHEMA’
  
  CREATE DATALAKE TABLE myschema.mytable(id int) LOCATION
  
  ‘DB2REMOTE://ODFDEFAULT/DEFAULT/MYTABLE’

• Example 3: Datalake schema with no location.

  CREATE DATALAKE SCHEMA ON DB2 SCHEMA IF NOT EXISTS MYSCHEMA