<i>Is there a better way to do this or does the AS400 lack the required flexibility?</i>
That’s a tricky question. There are many possibly alternatives between “better way” and “lack the required flexibility”.
For dynamic database access, you need to code dynamic access routines. In general, that’s done the same way on AS/400s as it’s done elsewhere. The result (as always) is some pretty difficult code.
Four potential ways you might go:<ol>
<li>The <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/apis/database2.htm”>Call Level Interface (CLI) APIs</a> allow you do just about anything that you could do with ODBC. This includes dynamic SQL statements and dynamic parameters. If you’ve done ODBC programming, most of those should be quickly familiar.</li><li>The <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/apis/database3.htm”>Extended Dynamic Remote SQL (EDRS) APIs</a> (probably with the <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/apis/qxdaextd.htm”>Process Extended Dynamic SQL (QSQPRCED) API</a>) let you do some fancier things, but few need more than ODBC.</li><li>REXX is always available for dynamic SQL processing. It’s fundamentally dynamic anyway. Creating variables on the fly is standard stuff for REXX. The SQL is free.</li><li>The <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/books/sc415607.pdf”>ILE C run-time library functions (PDF)</a> include just about anything you’d ever need. The functions can be called from any ILE language, including the memory allocation/deallocation and other functions that you’re going to use for your dynamic work.</li>
And there are all of the oldies-but-goodies that can be built around a generic program-described file plus OVRDBF LVLCHK(*NO), possibly combined with a dynamically generated OPNQRYF. And Query Manager queries can be dynamically constructed as well. And… there are other possibilities.
Many of those put you out into risky territory. You necessarily bypass some safeguards that the integrated nature of DB2, i5/OS and the language compilers provides to developers.
But it’s certainly there if you want it.