Cool, Thanks Anoop, eric From: Anoop Sharma [mailto:anoop.sha...@esgyn.com] Sent: Thursday, March 2, 2017 9:18 AM To: user@trafodion.incubator.apache.org; Kevin <kaihua...@esgyn.cn> Cc: d...@trafodion.incubator.apache.org Subject: RE: trafodion jdbc metadata related question
One can also use metadata views to get information about columns, keys etc for a particular table. Metadata views are defined in TRAFODION."_MD_" schema. >>get views in schema "_MD_"; Views in Schema TRAFODION._MD_ ============================== COLUMNS_VIEW INDEXES_VIEW KEYS_VIEW REF_CONSTRAINTS_VIEW SEQUENCES_VIEW TABLES_VIEW VIEWS_VIEW --- SQL operation complete. >> Doing an INVOKE on the view will tell the columns that will be returned. For ex: create table tpart (a int not null primary key, b int) salt using 4 partitions; >>select left(column_name,5) from "_MD_".columns_view where table_name = >>'TPART'; (EXPR) -------------------- A B _SALT --- 3 row(s) selected. >> Similarly select from keys_view will tell the keys for a table. anoop From: Eric Owhadi [mailto:eric.owh...@esgyn.com] Sent: Thursday, March 2, 2017 6:25 AM To: Kevin <kaihua...@esgyn.cn<mailto:kaihua...@esgyn.cn>> Cc: user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org>; d...@trafodion.incubator.apache.org<mailto:d...@trafodion.incubator.apache.org> Subject: RE: trafodion jdbc metadata related question Thanks Kevin, That helps regards Eric Sent from my Samsung Galaxy smartphone. -------- Original message -------- From: "Xu, Kai-Hua (Kevin)" <kaihua...@esgyn.cn<mailto:kaihua...@esgyn.cn>> Date: 3/2/17 3:59 AM (GMT-06:00) To: Eric Owhadi <eric.owh...@esgyn.com<mailto:eric.owh...@esgyn.com>> Cc: user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org>, d...@trafodion.incubator.apache.org<mailto:d...@trafodion.incubator.apache.org> Subject: RE: trafodion jdbc metadata related question Hi Eric, As you see, it's true that some columns are hidden. Another way is to retrieve metadata table directly as follows: --retrieve all columns select c.* from "_MD_".objects o left join "_MD_".columns c on o.object_uid=c.object_uid where o.schema_name='ERIC' and o.object_name='TBL'; --retrieve all clustering key columns select k.* from "_MD_".objects o left join "_MD_".keys k on o.object_uid=k.object_uid where o.schema_name=' ERIC' and o.object_name='TBL'; Best Regards, Kevin Xu From: Eric Owhadi [mailto:eric.owh...@esgyn.com] Sent: Thursday, March 02, 2017 3:24 AM To: user@trafodion.incubator.apache.org<mailto:user@trafodion.incubator.apache.org>; d...@trafodion.incubator.apache.org<mailto:d...@trafodion.incubator.apache.org> Subject: trafodion jdbc metadata related question Hello trafodioneers, I am trying to use jdbc metadata api to retrieve the equivalent of what a showddl or invoke would provide in sqlci or trafci. If I use DatabaseMetaData metadata = conn.getMetaData(); ResultSet rs = metadata.getColumns("TRAFODION", "ERIC","TBL", "%"); The resulting resultset does not provide hidden columns. For example the "_SALT_" and "_DIVISION_1_". And when doing a rs = metadata.getPrimaryKeys("TRAFODION",primarySchema, primaryTable); the "_DIVISION_1_" column is returned, but not the _SALT_. Any idea if it is possible to use jdbc to get same level of info I would get with showddl or invoke in SQLCI/trafci? Thanks in advance for the help, Eric
