Home

UnityJDBC User Manual

1. en dear 958 a 1select from OrderDB NATION N INNER JOIN PartDB Nation N2 EN TABLE 2 ON N n_nationkey N2 n_nationkey G3 CUSTOMER 7 H E UNEMEM s H O NATION 6 H ORDERS i gt CB PART H O PARTSUPP H E REGION ee Ie freee ORR FO lod E O PROCEDURE N_NATIONKEY L N_REGIONKEY NC PartDB 0 ALGERIA 0 final accounts wake quickly special 2G TABLE 1 ARGENTINA 1 idly final instructions cajole stealthil 2 BRAZIL 1 always pending pinto beans sleep si H E CUSTOMER 3 CANADA 1 foxes among the bold requests a UNEITEM 4 EGYPT 4 pending accounts haggle furiously H E NATION 5 ETHIOPIA 0 fluffily ruthless requests integrate fl H O ORDERS 6 FRANCE 3 even requests detect near the pend amp PART 7 GERMANY 3 blithely ironic foxes grow quickly p H O PARTSUPP 9 8 INDIA 2 ironic packages should have to chia ccna I 99 INDONESIA 2 unusual excuses are quickly request v es II TT a PON OS RazorSQL Executing a Multiple Database Join with UnityJDBC A few possible setup errors and their resolution 1 Unable to make a connection If you do not type the connection string correctly or you have not put the XML files in the right location you will get the error below To fix verify the location of the files ERROR An error occurred while trying to make a connection to the database JDBC URL jdbcunity c temp unityjdbc UnityDemo xm to retrieve connection information at c
2. Calculate the number of countries in each region Only return a region and its country count if it has more than 4 countries in it Order by regions with most countries ELECT R r_regionkey R r_name COUNT N n_nationkey ROM OrderDB Nation as N OrderDB Region as R HERE N n_regionkey R r_regionkey ROUP BY R r_regionkey R r_name I AVING COUNT N n_nationkey gt 4 RDER BY COUNT N n_nationkey DESC INSERT Statement The INSERT statement supported by UnityJDBC has the following syntax OomQattn INSERT INTO lt tbl_name gt lt col_name gt VALUES lt exprList gt Specifying column names is optional An example is below INSERT INTO mydb Customer id firstname lastname street city VALUES 52 Fred Jones Smith Lane Chicago 64 Supported SQL Syntax UnityJDBC also supports INSERT INTO SE H ECT with the following syntax INSERT INTO lt tbl_name gt lt col_name gt VALUES lt exprList gt SELECT lt query gt This is useful for storing query results into another table Note that this table and all its column must already exist or an error will be returned Here is an example INSERT INTO emptydb customer SELECT FROM mydb customer UPDATE Statement The UPDATE statement supported by UnityJDBC has the following syntax UPDATE lt tbl_name gt SET co
3. Hame sybase Source Type Schema file sybase xml ee URL idbc sybase Tds lok ubc ca 5000fpch Generic JDBC idbc sybase Tds cssql ok ubc ca pc Generic ODBC Driver com sybase jdbc4 jdbc SybDriver HSQLDB User rlawrenc IBM DB2 Microsoft SQL Server Password ve sss MongoDB Catalog MySQL Oracle Schema PostgreSQL Tables included SerniceNow ES Sybase Tables excluded Vectorwise Statistics Row Counts v Add Source Progress 9of9 Success Messages Computing table statistics SELECT COUNT FROM tpch dbo region Done processing table region Table supplier Schema dbo Catalog tpch Computing table statistics SELECT COUNT FROM tpch dbo supplier Done processing table supplier CE il Ill 4 Adding a Sybase Data Source to the Data Virtualization Multiple Database Virtualization and Querying for MongoDB Using UnityJDBC SourceBuilder it is possible to build a data virtualization of one or more MongoDB databases collections with any other database system Note that a MongoDB JDBC driver is built into UnityJDBC and can be used separately from UnityJDBC directly For more information go to http www unityjdbc com mongojdbc To add a MongoDB data source the following information is used 1 JDBC Driver class name mongodb jdbc MongoDriver 2 JDBC URL jdbc mongo lt server gt 1521 lt database gt An example of adding a MongoDB d
4. o tpch Customer o tpch Lineitem tpch Nation o tpch Orders EXECUTE Clear o tpch Part o tpch Part2 Messages o F tech Partsupp o tpch Region Executing query o tpch Supplier Query result 1 at time 99 o E tpch tablet Query result 10 at time 100 amp El tpchtable2 Query result 100 at time 101 Query result 1000 at time 108 lumber of results 1000 Query complete Gi postgres customer lineitem E nation E orders E part partsupp region supplier Results totalOrderPrice 18 2494170 26 4 13 1912842 87 c_custkey numOrders RL as c a A e in amp in 2495156 37 v Multiple Database Query Example that Joins Table in MySQL with a Table in PostgreSQL d After creating your virtual database you will have one sources xml and an XML schema file for each database These files can be moved to any location and are used to configure UnityJDBC when using it with other applications Using UnityJDBC Now that a virtual database is created there are three general steps to using UnityJDBC common in all cases a Move the sources xml and the XML schema files for the databases into a desired directory For this example the directory is unityjdbc b Put the unityjdbc jar into your CLASSPATH as well as the drivers for each database Some drivers are i
5. 15 Using SourceBuilder Tutorial on Multiple Database Querying File Demonstration Help Databases Virtualized E ECTLI exendedprice P p_retailprice L_partkey FROM OrderDB Lineitem L PartDB Part P PartDB WHERE P p_partkey L _partkey AND L _partkey lt 10 El o F CUSTOMER ES LINEITEM E NATION ORDERS E PART E PARTSUPP F REGION F SUPPLIER OrderDB fatal a Gi Query result 10 at time 176 Query result 100 at time 179 Number of results 257 Query complete Query Exection time is 190 ms F CUSTOMER LINEITEM E NATION F ORDERS E PART E PARTSUPP REGION E SUPPLIER o gt e e gt Ca oe L_EXTENDEDPRICE P_RETAILPRICE L_PARTKEY SourceBuilder Multiple Database Query Example with a Cross Database Join Data Virtualization Using SourceBuilder To build your own data virtualization of multiple data sources create a new sources file by selecting File gt New Source Group and providing a file name the default is sources xml 16 Using SourceBuilder Tutorial on Multiple Database Querying Load Source Group New Source Remove Source Remove Table Refresh Exit Messages elcome To get started either 1 Create your own sources by a File Menu gt New Source Group and create new XML config file b File Menu gt New Source and enter the JDBC connection information
6. Change Alias postgres Driver v PostgreSQL z new UserName rtawrenc O Z OZO O00000 O Password A 7 Auto logon _ Connect at Startup EA Warning Passwords are saved in clear text ok cose test Registering a PostgreSQL Source in SQuirreL SQL 3 Create a Multiple Database Alias Make sure you have registered the UnityJDBC driver during installation Create an alias consisting of virtual sources The name field can be any name It does not have to be virtual If you are using the virtualization embedded into the plugin the URL is jdbc unity virtual If you have previously created a data virtualization using the SourceBuilder utility then the URL should be the file location of the sources file previously created Y UnityJDBC y new User Name Password 7 Auto logon _ Connect at Startup ELTA Warning Passwords are saved in clear text Add Alias Name virtual Driver URL ok cose test Add a Data Virtualization Alias for Multiple Sources in SQuirreL SQL 36 Using UnityJDBC with Query and Reporting Software 4 Add Microsoft SQL Server to Data Virtualization Right click on the root object in the object tree and select Virtualization Add Source User selects the Microsoft SQL Server source to add to the data virtualization Elle Aliases Plugins Session Windows Help E amp Active Session 4
7. Aqua Example Adding a MySQL Database to the Data Virtualization 6 Viewing Data Virtualization of Sources Select Exit to return to the main screen and see data virtualization of MySQL and Microsoft SQL Server sources 45 Using UnityJDBC with Query and Reporting Software File Demonstration Help Databases Virtualized F m 7 E db2_mysql o F Region EE Partsupp E Part2 E Orders ES Lineitem 3 E Part Messages Customer E Supplier Welcome To get started either o Nation 3 a z a E db1_sqlserver 1 Create your own sources by ja D Aa B a File Menu gt New Source Group and create new XML config file o F ORDERS b File Menu gt New Source and enter the JDBC connection information gt LINEITEM for each source o PART o F CUSTOMER Results o F SUPPLIER o F NATION Aqua Example Resulting Data Virtualization of Two Database Sources 7 Multiple Database Query and Cross Database Join Example Type in a cross database query and execute it to view results 46 Using UnityJDBC with Query and Reporting Software File Demonstration Help Databases Virtualized 3 peant rom db1_sqlserver Orders 01 INNER JOIN db2_mysql Orders 02 9 E db2_mysql H ON 01 0_orderkey 02 0_orderkey E 7 Pan Messages Customer E Supplier ecuting query Nation Query result 1 at time 134 TT TTT AAA Number o
8. m 24 200 455M8 E Aqua Example Executing a Multiple Database Join Query and Displaying Results 13 Function and SQL Dialect Translation with Aqua Data Studio Forget what functions you can use on each database No problem UnityJDBC will translate automatically This translation is supported for common databases and can be freely extended by user defined functions and translations for each database dialect File Edit Server Query Automate Tools DBA Tool FRPS FRP ORAS AE EE db1_sqlserver S E Table co E CUSTOMER co E LINEITEM c E NATION E E ORDERS c0 E PART E FE PARTSUPP E FE REGION E E SUPPLIER O3 Procedures 6 03 User Defined Types cr ERModeler Window Help BEEBE m Servers O E unity_multiple Untitled x 18 E PS tpchio alre EG unity_multiple BCHas B t BRYS P hh 5 0 Catalog de B E A S A unity SH 6 e aesa Es amp E Max Results S Database Database unity v Schema db1_sqlserver w Username i select trim N_NAME as n_name left R_NAME 5 as r_name from dbl_sqlserver Nation INNER JOIN db2_mysql Region ON n_nationkey r_reg tea El SE db2_mysdl Lan INS 7 11 2012 12 17 54 AM Script executed No Errors Time 86ms EE Table EB Customer su m amp 5 record s Fetch Data 1ms Sum 0 E Lineitem B E Nation E Orders E Part E
9. MySQL Oracle Schema RLAVVRENC PostgreS L Tables included ServiceNow ES Sybase Tables excluded Vectorwise Statistics Row Counts y Add Source Progress 90f9 Success Messages Done processing table REGION Table SUPPLIER Schema RLAWRENC Catalog null Writing information into XML file Updating sources file SUCCESS Data source successfully added Computing table statistics SELECT COUNT FROM RLAWRENC SUPPLIER Done processing table SUPPLIER Success Adding an Oracle Data Source to the Data Virtualization Multiple Database Virtualization and Querying for PostgreSQL Using UnityJDBC SourceBuilder it is possible to build a data virtualization of one or more PostgreSQL databases with any other database system To add a PostgreSQL data source the following information is used 1 JDBC Driver class name org postgresql Driver 2 JDBC URL jdbc postgresql lt server gt lt database gt user lt userld gt amp password lt password gt An example of adding a PostgreSQL source to a data virtualzation is below 24 Using SourceBuilder Tutorial on Multiple Database Querying U Source Connection Setup Sources file sources xml Ext Hame postgres Source Type Schema file postgres xml Il URL idbe post l db ok ub Apch Generic JDBC idbc postgresdl dh ok ubc catpc Generic
10. virtual Ho e ti amp x 0 4 virtual BES x gt BokhRBaaeax tei Vf Metadata Status Schemas Table Types Data Types Numeric Functions String Functions System Functions Time Date Functions Keywords Value C eclipse workspace UnityJDBC_Live unityjdbc jar isnull gt false UnityJDBC 41 109 ES ransupp getDriverName UnityJDBC Remon allProceduresAreCallable false oe ee allTablesAreSelectable true autoCommitFailureClosesAllResultSets false false dataDefinitionignoredinTransactions false doesMaxRowSizelnciudeBlobs false getCatalogSeparator E getCatalogTerm _ catalog getClientinfoProperties getDatabaseMajorVersion 4 getDatabaseMinorVersion 1 getDefaultTransactionlsolation TRANSACTION_NONE getDriverMajorVersion 4 getDriverMinorVersion 1 getDriverVersion 14 1 109 getExtraNameCharacters LaetidentifierQuoteStrina Select source to add to the virtualization Hane mss Catalog o Schema A A Tables included e Tables excluded gr Statistics Row Counts x Progress 80f8 Success Messages Computing table statistics SELECT COUNT FROM tpch100 dbo SUPPLIER Done processing table SUPPLIER Success 37 Using UnityJDBC with Query and Reporting Software Prompt to add Mi
11. Done processing table table2 Success Multiple Database Virtualization Adding MySQL Database Installation Sources file sources xml exit Hame postgres Source Type Schema file postgres xml Cassandra r Generic JDBC URL idbc postgresql db ok ubc catpch Generic ODBC Driver org postgresal Driver HS LDB IBM DB2 Microsoft SQL Server Password ee s OE MongoDB Catalog MySQL Oracle Schema l PostgreSQL Tables included EE ServiceNow RUS t Sybase Tables excluded 5 Add Source Progress 10 0f 10 Success essages Preparing to add data source User tawrenc Registering driver org postgresql Driver Connecting to database Connection successful uto generated database id 40080408 Database name PostgreSQL 8 able customer Schema null Catalog null Computing table statistics SELECT COUNT Multiple Database Virtualization Adding PostgreSQL Database c Write a query that can join across multiple databases The only difference from standard SQL is that you prefix the table name with the database name Installation File Demonstration Help Databases Virtualized mesa FROM mysql Orders O INNER JOIN postgres customer C ON O 0_custkey C c_custkey GROUP BY c_custkey SELECT c_custkey COUNT o_orderkey as numOrders SUM o_totalprice as totalOrderPrice
12. IS NOT NULL tests if value is NULL IS NOT TRUE FALSE tests if value is true or false Arithmetic Functions and Operators The following mathematical operators are supported Table 6 2 Mathematical Operators Operator Description addition and string concatenation for strings A subtraction division modulus remainder of integer division multiplication The following are a few of the mathematical functions supported A complete list of functions is available on the web site Table 6 3 Mathematical Functions Function Return Example Result Description Type abs x Same as x abs 17 4 17 4 Absolute value ceil x Same ceil 42 8 42 Smallest integer not less than argument as input exp x Same exp 1 0 2 718 exponential as input floor x Same In 2 0 0 69314 natural logarithm as input log x Same log 100 0 2 base 10 logarithm as input power a b double power 9 3 729 a raised to the power of b precision random double random random value between 0 0 and 1 0 precision sqrt x double sqrt 2 0 1 4142 square root precision String Functions The following are a few of the string functions supported A complete list of functions is available on the web site 59 Supported SQL Syntax Table 6 4 String Functions Function Return Example Result Descri
13. sql GI E Example 4 INSERT Unity Parsed sql INSERT INTO mydb Customer id firstname lastname street city VALUES 52 Fred Jones Smith Lane Chicago stmt executeUpdate sql Example 5 INSERT INTO SELECT across databases sql INSERT INTO emptydb customer SELECT FROM mydb customer stmt executeUpdate sql Prove that we transferred the data sql SELECT FROM emptydb Customer rst stmt executeQuery sql printResult rst Note that you can use the by pass feature to execute any statement on a source database that UnityJDBC does not natively support Experimental results show that the by pass features adds insignificant overhead compared to calling the source JDBC driver directly Thus client code only needs to load and use the UnityJDBC driver directly This results in more portable code that can be more easily moved between database systems When UnityJDBC parses the SQL you can use table and field references that are prefixed with the database name This is optional if the table and field names are unique across all databases otherwise the database name is required 31 Multiple Database Programming with UnityJDBC The database name is assigned in the schema file describing the source and does not have to be the same as the system name used by the database system itself That is the name c
14. E Part2 m e Partsupp E E Region B E Supplier O Procedures 4 09 User Defined Types c FS UnityIDBC_cssql bd opener n name r_name ALGERIA AFRIC ARGENTINA AMERI BRAZIL ASIA CANADA EUROP EGYPT MIDDL E Servers Files 15 Projects Aqua Example Performing SQL Dialect and Function Translation EX Grid FE Messages Local Database Servers unity_multiple Catalog unity Database db1_salserver Table CUSTOMER 121 208 455MB ii UnityJDBC Data Virtualization with RazorSQL UnityJDBC allows RazorSQL users to create a virtual data source that may consist of multiple data sources on different servers and platforms The user can enter one SQL query to combine and join information from multiple sources 52 Using UnityJDBC with Query and Reporting Software Benefits UnityJDBC allows RazorSQL to support multiple database queries No data source or server changes are required e UnityJDBC will perform function translation where a user requests a function that is not supported on a certain source This example assumes that the installation of UnityJDBC has already been completed and that the user has already created a data virtualization using the UnityJDBC SourceBuilder The following is an example of creating sources and executing multiple database queries with RazorSQL 1 Installing the Jars Copy the unityjdbc jar into the RazorSQL JRE directory e g C
15. ODBC Driver org postgresal Driver HSQLDB User rlawrenc IBM DB2 Microsoft SQL Server Password e MongoDB Catalog MySQL Oracle Schema PostgreSQL Tables included ServiceNow ES Sybase Tables excluded Vectorwise Statistics Row Counts y Add Source Progress 100f 10 Success Messages Preparing to add data source Registering driver org postgresql Driver E Connecting to database Connection successful Auto generated database id 40080408 Database name PostgreSQL 8 Table customer Schema null Catalog null Computing table statistics SELECT COUNT EDOMm euctormor KI Adding a PostgreSQL Data Source to the Data Virtualization Multiple Database Virtualization and Querying for Sybase Using UnityJDBC SourceBuilder it is possible to build a data virtualization of one or more Sybase databases with any other database system To add a Sybase data source the following information is used 1 JDBC Driver class name com sybase jdbc4 jdbc SybDriver 2 JDBC URL jdbc sybase Tds lt server gt lt port gt lt database gt user lt userid gt amp password lt password gt An example of adding a Sybase source to a data virtualzation is below 25 Using SourceBuilder Tutorial on Multiple Database Querying U Source Connection Setup Sources file sources xml Exit
16. Program Files x86 RazorSQL jre lib ext 2 Setup RazorSQL Start RazorSQL Add a UnityJDBC connection profile under menu Connect ion gt Add Connection Profile onnections SQL Results Tools Code View Window Help A New Tab No Connection De 0 o a o New Tab Share Connection gt PSSev gt 20 a Js Click to Connect to a Database Connect Cui 1 Add Connection Profile Ctri 2 Connect to Built in Database HSQLDB 0 0 Ln Coll Lines1 INSERT WRITABLE Delimiter Export Connection Profiles Import Connection Profiles O 4 amp amp amp EF View SQL History View Query Log RazorSQL Adding a New Connection Profile 3 Setup RazorSQL Part 2 Select OTHER then press the CONTINUE button 53 Using UnityJDBC with Query and Reporting Software Connection Profiles DATABASE TYPE Cache InterSystems DBASE DB2 Derby FileMaker Firebird FrontBase Greenplum H2 HSQLDB HyperSQL Ingres Informix Interbase Microsoft Access Select a database type from the list on the left Microsoft SQL Server Mimer SQL If the database type is not in the list select OTHER MongoDB MySQL Then select the Continue button to enter connection information Netezza OpenBase E Oracle Paradox Pervasive PointBase PostgreSQL SimpleDB Amazon Solid SQLite SQL Azure Sybase ASE Sybase IQ Sybase SQL Anywhere Teradata RazorSQL Adding a
17. SELELI LUN FROM tpch table1 Done processing table table1 gt Table table2 Schema null Catalog tpch Computing table statistics SELECT COUNT FROM tpch table2 Done processing table table2 Success Li Adding a MySQL Data Source to the Data Virtualization There are also several additional features that can be used to control the extraction 1 Schema Specify a schema name or pattern JDBC API to only retrieve tables in the given schema This is especially important for Oracle as by default tables from all schemas will be added to the virtualization Tables included Specify a JDBC API pattern use for wild card character match to indicate which tables should be added to the extraction For example a pattern of R will only add tables that begin with R Tables excluded Specify a Java string pattern use for matching any sequence of characters to indicate what tables to exclude from extraction Each database has certain default exclusion patterns to avoid extracting system tables It is recommended to modify the exclusion pattern if there are any issues extracting too many tables especially system tables for your data source Statistics Statistics collection helps the UnityJDBC optimizer perform more efficient data virtualization queries The default statistics collection is Row Counts which will calculate the number of rows for each extracted table 18 Using SourceBui
18. SeniceNow ES Sybase Tables excluded Vectorwise Statistics Row Counts y Add Source Progress 80f8 Success Messages SELECT COUNT FROM tpch100 dbo REGION Done processing table REGION gt Table SUPPLIER Schema dbo Catalog tpch100 Computing table statistics SELECT COUNT FROM tpch100 dbo SUPPLIER Done processing table SUPPLIER Success Adding a Microsoft SQL Server Data Source to the Data Virtualization Multiple Database Virtualization and Querying for MySQL Using UnityJDBC SourceBuilder it is possible to build a data virtualization of one or more MySQL databases with any other database system To add a MySQL data source the following information is used 1 JDBC Driver class name com mysql jdbc Driver 2 JDBC URL jdbc mysql lt host address gt lt database name gt An example of adding a MySQL source to a data virtualzation is below 22 Using SourceBuilder Tutorial on Multiple Database Querying U Source Connection Setup x Sources file sources xml Exit Hame mysql Source Type Schema file mysql xml Ii URL idbe l i 4 ok ub Apch Generic JDBC idbe mysql fics suse 4 ok ubc caftpe Generic ODBC Driver com mysq jdbe Driver HSQLDB User rlawrenc IBM DB2 Microsoft SQL Server Password e MongoDB Catalog MySQL Oracle Schema PostgreSQL Tables inc
19. UnityJDBC Virtual Connection using OTHER Option 4 Setup RazorSQL Connection Profile When setting up the connection profile the profile name can be anything you wish The driver location is where you just put the unityjdbc jar The driver class is unity jdbc UnityDriver There is no login or password The JDBC URL is the location of the configuration files for the virtual sources In this example we put the files UnityDemo xml UnityDemoOrder xml UnityDemoPart xml that came in the distribution in the directory c temp unity3dbc You will change this to your source files that you built using the SourceBuilder utility 54 Using UnityJDBC with Query and Reporting Software a CONNECTION TYPE Driver Location C Program Files 86 RazorSQL jre lib ext unityjdbcjar Driver Class unity jdbc UnityDriver RazorSQL JDBC Bridge Save Password Y DATABASE INFO Driver is shipped with RazorSQL JDBC URL jdbcunity c temp unityjdbc UnityDemo xml Auto Commit on Off O Smart Commit Read Write Delete SOL Restrictions None e ear O summa Goga Connect at Startup _ RazorSQL Creating a UnityJDBC Virtual Connection 5 RazorSQL Multiple Database Connection and Query Connect to the connection profile You can then see tables from all your virtual sources and built a multiple database query 55 Using UnityJDBC with Query and Reporting Software
20. afer al aja e ae i Objects Ysa Hibernate select trim n_name as trimmedName from MSSQL Nation Y Limit rows immedName from MSSQL Nation Drivers selecttrim c_n selecttrim A selecttrimc 100 la Rows 25 select trim n_name as trimmedName from MSSQL Nation Results MetaData Info Overview koe trimmedNa ALGERIA ARGENTINA BRAZIL CANADA EGYPT ETHIOPIA FRANCE GERMANY INDIA INDONESIA IRAN IRAQ JAPAN JORDAN KENYA EH HtestiMSSQL 1 38 38 No SQL selected for execution Query 1 of 1 Rows read 25 Elapsed time seconds Total 0 026 SQL query 0 006 Building output 0 02 Logs Errors 4 Warnings O Infos 20 An example of SQL Query and Dialect Translation Converting TRIM function for Microsoft SQL tap 3 48 35 PM PDT Server The plugin source code like all of SQuirreL is released under the GNU Lesser General Public License The UnityJDBC virtualization driver is released under a commercial license However the UnityJDBC driver included in the plugin is fully functioning with no time limits allowing an unlimited number of sources and queries The only limita tion is the size of the result set is limited to the first 100 rows Note there is no limit on the number of rows extracted from
21. each source So select count from table with a 1 million row table is fine as it only returns one result row Use LIMIT 100 to get the first 100 results of a query A full version of the UnityJDBC driver can be purchased as www unityjdbc com http www unityjdbc com UnityJDBC Data Virtualization with JasperReports The JasperReports library and community version of JasperReports server does not support data virtualization allowing one SQL query to extract data from multiple databases UnityJDBC can be used with JasperReports to enable this data virtualization which simplifies the construction of reports especially reports that would usually use subreports UnityJDBC Data Virtualization with Aqua Data Studio Aqua Data Studio can query multiple databases with different SQL queries However you cannot query more than one database at the same time in one SQL query UnityJDBC allows a user to write one query to join aggregate and summarize data across any number of databases It also performs translation of SQL dialects and functions Benefits UnityJDBC allows Aqua Data Studio to support multiple source queries 41 Using UnityJDBC with Query and Reporting Software No data source or server changes are required UnityJDBC supports standard SQL including joins group by aggregation LIMIT and ordering where tables may come from one or more sources UnityJDBC will perform function translation where a user requ
22. for each source Results Creating a New Source Group with SourceBuilder Enter Location for Source Group XML Configuration File Filename pouces II Prompt for Name of Sources File Then you will add each one of your sources by providing its JDBC connection information To add a source you need the following information 1 The JDBC Driver class name e g com mysql jdbc Driver 2 The JDBC URL to connect to the source e g jdbc mysql localhost mydb 3 User and password information if not specified in the JOBC URL 17 Using SourceBuilder Tutorial on Multiple Database Querying 4 A unique database name does not have to be the same as the system database name to refer to the data source in your data virtualization An example of adding a MySQL source to the virtual database is below U Source Connection Setup E Sources file sources xml Exit Hame mysql Source Type Schema file mysql xml Cassandra aa 7 a Generic JDBC 4 idbe mysql ics suse 4 ok ubc caftpch Generic ODBC Driver com mysql jdbe Driver HSQLDB User rlawrenc IBM DB2 Microsoft SQL Server Password e MongoDB Catalog MySQL Oracle Schema PostgreSQL Tables included ServiceNow Sybase Tables excluded 5 Vectorwise Statistics Row Counts M Add Source Progress 110f11 Success Messages
23. require setting a large heap space JVM command line parameters 0 Xms500m Xmx500m These parameters set heap space to 500 MB rst stmt executeQuery sql System out println n nTHE RESULTS int i 0 long timeStart System currentTimeMillis 29 Multiple Database Programming with UnityJDBC catch SQLException ex long timeEnd ResultSetMetaData meta rst getMetaData System out println Total columns meta getColumnCount System out print meta getColumnName 1 for int j 2 j lt meta getColumnCount j System out print meta getColumnName 3 System out println while rst next System out print rst getObject 1 for int j 2 j lt meta getColumnCount j System out print rst getObject 3 System out println 1 timeEnd System currentTimeMillis System out println Query took timeEnd timeStart 1000 seconds System out println Number of results printed i stmt close System out println nOP GI RATION COMPLETED SUCCESSFULLY System out println SQLException ex finally if con null try con close catch SQLException ex System out println SQLException ex The UnityJDBC driver behaves exactly like other JDBC drivers The basic steps for querying a database with a JDBC drive
24. run the sample code javac test ExampleQuery java java test ExampleQuery If you have issues with compiling or running try to explicitly indicate the location of the UnityJDBC JAR javac cp unityjdbc jar sampleDB hsqldb hsqldb jar test ExampleQuery java java cp unityjdbc jar sampleDB hsqldb hsqldb jar test ExampleQuery To create your own Java program copy the file ExampleQuery java to MyQuery java There are 2 lines that you must modify The first line indicates where your new sources XML file is located on your machine You may specify an absolute or relative path from the current directory The second line you must modify is to change the SQL query to reference fields and tables in your data source s Compile and run the program Queries can reference any table or field in any data source in your XML sources file as long as you prefix a table or field with the database name such as MyDB MyTable MyField Installation Installation Walkthrough Once you have downloaded UnityJDBC from http www unityjdbc com download php you will have downloaded a JAR file called UnityJDBC_Trial_Install jar First you need to have Java previously installed in order to install UnityJDBC Then you can install UnityJDBC by either double clicking the JAR file or running the following command java jar UnityJDBC_Trial_Install jar The installation steps are below 1 Welcome Screen Provides some backgroun
25. the source or to guarantee absolute consistency of function execution across different sources The UnityJDBC function database is encrypted and stored in the unityjdbc jar To add user defined functions to the function database create a mapping xml file in the JRE classpath execution directory etc that stores the information on the function An example is included in the release and more information is available on the web site 62 Supported SQL Syntax Non parsed Functions UnityJDBC attempts to support most of the SQL standard If there is a function or feature not supported it is possible to use the NP function to pass the query string directly to the data source by passing UnityJDBC validation This may be used to support non standard functions or SQL syntax Below are several examples Query FROM OrderDB Nation N1 WH RE Nl n_nationkey 1 SELECT n_nationkey n_name FROM Nation N1 WHERE N1l n_nationkey 1 Result SELECT N1 N_NATIONKEY F ROM NATION N1 WHERE N1 N_NATIONKEY 1 Query SELECT N2 FROM NP OrderDB select n_name n_nationkey from nation n_name n NP PartDB select n_name n_nationkey from nation n_name n where N2 n_nationkey lt 2 and Nl n_nationkey N2 n_n Result Substitutes subquery for each of the two data sou The result of the two subqueries is then joined a OrderDB SELECT N2 n
26. try Create new instance of UnityDriver and make connection System out println nRegistering driver Class forName unity jdbc UnityDriver System out println nGetting connection turl con DriverManager getConnection url System out println nConnection successful for url System out println nCreating statement stmt con createStatement Unity supports scrollable ResultSets but better performance with FORWARD_ONLY stmt con createStatement ResultSet TYPE_SCROLL_INSENSITIV ResultSet CONCUR_READ_ONLY E A query is exactly like SOL Attributes should be FULLY qualified database table field Statement must end with a semi colon This query performs cross database join on the client sid String sql SELECT PartDB Part P_NAME OrderDB LineItem L_ QUANTITY OrderDB Customer C_Name PartDB Supplier s_name FROM OrderDB CUSTOMER OrderDB LINEITEM OrderDB ORDERS PartDB PART PartDB Supplier WHERE OrderDB LINEITEM L_PARTKEY PartDB PART P_PARTKEY AND 2 OrderDB LINEITEM L ORDERKEY OrderDB ORDERS O_ORDERKEY AND OrderDB ORDERS O_CUSTKEY OrderDB CUSTOMER C_CUSTKEY AND PartDB supplier s_suppkey OrderDB lineitem l_suppkey AND OrderDB Customer C_Name Customer 000000025 Note Client s local JVM is used to process some operations For large queries this may
27. unityjdbe jar 7 10 2012 10 48 PM Executable Jar File 920 KB F UnityJDBCDoc pdf 9 30 2011 2 38 PM Adobe Acrobat D 211 KB Aqua Example Finding Source and Schema Files 9 Moving Sources and Schema Files Optionally move the sources and schema files to a permanent location In this example they are moved to C unityjdbc 48 Using UnityJDBC with Query and Reporting Software w m gt Computer Local Disk C unityjdbc v gt Search unityjdbe Pp Organize v Include in library y Share with w Burn New folder v fi e Name i Date modified Type Size dbl_sqlserverxml 7 10 2012 11 02 PM XML File 50 KB L db2_mysql xml 7 10 2012 11 01PM XML File 46 KB B sources xml 7 10 2012 11 02 PM XML File 1KB Aqua Example Moving Source and Schema Files to Another Location 10 Adding UnityJDBC data source to Aqua Data Studio Copy unityjdbc jar and all JDBC drivers for databases into jre 1ib ext in Aqua Installation directory 49 Using UnityJDBC with Query and Reporting Software oe A Li lt Program Files AquaDataStudioll jre lib ext ron UMU el soner OOO O A Open Burm New folder A Name Date modified Type 4 dnsns jar 2 localedata jar meta index 7 10 2012 11 58 PM Executable Jar File 7 10 2012 11 58 PM Executable Jar File 7 10 2012 11 58 PM mysql jar dreul 3 f o le sqlidbc jar 7 2 2012 9 55 P 570 KB l sunjce_provider jar 7 10 2012 11 58 PM Execut
28. It is also possible to explicitly track global query progress on a per operator basis or perform your own optimization of queries after the UnityJDBC optimizer has built an execution tree Using ExampleNoFileConnection java ExampleNoFileConnection java demonstrates UnityJDBC s ability to be dynamically configured at run time including adding removing or updating sources It is possible to dynamically build a virtual database without using XML configuration files by interacting with UnityJDBC through its metadata API 32 Chapter 5 Using UnityJDBC with Query and Reporting Software The UnityJDBC SourceBuilder is a simple query utility for cross database joins and data virtualization In most cases you will use UnityJDBC to perform data virtualization with reporting software such as JasperReports and Splunk or query software such as SQuirreL SQL or Aqua Data Studio Once you have used SourceBuilder to build the virtual source and schema XML configuration files you use UnityJDBC like any other JDBC driver This section contains examples on how to install and use UnityJDBC data virtualization in popular software systems UnityJDBC Data Virtualization with SQuirreL SQL UnityJDBC is integrated into SQuirreL SQL allowing users to build SQL queries that join data from multiple sources directly within SQuirreL The multiple source query plugin allows SQuirreL users to create a virtual data source that may consist of multiple data sources o
29. QuirreL SQL Aqua Data Studio Toad and RazorSQL works will business intelligence and reporting software including JasperReports Pentaho and Splunk Chapter 2 Installation Overview The free evaluation version of UnityJDBC is a fully functioning system The only constraint with the evaluation version is that it will only produce the first 100 results in a ResultSet The full version has no restrictions The evaluation version can be downloaded at http www unityjdbc com download php You are free to distribute the evaluation version of the software System Requirements UnityJDBC requires a JRE of 1 6 or higher UnityJDBC will run on a J2SE or J2EE platform Quick Setup and Installation UnityJDBC can be downloaded installed tested and configured for your environment in less than 10 minutes Here are the easy steps 1 Downloading First download the UnityJDBC package to your computer from http www unityjdbc com download php 2 Installation The UnityJDBC installation package contains the driver a simple query GUI and some test programs The UnityJDBC_Trial_Install jar can be run by typing the command java jar UnityJDBC_Trial_Install jar Follow the prompts to install UnityJDBC on your machine 3 Quick Start The installation creates a shortcut to the UnityJDBC SourceBuilder which is a simple graphical query editor and configuration tool You can also run it directly using initsources bator initsources sh in th
30. UnityJDBC This software is developed by Unity Data Inc For more information go to htto Awww unityidbc com For free trial support please contact supportrdunityjdbc com Step 6 of 6 CS Done UnityJDBC Installation Part 6 Installation Complete UnityJDBC will auto start at the completion of the installation If it does not click on the shortcut created If there is no shortcut on Windows run initsources bat and on Linux Mac run initsources sh in the installation directory Configuring Data Sources UnityJDBC requires information about the data sources being queried in order to validate optimize and execute queries against those data sources All source information is stored in XML files There are two types of source information files the sources file and schema files A sources file with default name sources xml provides information on all the sources that could be potentially queried The location of this file is provided via the URL when initializing the driver Inside the file is information on each source including its connection URL and parameters JDBC driver and schema file location The sample sources file code test xspec UnityDemo xml is provided in the distribution package Each data source requires a schema file The schema file is an XML encoding of the schema information including table and field names keys joins and relation sizes It is used for validating queries and optimization Two schema files provid
31. UnityJDBC User Documentation UnityJDBC User Documentation Table of Contents TS General Inf rmate da p trates A Ae iaaa 1 Overview of UnityIDBC i sicc sek G65 eck ooo tic ee RSS eee EE ad hatte Fea na ee Bot Sedo 1 DM A A E as dahos teas E e A 2 Overview ra ici betis 2 SIEM REM e A o 2 Quick Setup and Install tn sa eee Peele 2 Installation Walkthrough 202 ia 2 A Rt id Bo eS Rede Re de Rts 8 Configuring Data SourceS nd adele 13 3 Using SourceBuilder Tutorial on Multiple Database Querying cc cece ceeec cece cece cece eene eee eeaeeeneeenaes 15 Using the Sample Databases oc 2 s 2252 2i6ces roses fee Deo eo os de 15 Data Virtualization Using SourceBuilder cecceecceecc ence ence eeceeeceeecn seca esse eeseeeae eeu eeueeeeeeeeneeenegees 16 Multiple Database Virtualization and Querying for HSQLDB cee cccc cece cece ceeece een eeeeeeueeennes 19 Multiple Database Virtualization and Querying for IBM DB2 cseceeecceeceeeceeeeeeeeeeeeeeenes 20 Multiple Database Virtualization and Querying for Microsoft SQL Server 0oocccooccccncccnnccconoconnnicnns 21 Multiple Database Virtualization and Querying for MySQL 00ccocccccnncinnccnnccnncnnncnonconnconncnonononnnnns 22 Multiple Database Virtualization and Querying for Oracle oocoocncccnnccnoconncnnncnnnconnconncnnnrnononon 23 Multiple Database Virtualization and Querying for PostgreSQL cecceecceeceeeceee
32. _name N2 n_nationkey ROM select n_name n_nationkey from nation N2 n WH select n_name from nation n2 where Nl n_nationkey SELECT Nl n_nationkey NP OrderDB n_name varchar Result n_name is substituted directly into the query Query SELECT Nl n_nationkey NP OrderDB select n_name from nation n2 where N1l n_nationkey N2 n_nationkey varchar as name FROM OrderDB Nation N1 WHERE N1l n_nationkey 1 N2 n_nationkey name _nationkey N1 _nationkey as N2 ationkey rces OrderDB and PartDB t the UnityJDBC level ERE N2 n_nationkey lt 2 F PartDB SI More information on non parsed functions is available on the web site SELECT Statement The SELECT statement supported by UnityJDBC has the following syntax SELECT ALL DISTINCT lt exprList gt ROM lt tableList gt HERE lt condition gt ROUP BY lt exprList gt AVING lt condition gt RDER BY lt expr gt ASC DESC OD OGS ELECT N1 n_nationkey FROM select n_name n_nationkey from nation N1 63 Supported SQL Syntax LIMIT lt expr gt OFFSET lt expr gt e An lt exprList gt isa list of expressions Each individual expression lt expr gt may be a column identifier a literal constant or some expression consisting of operators functions constants and column identifiers Recall that a column identifier may often ne
33. aaj jar C Program Files x86 squirrel sql 3 2 1 lib axis wsdl4j jar Class Name unity j dbc UnityDriver x Adding the UnityJDBC to SQuirreL SQL 2 Registering your Data Sources Register your data sources as usual In this example we will perform data virtualization of multiple databases on Microsoft SQL Server MySQL Oracle and PostgreSQL Any database with a JDBC driver is supported including those accessible using the JDBC ODBC bridge Change Alias MSSQL mssaL Y Microsoft MSSQL Server JDBC Driver v New idbe sqlserver iicssql ok ubc ca 1433 databaseName tpct sa 7 Auto logon _ Connect at Startup eines Warning Passwords are saved in clear text ok cose test 34 Using UnityJDBC with Query and Reporting Software Registering a Microsoft SQL Server Source in SQuirreL SQL Change Alias mysql_tpch mysql v MySQL Driver y new idbc mysqtiics suse 4 okubccaitpch awene oOo O ps 7 Auto logon _ Connect at Startup E Properties Warning Passwords are saved in clear text ok cose test Registering a MySQL Source in SQuirreL SQL User Name system Password sceeeee C Auto logon Connect at Startup E Properties Warning Passwords are saved in clear text Lo Co res Registering an Oracle Source in SQuirreL SQL 35 Using UnityJDBC with Query and Reporting Software
34. able Jar File l unityjdbc jar 7 10 2012 9 42 PM Executable Jar File Aqua Example Installing unityjdbc jar in Aqua Data Studio 11 Start Aqua and Register a Generic JDBC Source The configuration information is e Driver unity jdbc UnityDriver e URL jdbc unity lt path to sources xml file gt 50 Using UnityJDBC with Query and Reporting Software Ej General EY Filter G8 Advanced i Driver Sj Permissions Ej Script Oracle 8i Oracle 8i OCI Oracle 9i 109 11g Oracle 9i 10g 11g OCI MS SOL Server 2000 5 8 12 MS SQL Server 7 0 MS SQL Server 7 0 ODBC Sybase ASE 11 12 15 TDS 4 5 Sybase ASE 11 12 15 TDS 5 5 Sybase Anywhere Sybase IQ DB2 LUW 7 x DB2 LUW 8 x 9 x DB2 iSeries DBZ 2 05 Informix Informix ODBC Teradata Netezza Aster nCluster ParAccel Analytic Platform MySQL PostgreSQL Apache Derby Generic JDBC Generic ODBC MS SQL Server 2000 5 8 12 ODBC Name unity_multiple Type Production Tab Color ES oy Tab Title Format 0 1 Explain Authentication Login Name Password i Y Save password Location URL jdbc unity c funityidbc sources xml Driver unity jdbe UnityDriver Driver Location ar C Program Files AquaDataStudio1 1 jre lib ext unityjdbc jar Mounted Scripts Folder Browse Caa Aqua Exa
35. an be set by the developer using UnityJDBC Multiple source UnityJDBC queries can be used with an INSERT INTO statement to populate a table in the database This allows a user to write a cross database query to collect information from multiple sources and then insert the result back into a table in any one of the sources Currently the only restriction is that the table that will be inserted into must exist and must be present in the schema file describing the source Using ExampleMetadata java ExampleMetadata java demonstrates UnityJDBC s support for the DatabaseMetaData interface This interface functions exactly according to the standard with the major difference that metadata is returned for all databases in the data virtualization rather than from a single database That is all your integrated databases really do appear as a single database to your application Using ExampleEngine java Embedded in the UnityJDBC driver is a complete relational engine This is required to process cross database join queries Most users will not interact with the engine directly and their only contact with the engine may be to increase the JVM heap sizes for processing large cross database queries However all of the relational operators of selection projection and join are available for direct use in your programs The join algorithms support sources larger than main memory and allow you the full power of combining ResultSets from multiple databases
36. ata source to a data virtualzation is below 26 Using SourceBuilder Tutorial on Multiple Database Querying U Source Connection Setup Sources file sources xml Ext Hame mongo Source Type Schema file mongo xml Saana URL idbe ids029847 lab 29847 Apch Generic JDEC idbc mongo ds mongolab com pel Generic ODBC Driver mongodb jdbe MongoDriver HSALDE User dbuser IBM DB2 Microsoft SQL Server Password eo MongoDB Catalog MySQL Oracle Schema PostgreSQL Tables included ServiceNow ES Sybase Tables excluded Vectorwise Statistics Row Counts y Add Source Progress 14 0f 14 Success Messages Registering driver mongodb jdbc MongoDriver E Connecting to database Connection successful Auto generated database id 80020202 Database name MongoDB 2 2 Table schema Schema null Catalog null Computing table statistics SELECT COUNT FROM schema 4 Ill gt 4 Adding a MongoDB Data Source to the Data Virtualization Multiple Database Virtualization and Querying for Other JDBC ODBC Sources Using UnityJDBC SourceBuilder it is possible to build a data virtualization for any data source that supports JDBC or ODBC including Microsoft Access databases Excel files text files and any data source that has a JDBC driver 27 Chapter 4 Multiple Database Programm
37. ation Use the default sources xml or select a file location 42 Using UnityJDBC with Query and Reporting Software Enter Location for Source Group XML Configuration File Filename sources Aqua Example Specifying a Source File Location 3 Select New Source Under File Menu Select New Source Demonstration Help New Source Group i Load Source Group Virtualized Remove Source Remove Table Refresh Exit Messages elcome To get started either 1 Create your own sources by a File Menu gt New Source Group and create new XML config file b File Menu gt New Source and enter the JDBC connection information for each source Results Aqua Example Creating a New Source 4 Add a Microsoft Database Enter connection information for Microsoft SQL Server to add it to the data virtualization 43 Using UnityJDBC with Query and Reporting Software Sources file sources xml exi Source Type db1_sqlserver Generic JDBC loc sqlserver icssql ok ubc ca DatabaseName tpch1 00 responseBuffering adaptive Generic ODBC rlawrenc HS LDB IBM DB2 MyS L Oracle Driver com microsoft sqlserver jdbc SQLServerDriver PostgreSQL Schema file db1_sqlserver xml Sybase Add Source ssages Preparing to add data source Registering driver com microsoft sqlserver jdbc SQLServerDriver Driver successfully registered Connecting to database Connection es
38. ble Region Schema null Catalog tpch Computing table statistics SELECT COUNT FROM Region Done processing table Region Table Supplier Schema null Catalog tpch Computing table statistics Writing information into XML file gt 4 Adding an IBM DB2 Data Source to the Data Virtualization Multiple Database Virtualization and Querying for Microsoft SQL Server Using UnityJDBC SourceBuilder it is possible to build a data virtualization of one or more Microsoft SQL Server databases with any other database system To add a Microsoft SQL Server data source the following information is used 1 JDBC Driver class name com microsoft sqlserver jdbc SQLServerDriver 2 JDBC URL jdbc sqlserver lt host gt DatabaseName lt database name gt An example of adding a Microsoft SQL Server data source to a data virtualzation is below 21 Using SourceBuilder Tutorial on Multiple Database Querying U Source Connection Setup Sources file sources xml Ext Hame sqlserver Source Type Schema file sglserver xml sassanora URL idbc sql di ok ubc ca DatabaseN TPCH100 Generic JDBC E idbc sqiserver icssql ok ubc ca DatabaseName Generic ODBC Driver com microsoft sqlserver jdbc SQLServerDriver HSQLDB User rlawrenc IBM DB2 Microsoft SQL Server Password e MongoDB Catalog MySQL Oracle Schema PostgreSQL Tables included
39. caisvchavecdvoeivactecvenecaudateactevhawesde ii lane aora dios Mebane cial slides 65 EXPLAIN Statement A A a id Has 65 By Pass TAME at a a O sra 65 Te Supported JDBC Methods ns aaa 66 OVERVIEW O A O NN e E e E S 66 8 UntyJDBC Driver Internals a AA add iia 67 O O oak Meret e neti raat sedte ena E a a a aa EE tebe aa T Ra 67 Embedded Relational Database Engine s ssnesesseesrsssrsrssesrerersrrsrsersrsserrrssrsreserseesesereessereesseseess 67 9 History and Planned Featuress hisni da at di 68 History of UnityIDBC A is 68 Planned Features ct A A A den Sted ase SEERA 68 Feature cds 68 Contacts and Support iii liado ani 69 iv List of Tables 6 1 6 2 6 3 6 4 6 5 6 6 9 1 9 2 9 3 Comparison Operators nad 58 Mathematical Operator e db ES 59 Mathematical Functions ETE A E asses henteasdee oes 59 Stein FUNCHONS o tease bee aun 60 Date HUnCt id se 61 Agere pate FUNCHONS ii A A deeds otsabin es A E a iah 61 UnttyIDBC Release History A A A eee A RR eS 68 Planned Features in Coming Versions secceecceecceeceeeceeeee seen cece secs E E 68 UnityIDBC Estrada tents e 68 Chapter 1 General Information Overview of UnityJDBC UnityJDBC is a Type 4 JDBC driver capable of querying multiple databases in a single SQL query The driver can be used similar to other JDBC drivers including with query reporting and business intelligence tools application and web servers or stand alone Java pr
40. cccooccccnoccnnccnonnccnnnncnonocnnnnccnnnrcnnnccnnnnccnnoss 41 UnityJDBC Data Virtualization with RazorSQL ccecc cece cece ence ece eee ence eceeeeeeeseeeeaeeee eee eeaeeeaeeeaeees 52 6 Supported SOLsSyntax e o a ado dond 58 O NN 58 Dita PES A E Sah bese vance E A E ai 58 TASA A A Monat wen desis ete Ad 58 Functions and Operators a a e dci 58 Logical Dpto e tele e dad di DU 58 Comparison Operators ooccooccnoconnconncnonononnnnnnn no eee nn rr need cence nee EGE EG EEG E 58 Arithmetic Functions and Operators ooccooccnoconnconnconncnnnonnnnnnnnnnonnnnnn ceca eeceeeee esse eeeeeeneeeeeeeeegees 59 Stem Functions aise eos cede E EETAS E ie tad eshade tase each saber bcs Pass bacon sees an satitonaeet 59 Pattern Matohi e Operators des fase Parte oe 60 Data Type Conversion Functions cccccccceeecneeceeeceee cece ee eeeeeeeeeeeeeeeeeeeeeeeeaeeeaeesaeeaeeaneeanees 60 Date Time Functions and Operators seepi cece cece cece eceeeceeeceeeeeeeeeeceeeeeeeea sess ees a 61 Agere pate ADI BKE ETO nA St ade A ede a Dectat cada et 61 User Defined Functions and Support for Other Functions oocoooccoccnocnnocnnnonnconoconncnoncnoncnnncnnnss 62 Function Iranslai n e a thes AA Hada tess Sov ch vig ade disease henaa Ta TESA 62 Non parsed FUNCHONS A ees eee 63 SELECT Statement iia i E o 63 INSERT Sta teMeMt emi o lides 64 UPDATE Statement viens A A A dai 65 111 UnityJDBC User Documentation DELETE Statement si
41. ceeeeeeeeeeeeneeees 24 Multiple Database Virtualization and Querying for Sybase ceceecceecceeceeeeeeeeeeeeeeeeeeeeneeees 25 Multiple Database Virtualization and Querying for MongoDB cecceeceeeceeeceeeeeeeeeeeeeeeeaes 26 Multiple Database Virtualization and Querying for Other JDBC ODBC Sources ceeeeeeeee eee 27 4 Multiple Database Programming with UnityJDBC 000 000 cece cece cece eee e cence ee ee ee eeeeeeeeeeneeeeeeaeeea essa eeaa nega 28 Usinethe Sample POTASA celica did 28 Using ExampleQuery jaVa oooocoocncocnnconnconnononcnnncnnnronnrrnnrn nr nr rn nr rn nr nn essa essa esse een eeaneeaneeenegeeeges 28 Using Ex mpleUpd te Javarer e n e eas 31 Using ExampleMetadata java ccccceccceeceeece eee ee eee eee eee eee nn rr nn cena eee eeeeeeeeeeeeeeseaeeeaeseneeggs 32 Using ExampleEngitie javar csi s c cctincd set incad a c 32 Using ExampleNoFileConnection Java cccecceece ence eeceeeeeeeeeeeeeeeeeeeeeeaeeeaeeea este eeaaeeaeeeeneenaes 32 5 Using UnityJDBC with Query and Reporting Software cccccecc cece neces ceeeeeeeeeeeeeeeeeeeeeeeaeeeeeeeaeene tees 33 UnityJDBC Data Virtualization with SQuirreL SQL 00 ccecececc cece ence ence eeceeeeeeeeneecaeeea esse eeseeaeeeaeeeaaes 33 UnityJDBC Data Virtualization with JasperReports oooccocccoccnnconnconnconnononcnnnnnnnnnncnnnonnnnnnnrnncnoncnnnoos 41 UnityJDBC Data Virtualization with Aqua Data Studio ooo
42. crosoft SQL Server Source to the Data Virtualization Aliases u s A y Roku axte E 1 am A usso Property Name Value E 9 ETALE JDBC Driver CLASSPATH CAeclipse workspace UnityJDBC_Live unityjdbc jar BB CUSTOMER 1500 SetURL Py E LINEITEM 60057 se r isReadOnly false E NATION 25 getDatabaseProductName UnityJDBC E ORDERS 150000 getUserName El PART 20000 oetmriverversion 4 1 109 PARTSUPP 8000 getpatabaseProductVersion 4 1 109 a REGIONES getDriverName UnityJDBC La E SUPPLIER 1000 alproceduresfreCallabl false allTablesAreSelectable true autoCommitFailureClosesAllResultSets false j dataDefinitionCausesTransactionCommit false false false getCatalogSeparator F getCatalogTerm catalog getClientinfoProperties getDatabaseMajorVersion 4 getDatabaseMinorVersion mn _ getDefaultTransactionisolation TRANSACTION_NONE getDriverMajorVersion 4 getDriverMinorVersion i getExtraNameCharacters pal oetidentifierQuoteStrino Zi Resulting Data Virtualization 5 Add Oracle Database to Data Virtualization User can add as many sources as they wish You can also rename the source in the virtual view It does not have to be the same as the alias name used by SQuirreL When adding Oracle sources make sure to specif
43. ct from MSSQL Region R INNER JOIN mysql Nation N ON R r_regionkey N n_regionkey y af m timitrows 100 m 3 select from MSSOL Region R INNER JOIN mysql Nation N ON R r_regionkey N n_regionkey a silent bold requests sleep slyly across the quickly sly depend 12 select from m Rows 25 select from MSSOL Region R INNER JOIN mysql Nation N ON R r_regionkey N n regionkey Kf Results MetaData Info Overview RLREGIO R_NAME 0 AFF e 4 h30 ikyLk6khM 1 AMERICA 1 zQn30kwz1wLn7PLS30hCgn56kPS5PyRikgi 1B 7 1 AMERICA even ironic theodolites according to the bold platelets wa 2 BRAZIL gLmSOnACAmnBCj2kiki7RCPNgPxnCOjNask q 1 AMERICA i even ironic theodolites according to the bold platelets wa 3 CANADA S 4yMO AhnQSLh wzQAM662Aw1ByCI7CxmzRwNj 4 MIDDLE EAST furiously unusual packages use carefully above the unusual 4 EGYPT Mim5126_ Cxj NMQmLxOikni02j2m3Ah4yNR1Q 0 AFRICA special Tiresias about the furiously even dolphins are furi 5 ETHIOPIA NS7n LSOP Oz5n1AI8B2S02nN0IMh4SBxP RhBl 3 EUROPE special bold deposits haggle foxes platelet 6 FRANCE 3mjmizl S 3L3k2hNNhNIP4w370xRxyN15wn 3 EUROPE special bold deposits haggle foxes platelet 7 GERMANY znOP4RkwO CmzBB 516mAg IByw4OM3QyNP_ 2 ASIA silent bold requests sleep slyly across the quickly sly depend 8 INDIA MNIRSRCiRMj1111wjN7Myn MAIyINIMMBOI7PL 2 ASIA _ silent bold requests sleep s
44. d in the directory unity functions Function Translation UnityJDBC has a database of known functions This database contains information on what functions are supported on each data source This is how UnityJDBC processes functions 1 UnityJDBC does not support function If a function is not in the UnityJDBC database it is passed down as is to the underlying source If the source is able to execute it successfully the query continues If not an error is thrown 2 UnityJDBC supports function data source requires translation If the function requested in the query is not directly supported by the data source different name different parameters etc but UnityJDBC contains a mapping in its database the function is translated to the correct form on the data source and executed on the data source 3 UnityJDBC supports function data source does not support function If UnityJDBC supports the function but not the data source then the query is optimized to perform as much of the processing as possible on the source but the function execution is performed internally in UnityJDBC This way your query can execute on data sources with the help of UnityJDBC that do not support the required functions 4 UnityJDBC is running with local execution If the local execution flag is set for the UnityStatement object executing the query all functions except aggregate functions are executed by UnityJDBC This setting may be useful to reduce load on
45. d on UnityJDBC Click Next unity JDB Welcome to Unity JDBC Welcome to UnityJDBC UnityJDBC makes it easy to access combine and analyze data from all your database systems Use standard JDBC and SQL to combine data from multiple databases including NoSQL databases like MongoDB without complicated server modifications or new systems UnityJDBC simplifies data virtualization Using UnityJDBC This installation program will install the UnityJDBC driver on your machine as well as sample code files and a sample database The installation will create a program shortcut for the UnityJDBC Source Builder application which can also be run directly using initsources bat Windows or initsources sh Mac Linux Once you have configured your virtualization you can use UnityJDBC with any Java based query or reporting software just like any other JDBC driver Free Trial Support Documentation and videos on how to setup UnityJDBC are available at http Avww unityidbc com support We provide free help to get started Contact us at support unityidbc com Thank you for using UnityJDBC Step Lof 6 CD CO aut UnityJDBC Installation Part 1 Welcome Screen 2 End User License Agreement Read the EULA select Accept and click Next Installation un uly JDB 5 Unity JDBC End User License Agreement UnityJDBC END USER LICENSE AGREEMENT EULA This End User License Agreement the Agreement is between you the C
46. e executed at the source are available UnityJDBC and user defined functions are used only when applying functions to data after it is extracted from the sources UnityJDBC will parse queries containing functions that it itself cannot process in its internal database engine These functions are passed down to the database engine and executed locally Only functions that require inputs from more than one database are processed in the UnityJDBC database engine All other functions are passed down to the sources UnityJDBC supports user defined functions UDFs Adding your own user defined function is easy There are two types of functions row functions and aggregate functions A row function operates on one row at a time for its data and includes functions like SUBSTRING and ABS An aggregate function is used in GROUP BY queries and aggregates an expression usually a column across multiple rows in a group to produce a single value Examples include MAX and COUNT To create a row function you must create a Java class that extends the Function class A template example is in the file F_Function_Template java This class must implement a constructor an evaluate method and provide information on the parameters it requires Once completed as long as this function is available in the CLASSPATH UnityJDBC will search for it when called A similar template is available for aggregate functions A_Aggregrate_Template java Sample code is provide
47. e installation directory To try the sample queries you will also want to click on the Start Database shortcut or run the script startDB bat or startDB sh in the directory sampleDB hsqldb Below is a screenshot of running a sample query that joins across two databases Installation IU UnityJDEC SourceBuilder and Query pp ication File Demonstration Help Databases Virtualized ELECT LI_extendedprice P p_retailprice LI_partkey HA FROM OrderDB Lineitem L PatDB Part P gt r E PartDB WHERE P p_partkey L _partkey AND L I_partkey 10 F CUSTOMER o F LINEITEM o E NATION o ORDERS PART o El PARTSUPP Messages F REGION o F SUPPLIER ecuting query gt E OrderDB Query result 1 at time 175 Query result 10 at time 176 amp E CUSTOMER JQuery result 100 at time 179 ie El TER umber of results 257 o ORDERS Query complete o E PART Query Exection time is 190 ms o F PARTSUPP o REGION Results E SUPPLIER i L_EXTENDEDPRICE P_RETAILPRICE L_PARTKEY 901 00 901 00 901 00 901 00 901 00 901 00 901 00 901 00 Multiple Database Query Example 4 Writing queries UnityJDBC uses the standard SQL language You can include as many databases in the virtual database that you wish To reference a table in a given database prefix it with the database name In the example above OrderDB Lineitem refers to
48. e the engine to build an execution tree Also in this file is an example on how you can have Unity parse but not execute a global query UnityJDBC will return its global query and execution plan which you can later execute This feature gives you the opportunity to modify the global execution plan before execution if desired It also allows you to track the progress of a global query at the operator level 67 Chapter 9 History and Planned Features History of UnityJDBC UnityJDBC is the product of over 10 years of research and development in database integration and virtualization UnityJDBC was first released in 2006 and commercial support and development has been ongoing since 2011 Table 9 1 UnityJDBC Release History Release Version and Date Major Features UnityJDBC v1 0 May 2006 Cross database join support match functions full optimizer query by pass UnityJDBC v2 0 May 2007 Connection pools DataSource connections more functions UnityJDBC v3 0 May 2008 Native INSERT UPDATE DELETE INSERT INTO SELECT across databases PreparedStatements user defined functions UnityJDBC v4 0 August 2011 Database dialect translation paging using LIMIT OFFSET single database subqueries result caching JnityJDBC v4 1 June 2012 Integration with SQuirreL SQL and JasperReports BLOB support G El nityJDBC v4 2 January 2013 Memory optimized query execution engine EXPLAIN for query plans subq
49. ed in the distribution are code test xspec UnityDemoOrder xml and code test xspec UnityDemoPart xml There are two ways to create your own sources file and associated schema files 1 The easiest way is to use the SourceBuilder GUI that can be started using the installed shortcut or running initsources bat or initsources sh This GUI will automatically extract source information and build 13 Installation the necessary files If you are using UnityJDBC as the multisource plugin in SQuirreL SQL all features of the SourceBuilder are integrated into SQuirreL and the XML files are generated automatically You can manually build a sources file using a text editor To produce a schema file for a source open up the program called com unityjdbc SourceBuilder SchemaExtractor java in the code directory Modify the JDBC URL driver path and output directory accordingly and run the program The account that you connect with must have read access to the database and associated tables that you want to access After the XML schema file has been produced move it to the directory where you want it and update the sources file to reference the correct location In almost all cases using the SourceBuilder utility will be faster and easier 14 Chapter 3 Using SourceBuilder Tutorial on Multiple Database Querying Using the Sample Databases SourceBuilder is a graphical query tool that allows you to define a virtual database consis
50. ed to be prefixed by its database name and table name e A lt tableList gt is a list of table references Each table reference can be aliased using the AS operator A table reference may also be a named subquery such as SELECT FROM SELECT FROM T1 AS R WHERE R val gt 50 e A lt condition gt isa boolean condition that may contain multiple subconditions related using AND OR and XOR e Ifthe GROUP BY clause is used no attributes should be present in the SELECT lt exprList gt that are not in an aggregate function or are GROUP BY attributes e The HAVING lt condition gt filters groups and typically should contain only aggregate functions e The ORDER BY clause can order results on any number of attributes in either ascending or descending order The LIMIT clause allows paging of results The OFFSET clause determines the first row of the result with the first row numbered as 1 Some examples using the TPC H schema follow The database name for these examples is OrderDB Return all nations with their key and name SELECT OrderDB Nation n_nationkey OrderDB Nation n_name FROM OrderDB Nation Return the nations and their regions Only return nations in the region name of AMERICA Note the use of table aliasing using AS SELECT N n_nationkey N n_name R r_regionkey R r_name FROM OrderDB Nation as N OrderDB Region as R WHERE N n_regionkey R r_regionkey AND R r_name AMERICA
51. ests a function that is not supported on a certain source This example assumes that the installation of UnityJDBC has already been completed The following is an example of creating sources and executing multiple database queries with Aqua Data Studio 1 Create a New Source Group Under File Menu Select New Source Group 3 Query Load Source Group Virtualized EE ECT Li_extendedprice P p_retailprice L_partkey New Source FROM OrderDB Lineitem L PartDB Part P WHERE P artkey L _partkey AND L I_partkey lt 10 Remove Source p_partkey L _partkey _partkey lt 10 Remove Table Refresh s ES o F CUSTOMER Messages o SUPPLIER o NATION Executing query Query result 1 at time 168 Greece Query result 10 at time 171 o REGION Query result 100 at time 172 o PARTSUPP D a z gt E ORDERS ERROR java sql SQLException Maximum of rows is 100 in the trial version Full gt El TestTime version at www unityjdbc com o F LINEITEM Number of results 100 o PART oe gt o CUSTOMER Results amp E SUPPLIER L_EXTENDEDPRICE P_RETAILPRICE L_PARTKEY o F NATION E 4505 00 901 00 45050 00 901 00 29733 00 901 00 29733 00 901 00 30634 00 901 00 29733 00 901 00 26129 00 901 00 24327 00 901 00 Aqua Example Creating a New Source Group Data Virtualization 2 Select Sources File Name and Loc
52. f results 1 Query complete Query Exection time is 135 ms db1_sqlserver 7 REGION PARTSUPP 7 ORDERS LINEITEM T PART CUSTOMER Results 7 SUPPLIER MatchFunc1 NATION 15000 a BARAR AS at i ii in i it Aqua Example Executing a Cross Database Join of Two Databases 8 Finding Sources and Schema Files Now that the data virtualization is complete find the sources xml file and schema files for your sources The image below shows the default location which would be inside the UnityJDBC installation directory 47 Using UnityJDBC with Query and Reporting Software ou LE e a Ji Local Disk C gt Program Files x86 UnityIDBC gt y 49 search uniy1Dec e Organize Open Burn Newfolder 0o Name 7 Date modified Type Size B code 7 3 2012 8 57 PM File folder de OtherJDBCDrivers 7 3 2012 8 57 PM File folder Lo sampleDB 7 3 2012 8 57 PM File folder B Uninstaller 7 3 2012 8 57 PM File folder de webserver 7 5 2012 12 32 PM File folder D sinstallationinformation 7 10 2012 10 47 PM INSTALLATIONIN 7 KB dbl sq re mil Mi db2_mysqlxml 7 10 20 M XML File 46 KB defxml 7 10 2012 9 47 PM XML File 50 KB initsources bat 7 1 2012 11 39PM Windows Batch File 1KB initsources sh 7 4 2012 3 57 PM Shell Script 1KB README bet 6 7 2012 7 58 PM TXT File 7 KB sources xml 7 10 2012 11 02 PM XML File 1KB U unity ico 6 29 2012 8 13 AM Icon 4KB l
53. ing with UnityJDBC Using the Sample Programs UnityJDBC is a universal query translator It allows you to develop your programs without worrying about the underlying database All SQL statements executed with UnityJDBC are translated for the database used Even if you do not need multiple database queries data virtualization or queries that span multiple different databases UnityJDBC simplifies your development by handling all the issues with SQL dialects If a function is not supported by your database UnityJDBC will execute it internally If you forget a function name for a database it will translate to the correct function call for the particular source This allows you the freedom to write your SQL code in a database independent way You no longer have to make major changes to your code if you change database systems If you want full control you can use the UnityJDBC database engine directly in your code to join ResultSets with each other regardless of their source and perform dynamic filtering ordering and analysis Sample programs are provided in the directory code Here is a list of the programs and the features they demonstrate 1 ExampleQuery java a query example that joins data across two databases 2 ExampleUpdate java demonstrates INSERT UPDATE DELETE and how to store a cross database query result into a table 3 ExampleMetaData java query example showing how to extract metadata information 4 E
54. lder Tutorial on Multiple Database Querying The A11 statistics setting collects rows counts as well as value distribution information for each field Some sources do not support collecting field distribution information in which case the Row Counts setting should be used A setting of None will collect no statistics and is the fastest when performing extraction UnityJDBC will execute queries with no statistics perfectly fine although statistics do help in query planning for complex queries involving many tables Below is an example of extracting only the tables that end in ER from an Oracle database Note how the schema is also supplied as RLAWRENC e U Source Connection Setup Ee Sources file sources xml Exit Hame oracle_tpch Source Type Schema file oracle_tpch xml sarsanara URL idbe le thin rl fest lok ub 1521 ix Generic JDBC abc oracle tnin rawrencile cssq OK URC Ca e Generic ODBC Driver oracle jdbc driver OracleDriver HSQLDB User rlawrenc IBM DB2 Microsoft SQL Server Password e MongoDB Catalog MySQL Oracle Schema RLAVVRENC PostgreSQL Tables included ER ServiceNow Sybase Tables excluded f Vectorwise Statistics Row Counts w Add Source Progress 20f2 Success Messages Done processing table CUSTOMER gt Table SUPPLIER Schema RLAWRENC Catalog null riting inf
55. lect Installation Path 4 Install Progress The installation will be performed and progress shown Click Next when complete 10 Installation unity JDBC Installing Unity JDBC Files Pack installation progress Finished Overall installation progress 171 Step 4 of 6 Previous CB Net UnityJDBC Installation Part 4 Installation Progress 5 Install Shortcuts The installation will install shortcuts to start SourceBuilder and a sample database This screen allows you to control if shortcuts are created and their location When finished click Next 11 Installation un uly JDB 5 Specify Shortcuts for Unity JDBC mM Create shortcuts in the Start Menu Mm Create additional shortcuts on the desktop Select a Program Group for the Shortcuts 7 Zip Accessories Administrative Tools all users Advantage Database Server 10 10 Apache Tomcat 7 0 create shortcut for current user Aptana ArgoUML Astah Community CamStudio DBEdit 2 DbScherna UnityJDBC Installation Part 5 Select Shortcuts 6 Installation Complete The installation complete screen will be displayed if the installation is successful UnityJDBC SourceBuilder will be automatically started Click Done to close the install window 12 Installation Y UnityDBc bobak unity JDB E Installation Complete Starting Unity JDBC Installation Complete Thank you for choosing
56. ll expr1 col2 expr2 WHERE lt condition gt An example is below UPDATE Employee SET salary salary 1 10 WHERE age gt 50 DELETE Statement The DELETE statement supported by UnityJDBC has the following syntax DELETE FROM lt tbl_name gt WHERE lt condition gt An example is below DELETE FROM Employee WHERE salary gt 100000 EXPLAIN Statement The EXPLAIN statement supported by UnityJDBC has the following syntax EXPLAIN lt query gt The EXPLAIN statement provides an explanation of the how UnityJDBC will execute a given query include the translated queries to be executed on each source the operations performed by UnityJDBC and the expected cost of each query operation Using EXPLAIN is a great way to determine the performance of queries and improve their execution speed By Pass Statement You can use methods to by pass or flow through the driver to execute an untranslated query directly on a single source In the UnityStatement class are these two methods ResultSet executeByPassQuery String dbName String sql int executeByPassUpdate String dbName String sql These methods will execute a query or update on a single source given by name The SQL statement provided is not parsed or validated and passed directly to the source driver There is no overhead in this type of query as it is equivalent to invoking the sou
57. luded ServiceNow ES Sybase Tables excluded Vectorwise Statistics Row Counts v Add Source Progress 110f11 Success Messages SELEL LUUINIL FROM tpch table1 Done processing table table1 Table table2 Schema null Catalog tpch Computing table statistics SELECT COUNT FROM tpch table2 Done processing table table2 Success Lil Adding a MySQL Data Source to the Data Virtualization Multiple Database Virtualization and Querying for Oracle Using UnityJDBC SourceBuilder it is possible to build a data virtualization of one or more Oracle databases with any other database system To add an Oracle data source the following information is used 1 JDBC Driver class name oracle jdbc driver OracleDriver 2 JDBC URL jdbc oracle thin lt user gt lt password gt lt server gt 1521 lt service gt An example of adding an Oracle data source to a data virtualzation is below 23 Using SourceBuilder Tutorial on Multiple Database Querying U Source Connection Setup Sources file sources xml Ext Hame oracle Source Type Schema file oracle xml EU URL idbe le thin rl NK lok ub 11521 ix Generic JDBC idbc oracle thin rlawrenchoocBcssql ok ubc ca e Generic ODBC Driver oracle jdbc driver OracleDriver HSQLDB User IBM DB2 Microsoft SQL Server Password MongoDB Catalog
58. lyly across the quickly sly depend 9 _ _ INDONESIA _ 2 SIPmM0O7iLi7ABJ6MMAOK3nLwi73BPx2Cwj2M 4 MIDDLE EAST furiously unusual packages use carefully above the unusual IRAN 4 h532943BgShy0500gSB2h06jxXQn301w4NmP 4 MIDDLE EAST furiously unusual packages use carefully above the unusual 11 4 IGN gw7Mg4xPiAwjQS6iROwP gjz7jkPIISRkRxg 2 2 y PiRn6O4RnyOPGQONHMSN26AhghiCIRQIP QI PEALBPDS 2PM 7A haana MA7 CidOkRAR ay ErrorCode 0 Please try out the Tools popup by hitting ctri t in the SQL Editor Do it three times to stop this message Logs Errors 202 Warnings O Infos 5 896 _ G A Multiple Database Query with Join Results Expressed on Previous Data Virtualization 7 Perform SQL Query Translation The UnityJDBC driver used to perform the virtualization will also translate functions that are not implemented by certain sources For example Microsoft SQL Server does not support TRIM but you can do the same result using RTRIM LTRIM Unity will automatically translate a TRIM function specified in a MSSQL query to the correct syntax supported by the database 40 Using UnityJDBC with Query and Reporting Software amp SQuirrel SQL Client Version 3 3 0 File Plugins Session Windows Help ean tete Connect to essal unity w a El Active Session i test 7 y E Ca amp ajo x e eta a ex o o s a
59. mple Adding a UnityJDBC Virtual Data Source in Aqua Data Studio 12 Multiple Database Query in Aqua Data Studio Create a query like usual except it can contain multiple databases 51 Using UnityJDBC with Query and Reporting Software File Edit Server Query Automate Tools DBATools ERModeler Window Help FPS FF AREER BBE ADA RS a Servers E unity_multiple untitled x peo E fh tpch10 Aline z El GB unity_multiple oca alatae6oo 7Y 4 5 0 Catalog b yw a fa m 5 0 uy Dr EB 8 a asa Es E E max Results 5 Database Database unity v Schema dbi_sqlserver Username amp z eoo 1 select count as count from dbl_sqlserver CUSTOMER INNER JOIN db2_mysql Orders ON c_custkey o_custkey a a able E EE CUSTOMER c E LINEITEM a EB NATION H E ORDERS fe EE PART E E PARTSUPP 5 E REGION E E SUPPLIER W E Procedures 6 0 User Defined Types B F db2_mysql E E5 Table EB Customer Es El 8 1 record s Fetch Data 14ms Sum O Q E Lineitem D E Nation count E E Orders F 15000 5 Part E E Part2 5 E Partsupp E E Region 5 ES Supplier 6 Procedures Ly ta E User Defined Types By UnityJDBC_cssal bal eee FR Grid E Messages Servers Files 13 Projects Local Database Servers unity_multiple Catalog unity Database db1_salserver Table CUSTOMER 1 30 INS 17 11 2012 12 15 12 AM Script executed No Errors Time 117ms
60. n different servers and platforms The user can enter one SQL query to combine and join information from multiple sources Benefits The multisource plugin powered by UnityJDBC allows SQuirreL SQL to support multiple source queries No data source or server changes are required The plugin supports standard SQL including joins group by aggregation LIMIT and ordering where tables may come from one or more sources The plugin will perform function translation where a user requests a function that is not supported on a certain source UnityJDBC can be installed directly as a plug in through the SQuirreL SQL plug in interface It is also possible to download the UnityJDBC distribution and replace the unityjdbc jar in the plug in with the latest from UnityJDBC 1 Registering the UnityJDBC Driver By adding a driver 33 Using UnityJDBC with Query and Reporting Software Change Driver UnityJDBC Driver Name UnityJDBC Example URL jdbc unity Nirtual Website URL www unityjdbc com Extra Class Path C Program Files x86 squirrel sql 3 2 1 lib antir jar C Program Files x86 squirrel sql 3 2 1 lib aopalliance jar C Program Files x86 squirrel sql 3 2 1 lib asm attrs jar C Program Files x86 isquirrel sql 3 2 1Wiblasm jar C Program Files x86 isquirrel sql 3 2 Miblautocomplete jar C Program Files x86 squirrel sql 3 2 1 lib axis jaxrpc jar C Program Files x86 squirrel sql 3 2 1 lib axis s
61. ncluded with the UnityJDBC distribution in the directory drivers in the installation folder A common location is in lt JAVA_HOME gt jre lib ext c The connection information is the UnityJDBC driver class unity jdbc UnityDriver and the URL is jdbc unity lt relative or absolute path to sources file gt suchas jdbc unity unityjdbc sources xml The UnityJDBC driver can be used with any software that supports JDBC A screenshot of configuring it in Aqua Data Studio is below The same connection information applies to all JDBC based software Installation By Edit Server Properties ea E cea A a aaa ree RDBMS Oracle 8i Name UnityJDBC Oracle 8i OCI Oracle 9i 10g Type Production y 0 10g OCI er 2000 5 Authentication er 2000 5 ODBC er 7 0 Login Name er 7 0 ODBC Sybase ASE 11 12 15 TDS 4 5 Password Sybase ASE 11 12 15 TDS 5 5 Sybase n e DB2 For LU Save password wv DB2 for LL Informi Location URL jdbc unity c funityidbc sources xml Generic ODBC Driver unity jdbc UnityDriver Driver Location F Mounted Scripts Folder J Ok Close Registering the UnityJDBC Driver in Aqua Data Studio UnityJDBC can also be used with your own Java programs There is sample code in the code directory in the installation folder Here are two commands executed from the code directory to compile and
62. ograms Internally UnityJDBC contains a database engine and optimizer allowing it to efficiently join data from source databases to produce a single ResultSet UnityJDBC supports updating data using results produced from cross database queries and performs automatic dialect translation to convert queries into the proper dialect A brief list of the major supported features is below supports cross database joins of any number or type of JDBC accessible sources Microsoft SQL Server Oracle DB2 Postgres MySQL Sybase MongoDB Cassandra etc allows SQL based comparison of data across databases to detect data inconsistencies errors or for synchronization of data between databases e performs SQL dialect translation and automatically executes functions and features internally in the driver if the data source does not support them contains an advanced optimizer and query processor that performs efficient query processing by having each source process as much of the query as possible e g push down filters cross database queries can be used to insert records into tables INSERT INTO SELECT supports cross database PreparedStatements has a driver by pass feature to allow direct access to individual sources supports connection pools and connection properties supports user defined functions works with any data source that has a JDBC driver and will run on any Java supported platform works with all SQL query software including S
63. ormation into XML file Updating sources file SUCCESS Data source successfully added Computing table statistics SELECT COUNT FROM RLAWRENC SUPPLIER Done processing table SUPPLIER Success Adding an Oracle Data Source to the Data Virtualization note use of schema name Multiple Database Virtualization and Querying for HSQLDB 1 Using UnityJDBC SourceBuilder it is possible to build a data virtualization of one or more HSQLDB databases with any other database system To add a HSQLDB data source the following information is used JDBC Driver class name org hsqldb jdbcDriver 2 JDBC URL jdbc hsqldb hsql lt host address gt lt database name gt 19 Using SourceBuilder Tutorial on Multiple Database Querying An example of adding a HSQLDB source to a data virtualzation is below y U Source Connection Setup a Sources file sources xml Exit Hame hsqidb Source Type Schema file hsqldb xml paces URL idbe hsqidb hsql ocalhostapch Generic JDBC E idbc hsqldb hsql Mocalhost pcl Generic ODBC Driver org hsqldb jdbcDriver HSQLDB User IBM DB2 Microsoft SQL Server Password MongoDB Catalog MySQL Oracle Schema PostgreSQL Tables included ServiceNow Sybase Tables excluded 5 Vectorwise Statistics Row Counts w Add Source Progress 80f8 Success Me
64. ption lt str gt lt str gt String Unity JDBC UnityJDBC String concatenation ascii string int ascli xyz 120 ASCII code of the first character of the input string length string int length UnityJDBC 9 Length of string in characters lower string String lower JDBC jdbc Convert string to lower case position search int position J UnityJDBC 5 Location of search in target target indexed from 1 replace source String replace abUnityabJDBC X XUnityXXJDBC Replace all occurrences search replace ab XX of search string in source string with replace string substring string String substring UnityJDBC 6 JDBC substring starting at position start start substring string String substring UnityJDBC 6 2 JD substring starting at position start count start and taking count characters trim string String trim UnityJDBC UnityJDBC remove leading and trailing spaces from string Itrim string OR String trim UnityJDBC UnityJDBC remove leading trim string spaces from string LEADING rtrim string OR String trim UnityJDBC UnityJDBC remove trailing spaces from string trim string TRAILING gt trim string String trim aaaUnityJDBCbbb UnityJDBC remove leading trailing BOTH BOTH ab or both from string where LEADING characters removed may be TRAILING optionally specified in lt chars gt lt chars gt upper string String upper jdbc JDBC Convert
65. r are 1 2 Load the driver optional This is done by Class forName unity jdbc UnityDriver Make a connection A connection is made to a database by providing the database URL and other properties including user id and password This example is using the DriverManager to make the connection con DriverManager getConnection url Note that the URL is of the form jdbc unity lt path_to_sources_file gt In this case the URL is jdbc unity test xspec UnityDemo xml This path may be an absolute or relative path on the machine It is also possible to retrieve encrypted XML files from a network source The sources file provides the connection information for the individual data sources for use by UnityJDBC Execute a statement UnityJDBC follows the JDBC API for creating statements and executing queries and updates There are some methods unique to UnityJDBC which are covered in a later section Standard SQL syntax is supported The major difference is that tables in different databases can be referenced in the same query This is accomplished using the syntax database table to refer to tables and database table field to refer to fields Note that aliasing using AS is supported Zf full names are not provided UnityJDBC will attempt to match as appropriate but it will generate errors if the provided names are not unique 30 Multiple Database Programming with UnityJDBC This file is a good one to modify to sta
66. rce s JDBC driver directly 65 Chapter 7 Supported JDBC Methods Overview UnityJDBC supports the majority of the methods in the Driver Connection Statement ResultSet and ResultSetMetaData interfaces UnityJDBC supports the PreparedStatement interface but not the CallableStatement interface UnityJDBC supports native updates using INSERT DELETE and UPDATE It is also possible to use INSERT INTO to insert query results into another table UnityJDBC does not support transactions across databases Support for other JDBC methods is also limited by the underlying support of the JDBC driver for each data source UnityJDBC requires a JDK of 1 6 or higher 66 Chapter 8 UnityJDBC Driver Internals Overview UnityJDBC contains an embedded database engine to join the results produced by executing queries on other JDBC accessible sources It requires a JDBC driver for each source to be accessed The UnityJDBC architecture is the result of years of research and development and has been published in numerous technical and research publications Embedded Relational Database Engine Embedded in UnityJDBC is a relational database engine and associated operators of selection projection grouping ordering and join You can build your own global query spanning data sources by combining these operators into an execution tree In the distribution is a file called ExampleEngine java which demonstrates how to us
67. reClosesAllResultSets false 2 io Fe TABLE dataDefinitionCausesTransactionCommit false o 2 UDT dataDefinitionlgnoredinTransactions false doesMaxRowSizelncludeBlobs false Y postgres getCatalogSeparator a E FA customer asol getCatalogTerm catalog EA getClientinfoProperties _ j l lineitem 6000 getDatabaseMajorVersion 4 E nation 25 getDatabaseMinorVersion 1 El one 0 getDefaultTransactionisolation TRANSACTION_NONE E orders 15000 getDriverMajorVersion la aa E part 2000 getDriverMinorVersion 1 E partsupp 8000 y getExtraNameCharacters AA gt oetidentifierQuateStrina 2 ad J 1 111 ay ze Please try out the Tools popup by hitting ctr t in the SQL Editor Do it three times to stop this message Logs Errors 3 Warnings O Infos 203 G 28 of 122 MB Q 2 33 51 PM PDT A Data Virtualization in SQuirreL SQL with Databases MySQL Oracle PostgreSQL and Microsoft SQL Server 39 Using UnityJDBC with Query and Reporting Software 6 Execute a Multiple Database Query The user can execute an SQL query that spans multiple sources and get a single result The virtualization is transparent to the user and SQuirreL Below is an example of a query that joins two tables in different databases File Drivers Alia Plugins Session Windows Help Connect to MSSQL v E amp Active Session 4 virtual JE o fi x 0 4 virtual E mn Oo x GTSCRMESR GOB lt Objects sat sele
68. rt your own program Simply change the class and file name the URL to the location of your source list file and the query executed and you are done Using ExampleUpdate java UnityJDBC natively supports INSERT UPDATE and DELETE statements on a single database These statements can be executed in by pass mode in which case UnityJDBC does not parse or validate the statement and passes it straight to the JDBC driver for the corresponding database In native mode UnityJDBC will parse and validate the statement before passing it to the data source Note that the basic INSERT UPDATE and DELETE statements operate only on a single table in SQL so no cross database query processing is necessary A sample of the code in ExampleUpdate java is below Class forName unity jdbc UnityDriver con DriverManager getConnection url stmt con createStatement Example 1 Basic query String sql SELECT FROM mydb Customer rst stmt executeQuery sql printResult rst Example 2 DELETE using native parsing String databaseName mydb sql DELETE FROM mydb customer WHERE id 51 or id 52 stmt executeUpdate sql Example 3 INSERT by pass method sql INSERT INTO Customer id firstname lastname street city VALUES 51 Joe Smith Drury Lane Detroit UnityStatement stmt executeByPassQuery databaseName
69. s the month of the glven date expression DAY INT DAY 2011 07 06 6 Returns the day of the glven date expression DATEADD TIMESTAMP DATEADD 2011 07 06 2006 07 06 Allows the addition of INTERVAL 3 days 12 53 45 a given date field to a datetime expression Intervals are supported and are translated as necessary for systems that do not support them Aggregate Functions The following aggregate functions are supported Table 6 6 Aggregate Functions Function Argument Return Type Description Type avg x int float int for integer types double Average of all input values double precision for float double types precision type count N A int Count of number of input values count x any int Count of number of non null input values group concat x any varchar Returns a comma separated list of all input values max x any same as input Maximum of all input values comparable type 61 Supported SQL Syntax Function Argument Return Type Description Type min x any same as input Minimum of all input values comparable type sum x int float int for integer types double Sum of all input values double precision for float double types precision type User Defined Functions and Support for Other Functions For queries on a single database UnityJDBC parses functions and passes them directly to the database engine for execution Thus all functions that can b
70. ss sources 3 0 INSERT INTO across sources 4 0 Paging using LIMIT OFFSET 4 0 Query and ResultSet caching 4 0 Universal dialect and function translation support for sources missing functions 4 0 Single source subqueries 4 1 Support for BLOBs CLOBs 4 2 Multiple source subqueries in FROM clause 4 2 EXPLAIN command for query execution information 4 2 Ability to control tables extracted into data virtualization by inclusion exclusion patterns 4 3 Full subquery support including correlated subqueries 4 3 INTERSECT EXCEPT 4 3 MongoDB TokuMX and Cassandra support Contacts and Support Please contact support unityjdbc com if you encounter any bugs issues or have feature requests This document is Copyright 2014 by Unity Data Inc All rights reserved 69
71. ssages SELECT COUNT FROM PUBLIC REGION Done processing table REGION Table SUPPLIER Schema null Catalog PUBLIC Computing table statistics SELECT COUNT FROM PUBLIC SUPPLIER Done processing table SUPPLIER Success Adding a HSQLDB Data Source to the Data Virtualization Multiple Database Virtualization and Querying for IBM DB2 Using UnityJDBC SourceBuilder it is possible to build a data virtualization of one or more IBM DB2 databases with any other database system To add an IBM DB2 data source the following information is used 1 JDBC Driver class name com ibm db2 jcc DB2Driver 2 JDBC URL jdbc db2 lt host name gt lt database name gt An example of adding an IBM DB2 source to a data virtualzation is below 20 Using SourceBuilder Tutorial on Multiple Database Querying g U Source Connection Setup Sources file sources xml Exit Hame db2 Schema file db2 xml Source Type URL idbc db2 cssql ok ubc catpch Generic JDBC Generic ODBC Driver com ibm db2 jcc DB2Driver HSQLDB User rlawrenc IBM DB2 Microsoft SQL Server E MySQL Schema grace Tabl included es inc z PostgreSQL Sybase Tables excluded Statistics Row Counts v Add Source Progress 90f9 Success Messages _ SELECT COUNT FROM Partsupp Done processing table Partsupp Ta
72. string to upper case Pattern Matching Operators Pattern matching is supported using the LIKI For example abcdef match a single character LIKE E operator Data Type Conversion Functions Data type conversions are performed using the CAST x y function The CAST function takes any object as the first parameter and takes a string literal representation of the type to cast to as the second parameter Note that the type must be put in single quotes as a string literal Example CAST 45 Possible type names are VARCHAR TIME VARCHAR creates 45 CHAR INT FLOAT DOUBLI ab is true The is used to match one or more characters and _ is used to DATI GI BI 60 TIMESTAMP Supported SQL Syntax Date Time Functions and Operators The following are a few of the date functions supported A complete list is on the website Table 6 5 Date Functions Function Return Type Example Result Description CURRENT TIMESTAMP TIMESTAMP CURRENT TIMESTAMP 2011 07 06 Returns the current 12 53 45 date Format yyyy MM dd HH mm ss CURRENT_TIME TIME CURRENT_TIME 12 53 45 Returns the current time Format HH mm ss CURRENT_DATE DATE CURRENT_DATE 2011 07 06 Returns the current date Format yyyy MM dd YEAR INT YEAR 2011 07 06 2011 Returns the year of the glven date expression MONTH INT MONTH 2011 07 06 7 Return
73. tablished to database Extracting schema information uto generated database id 10090000 Database name Microsoft SQL Server 2005 able CUSTOMER Schema dbo Catalog tpch100 Computing table statistics SELECT COUNT FROM dbo CUSTOMER Done processing table able LINEITEM Schema dbo Catalog tpch100 Computing table statistics SELECT COUNT Aqua Example Adding a Microsoft SQL Server Database to the Data Virtualization 5 Add a MySQL Database Enter connection information for MySQL database to add it to the data virtualization 44 Using UnityJDBC with Query and Reporting Software Sources file sources xml Source Type d2 mysa Generic JDBC lide mysql fics suse 4 ok ubc caftpch Generic ODBC A HS LDB IBM DB2 Password test Microsoft SQL Server Schema A MySQL Oracle Driver commysqljdboDriver 0 O PostgreSQL Schema file ab2_mysql xmi_ Sybase ssages Preparing to add data source Registering driver com mysql jdbc Driver Driver successfully registered Connecting to database Connection established to database Extracting schema information uto generated database id 60050143 Database name MySQL 5 1 able Customer Schema null Catalog tpch Computing table statistics SELECT COUNT FROM Customer Done processing table able Lineitem Schema null Catalog tpch Computing table statistics SELECT COUNT
74. temp unityjdbc UnityDemo xm RazorSQL Error When Unable to Find Sources File 2 Unable to find driver If you did not put the correct class name or location of the unity jdbc jar you will get the following error Verify the class name and jar location to resolve 56 Using UnityJDBC with Query and Reporting Software RazorSQL Error When Unable to Find Driver or Incorrect Driver Class Name 57 Chapter 6 Supported SQL Syntax Overview UnityJDBC supports a cross database SELECT statement The SELECT statement has the standard SQL 92 syntax and supports WHERE ORDER BY GROUP BY and HAVING UnityJDBC supports subqueries on a single database and cross database including subqueries in the FROM clause and WHERE clause entire query is on a single database SQL functions are supported using a function syntax with parameters rather than using SQL keywords and syntax Table and fields often should be prefixed with the database name they originate from This database name is provided in the schema file for the data source Data Types The standard SQL data types are supported Since UnityJDBC uses the JDBC drivers provided by database vendors non standard data types may not be universally supported Identifiers An identifier is a string used to reference a database table or field Identifiers follow the standard SQL rules Since a UnityJDBC query may span mul
75. the Lineitem table in OrderDB a user assigned name for the sample HSQL database and PartDB Part refers to the Part table in the PartDB database Writing queries is as easy as prefixing the table name with the database name 5 Creating Your Own Virtual Database A virtual database is defined using a sources XML file often with the default filename of sources xml Each database also has a schema file storing information on its tables and fields These XML files are automatically built by SourceBuilder but can also be edited directly at any time A few quick steps to produce your own virtual database a Select File gt New Source Group and use the default sources xml1 file b Select File gt New Source and provide the JDBC connection information for your source The screenshots below show MySQL and PostgreSQL Installation Sources file sources xml Exit Hame mysql Source Type Schema file Imysqlxml Cassandra r Generic ODBC Driver com mysaljdbe Driver 00200000 O O HS LDB User flawenc o0 O IBM DB2 ae Microsoft SQL Server Password CA MongoDB ae DD O MYSQL a Oracle Schema PostgreSQL A ServiceNow Sybase Tables excluded agro Nectowise statistics Progress 110f11 Success ssages MELEL I CUDINI FROM tpch table1 Done processing table table1 able table2 Schema null Catalog tpch Computing table statistics SELECT COUNT FROM tpch table2
76. ting of multiple databases including MySQL PostgreSQL Oracle Microsoft SQL Server Sybase MongoDB and others SourceBuilder can be started using the shortcut produced during the installation by running initsources bat or initsources sh in the installation folder or directly from the driver using the command java jar unityjdbc jar Itis required that the unity jdbc jar and all JDBC driver JAR files for databases used be in the Java classpath When SourceBuilder is first started a welcome screen is displayed a U UnityJDEC SourceBuilder and Query Application o JLo Esa File Demonstration Help Query EXECUTE Clear Messages Welcome To get started either 1 Create your own sources by a File Menu gt New Source Group and create new XML config file b File Menu gt New Source and enter the JDBC connection information for each source q Results SourceBuilder Welcome Screen To use the sample database and queries included make sure to start the HSQLDB using the Start Database shortcut or run the script startDB bat or startDB sh in the directory sampleDB hsqldb Then select Demonstration gt Cross database Join Test Click the EXECUTE button to run the query and get results If an error occurs verify that the sample database is started and the hsqldb jar file is in your classpath You can try several other of the sample queries or write your own
77. tiple databases table and field identifiers defined in a data source may not be unique across all data sources In which case the database name should be added to the identifier to create a unique system wide identifier For instance consider an order database given the name OrderDB with a table called Orders and fields id and orderDate The Orders table may be referred to using only Orders or OrderDB Orders Similarly the field id may be referred to as Orders id or OrderDB Orders id Standard aliasing using AS in the FROM and SELECT clauses is supported Delimited identifiers are supported by enclosing in double quotes e g from or my field with spaces Delimited identifiers must be used for SQL reserved words Functions and Operators Arithmetic operators are supported as well as generic expressions Functions are not specified according to SQL keyword syntax but rather as a function identifier with parameters similar to programming languages The format of functions is function paraml param2 Logical Operators The logical operators of AND OR NOT and XOR are available Comparison Operators The following comparison operators are available Table 6 1 Comparison Operators Operator Description lt less than gt greater than lt less than or equal to 58 Supported SQL Syntax Operator Description gt greater than or equal to equal not equal
78. ueries in FROM clause improved SourceBuilder GUI JnityJDBC v4 3 June 2014 Full subqueries EXCEPT INTERSECT implementation of PooledDataSource and ConnectionPoolDataSource support for MongoDB TokuMX Cassandra and ServiceNow C Planned Features The following features are planned in coming versions Version 5 0 will be released in November 2014 with new versions released approximately every year If you have any feature requests please e mail support unityjdbc com Table 9 2 Planned Features in Coming Versions Version Feature Description 5 0 Improved SourceBuilder user interface 6 0 Support for distributed transactions Feature List The following table summarizes the features of UnityJDBC and the version where they were first introduced Table 9 3 UnityJDBC Feature List Version Feature Description 1 0 Cross database SQL queries for any JDBC source 1 0 Query by pass 68 History and Planned Features Version Feature Description 1 0 MERGE feature with MATCH functions 1 0 Embedded relational database engine 1 0 Source and schema file encryption 1 0 Support for Applets 1 0 Support for query results databases larger than main memory 2 0 DataSource connections 2 0 Pooled connections 3 0 Prepared Statements 3 0 User defined Functions 3 0 INSERT DELETE UPDATE on a single source 3 0 INSERT DELETE UPDATE acro
79. ustomer an organization or person and Unity Data Inc UnityData with respect to the use of the software UnityJDBC the Software Product and its associated documentation By accepting this Agreement or by installing or using any portion of the Software Product the Customer agrees to enter into this Agreement A contract is then formed between UnityData and the Customer 1 GRANT OF LICENSE UnityData grants Customer a perpetual non transferable limited and non exclusive license to use the Software Product solely for Customer s internal business purposes and subject to the terms and conditions set forth in this Agreement 2 LICENSE RESTRICTIONS The trial version of the software may be downloaded copied distributed and installed with no restrictions There cannot be any reverse engineering or software modification that removes the trial restrictions of the trial version The client and server versions of the software are licensed on a per machine basis A license Laccept the terms of this license agreement O Ido not accept the terms of this license agreement UnityJDBC Installation Part 2 End User License Agreement 3 Install Path Select the installation path for UnityJDBC and then click Next Installation lios un l ty JDB S Select an Installation Location B Select the installation path C Program Files UnityJDBC amp Browse UnityJDBC Installation Part 3 Se
80. xampleEngine java an advanced example that shows how users can use the UnityJDBC database engine directly 5 ExampleNoFileConnection java example showing how to configure UnityJDBC in code without using XML files All of these examples use a local HSQL database that can be started using the script startDB bat or startDB sh in the directory samp1eDB hsq 1db in the installation folder To compile and run any of these sample programs make sure you are in the code directory and execute the following commands javac test ExampleQuery java java test ExampleQuery If you have CLASSPATH issues you can explicitly indicate the location of the HSQL JDBC driver and the UnityJDBC driver by javac cp UnityJDBC jar sampleDB hsqldb hsqldb jar test ExampleQuery java java cp UnityJDBC jar sampleDB hsqldb hsqldb jar test ExampleQuery Using ExampleQuery java The ExampleQuery java demonstrates the basic features of the UnityJDBC driver The code is below 28 Multiple Database Programming with UnityJDBC import java sql public class ExampleQuery URL for sources xml file specifying what databases to integrate This file must be locally accessible or available via http URL static String url 3jdbc unity test xspec UnityDemo xml public static void main String args throws Exception Connection con null Statement stmt null ResultSet rst
81. y a schema so that system tables and tables from all schemas are not extracted 38 Using UnityJDBC with Query and Reporting Software Hame oracle Catalog Schema RLAWRENC Tables included gt Row Counts v Tables excluded Statistics Progress 90f9 Success Messages Computing table statistics SELECT COUNT FROM RLAWRENC SUPPLIER Done processing table SUPPLIER Success Prompt to add Oracle Source to the Data Virtualization Note use of SCHEMA File 20 4 oses Plugins Session Windows Help _Connectto cssql_unity Y E amp Active Session 1 virtual He 56 a fox mnonhunax tee Objects SB OO Uf Metadata Status Schemas Table Types Data Types Numeric Functions String Functions System Functions TimelDate Functions Keywords 8 Property Name Value Q E mssaL 9 TABLE JDBC Driver CLASSPATH C eclipse workspace UnityJDBC_Livelunityjdbcjar El CUSTOMER A ur PTE a piel isReadOnly false a picado getDatabaseProductName UnityJDBC getUserName E PART 20000 HA cetpriverversion 41109 PASE A getDatabaseProductVersion 4 1 109 Sprints of getDrivervame UnityJDBC o UDT allProceduresAreCallable false El mysal allTablesAreSelectable true El she autoCommitFailu

UnityJDBC User Manual

Contents

Download Pdf Manuals

Related Search

Related Contents