Edit and Manage Relational Data

BMC AMI Test Drive

NOTE: At any point should you need to restart this Test Drive script, follow the instructions below:

  • Delete the SQL Server and DB2 object filters by right-clicking and selecting delete
  • Disable the SQL Server Host Type display by going to Windows – Preferences - Host Types and deselecting it from the list. Save settings.

If data needs to be reinitialized:

  • Terminate your TestDrive session and open a new one
  • For DB2 z/OS open and submit the RSTRDB2 member in yourID.WBSAMP.JCL library in Host Explorer

  • Restart script from beginning

    Getting Started with BMC AMI DevX Data Studio Editor


    BMC AMI DevX Data Studio enables users to easily navigate and manipulate relational data to increase productivity for day-to-day tasks.

  • This test drive will take you through the following activities:
  • Enabling SQL Server Host Types in Host Explorer
  • Creating filters for SQL Server and DB2 objects
  • Creating and reusing queries/selection criteria
  • Browsing/Editing multiple datatypes simultaneously
  • View and manipulate relational data
  • Find and Replace Data

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 guide
  • Please enter field values in UPPERCASE if the value is in uppercase in script text or screenshot

This script requires that you first complete the "Getting Started" script.
 If at any time during the execution of this script the BMC AMI Common Enterprise Services (CES) 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.

image-2023-4-10_9-23-16.png


DevX Data Studio Perspective

Do This
  • Open the DevX Data Studio perspective by clicking on the BMC toolbar option and selecting DevX Data Studio.
  • Expand Hosts by clicking the arrow to the left. 


image-2024-9-25_17-10-6.png

Enabling SQL Server Host Type in Host Explorer

Do This
  • On the Menu Bar, click on "Window then Preferences". 
  • In the filter box where your cursor is positioned type "host".
  • Click on "Host Types" from the returned list. 
  • Select SQL Server Databases from the list.
  • Click Apply and Close.


image-2023-4-6_9-19-17.png


 "SQL Server Sample" predefined Host Connection for this exercise, should now appear in your Host Explorer view when you expand Hosts. 


Defining a SQL Server Filter in Host Explorer

Do This
  • Double click on the SQL Server Sample host connection.
  • Type "testdrive" for User ID and Password for Login Credentials.
  • Click OK.

image-2023-4-6_9-24-32.png

Do This
  • Right-click on Microsoft SQL Server and select Add Filter.

image2021-9-22_15-46-57.png

Do This
  • When the filter box appears, type "dbo" for Schema in lower case and "*" for Table to wildcard and show all tables. 
  • Click OK.


image-2023-4-6_9-27-6.png

Your filter is now defined, and the sample SQL Server tables should be visible for use. 


Do This
  • Collapse the filter to hide table names to give us a little more work area by clicking the arrow icon next to the filter name. 

worddavdd2eb3b034e23656bd0d9f1985225dad.png


Defining a DB2 z/OS Filter in Host Explorer

Do This
  • Double click on the TestDrive host connection.
  • Use your TestDrive User ID and Password for Login Credentials .
  • Click OK. 

image-2023-4-6_9-30-34.png



Do This
  • Right-click on DB2 for z/OS and select Add Filter. 

image-2023-4-6_9-32-23.png

Do This
  • When the filter box appears, select DBCC for the SSID, type your TestDrive ID for Schema in UPPER case and "*" for Table to wildcard and show all tables. 
  • Click OK.

image-2023-4-16_16-39-4.png


Your filter is now defined, and the sample DB2 tables should be visible for use.

Do This
  • Collapse the filter to hide table names to give us a little more work area by clicking the arrow icon next to it. 

worddav12ec4f070bf61bbe653bf1eb5fb15c7e.png


Opening Multiple Data Sources for Browse/Edit

Now that we have multiple Relational Database connections defined in Host Explorer, let us explore how to work with multiple sources simultaneously in a Workbench for Eclipse session. For our example, we will use DB2 for z/OS and SQL Server as our sources.

