• 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
    Rocket Software

    Disrupt Without Disruption

    For over 35 years, Rocket Software’s solutions have empowered businesses to modernize their infrastructure, unlock data value, and drive transformation – all while ensuring modernization without disruption.

    Learn How

    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

  • To Comfort The Afflicted And Afflict The Comfortable
  • How FalconStor Is Reinventing Itself, And Why IBM Noticed
  • Guru: When Procedure Driven RPG Really Works
  • Vendors Fill In The Gaps With IBM’s New MFA Solution
  • IBM i PTF Guide, Volume 27, Number 27
  • 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

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