Rational DA Data Architecture and DB2 9: Build an SQL command

Are you familiar with Database Explorer? Whether or not, please read the following article to learn about some of the functions and components of this type of data architecture, more specifically about the ability to build SQL commands encountered in the database (database ) yours.

Build an SQL command

Rational DA (rational data architecture) has a built-in tool for creating SQL commands. You can access it from various Rational DA sources such as using the Database Explorer virtual table .

See how to build the underlying query using Rational DA:

SELECT DEPARTMENT.LOCATION, EMPLOYEE.EMPNO, EMPLOYEE.FIRSTNME, EMPLOYEE.LASTNAME, EMPLOYEE.PHONENO FROM DEPARTMENT, EMPLOYEE WHERE DEPARTMENT.DEPTNO = EMPLOYEE.WORKDEPT AND EMPLOYEE.SEX = 'F' ORDER BY LASTNAME DESC, FIRSTNME DESC

This query returns results that are all female employees in the company.

To build an SQL command from the Database Explorer virtual table, perform the following steps:

1. Right-click the database connection object and select New SQL Statement .

You can choose this option from a seamless or separate database connection object. If you select New SQL Statement from a removable object, it will automatically create a connection to the database. Then right-click on the database object and select the option.

2. In the New SQL Statement window, type the command name in the Statement name box and select the query type in the Statement template list. For example, name the Statement name FemaleEmployees , Statement template as SELECT and place SQL builder for the Edit using box, then click OK . The SQL generator will be opened with the command template SELECT.

In the Statement template list, Rational DA provides original templates for all query types, such as: INSERT, UPDATE, DELETE, FULLSELECT, and WITH (for common table expression definitions (Common Table Expression)).

