The Three Sources Of RUNSQLSTM
March 1, 2016 Ted Holt
The Run SQL Statements (RUNSQLSTM) command executes a file of SQL and/or CL commands. If you’ve used this command, you probably know that RUNSQLSTM reads the commands from a source physical file member. But did you know that there are two other places from which it can read SQL commands?
First, the usual way. Here’s member LOADPLANTS in source physical file SCRIPTS.
CREATE TABLE PLANTS (ID DEC(3), LOCATION VARCHAR(24), PRIMARY KEY (ID)); INSERT INTO PLANTS VALUES ( 1, 'Lost Angeles'), ( 2, 'New Yolk'), ( 3, 'Waste Virginia');
To create the PLANTS table and initialize it with data, do this:
RUNSQLSTM SRCFILE(SCRIPTS) SRCMBR(LOADPLANTS) COMMIT(*NONE)
You can also store SQL statements in the Integrated File System (IFS). Here’s how you would run the same script from stream file LoadPlants.SQL in the current directory.
RUNSQLSTM SRCSTMF('LoadPlants.SQL') COMMIT(*NONE)
Finally, you can put SQL statements in an inline data file. Here’s member PLANTLOAD in source physical file BATCHJOBS:
//BCHJOB JOB(PLANTLOAD) JOBQ(QBATCH) RUNSQLSTM SRCFILE(QINLINE) SRCMBR(QINLINE) COMMIT(*NONE) //DATA FILETYPE(*SRC) CREATE TABLE PLANTS (ID DEC(3), LOCATION VARCHAR(24), PRIMARY KEY (ID)); INSERT INTO PLANTS VALUES ( 7, 'Lost Angeles'), ( 8, 'New Yolk'), ( 9, 'Waste Virginia'); // //ENDBCHJOB
To create and load the plants table, use the Submit Database Job (SBMDBJOB) command.
SBMDBJOB FILE(SOMELIB/BATCHJOBS) MBR(PLANTLOAD)
I doubt anybody uses the inline data file method, but running SQL scripts out of the IFS has possibilities, don’t you think?