BID210: Using Sybase IQ as a Report Server for Oracle

1. BID210: Using Sybase IQ as a Report Server for Oracle John Barton Principal Systems Consultant john.barton@sybase.com August 15-19, 2004

2. The Enterprise. Unwired.

3. The Enterprise. Unwired. Industry and Cross Platform Solutions Manage Information Unwire Information Unwire People • Adaptive Server Enterprise • Adaptive Server Anywhere • Sybase IQ • Dynamic Archive • Dynamic ODS • Replication Server • OpenSwitch • Mirror Activator • PowerDesigner • Connectivity Options • EAServer • Industry Warehouse Studio • Unwired Accelerator • Unwired Orchestrator • Unwired Toolkit • Enterprise Portal • Real Time Data Services • SQL Anywhere Studio • M-Business Anywhere • Pylon Family (Mobile Email) • Mobile Sales • XcelleNet Frontline Solutions • PocketBuilder • PowerBuilder Family • AvantGo Sybase Workspace

4. Moving Oracle Reporting into Sybase IQ • Migrate DDL • Initial Data Load • Incremental Data Loads • Reporting SQL

5. Migrating DDL from Oracle to Sybase IQ The first step is to convert DDL from Oracle to Sybase IQ Two recommended methods for migrating DDL • Oracle DDL Scripts Data Type Mapping Oracle to Sybase IQ – next slide Search and Replace in a text editor • PowerDesigner Model Migration • Use PowerDesigner to reverse engineer the DDL • Change the Target Database from Oracle to Sybase IQ • Verify Translation

6. Data Type Mapping Oracle to Sybase IQ Use IQ 12.5 ESD 11 or higher with LOB

7. Using a Text Editor to Migrate DDL sed – UNIX utility for search and replace Example: Replace number(1) with tinyint sed 's/number(1)/tinyint/' ddl_1.sql > ddl_2.sql Textpad – http://www.textpad.com/ great text editor for full control over search and replace

8. Using PowerDesigner to Migrate DDL 1. Reverse Engineer Oracle Database DDL Script ODBC DSN 2. Validate Model 3. Change the Target Database Current: Oracle New: Sybase IQ

9. PowerDesigner DBMS Resource Editor Oracle 8, 9, 10 Sybase IQ 12.5 Source Database (PhysDataType) PowerDesigner Internal Data Type Target Database (AMCDDataType) • Customize DBMS Using Resource Editor • This provides granular control on how data types are converted between source and target databases. • DBMS Properties can be customized (edited) • Click on Tools -> Resources -> DBMS Create a new copy of the DBMS definition file.

10. Source Data Type Mapping to Internal Types

11. Target Data Type Mapping from Internal Types

12. Other DDL Mapping Considerations • Table of data types and mapping • Trailing space issue In Oracle Trailing spaces matter • Data Type mapping issues • Understanding Data Domains Select max(col_x), min(col_x) from table_z Select count(distinct col_x) from table_z

13. Extracting Data from Oracle • Using Sybase Direct Connect for Oracle • Using SQLPlus for Data Extraction

14. Oracle Database DirectConnect for Oracle Oracle Informix MS SQL Server DB2 UDB DB2 400 ODBC Mainframe Sources Non-Sybase Database Sybase IQ DirectConnect Loading Directly from Oracle Sybase Enterprise Connect Product Family DirectConnect for Oracle DirectConnect for Other Supported Databases

15. Loading Sybase IQ From Oracle 1 2 4 3 Oracle Database Client Application DirectConnect for Oracle Sybase IQ • Sequence Steps • A Client application connects to the Sybase IQ Server and issue the insert from location command • The IQ Server opens an Open Client connection to the DirectConnect for Oracle Access Service • The DirectConnect Access Service opens a native OCI connection to the Oracle Server • The IQ Server sends the Oracle SQL Select statement from the insert from location command to the DCO Access Service

