SQL Development

BMC AMI Test Drive 
e and Data Management SQL Development

This tutorial will introduce you to the SQL Development in BMC AMI DevX Workbench for Eclipse that enables you to query, create, and manage various database types of data.

Revised: 2024/03/14 00:00 




Getting Started

Instructions:

  • This guide contains many screenshots to provide a visual reference
  • Please note each place that you must enter your own specific ID or number
  • You must complete each step before proceeding to the next to successfully follow the test drive script

If, at any point during your experience, your host connection times out, you may need to log back in to the TestDrive host connection.

image-2023-4-10_9-24-14.png

If at any time during the execution of this script the Common Enterprise Services Login popup is shown, enter your test drive ID and password under User ID and Password, check the Save credentials box and then depress the ENTER key or click OK.


SQL Development

One popular feature in BMC AMI DevX File-AID for DB2 is the SQL Command Manager (option 3.5). 
The SQL Development introduces similar capabilities into the BMC AMI DevX Workbench for Eclipse.

Just as the SQL Command Manager leveraged SPUFI, the SQL Development leverages the Eclipse framework called Data Tools. Data Tools generally uses a JDBC connection to connect to an RDBMS. The Workbench implementation does not require a JDBC driver, instead it uses HCI to submit the request and File-AID for DB2 as the back engine.

SQL Development is part of Workbench for Eclipse; the only requirement is File-AID for DB2.


Do This
  • Select BMC and Database Development.

image-2023-10-10_10-50-13.png


Do This
  • Right-click on Data Connections.
  • Select New.

image-2023-4-5_11-9-13.png


Do This
  • Select DB2 for z/OS using Topaz.
  • Click Next.

image-2023-4-5_11-9-59.png

Data Tools can connect to a wide variety of relational databases. Generally, it uses JDBC drivers to complete the SQL request. The behavior is identical, the processing is different. A hybrid programmer, one working across platforms, could use Data Tools within Workbench for Eclipse to connect to other databases like SQL Server, Oracle, or Sybase. The DB2 for z/OS using Topaz is the only connection that BMC AMI DevX supports.


Do This
  • From the Host dropdown select TestDrive. (Login if prompted - next screenshot)
  • In the SSID dropdown select DBCC subsystem.
  • Click Next.

image-2023-4-16_15-11-44.png


Do This
  • Log on with your Test Drive credentials: (replace CWEZXXX with your CWEZ ID number).

image-2023-4-5_11-13-9.png


Do This
  • Review the New Connection Profile Summary.
  • Click Finish.

image-2023-4-16_15-12-32.png


Do This
  • Within the Data Source Explorer tab, expand your newly created Data Base Connection.
  • Expand New DB2 for z/OS using Topaz.
  • Expand DBCC TestDrive.

image-2023-4-16_15-14-20.png


Do This
  • Drill down to Expand Catalogs, then DBCC, and finally Schemas.

image-2023-4-16_15-15-17.png

The list of Schema's can be very long at your site. You can filter it by:

  • Right click on Schemas
  • Select Properties
  • Unclick Disable Filters check box
  • Enter filter as desired (type filter in uppercase) 


