Home

AGINITY WORKBENCH FOR NETEZZA FUNCTIONALITY OVERVIEW

1. SOL ES 75 12 3 1 Constructing a command line in Command Line 77 12 3 2 Tracing a SQL Statement in Workbench Command Line 80 13 55 8 P NT 82 Copyright 2013 Aginity LLC All Rights Reserved No part of this user manual may be reproduced transmitted transcribed stored in a retrieval system or translated into any language in any form or means electronic mechanical photocopying scanning recording or otherwise without prior permission in writing from Aginity LLC CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO aginity 1 Overview Aginity Workbench is an easy to use application that enhances your performance when you are working with your data warehouse The application provides a powerful set of GUI based tools for Developers DBAs and Data Analysts to maximize the productivity Of users 1n these roles This SQL database development tool offers unique capabilities that allow you to focus on MPP databases It provides an intuitive user interface for creating managing and tracking both individual SQL queties and entire database schemas Created by and for Aginity s own software developers Aginity Workbench is based on our profound knowledge of the functionality an advanced data warehouse appliance is expected to
2. CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 36 aginity Analyze Database Wizard 2 Analyze Database Wizard step 3 of 3 Host RV QR E Database SYSTEM PK uniquiness Yes UK uniquiness Yes FK integrity Yes bad skew Yes FK data type Yes Need to reclaim Yes Min skew 1 Ignore tables smaller than 10 megabytes Ihe 4 table s to be analyzed are ADMIN FZZLDUMMY ADMIN FZZLANCOVASTATS ADMIN FZZLENV ADMIN FZZLKMEANSCENTROID Click Finish button to start the process now To start the analysis procedure click Finish When the procedure is complete the result is displayed in the Database Analysis window The window displays a table that provides a summary of the processed data and SQL queries used This information is displayed at the bottom of the table CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 37 Et Severi SQL Validation Rule Stat in STC GUTER S _ AKOSHCH The numberof deleted rec 0 00 SET show delete Needtoreclaim 3 13 2012 12 59 3 13 2012 1259 0 0 SET show_deleted_records 1 select CAST count as BIGINT as TotalRecords ee X 0 1 DeletedRecords e Analyze Database Data Performs analysis of the contents of the data table to suggest improvements related to the nullability of the fields that allow nulls minimi
3. m 39 select 123 union select test parameter 1 2147467259 ERROR error in test paramete ProcessStepiD ProcessID FK b Statement RowsAffected 1 ErrorCode int 1 21 ErrorMessage v StartDateTime T EndDateTime c NEN CONFIDENTIAL AND PROPRIETARY AGINITY LLC DISTRIBUTE WITHOUT PRIOR AUTHORIZATION DO NOT 66 aginity 9 Results Menu The Results menu is used to manage the Result Set section more quickly by using keyboard shortcuts to delete all result sets and to display the Result Sets pane Results Object Tools 3 Clear Go to Toggle Ctri R The Results menu consists of the three following options e Clear Closes and removes all the tabs from the result set e Go to Allows you to switch between multiple tabs in the Result Set including the Output tab Up to 9 tabs are supported e Toggle Allows you to manage the visibility of the Result Set section thus also maximizing the size of SOL Editor CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO aginity 10 View Menu The View menu is mainly used to change the way objects look in the Object Browser It provides a variety of options for viewing tables and sorting databases You can also create your own entity filters to choose from and view a user s and server s query history to access more detailed user and server related information View Query Results
4. Get Drivers To establish a DB connection 1 In the corresponding boxes specify the values of the following parameters e Server e User ID e Password 2 To specify the database to connect to in the Database drop down list select or type the name of the appropriate database CONFIDENTIAL AND ROPRI P RIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIO E R AUTHORIZATION aginity 3 If you want to change the port in the Port combo box select or enter the appropriate port 4 save the provided configuration click Save Once saved a connection entry can be selected from the Saved drop down list Connection entries are stored in an encrypted form and can be decrypted by the currently logged on Windows user only For security reasons you can choose not to save your password with your connection string In this case you need to enter your password every time a connection to the database needs to be established The following other capabilities are also available e New Creates a new connection entry e Delete Deletes a saved connection entry e Rename Renames an existing connection entry e Copy Provides access to a shortcut menu with the following functions o Duplicate Connection Entry Creates a copy of the current connection entry o Connection String to Clipboard Copies the connection string for the current connection entry to the clipboard O Create an Entry from Connection String in C
5. Ready Pos 24 row l col25 admin 1 query 0 069 sec 26 rows Count4 Min 24 48 CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 15 aginity 4 File Menu The File menu consists of standard type file management options and allows you to access the main functionality provided by the application View Query Results Connect New Query Window Ctrl N Open Query Set Recent Query Sets Save Query Set Save Query Set As Open SQL File Ctrl Recent SQL Files Save Query 1 5 Save Query As New DDL File Open DDL File Recent DDL Files New UDX Open UDX file Recent UDXs Open Query Plan file Print Send Recent Query Plans Open SPU Utilization Info file Recent SPU Utilization Infos Exit Alt F4 The following is a list of the functions that you can access from the File menu e Connect Allows you to connect to another database and or server A new Query Analyzer window will be displayed for each new connection You can switch between several windows by using CTRL TAB shortcut or the Window menu CONFIDENTIAL AND ROP P RI DISTRIBUTE WITHOUT PRIO ETARY AGINITY LLC DO NOT R AUTHORIZATION 16 aginity e New Query Window Creates a new connection instance for the same database or server A new Query Analyzer window is displayed for the new connection e Open Query Set Opens a set of queries using a specified path and displays them in the Que
6. aginity AGINITY WORKBENCH FOR NETEZZA FUNCTIONALITY OVERVIEW CONFIDENTIAL AND DISTRIBUTE WITHOU 4 0 0 JJ o J aginity CONTENTS eve 2 Database Connection Dialog BOX cccccccseccseccsecseccseceseceuccseceneceuceseceseeeuctseceeeeeeeeeeceseeeeeteeeeeeeeeetes 3 Main Window of Aginity Workbench 4 1 04 4 4 mene menn nennen nnns 10 225 10 12 25221 Adding New Tab the SQL 12 3 2 2 Accessing SQL Statement Management 5 12 3 2 3 Inserting Object Column Names into the SQL 13 33 Result 58654554 84880084140804048448688445540484884888641443888885814081880446858 8848464 4466044464 4234 13 3 3 1 Managing Grid esses et eese 14 3 3 2 Exporting the Query Execution 0 1 15 24 15 Mm 15 P 16 5 19 ODE NE M 0000004 0 0 510909009 0008008004 24 6 1 Functions Provided by the Object 24 0 2 Viewing Database 30 7 1008
7. not expected to know Tracing SQL Statements INSERT INTO dbo LogProcess BatchID Description StartD VALUES batchid cmd line test GETDATE select SCOPE IDENTITY Recent This SQL is executed before the first script statement of the script 7 Chck OK The following is an example of executing a SQL query with tracing Microsoft SOL Database CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION File Edit View Query Project Debug Tools Window Help Pdl 4 24 3 3951215 D Er c p Debug orsi TARNE Object Explorer OP O st TT select from logprocessstep Columns 100 ProcessiD 8 1 BatchiD bigint C Resuts 1 Messages 2 Description ProcessiID BatchlD Description Start Datetime EndDate Time EsrorCode ErorMessage 2 StartDatetime 1 39 567 emdine test 2013 02 13 14 09 55 397 2013 02 13 14 09 56 500 2147467259 ERROR pg error in test EndDateTime ErrorCode int 21 ErrorMessage v Keys amp Constraints Triggers 22 Indexes T Statistics ProcessStepID ProcessID Statement RowsAffected ErorMessage 21 dbo LogProcessStep 66_ nl 39 select test parameter 1 0 Columns
8. region RegionName 2 Select from TableName2 limit 100 endregion endregion Note The RegionName text quoted in the above example is optional Aginity Workbench also supports nested regions as illustrated in the folloning example region RegionName 1 Select from TableName Re jionName 2 endregion The vertical line in the left side of the SQL Editor window uses a square with a minus sign to mark the start of each collapsible code region When you click the minus sign the text within the code region is replaced with a box that contains the region s name or the ellipsis sign if the region name was not specified The minus sign is then changed to the plus sign When you click a plus sign the collapsed code is expanded and the plus sign is changed to a minus sion If you point to the box with the ellipses sign or to a region s name a tooltip is displayed that shows the code contained in the collapsed region When Collapse is clicked a region is compacted and the region s name 15 displayed CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 23 aginity 6 Object Menu The Object menu allows you to access the various commands associated with functions that are used to manage objects To perform an operation on an object you must select this object in the Object Browser click Object on the main toolbar of the program select the appropriate object type on the
9. Unique Constraints lt lt Unique Constrarts TEST_IMP_CSV3 missing 3 Owner Ater Sonpt 0 Owner Columns lt lt Columns Defintion 1 CREATE TABLE TABLE 1 CREATE TABLE TABLE 1 MEMBER KEY BIGINT NULL 4 MEMBER KEY BIGINT NULL CUSTOMER FIRST NAME CHARACTER VARYING 255 CUSTOMER FIRST NAME CHARACTER VARYING 255 CUSTOMER COUNTRY CHARACTER VARYING 255 CUSTOMER COUNTRY CHARACTER VARYING 25S5 CUSTOMER CITY CHARACTER VARYING 255 CUSTOMER CITY CHARACTER VARYING 25S5 CUSTOMER CATALOG CUSTOMER NUMBER CHARACTER VYARYING 255 CUSTOMER INTERNET ROLLING YEAR SEGMENT CHARACTER VARYING 255 If the Preserve Tables content check box is selected the delta script is automatically modified to retain the table data in the database being modified e Analyze Database Schema Use this command to perform various types of data validation including those related to the uniqueness of data its referential integrity bad table distribution and so on When the Analyze Database Schema command 15 clicked the Analyze Database wizard 15 displayed that consists of the three following steps 1 Selecting one or more entities to wotk with 2 Performing the following types of data validation O Foreign Key Data Integrity Checks whether the child tables contain any orphan tecotds that reference non existent parent records O Foreign Key Data Type Checks whether the data types of the primary and foreign keys ate the same CONFIDENTI
10. AND PROPR DISTRIBUTE WITHOUT PRIO aginity 2 Database Connection Dialog Box When launched Aginity Workbench automatically displays the database connection dialog box You can also start establishing a new connection by clicking Connect on the application s toolbar You can simultaneously get connected to several database servers For details refer to Main Window of Aginity Workbench Note You need to have Netezza drivers ODBC and or OleDb installed on your computer to be able to connect to the database Use the LBM Fix Central web site http 007 93 3 2 support fexcentral for finding and downloading the IBM Netezza drivers that are suitable for your processor architecture 32 or 64 bit Also note that each IBM client accessing the Fix Central is required to have an individual IBM ID CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO 2 Connect to Netezza 28 aginity Aginity helps companies compete on analytics by building end to end Big Data solutions we call Data Factories We are the company that is behind Netezza s Data Factory We are experts in architecting and delivering complete data solutions that leverage the extreme capabilities of the Netezza DW Appliance which is why we wanted to arm our team with a powerful workbench Visit Aginity com for more information Connection Properties Server User ID Password 1 Save password P L 9 5480 5 Netezza OleDb
11. File Go to Tree Go to Query Goto Query Tab Next Query Tab Prev Query Tab Go to Results Word Wrap Current Query Options de Snippet F5 CIA DEMO 3 1 2 Ctrl T Alt T Alt Q Ctrl F6 Ctrl Shift F6 Ctri W The Execute with Trace capability uniquely enables you to log data related to SQL query execution independently of the main transaction This is achieved by employing an external transaction that 1s logged to a database other than the one to which the main transaction is stored This second database can be the Netezza database or some other external database You can select from several database options This approach enables you to perform logging of your executed SQL queries at all times even if the execution has failed or the main transaction has been rolled back You can trace the SOL query execution process using a set of 4 SOL scripts Two of these scripts are responsible for reporting on the process and result o e entire script s execution whereas ponsible for reporting on the p d result of the entire SQL script tion wh the other two are responsible for reporting on the individual SQL statements execution They are CONFIDENTIAL AND P DISTRIBUTE WITHOUT Script Prolog Performs execution before the first SQL statement in the main SQL script Script Epilog Performs execution after the last SQL statement is executed Statement Prolog Performs execution before each indivi
12. OK and then verify the name that is now displayed in the list Note You can also use regular expressions in the Database Name box For instance type A to apply a rule that starts with A to all the databases The following is an example of a rule created for a System database to have a red background in the drop down list and a green background in the main section of the Query Analyzer CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 49 amp aginity Query Results Object Tools Window Help 0 CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION Jp Ti Tab2 x E T3 x New 50 8 Query Menu aginity The Query menu provides several additional features that facilitate working with the Query Analyzer SQL Editor Query Results Object Tools Execute F5 Explain F8 Select Query at Cursor F12 Complete Code Snippet Alt C Execute All Shift F5 Execute as Single Batch Ctrl F5 Execute in Selected Databases Execute with SPU Utilization monitor Execute to File Execute with Trace Add New SQL Editor Ctrl T Go to Tree Alt T Go to Query Alt Q Goto Query Tab Next Query Tab Ctrl F6 Prev Query Tab Ctrl Shift F6 Go to Results Alt R Word Wrap Ctri Alt W Current Query Options Window The following is a list of the functions that you can access using the Query menu CONFIDENTIAL AND Execute Executes
13. and alter table distribution e Secure Shell SSH interface to server Gives you quick access to the shell commands of the appliance s operating system for performing more advanced DBA operations e Space Use Diagnostics Allows you to analyze space utilization across the entire server in a way that clearly identifies where and how space is used thus making it easy to address capacity issues e Server Query History Analysis Allows you to quickly view query history over extended periods of time to identify slowly running queries You can quickly perform a detailed analysis of the query plans and processing distribution e Grooming and Organizing Tools Tools that allow you to groom tables Query Output Grid Allows you to manage and analyze data more quickly by providing sorting column relocation pinning aggregation grouping and charting functions You can quickly analyze complex result sets directly in the query results set without having to export the data to other tools such as Excel e Profile Column Allows you to analyze a column s data type profile as well as check its ability to show value distribution graphically e Integrated Charting Provides the ability to visualize the various aspects of a result set graphically e Integrity Analysis Allows you to use packaged rules to analyze a table or an entire database for identifying duplicate keys relational violations data type issues and so on CONFIDENTIAL
14. display an area that provides general information on the database associated with it The information includes allocated space used space the percentage of used space in the allocated space number of rows skew and the created date CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO 31 Edt Toots Window f Connect Refresh cures Columns 8 Crested 2012 02 27 1357 49 Ready Total Blocks 57 552 Total Bytes 7 543 455 744 Used Blocks 39495 Used Bytes 5 176 688 640 CONFIDENTIAL AND PROPRIETARY AGINITY LLC DISTRIBUTE WITHOUT PRIOR AUTHORIZATION DO NOT 32 aginity 7 Tools Menu The Tools menu allows you to access a variety of capabilities offered by Aginity Workbench for Netezza application Tools Window Plug ins Sql Dump Runner Reverse Engineer Database Compare Schemas Analyze Database Schema Analyze Database Data Migrate DDL Groom Database SSH Terminal File Utilities Import gt Options The following is a list of all the commands that you can access using the Tools menu SQL Dump Runner Use this command to open a SQL file and run each of the queries in the Debug mode In contrast with the Query Analyzer that uses a new connection for every SQL execution the SQL Dump Runner keeps an open connection to database thus enabling you to see the content of intermediate temporary tables created by the script e Reverse Engineer Database U
15. displayed menu and then click the required command Note The same set of commands provided by the Object menu can also be accessed by right clicking any object in the Object Browser and then selecting the required function from the displayed menu The commands are available only if they are relevant to the selected object 6 1 Functions Provided by the Object Menu Functions Specific to the Server Object Type e Show Disk Usage Displays disk usage information as a grid SPU ID Total MB Used MB Disk Usage and so on e Open SSH terminal Displays a window that allows connecting to the SSH terminal e Refresh server information Updates the status of all servers in the Object Browser Functions Specific to the Database Object Type e Refresh DB list Updates the list of all databases in the Object Browser Comment on Database Adds a comment for the selected database CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 24 aginity e Script Database Allows publishing a SQL command file that contains all the necessary commands for completely rebuilding the database structure You can also specify the objects that must be included the script Show User Sessions Displays the Sesszon List window that shows all those users who are connected to the selected server The Session List window provides an option for terminating selected sessions e Show Query History Displays a server side history fo
16. following e To specify the type of the database to be used in the DB type drop down list select the appropriate database type e To specify the database type automatically click Autodetect 4 If you want to test the connection click Test 5 Do the following a Inthe Script Start tab of the Tracing SQL Statements area of the window enter the SQL statement to perform tracing at the beginning of the SQL script or select the corresponding SQL file Note If you want to enter a SOL statement by pasting 2 into the tab keep the SOL script option selected To select a file instead select the SOL file option This is applicable for all the 4 auxiliary tracing scripts b In the Script End tab of the Tracing SQL Statements area of the window enter the SOL statement to perform tracing at the end of the SQL script or select the corresponding SQL file c Inthe Statement Start tab of the Tracing SQL Statements area of the window enter the SQL statement to perform tracing at the beginning of the individual SQL statements within the script or select the corresponding SQL file d Inthe Statement End tab of the Tracing SQL Statements area of the window enter the SQL statement to perform tracing at the end of the individual SQL statements within the script or select the corresponding SQL file CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 64 Connection string Data Source ocal User ID sa Password you
17. has returned several result sets the result tabs are added for those queries that had been executed before the failed execution took place CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 13 aginity Output Result 1 Standard Text Grid 25 Session ID 43514 Start time 08 09 2011 09 29 21 end time 08 09 2011 09 29 21 Duration 0 0410024 sec Records Affected 3 select from test 3 3 1 Managing Grid View If you do not want the Text and Grid sub tabs to be displayed in the Output tab click Tools select Options gt Result set options Output options and then clear the Enable Text and Grid Output check boxes The Result Set allows you to drag a column from the Results tab and perform grouping by this column To do this drag the column that you want to group by to the drag a column header here area The additional functions can be accessed by right clicking in the table and then selecting Actions for the column Name it is possible to create the Pareto and Frequency Distribution Charts The Result Set also allows you to edit the data output format You can set the table columns to be re sized automatically The following options are available e Column header e All cells except header e cells e Displayed cells except header e Displayed cells You can also set rows to be re sized automatically The following options are available CONFIDENTIAL AND ROP P R
18. must be logged in to be able post News Under development e Show Log File Shows a system log file that contains all executed commands and errors that have occurred if any System Info Displays system info CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO 83
19. or numbered bookmarks in the open SQL Editor window It is possible to create up to ten numbered bookmarks and an unlimited number of unnumbered anonymous bookmarks Bookmarks gt Toggle Bookmark B Advanced gt Goto Next Bookmark Ctri Alt Right Outlining Goto Previous Bookmark Ctrl Alt Left Enable Disable Bookmark Clear Bookmarks Numbered gt For example you can place Bookmark 1 at the beginning of a complicated SQL statement to which you want to be able to gain quick access and Bookmark 2 at your current editing location After this to quickly reach the SOL section you need to press Ctrl Alt 1 You can return to your second bookmark location by pressing Ctrl Alt 2 set numbered bookmark or to go to a bookmark that you have set previously you can use keyboard shortcuts or the Edit menu To set a numbered bookmark using a keyboard shortcut press Shift Alt marker bookmark number gt For example to set a CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 20 aginity Bookmark 1 press Ctrl Shift Alt 1 go to a numbered bookmark using a keyboard shortcut press Ctrl Alt lt marker bookmark number gt For example to go to a Bookmark 1 press Ctrlt Alt 1 To set an unnumbered bookmark or to delete a bookmark that you are pointing to press F9 To go to the next or previously set bookmark press Ctrl Alt Right or Ctrl Alt Left respectively Note Some gra
20. port number as specified in the main connection string WB_SESSION_DBUSERNAME The name of the user running the main script WB_SESSION_DBNAME The database name the main script is executed in WB SESSION WINHOSTNAME The host name of the machine where Workbench is running ROP INITY ZATION 62 aginity WB SESSION WINUSERDOMAIN The domain of the user logged on to the Windows workstation where the Workbench is running WB_SESSION_WINUSERNAME The user name of the user logged on to the Windows wotkstation where the Workbench is running 4 amp Q SWB ERROR MESSAGE Q SWB ROWS AFFECTED Q SWB SCRIPT PROLOG RETURN VALUE Q SWB SESSION DBHOSTNAME Q SWB SESSION DBNAME Q SWB SESSION DBPORT Q SWB SESSION DBUSERNAME SWB SESSION ID Q SWB SESSION PROVIDER gt To execute a SQL query with tracing 1 Click Queries on the Menu bar and then select Execute with trace Execute with Trace Tracing SQL Statements Script Start Script End Statement Start Statement End SQL Script SQL File This SQL is executed before the first script statement of the script CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION aginity 2 In the Connection string box of the displayed Execute with Trace window select or enter the connection string for the server on which the tracing result must be processed 3 Do one of the
21. specify whether the UDX must be saved before compiling Of not Database Highlighting Allows you to use different background colors for the different connections and databases For details on this functionality refer to Using Database Highlighting Diff Tool Allows finding out the differences between two database schemas or between two versions of the same file one of which has been modified externally For details on this functionality refer to Using a 3 rd Party File Diff Tool in Workbench Aginity Workbench supports the following file diff tools that are most wide spread on the market Araxis Super Merge Beyond Compare WinMerge CodeCompare ExamDiff and Guiffy 7 1 Importing Data to the Database It is possible to upload data to the database using Aginity Workbench This function 1s provided by the Tools menu CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO 4 4 aginity Tools Window Plug ins Help Sql Dump Runner 5 18 Reverse Engineer Database Compare Schemas ERE s Analyze Database Schema M Analyze Database Data Migrate DDL Groom Database SSH Terminal File Utilities From Excel Options From a CSV File From a Fixed Column Widths File From Multiple CSV Files From External Database The Workbench supports source file import for the following formats e Microsoft Office Excel both 2003 and 2007 e Comma Separated Values CSV e Fixed Colum
22. still allowed to override the data type for the automatically assigned parameters manually e now The type of the parameter is automatically specified as date and time and its value is set to the current default time value in the dd mm yy hh mm ss AM PM format e If the name of a parameter contains time related text for example test Tze Timetest test zzetest the type of the parameter is automatically specified as date and time and the value of the parameter is set to the current default time value in the dd mm yy hh mm ss AM PM format e If the date of the parameter contains date related text for example date testdate test Dafetest the text of the parameter is automatically specified as date and the value of the parameter is set to the current default date in the dd mm yy 12 00 00 AM format e Ifthe name of a parameter contains today related text for example today test Today test I odaytest the type of the parameter is automatically specified as date and the value of the parameter is set to the current default date in the dd mm yy 12 00 00 AM format e Ifthe name of the parameter ends into the type of the parameter is automatically specified as Bigint and its value is set to 0 After the changes are made the parameter values the SOL query contains are replaced with the values that you have specified and the query is executed CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 59 aginit
23. the exec err file in a temp folder The command timeout is 600 seconds The result sets returned by the queries will be written to the standard output file using a Tab character as a column separator and nix like line endings A single quote char will be used for quoting values that require quotes Either sql or sqlfile should be specified See above Use r n t to specify the CR LF and Tab characters respectively See above CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 76 aginity 12 3 1 Constructing a command line in Command Line Builder To create a command line to the application 1 On the Menu bar click Tools and then select Workbench Command Line Builder gt New 2 In the Command type drop down list in the displayed Command type dialog box select the required command Note Currently only the Execute SOL command is available New Workbench Command Line File x Command type Executes a SQL script in a specified database 3 On the Menu bar click Tools and then select Workbench Command Line New 4 In the Connection string box of the displayed Workbench Command Line Builder window specify the connection string for the server to be used Note To specify the connection string correctly click the ellipsis button next to the box select the appropriate database and cick Connect CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DIS
24. underscores Add any valid statement into the box and then click OK The new snippet will be added to the list of existing snippets in alphabetical order After the snippet is created and saved type the name of the snippet in the Query Analyzer window select it and press ALT C The selected text will automatically be transformed into a pre defined snippet To delete a snippet select it and then click Delete A confirmation dialog box will be displayed If you want to proceed with deleting the snippet click Yes in the confirmation dialog box The following shortcuts can be used to add delete or modify a snippet e To add a new snippet press Insert or ALT A CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 54 aginity e To delete a snippet press Delete or ALT D e To modify a snippet press ALT M or double click the snippet e Execute all Executes the one or more selected queries or all existing queries if no query has yet been selected in the open SQL Editor window e Execute as Single Batch Executes selected text as a single batch without splitting it into any semicolon separated individual SQL statements Executes all the text that is displayed in the open SQL Editor if no part of this text is selected The whole of the statement batch 15 submitted to the database for processing e Execute in Selected Databases Executes the current statement or selected s
25. 1511 015 55466 5 o o T E 33 7 1 Importing Data to the Database 44 7 2 Using a 3 rd Party File Diff Tool in 11 1 nnn 47 25 0 1 0 10 E EFI MU IUE 49 2 40536420 42651631 640144144100 18418 155 44644244014441040404416 216 0364020 0244 41040404410 416 41260436 02440244044 51 8 1 Using Parameter 500501 0111 2562 368445542524 45485 5538 8 56 CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION aginity 8 2 Executing SQL Queries with Tracing The Execute with Trace 61 RESUITS 67 10 68 TI OV TNL NA DIE 73 12 Workbench Command Line 74 12 1 Command Line 74 12 2 Specifying an ACTION cccscccsssccusscsssssccusscsssssccussosssssccussoustsccussosssseeussousnasceussoussaseeuscousneseuune 75
26. AL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 35 aginity Need to reclaim Checks whether the percentage of deleted records in a table exceeds the defined threshold value Primary Key Uniqueness Checks whether there are any duplicate records that violate the Primary Key constraint o Table Distribution Checks whether the table skew exceeds the defined threshold value o Unique Key Uniqueness Checks whether there are any duplicate records that violate the Unique Key constraint Note Additionally you can adjust the bad skew and deleted records thresholds using the combo boxes at the bottom of the second page of the wizard You can also specify the size of the tables for which the bad skew should be ignored Analyze Database Wizard Analyze Database Wizard step 2 of 3 Summary Check type Foreign Key data integrity Checks there are orphan records in child Foreign Key data type Checks if the data type of a primary key an V Need to reclaim Checks if the per cent of deleled records in V Primary Key uniqueness Checks if there are duplicate records that v V Table distribution Checks if the table skew is not greater thar V Unique Key uniqueness Checks if there are duplicate records that v Bad skew threshold 1 00 2 Ignore bad skew fortables smallerthan 10 Deleted records threshold 10 3 The wizatd displays a summaty of the selections made during the previous two steps
27. DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 75 aginity The standard output and error will be appended if this argument is stdappend No specified at the command line timeout No The command timeout for SC L statements execution in seconds For SQL statements that return a result set this is a column separator colsep No to be used when you save the result set to a standard output file For SQL statements that return a result set this is a record separator recsep to be used when saving the result set to a standard output file For SQL statements that return a result set this is a character to be quote No used as a quote char when saving the result set to a standard output file Instructs Workbench to turn parameters off useful for execution ipgnorepatams stored procedure DDL scripts The following is an example of a command line unattended action exec sql select current timestamp select 123 connstr Provider NZOLEDB Data Source my server com User D admin Password Initial Catalog my_db stdout c temp exec txt stderr c temp exec err stdappend timeout 600 colsep t recsep n quote The above command line instructs the Workbench to connect to the my_db database on the my server com and execute two SQL statements one by one The results of the execution are to be appended to the exec txt file in a temp folder The errors if any are to be appended to
28. ENTIAL AND ROP P RI DISTRIBUTE WITHOUT PRIO ETARY AGINITY LLC DO NOT R AUTHORIZATION 10 aginity Note You can manage the visibility of the Object Browser Press CI RL B to hide or display the object tree e File Edit View Query Results Object Tools Window Help MX Connect 25 Execute Explain Abort Database SYSTEM 22 Databases 7 Tab 1 New 9 0 Server select 1 Security Output Result 1 Drag a column header here to group by that column COLUMN Live 1 By right clicking a database table server or another object you can access functions specific to the corresponding object type Note The Object menu on the Menu bar provides the same functions as the shortcut menu available by left clicking a tree node The following are some of the available functions by object type Database e Script Database Allows publishing a SQL file that contains all the commands that are necessary for building the entire database structure of a selected database You can also specify the objects that must be included in the script as well as include table contents for the selected tables e Show Query History Displays a server side query history e Analyze DB Schema Allows performing several types of data validation including the uniqueness of primary unique keys referential integrity and bad table distribution For details refer to Tools Menu Analyze DB Dat
29. Edit Comment Allows adding and editing the description of a selected procedure CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 27 aginity Functions Specific to the External Table Object type e Refresh external table list Updates the list of external tables in the selected database e Script Generates SELECT statements INSERT statements or DDL commands inserting them into the Query Editor ot copying them to the clipboard e Refresh Columns Updates the selected table s columns Functions Specific to Materialized Views e Refresh materialized view list Updates the list of views in the selected database e Script Generates a SELECT statement or DDL commands inserting them into the SQL Editor or adding them to the clipboatd e Data Review Executes a SELECT script to retrieve all existing duplicate records or the top 100 duplicate records e Refresh columns Updates the selected table s columns Functions Specific to User Defined Functions e Refresh UDF list Updates the list of user defined functions in the selected database e Script Generates a SELECT statement inserting it into the Query Editor or copying it to the clipboard e New Allows creating a user defined scalar function Fill in the Function Properties form and then click OK CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 28 aginity e New UDTF Allows creating a new user defined table va
30. F3 Replace Ctri H Goto Line Ctrl G Bookmarks Advanced Outlining The following 15 a list of the functions that can be accessed from the Edit menu e Undo Reverses the latest change made in the SQL Editor e Redo Restores the result of an action that you have cancelled in the SQL Editor e Cut Allows you to clip out a selected text in the SQL Editor CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 19 aginity e Copy Copies text selected in the SQL Editor or a cell range selected in the Result Set grid depending on which part of the window has the keyboard focus The command copies selected grid data with or without the headers depending on the setting that can be adjusted to your preferences in Tools Options e Copy with Headers Allows you to copy selected rows including the row headers from the result set in the Set window e Paste Allows you to paste text from the clipboard into the SQL Editor e Find Displays the standard Find window for the SQL Editor e Find Again Allows you to perform a search in the SQL Editor using the most recently used search criteria without requesting you to provide any more data e Replace Displays the standard Find and Replace window for the SQL Editor e Goto Line Displays the Goto Line window for the SQL Editor where you can specify the text line to which the pointer must be moved Bookmarks Allows you to set anonymous
31. INITY DISTRIBUTE WITHOUT PRIO ZATION 14 aginity e All cells e Displayed cells Note You can display a full screen view of the result set To do this press and bold CTRL and then double click any of the result set s headers lo return to the regular view mode Press Cancel 3 3 2 Exporting the Query Execution Result You can export query execution results to the Excel 2003 XML or CSV formats To do this right click anywhere in the Result tab and then select Export You can also open the content of the result set in Microsoft Excel without creating an intermediate disk file 3 4 Menu Bar and Toolbar The Menu bar provides access to several menus that are used to manage the bulk of the system s functionality The Toolbar is located under the Menu bar and provides access to the most frequently used options Aginity Workbench for Netezza Query Analyzer 1 Untitled m Fille Edit View Query Results Object Tools Window Plug ins Help Menu bar Connect gt g Execute Explain Abort Database SYSTEM Tool bar 9 3 5 Status Bar The Status bar is located under the Result Set section It shows helpful status information If you select more than 1 row in the result set the Status bar can show the minimum maximum average and other data To make this feature available click the drop down arrow in the right side of the Status bar and select the options that you want to be displayed
32. LC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 39 aginity e Replace whitespaces with underscores e DDL Generator Wizard Migrate Database Wizard step 2 of 4 DDL Generation Options Char case Generate UPPER CASE identifiers v Convert Pascal notation to underscores Replace whitespaces with underscores Sample SourceColumnName gt SOURCE COLUMN 3 This step is specific to the source database system for example it may be suggested that the HIERARCHY data type that exists in the Microsoft SQL Server be converted to parent child columns The Reverse Engineering Completed step 4 of 4 page of the wizard displays warnings if any generated during the procedure 4 To start generating the DLL on the Reverse Engineering Completed step 4 of 4 page of the wizatd click Finish CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 40 q DDL Generator Wizard Migrate Database Wizard step 4 of 4 Reverse Engineering Completed The Workbench has reverse engineered target database Please review messages generated during this process below if any Click Finish button to begin generating the DDL Warning Sales Store Demographics data type xml is not supported the target system and will be scripted as VARCHAR 16000 Warning Production ProductPhoto ThumbNailPhoto data type varbinary is not supported the target sys
33. Menu bar click Object and then select Database gt Show Tables Size File Edit View Query Results Object Tools W Connect gt Execute Explain Abort Datat Databases 83 41 1 Server Refresh Databases Security Copy Database List to Clipboard Show User Sessions Show Query History Show Tables Size View gt Note You can also display the Table Space window by right clicking the database name and then clicking Show Tables Size The Table Space window has three tabs Summary Details by Table and Details by Data Slice The Summary tab consists of three additional sub tabs Grid displayed by default Chart shows the allocated number of bytes and Chart shows the number of bytes used You can switch between the sub tabs by clicking the name of the required sub tab It is possible to specify for which tables the chart must be created The following options are available e Smart Attempts to optimize the number of the displayed largest entities so that the chart does not look over cluttered with a lot of small tables CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 30 aginity e Top 10 largest tables e Top 20 largest tables e All tables In addition you can set the component the chart must be based on by selecting it from the View drop down list The following options are available e Bubbles e Rectangular e Ring By pointing to a component you can
34. OUNTRY CHARACTER VARYING 255 CUSTOMER CITY CHARACTER VARYING 255 CUSTOMER CITY CHARACTER VARYING 255 CUSTOMER CATALOG CUSTOMER NUMBER CHARACTER VARYING 255 CUSTOMER INTERNET ROLLING YEAR SEGMENT CHARACTER VARYING 255 CUSTOMER INTERNET ROLLING YEAR SEGMENT CHARACTER VARYING 255 CUSTOMER PERC NUMERIC 18 5 CUSTOMER PERC NUMERIC 18 5 CUSTOMER MAXIMUM TRIPS SUB DEPARTMENT CHARACTER VARYING 255 CUSTOMER MAXIMUM PURCHASES CATEGORY CHARACTER VARYING 255 CUSTOMER TOTAL SALES DELI NUMERIC 18 5 CUSTOMER MAXIMUM TRIPS SUB DEPARTMENT CHARACTER VARYING 255 CUSTOMER SPENT PERC OTHER STORES NUMERIC 18 5 CUSTOMER MAXIMUM TRIPS DEPARTMENT CHARACTER VARYING 255 CUSTOMER SALES AMOUNT ON INTERNET NUMERIC 18 5 Datrbuted On D Dutrbuted On Organized On Organized On 9 Primary Key 5 Primary Key Foreign Keys Foreign Keys Unique Constraints Unique Constrarts Description Description A demensional table for CUSTOMER category A dimensional table for CUSTOMER category TEST IMP CSV 3 aisting 9 Owner Owner Columns Columns Distributed On Distributed On Organized On Organized On Primary Key Primary Key Foreign Keys Foreign Keys Unique Constraints Unique Constraints 3 TEST IMP 5 2 missing 9 Owner Owner Columns Columns Distnibuted On Distributed On Organized On Compare Organized On Primary Key Primary Foreign Keys Ater Foreign Keys
35. Objet Quoted Identifiers Entity Schema Group by Schema Sort Columns Alphabetically Entity Filter User Query History Alt H Server Query History Toggle Object Browser Ctrl B The following 15 a list of functions that can be accessed from the View menu e Quoted Identifiers Parenthesizes each identifier databases tables and so e Entity Schema Displays objects entity schema in the Object Browser e Group by Schema Groups objects by entity schema in the Object Browser e Sort Columns Alphabetically Displays object columns alphabetically in the Object Browser as opposed to the physical column order defined by the schema e Entity Filter Allows you to manage the existing entity filters and create new ones Those objects that do not meet the filtering criteria are not displayed in the Object Browser tree CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 68 aginity To create an entity filter 1 On the Menu bar click View and then select Entity Filter gt Manage Filters 2 In the displayed Manage Schema Filters window click New Manage Schema Filters a lt SCHEMA gt STARTS WITH quarter nocase 3 In the Filter Name box in the displayed Schema Filter Properties window type the name of the entity filter being created 4 In the Logical Operation drop down list select a logical operand The following options are available e Or e And 5 To specify
36. PRIOR AUTHORIZATION 74 aginity where arg1 is a simple indicator type argument and arg2 is a name value type argument where arg2value that is following the arg2 switch contains the argument s value The names of command line arguments can be prefixed by a or specified without any prefix Argument values are accepted as are parenthesized or not 12 2 Specifying an Action An action command prompt argument is used to specify what action must be performed by Aginity Workbench The following is an example of an argument that instructs the application to execute a query Example unattended action exec sqlfile C script1 sql 12 3 Executing SQL Statements The following command prompt parameters are used to execute an SQL statement Execute a set of semi colon delimited SC L statements one by one action Yes equivalent to pressing Shift F5 in the Query Analyzer Execute a SQL statement as a block equivalent to pressing Ctrl F5 in action Yes the Query Analyzer sql No Specifies the SQL statement to execute sqlfile Specifics the name of the file that contains SOL statements to execute The connection string to the destination database where the SOL connstt Yes statements are to be executed stdout No Specifies the name of the file for the standard output stderr No Specifics the name of the file for the standard error CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT
37. TRIBUTE WITHOUT PRIOR AUTHORIZATION aginity 2 Workbench Command Line Builder belok Command Type Execute SQL Description New Execute SQL Command Properties General SQL Parameters Tracing Final SQL Connection Details amp SQL Connection string rovider NZOLEDB Data Sourcessim User ID admin Password password Initial Catalog2 NPS test m DB ype SQL file C test_query sal v m Command timeout 0 sec l Execute as Block v Output and Error Targets Output file c stdout bd m m Error file Output Formatting Column separator 9 224 ded 5 Do one of the following e To specify the type of the database to be used in the DB type drop down list select the appropriate database type To specify the database type automatically click Autodetect CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO aginity 6 Inthe Command timeout combo box in the Connection Details amp SQL area of the window specify the time out in seconds for the execution of the selected SQL script 7 If you want the selected SQL script to be executed as a single batch and not as a series of individual SQL statements select the Execute as a block check box 8 Inthe Output File drop down list box in the Output and Error Targets area do one of the following e If you want an existing output file to be used select the file to which the execution result must be writt
38. ZLKMEANSCLUSTERID FZZLKMEANSEUCLDIST FZZLKMEANSMINMAX m Definition 1 Definition 2 CREATE DATABASE ADMIN SYSTEM CREATE DATABASE ADMIN TEST TOOLKIT COMMENT ON DATABASE ADMIN SYSIEM IS syste 4 TT em The Compare Schemas capability also allows you to synchronize schemas while optionally retaining or discarding the data contained in the tables that need to be altered To synchronize two schemas click Alter Script A file that contains SOL scripts that reconcile any existing differences between the schemas is created You can also generate a delta script to update a schema to be an exact copy of the other schema To do this choose the schema that you want to update and then click one of the Alter Script buttons The Generate Delta Script Options window is displayed CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 34 aginity 2 Aginity Workbench for Netezza 83 File View Tools Window Help 9 Connect Compare Schemas Generate Delta Script Schema 1 2 Open d Save As 2 al Save As Source Database Source Database Host Database lt 3 2 LEGE TABLE 1 5 Owner Owner Columns Columns MEMBER KEY BIGINT NOT NULL MEMBER KEY BIGINT NOT NULL CUSTOMER FIRST NAME CHARACTER VARYING 255 CUSTOMER FIRST NAME CHARACTER VARYING 255 CUSTOMER COUNTRY CHARACTER VARYING 255 CUSTOMER C
39. a Performs analysis of the table contents to suggest improvements on the nullability of those fields that allow nulls minimization of the row size by using minimum precision for columns and minimization of the character columns length CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 11 aginity Server e Show Disk Usage Displays disk usage information as a grid SPU ID Total MB Used MB Disk Usage and so on e Open SSH terminal Displays a window that allows connecting to the SSH terminal e Refresh server information Updates the status of all servers in the Object Browser 3 2 SQL Editor The section in the upper right of the Overy Analyzer window is used for editing SQL scripts 3 2 1 Adding a New Tab in the SQL Editor To add a new tab to write a SQL query in click New tab You can also add a new tab by pressing CTRL T if the Qzery Analyzer window is currently open To switch between several tabs in the active Query Analyzer window press CTRL F6 and CTRL SHIFT F6 Tab 1 New breate database TestDabase create table TestTable 3 2 2 Accessing SQL Statement Management Functions CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 12 aginity By right clicking anywhere in the SQL Editor you can access a number of useful additional features and SQL query management options For example you can override the row limit for the result set use syntax highlighting and
40. ct Tools Window Plug ins Connect 25 O Execute Explain Abort Database Ej A Im CIF mer Ate 3 98 Databases 160 E Ta1 x cwr sTR LOC DIM 20130201 x E CUSTOMER Base Load Tab8 FR Tab10 88 QA group 3 5 95 Uncategorized 157 1 endregion tdi i endregion SSSSSSSSSSSSSSSSSSSSSSSSSSSSSESSSSSSSSSSSESSSSSSSSSSSSSS SSS SSS SS SSS SSS SSS SSS SSS SSSSSSSSSSSSSSSSSSESSSESSSSSSSSSSS region COMMIT COMMIT SQL Editor endregion Object Browser region SCRIPT SECIION After commit finalization region step 58 Groom not arrived yet member table f Resut 14 X Resut 15 2 X Resut 16 gt Output Resut 1 9 Resut2 2 Resuk 3 33 x Resut 4 x Resut 5 9 X Resut6 x Resut Drag a column header here to group by that column UT DROP TABLE IF EXISTS 1 0 Result Set 3 1 Object Browser The left section of the Query Analyzer window is called the Object Browser It allows you to browse databases and their child entities and view information that includes the databases size number of entities they include and so on CONFID
41. d just start typing the text to make a search for it The number of records in the history is defined in the application settings and it 1s available in Tools Options CONFIDENTIAL AND P INITY DISTRIBUTE WITHOUT ZATION 71 aginity To adjust the number of user queries stored on the current workstation 1 To set this number on the Menu bar click Tools and then select Options 2 Inthe tree in the left pane of the displayed Application Options dialog box select Query Analyzer 3 Inthe Keep number user queries in history combo box select the appropriate number of queries e Server Query History Displays a history of recently executed queries on the server The data is stored on the server You can filter the data by the following parameters Database User How many rows to show the duration of the query and date range The data can then be sorted based on the selected parameters Note This functionality does not use a Query History database if it was configured for the database host you are connected to It uses a system default history of the most recent queries up to 2000 by default that can be adjusted in the database system configuration e Toggle Object Browser Allows you to manage the visibility of the Object Browser CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO 72 aginity 11 Window Menu The Window menu allows you to switch between several open windows more quickly If you hav
42. dual SQL statement within a SQL batch INITY ZATION 61 aginity o Statement Epilog Performs execution after each individual SQL statement within a SQL batch Tracing allows you to identify the reasons for a SQL script having failed by re executing a batch Besides it can provide you with some additional information that would otherwise be unavailable for example the number of text lines in a specific SQL query In addition the feature can be very useful in profiling scripts that are run on a daily basis and analyzing the daily execution results in terms of the rows affected by a statement There are 6 Workbench parameters used in auxiliary SQL statements employed for tracing purposes CONFIDENTIAL AND P R DISTRIBUTE WITHOUT PRIO WB_ERROR_CODE The error code of the most recently executed SQL script WB_ERROR_MESSAGE The text of the error of the most recently executed main SQL script WB_ROWS_AFFECTED The number of lines returned by an executed SQL statement that is part of the main SQL script WB_PROLOG_RETURN_VALUE The value returned by an executed start script WB_SQL_TEXT The text of the most recently executed SQL statement WB STATEMENT PROLOG RETURN VALUE The value returned by the start SQL statement script WB_SESSION_ID The session ID of the main script connection WB_SESSION_DBHOSTNAME The host name or IP address as specified in the main connection string WB_SESSION_DBPORT The
43. e Edit View Query Results Object Tools Window Plug ins mx m Connect 2 G Execute Explain A 54 Dump Runner 2 Forum Search z CSP Binning Charts gt CIF 21 Reverse Engineer Database h 5 248 DEMO 3 12 2 Compare Schema 3 DEMO 3 1 2 BACKUP Analyze Database Schema 2 CIA DEMO 3 1 2 TEMPLATE Analyze Database Data 3 E4 CIA DEMO DATAMGMT V1 Mi DOL 3 CIA_DEPLOYMENT_TEST E CIA_ISCHENKO Groom Database CIA MERCH SOURCE SSH Terminal 3 CIA 2013 3i E3 CIA NRF DEMO SFTP File Explorer 2 8 CIA DEMO DM File Utilities gt 3 8 CIA OFFERING MATCHING TRAINING 2 8 CIA New E POCIB Open 41 POC2 CLICKSTREAM 3 POC2 DM PROD 5 4 CIA POC2 GENERATED E CIA 2 PROD 3 CIA 2 SCORING 9 83 CIA POC4 SOURCE H RIO DEV 3 CIA SAVED LISTS 2 83 CIA SOURCES DEMO 3 CIA STAGE V3 12 3 CIA STUB 4 CIA TEST 4 MA n Recent You can submit commands to Aginity Workbench by specifying command line parameters at the command prompt Arguments can consist of a name and value argument pair whereas others are indicator type arguments that do not have a In the following example two arguments are submitted to the application using the command prompt Example 1 2 arg2value CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT
44. e more than one application window open such as for example the Query Analyzer window File gt New Query Window the Server Query History window or any other non modal window and you want to quickly switch between these windows go to the Window menu and then select the required window from the drop down list When the required window is selected it becomes active and is displayed at the top of the list Window Help Minimize All Cascade Show Job Monitor 1 Query Analyzer 172 28 118 29 Untitled The following 15 a list of the options the Window menu includes e Minimize All Minimizes all open child application windows without minimizing the main Aginity Workbench window e Cascade Allows you to cascade all existing child application windows e Show Job Monitor Displays a window that contains a list of the application s background processes The listed operations include but are not limited to importing and exporting data converting text files and so on The window is automatically displayed when you execute any action that triggers background asynchronous processing in the application CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 7 12 Workbench Command Line Builder 12 1 Command Line Processing Command line processing in Aginity Workbench is performed using the Workbench Command Line Builder The Workbench Command Line Builder can be accessed from the Tools menu Q Fil
45. e type of files to be opened by Aginity Workbench when they are double clicked in Windows Explorer e Security Provides options for managing cached SSH credentials e Diff tool Allows using an external file diff tool configured using the Tools Options functionality for comparison purposes There are several file diff tools supported by Aginity Workbench For details refer to Using a 3 rd Party File Diff Tool in Workbench e Query Analyzer Defaults Allows you to modify the Object Browser settings number of results returned by a query Enables syntax highlighting and multiple other parameters e Code auto complete Provides access to the various auto complete code options e Code snippets Allows you to create and edit your own code snippets e Result set options Output options Allows you to edit the output format of the data in the Result Set section e Excel CSV export Allows you to adjust the default data import and export settings e Database highlighting Allows you to use different background colors for different database names e Stored Procedures Allows you to choose a method for creating or replacing a stored procedure e DB Script Allows you to specify which objects must be included in the script by default e Compare Schema Allows you to specify the actions that must be selected by default CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 43 aginity e UDX Designer Allows you to
46. ema and save it in either the internal Workbench format so that it could be opened later or as a SQL file e Open DDL File Opens a DDL file from a local drive and displays it in a new window You can compare this DDL file with another DDL file and or database save the DDL file and generate a SQL script from the open DDL file CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 17 aginity e Recent DDL Files Displays the 10 most recently opened DDL files To adjust the number of the most recently opened DDL files to be displayed click Tools and then select gt Options gt General settings e New UDX Allows you to create a new user defined function Aggregate Function or Table Function using C e Open UDX file Allows you to open a previously created UDX file e Recent UDXs Displays the 10 most recently opened UDX files To adjust the number of the most recently opened UDX files to be displayed click Tools and then select Options gt General settings e Open Query Plan file Allows you to open a query plan file When the file is opened a new window displays the query execution plan and a step by step description of the actions performed on the host machine and SPUs e Recent Query Plans Displays the 10 most recently opened query plans files To adjust the number of the most recently opened UDX files to be displayed click Tools and then select gt Options gt General settings e Print All
47. en or click m to browse for the appropriate file e If you want a new output file to be created type its name in the Output file list box 9 Inthe Error file drop down list do one of the following e If you want to use an existing error file select the file to which errors that may occur during the SQL script s execution must be written or click to browse for the appropriate file e If you want to use a new error file type its name in the Error file list box 10 In the Output Formatting area specify the values of the following formatting parameters e Column separator e Row separator e Quote char 11 If any user defined parameters are used in either in the SQL script to be executed or in any of the SQL scripts employed for tracing purposes you need to define the value of those parameters in the SQL Parameters tab Do one of the following e To specify the values of the user defined parameters at the top of the Properties area select the SQL Parameters tab e Go to step 14 of this procedure 12 To specify the values of the user defined parameters and their data types do the following CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 79 aginity In the Value filed of the displayed SQL Parameters tab specify the values of the existing user defined parameters b In the corresponding Type drop down list specify the data type for the specified parameter value 13 Do one of the followi
48. er s Query History The application keeps a history of your queries issued from the current workstation The data is stored on the user s PC This option is useful if you need to know if some specific SQL command or query has been executed by the current user By default the main workspace of the window contains a list of all user queries issued from your current workstation When a history record is selected and highlighted the query is displayed in full at the bottom of the window DB all User all Host all Find Clear Find Query History Server Database User Run Date v End Time Duration Rows SQL Statement 192 168 0 90 2013 02 05 00 00 00 695 EXEC UT_DROP_TABLE_IF_EXISTS temp CUSTOMER scd 0 192 168 0 90 2013 02 05 14 58 52 00 00 00 45 4 Generate statistics STG CUSTOMER MEMBER 192 168 0 90 2013 02 05 14 58 52 00 00 03 041 22 766 create temp table STG CUSTOMER MEMBER as select MEMBER NATURAL KEY Lt 192 168 0 90 2013 02 05 14 58 49 00 00 00 496 1 Generate statistics on temp CUSTOMER scd 0 192 168 0 90 2013 02 05 14 58 48 00 00 01 737 22 766 create temp table temp CUSTOMER scd 0 asselect cast src CW STR ID 1 lsrc CR 192 168 0 90 2013 02 05 14 58 46 00 00 00 414 1 EXEC UT TABLE EXISTS temp CUSTOMER scd 0 The query list can be filtered using the filters at the top of the window If you want to locate a specific text type it in the Find box or if the keyboard focus is on the history gri
49. estination database and name of the destination table CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 46 aginity Note Importing data into an existing table 15 not currently supported After all the steps are completed the newly created and completed table is displayed in the Object Browser Note You need to refresh the Object Browser for the table to be displayed 7 2 Using a 3 rd Party File Diff Tool in Workbench To use a 3 d Party File Diff Tool in Workbench 1 Select Tools gt Options 2 In the tree in the left pane of the displayed Apphcation Options window select General gt Diff Tool The first installed file diff tool that is recognized by the Workbench 15 selected in the Tool drop down list The appropriate arguments for the tool are automatically displayed in the Arguments box CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO 47 e Application Options File Diff Tool Configuration Toot CodeCompare C Program Files x86 Devart CodeCompare CodeCompareGui ual 51 2 7 14221 24722 OK 1 the first file to compare 2 the second file to compare the title for the first file if applicable 22 the litle for the second file if applicable Data import export Excel CSV export Database Highlighting Stored Procedures Defaults DB Script Defaults Notes e The Workbench allows using any other tool that is not isted provided that t
50. f one or more parameter variables can be substituted before the SQL query 15 sent to the database To apply parameter substitution create a SQL statement that using the sign The following is an example of a SQL statement that uses the sign select from TestParam limit 10 In the above example TestParam is the name of the parameter that must be substituted When you execute a SQL query containing a parameter that follows the ParamName mask incidentally 9 TestParam the Query Parameters Editor is displayed requesting you to specify the value of the parameter and select its data type Query Parameters Editor CONFIDENTIAL AND INITY DISTRIBUTE WITHOU ZATION 57 0 0 JJ o J aginity Query Parameters Editor The following 15 a list of the parameter types used e Byte e Boolean e Date e Date and time Numetic Double e Int 16 32 64 e Signed byte e String CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 58 aginity e Time e Unsigned int 16 32 64 e Inserted As Is The As is parameter type is used to perform text replacement without parenthesizing the parameter value This allows you to use statements such as select from table where a table can be defined as an As Is parameter The following parameter names are automatically assigned a data type other than String if not specified otherwise by the user Note The user 15
51. his tool supports transferring files using the command prompt e Under Legend a hint is displayed how the Arguments box must be filled in for a tool that is not known to Aginity Workbench 3 Select another file diff tool installed on the computer or select a custom file diff tool from the Tool drop down list 4 f a custom diff tool is selected specify the executable location and command prompt parameters for the custom tool The green OK label is displayed if under the Arguments box if the program file has been located and the tool is ready to be used You can use the selected diff tool to find out the differences between two database schemas or CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 48 aginity between two versions of the same file one of which has been modified externally and is currently opened in the SQL Editor 7 3 Using Database Highlighting The database highlighting function allows modifying the appearance of the SQL window and the background of the DB Name box This is done by applying a set of modifiable rules DB Name SECT 7 Query editor background Database_Test A 192 255 192 V DB selector item background Firebrick To set modify the rules to be applied 1 Select Tools gt Options gt Database Highlighting 2 In the Database Highlighting section click Add 3 When the DB name is provided click
52. lays a new window that allows you to connect to the SSH terminal e File Utilities Allows converting a file to the CSV format reformatting a txt file and changing the character encoding and line endings e Import Data Allows uploading data to the database For details on this functionality refer to Importing Data to the Database e Options Allows completely customizing Aginity Workbench for Netezza Application Options General Application Parameters Default query timeout 3600 sec Show 10 items in Most Recently Used file menus 171 Show Tips and Tricks at the application startup Send anonymous usage to the server to help improve Workbench Options Auto save interval 30 gt seconds OleDb Connection Defaults OleDb services Tab Characters Tab size 4 71 Convert TABs to Spaces automatically The Application Options functionality includes the following option related sections e General Allows you to set the default query time adjust the number of the files displayed on the Most Recently Used menus and switch on ot off the Connection pooling functionality IETARY AGINITY LLC DO NOT CONFIDENTIAL AND PROPRIE R AUTHORIZATION 42 P R DISTRIBUTE WITHOUT PRIO aginity e Fonts and Colors Allows you to customize the appearance of the SQL Editor and syntax highlighting e Shell Integrations Allows you to select th
53. lected table including Reclaim Groom Change Distribution Change Organize On Re order the data and Analyze the data e Charts Displays a frequency distribution chart e Edit Comment Allows adding comments to changes and modifications Comments can also be added to table columns They can be edited using both Workbench functionality and SQL queries e Refresh Columns Updates columns in the selected table e Show Distribution Visualizes how your data 18 distributed over the SPUs in your system The data highlighted in blue is live data The red highlighting signifies data that has been deleted but is yet to be purged by using the Groom database or Reclaim commands e Show Storage Displays technical details on the selected table s storage The information 1 provided by Table by Data Slice and as a Summary e Import Export Data Imports and exports a table from and to an external text file You can specify how the data to be imported must be formatted For details on the available options refer to the Netezza Data Loading User Guide e Refresh view list Updates the view list in the selected database e Script Generates a DML or DDL commands either inserting them into the Query window ot copying them to the clipboard e Data Review Executes a SQL script that retrieves the top 100 records e Charts Displays a frequency distribution chart You are requested to select a numeric table column to be used for creating
54. lipboard Parses a connection string that is currently in the clipboard if any and creates a new connection entry based on its contents o Export to File Saves a user defined set of connection entries to a password protected disk file This 1s useful for transferring connection entries from one computer to another o Import from File Reads connection entries from a file created by the Export to File function CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO aginity If you have both ODBC and OleDb drivers installed on your system you can choose which driver to use for the current connection Aginity Workbench does require either of those drivers to be installed on the user machine be able connecting to the Netezza appliance CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO aginity 3 Main Window of Aginity Workbench The most widely used window of Aginity Workbench application is the Query Analyzer window that allows exploring the database schema and executing SQL queries You can simultaneously connect to several database servers A new Query Analyzer window is opened for each new connection You can use Ctrl Tab key combination or the Window menu for switching between several Query Analyzer windows The Query Analyzer window consists of three major parts the Object Browser the SQL Editor and the Result Set 2 Aginity Workbench for Netezza Query Analyzer _ Untitle File Edit View Query Results Obje
55. lued function e Edit Comment Allows adding and editing the description of the selected user defined function Functions Specific to User Defined Aggregates e Refresh UDA list Updates the UDA list Updates the user defined ageregates list in the selected database e Script Generates a SELECT statement inserting it into the SQL Editor or adding it to the clipboard e New Allows creating a user defined function Fill in the Function Properties form and then click OK e Edit Comment Allows adding and editing the description of the selected user defined aggregates Functions Specific to Synonyms e Refresh synonym list Updates the user defined aggregates list in the selected database e Edit Comment Allows editing comments for the selected synonym CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 29 aginity 6 2 Viewing Database Space You can view the amount of space is occupied by any table that exists in your database using the Show Tables Size function You can access this function by selecting clicking Object on the Menu bar and then selecting Database gt Show tables size The system provides storage details for tables in a selected database that can be displayed in the Table Space window The information is presented as a grid and a chart and includes data skew blocks bytes used blocks bytes allocated and so on To view the grid and chart for a selected database on the
56. n Width File e External Database Note Files in all the supported formats can be imported directly from an archive zip gzip bzip2 without creating any intermediate disk file that may be quite large Multiple CSV files can be imported at a time You should take the following into consideration while uploading data to the database e Source selection is required if a file is opened from an archive and the archive contains more than one file If there are more than one non empty worksheet in the Excel file select a worksheet to work with CONFIDENTIAL AND ROP P RI DISTRIBUTE WITHOUT PRIO ETARY AGINITY LLC DO NOT R AUTHORIZATION 45 amp aginity Pa After the source file is selected the Data Import Wizard window 1s displayed that allows you to specify the data import parameters The data import procedure consists of the following steps 1 Specifying the file encoding and field delimiter applicable to CSV only or the column widths and file encoding applicable to a fixed width columns file only 2 Selecting the limit for the number of rows or columns specifying the data range 3 Specifying whether or not the first row must contain column names columns to be included in the output and their order 4 Specifying the physical data types The data type information is provided automatically but it can be customized by selecting the Treat all columns as varchar check box 5 Specifying the d
57. ng e To save the defined configuration as an executable file click Save as CMD e To save the defined configuration as Workench CMD awbcmd file click Save Note A Workbench CMD file can be opened with the Workbench Command Line Builder for viewing or editing purposes Also it can be executed from the Windows command prompt by specifying the file as one that contains the parameters for launching Aginity Workbench do this the user can save the file as action awbemd file the awbcmd configuration file name pwd the file password 12 3 2 Tracing a SQL Statement in Workbench Command Line Builder The Workbench Command Line Builder allows specifying the tracing SQL statements You can access this functionality by selecting the Tracing tab CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO 80 Workbench Command Line Builder blaj Command Type Execute SQL Description New Execute SQL Command Properties General SQL Parameters Tracing Final SQL Enable tracing Connection string Data Source local User ID sa Password no way of showing it here Initial Catalog test m Tracing SQL Statements Script Start Script End Statement Start Statement End SQL Script SQL File INSERT INTO dbo LogProcess BatchID Description StartDate VALUES Sbatchid cmd line test GETDATE select SCOPE IDENTITY 4 This SQL is executed before the first script statemen
58. ows you to print text found in the SQL Editor e Send Sends an open query by e mail either as an attachment or as a compressed attachment e Open SPU Utilization Info file Allows you to open an SPU utilization info files To create the file on the Query menu click Execute with SPU Utilization monitor The file contains details related to the timings of a query s execution This allows you to detect the processing skew e Recent SPU Utilization Infos Displays the 10 most recently opened SPU utilization files To adjust the number of the most recently opened SPU Utilization files to be displayed on the Menu bar click Tools and then select Options gt General settings e Exit Allows you to exit Aginity Workbench CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 18 aginity 5 Edit Menu Most of the functions provided by the Edit menu become available only when the pointer 18 positioned in the SQL Editor Query Analyzer However some of the functions are also available when the keyboard focus is on the Result Set grid or on the Object Explorer tree The Edit menu allows managing text data For example you can undo the latest change cut copy paste selected text and make some minor changes to the text Edit View Query Result im Undo Ctrl Z Redo Ctrl Y 4 Cut Ctrl X Ctrl C Copy with Headers Paste Ctrl V Find Ctrl F Find Again
59. phics cards in Windows OS support key combinations such asCtrlt Altt Right and Ctrl Altt Left Make sure to check the graphics card settings prior to using these key combinations in Aginity Workbench A bookmark can also be turned on or off Turning a bookmark off makes the bookmark marker invisible when the go to next previous bookmark option is used To turn a bookmark on or off point to the line where the bookmark 15 located and then select Edit gt Bookmarks gt Enable Disable bookmarks To remove all bookmarks click Edit and then select Bookmarks gt Clear Bookmarks The following is an example that shows three set bookmarks select 1 select 1 0 select 2 select 2 1 select 3 select 3 The first bookmark to the left of the select 1 statement is an unnumbered and enabled bookmark The second bookmark to the left of the select 2 statement is a numbered and disabled bookmark The third bookmark is a numbered bookmark that is currently enabled e Advanced A set of advanced functions for working with text in the Query Analyzer They include CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 21 aginity o Comment selection Adds the comment symbol to the beginning of each line of the selected text or to the current line if no text is selected in the open SQL Editor window o Uncomment selection Removes the comment symbol from the beginning of each line of the selec
60. provide as well as on that of MPP solutions and MPP system implementations in particular The bulk of the functionality provided by Aginity Workbench consists of the following capabilities e Auto complete Allows you to greatly facilitate SQL development by automatically completing the text that you are starting to type e Parameterized Queries Creates SOL templates that have parameterized values thus making it easy to store and re use SQL logic e Stored Procedure Wizard Allows you to rapidly create and deploy stored procedures e UDX Procedure Wizard Allows you to rapidly create test and deploy user defined scalar functions table functions and aggregates using C e Query Plan Analysis Monitoring amp Viewing The ability to view and monitor how query parallelism is distributed across the appliance with respect to the resources as well as quickly relate this to the query plan e Reverse Engineer Allows you to reverse engineer an MPP schema with complete syntactic suppott CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO aginity e Cross Platform Schema Migration Allows you to compare a database schema within or across appliances and produce a script to synchronize the schemas while retaining original data You can retrieve the schema and data definition language DDL from the source databases and translate this data into your target system schema e Data Distribution Allows you to quickly view
61. r all executed queries up to a system defined limit which is 2000 queries by default e Show Tables Size Displays a window that provides a detailed description i e the Data Skew of the currently selected database tables For details refer to Viewing Database Space e Analyze DB Schema Allows performing several types of data validation including the uniqueness of data referential integrity bad table distribution and so on For details refer to the Tools Menu e Analyze DB Data Performs analysis of the table contents to suggest improvements on the nullability of those fields that allow nulls minimization of the row size by using a minimum precision for columns and minimization of the character columns length e Groom Database Purges the previously deleted records from the database tables Functions Specific to the Table Object Type e Refresh table list Updates the list of tables for the currently selected database e Script Generates the basic DML commands for the selected table such as SELECT INSERT and UPDATE as well as DDL commands inserting them into the open Query Editor New Query Editor or copying them to the clipboard e Data Review Executes a SQL script that retrieves the top 100 records all existing duplicate records or top 100 duplicate records CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 25 aginity e Advanced Displays a set of advanced options for managing the se
62. ry Analyzer window Every time you open a query set the application requests you to establish a new connection and displays the query set in a new Query Analyzer window e Recent Query Sets Displays the 10 most recently opened query sets You can also open any of the most recent queries by selecting it from a drop down list box You can adjust the number of the recently opened query sets to be displayed by clicking Tools on the Menu Bar and then selecting Options gt General settings e Save Query Set Allows you to save all tabs that are currently opened in the Query Analyzer to a single query set file The tabs that contain text loaded from a SQL file save the corresponding SQL files The SQL script that 1s contained in the tabs and has not been saved to a file is saved to the query set file e Save Query Set As Allows you to save a query set under a different name e Open SQL File Opens a SQL file and adds it to the new tab in the Query Analyzer e Recent SQL Files By default displays the 10 most recently opened SQL files You can adjust the number of the recently opened query sets to be displayed by clicking Tools on the Menu Bar and then selecting Options gt General settings e Save Query Saves text in the open SQL Editor to a disk file e Save Query As Allows you to save a text in the active SQL Editor under a different name e New DDL file Allows you to create a new DDL Data Definition Language file You can edit the sch
63. se this command to perform an operation similar to the one launched by the Script Database command Unlike with the Script Database command you must provide the connection details for the database that you want to reverse engineer e Compare Schemas Use this command to launch an easy to use GUI tool that allows you to effortlessly compare two database schemas and quickly generate delta SQL scripts to synchronize their structures To get started specify two databases or the NHS files previously created by the Script Database function and then click Compare CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 33 aginity When the comparison procedure is complete the structures of the selected databases are displayed The objects of the schema that are highlighted in red are either missing or different from those in the other schema To access the tool on the Menu bar click Tool and then select Compare Schemas a ee Aginity Workbench for Netezza Compare Schemas e File View Tools Window QS Connect Compare Schemas Generate Delta Script Schema 1 25 Open Save As 25 Open Save As Host 4 I e Host 2 1 Database SYSTEM Database TEST TOOLKIT Name TEST TOOLKIT Owner ADMIN Collation BINARY Collation BINARY Charset LATINS Charset LATINS Description Tables FZZLANCOVASTATS 8 FZZLDUMMY amp j FZZLENV 3j FZZLKMEANSCENTROID H FZ
64. t of the script Command Line To enable and be able to use the Tracing functionality select the Enable tracing check box in the upper left of the window For details on using the Tracing functionality refer to steps 2 5 of the procedure for executing SQL queries with tracing in 8 2 Executing SOL Queries with Tracing The Execute with Trace Feature CONFIDENTIAL AND P DISTRIBUTE WITHOUT 81 aginity 13 Help Menu The Help menu allows you to access the online help and forums check for updates and view the log file Help 23 About Help Tips and Tricks Check for Updates Get Netezza Drivers Provide Feedback Discussion Forums News Show Log File System Info The following 15 a list of the available Help menu options e About Provides program s version and brief description e Help Displays online help documentation Tips and Tricks Displays the and Tricks window where you can specify whether the tips and tricks information must be displayed during the start of the application e Check for Updates Checks whether you re using the latest available version of Aginity Workbench e Get Netezza Drivers Allows you to download drivers for Netezza e Provide Feedback Allows you to send in a suggestion or feature request to developers CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 82 aginity e Discussion Forums Displays a page with Discussion Forums You
65. tatements 1n multiple databases selected by the user e Execute with SPU Utilization monitor Displays the SPU utilization monitor when a SQL script is executed Note This function requires that you provide credentials for connecting to the database host operating system The user must belong to the nz user group for this functionality to work properly e Execute to File Executes the current statement or selected statements writing the execution result to one ot more disk files Various options for customizing the output format as well as for handling multiple result sets are provided e Add New SQL Editor Displays a new SQL Editor tab in the Query Analyzer The rest of the options allow you to quickly access the different parts of the system s functionality e Go to Tree Positions the pointer in the object tree of the Object Browser e Go to Query Positions the pointer in the current SQL Editor e Next Query Tab Displays the next tab in the SQL Editor e Prev Query Tab Displays the previous tab in the SQL Editor CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 55 aginity e Word wrap Turns on and off the word wrapping mode e Current Query Options Displays the options that are available for the open SQL Editor and allows you to switch on or off syntax highlighting You can also override the default time limit for query timeouts and returned rows limit set in seconds In addition this func
66. ted text or from the current line if no text is selected text in the open SQL Editor window Tabify selected lines Replaces spaces at the beginning of a line with tabs o Untabify selected lines Replaces tabs at the begging of a line with a user defined number of space characters the tab size measured in spaces can be adjusted in Tools Options o Make selection Upper case Makes selected text upper case o Make selection lower case Makes selected text lower case o Change selection case Changes lower case symbols to upper case symbols and vice versa Comment selection Ctrl Alt C Uncomment selection Ctrl Alt U Tabify selected lines Untabify selected lines Make selection UPPER case Ctrl Shift U Make selection lower case Ctrl U Change selection cASE Ctrl Alt Shift U e Outlining The Outlining function allows you to expand or collapse regions in the code in the Query Analyzer By default all code 1 visible in all Query Analyzer tabs CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO aginity This function can also help make the code more compact and readable by hiding the unnecessary lines under the Region clause region s boundaries are defined by the following text region RegionName to mark the beginning of the region and endregion to mark its end To create a region simply type the required text in the Query Analyzer region RegionName 1 Select from TableName
67. tem and will be scripted as VARCHAR 16000 Warning Production ProductPhoto LargePhoto data type varbinary is not supported the target system and vill be scripted as VARCHAR 16000 Warning Person Contact AdditionalContactInfo data type xml is not supported the target system and will be scripted as VARCHAR 16000 Warning Production Document Document data type varbinary is not supported the target system and vill be scripted as VARCHAR 16000 Warning Production Illustration Diagram data type xml not supported the target system and vill be scripted as VARCHAR 16000 Warning Sales Individual Demographics data type xml not supported the target system and vill be scripted as SS The generated script will automatically appear in the Query Analyzer window The following is an example of a DDL script displayed in the Query Analyzer Script CREATE TABLE ADDRESS ADDRESS ID INTEGER NOT NULL ADDRESS ID INTEGER NOT NULL ADDRESS LINEl NVARCHAR 60 NOT NULL ADDRESS LINEl NVARCHAR 60 NOT NULL ADDRESS LINEZ NVARCHAR 60 ADDRESS LINEZ NVARCHAR 60 CITY NVARCHAR 30 NOT NULL CITY NVARCHAR 30 NOT NULL STATE DDAOVTNCE Th MAT MITI T CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 41 aginity e Groom Database Irrevocably deletes records from the selected database tables e SSH Terminal Disp
68. th a LIMIT clause If you type sf in the Query Analyzer and then press ALT C the sf will be replaced with the actual pre defined SELECT statement To view and edit default code snippets or to add a new one select Tools gt Options gt Code snippets CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 52 select from limit 100 Code snippet is short string that expands into a statement you define For instance a snippet sf expands into SELECT statement with a LIMIT clause You type sf ht Alt C and the sf chars are replaced with the actual SELECT 4 Code snippets are defined separately for each database To edit a snippet double click it or click Modify The Code Snippet Properties window will be displayed Make the appropriate changes and then click OK Note The pointer will remain in the position in which you will leave it in the box until you click OK when using the Add New or Modify function CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 53 e Code Snippet Properties Snippet Properties Snippet Code sf Snippet Text select from limit 100 4 The cursor will be set to the position that you leave it in in the text box above before clicking OK button To add a new snippet click Add New and then provide a valid code for the new snippet The code must consist of alphanumeric characters and it can contain
69. the appropriate conditions click New 6 In the displayed Fier Condition Properties dialog box specify the following CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 69 aginity e Filter Field The field the filtering must be performed by The following options are available o Schema o Name e Criteria The following options are available o Equals O Starts with O Contains o Ends with e Not Equals e Filter Value 7 Ifyou want the specified filtering values to be case insensitive select the Ignore case check box 8 To go back to the Manage Schema Filters window for verifying that a filter with the specified filtering values has been added to the filters list click OK 9 In the Schema Value Properties dialog box click OK The specified filters are displayed in the Filter area of the Manage Schema Filters window Now the created filter needs to be applied to the Object Browser CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 70 Ee aginity To apply a created filter to the Object Browser Click View select gt Entity Filter gt Select and then select the created filter Notes Only those objects whose schemas and or names meet the filtering criteria will be displayed in the Tree view o Ifyou want to remove the filter click View and then select gt Entity Filtet gt None e User Query History Displays the current us
70. the code auto complete capability and more 3 2 3 Inserting a Object Column Names into the SQL Editor If the name of a database entity or an entity column selected in the Object Browser needs to be inserted into the SQL Editor double click the column The column name will automatically be inserted into the spot where the cursor is currently positioned in the open Query Editor You can choose from multiple text formatting options by selecting Tools gt Options gt Object browser and then selecting the most appropriate format from the On column double click insert drop down list In addition you can drag any node from the Object Explorer tree to the SQL Editor to insert the text where the pointer is currently positioned or at the point where the text is copied into the Query Editor this behavior can also be customized using the Too s Options windows 3 3 Result Set The section at the bottom of the Aginity Workbench window is called the Result Set The section displays the SQL Query execution result in two tabs the Output tab and the Result tab The Output tab has three sub tabs Standard providing a brief summary of the executed query Text providing a detailed description of the execution process and Grid allowing a grid view of the execution result The system displays the Output tab regardless of whether or not an error has occurred during a SQL script s execution If multiple statements were executed and the server
71. the current SQL query A semicolon is used as a delimiter If there is no selection made in the SOL Editor the query in which the cursor is located is executed If there is a selection made in the SQL Editor the query the pointer is set on 1 split into individual SQL statements using a semi colon as the separator Each of these queries is executed separately one by one using the same physical connection to the database ROP P DISTRIBUTE WITHOUT PRI IETARY AGINITY LLC DO NOT RIE OR AUTHORIZATION 51 aginity e Explain Displays a detailed explanation of how the query is going to be executed in the Explain window The Explain window has three tabs O Query Displays This tab is used to display the query under examination o Explain This tab displays a detailed explanation of how the quety will be executed o Explain Analyze This tab provides the related statistics and a detailed explanation of the execution result e Select Query at Cursor Selects the part of a query that precedes the semicolon symbol and displays it in the SQL Editor when you point to this query e Complete Code Snippet Allows you to create new code snippets and edit the existing ones To facilitate working with the application Aginity Workbench supports code snippets short and often re usable strings that can be expanded to create a statement For example a default sf snippet can be expanded into a SELECT statement wi
72. the frequency distribution plot e Create View Inserts the CREATE OR REPLACE VIEW SQL statement into a new SQL Editor window for fast and easy view creation CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 26 aginity e Edit View Inserts the CREATE OR REPLACE VIEW SQL statement into a new SQL Editor window providing all the required data from the currently selected view e Edit Comment Allows adding or editing the description of a selected view e Refresh columns Updates the columns of the selected view e Export Data Displays the Export Data window and allows you to specify the various parameters that affect the format of the output file Functions Specific to Sequences e Refresh Sequence List Updates the sequence list in the selected database e Script Generates a DDL command and inserts it into a query window or copies it to the clipboard e Edit Comment Allows adding and editing the description of the selected sequence Functions Specific to Stored Procedures e Refresh Stored Procedures List Updates the list of external tables in the selected database e Script Generates an EXECUTE statement or DDL command and inserts it into the Query Editor or copies it to the clipboard e Create Procedure Displays the Stored Procedure Properties window where you can specify the new procedure s name language and arguments e Edit Procedure Allows editing any stored procedure e
73. tion allows you to specify the proper query type when you are editing a view or stored procedure for example you can change the behavior of the Execute function to Execute as Single Block so that no errors will occur when the stored procedure is created 2 Query Options Query timeout 3600 Quer kind V Enable parameterized queries parameters are specified as SPARAM_NAME iV Use Syntax Highlight V Enable code autocompletion Override default resultset row limit 10 8 1 Using Parameter Substitution Parameters are an essential part of SQL queries Aginity Workbench for Netezza supports parameter substitution for parameters that follow the ParamName or ParamName name mask The notation with the braces is especially useful if you are going to execute the SQL script on the database host machine and take advantage of the nzsql feature allows you to replace the placeholders with the values of environment variables You can enable this feature by selecting the Enable Parameterized Queries check box in the Query Options window CONFIDENTIAL AND ROP P R INITY DISTRIBUTE WITHOUT PRIO ZATION 56 aginity To access the Query Options window on Main tool bar click Query and then select Current Query Options You can also right click in anywhere in the Query Analyzer and then select Options from the displayed drop down list When the Enabled Parametrized Queries feature becomes available the value o
74. y If you are running the script on a Linux machine the following syntax can be used select from Test limit 10 nzsql performs a replacement of the parameter s with the values of the corresponding environment vatiables CONFIDENTIAL AND PROPR DISTRIBUTE WITHOUT PRIO 60 aginity 8 2 Executing SQL Queries with Tracing The Execute with Trace Feature The Tracing functionality can be accessed by selecting the Tracing tab in the Workbench Command Line Builder or by clicking Queries on the menu bar and then selecting Execute with Trace File Edit View Query Results Object Tools Window Plug ins Connect 25 E 6 74 3 8 RI RI 9 RI RI REIR REI REIR RIDE REIR RR RD REIR RR I Binning Charts CIA DEMO 3 1 CIA DEMO 3 1 CIA DEMO 3 1 CIA DEMO DAT CIA DEPLOYME CIA ISCHENKO CIA MERCH SQ CIA NRF 2013 CIA NRF CIA NRF Execute with Trace CIA OFFERING CIA POC TRAIN E Add New SQL Editor CIA CIA POCIB CIA POC2 CLIC CIA 2 DM CIA POC2 GEN CIA POC2 PRO CIA 2 SCO CIA 4 SOU CIA RIO DEV CIA SAVED LIS CIA SOURCES CIA STAGE V3 CIA STUB CIA TEST CIA TEST CIA TEST PACKAGE CIA TEST PROCEDURES ERROR Execute Explain Select Query at Cursor Complete Co Execute All Execute as Single Batch Execute in Selected Databases Execute with SPU Utilization Execute to
75. zation of the row size by using the minimum precision for the columns and minimization of the size of the character columns e Migrate DDL Allows you to generate a DDL script and migrate a database from various platforms to Netezza The supported platforms include IBM DB2 Microsoft SQL server MySql Oracle and Teradata CONFIDENTIAL AND PROPRIETARY AGINITY LLC DO NOT DISTRIBUTE WITHOUT PRIOR AUTHORIZATION 38 aginity cows M es Select Source Database Type IBM DB2 Microsoft SOL Server MySql Oracle Teradata Cancel To migrate a database from any of the above platforms to Netezza you first need to choose the database type and establish a valid connection to the specified database This is done by clicking Tools and then selecting Migrate DDL After the connection is established the DDL Generator Wizard 1s displayed The DDL Generator Wizard allows you to generate the required DLL script Note You must follow all the 4 steps of the wizard to successfully migrate the database To generate a DDL script 1 On the Select Entities to Script step 1 of 4 page of the wizard select the entities that must be included in the script 2 On the DDL Generation Options step 2 of 4 page of the wizard select the DLL generation options that must be used The following options are available e Chart case Select from a list e Convert Pascal notations to underscores CONFIDENTIAL AND PROPRIETARY AGINITY L

AGINITY WORKBENCH FOR NETEZZA FUNCTIONALITY OVERVIEW

Contents

Download Pdf Manuals

Related Search

Related Contents