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, '-dbProfile default' will be 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, 'wcs' will be used.
dbType
This value can be one of 'db2' or 'oracle'.
dbUser
The username to connect with. If dbProfile is provided, this value will be ignored and is not required.
dbPassword
The password for the given username. If dbProfile is provided, this value will be ignored and is not required.
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 Similar to 0, but also logs errors.
  • 2 Similar to 1, but also logs warnings.
  • 3 Similar to 2, but also logs additional diagnostic information.
log
Specifies the base name of the log file. The default value is 'runSQL.log'. A timestamp will be 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.

A system property called WCInstallDir must also be set (e.g. DWCInstallDir=/opt/WebSphere/CommerceServer90). This will normally be set by the calling script.

The dbName can be in one of two styles. 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 has 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://16.3.114.132:50000/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