Huh again. Why would SQL allocate heap storage?
SQL would perform like a dog otherwise. The SQL engine is extremely powerful, but it relies on allocating huge blocks of memory (relatively speaking) to manage large complex networks of records in order to perform well.
Maybe it would be helpful to step back for a moment and clarify the difference between "static" and "heap" storage. When an RPG program is created the system will calculate the amount of memory that the program will need based on various declarations in the source code, and store that number in the *pgm object as "static storage size". Static memory is automatically allocated when the *pgm is activated. In many cases, static memory may be all that is required during the life of the program.
When you embed SQL into RPG, the pre-compiler will insert calls to other programs (specifically QSQLOPEN, QSQLCLSE, QSQLCMIT, and QSQROUTE). In turn, those programs will activate a dizzying array of service programs. The array of program/service programs activated to support SQL is quite astonishing. The static memory required for those activations is significant. But even that is still small compared to the heap storage that they allocate at runtime to store huge networks of records in memory.
If your RPG program uses native I/O, it will be bound to a service program named QRNXIO, which has relatively modest memory and CPU requirements. Prior to SQL, you might see a lot of systems supporting dozens of users, with something like 96 MB of total RAM. With SQL you'll need something like 100 times that amount of memory.
That's why I found it ironic that you would strain over 4K of static memory. It wouldn't surprise me if some of your programs were using hundreds of megabytes of additional memory, allocated at runtime.
SQL is not even native to the RPG.
I think I get your drift. RPG makes calls to the aforementioned programs to run SQL. But at least they run in the same address space as your RPG program. SQL is native in that sense. In contrast, most of the world deploys their DBMS on one server and their applications on another. The RPG-SQL interface is a lot more native than that!
Why not just let the RPG team extract the SQL statements and pass
the SQL statement to functions written by the SQL Team?
I'm with you there. I don't like the pre-compiler either. Procedure calls would be better than the dynamic calls made to the aforementioned programs. You could make your own calls to the CLI procedures; but you've probably already considered that.