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.

 

image1.png

 

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)

Image2.png

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

5 Comments

  • 0
    Avatar
    Elena

    Hi,

    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%

    but 

    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
    Avatar
    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: 

    https://www.aginity.com/workbench/

    Edited by Eli Weine
  • 0
    Avatar
    Roman Kazmin

    Elena, 

    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
    Avatar
    Matt

    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
    Avatar
    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)>

     

Please sign in to leave a comment.
Powered by Zendesk