When fetching the tables and columns the names are normalized to lowercase

[IceS2]

I'm using this library as well to fetch metadata from DB2 and the Tables and Columns are normalized to lowercase instead of returning as they are.

I was checking the code and found the method that does that, while the whole code keeps denormalizing them in order to query the database further.

I'd like to understand why this normalization is done instead of retrieving the names as they are in the database and allow the users to normalize it afterwards if they actually desire to do so.

Thanks

[bchoudhary6415]

Hello @IceS2
By default, all tables and schemas stored in a Db2 database are created using capital letters only, so the whole code keeps denormalizing them in order to query the database further.

Can you please share the sample case while retrieving the normalization happens and the names as they are in the database are not giving?

Thank you

[IceS2]

Hey @bchoudhary6415 ,

For instance, we are using the SQLAlchemy inspector in order to retrieve all the tables on a given schema.

This prompts thje call to

    @reflection.cache
    def get_table_names(self, connection, schema=None, **kw):
        current_schema = self.denormalize_name(schema or self.default_schema_name)
        systbl = self.sys_tables
        query = sql.select(systbl.c.tabname).\
            where(systbl.c.type == 'T').\
            where(systbl.c.tabschema == current_schema).\
            order_by(systbl.c.tabname)
        return [self.normalize_name(r[0]) for r in connection.execute(query)]

This returns the normalized name, that is given by

    def normalize_name(self, name):
        if isinstance(name, str):
            name = name
        if name is not None:
            return name.lower() if name.upper() == name and \
               not self.identifier_preparer._requires_quotes(name.lower()) \
               else name
        return name

As you mentioned, the default is that the tables in a DB2 Database are created using capital letters so that is what I would expect the get_table_names to return, but due to the normalize_name method, I'm receiving them as lowercase.

[harshach]

@bchoudhary6415
could you please let us know what would be the reason normalize those names as per @IceS2 comment above

[bchoudhary6415]

Hello @harshach and @IceS2

Here's what I've observed regarding the difference in table name casing:

When executing the following query directly using SQLAlchemy:

qry = text("SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = 'USER'")
result = conn.execute(qry)
for row in result:
    print(row)

The table names are returned in uppercase. This is because, by default, IBM Db2 return table names in uppercase when queried directly from system catalog views like SYSCAT.TABLES, as it's created in uppercase only.

However, when using the dialect.get_table_names() method like this:

dialect = conn.dialect
schema_name = 'USER'
table_names = dialect.get_table_names(conn, schema=schema_name)
for table_name in table_names:
    print(table_name)

The table names are returned in lowercase. This behavior is due to the internal usage of the normalize_name() method within SQLAlchemy's dialect system.

As of now, I'm investigating the specific reason the normalize_name() method is used in this context, as it might be designed to ensure uniformity and handle case sensitivity or database-specific behavior across different environments. I'll update you as soon as I have more details.

Thank you