CREATE EXTERNAL MODEL statement

The CREATE EXTERNAL MODEL statement registers an external AI model in Db2.

The model definition includes credentials and metadata for an external model, such as Watsonx.ai or OpenAI. By including this information in the definition, the model can be referenced by a logical name in builtin functions, such as TO_EMBEDDING or TEXT_GENERATION.

Syntax

Read syntax diagramSkip visual syntax diagramCREATE EXTERNAL MODELmodel-namePROVIDER WATSONXwatsonx-clausesOPENAIopenai-clauses
watsonx-clauses
Read syntax diagramSkip visual syntax diagram●IDstring-constant●URLstring-constant●TYPEmodel-type●KEYstring-constant●PROJECT IDSPACE_IDstring-constant●
openai-clauses
Read syntax diagramSkip visual syntax diagram●IDstring-constant●URLstring-constant●TYPEmodel-type●KEYstring-constant●
model-type
Read syntax diagramSkip visual syntax diagramTEXT_EMBEDDING RETURNING VECTOR(dimension,coordinate-type)TEXT_GENERATION RETURNINGtext-generation-data-typewatsonx-text-generation-optionsopenai-text-generation-options
coordinate-type
Read syntax diagramSkip visual syntax diagramREALFLOAT32INT8
watsonx-text-generation-options
Read syntax diagramSkip visual syntax diagramMAX_NEW_TOKENSinteger-constantMIN_NEW_TOKENSinteger-constantRANDOM_SEEDinteger-constantREPETITION_PENALTYnumeric_constantSTOP_SEQUENCE1string-constantSTOP_SEQUENCE2string-constantSTOP_SEQUENCE3string-constantSTOP_SEQUENCE4string-constantTEMPERATUREnumeric-constantTIME_LIMITinteger-constantTRUNCATE_INPUT_TOKENSinteger_constant
openai-text-options
Read syntax diagramSkip visual syntax diagramFREQUENCY_PENALTYnumeric-constantMAX_COMPLETION_TOKENSinteger-constantREASONING_EFFORTMINIMALLOWMEDIUMHIGHSTOP1string-constantSTOP2string-constantSTOP3string-constantSTOP4string-constantTEMPERATUREnumeric-constant
text-generation-data-type
Read syntax diagramSkip visual syntax diagram VARCHARCHARACTERVARVARYING( integer-constantOCTETSCODEUNITS32)CLOBCHARACTERCHARLARGE OBJECTinteger-constant(1M)integer-constantKMGOCTETSCODEUNITS32

Description

model-name
Specifies the external model.
The name, including an implicit or explicit qualifier, must not identify an external model that already exists at the current server (SQLSTATE 42710). If a qualifier is not specified, the current schema is implicitly assigned. If the name is explicitly qualified with a schema name, the schema name must not begin with the characters "SYS" (SQLSTATE 42939).
PROVIDER
Specifies the provider of the language model being used.
Db2 supports integration with various API providers for invoking language models by using REST APIs. Each supported API provider includes a REST API endpoint tailored to the specific model types it supports.
  • WATSONX: When using the WATSONX provider, Db2 leverages the REST API specifications provided by watsonx.ai for model invocation.
  • OPENAI: This provider option is for language models hosted in a private cloud environment. The OPENAI provider supports only REST APIs that are compatible with the OpenAI API specification.
ID string-constant
Uniquely specifies the target model within the context of a given provider. Different providers may use the same model ID to refer to similar or identical models, but the ID must be unique within each provider’s model catalog. Db2 uses the ID to locate and interact with the correct remote model during API calls. The value must match the identifier published in the provider’s documentation or model registry. If the model ID does not correspond to the expected value, integration and invocation fails.
The string-constant value must not be an empty string, and must not exceed 256 bytes in length (SQLSTATE 42615).
URL string-constant
Specifies the HTTPS endpoint of the watsonx embedding API.
This endpoint must be a valid and accessible HTTPS URL that points to the embedding service interface of the model. Db2 uses this URL to issue API requests for model invocation. If watsonx supplies only a base URL and expects the client to construct the full endpoint dynamically, Db2 assembles the complete URL according to the documented conventions of the watsonx API. The final URL must conform to the watsonx expected request structure and support the required authentication mechanisms.
The string-constant value must not exceed 512 bytes in length (SQLSTATE 42622).
Specifies the HTTPS endpoint of the external model provided by the model provider.
This endpoint must be a valid and accessible HTTPS URL that points to the model’s inference or service interface. Db2 uses this URL to issue API requests for model invocation. If the provider supplies only a base URL and expects the client to construct the full endpoint dynamically, Db2 will automatically assemble the complete URL according to the provider’s documented conventions. The final URL must conform to the provider’s expected request structure and support the required authentication mechanisms.
The string-constant value value must not be an empty string, and must not exceed 256 bytes in length (SQLSTATE 42615).
PROJECT_ID string-constant
Specifies the project identifier for API usage, billing, and access control.
This value must follow platform naming rules and be unique within the account.
The string-constant value must not exceed 256 bytes in length (SQLSTATE 42622).
SPACE_ID string-constant
Specifies the space identifier for API usage, billing, and access control.
This value must follow platform naming rules and be unique within the account.
The string-constant value must not exceed 256 bytes in length (SQLSTATE 42622).
KEY string-constant
Provides the authentication token or API key required to access the provider's model endpoint.
This key is used to authorize requests made from Db2 to the external provider. The key must be valid, active, and have the necessary permissions to invoke the specified model. The key is typically issued by the provider and can be subject to expiration or usage limits.
The string-constant value must not exceed 512 bytes in length (SQLSTATE 42622).
model-type
Defines the functional category of the external model being registered. This parameter informs Db2 of the model’s intended purpose and determines how it will be integrated and invoked within SQL operations.
The supported model types are:
TEXT_EMBEDDING
Indicates that the model transforms input text into high-dimensional vector embeddings, which can be used for tasks such as semantic similarity, clustering, or vector-based search. This type of model is used in the TO_EMBEDDING builtin function.
dimension
An integer constant that specifies the dimension of the vector. For a row-organized table, the value must be from 1 to 8168 for FLOAT32 and from 1 to 32672 for INT8 (SQLSTATE 42611). For a column-organized table, the value must be from 1 to 8148 for FLOAT32 and from 1 to 32592 for INT8 (SQLSTATE 42611).
coordinate-type
  • REAL or FLOAT32: single-precision, 4-byte floating point.
  • INT8: 1-byte integer.
