Netezza in JAMS

Installing the Netezza Integration Pack

To use the JAMS Add-on for Netezza, users must complete the following installations:

  • Install the JAMS Scheduler and JAMS Add-on for Netezza on the JAMS Server.
  • Install JAMS AgentX on the Netezza Server, or on a Linux Machine with access to the Netezza Server.

Installing Netezza for JAMS

  1. On the JAMS Scheduler Server, run the JAMS Installer.
  2. On the Feature Selection step, ensure the JAMS Integration Pack for Netezza Integration Pack is checked. Do not un-check any components unless those components should be uninstalled.
  3. Finish the installation.
  4. Deploy JAMS AgentX from the JAMS Scheduler Server to the Linux Machine, or install JAMS AgentX directly onto the Linux Machine. See AgentX Documentation for detailed instructions on deploying JAMS AgentX.
NOTE: Un Checking components in the Feature Selection step will uninstall those components.
NOTE: To deploy and use JAMS AgentX, a user must be configured in JAMS with permissions to the relevant Netezza or Linux box where AgentX will be used.

Creating Netezza Jobs in JAMS

JAMS Offers NetezzaLoad, NetezzaBackup, and NetezzaSQL Execution Methods to automate Netezza.

The NetezzaLoad Execution Method allows users to automate loads to the Netezza Server.

The NetezzaBackup Execution Method allows users to automate backups of Netezza Databases.

The NetezzaSQL Execution Method allows users to run a SQL Query on a Netezza system.

Creating NetezzaLoad Jobs in JAMS

  1. Create a Job in the desired folder and choose the NetezzaLoad Execution Method.
  2. Select the desired folder for the NetezzaLoad Job, then ensure the Job Definitions tab is selected.    
  3. Click the Add button from the Control Bar to open the Add a New JAMS Job Definition dialog.
  4. In the dialog, give the new Job a Name, Description (optional), and Execution Method.
    In this case, select NetezzaLoad to create a Netezza Load Job.
  5. By default, the full Job Definition dialog will open when the Job is initially saved. Click OK.
  6. The Job Definition dialog will open.
  7. Define Elements, Parameters, Security, Properties, and Documentation as desired.
    NOTE: An Execute As property should be configured with a user that has access to Netezza.
  8. On the Source tab, define the available properties as follows:
    PropertyDescription
    CA Certificate FileSpecified the pathname of the root CA certificate file on the client system. This argument is used by Netezza clients who use peer authentication to verify the Netezza host system. The default value is NULL, which skips the peer authentication process.
    Security Level

    Specifies the security level that should be used for the session. The argument has 4 available values:
    0 - preferredUnsecured - This is the default value. Specify this option when you would prefer an unsecured connection, but you will accept a secured connection if the Netezza system requires one.
    1 - OnlyUnsecured - Specify this option when you want an unsecured connection to the Netezza system. If the Netezza system requires a secured connection, the connection will be rejected.
    2 - preferredSecured - Specify this option when you want a secured connection to the Netezza system, but you will accept an unsecured connection if the Netezza system is configured to use only unsecured connections.
    3 - onlySecured - Specify this option when you want a secured connection to the Netezza system. If the Netezza system accepts only unsecured connections, or if you are attempting to connect to a Netezza system that is running a release prior to 4.5, the connection will be rejected.

    TableSpecifies the table name. You can specify a fully qualified name for this value.
    PortSpecifies the port to use, allowing users to override the default. Leave 0 to use the default value.
    Login TimeoutAllows you to enter a different value, expressed in seconds, for the login timeout. This overrides the default value of 30 seconds.
    UserDatabase username. [NZ_USER] by default.
    PasswordDatabase password. [NZ_PASSWORD] by default.
    DatabaseDatabase name. [NZ_DATABASE] by default.
    HostNetezza hostname or IP address. [NZ_HOST] by default.
    DelimiterSpecifies field-delimiter character.
    Control FileSpecifies the control file.
    Data FileSpecifies the datafile to load.
    Log FileSpecifies the log file name. If the file exists, this appends to it.
    Bad/Rejected Rows FileSpecifies the bad/rejected rows filename (overwrite if the file exists).
    Output DirectorySpecifies the output directory for the log and bad/rejected rows files.
    Log File SizeSession variable (LOAD_LOG_MAX_FILESIZE) that specifies the size (in MB) of the log and bad/rejected rows files. The default is 2000MB (2GB).
    File Buffer SizeSpecifies the chunk size (MB for fileBufSize or bytes for fileBufByteSize) at which to read the data from the source file. Leave 0 to use default value.
    Allow ReplaySession variables (LOAD_REPLAY_REGION and MAX_QUERY_RESTARTS) that specify the number of query restarts for load continuation if a SPU has been reset or failed over. If n is a valid non-zero number, it specifies the number of allowable query restarts. If n is 0, the system defaults to the postgres default setting.
  9. Save and Close the Job definition.

