Deploying 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 provides a means for running SQL files.

About this task

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.

Procedure

  • DB2From 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
    
  • For IBM i OS operating system
    1. Copy the SQL file to your database machine.
    2. Sign on to your target database machine using your WebSphere Commerce Instance profile.
    3. Start a Qshell session.
    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