Deploying schema changes

Schema changes involve adding or altering tables in the target HCL Commerce database. These actions are typically stored in SQL files. Each database vendor provides a means for running SQL files.

About this task

Do not run complex SQL scripts when the HCL Commerce database is busy, since the SQL scripts will compete with the HCL 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 HCL Commerce application because of lock waiting or deadlock problems.

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

Procedure

  • HCL Commerce Version 9.0.0.8 or later If you are using the preconfigured Db2 Docker container:
    1. Start a bash shell by running the following command with the Db2 container name. For example,
      docker exec -it <container_name> bash
    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
      
  • DB2If you are using Db2 database other than the preconfigured Db2 Docker container:
    1. Db2 From the database node, connect to the database with your user ID and password. As an example, the database user johnsmith with password pwd wants to run the SQL script C:\script.sql : db2 connect to database_name user user_name using password
      
      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 If you are using an Oracle database, running the following command:

    sqlplus user_name/ password@ database_name@ script_file_name

    As an example, 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:

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