• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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?

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    OCEAN User Group

    OCEAN TechCon25 Online

    It’s an Exciting Time for IBM i !

    July 16 & 17, 2025 – ONLINE

    Two virtual days of learning, presented by an outstanding group of IBM’ers and IBM Champions, featuring leading-edge topics.

    FREE for OCEAN members!

    Register NOW!

    Annual (12-month) Individual OCEAN Memberships are $80 and a Corporate Membership is $250. A Corporate Membership would allow your entire company to have full access to the OCEAN website & video library and to attend OCEAN events at member rates. Act now because rates are increasing on August 1, 2025.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    COMMON:  2016 Annual Meeting & Expo, May 15 - 18, in New Orleans! Great Power Systems event!
    System i Developer:  RPG & DB2 Summit - March 22-24 in Dallas. Check out the session grid!
    Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars

    Where In The World Is IBM i? A Possibly Coherent Future Power Hybrid System

    Leave a Reply Cancel reply

Volume 16, Number 05 -- March 1, 2016
THIS ISSUE SPONSORED BY:

WorksRight Software
Rocket Software
Northeast User Groups Conference

Table of Contents

  • Retrieving Data From All Members With SQL
  • Easy Printing From CL
  • The Three Sources Of RUNSQLSTM

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle