• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Create Database Files from SQL Queries

    November 15, 2006 Hey, Ted

    While using interactive SQL/400 from a green-screen session, I can direct the output of an SQL command to a database file. Is there a way to direct the output of a dynamically created SQL command to a database file from a batch job?

    –Rich

    Yes, you have a few options. First, let’s review the steps you use in your interactive session, for the benefit of the readers.

    From the interactive session, press F13 to access the Services panel. From there select option 1 (change session attributes), and enter 3 at the SELECT output prompt. Fill in file name, library, and member name, and choose option 1 to indicate that you wish to create a file. Press Enter twice to return to the Enter SQL Statements panel, then run the SQL statement. A handy feature sometimes, but not available in batch mode.

    One way to solve your challenge is to embed your select command within the Create Table command. Here’s a short RPG program–QAD1021–that I wrote to illustrate how this works. (If you try this out, create a module from this source, then bind this module to the assert module, which was previously published in this newsletter, to create a program.)

    H option(*srcstmt: *nodebugio) 
    
    D QAD1021         pr                                       
    D  inSqlCmd                    512a                        
    D QAD1021         pi                                       
    D  inSqlCmd                    512a                        
                                                               
    D/copy prototypes,assert                                   
                                                               
    C/exec sql                                                 
    C+     set option commit=*none,closqlcsr=*endmod 
    C/end-exec                                                 
                                                               
    C/free                                                     
          *inlr = *on;                                         
     /end-free                                                                
    C/exec sql                                                                
    C+                                                                        
    C+      execute immediate :inSqlCmd    
    C+                                                                        
    C/end-exec                                                                
    C/free                                                                    
         assert (SqlStt < '02000': 'Failed with status ' + Sqlstt); 
         return;                                                              
     /end-free                                                                
    

    Here’s an example Create Table command you might pass to this program:

    create table mylib/somefile as                       
      ( SELECT c.CUSNUM, c.LSTNAM, c.INIT,                 
               c.STREET, c.CITY, s.Name, c.ZIPCOD, c.baldue
          FROM qcustcdt as c                          
          LEFT JOIN states as s                      
            ON c.state = s.abbr                            
         WHERE c.baldue >= 250                             
      ) with data
    

    Voilà! You now have a new physical file on the system.

    Shops that don’t have the SQL Development Kit might prefer to use Query Management instead. Create a source physical file to hold the SQL source.

    CRTSRCPF FILE(MYLIB/QQMQRYSRC) RCDLEN(91)
    

    Use some mechanism, such as an RPG program, to write the SQL command to a member the source file. You will only need the Select statement, not Select embedded within Create Table, because Query Management can write to an outfile.

    SELECT c.CUSNUM, c.LSTNAM, c.INIT,                 
           c.STREET, c.CITY, s.Name, c.ZIPCOD, c.baldue
      FROM qcustcdt as c                          
      LEFT JOIN states as s                      
        ON c.state = s.abbr                            
     WHERE c.baldue >= 250                             
    

    Create the Query Management query. (In this example, the SQL source is in member MYQRY.)

    CRTQMQRY QMQRY(MYLIB/MYQRY) SRCFILE(MYLIB/QQMQRYSRC)
    

    Then run the query. Name the database file in the OUTFILE parameter, and be sure to specify OUTPUT(*OUTFILE).

    STRQMQRY QMQRY(MYLIB/MYQRY) OUTPUT(*OUTFILE) OUTFILE(MYLIB/SOMEFILE)
    

    If you have other, non-IBM SQL software products, you may have other ways to create database files from SQL commands.

    –Ted

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Chilli IT

    Chilli is one of the UKs leading IBM support and management providers with 20 years’ experience in the power and storage industry. Our bespoke solutions for maintenance, security and infrastructure delivers a service which is cost effective, increases productivity and enhances efficiency. Our ethical approach and unrivalled knowledge has secured business partnerships with blue-chip companies in the technology, retail, banking and travel sectors.

    As an IBM Business Partner, we provide you with the peace of mind that you are working in partnership with a company accredited to the highest standard. Our team of experts have worked together for many years and deliver projects which include consolidation, High Availability, Operating System upgrades; and backup and recovery installations.

    Contact us to see how we can help your business with IBM support and management.

    www.chilli-it.co.uk

    info@chilli–it.co.uk

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Patrick Townsend & Associates:  Alliance AES/400 - database field encryption
    New Generation Software:  Leading provider of iSeries BI and financial management software
    COMMON:  Join us at the Annual 2007 Conference & Expo, April 29 - May 3, in Anaheim, California

    Online Backups Business Treating EVault Well The State of the System i: The Analysts Speak

    Leave a Reply Cancel reply

Volume 6, Number 41 -- November 15, 2006
THIS ISSUE SPONSORED BY:

T.L. Ashford
iTera
WorksRight Software

Table of Contents

  • Create Database Files from SQL Queries
  • Stream Files and End-of-Line Delimiters, Take Three
  • Admin Alert: Three Steps to Mapping iSeries Data to a Windows Network Drive
  • Get Creative Using the SQL Database Exit Point
  • Finding Large IFS Directories
  • Admin Alert: The Rule of Fours for i5 Test Environments

Content archive

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

Recent Posts

  • POWERUp Brings IBM i Base Back Together in the Big Easy
  • New Nav for i Brings New Stuff to You
  • Why Infor’s IDF Is Important for Customer Innovation
  • Four Hundred Monitor, May 25
  • IBM i PTF Guide, Volume 24, Number 21
  • How Committed Is Big Blue To The IBM Cloud?
  • Immutable Copies Are Only As Good As Your Validation
  • Guru: IBM i *USRPRF Security
  • ERP Transitions Loom for SAP on IBM i Customers
  • Inflation Pumps Up Global IT Spending, Supply Chain Deflates It

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 © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.