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?
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.