Database-Specific Extraction Settings: DB2, Oracle, SQL Server

Some databases store table and column comments outside of standard JDBC metadata. DB2, Oracle, and SQL Server each require custom SQL queries to retrieve these descriptions during schema extraction. Starlake provides the columnRemarks and tableRemarks fields in the extraction YAML for this purpose. MySQL and PostgreSQL expose comments through standard JDBC metadata calls and do not need extra configuration.

info

The columnRemarks and tableRemarks configuration applies to the extract-schema command only. It does not affect extract-data.

Configuring Column and Table Remarks

The columnRemarks and tableRemarks fields accept SQL queries with two placeholders:

• {{schema}} -- replaced at runtime with the schema name being extracted.
• {{table}} -- replaced at runtime with the table name being extracted.

These fields are optional. Configure them only when you need to include column and table descriptions in the generated schema files.

DB2

DB2 Configuration

DB2 stores column descriptions in SYSIBM.SQLCOLUMNS (COLUMN_TEXT field) and table descriptions in QSYS2.SYSTABLES (TABLE_TEXT field).

metadata/extract/db2_extract.sl.yml

extract:
  connectionRef: "my_db2_connection"
  jdbcSchemas:
    - schema: "starbake"
      columnRemarks: SELECT COLUMN_NAME, COLUMN_TEXT FROM "SYSIBM"."SQLCOLUMNS" WHERE TABLE_SCHEM = '{{schema}}' AND TABLE_NAME = '{{table}}'
      tableRemarks: SELECT TABLE_TEXT FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = '{{schema}}' AND TABLE_NAME = '{{table}}'
      ...

Oracle

Oracle Configuration

Oracle stores comments in ALL_TAB_COMMENTS and ALL_COL_COMMENTS.

metadata/extract/oracle_extract.sl.yml

extract:
  connectionRef: "my_oracle_connection"
  jdbcSchemas:
    - schema: "STARBAKE"
      columnRemarks: SELECT COLUMN_NAME, COMMENTS FROM ALL_COL_COMMENTS WHERE OWNER = '{{schema}}' AND TABLE_NAME = '{{table}}'
      tableRemarks: SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE OWNER = '{{schema}}' AND TABLE_NAME = '{{table}}'
      ...

note

Oracle schema and table names are typically uppercase. Make sure the schema value in your configuration matches the case used in the database.

SQL Server

SQL Server Configuration

SQL Server stores extended properties (including descriptions) via sys.extended_properties. The MS_Description property is the conventional name for table and column comments.

metadata/extract/sqlserver_extract.sl.yml

extract:
  connectionRef: "my_sqlserver_connection"
  jdbcSchemas:
    - schema: "dbo"
      columnRemarks: >
        SELECT c.name AS COLUMN_NAME, CAST(ep.value AS NVARCHAR(MAX)) AS COLUMN_TEXT
        FROM sys.columns c
        JOIN sys.tables t ON c.object_id = t.object_id
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        LEFT JOIN sys.extended_properties ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description'
        WHERE s.name = '{{schema}}' AND t.name = '{{table}}'
      tableRemarks: >
        SELECT CAST(ep.value AS NVARCHAR(MAX)) AS TABLE_TEXT
        FROM sys.tables t
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        LEFT JOIN sys.extended_properties ep ON ep.major_id = t.object_id AND ep.minor_id = 0 AND ep.name = 'MS_Description'
        WHERE s.name = '{{schema}}' AND t.name = '{{table}}'
      ...

Summary: System Catalog Tables by Database

Database	Column comments	Table comments
DB2	SYSIBM.SQLCOLUMNS (COLUMN_TEXT)	QSYS2.SYSTABLES (TABLE_TEXT)
Oracle	ALL_COL_COMMENTS (COMMENTS)	ALL_TAB_COMMENTS (COMMENTS)
SQL Server	sys.extended_properties (MS_Description)	sys.extended_properties (MS_Description)
MySQL	Standard JDBC metadata (no config needed)	Standard JDBC metadata (no config needed)
PostgreSQL	Standard JDBC metadata (no config needed)	Standard JDBC metadata (no config needed)

Frequently Asked Questions

Which databases require special configuration for comment extraction in Starlake?

DB2, Oracle, and SQL Server require configuring columnRemarks and tableRemarks in the extraction YAML file. MySQL and PostgreSQL retrieve comments via standard JDBC metadata without extra configuration.

How do the {{schema}} and {{table}} placeholders work?

These are substitution variables. Starlake automatically replaces them with the schema name and table name being extracted at runtime when executing the SQL query.

Where are column comments stored in DB2?

DB2 stores column descriptions in SYSIBM.SQLCOLUMNS (COLUMN_TEXT field) and table descriptions in QSYS2.SYSTABLES (TABLE_TEXT field).

Where are column comments stored in Oracle?

Oracle uses ALL_COL_COMMENTS for column comments and ALL_TAB_COMMENTS for table comments. Schema and table names are typically uppercase.

Where are column comments stored in SQL Server?

SQL Server uses sys.extended_properties with the MS_Description property for column and table descriptions.

Is it mandatory to configure columnRemarks and tableRemarks?

No. These fields are optional. They are only needed if you want to extract comments/descriptions in addition to the table schema.