Batch Processing With Workbench!

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


Screen Shot 2014-06-12 at 6.46.19 PM.png

This way we can easily script repeatable tasks using Aginity Workbench!

Written by Dan Gasperut, Aginity Architect

Have more questions? Submit a request


  • 0


    I use this command for creating series of procedures:

    Aginity.NetezzaWorkbench.exe --unattended --action execblock --sqlfile "C:\TEST.sql" --connstr %ConnectionString% --dbtype NetezzaODBC --stdappend --stdout %OutFile% --stderr %ErrFile%


    1) all operators between BEGIN_PROC and END_PROC created without newline characters as single line, how to fix this?

    (new line characters in my sql file - (CR)(LF))

    2) default encoding in my files - ANSI, national character in created procedures looks like  ��������

    is it possible to change encoding to UTF-8 by some properties?

  • 0
    Eli Weine

    The above issue was solved via an Aginity support ticket, but I would like to post the solution below in case anyone else faces similar issues.

    We checked the format of the procedures after they are created via command line. Unfortunately we can do nothing regarding this. There is no way to preserve the sproc/view formatting because the nz drivers remove it (as well as comments).

    As workaround you can open your sql files directly in workbench and execute them there. The only thing that you need to do before execution is switch the query window to "SP / Function" mode. To do this, right-click on the Query Analyzer window, select Options, and then change the Query Kind to SP/Function. After that you can execute the query via F5. All Create/Update queries that you have in the active query window will be executed, so there is no need in CTRL+F5 command.

    As always - make sure you have the most up-to-date version of Aginity Workbench which can be download from our website here:

    Edited by Eli Weine
  • 0
    Roman Kazmin


    About your second question.There is a tool In workbench called "Text File Converter". You can call it via Tools menu -> File Utilites -> Re-code a text file. This tool allows to convert file from one encoding to another.

    I hope it helps.

  • 0

    I encountered a difficulty when running workbench from the command line with a batch file.  A typical call to a batch file would be:

    C:> CALL Example.bat > Example.log 2>&1

    Note the output redirection; " > Example.log" sends any output on stdout to the log file Example.log; " 2>&1" appends stderr to stdout so that error messages also make it to Example.log.

    The issue I found is that when running my batch file this way, each call to workbench within the batch file sends a newline to the command prompt.  If I leave out the " 2>&1", the extra newline is not sent.  This is extremely problematic because this extra newline is received by any active application; for example, if I start the batch file and then go edit a document while it runs, eventually the extra newlines are sent, and they go directly to the document being edited.

    If this is something that can be fixed in a later release of workbench that would be great.  If there's a workaround or a cause not controlled by workbench or if anyone else has experience this issue, I'd be very interested to hear about it.

  • 0
    Sanjeevi Vadlamudi

    Hi ,

    To execute SQL command (--unattended mode) from command prompt, are there any prerequisites needs to be done in local machine ?

    When I was trying to execute step by step as suggested above , no output/error files generated and in fact nothing is happening.

    Could someone please help me in this ?

    C:\Program Files (x86)\Aginity\Aginity Workbench for Netezza(x64)>set Outfile=Documents\asd1.txt

    C:\Program Files (x86)\Aginity\Aginity Workbench for Netezza(x64)>set ErrFile=Documents\asd2.txt

    C:\Program Files (x86)\Aginity\Aginity Workbench for Netezza(x64)>set ConnectionString="Driver={NetezzaSQL};server=<serveraddress>;UserName=<username>;Password=<password>;Database=<database>;Query Timeout=120"

    C:\Program Files (x86)\Aginity\Aginity Workbench for Netezza(x64)>Aginity.NetezzaWorkbench.exe --unattended --action exec --sql "select count(1);" --connstr %ConnectionString% --stdout %OutFile% --stderr %ErrFile% --stdappend --ignoreparams true (After pressing enter button it's prompting for the next command).

    C:\Program Files (x86)\Aginity\Aginity Workbench for Netezza(x64)>


  • 0


    I am using Aginity for connecting to Redshift. My objective is to run a bunch of SQLs on a daily basis and get an email notification upon completion of runs

    Using Workbench command line builder, I am able to build a batch .CMD file with my required.sql file. No issues there. In the task notification, I have set up email SMTP server and was able to test if a test email can be sent. All good till this point

    However, when I set up email options to Send email "when jobs finishes(error or success)", I could save the option but when I run the job and even after successful job completion, I am not getting an email. The email option is defaulting to Send email " when Job fails" . I opened the .awbcmd file and saved the option I want but it again default to Send email"When job fails". 

    Any idea how to set the email option to send email when job finishes. I am using aginity version ( for redshift) and 4.1.1136.21080 ( for netezza)

    Note: I have tested the scenario of email when the job actually fails and it works perfectly fine. No issue with aginity talking to my email server, but it just does not save the email option I want

    By default below option is saved


    I want below option to be saved and reflected so that I can get an email when the job actually is finished running( either success or failure) 

    Appreciate if anyone of you faced this issue and if you have any solution



Please sign in to leave a comment.

** Aginity, Inc.’s Provision of Scripts and Similar Materials at Help Desk Center. For the convenience of Aginity Amp™ clients, we provide code snippets, scripts and similar materials at this Help Desk Center. Such materials are reference materials provided for illustration purposes only. These are intended to serve as an example for self-service clients and are generally geared to respond to common questions asked by similar clients. Such materials constitute Aginity’s intellectual property. Aginity Amp clients and their authorized users are permitted to use these materials in connection with their software license and/or subscription of Aginity Amp. Nothing herein shall limit Aginity’s right to use, develop, enhance, modify or market any of these materials as part of its business. These materials are not formally supported by Aginity or its affiliates. Usage of these materials does not guarantee any specific results, uptime, performance or error-free operation. Aginity disclaims all warranties of any kind, whether express, implied, statutory or otherwise, including any implied warranty of merchantability or fitness for a particular purpose.

Powered by Zendesk