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.
ON TABLE HOLD AS script_name FORMAT SQL_SCRIPT
where:
Is the name of the .sql file or the .ftm file created as a result of the HOLD FORMAT SQL_SCRIPT command.
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, $
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 |