Informix : show different type of objects in separated folders
- 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 ;
```

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
Assessment
This issue has not been assessed yet.