Problem:

When a query containing variables is executed in SQL Wizard, the first time the query is run, SQL Wizard prompts for a value to be entered. Then this value is kept in the memory and if you want to enter a new one, you need to restart the SQL window for queries. For example: Try running something similar to the following in SQL Wizard

update tbl11aa  set program_styr_data = 'USER=****,DATABAS=:databas'  where program_ver = '000PS'  ;

When the query is executed for the first time, SQL Wizard prompts for a value in DATABAS as expected. 

If the query is executed for a second time, then SQL Wizard doesn't prompt for a new value.

If a  new value has to be specified, SQL Wizard has to be closed and reopened and then the promp will appear again.

Solution:

In the SQLWizard menu the “:variables” are known in SQLWizard as “Query Parameters”.  Query parameters are associated with the SQL Window in SQLWizard.  There is one set of parameters for each SQL Window.

If you open a new SQL Window (File ->New->SQL) and type the following for example: select * from sysibm.systables where name like :tbname;

select * from sysibm.systables where name like :another;

The first time you run the queries, you will be prompted to enter the query parameters. Those parameters are kept with that SQL window.  If you rerun the SQL, it will use the same parameters.  The values for the query parameters may be changed by using the SQLWizard menu entry “Query -> Parameters”.

Another SQL window may be opened up and it won’t have any parameters associated with it.  They can either be specified when the new SQL is run or by using the “Query->Parameters” menu entry.  This gives two possible solutions to the problem:

1. Prior to running SQL statements, use the “Query->Parameters” menu entry to validate the host variable values 2. Save the SQL script that is to be executed.  Open the script (“File->Open” menu entry) as a new window.  Parameters will be requested when it is run.  Close the window and open a new SQL Window for the next iteration.

Incident: 2573243

Product: Mainframe Express EE 3.1