Do This
  • Scroll down to see the Schema corresponding to your Test Drive ID (CWEZ### where ### is your assigned number). 
  • Expand the node to see available options. 


    We will be working with tables in our examples. 

  • Expand Tables to see the list of Db2 tables within your schema.

image-2023-4-16_15-16-51.png


Do This
  • Expand the ORDER_TABLE to see additional table items such as Columns, Constraints, Indexes, etc. 
  • Right click on the ORDER_TABLE.
  • Select Data.
  • Select Sample Contents.

image-2023-4-16_15-18-9.png

Note how there is now an SQL Results tab open. The left-hand side of the SQL Results view shows the results of the commands currently executed. We should have one successful command executed. The right-hand side shows the results (some sample data from the ORDER_TABLE) and, if you click on the Status tab, will show the command executed and the time to complete the request.

image-2023-4-16_15-21-31.png

The icons on the right side offer some additional controls as to what is displayed.


Using the SQL Scrapbook


Do This
  • Click on the rightmost icon of the Data Source Explorer view. 


    Hover will display "Open scrapbook to edit SQL statements".

image-2023-4-16_15-22-25.png


Type Ahead Functionality

 The SQL Development colorizes SQL keywords and provides type-ahead functionality.

Do This
  • Type all instructions in uppercase. Start typing the select statement SELECT COUNT (D in the edit window.
  • Select the distinct keyword from the pop-up window.

image-2023-4-16_15-24-58.pngSELECT COUNT (D


Execute SQL Statements


Do This
  • Finish typing the select statement: SELECT COUNT (distinct CUST_NUM) FROM ORDER_TABLE;
  • Right click on the statement.
  • Select Execute Current Text.


With the type ahead functionality it may prompt you with the table names.

image-2023-4-16_15-26-55.png

You now have two successful executions on the left, and the right side shows the Status tab which shows the statement executed and elapsed time.

image-2023-4-16_15-28-36.png


View Results


Do This
  • Click on the Result tab to see how many distinct Customer Numbers are in the Order Table.

image-2023-4-16_15-29-22.png

You can enter multiple statements in the SQL Scrapbook Editor. Always end the early SQL statements with ";"
The SQL Scrapbook also accepts commands other than SELECT statements.


Do This
  • Click into the Editor and Enter SELECT * FORM CUSTOMER_TABLE; <note the typo>. 
  • Right click and select Execute All.

image-2023-4-16_15-30-43.png

It returned the expected error from our typo. 



Do This
  • Click OK to clear message and correct the word Form to FROM.
  • Right click and Execute All again.

image-2023-4-16_15-31-37.png

The SQL Results view on the left under Status has a drilldown when multiple statements are entered and shows Group Execution under the Operation column.

Clicking on the expanded entries will show their results on the right-hand side.

 image-2023-4-16_15-32-50.png


Do This
  • Expand the entry on the left.
  • Select the second statement.
  • Click the Result tab on the right to see data brought back from that Select statement.

image-2023-4-16_15-33-48.png

Using the SQL Query Builder

Create, edit, or run SQL statements using the SQL Query Builder graphical interface, which provides access to your database schema and objects so that you can quickly create or edit SQL statements without needing to type any SQL code. You also have the flexibility to add or modify the SQL code in the editor window.
 

Do This
  • Right click in the white space in the Editor.
  • Select Edit in SQL Query Builder.

image-2023-4-16_15-34-41.png


Table Joins

The SQL Query Builder window opens. For this exercise let's join two tables. 

Do This
  • To add a table right-click in the white space and select the Add Table pop-up command.

image-2023-4-16_15-35-55.png


Do This
  • Scroll down to find your set of tables in the schema with the same name as your Test Drive ID.
  • Expand and select the CUSTOMER_TABLE. 
  • Hold down the CRTL key and select the ORDER_TABLE.
  • Click OK.

image-2023-4-16_15-37-30.png



Do This
  • To create the join, right click between the tables and select Create Join.

image-2023-4-16_15-38-23.png


Note that it has already started building the SQL Statement in the top window.

The Create Join window appears and lists the Customer table for both source and target. You will create the join between the two tables based on the Customer Number value. 

Do This
  • Use the dropdown for the Target to select the Order Table. 
  • Use the Column dropdown to select the Cust_Num column. 
  • Click OK.

image-2023-4-16_15-39-31.png

Note the visual line now connecting the tables. Next select the columns from which to return data.

Do This
  • Click COMPANY_NAME in the Customer table.
  • Scroll down in the Order table and select the ORD_STAT and ORD_AMOUNT columns.

image-2023-4-16_15-41-19.png

These columns are now listed in the Columns tab at the bottom of the screen. This has now created the Select statement for these joined tables.
The SQL SELECT statement gets built in the top-most pane. Clicking OK at this point will take this generated SQL statement and append it into the SQL Scrapbook. You can also execute it by right clicking in the top pane and selecting Run SQL. 

Do This
  • Right click in the top pane and select RUN SQL. 
  • View your results from the execution by selecting the Result tab.

image-2023-4-16_15-42-9.png

Your results will show only the selected columns from both tables.

Do This
  • Click OK.

image-2023-4-16_15-43-34.png

The SQL Statement is brought back into the SQL Scrapbook and can be executed from here as well.

image-2023-4-16_15-46-2.png 


Additional Functionality

Other things to experiment with in the SQL Development. 
In the left-hand tree view:

  • Right Click on a table and select Generate DDL…
  • Right Click on a table and select Data -> Edit
  • Right Click on a table and select Data -> Extract
  • Right Click on a table and select Data -> Sample Contents. Then from the results pane on the bottom right, try the various icons. Right click in a row and try the various choices.
  • Notice how you can export into a .txt file

(Some commands may not be available at this time)

Congratulations! This completes the SQL Development for Db2 tutorial for BMC AMI Test Drive.




 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*