Create DB2 target schema's

This section outlines the steps for creating the BI Data Warehouse Databases, i.e. the target Databases for Staging, Central and Datamarts.

It is important to note that the Staging and Central tables should reside in the same database. For the purpose of this document, the Staging and Central tables are stored in the Central, i.e. CuramDW, database. Failure to have the Staging and Central tables co-located in the same database will result in errors when attempting to execute the Central ETL processes.

Follow these steps to create the required Databases:

  1. In order to change the language used in localized property names which is defaulted to English, please modify the Component.locale.order.installedLanguage variable in BIApplication.properties and also modify the language code in the 3 initialdata.sql files (Please refer to Appendix L on Globalization for full details)
  2. Update all Properties in BIBootstrap.properties, which can be found at ..\Reporting\project\properties. Ensure the database names and user names match your project naming standards. Each name will be a database:
    • staging.db.name=CuramDW - this will contain the Staging Database Objects
    • central.db.name=CuramDW - this will contain the Central Database Objects
    • centraldm.db.name=CuramDM - this will contain the Datamart Database Objects
    • dmdemodata.db.name=CuramDMO - this will contain the Demo Data Database Objects
    • design.db.name=CuramDW - this will be used as the WAS execution database
  3. All passwords are stored as encrypted strings in the BIBootstrap.properties file. Run this command to encrypt your passwords, replacing password with your own password:
    • build encrypt.password -Dpassword=password
  4. Copy and paste the encrypted string that the command returns into the password properties in the BIBootstrap.properties file.
  5. Start a command prompt and navigate to ..\Reporting\components. Run the build db2.create.database command to create the BI Data Warehouse Databases. Please note that the Staging and Central Tables will be deployed on to the same CuramDW Database, and the Datamart Tables will be deployed onto a separate CuramDM Database. This command takes these parameters:
    • Ddatabase.name - This specifies the name of the Database that will be created
    • Ddb2.drive - This specifies the drive
    • Ddb2.dir - This specifies the path to the SQLLIB folder
    • Ddatabase.userid - This specifies a Database Super User Name who has permission to create a Database
    • Ddatabase.userpassword - This specifies the Password for the User Name, which should be encrypted
    Here are sample commands to create the CuramDW and CuramDM Databases. These commands contain sample values – please set them as specified by your own project standards. If required, the commands can be used to create the Demo Data Database, and the Execution Database by changing the Database Names and Passwords:
    • build db2.create.database -Ddatabase.name=CuramDW -Ddb2.drive=c:\ -Ddb2.dir=C:\IBM\SQLLIB -Ddatabase.userid=db2admin -Ddatabase.userpassword="/B22j7ZBq+gjuWdxwts++A=="
    • build db2.create.database -Ddatabase.name=CuramDM -Ddb2.drive=c:\ -Ddb2.dir=C:\IBM\SQLLIB -Ddatabase.userid=db2admin -Ddatabase.userpassword="/B22j7ZBq+gjuWdxwts++A=="
  6. Run the build db2.create.role command to create the role which contains the required permissions to use the above Databases. This command takes these parameters:
    • Ddatabase.name - This specifies the name of the Database that the role will be created for
    • Dcreate.db2role - This specifies that the role is to be created
    • Denvironment.db2.dba.userid - This specifies the User Name that will be connecting to the Database when running ETL's
    • Denvironment.db2.dba.password - This specifies the Password for the User Name, which should be encrypted

    Please note that a Database User cannot grant a role to itself.

    Here are sample commands to create the roles for the Users of the CuramDW and CuramDM Databases. These commands contain sample values – please set them as specified by your own project standards. Also, the roles that they create are only intended as a quick starting point for a Development Environment – please apply your own Database Security Policies when configuring your Databases:
    • build db2.create.role -Ddatabase.name=CuramDW -Dcreate.db2role=true -Denvironment.db2.dba.userid=db2user -Denvironment.db2.dba.password="/B22j7ZBq+gjuWdxwts++A=="
    • build db2.create.role -Ddatabase.name=CuramDM -Dcreate.db2role=true -Denvironment.db2.dba.userid=db2user -Denvironment.db2.dba.password="/B22j7ZBq+gjuWdxwts++A=="
Parent topic: DB2 Environment