Deploy schema changes

Schema changes involve adding or altering tables in the target WebSphere Commerce database. These actions are typically stored in SQL files. Each database vendor (DB2 or UDB for i5/OS, and Oracle) provides a means for running SQL files.

Regardless of which database you use, it is not recommended that you run complex SQL scripts when the WebSphere Commerce database is busy, since the SQL scripts will compete with the WebSphere Commerce application on database resources, such as for CPU or memory. In addition, the SQL statements in the scripts may need to lock the database resources (such as database records or tables), and this may affect the WebSphere Commerce application because of lock waiting or deadlock problems.

If you are making schema changes and are using the WebSphere Commerce workspaces feature, it is important that you also invoke the authoring server schema update utility when deploying these changes to your authoring server environment.

• (DB2) From a command prompt, connect to the database with proper user ID and password by running:

  1. db2 connect to database_name user user_name using password For example, if the database user "johnsmith" with password "pwd" wants to run the SQL script C:\script.sql against the database called "mall", this would be the command

     db2 connect to mall user johnsmith using pwd
     

  2. Run your SQL script file

     db2 -tvf 
     script_file_name
     

     For example, if the SQL script file is C:\script.sql, run:

     db2 -tvf c:\script.sql
     

• (Oracle)

  sqlplus user_name/ password@ database_name@ script_file_name

  For example, if the database user "johnsmith" with password is "pwd" wants to run the SQL script file "c:\script.sql" against the database called "mall", this would be the command:

  sqlplus johnsmith/pwd@mall@ c:\script.sql
  

• I5/OS:

  1. Copy the SQL file to your i5/OS database machine.

  2. Sign on to your target i5/OS database machine using your WebSphere Commerce Instance profile.

  3. Start a QShell session by entering STRQSH.

  4. Run your SQL script file

     db2 -tvf 
     script_file_name
     

     For example, if the SQL script file is script.sql, run:

     db2 -tvf script.sql
     

Related concepts

Authoring server schema update tool

Related tasks

Deploy custom database assets