runSQL utility

The runSQL utility runs SQL statements defined in one or more files against a database.

Command syntax

./runSQL.sh [-dbProfile profileName] [-dbName dbname] [-dbSchema schemaName] [-dbType dbType] 
[-dbUser username] [-dbPassword password] [-file sqlfile] [-listFile listfile -listFileDir directory] 
[-help] [-trace integer] [-log logName] [-:propertyName propertyValue] [-:propertyName propertyValue] ...

Parameter values

dbProfile
The database profile name, which is used to lookup a profile in the alldbconnector.xml file. The dbType parameter is required. Only one of dbProfile or dbName can be provided. If neither is provided, the default -dbProfile default is used.
dbName
The JDBC URL or hostname. Only one of dbProfile or dbName can be provided.
dbSchema
The name of the schema in which to execute the SQL. If this parameter is not provided, the default wcs is used.
dbType
This value can be one of db2 or oracle.
dbUser
The username to connect with. If dbProfile is provided, then this value is not required, and will be ignored.
dbPassword
The password for the given username. If dbProfile is provided, then this value is not required, and will be ignored.
file
The full path or relative path of a file that contains SQL statements to run. This parameter, or listFile is required.
listFile
The name of a file that contains a list of other SQL files to run. Each line contains one SQL filename, which must be a path relative to listFileDir. The SQL files will be executed in the order that they occur in the file. This parameter, or file is required.
listFileDir
The base directory for the relative paths listed in the file specified by listFile. This parameter is required if listFile is used.
help
Prints this message and ignores all other parameters.
trace
Sets the amount of logging information to be provided. The default value is 2 and includes basic information, errors, and warnings. The value must be between 0 and 3.
  • 0 The minimum logging level.
  • 1 Includes all rows from log level 0, but also logs errors.
  • 2 Inlcludes all rows from log level 1, but also logs warnings.
  • 3 Includes all rows from log level 2, but also logs additional diagnostic information.
log
Specifies the base name of the log file. The default value is runSQL.log. A timestamp is added to the filename.
:propertyName
Any number of connection properties may be specified which will be set on the connection. This can be used to specify database-specific properties. If an override in alldbconnector.xml is used which specifies the same property, the value in alldbconnector.xml will take precedence.

Usage notes

  • System properties include:
    • A system property called WCInstallDir that specifies the HCL Commerce installation directory (WCInstallDir) must be set. Typically this is done by the calling script.

      For example, -propertyname:DWCInstallDir=/opt/WebSphere/CommerceServer90

  • The dbName can be specified in one of two ways:
    • If it is a JDBC URL, it has one of the following forms: 
      jdbc:db2://hostname:port/name
jdbc:oracle:thin:@hostname:port:SID
jdbc:oracle:thin:@//hostname:port/service
      In this style, the dbUser and dbPassword parameters are required.
    • If dbName is a hostname, it can be specified in the form:
      hostname:port/name
      where name is the database name. In this style, the dbType, dbUser and dbPassword parameters are required.

Example

Example usage with a URL:
./runSQL.sh -dbUser wcs -dbPassword password -dbName jdbc:db2://DB2_host:ssl_DB2_port/mall -file insert.sql
Example usage with a profile:
./runSQL.sh -dbType db2 -dbProfile mall -file insert.sql
Example usage with the default profile:
./runSQL.sh -dbType db2 -file insert.sql