Schema Creation Scripts

The following table describes the scripts that create the ATG Service schemas.

Note: If you have already created production and data warehouse schemas for your ATG installation, use the existing schemas. Do not create new production and data warehouse schemas.

SQL File

Description

service_agent_all.sql

This SQL script packages all of the application and platform tables required to run ATG Knowledge. It is a combination of service_agent.sql and service_agent_platform.sql. Use this script if you do not already have all of the required platform and application tables installed in your Agent schema.

Each supported database has its own version of the service-all script in the <ATG9dir>/Service9.3/Service/install/scripts/database-type directory. This script runs a number of SQL files for your specific environment. You can use your own database tool to run the various SQL files outlined in the create-service-all script.

service_agent.sql

Versioned ATG Knowledge tables.

service_agent_platform.sql

This SQL script packages all platform tables required to run Knowledge. Use this script to install the required platform tables for your Agent schema but not the application tables.

service_datawarehouse.sql

These are the ATG Knowledge and ATG Self Service data warehouse tables and should be installed where other data warehouse tables are installed, usually in a separate DB instance optimized for data warehousing.

service_platform_datawarehouse.sql

These are all the data warehouse platform tables. This SQL script should be used if you already have the platform data warehouse tables installed. This SQL script should be installed where other data warehouse tables will be installed, usually in a separate DB instance optimized for data warehousing.

service_datawarehouse_all.sql

This SQL script combines service_datawarehouse.sql and service_platform_datawarehouse.sql.

service_production_all.sql

This SQL script combines service_production_platform.sql, service_self_service.sql,and service_shared.sql. It is provided as a convenience for setting up demos and QA testing. In a production installation, the data warehouse scripts would not be installed in the production schema.

service_production_platform.sql

These are all the production/customer facing platform tables need for ATG Self-Service. This SQL script should be used only if you do not already have the platform tables installed in the production/customer facing schema. They are installed on the production/customer facing database server.

service_self_service.sql

These are the unversioned tables that are read from and written to by both the ATG Knowledge and ATG Self-Service applications. They are installed on the production/customer facing database server.

Note: ATG provides table drop scripts to match each table create script; for example, drop-service-all that uninstalls tables as necessary.

Using MSSQL in Multi-Language Sites

Keep the following in mind for multi-language sites:

MSSQL and Performance

To significantly improve performance, set the sendStringParametersAsUnicode parameter in the Microsoft SQL server driver. If using the com.microsoft.jdbc.sqlserver.SQLServerDriver, make sure the data source URL includes sendStringParametersAsUnicode=false.

For example:

jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=admin;
sendStringParametersAsUnicode=false;SelectMethod=cursor
To Edit and Install the Service Schemas

The create-service-all batch/shell script creates the database tables for all of the Service modules, including Search. For more information, refer to Using ATG Search with ATG Service.

Before running the scripts, you must configure the environment variables file service-env for your database.

  1. Open the <ATG9dir>/service9.3/Service/install/scripts/service-env.bat file and set the variables for your database.

  2. Modify the agent, production and data warehouse database account name for your environment. The script contains several markers for the following variables. Change the variable information to match your environment.

    Note: Variable names change between database types, and not all variables are required for all database types:

    • DYNAMO_HOME – For example: DYNAMO_HOME=C:\ATG\ATG9.3\home

    • DB_USER_AGENT – The name of the agent database user

    • DB_PWD_AGENT – The password of the agent database user

    • DB_NAME_AGENT – The name of the agent database

    • (Oracle only) ORA_TNS_PRODUCTION – The TNS identifier on the production machine

    • (Oracle only) ORA_TNS_AGENT – The TNS identifier on the agent machine

    • (Oracle only) ORA_TNS_DW – The TNS identifier on the data warehouse machine

    • (Oracle only) SEARCH_ORACLE_TNS – The TNS identifier on the search machine

    • DB_USER_PRODUCTION – Identify the name of the database user on the production machine

    • DB_PWD_PRODUCTION – The password of the database user on the production machine

    • DB_NAME_PRODUCTION – The name of the production database

    • DB_USER_DW – The name of the data warehouse database user
      DB_PWD_DW – The password of the data warehouse database user

    • DB_NAME_DW – The name of the data warehouse database

  3. Save the service-env script.

  4. Once you have completed the edits, run the create-service-all batch or shell script.

 
loading table of contents...