This section lists the commands needed to create a database and load the default Data Definition Language (DDL). The export DDL is generated by tools provided with Waveset to match the current export schema.
The create_warehouse scripts are located in the $WSHOME/exporter directory. Waveset also includes corresponding drop_warehouse scripts in the same directory.
Execute a script similar to the following as the system DBA. Be sure to create the idm_warehouse database and the idm_warehouse/idm_warehouse user before running the script.
CONNECT TO idm_warehouse USER idm_warehouse using ’idm_warehouse’ CREATE SCHEMA idm_warehouse AUTHORIZATION idm_warehouse GRANT CONNECT ON DATABASE TO USER idm_warehouse
To load the DDL, add the following line to the %WSHOME%\exporter\create_warehouse.db2 file:
CONNECT TO idm_warehouse USER idm_warehouse using ’idm_warehouse’
Then run the following command (assuming a Windows DB2 server):
db2cmd db2setcp.bat db2 -f create_warehouse.db2
Execute a script similar to the following as the system DBA.
# Create the database (Schema in MySQL terms) CREATE DATABASE IF NOT EXISTS idm_warehouse CHARACTER SET utf8 COLLATE utf8_bin; # Give permissions to the "idm_warehouse" userid logging in from any host. GRANT ALL PRIVILEGES on idm_warehouse.* TO idm_warehouse IDENTIFIED BY ’idm_warehouse’; # Give permissions to the "idm_warehouse" userid logging in from any host. GRANT ALL PRIVILEGES on idm_warehouse.* TO idm_warehouse@’%’ IDENTIFIED BY ’idm_warehouse’; # Give permissions to the "idm_warehouse" user when it logs in from the localhost. GRANT ALL PRIVILEGES on idm_warehouse.* TO idm_warehouse@localhost IDENTIFIED BY ’idm_warehouse’;
To load the DDL, execute the following command:
# mysql -uidm_warehouse -pidm_warehouse -Didm_warehouse < create_warehouse.mysql
Execute a script similar to the following as the system DBA.
-- Create tablespace and a user for warehouse CREATE TABLESPACE idm_warehouse_ts DATAFILE ’D:/Oracle/warehouse/idm_warehouse.dbf’ SIZE 10M AUTOEXTEND ON NEXT 10M DEFAULT STORAGE (INITIAL 10M NEXT 10M); CREATE USER idm_warehouse IDENTIFIED BY idm_warehouse DEFAULT TABLESPACE idm_warehouse_ts QUOTA UNLIMITED ON idm_warehouse_ts; GRANT CREATE SESSION to idm_warehouse;
To load the DDL, execute the following command
sqlplus idm_warehouse/idm_warehouse@idm_warehouse < create_warehouse.oracle
Execute a script similar to the following as the system DBA. Uncomment lines as necessary.
CREATE DATABASE idm_warehouse GO --For SQL Server authentication: -- sp_addlogin user, password, defaultdb--For Windows authentication: -- sp_grantlogin <domain\user> --For SQL Server 2005: --CREATE LOGIN idm_warehouse WITH PASSWORD = ’idm_warehouse’, DEFAULT_DATABASE = idm_warehouse sp_addlogin ’idm_warehouse’, ’idm_warehouse’, ’idm_warehouse’ USE idm_warehouse GO --For SQL Server 2005 SP2 create a schema - not needed in other versions: --CREATE SCHEMA idm_warehouse --GO --For SQL Server 2005 SP2 use CREATE user instead of sp_grantdbaccess --CREATE USER idm_warehouse FOR LOGIN idm_warehouse with DEFAULT_SCHEMA = idm_warehouse sp_grantdbaccess ’idm_warehouse’ GO
To load the DDL, execute the following command:
osql -d idm_warehouse -U idm_warehouse -P idm_warehouse < create_warehouse.sqlserver