Github user arvind-narain commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/243#discussion_r50204189
--- Diff: core/conn/jdbc_type2/native/SrvrCommon.cpp ---
@@ -1634,6 +1635,53 @@ short do_ExecSMD(
tableParam[0], inputParam[0], inputParam[1],
inputParam[2], inputParam[3]);
break;
+ case SQL_API_SQLSTATISTICS:
+ if ((!checkIfWildCard(catalogNm, catalogNmNoEsc) ||
!checkIfWildCard(schemaNm, schemaNmNoEsc)
+ || !checkIfWildCard(tableNm, tableNmNoEsc)) &&
!metadataId)
+ {
+ executeException->exception_nr =
odbc_SQLSvc_GetSQLCatalogs_ParamError_exn_;
+ executeException->u.ParamError.ParamDesc =
SQLSVC_EXCEPTION_WILDCARD_NOT_SUPPORTED;
+
FUNCTION_RETURN_NUMERIC(EXECUTE_EXCEPTION,("EXECUTE_EXCEPTION"));
+ }
+
+ if (strcmp(catalogNm,"") == 0)
+ strcpy(tableName1,SEABASE_MD_CATALOG);
+ else
+ strcpy(tableName1, catalogNm);
+ tableParam[0] = tableName1;
+ convertWildcard(metadataId, TRUE, schemaNm, expSchemaNm);
+ convertWildcardNoEsc(metadataId, TRUE, schemaNm,
schemaNmNoEsc);
+ convertWildcard(metadataId, TRUE, tableNm, expTableNm);
+ convertWildcardNoEsc(metadataId, TRUE, tableNm,
tableNmNoEsc);
+ if( inputParam[5] != NULL )
+ {
+ sprintf(cunique, " and index.IS_UNIQUE=1");
+ }
+ inputParam[0] = schemaNmNoEsc;
+ inputParam[1] = expSchemaNm;
+ inputParam[2] = tableNmNoEsc;
+ inputParam[3] = expTableNm;
+ inputParam[4] = NULL;
+ snprintf((char *)sqlString->dataValue._buffer, totalSize,
+ "select "
+ "obj.CATALOG_NAME TABLE_CAT, obj.SCHEMA_NAME TABLE_SCHEM,
obj.OBJECT_NAME TABLE_NAME,"
+ "cast((case when index.IS_UNIQUE=0 then 1 else 0 end) as
smallint) NON_UNIQUE, "
+ "cast(NULL as varchar(32)) INDEX_QUALIFIER,
idxobj.OBJECT_NAME INDEX_NAME, "
+ "cast(-2 as smallint) TYPE, cols.COLUMN_NUMBER
ORDINAL_POSITION, "
+ "substr(cols.COLUMN_NAME,0,CHAR_LENGTH(cols.COLUMN_NAME))
COLUMN_NAME, "
+ "cast((case when keys.ORDERING=0 then 'A' else 'D' end) as
varchar(4)) ASC_OR_DESC,"
+ "cast(-2 as int) CARDINALITY, cast(-2 as int) PAGES,
cast(NULL as varchar(10)) FILTER_CONDITION "
--- End diff --
I checked on the query. With the current one if table name is not specified
( all indexes in a schema ) then I get index_names with NULL and get extra
entries - please do check.
Following query worked for me:
select
trim(obj.CATALOG_NAME) TABLE_CAT, trim(obj.SCHEMA_NAME) TABLE_SCHEM,
trim(obj.OBJECT_NAME) TABLE_NAME,
cast((case when index.IS_UNIQUE=0 then 1 else 0 end) as smallint)
NON_UNIQUE,
cast(NULL as varchar(1)) INDEX_QUALIFIER,
trim(idxobj.OBJECT_NAME) INDEX_NAME,
cast(3 as smallint) TYPE,
cast(cols.COLUMN_NUMBER as smallint) ORDINAL_POSITION,
trim(cols.COLUMN_NAME) COLUMN_NAME,
cast((case when keys.ORDERING=0 then 'A' else 'D' end) as varchar(4))
ASC_OR_DESC,
cast(NULL as int) CARDINALITY, cast(NULL as int) PAGES, cast(NULL as
varchar(1)) FILTER_CONDITION
from TRAFODION."_MD_".OBJECTS obj
join TRAFODION."_MD_".INDEXES index on obj.OBJECT_UID=index.BASE_TABLE_UID
join TRAFODION."_MD_".KEYS keys on index.INDEX_UID = keys.OBJECT_UID
join TRAFODION."_MD_".COLUMNS cols on obj.OBJECT_UID = cols.OBJECT_UID and
keys.COLUMN_NUMBER = cols.COLUMN_NUMBER
join TRAFODION."_MD_".OBJECTS idxobj on index.index_uid=idxobj.OBJECT_UID
where
(obj.SCHEMA_NAME = '%s' or trim(obj.SCHEMA_NAME) LIKE '%s' ESCAPE '\\')
and (obj.OBJECT_NAME = '%s' or trim(obj.OBJECT_NAME) LIKE '%s' ESCAPE '\\')
and (obj.object_type = 'BT')
%s FOR READ UNCOMMITTED ACCESS order by NON_UNIQUE, TYPE, INDEX_QUALIFIER,
INDEX_NAME, ORDINAL_POSITION;
Just looked at the documentation for SQLSTATISTICS - ASC_OR_DESC should be
char(1). For defaults I chose TYPE as 3 and others NULL
@zellerh - Hans - could you look at the query for getting index information
from metadata and advice - I referred to smdixquery in regression tests.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at [email protected] or file a JIRA ticket
with INFRA.
---
