Moving Data and Other Planning Considerations

1. Moving Data and Other Planning Considerations

2. Overview • Repository and Schema Management • DATAEXP and DATAIMP Utilities • Planning Considerations • Managing User Accounts • Database Access Control

3. Lesson: Repository and Schema Management • Repository Overview • Repository Migration • Utilities used in Repository Migration • Comparing the Migration Process Between Siebel 7.x and Siebel 6.x

4. Repository Overview • Core of the Siebel application • Contains definitions for the physical, logical, and presentation layers of your Siebel implementation • Repository is manipulated using Siebel Tools • Siebel provides a “default” Repository and you have likely customized the Repository to meet your business requirements • Customizations may include schema extensions, new business objects, custom code (eScript or VBScript), or new screens/views

5. Repository Overview (…continued) • Logical schema is defined in the Repository • Repository includes a logical representation of Tables, Columns, and Indexes • Typically extend the schema by using Siebel Tools to modify the logical schema • New Columns are named X_… to avoid future conflicts. Definition stored in S_COLUMN • New Tables are named CX_… to avoid future conflicts. Definition stored in S_TABLE • New Indexes include …_X… somewhere in the name to avoid future conflicts. Definition stored in S_INDEX • Utility to synchronize the logical and physical schemas as part of the migration process

6. Repository Migration • Siebel provides utilities to migrate the Repository between Enterprises (ie., environments) • One Repository “master” – typically in the Development environment • Process migrates the Repository and not reference data (LOVs) or transactional data (Accounts and Contacts) • Customer determines source and target Siebel Enterprises when performing a migration • An Enterprise can have 1+ Repositories. Might keep multiple versions of Repository in PROD as a precaution

7. Synchronizes logical and physical schemas DDLSYNC Export Exports entire Repository to a single binary file Increments the schema version number Bumpver Imports entire Repository from a binary file Import Utilities Used in Repository Migration Process hasn’t really changed between Siebel 6.x and 7.x (only the utilities have changed)

8. Comparing the Migration Process Between Siebel 7.x and Siebel 6.x • Multiple GUI tools installed with the DB Server scripts • Export, Import, and DDLSync (includes Bumpver) are each done with separate GUI requests • DDLSync is with “Run Database Utilities” and includes Unicode and Non-Unicode schemas Siebel 7.x • DEV2PROD script in \bin folder of each Siebel Server • DEV2PROD not limited to migrating a Repository from DEV to PROD Enterprises – think of it as source  target • Performs Export, Import, DDLSync, and Bumpver steps in a single script • Customer specifies source and target systems Siebel 6.x

9. Lesson: DATAEXP and DATAIMP Utilities • Overview of DATAEXP and DATAIMP • Review of DATAEXP and DATAIMP • Limitations of DATAEXP and DATAIMP • DATAEXP • Preparing for DATAEXP • DATAIMP • Considerations for Using DATAEXP and DATAIMP

10. Overview of DATAEXP and DATAIMP • DATAEXP and DATAIMP are Siebel utilities • Used to export (DATAEXP) and import (DATAIMP) data • Installed in <siebsrvr>\bin on every Siebel Server • Available on Siebel 5.x, 6.x, and 7.x • Additional options available in recent Siebel versions (e.g. 7.7) • You are indirectly familiar with them (hint: Repository import and export!) • Our primary tool to migrate non-Repository data from Oracle to SQL Server

11. Review of DATAEXP and DATAIMP • DATAEXP and DATAIMP are database-independent utilities • Easy to use • Operate from command line, so can be included in scripts • Can preserve Siebel ROW_ID values • Good performance overall, and can be setup to run parallel export and import tasks • You can use them to support your Platform Migration

12. Limitations of DATAEXP and DATAIMP • Cannot be used to move data between different versions of Siebel (e.g. 7.5.3 to 7.7) • May not provide optimal export or import performance for very large Tables

13. DATAEXP • You specify the desired Tables and rows at runtime • Resulting data is exported to a single, binary file • Some options not available prior to Siebel 7.7 • dataexp /h to see all command-line switches • All data is preserved including ROW_ID, CREATED, LAST_UPD etc.

14. Preparing for DATAEXP • Reaffirm that source and target schemas are identical • Truncate EIM Tables where appropriate • Understand your data • Identify largest Tables by total rows and by total Mbytes • May need to temporarily collect statistics in Oracle • Exclude Repository Tables from DATAEXP • Determine if there is one process or many/parallel processes • Prepare a list of Table names for each process