Do This
  • Expand the two filters we just created, by clicking the triangle icon next to them, to display the available tables that match the filter criteria. 

image-2023-4-16_16-41-50.png


Do This
  • Double-click on the CUSTOMER_TABLE in SQL Server.

image-2023-4-16_16-45-26.png

Do This
  • Leave all default values and click Run to execute the query. 

worddavd9cffc373f0cf357e9dd38cc9a1f1e8e.png

We will discuss parameters later. You should see the table opened in edit mode.


Do This
  • Double-click on the CUSTOMER_TABLE under the DB2 for z/OS filter.
  • Click run.

image-2023-4-16_16-52-10.png

The second CUSTOMER table opens in a new tab.

 

Do This
  • Now click and drag the second tab over to the right until you see the grey borders line up side-by-side as shown in the screenshot below and release.

You can also drag borders in other directions if you want to experiment. 

image-2023-4-16_16-57-6.png

You should now see the two tables open for edit side-by-side. Our example shows one from SQL Server and one from DB2 for z/OS on the mainframe. 

Do This
  • Close the tables by clicking the X on tab. 

worddavc20bbca979dfce4467d84c704e072d67.png

The remainder of the script can be performed on any of the connection types defined in the preceding portion of this script. 

Defining and Saving a BMC AMI Data Studio Data Editor Request

Do This
  • Right-Click on the CUSTOMER_TABLE again and select Open. 

image-2023-4-16_17-0-19.png


Do This
  • In the DevX Data Studio Editor Request dialog panel, type "CUSTOMER" for Request Name (this will allow us to save and reuse the request).
  • Click on Run to execute.

worddavf1bb01c02cd66af60c00e48148682169.png

Note: The DB2 for z/OS Data Editor Request Screen has "Use Selection Criteria Dataset(member)" instead of "SQL File". This allows users to use DevX Data Studio queries they may have saved in PDS files on the mainframe. 

The request is executed, and our data is returned.


Do This
  • Click Return to Request to modify with some selection criteria.

image-2023-4-16_17-4-10.png

Do This
  • Click on the Conditions tab .
  • Click on Select field link under the Column Name header.

image-2023-4-16_17-6-41.png

Do This
  • Click on the CUSTOMER_NUMBER column name.
  • Click OK. 

image-2023-4-16_17-10-24.png

Do This
  • Click on the select condition link under the Operator Header.
  • Select "contains" from the drop-down list.
  • Enter 'C%' under the Value column using single quotes, as shown below.
  • Click on the Add Condition button.
  • Click Save to save this request with the selection criteria.
  • Select "Run" to execute the query as defined.

image-2023-4-16_17-14-2.png

View and Manipulate Relational Data

Do This
  • Double-click the CUSTOMER_TABLE editor tab to maximize the view.

image-2023-4-16_17-16-30.png

Notice that some columns are not displayed in their entirety.


Do This
  • Right-click on the "CITY" column header.
  • Select Auto Resize Selected Columns. 


    Note the change.
     

image-2023-4-16_17-19-13.png


Do This
  • Right-click in the "AREA_CODE" column header.
  • Select Hide Column.

 Note the column is no longer displayed.

To reset right-click in any column header and select Show All Columns.

image-2023-4-16_17-21-52.png


Do This
  • Right-click on the CUSTOMER_NUMBER column header.
  • Select Freeze Columns.
  • Click on the CUSTOMER_NUMBER column.
  • Use the yellow arrow to move it to the frozen columns panel.
  • Click OK. 

image-2023-4-16_17-25-1.png



Note how when using the horizontal scroll bar that the frozen column remains stationary while unfrozen columns scroll. 

image-2023-4-16_17-27-35.png

Do This
  • Type CN into the blank column filter area below the CUSTOMER_NUMBER column header.
  • Hit the enter key. 

All data values containing CN are now displayed. 

  • Use the small red icon to clear the filter when done. 

image-2023-4-16_17-30-7.png

Now we will select multiple rows in our table using the column on the left containing row numbers.

Do This
  • Click on row number 1.
  • Shift-click on row 5 to select the block of rows.
  • Now ctrl-click on rows 10 and 12. 



