Frequently I want to execute series of sql script, say for deploying an application into a new database, from a windows client. On Linux, the Nzsql the command line utility can be used to submit sqls to Netezza, but this is not available as a compiled windows executable. Workbench in command line mode comes to the rescue here.
Lets say I have setup some sql scripts in a structure like this.
To setup this application I need to deploy some tables, create some sequences, setup some views, and populate some initial seed data. I’d like to run the contents of each sql script in a sequence. I can invoke netezza workbench in unattended mode with the flag exec to run any command like this:
Aginity.NetezzaWorkbench.exe --unattended --action exec --sql "select count(1);" --connstr %ConnectionString% --stdout %OutFile% --stderr %ErrFile% --stdappend --ignoreparams true
Even easier, this command can be built from inside of workbench using the Command Line Builder to set all of the options. (Tools > Workbench Command Line Builder > New)
Here you can build each command explicitly. Then save the statement as a CMD file for scheduling or copy the command itself to the clipboard.
You can set many values here in this builder:
SQL Parameters - Assign Parameters values to inputs from the command line
Execute as Block - Multiple statements in the SQL file can be executed as a block
Output File / Error File - Specify the output and error files as appendable to keep a running log
Output formatting - Specify the formatting of the output files
I like to test and then save these copied commands in single batch file that can be executed manually or by scheduler, here’s an example of my DeployApp.bat. I modified the command string a bit to take command line parameters for ServerName, UserName, Password and DatabaseName
This way we can easily script repeatable tasks using Aginity Workbench!
Written by Dan Gasperut, Aginity Architect