Db2 Tools for z/OS

 View Only

Using QMF query results to generate executable scripts.

By shawn sullivan posted Thu September 17, 2020 02:47 PM

  

QMF: Generating  Executable Statements from QMF Query Results
Recently a request came in from a customer asking if QMF could generate SQL Insert Statements from a query result set. The process detailed here can accomplish this. It can also be used to generate several other types of executable statements.

1) Run a query getting your results:

figure 1

2) Generate a new Form using the menu Results> Display Report

figure 2

3) Choose Classic Report

figure 3

4) Edit the report using the Design tab at the bottom

Figure 4

5) Choose the Options panel, and Uncheck the box for “Column heading separator”

figure 5

 

6) Choose the Detail1 panel and

a) set “Put Tabular data at line” to NONE
b) Uncheck “Include column headings with detail headings”
figure 6

Now click on the Report Tab and you should have an almost blank report

figure 7

If you scroll to the bottom you will see Page number and the date. We will take care of that at the end.


 

7) Now put in the Executable statement using Form Variables.  There is a Form Variable &n where n is the column number of the column you want to pull data from. Here is the syntax for creating SQL INSERT STATEMENTS

Note that in this case column 2 and column 4 are character columns and will require single quotes. They can be supplied by the QMF Global DSQQW_SQ

figure 8

Note that line 3 ends with a SEMI COLON and I have added a Line 4 with the “Add Block Text”  button so I can put a COMMIT after each statement.

8) Now the report looks like this:

figure 9

 

9) To deal with the Page numbers and dates, use the menu File>Page Setup

Uncheck the boxes for Include date and time and Include page numbers” and change the Page length and Page width to “Continuous”

figure10

Click OK and save the Form.

10 ) Finally make a QMF Proc like this:

figure 11

When Run it will populate the X.STAFF TABLE that is now empty

figure 12

 

NOTES:

A) This process is not limited to generating SQL statements. It could be used to generate QMF Procedures to run queries then export the results and use a WinZip command line to compress them, CLIST, FTP commands to move an exported PDF to a server, and so on.
B) This process does not detect internal quotes as in the NAME O’BRIEN
C) The IMPORT QUERY and RUN QUERY lines are optional. You may take generated text and run it in another interface.
D) The VRY extension on the EXPORTed report is specific to running the generated query in QMF.
E) To deal with NULL values in QMF for Workstation, there is a small problem in the All NULL values appear as a hyphen -    in the Form regardless of the setting in the View>Preferences>Appearance. This will require a post processing action of REPLACE ALL changing the string     ,-   to ,NULL and the string ,’-‘ to NULL.
F) If the table to be populated is NOT in the same Database as the source table the PROC will need a CONNECT TO command between the IMPORT QUERY and the RUN QUERY commands.
G) Some users prefer to create a simpler Global for the Single quote like this
figure 12

Then the Form.Detail simplifies to the above.

 




#Db2Toolsforz/OS
#Db2QMF
#QMF
0 comments
20 views

Permalink