Create Database Files from SQL Queries

Published: 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                                                                

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