Lab 9 Optional:  Using SQuirrel SQL Client

As you saw in the previous lab, Big SQL provides web tooling to issue SQL statements and perform other functions.  In addition, because Big SQL supports DB2-compliant JDBC and ODBC drivers, many popular query, analysis, and programming tools can work directly with Big SQL. Among these is SQuirrel SQL Client, a popular open source SQL tool.
 
After completing this lab, you will be able to:

  • Configure SQuirrel SQL Client to connect to Big SQL.
  • Execute Big SQL queries and inspect result sets.
  • Create charts from your result sets.

Prior to beginning this lab, download and install the standalone SQuirrel SQL Client (http://www.squirrelsql.org/).  Consult the SQuirrel SQL web site for instructions and download options.  Examples in this lab are based on SQuirrel SQL 3.6 and presume you did not install the optional plugin for DB2 as part of the installation process.  In addition, prior to starting this lab you need to have created and populated the sample tables presented in an earlier lab on Querying Structured Data.
 
Allow 30 – 45 minutes to complete this lab. Please post questions or comments about this lab to the forum on Hadoop Dev at https://developer.ibm.com/answers?community=hadoop.

9.1.          Connecting to Big SQL

To connect to Big SQL from SQuirrel SQL, you must create a JDBC driver specification and an alias (connection specification).  This exercise takes you through the necessary steps.
 
__1.          Download the Big SQL JDBC driver to the platform on which you have SQuirrel SQL Client installed.  In particular, you must download the db2jcc4.jar file, which is typically found in the …/db2/java subdirectory of your Big SQL home directory.  (By default, db2jcc4.jar will be installed at /usr/ibmpacks/bigsql/4.0/db2/java.)  Alternatively, you can download the IBM Data Server Driver for JDBC and SQLJ (JCC Driver) Version 10.5 directly from http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217.
 
__2.          Launch SQuirrel SQL Client following the standard process for your environment.  If necessary, consult the open source documentation for details.

 

2015-09-03 06_34_42-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__3.          Click the vertical Drivers tab at left and note the list of potential JDBC data source drivers.  (Ignore the red Xs, which indicate that additional information or JARs are needed.)

 

2015-09-03 06_35_14-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__4.          Click the plus sign (+) in the upper left corner of the Drivers pane to add a new driver.  A new menu will appear.

 

2015-09-03 06_36_02-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader2015-09-03 06_36_26-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__5.          Click the Extra Class Path tab.
 
2015-09-03 06_37_10-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader
 
__6.          Click Add to add a new .jar file.

 

2015-09-03 06_37_39-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__7.          In the pop-up menu, navigate to the local directory containing the Big SQL JDBC JAR file that you downloaded at the beginning of this exercise.  Select the db2jcc4.jar file and click Open.

 

2015-09-03 06_38_17-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__8.          Verify that the JAR file now appears in the Extra Class Path list.

 

2015-09-03 06_38_48-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__9.          Supply values for remaining fields in this menu.  In the Name field at top, enter Big SQL Driver.  For the Example URL, enter the appropriate JDBC connection URL for your environment, such as jdbc:db2://myhost.ibm.com:51000/bigsql.  For the Class Name (at bottom), enter com.ibm.db2.jcc.DB2Driver.  Leave the Website URL field blank and click OK.

 

2015-09-03 06_39_16-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__10.       To create a new connection to your Big SQL server based on this driver, click the vertical Alias tab at left.

 

2015-09-03 06_39_46-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__11.       Click the plus sign (+) in the upper left corner of the Aliases pane to add a new alias (database connection).

 

2015-09-03 06_40_18-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__12.       Enter appropriate connection information in the pop-up menu that appears.  For the Name, enter Big SQL Lab Server.  For the Driver, select the Big SQL driver you just created from the drop-down list.  For the URL, verify that the appropriate JDBC URL for your environment was copied from the Example URL you defined in the driver specification (e.g., jdbc:db2://myhost.ibm.com:51000/bigsql).  If not, edit the JDBC URL as needed. Finally, enter valid values for the User and Password fields (such as bigsql/bigsql).

 

2015-09-03 06_40_54-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__13.       Click Test and Connect when prompted.  Verify that your connection is successful.

 

2015-09-03 06_41_22-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__14.       Click OK to save the new alias.
 
__15.       In the Aliases pane, right mouse click on your new alias and select Connect.  Review the connectivity information presented and select Connect again.
 
Now you’re ready to inspect the contents of your Big SQL database and execute queries.

9.2.          Exploring your Big SQL database

In this exercise, you will explore the contents of your Big SQL database. Make sure that you created and populated the tables specified in the Querying Structured Data lab before proceeding.
 
__1.          If necessary, connect to your Big SQL database following instructions in the previous exercise.
 
__2.          Click the Objects tab associated with your Big SQL alias (connection).

 

2015-09-03 06_41_59-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__3.          Note the list of schemas that appears in the pane at the far left.  Expand the BIGSQL schema by clicking on the toggle icon just before its name.

 

2015-09-03 06_42_29-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__4.          Explore the tables in this schema.  (Click on the icon beside TABLE.)

 

2015-09-03 06_42_57-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__5.          Click on one of the tables in the list, and in the pane at right, click on the Columns tab to display information about columns defined for this table. The example below displays column information for the BIGSQL.SLS_SALES_FACT table.

 

2015-09-03 06_43_27-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__6.          Click on the Content tab to view a sampling of rows from this table.  (A subset of the information in the BIGSQL.SLS_SALES_FACT table is shown below.)

 

2015-09-03 06_44_01-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__7.          Explore the views in the SYSHADOOP schema; these views are based on system catalog data.  In particular, display the contents of SYSHADOOP.HCAT_TABLES view, noting that information about each table’s INPUTFORMAT, TYPE, and other data is available.

 

2015-09-03 06_44_36-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

9.3.          Issuing queries and inspecting results

In this exercise, you will execute a query and inspect the results. For simplicity, your query will be based on one that you already issued in a previous lab on Querying Structured Data.
 
__1.           Click the SQL tab towards the top of your screen (next to the Objects tab).

 

2015-09-03 06_45_10-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__2.          In the empty query box to the left of the database connection information, paste the following query:

 

-- Fetch the product name, quantity, and order method of products sold.
-- Query 1
SELECT pnumb.product_name, sales.quantity,
meth.order_method_en
FROM
sls_sales_fact sales,
sls_product_dim prod,
sls_product_lookup pnumb,
sls_order_method_dim meth
WHERE
pnumb.product_language='EN'
AND sales.product_key=prod.product_key
AND prod.product_number=pnumb.product_number
AND meth.order_method_key=sales.order_method_key;

 

 

2015-09-03 06_46_07-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader
 
__3.          Run the query.  Click the run icon at top.  (If you prefer to use the keyboard, hit Ctrl+ENTER.)

 

2015-09-03 06_46_34-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__4.          Inspect the query results displayed in the lower pane.

 

2015-09-03 06_47_07-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__5.          Optionally, click on the MetaData tab to review column names, data types, and other information about your result set.  Use the horizontal scroll bar as needed to fully inspect the meta data available.

 

2015-09-03 06_47_34-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

9.4.          Charting query results

In this exercise, you will use built-in charting facilities to create a simple bar chart based on the results of a query.  Your chart will enable you to visualize the popularity of different order methods for sales.
 
__1.          In the SQL tab, change the Limit Rows value to 1000.

 

2015-09-03 06_48_00-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__2.          Re-execute the query that you ran in the previous exercise.  (If necessary, return to that exercise and copy the SQL statement into the tool.  Then click on the run icon.)
 
__3.          Click the Overview / Charts tab in the result set pane.

 

2015-09-03 06_48_34-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__4.          Click Charts to display the menu for creating charts.

 

2015-09-03 06_49_16-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__5.          Change the X axis value to the ORDER_METHOD_EN column.  Accept the remaining default values and click Open chart window.

 

2015-09-03 06_49_43-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__6.          Inspect the bar chart that appears in the pop-up window and note the relative popularity of each sales order method.

 

2015-09-03 06_50_21-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__7.          Close the chart window.

Explore other capabilities of the SQuirrel SQL Client as desired, and close the tool when finished.

 

Summary

Congratulations!  You’ve just learned many important aspects of Big SQL, IBM’s query interface for big data.  To expand your skills and learn more, enroll in free online courses offered by Big Data University (http://www.bigdatauniversity.com/) or work through free tutorials included in the BigInsights product documentation.  The HadoopDev web site (https://developer.ibm.com/hadoop/) contains links to these and other resources.

1 comment on"Getting Started with Big SQL 4.0 Lab 9: Using SQuirrel SQL Client (Optional)"

  1. cartier bracelet men fake October 23, 2016

    cartierbraceletlove Mi sembra un’altra mossa pubblicitaria e le modelle non mi sembra che abbiano mai rappresentato le donne comuni
    cartier bracelet men fake http://courtshipgift.com/

Join The Discussion

Your email address will not be published. Required fields are marked *