16. 1 2 4 3 5 Oracle Database 7 6 Client Application DirectConnect for Oracle Sybase IQ 8 Loading From Oracle – Continued Sequence Steps - continued 5. The DCO Access Service passes the SQL statement to the Oracle Server 6. The Oracle Server executes the SQL statement and returns the result set to the DCO Access Service 7. The DCO Access Service translates the Oracle result set into an Open Client result set and forwards it to the IQ Server 8. The IQ Server loads the result set

17. Sybase IQ Syntax to Load from a Remote Table Connect to Sybase IQ and issue the following command INSERT INTO OWNER.TABLE-NAME (COLUMN LIST) LOCATION '<DCO SERVER>. <DB NAME>' {ORACLE SQL STATEMENT}; The ORACLE SQL STATEMENT inside the curly brackets can contain any valid SQL in native ORACLE syntax which returns a relational result set. This SQL gets passed directly through to the ORACLE Server without modification.

18. DirectConnect – Load Performance Customer Testimonial “We run batch processes at night that use the LOCATION clause to insert data from Oracle into Sybase IQ” “We are generally inserting about 1 million rows into tables that contain 22 to 34 columns. The insert for each table takes about 5 minutes. This is on a 2 CPU server with 4 GB memory. We also load very similar data from a file using the IQ LOAD TABLE command which takes about 60 seconds” “If the volume of data is low (a couple million rows or less), then using the LOCATION clause to get Oracle data into Sybase IQ is definitely the way to go because of the ease of use and convenience” Remember to consider total time = extract, transfer and load

19. Sybase IQ Oracle Database SQLPlus SQLPlus File System File System Extracting Data from Oracle Using SQLPlus A SQL Plus script can be used to extract data from Oracle Database • The results are written to a file or named pipe in delimited format • IQ can load from a file or named pipe which is feed by SQLPlus extract • Limited only by Oracle SQL Capabilities Gather Timings by running SQLPlus from local and remote hosts Measure Extract Speed of local transfer vs remote transfer Network overhead involved in the network transfer Network transfer overhead = Remote transfer time - Local transfer time

20. SQLPlus Extraction Script A SQL Plus script --Set the line size to accommodate the longest possible line. SET LINESIZE 136 --Turn off all page headings, column headings, etc. SET PAGESIZE 0 SET FEEDBACK OFF SET TERMOUT OFF SPOOL /stage/emp_data.csv SELECT TO_CHAR(employee_id) || ‘,’ || employee_name || ‘,’ || TO_CHAR(employee_hire_date,’YYYYMMDD’) || ‘,’ || TO_CHAR(employee_termination_date, ‘YYYYMMDD’) || ‘,’ || TO_CHAR(employee_billing_rate) || ‘,~’ FROM employee; SPOOL OFF

21. Load Processing Techniques • Initial Load • Ongoing Load • Incremental Load Techniques • Converting SQL Loader Scripts

22. Dimension Type Change Processing History Tracking Static None None Slowly Growing New records added None Type 1* Slowly Changing, Update with Changes Content updated None Type 2* Slowly Changing, Versioning of Changes Content updated, new records added Full Type 3* Slowly Changing, Keep Current and Previous values Content updated Partial ETL Examples - Managing Data Loads Tables and Dimensions can be categorized as follows: * Dimensions as defined by Ralph Kimball

