Creating a Subquery or Sequential File With HOLD FORMAT SQL_SCRIPT

How to:

When used in a request against a relational data source, the HOLD FORMAT SQL_SCRIPT command generates the SQL SELECT statement needed to execute the current query and stores it in an application as a file with a .sql extension on UNIX and Windows or in the SQL component of the application data sets on z/OS, along with the Master (.mas on UNIX and Windows, and MASTER.DATA on z/OS) and Access File (.acx on UNIX and Windows, and ACCESS.DATA on z/OS) pair that describes the SQL answer set. If you do not have an application defined, the script will be placed in a temporary file on z/OS.

When used in a request against any other type of data source, the HOLD FORMAT SQL_SCRIPT command executes the current query and stores the retrieved values in an application as a sequential file with an .ftm extension on UNIX or a .FTM low-level qualifier on z/OS, along with the Master File that describes the retrieved data. The data can also be stored in any sequential file on z/OS by allocating the AS name to a file.

You can use the output from HOLD FORMAT SQL_SCRIPT as the target file for the DB_INFILE function.

Note: Once you have the .sql file and its accompanying Master File, you can customize the .sql file using global Dialogue Manager variables. You must declare these global variables in the Master File. For information about parameterizing Master Files with global variables, see the Describing Data manual.

For information about configuring and working with applications, see the Developing Applications manual.


Top of page

x
Syntax: How to Create an SQL Script or Sequential File Using HOLD FORMAT SQL_SCRIPT
ON TABLE HOLD AS script_name FORMAT SQL_SCRIPT

where:

script_name

Is the name of the .sql file or the .ftm file created as a result of the HOLD FORMAT SQL_SCRIPT command.



Example: Creating an SQL Script File Using HOLD FORMAT SQL_SCRIPT

The following request creates an SQL Script file in the baseapp application.

APP HOLD baseapp
TABLE FILE WFLITE
SUM BUSINESS_SUB_REGION                                     
STATE_PROV_CODE_ISO_3166_2                                  
BY BUSINESS_SUB_REGION NOPRINT                              
BY STATE_PROV_CODE_ISO_3166_2 NOPRINT                       
WHERE BUSINESS_SUB_REGION EQ 'Midwest' OR 'East'            
WHERE STATE_PROV_CODE_ISO_3166_2                            
  EQ 'AR' OR 'IA' OR 'KS' OR 'KY' OR 'WY' OR 'CT' OR 'MA' OR
  'NJ' OR 'NY' OR 'RI'                                      
WHERE COUNTRY_NAME EQ 'United States'                       
ON TABLE HOLD AS RETAILS FORMAT SQL_SCRIPT
END

The result of this request is a script file named retails.sql on distributed systems and member RETAILS in the BASEAPP.SQL.DATA library on z/OS, and a corresponding Master and Access File.

The script file contains the following SQL SELECT statement:

SELECT   MAX(T3."BUSINESS_SUB_REGION") AS "E01", 
 MAX(T3."STATE_PROV_CODE_ISO_3166_2") AS "E02"  
 FROM  USER1.w_wf_retail_geography T3 
 WHERE(T3."COUNTRY_NAME" = 'United States') AND 
 (T3."STATE_PROV_CODE_ISO_3166_2" IN('AR', 'IA', 'KS', 'KY', 'WY', 'CT',
 'MA', 'NJ', 'NY', 'RI')) AND
 (T3."BUSINESS_SUB_REGION" IN('Midwest', 'East')) 
 GROUP BY  T3."BUSINESS_SUB_REGION",
 T3."STATE_PROV_CODE_ISO_3166_2"

The Master File follows:

FILENAME=RETAILS, SUFFIX=DB2     , $                                     
  SEGMENT=RETAILS, SEGTYPE=S0, $                                         
    FIELDNAME=BUSINESS_SUB_REGION, ALIAS=E01, USAGE=A25V, ACTUAL=A25V,   
      MISSING=ON,                                                        
      TITLE='Customer,Business,Sub Region', $                            
    FIELDNAME=STATE_PROV_CODE_ISO_3166_2, ALIAS=E02, USAGE=A5V, ACTUAL=A5
      MISSING=ON,                                                        
      TITLE='Customer,State,Province,ISO-3166-2,Code', $                

The Access File follows:

 SEGNAME=RETAILS,      
  CONNECTION=CON1,    
  DATASET=RETAILS.SQL,
  SUBQUERY=Y, $       


Example: Creating a Sequential File Using HOLD FORMAT SQL_SCRIPT

The following request against the EMPLOYEE data source creates a sequential file containing the values retrieved by the request along with a corresponding Master File:

APP HOLD baseapp 
TABLE FILE EMPLOYEE 
PRINT LAST_NAME FIRST_NAME 
WHERE DEPARTMENT EQ 'MIS' 
ON TABLE HOLD AS EMPVALUE FORMAT SQL_SCRIPT 
END

The sequential file empvalue.ftm contains the following data:

SMITH           MARY        JONES           DIANE       MCCOY           JOHN        BLACKWOOD       ROSEMARIE   GREENSPAN       MARY        CROSS           BARBARA     

The empvalue.mas Master File follows:

FILENAME=EMPVALUE, SUFFIX=FIX     , IOTYPE=BINARY, $ 
  SEGMENT=EMPVALUE, SEGTYPE=S0, $ 
    FIELDNAME=LAST_NAME, ALIAS=E01, USAGE=A15, ACTUAL=A16, $ 
    FIELDNAME=FIRST_NAME, ALIAS=E02, USAGE=A10, ACTUAL=A12, $

Information Builders