DB2 Server for VSE & VM: Interactive SQL Guide and Reference

STORE

>>-STORE--stored_statement_name---+---------+------------------>< '-REPLACE-'

STORE is an ISQL command that causes the current SQL (only) statement to be saved for later use. SQL statements remain stored until erased. When storing an SQL statement, you must associate a name with it. Use this name to recall the SQL statement when you want to execute it. The SQL statement, when stored, also remains as the current SQL statement.

Stored statements can be started, listed, renamed, erased, or recalled. When a stored statement is started, renamed, or recalled, it becomes the current SQL statement.

stored_statement_name

is the name you want to use to refer to the stored statement. Names can be up to 8 characters long. The name PREVIOUS is not allowed because ISQL always stores the current statement under that name when a new SQL statement is typed.

REPLACE

specifies that the current statement is to replace any existing stored SQL statement with the same name. If REPLACE is not specified and there is an existing stored SQL statement with the same name, a warning message is issued. This message gives you three options to select from:

• REPLACE, to replace the existing stored SQL statement with the current SQL statement
• END, to end the processing of the STORE command.
• Type a different name to be used as the name for the current SQL statement being stored.

When you store a SELECT statement, related display formatting information is also stored. Formatting is defined by FORMAT commands or current DB2 Server for VSE & VM formatting defaults.

This information, which cannot be "seen", remains stored with the SELECT statement and formats the display when the statement is recalled and executed. However, the following exceptions exist:

• Formatting information is not saved when SELECT statements are stored that contain placeholders in the SELECT or FROM clauses. Formatting information is saved when the placeholders occur in clauses other than the SELECT and FROM clauses.
• If a stored SELECT command is changed by a CHANGE command, formatting information is saved only when the change occurs in the WHERE, GROUP BY, ORDER BY, or HAVING clauses (that is, when the change does not occur in the SELECT or FROM clauses).
• If a table referenced by a stored query is dropped and later created with different column characteristics (such as DECIMAL instead of INTEGER), existing formatting information is not used.

The notation SELECT * FROM table_name is not recommended for use in stored SELECT statements. It is possible that someone can add a new column to the table, which causes any stored format information for the SELECT statement to be erased. By explicitly naming the columns required, or by referring to a view instead of a table (the view can have SELECT *), you can avoid this problem.

The steps necessary to store FORMAT command information along with an SQL statement are:

1. Execute a SELECT statement to retrieve data.
2. Type one or more FORMAT commands to format the display.
3. Type an END command to end the display.
4. Type a STORE command to store the statement (which is still the current SQL statement) and any formatting information.