Hi @colleenk_1, it looks like you have two separate problems.

1. From the JDBC feedback it appears that the account you are using to connect to your new Linux-based Informix server has not been given permission to create tables. You'll have to ask your IT group to enable that ability.
2. From the "normal" Informix feedback (which is ODBC based) it appears that you haven't created an ODBC DSN for the new Linux server yet.

I believe you will need to resolve the permission issue in (1) regardless of whether you use JDBC or ODBC. If you stick with JDBC you won't have to solve issue (2).

As an aside, you will likely have a better experience using the Informix Non-Spatial (JDBC) format rather than the "generic" JDBC format. (The strongest argument for using the ODBC based Informix Non-Spatial format is that it might have better performance.)

---

Hi @colleenk_1, it looks like you have two separate problems.

1. From the JDBC feedback it appears that the account you are using to connect to your new Linux-based Informix server has not been given permission to create tables. You'll have to ask your IT group to enable that ability.
2. From the "normal" Informix feedback (which is ODBC based) it appears that you haven't created an ODBC DSN for the new Linux server yet.

I believe you will need to resolve the permission issue in (1) regardless of whether you use JDBC or ODBC. If you stick with JDBC you won't have to solve issue (2).

As an aside, you will likely have a better experience using the Informix Non-Spatial (JDBC) format rather than the "generic" JDBC format. (The strongest argument for using the ODBC based Informix Non-Spatial format is that it might have better performance.)

Thanks for your quick response @ravenkopelman !

 

For 1. The weird thing is that I can use that same login using a tool like DBeaver and create tables. It does work with the Informix JDBC writer, so not a huge deal - just odd.

 

 

I need to solve the second problem though, because I have spatial data I need to write as well. When I try to use the informix jdbc writer and make the spatial column well known text, it just skips the spatial column and writes the others.

 

 

Do you have any ideas?

---

I have a number of questions; perhaps we can narrow this down:

1. From your log snippet, it looks like FME is running on Windows in all these examples? If so, are you using 64-bit or 32-bit FME?

2. For the working AIX server example, was this FME on Windows connecting to an Informix server on AIX? And now you cannot connect from FME on Windows to an Informix server on Linux? Which FME writer did you use successfully with the AIX server ("IBM Informix Non-Spatial", "IBM Informix Non-Spatial (JDBC)", "IBM Informix Spatial", or "JDBC (Java Database Connectivity)")?

3. For your experiment writing with "the built in writer (non-spatial)", i.e. FME's "IBM Informix Non-Spatial" writer, did you have the Informix ODBC driver installed? (You can see from the "ODBC Data Sources" control panel, Drivers tab. Make sure to pick the right control panel - 32-bit for 32-bit FME, or 64-bit for 64-bit FME.) Do you have a DSN created? (Same control panel, but "User DSN" or "System DSN" tabs.)

4. For your experiment with the JDBC writer, was the table you were trying to write called "GIS.B$PRI_COND_UG_N"? Did it exist in your database before the write (i.e., you wanted FME to create it)? Were you able to create it and load data with the same login and the DBeaver tool? If you had to create it externally, can FME load data into the existing table?

I'm flailing a bit here - it may be worth setting up a demo with one of our Experts - but perhaps the answers will get us a big closer.

---

I have a number of questions; perhaps we can narrow this down:

1. From your log snippet, it looks like FME is running on Windows in all these examples? If so, are you using 64-bit or 32-bit FME?

2. For the working AIX server example, was this FME on Windows connecting to an Informix server on AIX? And now you cannot connect from FME on Windows to an Informix server on Linux? Which FME writer did you use successfully with the AIX server ("IBM Informix Non-Spatial", "IBM Informix Non-Spatial (JDBC)", "IBM Informix Spatial", or "JDBC (Java Database Connectivity)")?

3. For your experiment writing with "the built in writer (non-spatial)", i.e. FME's "IBM Informix Non-Spatial" writer, did you have the Informix ODBC driver installed? (You can see from the "ODBC Data Sources" control panel, Drivers tab. Make sure to pick the right control panel - 32-bit for 32-bit FME, or 64-bit for 64-bit FME.) Do you have a DSN created? (Same control panel, but "User DSN" or "System DSN" tabs.)

4. For your experiment with the JDBC writer, was the table you were trying to write called "GIS.B$PRI_COND_UG_N"? Did it exist in your database before the write (i.e., you wanted FME to create it)? Were you able to create it and load data with the same login and the DBeaver tool? If you had to create it externally, can FME load data into the existing table?

I'm flailing a bit here - it may be worth setting up a demo with one of our Experts - but perhaps the answers will get us a big closer.

1. I am Windows 64 for Desktop, but my server (end goal of where I want to run this) is linux

 

2. It was my desktop windows version that was able to connect to the AIX server using the IBM Informix Non Spatial writer. Correct that I am no longer able to use that writer with my Informix Linux server

 

3. I think this is my problem - I have asked my Informix DBA to provide more driver information, but he is off shore, so we have not had a chance to connect yet.

 

4. The table did not exist, and I wanted FME to create it. Yes, I was able to log into Dbeaver and create it, add data. I was not able to load it using the generic JDBC writer, but I was with the informix jdbc writer.

 

 

I would love to set up a demo to make sure I have every thing set up correctly, but it is probably worth waiting until I am able to connect with my DBA to make sure the drivers are set up correctly on my side.

 

 

