How to get the SQL of a View using GetSchema in AS400

Question

I am trying to get the SQL using GetSchema but the View_Definition column is not retrieved.

This is my function to retrieve the view information:

internal DataTable GetViewDefinition(AS400Library library, string viewName)
{
    var connection = ODBC.Instance.GetConnection(library);

    try
    {
        connection.Open();
        return connection.GetSchema("Views", new string[] { library.ToString(), null, viewName });
    }
    finally
    {
        connection.Close();
    }
}

GetViewDefinition is returning the following columns but not the view_definition:

1. TABLE_CAT
2. TABLE_SCHEM
3. TABLE_NAME
4. TABLE_TYPE
5. REMARKS

How can I get the SQL itself?

Answer 1

ODBC and ADO.NET are generic interfaces, they only support the "least common denominator" of features and are unaware of implementation-specific details.

You can find view definitions in the System i catalog views, QSYS2.VIEWS or INFORMATION_SCHEMA.VIEWS. Check the manual for your version, as names may be different; INFORMATION_SCHEMA is only supported since 6.1, I believe.