TEXT_GENERATION
This type of model generates text based on an input prompt. This model type is used in the TEXT_GENERATION builtin function.
watsonx-text-generation-options
These options can only be specified for provider WATSONX with model type TEXT_GENERATION (SQLSTATE 42601).
MAX_NEW_TOKENS integer-constant
Specifies the maximum number of tokens the text generation model can generate. Controls output length and resource usage. The integer-constant value must be greater than or equal to 0 (SQLSTATE 42615)
MIN_NEW_TOKENS integer-constant
Specifies the minimum number of tokens the text generation model can generate. Controls output length and resource usage. The integer-constant value must be greater than or equal to 0 (SQLSTATE 42615).
RANDOM_SEED integer-constant
Random number generator seed to use in sampling mode for experimental repeatability. The integer-constant value must be greater than 0 (SQLSTATE 42615).
REPETITION_PENALTY numeric-constant
Represents the penalty for penalizing tokens that have already been generated or belong to the context. The value 1.0 means that there is no penalty. The numeric-constant value must be between 1.0 and 2.0 inclusive (SQLSTATE 42615).
STOP_SEQUENCE1 to STOP_SEQUENCE4 string_constant
Up to four strings which will cause the text generation to stop if or when any are produced as part of the output. Stop sequences encountered prior to the minimum number of tokens being generated will be ignored. The string-constant value must not be an empty string, and must not exceed 256 bytes in length (SQLSTATE 42615).
TEMPERATURE numeric-constant
Used to modify the next-token probabilities in sampling mode. Values less than 1.0 sharpen the probability distribution, producing a more deterministic output. Values greater than 1.0 flatten the probability distribution, producing a more varied output. A value of 1.0 has no effect. The numeric-constant value must be between 0.05 and 2.0 inclusive (SQLSTATE 42615).
TIME_LIMIT integer-constant
Time limit in milliseconds. If text generation is not completed within the time limit, the generation will stop. The text generated within the time limit is returned along with the TIME_LIMIT stop reason. Depending on the plan with the provider and on the model being used, an enforced time limit may be in place. The integer-constant value must be greater than 0 (SQLSTATE 42615).
TRUNCATE_INPUT_TOKENS integer-constant
Specifies the maximum number of input tokens allowed. Use this setting to prevent requests from failing when the input is longer than the configured limits. If truncation occurs, tokens are removed from the start of the input and the end of the input remains intact. If this value is greater than the model’s maximum sequence length, the request fails when the total token count exceeds that maximum. To find the model's maximum sequence length, refer to the specific model's documentation. The integer-constant value must be greater than 0 (SQLSTATE 42615).
openai-text-generation-options
These options can only be specified for provider OPENAI with model type TEXT_GENERATION (SQLSTATE 42601).
FREQUENCY_PENALTY numeric-constant
The numeric-constant value must be between -2.0 and 2.0 inclusive (SQLSTATE 42615).
MAX_COMPLETION_TOKENS integer-constant
The integer-constant value must be greater than or equal to 0 (SQLSTATE 42615).
REASONING_EFFORT
Constrains effort on reasoning. Supported values are MINIMAL, LOW, MEDIUM, and HIGH. Reducing reasoning effort can result in faster responses and fewer tokens used on reasoning in a response.
STOP1 to STOP4 string_constant
Up to four strings which cause the text generation to stop if any are produced as part of the output. The string-constant value must not be an empty string, and must not exceed 256 bytes in length (SQLSTATE 42615).
TEMPERATURE numeric-constant
The numeric-constant value must be between 0.00 and 2.0 inclusive (SQLSTATE 42615).

Notes

  • The owner of the external model is granted ALTER and USAGE privileges on the model. The owner of the model can drop the model.
  • There is no validation of credentials or other settings when creating a model. To validate the settings, invoke the appropriate builtin function such as TO_EMBEDDING or TEXT_GENERATION.

watsonx.ai example

CREATE EXTERNAL MODEL aschema.granite-embed
PROVIDER WATSONX KEY 'api-keyxxxxx'
ID 'ibm/slate-30m-english-rtrvr'
TYPE TEXT_EMBEDDING RETURNING VECTOR(1024, FLOAT32)
URL 'https://us-south.ml.cloud.ibm.com/ml/v1/text/embeddings'
PROJECT_ID 'f5599cfd-7aca-451e-b897-35d8f624e775'