Your selection should look like the screen shot.

image-2023-4-16_17-32-52.png

Do This
  • Right-click anywhere in the row number column.
  • Select Hide Row/Row(s). 

worddav264ff2474975b5b840aa1ebb1a1c99a1.png

The selected rows are now excluded from view.

Do This
  • Right-click in the row number column again and select Flip. 


Now the exclude bits are flipped and an inverted result set displayed.

worddav82add351c8e8de2de9e1da5e91038f6d.png

Flip is a toggle command. Use it multiple times to see the results. The Unhide All command can be used to bring all rows back into view.

With DevX Data Studio Editor it is easy to export data to multiple formats. 


Do This
  • Right-click in the row number column again and select Unhide All.
  • Click on the Export Data icon on the top right corner of the data view. 

image-2023-4-16_17-36-9.png



Do This
  • Click on the Format drop-down to see the various export formats supported. 
  • Click Cancel to close.

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

DevX Data Studio Editor can also easily display a single row at a time in a vertical formatted mode.


Do This
  • Click on the Row Editor icon (small eyeglass icon on grid) on top right corner of data view. 

image-2023-4-16_17-40-7.png


Do This
  • Use the black arrows and border drag with cursor to adjust which section is displayed and for optimal viewing if needed.
  • Click around on various row numbers in top view to see how the views automatically synchronize.
  • Close Row Mode by scrolling back up and clicking on the eyeglass icon again. 

image-2023-4-16_17-42-29.png

Find and Replace Data

Do This
  • Open the Find/Replace dialog by clicking the binoculars toolbar icon or pressing ctrl-F on your keyboard. 

image-2023-4-16_17-44-40.png

Do This
  • In the Find/Replace dialog box enter 312 as the find value and enter 313 as the Replace with value.
  • Select the Selected radio button in Column/Field Box .
  • Click on Select.

image-2023-4-16_17-46-55.png

Do This
  • Click to select the column CONTACT_AREA_CODE.
  • Click on the left yellow arrow icon to move from the Excluded Columns to the Selected Columns panel. 
  • Click OK. 


image-2023-10-9_12-4-56.png

Do This
  • Click on Replace All until the number of occurrences replaced is displayed in the message area. 
  • Click Close to remove the Find/Replace dialog box.

image-2023-4-16_17-50-40.png

Changed rows are marked with an icon in the row number column. Note the changed data values resulting from our Find/Replace. 

image-2023-4-16_17-53-8.png
For changed rows, the Undo command is available. You can undo changes in any order, even out of sequence of the order changes were made.


Do This
  • Click on any of the changed rows to select.
  • Right-click anywhere in the row number column (turns blue when selected). 
  • Select the Undo command to undo the change for the selected row. 



    Note there is an Undo All command as well. 



  • Click on X on tab to close the table and "don't save" your changes. 

image-2023-4-16_17-55-0.png

Selection criteria/queries can easily be reutilized in Topaz.

Do This
  • On the top menu bar, click the arrow next to the orange cylindrical File-AID icon.
  • Next click on Data Editor and you will see the CUSTOMER selection criteria that you saved. 
  • Click the arrow next to CUSTOMER and note that you can easily open or rerun your saved selection criteria.

image-2024-9-25_17-41-28.png

We have used a SQLServer table for this demonstration. Feel free to use the same table in one of the other databases to execute the steps again. Or open two or more tables simultaneously to get a feel for using the commands among the various tabs open. 
You are done with the File and Data Management DevX Data Studio Editor Test Drive! As a part of this exercise, you have been able to use DevX Data Studio Editor to:

  • Enable SQL Server Host Type in Host Explorer
  • Create filters for SQL Server and DB2 objects
  • Create and reuse queries/selection criteria
  • Browse/Edit multiple datatypes simultaneously
  • View and manipulate relational data
  • Find and Replace Data

Congratulations! This completes the BMC AMI DevX Data Studio Editor tutorial for BMC AMI Test Drive.


 

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