Please allow me to clarify.
The term “file” has multiple meanings for the AS400.
First there is the “physical file” which I mentioned before, and it contains “records” composed of “fields”.
For ODBC purposes, we can pretend it’s a SQL table (but it’s important to know that it’s not really a SQL table).
An AS400 “physical file” is completely different than a file in a file system.
The AS400’s file system is called the IFS (Integrated File System), which can store any file: plain-text, images, etc.
We can work with the IFS using a UNIX-like shell called Qshell (or simply QSH).
(It also supports the usual drag and drop; I’ve mapped MS Windows drives to the IFS and editors like TextPad and Notepad++ can open plain-text files without issue.)
The command-line approach you found from unix.com might help with reading files from the IFS, but it probably won’t help for SQL and ODBC.
Also, I see that you are in Italy, and therefore your AS400 might be configured for multi-language support, meaning your data might not be in EBCDIC and you might not have to worry.
I suggest checking with your admins.
What follows is my USA experience with an AS400 that had everything in EBCDIC.
Asking the admins to create SQL tables with a Unicode encoding would work, along with ensuring any code that interacts with legacy “physical files” follows EBCDIC rules (if applicable).
For example, in my experience the ASCII range of characters work fine in EBCDIC, but characters like ™ (and special characters copy+pasted from rich text, like Microsoft Word) will silently transform into junk during an INSERT or UPDATE.
(I was also using IBM’s specific AS400 JDBC driver for Java, so my experience might have been smoother than plain ODBC might offer.)
In summary, the AS400 has (at least) three forms of durable storage:
- “physical files”
- contain “records” composed of “fields”
- layout is defined using a language called DDS
- EBCDIC char encoding is likely
- easily accessible from RPG programs
- accessible via ODBC
- SQL tables
- layout is defined using DDL
- char encoding dictated by DDL (EBCDIC by default?)
- accessible from RPG programs via modern “free-form” syntax
- accessible via ODBC
- IFS files
- reside in a file system
- not accessible via ODBC, to my knowledge
There’s some nuance in the details, but (a) this is enough detail for now and (b) my memory is a bit hazy.
Finally, I don’t mean to dissuade you from pursuing Ecto + AS400 (because that would be awesome), but it might end up being a big challenge.
First up, some pioneering work might be needed because Ecto doesn’t currently support the IBM DB2 dialect, and the AS400’s SQL (DB400) is a sub-dialect of DB2.
But then again, this might not matter too much; I’ll explain.
Regarding the AS400 platform itself, organizations using it tend to have used it for decades, meaning:
- some of the data structure may predate modern database design
- the file/table layouts likely serve the purposes of the RPG programs that use them, and may not focus on data consistency as a whole
In other words, it’s likely the existing data structure has grown organically over a long period of time and might be very messy from the perspective of modern data tools like Ecto.
Also, the AS400 has a feature called “journaling”, which is how the AS400 supports transactions.
Journaling is on a table-by-table basis and it’s not uncommon for journaling to be disabled, meaning no database transactions.
Any RPG code using unjournaled tables will need to be updated to enabling journaling, so it’s not a simple task.
If you plan on accessing legacy data then you might need to write a lot of SQL by hand, and also program defensively if journaling is not enabled.
When I was tasked with accessing an AS400 via Java, I chose to use a lightweight library called Mybatis because it is designed to work with handwritten SQL without going crazy, and I needed both SQL and my sanity in order to work with the legacy nature of the system.
If I needed to do the same thing in Clojure I would use HugSQL.
Ecto or the Elixir ecosystem may already has something similar; AyeSQL looks promising.
Again, I’m sharing my experience in the spirit of knowing what you’re up against, and not telling you “don’t do it”.
If you’ve got the motivation and energy, by all means do it!