Scripting - Setparm

This function names a User-Defined Parameter and sets a value for it.

• Parm. The name of the parameter.
• Value. The value of the parameter.

Example:

--aqt setparm,parm=schema,value=SYSCAT

--aqt output,file=batch_sample.csv,type=csv,filemode=append

select * from syscat.tables where tabschema ='$schema';

Setting a parameter to a result of a query

You can set the parm to a value returned by a query. Example:

--aqt setparm,parm=yesterday,value=(select current date - 1 day from sysibm.sysdummy1)

If the query returns multiple rows/columns, only the first column of the first row will be used.

Setting a parameter to a value returned by a prior query or stored procedure

You can set a parameter to a value returned by a query, or to an OUT parameter from a stored procedure. This can be useful if you are passing data between a series of SQL statements.

Coding value=$result(3) will set the parm to the 3rd column of the last query run. If the query returned more than one row, the value will come from the last row. If there was no previous query, or the query returned zero rows, the results can be unpredictable.

Coding value=$parm(2) will set the parm to 2nd query parameter (as specified with a Parm function). You would typically use this when the Parm function has specified an Out or Inout parameter, and you have run a stored procedure.

You can embed these functions inside other characters. Example: value=EMP$result(1)00 will return EMPSMITH00 if $result(1) is SMITH.

Setting a parameter to an Environment Variable

You can set a parameter to an environment variable by coding:

value=$env(CUSTCODE)

In this example, CUSTCODE is an environment variable. When running batch-mode AQT, you can set this in the batch file that invokes AQT.

Setting a parameter to a System value

You can set a parameter Value to one of the following system values.