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
HCL Launch
Type
plugin
Compatibility
HCL Launch version 7.0 or later
Created by
HCL Software
Website
Version Name Action

12.1100867

launch-sql-jdbc-12.1100867.zip

launch-sql-jdbc-12.1100867.zip

12.1100867


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.