SQL RPGLE – How to override data set data structure at run time

20 pts.
Tags:
iSeries
RPGLE
SELECT statement
SQL
SQL Select statement problem
SQLRPGLE
I currently have an SQL RPGLE routine that receives a SELECT statement as a parameter and then fetches the data set into a data structure based on the file.

This only works over a single file as the datastructure is created at compile time and has to be based on filea.

I would like to make this routine flexible enough to run the SELECT statement over any file on our system.

The only options I can see at the moment are to either:

a) create a version of this routine for every file on our system (so that each program has the correct externally described data structure)

b) change the existing routine to contain an external data structure for every file on the system.

Both these solutions seem somewhat inelegant and inflexible - if we create a new file then it means either creating a new program or modding a program.

Is there a better way to do this or does the AS400 lack the required flexibility?

Answer Wiki

Thanks. We'll let you know when a new response is added.

<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>
</ol>
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.

Tom

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DanTheDane
    Just a hint: study SQL DESCRIPTOR. DanF
    2,555 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following