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 thealldbconnector.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. Default 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. Default 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

Related tasks
Configure central database access to the Utility Docker container