Filters

CLEAR ALL

Search Results ()

Filter Icon

Search Results ()

    All Plugins (171)

    Filter Icon

    Quick Info

    Product icon
    Product
    DevOps Deploy (HCL Launch)
    Plugin type icon
    Type
    plugin
    Compatibility icon
    Compatibility
    HCL Launch version 7.0 or later
    created by icon
    Created by
    HCL Software
    Website icon
    Website
    Published Date
    March 12th, 2021
    Last Updated
    May 17th, 2023

    Description

    The SQL-JDBC plugin is a database-independent plug-in. The SQL-JDBC plugin is an automation-based plugin. It is executed as part of the deployment to help manage database changes.

    Limitations: The SQL statements supported depend on the database vendor and the JDBC drivers being used.

    Quick Info

    Product icon
    Product
    DevOps Deploy (HCL Launch)
    Plugin type icon
    Type
    plugin
    Compatibility icon
    Compatibility
    HCL Launch version 7.0 or later
    created by icon
    Created by
    HCL Software
    Website icon
    Website
    Published Date
    March 12th, 2021
    Last Updated
    May 17th, 2023

    launch-sql-jdbc-13.1154072.zip

    Uploaded: 23-Mar-2023 10:27

    launch-sql-jdbc-12.1100867.zip

    Uploaded: 12-Mar-2021 11:49

    Summary

    The SQL-JDBC plugin automates SQL script runs by using a JDBC driver in a deploy process.

    This plugin includes one or more steps, click Steps for step details and properties.

    Compatibility

    This plugin requires HCL Launch version 7.0 or later.

    Installation

    See Installing plugins in HCL Launch for installing and removing plugins.

    History

    The following table describes the changes made in each plugin version.

    Plugin history details
    Version Description
    12 Minor enhancements

    Usage

    The SQL-JDBC plugin is a database-independent plugin. Use the Execute SQL Scripts or Execute SQL Scripts with PassTicket Authenitcation step to connect to a database and run SQL scripts by using a JDBC driver. You can specify that the SQL scripts run in a specific order or in any order.

    The SQL statements supported depend on the database vendor and the JDBC drivers being used.

    Step palette

    To access this plugin in the palette, click Database > SQL JDBC.

    Use Passticket Authentication with DB2 for zOS

    When used with DB2 for zOS, Execute SQL Script step supports two authentication methods, password and passticket.

    Authenticating with a Password or Password Script

    To use Password, use the Execute SQL Scripts step. Fill in the User and either the Password or Password Script field.

    Authenticating with a PassTicket

    To use Passticket, use the Execute SQL Scripts with PassTicket Authentication step. Fill in the fields: User, PassTicket Application ID, IRRRacf.jar File and IRRRacf Native Library Path.

    Passticket authentication eliminates the need to store password and send passwords across the network. Make the following system configurations to allow PassTickets:

    RACF Setup for PassTicket Generation and Authentication

    1. Activate the RACF PTKTDATA class if its not already active.
      Sample RACF commands:

      
      SETROPTS GENERIC(PTKTDATA)
      SETROPTS CLASSACT(PTKTDATA) RACLIST(PTKTDATA)
      
    2. Define a PTKTDATA profile for a DB2 system. PassTickets are generated and evaluated using a secret key. A PTKTDATA profile defines the secret key and the application ID that it applies to. The application ID for a DB2 system can be found in the LINKNAME column in the SYSIBM.LOCATIONS table. See the Sending RACF PassTickets topic in DB2 for zOS Knowledge Center to learn more. The key is a 64-bit number (16 hex characters). Replace the key16 placeholder with a user-supplied 16 character hex string (characters 0-9 and A-F) in the following sample RACF commands.
      Sample RACF commands for DB2 system DB2A:

      
      RDEFINE PTKTDATA FEKAPPL UACC(NONE) SSIGNON(KEYMASKED(key16))
      APPLDATA('NO REPLAY PROTECTION  DO NOT CHANGE')
      DATA('HCL Launch')
      
      
      The following example shows the command with the key16 value replaced:
      
      
      RDEFINE PTKTDATA FEKAPPL UACC(NONE) -
      DATA('HCL Launch') -
      APPLDATA('NO REPLAY PROTECTION - DO NOT CHANGE') -
      SSIGNON(KEYMASKED(0123456789ABCDEF))
      

      Notes:

      1. If the PTKTDATA class is already defined, verify that it is defined as a generic class before creating the profiles listed previously. The support for generic characters in the PTKTDATA class is new since z/OS release 1.7, with the introduction of a Java interface to PassTickets.
      2. If the system has a cryptographic product installed and available, you can encrypt the secured signon application key for added protection. Use the KEYENCRYPTED keyword instead of KEYMASKED. Refer to Security Server RACF Security Administrators Guide, (SA22-7683), for more information.
      3. If the system already has Rational Developer for System z or Rational Team Concert server components installed, the PTKTDATA profile may have been defined already.
    3. Define a PTKTDATA profile to control the ability to generate a PassTicket.
      Define the IRRPTAUTH profile in the PTKTDATA class to controls what userids a PassTicket may be generated for.

      Operation Profile name Required access
      Generate PassTicket IRRPTAUTH.application.target-userid Update

      Sample RACF commands:

      
      RDEFINE PTKTDATA IRRPTAUTH.FEKAPPL.USER1 UACC(NONE)
      
    4. Permit HCL Launch Agent to generate a PassTicket.
      In order for the HCL Launch Agent to generate a PassTicket, the userid of the agent must be permitted UPDATE access in the PTKTDATA profile that we created in the previous step.

      Sample RACF commands:

      
      PERMIT IRRPTAUTH.FEKAPPL.USER1 CLASS(PTKTDATA) ID(AGNTUSR) ACCESS(UPDATE)
      

      Refresh the PTKTDATA class for the new profiles and permissions to take effect.

      
      SETROPTS RACLIST (PTKTDATA) REFRESH
      

    RACF Setup Examples

    Example 1. Agent is started by user AGNTUSR. In a deploy process, a Job needs to be submitted on behalf of user USER1.

    
    RDEFINE PTKTDATA IRRPTAUTH.FEKAPPL.USER1 UACC(NONE)
    PERMIT IRRPTAUTH.FEKAPPL.USER1 CLASS(PTKTDATA) ID(AGNTUSR) ACCESS(UPDATE)
    SETROPTS RACLIST (PTKTDATA) REFRESH
    

    Example 2. Agent is started by user AGNTUSR. Allow this agent to submit jobs on behalf of any user.

    
    RDEFINE PTKTDATA IRRPTAUTH.FEKAPPL.* UACC(NONE)
    PERMIT IRRPTAUTH.FEKAPPL.* CLASS(PTKTDATA) ID(AGNTUSR) ACCESS(UPDATE)
    SETROPTS RACLIST (PTKTDATA) REFRESH
    

    Example 3. Agent is started by user AGNTUSR. Allow this agent to submit job on behalf of user AGNTUSR:

    
    RDEFINE PTKTDATA IRRPTAUTH.FEKAPPL.AGNTUSR UACC(NONE)
    PERMIT IRRPTAUTH.FEKAPPL.AGNTUSR CLASS(PTKTDATA) ID(AGNTUSR) ACCESS(UPDATE)
    SETROPTS RACLIST (PTKTDATA) REFRESH
    

    Steps

    The following process steps are available in the SQL-JDBC plugin.

    Execute SQL Scripts

    Execute SQL scripts using JDBC driver.

    Input properties for the Execute SQL Scripts step
    Name Type Description Required
    Autocommit Boolean Auto commit flag for database connection. No
    Connection String String The connection string used to connect to the database.
    Example: jdbc:mysql://dbhost:3306/mydb
    Yes
    Database JDBC Driver Name String The Fully Qualified JDBC driver Classname. Yes
    Driver Jar String The full path to the jdbc driver jar to use. Yes
    Encoding String The encoding of the files containing SQL statements. Leave blank to use the default
    JVM character encoding.
    No
    Error Handling Enumeration:

    • abort
    • continue
    • stop
    Action to perform when statement fails: continue, stop, abort. Yes
    Exclude Files String Enter a new line separated set of file filters for the files to exclude from the execution. No
    Files String A white-space or comma separated list of sqlfiles to run. These will run in order
    as opposed to files grabbed using Include Files. This also takes precedence meaning
    if this is set Include File box will be ignored.
    No
    Include Files String Enter a new line separated set of file filters for the sql scripts to execute. No
    Password Password The password to be used to connect to the database. If youre using a password script,
    leave this field blank and fill out the Password Script field below.
    No
    Password Script String If you want to use a script or property lookups for your password, leave the
    Password field blank and enter it here.
    No
    Print Result Sets Boolean Print result sets from the statements. No
    SQL Statement Delimiter String String that separates SQL statements. Yes
    User String The user name used to connect to the database. Yes

    Execute SQL Scripts with PassTicket Authentication

    Execute SQL scripts using JDBC driver and PassTicket authentication.

    Input properties for the Execute SQL Scripts with PassTicket Authentication step
    Name Type Description Required
    Autocommit Boolean Auto commit flag for database connection. No
    Connection String String The connection string used to connect to the database. For example: jdbc:mysql://dbhost:3306/mydb. Yes
    Database JDBC Driver Name String The Fully Qualified JDBC driver Classname. Yes
    Driver Jar String The full path to the jdbc driver jar to use. Yes
    Encoding String The encoding of the files containing SQL statements. Leave blank to use the default JVM character encoding. No
    Error Handling Enumeration:

    • abort
    • continue
    • stop
    Action to perform when statement fails: continue, stop, abort. Yes
    Exclude Files String Enter a new line separated set of file filters for the files to exclude from the execution. No
    Files String A white-space or comma separated list of sqlfiles to run. These will run in order as opposed to files grabbed using Include Files. This also takes precedence meaning if this is set Include File box will be ignored. No
    IRRRacf Native Library Path String Specify the path to the System Access Facility (SAF) native library, libIRRRacf.so. There is one library for 31-bit Java and one for 64-bit Java. You must point to the path of the appropriate library based on the version of Java you are running. The default value is /usr/lib. Yes
    IRRRacf.jar File String Specifies the path to the System Access Facility (SAF) JAR file, IRRRacf.jar. The default value is /usr/include/java_classes/IRRRacf.jar. Yes
    Include Files String Enter a new line separated set of file filters for the sql scripts to execute. No
    PassTicket Application ID String Subsystem ID of DB2 associated with PassTicket. Yes
    Print Result Sets Boolean Print result sets from the statements. No
    SQL Statement Delimiter String String that separates SQL statements. Yes
    User String The user name used to connect to the database. Yes

    Troubleshooting

    Deploying Stored Procedures from UCD

    The SQL-JDBC plugin does not support deploying stored procedures. We recommend using vendor provided tools such as IBM DB2 CLP, Oracle SQL* Plus or SQL Cmd when applying updates directly from UCD.

    For more advanced database deployments, consider using an intermediary tool such as Datical, DBMaestro and Liquibase.