15. DATAIMP • Reads the binary file produced by DATAEXP • Expects the identical Table structures in target database • Some options not available prior to Siebel 7.7 • dataimp /h to see all command-line switches • All data is preserved including ROW_ID, CREATED, LAST_UPD etc.

16. Considerations for Using DATAEXP and DATAIMP • All non-Repository data in target/ new Enterprise database needs to be truncated prior to moving • No changes to the File System or its contents • Large and Chained rows > 8K (Oracle) may need to be handled with extension tables • Script to check consistency in Oracle prior to dataexp/dataimp • Large Table move planning must be done • DATAEXP and DATAIMP can be run in parallel

17. Lab 3: Running DATAEXP In this lab, you will: • Prepare a Table List • Run DATAEXP • Review Counts

18. Lab 4: Running DATAIMP In this lab, you will: • Run DATAIMP • Review Counts and audit with DATAEXP counts • Review Results in SQL Server • Review results online in Siebel application

19. Lab Discussion Discuss the following: • User Accounts • Security

20. Lesson: Planning Considerations • Data Type Considerations • Repository and Physical Schema check • Large Rows • Code Page Change • Security

21. Data Type Considerations • Dates • The SQL Server earliest date is 1753-01-01 00:00:00, while the Oracle Server earliest date is January 1 4713BC 00:00:00 • Therefore, ensure that dates fall into this range before importing • Bad Data in Oracle DB • Ensure data type consistency in source database

22. Repository and Physical Schema check • Before starting the migration, a check needs to be performed to ensure the physical database schema matches the Siebel Repository • This ensures the success of the Export/Import processes • Validation is performed with Siebel’s DBCHCK utility

23. Large Rows • Oracle Data Block size is typically 8K, and can “chain” a row across blocks if necessary • SQL Server Data Page size is 8K and cannot be extended, except for Text (LONG) Columns • Beware of a Table with multiple extension columns (X_…) of large width such as VARCHAR(2000) • Critical to do multiple test migrations using all data from existing Production database

24. Code Page Change • May have an existing Siebel database but only by using the wrong code page or collation sequence • Can use the DATAEXP/ DATAIMP process to migrate the data to a new “siebeldb”

25. Security • Manage access to database through user accounts • Control access to data through privileges and roles

26. Lesson: Managing User Accounts • User Accounts in Oracle and SQL Server • Creating User Accounts • Maintaining User Accounts

27. User Accounts in Oracle and SQL Server • In Oracle a username is database system wide. SQL Server uses login accounts to the database system and user accounts for the individual databases • Oracle usernames and SQL Server logins can be operating system authenticated or database authenticated. In addition SQL Server logins could be authenticated by the network domain • In SQL Server a user account has to be created in every database that a login needs access to and can be named different from the login name

28. Creating User Accounts • Comparison of the syntax to create a user and the associated properties

29. Maintaining User Accounts • Options in maintaining user accounts are – Data dictionary information on users in Oracle can be viewed using DBA_USERS and DBA_TS_QUOTAS SQL Server system stored procedures sp_helplogins, sp_change_users_login and sp_helpuser can be used to view login and user information

30. Lesson: Database Access Control • Managing Privileges • Managing Roles • User Roles

31. Managing Privileges Oracle and SQL Server control access and activity within the database using system and object privileges ALTER DATABASE and GRANT are examples of system privileges while object privileges can be SELECT, INSERT, UPDATE, DELETE, etc. Oracle and SQL Server use GRANT statement to give privileges and REVOKE statement to remove privileges. In addition SQL Server has DENY statement to suspend privileges Use the system stored procedure sp_helprotect to list permissions on objects and statements in SQL Server

32. Managing Roles • Oracle and SQL Server offer system defined roles with predefined privileges and user defined roles • The SQL Server system defined roles are of two categories – fixed server roles which are defined for the database instance and fixed database roles whose scope is limited to a database • sysadmin fixed server role is equivalent to Oracle’s DBA role • In Oracle there is a single DBA role that has database instance wide privileges spanning all schemas. In SQL Server administrative privileges can be limited to individual databases by the use of fixed database roles

33. User Roles In Oracle and SQL Server the creation and use of roles differ Both Oracle and SQL Server use GRANT and REVOKE statements to control permissions on roles Oracle and SQL Server offer ways to modify and drop user roles Roles granted to Oracle users need to be enabled through the users login properties or using SET ROLE command. In SQL Server all granted roles are enabled by default except with application roles which need to be enabled using sp_setapprole

34. Review • Repository and Schema Management • DATAEXP and DATAIMP Utilities • Planning Considerations • Managing User Accounts • Database Access Control