Create database tables - assure_dq - 10.1.0

Decide whether to use the custom or express procedure to create database tables. The custom procedure typically requires a database administrator; an application server administrator typically performs the express procedure.

Custom Setup

The custom setup procedure requires the database administrator to edit and run scripts that create the database tablespaces and tables. By editing the scripts, the DBA can control tablespace allocation and create database tables across multiple physical disks and directories. This is desirable in most production environments to balance the I/O load and allow for expansion of the database over time.

During application installation, the system administrator runs another script to initialize the database and populate it with base data.

To set up database tables for a Precisely application, follow the appropriate procedure below:

• Oracle custom procedure
• SQL Server custom procedure

Express Setup

The express procedure creates the tables, initializes the database, and populates the database with a single script. To follow the express setup procedure, see “Populate the Database”.

Follow the procedure in this section to perform custom setup of Oracle database tables. To set up the database tables, you will run the following scripts:

• create-tablespaces—This script creates the storage area for the Precisely application database tables. Decide how to balance the I/O load across physical disks and change the script, accordingly.
• create-tables—This script creates the Precisely application database tables.

To edit and run the scripts, complete the following steps:

Follow the procedure in this section to perform custom setup of SQL Server database tables. To set up the database tables, you will run the following scripts:

• create-tablespaces—This script creates the storage area for the Precisely application database tables. Decide how to balance the I/O load across physical disks and change the script, accordingly.
• create-tables—This script creates the Precisely application database tables.

To edit and run the scripts, complete the following steps:

1. Open <install_folder>/Infogix/db/<product>/schema/sqlserver/advanced.
2. Copy the create-tables and create-tablespaces scripts for your database to a local directory on the computer where the database resides.
3. Edit the create-tablespaces script.

   The create-tablespaces script creates the storage area for the Precisely application database tables. Decide how to balance the I/O load across physical disks and change the script, accordingly. See sample code below:

   
   ALTER DATABASE " " 
     ADD FILEGROUP "_SECURITY"
   GO
   ALTER DATABASE " " ADD FILE
   (
     NAME = _SECURITYFILE,
     FILENAME = '_SECURITY.ndf',
     SIZE = 50MB, MAXSIZE = 2GB, FILEGROWTH = 50MB
   )
   TO FILEGROUP _SECURITY
   GO
         

   For each tablespace, replace the following variables, including the @ symbols:

   • @DATABASE_NAME@
   • @TABLESPACE_PREFIX@
   • @TABLESPACE_DATAFILE_DIR@

   For @DATABASE_NAME@, substitute the name of the database instance created for your Precisely application in step 3 SQL Server Initial Setup. For @TABLESPACE_PREFIX@, substitute the name or prefix for the Precisely application. This prefix must match the value for the TABLESPACE_PREFIX property in the database.properties file. The following prefixes are examples:

   • For Assure DQ, use IA
   • For ER, use ER
   • For Insight, use II
   • For Perceive, use IV
   Important: The tablespace prefix must be upper case.

   For @TABLESPACE_DATAFILE_DIR@, substitute the full path of the directory where you want to create space for the table. Use \\ (two backslashes) to separate folders and to indicate the end of the path.

4. Edit the create-tables script by replacing @TABLESPACE_PREFIX@ throughout the script with the same tablespace prefix specified in the create-tablespaces script above. The tablespace prefix variable is highlighted in the following sample code from the create-tables script:

   
   CREATE TABLE "PERMISSION" (
     "PERMISSIONPK" NUMERIC(20) NOT NULL,
     "PERMISSIONNAME" NVARCHAR(255) NOT NULL,
     "PERMISSIONTYPE" NVARCHAR(20) NOT NULL,
     "DISPLAYNAME" NVARCHAR(255) NULL,
     "DNRESOURCEBUNDLE" NVARCHAR(255) NULL,
     "DNRESOURCEKEY" NVARCHAR(40) NULL,
     "OWNERTABALIAS" NVARCHAR(40) NULL,
     "OWNERTABFK" NUMERIC(20) NULL,
     "PARENTPERFK" NUMERIC(20) NULL
   )
   ON "_SECURITY"
   GO
   CREATE INDEX "PER_PARENT_FK_IDX" on "PERMISSION" (
     "PARENTPERFK"
   )
   ON "_SECURITY"
   GO
             

5. For @DATABASE_NAME@, substitute the name of the database instance created for your Precisely application in step 3 SQL Server Initial Setup.
6. Run the create-tablespaces script.

   Using the SQL server command line processor, run the script by entering a command in the following format:

   
   osql -U <user> -P <password> -S <host> -d <dbname> -i create-tablespaces.sql -I
             

   where <user> and <password> are the SQL Server database administrative user ID and password, <host> is the name of the server on which the database is installed, and <dbname> is the name of the database instance for the application.

7. Run the create-tables script.

   Using the SQL server command line processor, run the script by entering a command in the following format:

   
   osql -U <user> -P <password> -S <host> -d <dbname> -i create-tables.sql -I
             

   where <user> and <password> are the Precisely application database user ID and password created in step 3 SQL Server Initial Setup, <host> is the name of the server on which the database is installed, and <dbname> is the name of the database instance for the application.

8. Proceed to Populate the Database.