The longer a query takes to run, the more CPU the AS400 will throw at it; the AS400 can sometimes be quite aggressive about it.
I’ve occasionally seen runaway queries eventually consume 50%~75% of the AS400’s CPU.
Depending on the size of your organization, your admins might catch it before things get that bad, but if you know you’ve got a runaway query on your hands, the best thing to do is notify your admins and they can kill it via WRKACTJOB.
Technically, everything running on the AS400 is a job running in a particular job queue, so it’s natural for the AS400 to keep throwing more resources at a long-running job in hopes of freeing up the queue.
#2
The AS400 can be very aggressive about record locks.
You may sometimes see your SQL statements time out with an error saying “FILE IN USE”.
This means some other process has a record lock and you must wait for the lock to be released.
A common cause of record locks are people keeping records open in their terminal. “Hanging out in the green screens” was a common occurrence at my old company and the quickest fix often involved walking over to other departments and asking someone to navigate back to the menus (and the response was always, “Oh sorry; I forgot.”). If we couldn’t find the person (lunch/meeting/etc) then we often killed their terminal session.
It’s possible that these headaches are not inherent to the AS400 and could simply be the result of how the RPG programmers at my old company programmed the terminal screens.
(Note: I’m not an AS400 expert and I’m glossing over a lot of technical detail. The above is my experience as a Java web programmer working with the AS400 for ~15 years.)
I thought of something else to be aware of when querying physical files.
Due to the fixed-width nature of physical files, the strings in query results will be right-padded with whitespace.
For example, if we query a CHAR(10) field then every string in the result set will be ten characters long, e.g.:
'Milano '
'Firenze '
'ten--chars'
Using the TRIM function will help, e.g., SELECT TRIM(MYFIELD) AS MYFIELD FROM MYLIBRARY.MYFILE, but that approach can become tedious and easy to accidentally omit.
Ecto may have a feature for auto-trimming strings in result sets.
Some updates here.
Thank you very much, Ted and all, for your useful explanations.
For my clients, this is a non priority task, so I’m focused on other activities and things proceed slowly. But this is an advantage for me.
In the previous week operations granted read access to some AS400 tables.
I proceeded just with the SQL and to put in place some selects (come complicated ones, also because the fields have really strange code names, so I have got a couple of calls, just to understand what is the meaning of the tables, columns and fields… AS400 people are stange, so I didn’t have a database map, as expected, but some wrietten explanations and these couple of calls ).
So for now I have just the queries in plain SQL.
I will proceed finalizing the query, and then later on on the Elixir Code.
By the way, things are moved in some way, so I have to put data in another Postgresql database, not to produce a JSON.
So these are my steps:
ODBC driver and connection inside Elixir --> worked;
Access to AS400 db --> worked;
SQL queries --> in progress;
Elixir code and connection with Postgresql.
The good news is that - for now - all is worked without pain. Just some things to understand (by the way, thank you for your help on this topic).
So I will try some Ecto programming: any suggestions on some tutorial for beginner about Ecto and Postgreesql?
).