23. Dimension Type Change Processing History Tracking Static None None Slowly Growing New records added None Slowly Growing Dimensions or Tables CREATE TABLE CUST ( CUST_ID INTEGER, FIRST_NAME char(30), LAST_NAME char(30), ADDRESS char(60), CITY char(30), STATE char(2), ZIP char(5) ); Load Table or INSERT LOCATION Used interchangeably LOAD TABLE CUST ( CUST_ID '|', FIRST_NAME '|', LAST_NAME '|', ADDRESS '|', CITY '|', STATE '|', ZIP '|‘ ) from 'C:\\Temp\\techwave\\cust.txt' quotes off escapes off preview on row delimited by '\n'; INSERT INTO CUST (CUST_ID, FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE, ZIP) LOCATION ‘SERVER.DBNAME’ {SELECT CUST_ID, FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE, ZIP FROM CUST};

24. Dimension Type Change Processing History Tracking Type 1 Slowly Changing, Update with Changes Content updated None Loading Type 1 Dimension Tables INSERT INTO STAGE_SUPPLIER ( S_NAME, S_ADDRESS, S_NATIONKEY, S_PHONE,S_ACCTBAL,S_COMMENT) LOCATION ‘SERVER.DBNAME’ { SELECT S_NAME, S_ADDRESS, S_NATIONKEY, S_PHONE,S_ACCTBAL,S_COMMENT FROM SUPPLIER}; Load Staging Table UPDATE SUPPLIER SET S_ACCTBAL = STAGE_SUPPLIER.S_ACCTBAL FROM STAGE_SUPPLIER, SUPPLIER WHERE STAGE_SUPPLIER.S_NAME = SUPPLIER.S_NAME AND STAGE_SUPPLIER.S_ADDRESS = SUPPLIER.S_ADDRESS AND STAGE_SUPPLIER.S_PHONE = SUPPLIER.S_PHONE AND STAGE_SUPPLIER.S_ACCTBAL != SUPPLIER.S_ACCTBAL Update Existing Insert New INSERT INTO SUPPLIER SELECT STAGE_SUPPLIER.* FROM STAGE_SUPPLIER WHERE STAGE_SUPPLIER.S_NAME NOT IN (SELECT SUPPLIER.S_NAME FROM SUPPLIER WHERE STAGE_SUPPLIER. S_NAME = SUPPLIER. S_NAME)

25. Dimension Type Change Processing History Tracking Type 2 Slowly Changing, Versioning of Changes Content updated, new records added Full Loading Type 2 Dimension Tables INSERT INTO STAGE_SUPPLIER (S_NAME, S_ADDRESS, S_NATIONKEY, S_PHONE,S_ACCTBAL,S_COMMENT) LOCATION ‘SERVER.DBNAME’ { SELECT S_NAME, S_ADDRESS, S_NATIONKEY, S_PHONE,S_ACCTBAL,S_COMMENT FROM SUPPLIER}; Load Staging Table UPDATE SUPPLIER SET S_CURR_FLAG = ‘N’ FROM STAGE_SUPPLIER, SUPPLIER WHERE STAGE_SUPPLIER.S_NAME = SUPPLIER.S_NAME Update Existing Insert INSERT INTO SUPPLIER SELECT STAGE_SUPPLIER.*, ‘Y’ FROM STAGE_SUPPLIER

26. Generating Sequence Numbers for Keys Adding Sequence Numbers / Generating Warehouse Keys CREATE VARIABLE suppkey INTEGER; SELECT isnull(max(S_SUPPKEY),0) INTO suppkey FROM SUPPLIER; INSERT INTO SUPPLIER SELECT number(*)+suppkey, S_NAME, S_ADDRESS, S_NATIONKEY, S_PHONE, S_ACCTBAL, S_COMMENT FROM STAGE_SUPPLIER; FIND MAX KEY VALUE CREATE TABLE STAGE_SUPPLIER ( S_NAME char(25), S_ADDRESS varchar(40), S_NATIONKEY integer, S_PHONE char(15), S_ACCTBAL double precision, S_COMMENT varchar(101) ); number(*) Function: Sequential numbers 1 to N LOAD DATA

27. Why Use Sybase IQ over Oracle • ANSI SQL • Reduced Administration • Reduced Ongoing Tuning • Data Storage Simplified • Multi User Performance • Hardware Costs • IQ Indexing outperforms Oracle Indexing

28. Changing Tools from Oracle to Sybase IQ Query Tools • SQL • Standard SQL 92 Entry Level Compliant • ODBC Business Intelligence Tools • Business Objects • Crystal Reports • Cognos • MicroStrategy • Brio • MS Access/Excel