.Cleanup Statement--Embed SQL Statements that Clean Up
The .cleanup statement embeds SQL statements that do not involve data retrieval into Report-Writer sections. Report-Writer executes the statements after the main report query is processed.
The .cleanup statement has the following format:
.cleanup SQL_statement; {SQL_statement;}
The parameters for the .cleanup statement are:
SQL_statement
One or more action SQL statements that do not involve data retrieval, separated by semicolons (;).
Note: The select statement cannot be used.
For a complete explanation of all available statements, see the SQL Reference Guide.
Description
Use the optional .cleanup statement to embed groups of SQL statements that perform cleanup tasks such as dropping temporary tables created in the .setup section. Report-Writer executes the .cleanup section after it substitutes Report-Writer variables into the SQL statements. For information on embedding groups of SQL statements before the report is processed, see the .Setup statement.
Use as many lines as you need to specify the .cleanup. You can also include embedded declared variables within an SQL statement in the .cleanup section. The end of .cleanup is detected by the start of a new Report-Writer statement or an end‑of‑file indicator.
The following rules apply to the .cleanup section:
• The .cleanup section only supports SQL statements. The language of your .query section determines the query language of the report. If you have a QUEL .query section, the query language is QUEL; otherwise it is SQL. You can have a QUEL query and SQL .setup and .cleanup sections.
• If the report query language is SQL, the default value of autocommit is off. If it is QUEL, the default is on. To override the default commit behavior, set autocommit off or on as the first statement in your .setup section.
• Only statements that are compatible with execute immediate are permitted in a .cleanup section. For a list of compatible statements, see the SQL Reference Guide. Neither the select statement nor any statement requiring embedded semicolons (;) or colons (:), such as create procedure, are allowed. Semicolons within quoted strings are allowed. Therefore, you can specify a table for selection by using the expression:
create table tablename as select...
• Report-Writer evaluates variables in the .cleanup section only once, before running the report. Therefore, you can set the value of the variable only at report runtime, as follows:
– On the command line in a variablename=valuestring clause
– On the command line in response to a prompt
– In the with value or with prompt clause of a .declare statement associated with the variable
While actually executing the .cleanup section, Report-Writer does not recognize any changes that have been made to .cleanup section variables while the report was being run.
• In the .cleanup section, Report-Writer evaluates variables before sending them to the Database Management System (DBMS) and evaluates SQL statements at report runtime. It generates error messages at runtime from the DBMS. If it does not detect any errors in the report or in the .cleanup and .setup sections, and if autocommit is off, Report-Writer executes an explicit commit at the close of report processing.
• Use the ‑d flag with the report command to run the report as if there were no errors. This flag causes DBMS errors in the .setup and .cleanup section to be ignored. The failure of the .setup statement, however, can affect data availability in the .query. For example, if you run the report on a temporary table created improperly in the .setup section, the report fails.
• When you specify the ‑d flag, error messages continues to display on the screen. Although it is not recommended, you can also run the report with the ‑s flag if you do not want error messages to display.
• If you do not use the ‑d flag, errors and transaction handling follow these rules:
– If Report-Writer detects an error in the .cleanup section, the .setup section (if it exists) and the report have already run. The transaction is rolled back if autocommit is off.
– If Report-Writer detects an error in the .setup section, neither the report nor the .cleanup section runs. The transaction is rolled back if autocommit is off.
– If Report-Writer detects an error in the query, or if there is a fatal error in the report, neither the .cleanup section nor the report runs. The transaction is rolled back if autocommit is off.
Examples
Last modified date: 11/09/2022