dbeaver / dbeaver/dbeaver

Informix : show different type of objects in separated folders

Open
#5,610 4 comments 1 reaction 1 assignee Claimed by @LonwoLonwo View on GitHub
x:informix xf:metadata xf:navigator
Dominant language
Java
Stars
51.8k
Forks
4.4k
Avg merge
3d 8h
Merged PRs (30d)
188

Description

* dbeaver 6.0
* Informix driver JDBC 4.10.JC12

This is a feature request.
Today the object tree in the database folder is very limited for Informix.

At the database navigator tree, the sequences, external tables, synonyms are shown in *Tables* folder .
Run these statements :
```sql
database sysmaster;
create database dbeaverdb with buffered log ;

create table test_table (id integer);
create sequence test_sequence increment by 1 ;
create external table test_external (id smallint) using ( datafiles('DISK:/tmp/test_external.txt'));
create public synonym test_syn_pub for sysmaster:sysdual ;
create private synonym test_syn_priv for sysmaster:sysdual ;
```
![image](https://user-images.githubusercontent.com/7062410/55179218-bfa02100-5165-11e9-936d-b5d99a68ccdf.png)

I don't know how the dbeaver gather the objects if they use JDBC interface or run they own SQL statements.
If they run their own statements, this probably occurs because the system table _systables_ contains all these objects (yes, that is nonsense, but is how it is).

Here is few information to help identify correctly all this :

Tables references :
- systables
[references here ](https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqlr.doc/ids_sqr_072.htm)
column tabtype =
T = Table
E = External Table
V = View
Q = Sequence
P = Private synonym
S = Public synonym

- syssyntable - for synonyms
[references here](https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqlr.doc/ids_sqr_069.htm)

- syssequences
[references here](https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqlr.doc/ids_sqr_067.htm)
- sysexternal
[references here](https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqlr.doc/ids_sqr_411.htm)
sysextdfiles [references here](https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqlr.doc/ids_sqr_409.htm)
sysextcols [references here](https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqlr.doc/ids_sqr_410.htm)

Here a few SQLs :
**For synonyms**
```sql
select t.tabid, t.owner, t.tabname, t.created, t.tabtype
, decode(t.tabtype,'S','Public','P','Private')
, s.dbname||decode(nvl(s.servername,'-'),'-','','@'||s.servername)||':'||trim(s.owner)||'.'||s.tabname as remote_table
, t2.tabname as local_table
from systables t, syssyntable s, outer systables t2
where t.tabtype in ('P','S')
and s.tabid = t.tabid
and t2.tabid = s.btabid
order by 5 ;
```
**For sequences**
```sql
select t.tabid, t.tabname as seqname, t.created
, seq.*
from systables t, syssequences seq
where t.tabtype = 'Q'
and t.tabid = seq.tabid
```

**For External tables**
I don't have anything here, but if needed, please let me know to look for them...

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.