Creating NetezzaBackup Jobs in JAMS

  1. Select the desired folder for the Netezza Backup Job, then ensure the Job Definitions tab is selected.    
  2. Click the Add button from the Control Bar to open the Add a New JAMS Job Definition dialog.
  3. In the dialog, give the new Job a Name, Description (optional), and Execution Method.
    In this case, select NetezzaBackup to create a Netezza Backup Job.
  4. By default, the full Job Definition dialog will open when the Job is initially saved. Click OK.
  5. The Job Definition dialog will open.
  6. Define Elements, Parameters, Security, Properties, and Documentation as desired.
    NOTE: An Execute As property should be configured with a user that has access to Netezza.
  7. On the Source tab, define the available properties as follows:
    PropertyDescription
    UserDatabase username. [NZ_USER] by default.
    PasswordDatabase password. [NZ_PASSWORD] by default.
    DatabaseDatabase name. [NZ_DATABASE] by default.
    HostNetezza hostname or IP address. [NZ_HOST] by default.
    DirectorySpecifies a list of one or more space-separated, full pathnames of the directories where the data is to be stored. This option applies to filesystem only (not veritas or tivoli). Users can specify up to 16 directories.
    Directory FileSpecifies a file with a list of backup target directories, one per line.
    ConnectorNames the connector to which you are sending the backup, either filesystem, veritas, or tivoli.
    Connector ArgumentsSpecifies a colon-separated list of passthrough arguments for the connector.
    Backup TypeSpecifies backup type.
    Backup UsersBacks up all users and groups, and any privileges which were defined in the system database. Note that the command backs up all users and groups regardless of whether they are referenced by any permission grants. The system backs up all global adminlevel permissions (those that are not associated with particular databases). The system does not back up permissions associated with specific databases, because they are saved during regular database backup of each database.
    Backup Schema OnlySaves only the schema of the specified database, but not the user data in tables or views. The schema includes the definitions of objects such as tables, views, synonyms, sequences, and others, as well as any access privileges defined in the database. This option is an easy way to replicate an empty database schema within a Netezza system.
    Number of StreamsBacks up the data using the specified number of streams.
    Backup Set IDSpecifies the backup users with to user for incremental backup, rather than the default. Note: the default backup set is the most recent backup set of the database you specify. You can override the default by using this option.
    Secret KeywordSpecifies a string value needed to generate a 256-bit symmetric key, which is used to encrypt the host key in the backed up data.
  8. Save and Close the Job definition.

Creating NetezzaSQL Jobs in JAMS

  1. Create a Job in the desired folder and choose the NetezzaSQL Execution Method.
  2. Select the desired folder for the Netezza Job, then ensure the Job Definitions tab is selected.    
  3. Click the Add button from the Control Bar to open the Add a New JAMS Job Definition dialog.
  4. In the dialog, give the new Job a Name, Description (optional), and Execution Method.
    In this case, select NetezzaSQL to create a Netezza SQL Job.
  5. By default, the full Job Definition dialog will open when the Job is initially saved. Click OK.
  6. The Job Definition dialog will open.
  7. Define Elements, Parameters, Security, Properties, and Documentation as desired.
    NOTE: An Execute As property should be configured with a user that has access to Netezza.
  8. On the Source tab, define the available properties as needed:
    PropertyDescription
    UserDatabase username. [NZ_USER] by default.
    PasswordDatabase password. [NZ_PASSWORD] by default.
    DatabaseDatabase name. [NZ_DATABASE] by default.
    HostNetezza hostname or IP address. [NZ_HOST] by default.
    QuerySpecifies SQL query to run.
    Query FileExecute queries from this file.
    Unaligned Table OutputSpecifies unaligned table output mode. Normally, output is well formatted with white space added so that columns align. If you use this option, the nzsql command removes extra white space. Use this option when you want to compare results between two systems or when you want to transfer data.
    Print Rows OnlyNormally the nzsql command includes column headings and a summary row for all SQL queries. Use 'Print Rows Only' option to eliminate the column headings and summary row. Use this option with the 'Unaligned Table Output' option to produce data in a transportable format.
    Expand Table OutputUsers may use this option to display the query results vertically instead of in the default table/2D grid.
    HTML Table OutputFormat the nzsql output to use HTML tags.
    Field SeparatorSet the field separator.
    Output FileFile to send the output to.
  9. Save and Close the Job definition.