vdmiter - A Swiss Army Knife

The vdmiter command is a simple mechanism for iterating over the rows of a manifest and macro-substituting values in a template script or an in-line SQL query. Here is an overview of the command parameters: vdmiter -m manifestfile -t templatefile -vx SQLtext

The Manifest File

The command iterates over each row of the manifest file, and executes a SQL script or statement after substituting variables by the row's values. A manifest file name can be passed with this option. If no manifest is passed, the default manifest is the value of environment variable MANIFESTFILE. You can see the value using the command "echo $MANIFESTFILE" , or export a file name using: export MANIFESTFILE=yourfilename. The first line of the manifest file contains the names to be used to form variables or structured comments for substitution. More on this here.

The Template File

One way of telling vdmiter what SQL command sequence to execute, is to put one or more commands in a file and pass its name as a parameter. The contents of the file must be a well formed SQL script with certain syntactical elements represented as "variables" and certain others as "structured comments". The script is executed once for each row of the manifest, after substituting variables with values from the row and exposing commented lines by removing matching structured comments.

SQL Text

If only one command is to be executed, then it can be passed after all options on the command line. The command may contain variables that will be substituted as they would in a template.

The Setup Script

Sometimes we want certain actions to take place before the iteration starts. The Setup Script, for example, may contain the creation and population of temporary tables.

DB2 options

The following DB2 options can be used:

-z DB2LOGFILE

Directs SQL output to file named DB2LOGFILE

-x

Suppresses headers of queries

-v

Verbose - repeats each executed SQL command

Debug Mode

Option -D, when set, suppresses the execution of the script, and instead generates a command stream that can be captured and stored for later execution, or used to inspect what SQL statements result after the substitutions.