SQL query source–operator properties

General page

Label

Type a name for the highlighted operator on the data flow canvas.

Description

Type a description to record additional information about the highlighted operator. The description is optional.

Location

Specify the location of the source database.

SQL execution database

Select this option when the database is local to the DB2® database where the data flow runs. The SQL execution database is defined in the General page of the data flow.

Remote database

Select this option when the database is not local to the SQL execution database, and is accessible via JDBC. This option supports all JDBC-compliant relational databases.

Database connection

Specify the remote database name either as a fixed value or a variable. When you specify a fixed value, this property is displayed as a list. For a variable value, the property is displayed as a field.

SELECT Statement page

SELECT statement

Type a SELECT statement in this field. You can type only one SELECT statement per operator to select and filter the data that you want to transform.

SELECT statement can be nested in a bigger SQL statement

Select this check box so that at run time the Design Studio creates a nested SELECT statement to pass the result of the specified SELECT statement query to the next operator in the data flow.

Nesting a SELECT statement in a bigger SQL statement is the most efficient method for the Design Studio to deal with user-specified SQL code. Because the Design Studio cannot parse the SQL code that you type in the SELECT statement field, the Design Studio cannot decide whether the user-specified SELECT statement can be nested in bigger SQL statements.

By using the SELECT statement can be nested in a bigger SQL statement check box, you tell the Design Studio whether the specified SELECT statement can be nested in a bigger SQL statement.

Example

You specified the following SELECT statement in the SELECT statement field:

SELECT * from employee

• If you select the SELECT statement can be nested in a bigger SQL statement check box, then at run time, the Design Studio can generate the following nested SELECT statement:

  SELECT COL_1 AS A, COL_2 AS B
  FROM (SELECT * from employee) AS Q47

• If you do not select the SELECT statement can be nested in a bigger SQL statement check box, then the SQL SELECT statement is not nested. Instead the Design Studio generates the following view to wrap the specified SELECT statement:

  CREATE VIEW MYVIEW01. TEMP6 (COL_1, COL_2) AS (SELECT * from employee)

Note: If you typed a SELECT statement that cannot be nested in a bigger SQL statement and selected the SELECT statement can be nested in a bigger SQL statement check box, the Design Studio creates a nested SELECT SQL statement. However, the generated SQL statement might have incorrect syntax.

Result Columns page

Result columns

Shows the columns in the order that they will appear in the output table. Move selected columns up or down in the list by clicking or . You can add or delete columns by clicking or . These result columns and expressions represent the table schema that is sent to the input port of the next operator in the data flow.

Remember: The Result Columns page is automatically populated if there is a live connection to the database where the SQL SELECT is executed.