Bug: manage.py migrate Fails with Invalid Defaults and Datetime Formatting on DB2 Community Edition

[justTil]

Bug Report: Django Migrations Fail on DB2 Community Edition with Invalid DEFAULTs and Datetime Formatting

Summary

When using the following setup with Django and DB2:

• Django==4.2.21
• ibm_db==3.2.6
• ibm_db_django==1.5.3.0
• Database: DB2 Community Edition (Linux CLI Driver)
• System: macOS with Python 3.13

running python manage.py migrate results in errors due to incompatibilities in how the ibm_db_django backend handles DEFAULT values and datetime string formatting.

This report documents the issue and provides tested fixes to make the backend compatible with DB2 Community Edition.

---

Problem Description

Upon executing the migrate command, Django attempts to create the django_migrations table (and others), but DB2 throws the following errors:

Error 1: Invalid DEFAULT or IDENTITY Value

django.db.migrations.exceptions.MigrationSchemaMissing:
Unable to create the django_migrations table (Statement Execute Failed:
[IBM][CLI Driver][DB2/LINUXX8664] SQL0574N  DEFAULT value or IDENTITY attribute value is not valid
for column "APPLIED" in table "DB2INST1.DJANGO_MIGRATIONS".  Reason code: "1".  SQLSTATE=42894 SQLCODE=-574)

This appears when Django tries to assign DEFAULT values to DateTimeField, BooleanField, or TimeField, which DB2 CE handles differently from Enterprise edition.

Error 2: Invalid Datetime Format

django.db.utils.DatabaseError: Statement Execute Failed:
[IBM][CLI Driver][DB2/LINUXX8664] SQL0180N  The syntax of the string representation of a datetime value is incorrect.
SQLSTATE=22007 SQLCODE=-180

This occurs when datetime values are passed using ISO 8601 strings (e.g. "2024-11-06T13:15:00Z"), which DB2 CE does not accept by default.

Workaround & Fixes

Fix 1: Avoid Problematic DEFAULTs in column_sql()

Patch the column_sql method in schemaEditor.py to skip generating default clauses for problematic types:

def column_sql(self, model, field, include_default=True, notnull=False):
    db_parameter = field.db_parameters(connection=self.connection)
    sql = db_parameter['type']
    if sql is None:
        return None, None

    # Skip default if type is problematic on DB2 Community Edition
    problematic_defaults = ('BooleanField', 'DateTimeField', 'TimeField')
    field_type = field.get_internal_type()

    if include_default and field_type not in problematic_defaults:
        if (field.default is not None) and field.has_default():
            value = field.get_default()
            if callable(value):
                value = value()
            if isinstance(value, (datetime.date, datetime.time, datetime.datetime)):
                value = "'%s'" % value
            elif isinstance(value, bool):
                value = '1' if value else '0'
            elif isinstance(value, bytes):
                value = "BX'%s'" % value.hex()
            else:
                value = "'%s'" % value if isinstance(value, str) else str(value)
            sql += f" DEFAULT {value}"
        elif not self.skip_default(field):
            value = self.effective_default(field)
            if value is not None and value != '':
                if isinstance(value, bool):
                    value = '1' if value else '0'
                sql += f" DEFAULT {value}"

    if not field.null or (notnull and 'DEFAULT' in sql):
        sql += " NOT NULL"

    if field.primary_key:
        sql += " PRIMARY KEY"
    elif field.unique:
        sql += " UNIQUE"

    return sql, []

Fix 2: Format Datetime Values for DB2 in operations.py

Patch the adapt_datetimefield_value and related methods to use DB2-compliant formats:

def adapt_datetimefield_value(self, value):
    if value is None:
        return None
    if hasattr(value, 'resolve_expression'):
        return value
    # Format: 'YYYY-MM-DD-HH.MM.SS'
    return f"TIMESTAMP('{value.strftime('%Y-%m-%d-%H.%M.%S')}')"

def adapt_datefield_value(self, value):
    if value is None:
        return None
    return f"DATE('{value.strftime('%Y-%m-%d')}')"

def adapt_timefield_value(self, value):
    if value is None:
        return None
    return f"{value.strftime('%H.%M.%S')}"

Request

Please consider incorporating these compatibility fixes into the ibm_db_django package. These patches allow basic Django functionality such as migrate to succeed without backend or driver-level errors, and bring the backend in line with Django 4.x expectations on DB2 Community Edition.

Let me know if you'd like help submitting a pull request.

Thank you for maintaining this adapter!

PS: I did not run into this problem when using DB2 EE