Thank you!!

 

 

---

Thanks for your quick response @ravenkopelman !

 

For 1. The weird thing is that I can use that same login using a tool like DBeaver and create tables. It does work with the Informix JDBC writer, so not a huge deal - just odd.

 

 

I need to solve the second problem though, because I have spatial data I need to write as well. When I try to use the informix jdbc writer and make the spatial column well known text, it just skips the spatial column and writes the others.

 

 

Do you have any ideas?

The difference between the generic JDBC and Informix JDBC formats is indeed strange, but we may have fixed that problem in 2018.1. Or it may be a consequence of the fact that we set the DELIMIDENT=Y property in the Informix JDBC writer. If you have the chance to try again, with the generic JDBC writer in FME 2018.1, or adding that flag to the generic writer, I would be curious as to the result.

 

 

Please post an Idea asking for Informix Spatial (JDBC) support - it would definitely increase the chances of it happening.

 

 

Finally, I am almost certain that you could convince the Informix Non-Spatial JDBC writer to write geometry (slowly), by using a SQLExecutor and manually crafting INSERT statements that use ST_GeomFromWKB() or friends. For a large data migration you are probably better off getting ODBC working.

 

 

(@paulnalos and our Experts team are the best points of contact for solving your ODBC configuration issues; I will bow out of that discussion.)

 

---

1. I am Windows 64 for Desktop, but my server (end goal of where I want to run this) is linux

 

2. It was my desktop windows version that was able to connect to the AIX server using the IBM Informix Non Spatial writer. Correct that I am no longer able to use that writer with my Informix Linux server

 

3. I think this is my problem - I have asked my Informix DBA to provide more driver information, but he is off shore, so we have not had a chance to connect yet.

 

4. The table did not exist, and I wanted FME to create it. Yes, I was able to log into Dbeaver and create it, add data. I was not able to load it using the generic JDBC writer, but I was with the informix jdbc writer.

 

 

I would love to set up a demo to make sure I have every thing set up correctly, but it is probably worth waiting until I am able to connect with my DBA to make sure the drivers are set up correctly on my side.

 

 

Thank you!!

 

 

re (1) con't: If you want to connect to Informix from FME (Server or Desktop) on Linux, the setup is a bit tricky (there are configuration files to create, and I'm not sure if these are sufficiently documented). We initially developed our Informix reader/writer for exactly this situation, but we've not actively tested it on Linux in a long time. You will need the Informix client software installed, and you will probably want an FME Desktop installed on Linux somewhere (same host as FME Server or a different one) to experiment with during the initial setup.

 

 

re (4) con't: It's surprising that FME's JDBC Informix writer couldn't create the table, while Dbeaver could with the same credentials, but at least FME was able to insert into that table once created. If this aspect becomes a blocker for you, we can investigate further.

---

re (1) con't: If you want to connect to Informix from FME (Server or Desktop) on Linux, the setup is a bit tricky (there are configuration files to create, and I'm not sure if these are sufficiently documented). We initially developed our Informix reader/writer for exactly this situation, but we've not actively tested it on Linux in a long time. You will need the Informix client software installed, and you will probably want an FME Desktop installed on Linux somewhere (same host as FME Server or a different one) to experiment with during the initial setup.

 

 

re (4) con't: It's surprising that FME's JDBC Informix writer couldn't create the table, while Dbeaver could with the same credentials, but at least FME was able to insert into that table once created. If this aspect becomes a blocker for you, we can investigate further.

^^ My comment about setup challenges above is specific to the non-JDBC option. (I missed that in my second draft; after clicking "Follow" above, my first draft was deleted - oops!)

 

 

You may also want to explore the insert-spatial-data-via-JDBC-WKT-and-SQLExecutor-transformers mentioned by Raven in the other thread, although this might not be fast or streamlined enough for your 700 tables.

 

---

The difference between the generic JDBC and Informix JDBC formats is indeed strange, but we may have fixed that problem in 2018.1. Or it may be a consequence of the fact that we set the DELIMIDENT=Y property in the Informix JDBC writer. If you have the chance to try again, with the generic JDBC writer in FME 2018.1, or adding that flag to the generic writer, I would be curious as to the result.

 

 

Please post an Idea asking for Informix Spatial (JDBC) support - it would definitely increase the chances of it happening.

 

 

Finally, I am almost certain that you could convince the Informix Non-Spatial JDBC writer to write geometry (slowly), by using a SQLExecutor and manually crafting INSERT statements that use ST_GeomFromWKB() or friends. For a large data migration you are probably better off getting ODBC working.

 

 

(@paulnalos and our Experts team are the best points of contact for solving your ODBC configuration issues; I will bow out of that discussion.)

 

Hi @colleenk_1, our expert team would love to get a closer look at this issue with you. Would you please reach out to our support team at https://www.safe.com/support/report-a-problem/ and reference this thread and/or my name?

 

Let's clarify a few things and then come back to this thread with a summary. Looking forward to connecting with you.

 

Brian

 

---

Hi @colleenk_1, our expert team would love to get a closer look at this issue with you. Would you please reach out to our support team at https://www.safe.com/support/report-a-problem/ and reference this thread and/or my name?

 

Let's clarify a few things and then come back to this thread with a summary. Looking forward to connecting with you.

 

Brian

 

Sounds great, @BrianAtSafe! I created a ticket (C136656).

 

---