You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@trafodion.apache.org by Eric Owhadi <er...@esgyn.com> on 2017/03/01 19:24:27 UTC
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
RE: trafodion jdbc metadata related question
Posted by Eric Owhadi <er...@esgyn.com>.
Cool,
Thanks Anoop,
eric
From: Anoop Sharma [mailto:anoop.sharma@esgyn.com]
Sent: Thursday, March 2, 2017 9:18 AM
To: user@trafodion.incubator.apache.org; Kevin <ka...@esgyn.cn>
Cc: dev@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.owhadi@esgyn.com]
Sent: Thursday, March 2, 2017 6:25 AM
To: Kevin <ka...@esgyn.cn>>
Cc: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>; dev@trafodion.incubator.apache.org<ma...@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)" <ka...@esgyn.cn>>
Date: 3/2/17 3:59 AM (GMT-06:00)
To: Eric Owhadi <er...@esgyn.com>>
Cc: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>, dev@trafodion.incubator.apache.org<ma...@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.owhadi@esgyn.com]
Sent: Thursday, March 02, 2017 3:24 AM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>; dev@trafodion.incubator.apache.org<ma...@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
RE: trafodion jdbc metadata related question
Posted by Anoop Sharma <an...@esgyn.com>.
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.owhadi@esgyn.com]
Sent: Thursday, March 2, 2017 6:25 AM
To: Kevin <ka...@esgyn.cn>
Cc: user@trafodion.incubator.apache.org; dev@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)" <ka...@esgyn.cn>>
Date: 3/2/17 3:59 AM (GMT-06:00)
To: Eric Owhadi <er...@esgyn.com>>
Cc: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>, dev@trafodion.incubator.apache.org<ma...@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.owhadi@esgyn.com]
Sent: Thursday, March 02, 2017 3:24 AM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>; dev@trafodion.incubator.apache.org<ma...@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
RE: trafodion jdbc metadata related question
Posted by Anoop Sharma <an...@esgyn.com>.
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.owhadi@esgyn.com]
Sent: Thursday, March 2, 2017 6:25 AM
To: Kevin <ka...@esgyn.cn>
Cc: user@trafodion.incubator.apache.org; dev@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)" <ka...@esgyn.cn>>
Date: 3/2/17 3:59 AM (GMT-06:00)
To: Eric Owhadi <er...@esgyn.com>>
Cc: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>, dev@trafodion.incubator.apache.org<ma...@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.owhadi@esgyn.com]
Sent: Thursday, March 02, 2017 3:24 AM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>; dev@trafodion.incubator.apache.org<ma...@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
RE: trafodion jdbc metadata related question
Posted by Eric Owhadi <er...@esgyn.com>.
Thanks Kevin,
That helps
regards
Eric
Sent from my Samsung Galaxy smartphone.
-------- Original message --------
From: "Xu, Kai-Hua (Kevin)" <ka...@esgyn.cn>
Date: 3/2/17 3:59 AM (GMT-06:00)
To: Eric Owhadi <er...@esgyn.com>
Cc: user@trafodion.incubator.apache.org, dev@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.owhadi@esgyn.com]
Sent: Thursday, March 02, 2017 3:24 AM
To: user@trafodion.incubator.apache.org; dev@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
RE: trafodion jdbc metadata related question
Posted by Eric Owhadi <er...@esgyn.com>.
Thanks Kevin,
That helps
regards
Eric
Sent from my Samsung Galaxy smartphone.
-------- Original message --------
From: "Xu, Kai-Hua (Kevin)" <ka...@esgyn.cn>
Date: 3/2/17 3:59 AM (GMT-06:00)
To: Eric Owhadi <er...@esgyn.com>
Cc: user@trafodion.incubator.apache.org, dev@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.owhadi@esgyn.com]
Sent: Thursday, March 02, 2017 3:24 AM
To: user@trafodion.incubator.apache.org; dev@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
RE: trafodion jdbc metadata related question
Posted by "Xu, Kai-Hua (Kevin)" <ka...@esgyn.cn>.
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.owhadi@esgyn.com]
Sent: Thursday, March 02, 2017 3:24 AM
To: user@trafodion.incubator.apache.org; dev@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
RE: trafodion jdbc metadata related question
Posted by "Xu, Kai-Hua (Kevin)" <ka...@esgyn.cn>.
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.owhadi@esgyn.com]
Sent: Thursday, March 02, 2017 3:24 AM
To: user@trafodion.incubator.apache.org; dev@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