无工具支持时能否通过IBM.Data.Db2库生成现有DB2表的DDL？

Absolutely! Even with your limited environment, you can generate table DDL using the IBM.Data.Db2 library—you just need to leverage Db2's built-in system catalog tables, which store all metadata about your database objects. Here's a step-by-step approach tailored to your situation:

1. Query Db2's System Catalogs for Metadata #

Db2 keeps all table/column details in system catalog views (like SYSCAT.COLUMNS, SYSCAT.TABLES, SYSCAT.KEYCOLUSE). You can run simple SELECT queries against these to pull the info you need, then use a DataReader to process the results.

First, get column-level details with this query:

SELECT 
  COLNAME, 
  TYPENAME, 
  LENGTH, 
  SCALE, 
  NULLS, 
  DEFAULT,
  COLNO
FROM SYSCAT.COLUMNS 
WHERE TABSCHEMA = 'YOUR_SCHEMA_NAME' AND TABNAME = 'YOUR_TABLE_NAME'
ORDER BY COLNO

• COLNAME: The column name
• TYPENAME: Data type (e.g., VARCHAR, DECIMAL)
• LENGTH: Length of the type (for string/numeric types)
• SCALE: Decimal places (for numeric types)
• NULLS: 'Y' if nullable, 'N' otherwise
• DEFAULT: Default value for the column (if any)
• COLNO: Column position to maintain order

To get primary key columns:

SELECT COLNAME 
FROM SYSCAT.KEYCOLUSE 
WHERE TABSCHEMA = 'YOUR_SCHEMA_NAME' AND TABNAME = 'YOUR_TABLE_NAME' AND TYPE = 'P'
ORDER BY COLSEQ

If you don't know the table's schema, run this to find it:

SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TABNAME = 'YOUR_TABLE_NAME'

2. Use C# to Fetch Data and Build DDL #

Since you're familiar with C#, you can write a method that uses IBM.Data.Db2 to execute these queries, read results with a DataReader, and dynamically build the DDL string. Here's a working example:

using IBM.Data.Db2;
using System.Text;
using System.Collections.Generic;

public string GenerateDb2TableDDL(string connectionString, string schema, string tableName)
{
    var ddl = new StringBuilder();
    // Start the CREATE TABLE statement
    ddl.AppendLine($"CREATE TABLE {schema}.{tableName} (");

    using (var conn = new Db2Connection(connectionString))
    {
        conn.Open();

        // Fetch column details
        var columnCmd = new Db2Command(@"SELECT COLNAME, TYPENAME, LENGTH, SCALE, NULLS, DEFAULT, COLNO
                                        FROM SYSCAT.COLUMNS 
                                        WHERE TABSCHEMA = @Schema AND TABNAME = @Table
                                        ORDER BY COLNO", conn);
        columnCmd.Parameters.Add(new Db2Parameter("@Schema", schema));
        columnCmd.Parameters.Add(new Db2Parameter("@Table", tableName));

        bool isFirstColumn = true;
        using (var columnReader = columnCmd.ExecuteReader())
        {
            while (columnReader.Read())
            {
                if (!isFirstColumn)
                    ddl.AppendLine(",");
                else
                    isFirstColumn = false;

                // Extract column properties
                string colName = columnReader["COLNAME"].ToString();
                string dataType = columnReader["TYPENAME"].ToString();
                int length = columnReader["LENGTH"] != DBNull.Value ? Convert.ToInt32(columnReader["LENGTH"]) : 0;
                int scale = columnReader["SCALE"] != DBNull.Value ? Convert.ToInt32(columnReader["SCALE"]) : 0;
                bool isNullable = columnReader["NULLS"].ToString() == "Y";
                string defaultValue = columnReader["DEFAULT"] != DBNull.Value ? columnReader["DEFAULT"].ToString() : null;

                // Build column definition
                var columnDef = $"  {colName} {dataType}";
                // Add length for string types
                if (dataType.Equals("VARCHAR", StringComparison.OrdinalIgnoreCase) || 
                    dataType.Equals("CHAR", StringComparison.OrdinalIgnoreCase))
                {
                    columnDef += $"({length})";
                }
                // Add precision/scale for decimal types
                else if (dataType.Equals("DECIMAL", StringComparison.OrdinalIgnoreCase) || 
                         dataType.Equals("NUMERIC", StringComparison.OrdinalIgnoreCase))
                {
                    columnDef += $"({length},{scale})";
                }

                // Add null constraint if needed
                if (!isNullable)
                    columnDef += " NOT NULL";

                // Add default value if present
                if (!string.IsNullOrEmpty(defaultValue))
                    columnDef += $" DEFAULT {defaultValue}";

                ddl.Append(columnDef);
            }
        }

        // Fetch and add primary key
        var pkCmd = new Db2Command(@"SELECT COLNAME 
                                    FROM SYSCAT.KEYCOLUSE 
                                    WHERE TABSCHEMA = @Schema AND TABNAME = @Table AND TYPE = 'P'
                                    ORDER BY COLSEQ", conn);
        pkCmd.Parameters.Add(new Db2Parameter("@Schema", schema));
        pkCmd.Parameters.Add(new Db2Parameter("@Table", tableName));

        var pkColumns = new List<string>();
        using (var pkReader = pkCmd.ExecuteReader())
        {
            while (pkReader.Read())
            {
                pkColumns.Add(pkReader["COLNAME"].ToString());
            }
        }

        if (pkColumns.Count > 0)
        {
            ddl.AppendLine(",");
            ddl.AppendLine($"  PRIMARY KEY ({string.Join(", ", pkColumns)})");
        }

        // Close the CREATE TABLE statement
        ddl.AppendLine(");");
    }

    return ddl.ToString();
}

3. Extend for More Objects (If Needed) #

If you need to include indexes, foreign keys, or other constraints, you can query additional system catalogs:

• Indexes: SYSCAT.INDEXES and SYSCAT.INDEXCOLUSE
• Foreign keys: SYSCAT.REFERENCES and SYSCAT.KEYCOLUSE (filter by TYPE = 'F')

Important Notes #

• Case Sensitivity: Db2 might treat schema/table names as case-sensitive depending on how they were created. If your query returns no results, try wrapping names in double quotes (e.g., TABNAME = ""MyTable"").
• Permissions: Ensure you have read access to the SYSCAT views—most regular user accounts have this by default, but if not, you'll need to ask your DBA for access.
• Data Type Edge Cases: Some Db2 data types (like TIMESTAMP, BLOB) don't need length/scale parameters, so adjust the code to handle those as needed.

内容的提问来源于stack exchange，提问作者John Butler