The SQL builder option cites a SQL command creation tool that supports graphics, allowing you to build SQL commands with drag-and-drop operations, simple cursor-and-click attribute changes (you'll see at The bottom step of the lesson).

SQL command generator as shown below:

The SQL editor option only opens the SQL command editor (which is part of the SQL builder, which contains SQL commands in SQL builder). The SQL editor provides some common help codes such as syntax coloring and automatic schema lookup, but does not provide drag-and-drop functions associated with the SQL builder to build SQL commands. We will be interested in the SQL editor in a recent article.

3. Right-click the Tables cell and select Add Table . The Add Table window opens. We will take the example with two EMPLOYEE and DEPARTMENT tables placed in the SAMPLE database.

As you can see in the image above, HR and PAULZ data schemas are displayed in the Add Table window.

Notice that you will also see an option to create an alias for the table in each query that is also displayed. Table aliases make it easier to reference it in SQL commands. When using aliases to represent tables (or virtual tables), Rational DA will adjust the SQL syntax and interface accordingly as shown below:

When adding both tables to the SQL builder workspace, the workspace of Rational DA will take the form:

Rational DA supports drag-and-drop operations from the Database Explorer virtual table to the SQL builder. Simply click and drag the table you want to add to the Tables frame, then release the mouse button to bring the table to the desired location.

This drag-and-drop support replaces the Add Table option described in the previous step; The drag-and-drop table selection process provides a more natural form of query building:

4. Right-click the Tables cell, select Create Join to create the link as shown in the figure below:

For example, create a link between the DEPARTMENT and EMPLOYEE tables, using the DEPARTMENT.DEPTNO and EMPLOYEE.WORKDEPT columns as shown above.

If you select two incompatible connection columns, the SQL builder will not allow linking two tables because of an error at runtime. Type of error has the form:

You can use the drag-and-drop method to create a link directly from the Tables cell by hovering over the connection column on a table, then releasing the mouse on the connection column in the destination table:

As shown in the illustration, when hovering over a column, the data type of the column below is given in the explanation next to it. This enriches the ability of design time and minimizes the possibility of link errors. If creating a link between two incompatible data types, Rational DA will change the mouse icon to an error indicator and stop the operation.

5. Click the corresponding square next to the columns if you want to have them in the result set of the SQL command from both tables. Make sure that the SQL generator looks similar to the example by selecting the following columns: DEPARTMENT.DEPTNAME, DEPARTMENT.LOCATION, EMPLOYEE.EMPNO, EMPLOYEE.FIRSTNME, EMPLOYEE.LASTNAME, and EMPLOYEE.PHONENO.

Notice how the SQL command you are building in the SQL Source cell changes positively when performing several different operations such as adding or removing tables, adding or limiting columns, and identifying associated attributes. .

Also note that Rational DA gives you a visual explanation by trading relationships encoded within the data schema related to the columns of each table. For example, as shown in the image above, you can see that DEPTNO and EMPNO columns are the primary key field by the trailing space sign:

. The MGRNO and ADMRDEPT columns of the DEPARTMENT table as well as the WORKDEPT column of the EMPLOYEE table are foreign key fields linked to another table also by the following space sign:

.

You can use the Columns tab at the end of the SQL generator to add columns to your SQL statement, simply by clicking on the Column list and selecting one or more columns you want to add.

6. Use the Columns tab, sort the order through the Sort Type columns and the Sort Order . Click on them and select the corresponding option. For example, the Columns tab has the form:

As you can see, the results of the first example SQL command will be sorted by the LASTNAME (last) field with the descending sequence from Z to A (you can select ascending from A -> Z if want); then continue to be sorted by FIRSTNAME (name) field, as described in the Sort Order column, also with the descending sequence as described in the Sort Type column.

7. Use conditional attributes for SQL commands (eg WHERE clause) by selecting the Conditions tab and building conditions when clicking on each corresponding column:

For example, to enter values ​​for the Value field simply click on it and enter the value instead of building the expression.

Note : Don't forget that the SEX column (gender) of the EMPLOYEE table is of type CHARACTER (character). Therefore, the value you describe in the Value column is uppercase and must be placed in pairs of single quotes (''). That's why I use the letter 'F' in the picture above.

8. You can use group options if you want to add it to the SQL command through the Groups and Group Conditions tab at the end of the SQL generator.

This step can be omitted if it is not necessary. But you should learn about it in case the SQL command you are building requires.

9. Press Ctrl + S to record the query. The query will look similar to the image below:

10. Check that the query has been built by clicking Run -> Run SQL or clicking on the corresponding icon as shown below:

The output part and the accompanying result set are displayed in the Data Output tab at the bottom of the Rational DA workspace:

Minimize time design errors with Rational DA

With a Database Explorer, you can set up database connection to work in offline model. It has an effective effect on the creation of cache schema (temporary storage of data schema) for database objects. This option allows creating models, SQL commands, and more without using a database connection. It also enhances the exploitation of additional benefits with the design-time helpdesk within Rational DA such as code support and SQL command parser. This component also helps to reduce a large number of coding errors that you normally cannot detect until deployed in practice.

For example, when writing an SQL command, Rational DA will warn you that the SQL command fails (even if the syntax is correct) when an error occurs such as changing from PAULZ.DEPARTMENT.DEPTNO to PAULZ.DEPARTMENT.DEPTNUMB , not true for the database and press Ctrl + S.

Rational DA will immediately display an error message for you, similar to:

( Error of validation: Cannot find PAULZ.DEPARTMENT.DEPTNUMB column. Some SQL generator functions have been deactivated. To allow these functions to work, change the syntax and record command .

To restore the recently made changes, select 'Revert to Last Correct Source' on the SQL menu).

Once again, you can see how the cache schema mechanism saves you valuable time when designing SQL commands. It is used to alert commands that are problematic during design time instead of at the test stage or at runtime.

In the figure below, you can see that Rational DA provides a number of possible causes for the SQL command to show an error. On the left of the figure, the bottom part creates a gray SQL to indicate that an error has occurred. (The symbol r is set to display this query incorrectly). The right side of the image (symbol a) is the exact written SQL command. Note, parts of the SQL generator are not polished.

Rational DA also provides a number of other tools to minimize design time errors, but to learn more about them is beyond the limit of the lesson. Hope we will meet this topic again in the near future.

Cannot write SQL command?

Sometimes you cannot write the newly created SQL command because there is no place to write it. This is one of the drawbacks of the SQL build process from the Database Explorer virtual table. If you want to write the SQL statement for future use, you need to create it as part of a project. (Of course it is always possible to use copy / paste to cut / paste SQL commands into a Rational DA project or to a separate file).

If you take a closer look at the SQL commands shown in the article, you'll see there is an asterisk (*) next to the query name to indicate that it's not a query forever.

If you want to create a direct write query inside Rational DA, you need to create a Data Design or Data Development project.

Follow these steps to create a Data Development project and use the steps described above to create SQL commands. Do as follows:

1. Go to File -> New -> Project , select Data Development Project from the New Project window, then click Next . (You can select Show All Wizards to see this project).

2. Enter a name for the Data Development project (eg MyFirstRDAProject ) and click Next .

You can use this window to set other options such as the default data schema for the project and optionally identify schema names in the generated commands. But in the scope of this article, we will skip this step.

3. Describe the default database connection you want to use for the project (in this case, DB2SAMPLE ) and click Next .

Here, the DB2SAMPLE database connection is selected. If you do not have a connection to the destination database, you can go to Create a new connection and click Next . (The frames in Wizard Add Database Connection will appear. So just follow the steps indicated).

4. Describe the default path for JDK and click Finish . By default, this box is pre-filled with the JavaTM path in the DB2 9 Database:

Notice in step 3 that the Finish button works after you select the object to connect to the target database. This happens because the JDK home field is populated by the Rational DA by default. If this field is not filled, the Finish button will not work. And later, you just need to click Finish is enough.

A Data Development project is created in the Data Project virtual table, usually located on the Database Explorer in the Rational DA workspace (you can adjust this order position at any time).

5. Expand the new project, select SQL Scripts -> New -> SQL Statement .

6. In the New SQL Statement window, select the project you want to create a new SQL command and click Next .

By default, this Wizard project is used to store new SQL commands. You can choose from other projects that exist in Rational DA by clicking Project or creating a new project by clicking New .

7. Follow the steps described above to recreate the FEMALEEMPLOYEES command (find the female employee in the company). When writing and checking this SQL command, the Rational DA Data Project Explorer virtual table takes the form:

You can see that the FemaleEmployees SQL command can now be written (and queried) from within the newly created Data Development project.

Summary

In this article, I showed you how to use a database connection to create new SQL commands and all the Rational DA development support provided for this process. There is also a way to make sure you can query the SQL statement later by using a Data Development project. ( Note : You can also use the Data Design project instead of Data Development ). We will meet again in the next part of this article with what can be done in Rational DA with a written SQL command as well as some other SQL editor functions that have not been introduced in the article.