private bool CheckTableExists(IDbConnection conn, DB2ZOSTableSourceInfo tableSource)
{
String query = "SELECT NAME as TABLE_NAME "
+ "FROM SYSIBM.SYSTABLES "
+ "WHERE CREATOR = '" + tableSource.Database.Identifier + "' and NAME = '" + tableSource.Name.ToUpper() + "'";
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, query);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
return(reader.Read());
}
}
public override IEnumerable <ITableSourceColumnInfo> GetTableSourceColumns(ITableSourceInfo tableSource)
{
DB2ZOSTableSourceInfo tableInfo = tableSource as DB2ZOSTableSourceInfo;
if (tableInfo == null)
{
return(null);
}
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
if (!CheckTableExists(conn, tableInfo))
{
throw new IntrospectionServiceException("'" + tableSource.DisplayName + "' is not a valid physical table name.");
}
}
return(GetColumnsInfo(tableInfo));
}
private IList <ITableSourceForeignKeyInfo> GetForeignKeysInfo(DB2ZOSTableSourceInfo tableSource)
{
IList <ITableSourceForeignKeyInfo> foreignKeys = new List <ITableSourceForeignKeyInfo>();
string query = "SELECT " +
"A.RELNAME as FK_NAME, " +
"B.COLNAME as FKCOLUMN_NAME," +
"A.REFTBCREATOR as PKTABLE_SCHEM, " +
"A.REFTBNAME as PKTABLE_NAME, " +
"(SELECT KYS.COLNAME FROM SYSIBM.SYSINDEXES PKS " +
"INNER JOIN SYSIBM.SYSKEYS KYS ON KYS.IXNAME = PKS.NAME " +
"WHERE PKS.TBNAME = A.REFTBNAME AND PKS.CREATOR = A.CREATOR AND PKS.UNIQUERULE = 'P') " +
"as PKCOLUMN_NAME, " +
"A.DELETERULE as CASCADE " +
"FROM SYSIBM.SYSRELS A, SYSIBM.SYSFOREIGNKEYS B " +
"WHERE A.TBNAME = '" + tableSource.Name.ToUpper() + "' " +
"AND B.TBNAME = '" + tableSource.Name.ToUpper() + "' " +
"AND A.CREATOR = '" + tableSource.Database.Identifier + "' " +
"AND B.CREATOR = '" + tableSource.Database.Identifier + "' " +
"AND A.RELNAME = B.RELNAME " +
"ORDER BY A.RELNAME, B.COLSEQ; ";
Console.WriteLine("GetForeignKeysInfo, SQL: " + query);
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, query);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
string foreignKeyName = (string)reader["FK_NAME"];
string columnName = (string)reader["FKCOLUMN_NAME"];
string referencedSchemaName = (string)reader["PKTABLE_SCHEM"];
string referencedTableName = (string)reader["PKTABLE_NAME"];
string referencedColumnName = (string)reader["PKCOLUMN_NAME"];
bool isCascadeDelete = "CASCADE".EqualsIgnoreCase((string)reader["CASCADE"]);
string qualifiedReferencedTableName = GetQualifiedIdentifier(referencedSchemaName, referencedTableName);
var a = tableSource.Database;
ITableSourceInfo referencedTableSource = new DB2ZOSTableSourceInfo(DatabaseServices, tableSource.Database, referencedTableName, qualifiedReferencedTableName);
ITableSourceForeignKeyInfo foreignKeyInfo = new DB2ZOSTableSourceForeignKeyInfo(tableSource, foreignKeyName, columnName, referencedTableSource, referencedColumnName, isCascadeDelete);
foreignKeys.Add(foreignKeyInfo);
}
}
return(foreignKeys);
}
}
private IList <ITableSourceColumnInfo> GetColumnsInfo(DB2ZOSTableSourceInfo tableSource)
{
IList <ITableSourceColumnInfo> columnsInfo = new List <ITableSourceColumnInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
string query = @"SELECT COLS.*,
(
SELECT PKS.NAME FROM SYSIBM.SYSINDEXES PKS
INNER JOIN SYSIBM.SYSKEYS KYS ON KYS.IXNAME = PKS.NAME
WHERE PKS.TBNAME = COLS.TBNAME AND PKS.CREATOR = COLS.TBCREATOR AND PKS.UNIQUERULE = 'P' AND KYS.COLNAME = COLS.NAME
) as PK_NAME,
COL_CHK.CHECKCONDITION as CHECK_CLAUSE,
UDTS.ENCODING_SCHEME as ORDERING_ROUT_NAME
FROM SYSIBM.SYSCOLUMNS COLS
INNER JOIN SYSIBM.SYSTABLES TAB ON TAB.NAME = COLS.TBNAME AND TAB.CREATOR = COLS.TBCREATOR
LEFT JOIN SYSIBM.SYSVIEWDEP VDEP ON TAB.TYPE = 'V' and TAB.CREATOR = VDEP.BCREATOR and TAB.NAME = VDEP.DNAME
LEFT JOIN SYSIBM.SYSCHECKDEP DEP ON
((TAB.TYPE = 'T' or TAB.TYPE = 'P') and DEP.TBOWNER = TAB.CREATOR and DEP.TBNAME = TAB.NAME and DEP.COLNAME = COLS.NAME)
OR (TAB.TYPE = 'V' and DEP.TBOWNER = VDEP.BCREATOR and DEP.TBNAME = VDEP.BNAME and DEP.COLNAME = COLS.NAME)
LEFT JOIN SYSIBM.SYSCHECKS COL_CHK ON
((TAB.TYPE = 'T' or TAB.TYPE = 'P') and COL_CHK.TBOWNER = DEP.TBOWNER and COL_CHK.TBNAME = TAB.NAME and COL_CHK.CHECKNAME = DEP.CHECKNAME)
OR (TAB.TYPE = 'V' and COL_CHK.TBOWNER = DEP.TBOWNER and COL_CHK.TBNAME = DEP.TBNAME and COL_CHK.CHECKNAME = DEP.CHECKNAME)
LEFT JOIN SYSIBM.SYSDATATYPES UDTS ON UDTS.NAME = COLS.COLTYPE
WHERE (COLS.GENERATED_ATTR is null OR COLS.GENERATED_ATTR <> 'A') and TAB.CREATOR = '" + tableSource.Database.Identifier + "' and TAB.NAME = '" + tableSource.Name.ToUpper() + "'";
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, query);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
int primaryKeyColumnCount = 0;
while (reader.Read())
{
string columnName = (string)reader["NAME"];
IDataTypeInfo datatype = CreateDataTypeInfo(reader["COLTYPE"] == DBNull.Value ? null : ((string)reader["COLTYPE"]).Trim(),
reader["LENGTH"] == DBNull.Value ? 0 : Convert.ToInt32(reader["LENGTH"]),
reader["LENGTH"] == DBNull.Value ? 0 : Convert.ToInt32(reader["LENGTH"]),
reader["SCALE"] == DBNull.Value ? 0 : Convert.ToInt32(reader["SCALE"]),
reader["CHECK_CLAUSE"] == DBNull.Value ? null : (string)reader["CHECK_CLAUSE"],
reader["ORDERING_ROUT_NAME"] == DBNull.Value ? null : (string)reader["ORDERING_ROUT_NAME"],
reader["CCSID"] == DBNull.Value ? 0 : Convert.ToInt32(reader["CCSID"]));
bool isMandatory = "N".EqualsIgnoreCase((string)reader["NULLS"]);
bool isPrimaryKey = (reader["PK_NAME"] != DBNull.Value);
var defaultVal = (string)reader["DEFAULT"];
bool isAutoGenerated = "I".EqualsIgnoreCase(defaultVal) || "J".EqualsIgnoreCase(defaultVal);
DB2ZOSTableSourceColumnInfo info = new DB2ZOSTableSourceColumnInfo(tableSource, columnName, datatype, isMandatory, isPrimaryKey, isAutoGenerated);
columnsInfo.Add(info);
primaryKeyColumnCount += isPrimaryKey ? 1 : 0;
}
if (primaryKeyColumnCount > 1)
{
//we don't support composite primary keys so setting all to non primary key
IList <ITableSourceColumnInfo> columnsInfoCopy = new List <ITableSourceColumnInfo>();
foreach (ITableSourceColumnInfo c in columnsInfo)
{
columnsInfoCopy.Add(new DB2ZOSTableSourceColumnInfo(c.TableSource, c.Name, c.DataType, c.IsMandatory, false, c.IsAutoGenerated));
}
columnsInfo = columnsInfoCopy;
}
}
}
return(columnsInfo);
}
