• 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
    Raz-Lee Security

    Start your Road to Zero Trust!

    Firewall Network security, controlling Exit Points, Open DB’s and SSH. Rule Wizards and graphical BI.

    Request Demo

    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

  • Positive News From The Kyndryl Mainframe Modernization Report
  • NAViGATE, inPower 2025 On Tap for September 2025
  • Guru: WCA4i And Granite – Because You’ve Got Bigger Things To Build
  • As I See It: Digital Coup
  • IBM i PTF Guide, Volume 27, Number 37
  • AI Is Coming for ERP. How Will IBM i Respond?
  • The Power And Storage Price Wiggling Continues – Again
  • LaserVault Adds Multi-Path Support To ViTL
  • As I See It: Spacing Out
  • IBM i PTF Guide, Volume 27, Numbers 34, 35, And 36

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