How do I programmatically run a complex query on an as400?

Question

I'm new at working on an as400 and I have a query the joins across 4 tables. The query itself is fine, it runs in STRSQL and displays the results.

What I am in struggling with is getting the query to be able to run programmatically (it will eventually be run from a scheduled CL script).

I tried have creating a physical file that contains the query running it with RUNQRY, but it simply displays the query itself, not the actual result set.

Does anyone know what I am doing wrong?

---

UPDATE

Thanks everyone for the direction and the resources, with them I was able to reach my goal. In case it helps anyone, this is what I ended up doing (all of this was done in it's own library, ALLOCATE):

1. Created a source physical file (using CRTSRCPF): QSQLSRC, and created a member named SQLLEAGSEA, with the type of TXT, that contains the SQL statement.

2. Created another source physical file: QCLSRC, and created a member named POPLEAGSEA, with the type of CLP, that changes the current library to ALLOCATE then runs the query using RUNSQLSTM (more detail on this below). Here is the actual command:

   RUNSQLSTM SRCFILE(QSQLSRC) SRCMBR(SQLLEAGSEA) COMMIT(*NONE) NAMING(*SYS)

3. Added the CLP to the scheduled jobs (using ADDJOBSCDE), running the following command:

CALL PGM(ALLOCATE/POPLEAGSEA)

With regard to RUNSQLSTM, my research indicated that I wasn't going to be able to use this function, because it didn't support SELECT statements. What I didn't indicate in my question was what I needed to do with the the result - I was going to be inserting the resultant data into another table (had I done that I'm sure the help could have figured that out a lot quicker). So effectively, I wasn't going to be doing an SELECT, my end result is actually an INSERT. So my SQL statement (in SQLLEAGSEA) begins with:

INSERT INTO ALLOCATE/LEAGSEAS

SELECT ... BLAH BLAH BLAH ...

From my research, I gather that RUNSQLSTM doesn't support SELECT because it doesn't have a mechanism to do anything with the results. Once I stopped taking baby steps and realized I needed to SELECT AND INSERT in the same statement, it solved my main problem.

Thanks again everyone!

Answer 1

The command is RUNSQLSTM to run a static SQL statement in a physical file member or stream file.

It is a non-interactive command so it will not execute sql statements that attempt to return a result set.

If you want more control, including the ability to run interactive statements, see the Qshell db2 utility.

For example:

QSH CMD('db2 -f /QSYS.LIB/MYLIB.LIB/MYSRCFILE.FILE/MYSQL.MBR')

Note that the db2 utility only accepts the *SQL naming convention.