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

> Configure Starlake schema extraction for DB2, Oracle, and SQL Server. Extract column and table comments using columnRemarks and tableRemarks SQL queries with template placeholders.

<script type="application/ld+json">
    {JSON.stringify({
      "@context": "https://schema.org",
      "@type": "FAQPage",
      "mainEntity": [
        {
          "@type": "Question",
          "name": "Which databases require special configuration for comment extraction in Starlake?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "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."
          }
        },
        {
          "@type": "Question",
          "name": "How do the {{schema}} and {{table}} placeholders work?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "These are substitution variables. Starlake automatically replaces them with the schema name and table name being extracted at runtime when executing the SQL query."
          }
        },
        {
          "@type": "Question",
          "name": "Where are column comments stored in DB2?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "DB2 stores column descriptions in SYSIBM.SQLCOLUMNS (COLUMN_TEXT field) and table descriptions in QSYS2.SYSTABLES (TABLE_TEXT field)."
          }
        },
        {
          "@type": "Question",
          "name": "Where are column comments stored in Oracle?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "Oracle uses ALL_COL_COMMENTS for column comments and ALL_TAB_COMMENTS for table comments. Schema and table names are typically uppercase."
          }
        },
        {
          "@type": "Question",
          "name": "Where are column comments stored in SQL Server?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "SQL Server uses sys.extended_properties with the MS_Description property for column and table descriptions."
          }
        },
        {
          "@type": "Question",
          "name": "Is it mandatory to configure columnRemarks and tableRemarks?",
          "acceptedAnswer": {
            "@type": "Answer",
            "text": "No. These fields are optional. They are only needed if you want to extract comments/descriptions in addition to the table schema."
          }
        }
      ]
    })}
  </script>

  <script type="application/ld+json">
    {JSON.stringify({
      "@context": "https://schema.org",
      "@type": "HowTo",
      "name": "How to Extract Column and Table Comments from DB2, Oracle, or SQL Server with Starlake",
      "description": "Configure columnRemarks and tableRemarks in the Starlake extraction YAML to retrieve table and column descriptions from databases that store them outside standard JDBC metadata.",
      "step": [
        {
          "@type": "HowToStep",
          "name": "Identify the database type",
          "text": "Determine if the source database is DB2, Oracle, or SQL Server. MySQL and PostgreSQL do not need extra configuration.",
          "position": 1
        },
        {
          "@type": "HowToStep",
          "name": "Locate the system catalog tables",
          "text": "Identify the system tables that contain column and table descriptions (e.g., ALL_COL_COMMENTS for Oracle, SYSIBM.SQLCOLUMNS for DB2, sys.extended_properties for SQL Server).",
          "position": 2
        },
        {
          "@type": "HowToStep",
          "name": "Configure columnRemarks",
          "text": "Add the columnRemarks field in the jdbcSchemas section with a SQL query using {{schema}} and {{table}} placeholders.",
          "position": 3
        },
        {
          "@type": "HowToStep",
          "name": "Configure tableRemarks",
          "text": "Add the tableRemarks field with the corresponding SQL query.",
          "position": 4
        },
        {
          "@type": "HowToStep",
          "name": "Run extract-schema",
          "text": "Execute starlake extract-schema --config <config_name> to generate schemas with comments included.",
          "position": 5
        }
      ]
    })}
  </script>



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

```yaml title="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`.

```yaml title="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.

```yaml title="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.