Usage question for "IMPORT SCHEMA"

[apollo13]

According to the postgres docs, the remote_schema is defined by the FDW itself, I am not sure what to put in there…

I tried a few things and all I get is:

IMPORT FOREIGN SCHEMA ther LIMIT TO (haptneu) FROM SERVER ids01_reichel INTO public; 
WARNING:  opened informix connection with warnings
DETAIL:  informix SQLSTATE 01I04: "Database selected "
NOTICE:  connected to an non-Informix SE instance
IMPORT FOREIGN SCHEMA

and no table is imported (also no error if the table name is wrong).

If I just do:

IMPORT FOREIGN SCHEMA ther FROM SERVER ids01_reichel INTO public;

I get all tables \o/ (that said I am still not sure about the schema)

[psoo]

Please post the output after setting the DEBUG level:

SET client_min_messages TO DEBUG1;

It might also worth to see the messages emitted by DEBUG5, but be sure to use the latest version of
the Informix FDW then. There was a bug, which caused DEBUG5 to leak the configured FDW password to the client...see commit 9307bc9.

[apollo13]

Here is the output for DEBUG5:

bap=# IMPORT FOREIGN SCHEMA ther LIMIT TO (haptneu) FROM SERVER ids01_reichel INTO public; 
DEBUG:  StartTransactionCommand
DEBUG:  StartTransaction
DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
DEBUG:  ProcessUtility
DEBUG:  informix connection dsn "reichel@ids01"
DEBUG:  reusing cached informix connection "informixreichelids01"
WARNING:  opened informix connection with warnings
DETAIL:  informix SQLSTATE 01I04: "Database selected "
NOTICE:  connected to an non-Informix SE instance
DEBUG:  prepare query "SELECT tabid, trim(owner), tabname FROM systables WHERE tabid >= 100 AND owner = 'ther' AND tabname IN ('haptneu') ORDER BY tabname DESC"
DEBUG:  declare cursor "informixreichelids01_cur2_1"
DEBUG:  informix FDW exception count: 1
DEBUG:  informix_fdw: undo open
DEBUG:  informix_fdw: undo allocate
DEBUG:  informix_fdw: undo declare
DEBUG:  informix_fdw: undo prepare
DEBUG:  CommitTransactionCommand
DEBUG:  CommitTransaction
DEBUG:  name: unnamed; blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
IMPORT FOREIGN SCHEMA

Manually executing the query also returns zero rows, something is really weird. If I manually query systables with dbaccess for 'hpatneu' I get:

tabname          hpatneu
owner            ther
partnum          2098154
tabid            696
rowsize          775
ncols            88
nindexes         15
nrows            27331.00000000
created          11.02.2016
version          48824433
tabtype          T
locklevel        P
npused           13666.00000000
fextsize         20684
nextsize         2068
flags            0
site
dbname
type_xid         0
am_id            0
pagesize         2048
ustlowts         2016-03-26 01:11:06.00000
secpolicyid      0
protgranularity
statchange
statlevel        A

Do you see anything which could kill the other query?

[apollo13]

Ups, I did have a typo there -- arg :D

[apollo13]

Actually, would it be possible to throw some kind of error if the requested tables are not found instead of silently ignoring them?

[psoo]

I don't think so. I deliberately choose this way to avoid the annoyance of programmatically imported Informix schemas which could be empty (scripts, migration, ...) and play around with SAVEPOINTs to make them safe enough. But we could have a WARNING or at least a NOTICE to tell the user something is possibly not the way he expects.

[rossj-cargotel]

What might save someone else some head scratching and debugging is to mention that remote_schema in import foreign schema remote_schema is actually the owner of the informix tables on the informix server.
This worked for me to import all of the tables owned by user informix:

import foreign schema informix from server cargotel_tcp into jplm_dev options (informixserver 'cargodevnet', informixdir '/opt/IBM/informix',database 'jplm', client_locale 'en_US.utf8', db_locale 'en_US.819');

[psoo]

See also the IDS documentation for CREATE SCHEMA, e.g. https://www.ibm.com/support/knowledgecenter/SSGU8G_11.70.0/com.ibm.sqls.doc/ids_sqs_0483.htm