zzzeek/ibm-db-sa — pull request #1 by sayap (2013-07-08 11:03:21+00:00)
Title: Add reflection for z/OS, plus minor fixes here and there

DB2 for z/OS comes with a bunch of quirks. With these commits, I am able to use dialect db2+pyodbcz successfully against DB2 for z/OS 9.1 in my project (*).

The main change is the addition of `ZOSReflector` class. Also, I have added a "flavor" attribute to each dialect, with values of luw, as400, and zos, to allow easier introspection from application regardless of the driver used (i.e. ibm_db, pyodbc, and zxjdbx)

(*) This statement comes with an asterisk because some of the quirks are handled locally in my application code, including:

- A self referential foreign key has to be added with `use_alter=True` and usually has `ondelete='NO ACTION'`.
- A statement can only reference up to 15 tables, thus breaking table creation for table with many foreign keys. `use_alter=True` comes to the rescue again.
- Use uppercase for quoted identifier. I have had my script butchered by DB2 for z/OS DBA who would just select all and change all text to uppercase. This is to defense against that.
- If a table is created in an explicitly set tablespace (i.e. `CREATE TABLE xxx (...) IN {{database}}.{{tablespace}}`), a unique index has to be created separately for each of its primary and unique constraints [1].
- When using a `CASE` statement that returns values from bind parameters, at least one of the values need to be casted to the appropriate type, to avoid error SQL0418N.
- `ALTER TABLE` capability is very limited, e.g. can't drop a column and can't toggle the nullable flag. The ability to drop a column is deemed as a crown jewel in DB2 11 [2].

These quirks seem a bit too extreme so I decided not to include them into ibm-db-sa.

[1] http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_createtable.htm (Technically, if a table is created in an implicit tablespace, the unique index would be created automatically)
[2] http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?infotype=AN&subtype=CA&htmlfid=897/ENUS212-364