Retrieving data by using the SELECT statement
The simplest way to retrieve data is to use the SQL statement SELECT to specify a result table. You can specify the columns and rows that you want to retrieve.
Before you begin
Consider developing your own SQL statements similar to the examples in this section, and then run them dynamically using SPUFI. For a tutorial see Lesson 1.1: Querying data interactively.
You can also use the command line processor, or Db2 Query Management Facility (QMF).
Procedure
To retrieve data into a result table:
Examples
- Example 1: Selecting all columns with SELECT *
- You do not need to know the column names to select Db2 data. Use an asterisk (*) in the SELECT clause to indicate that you want to retrieve all columns of each selected row of the named table. Implicitly hidden columns, such as ROWID columns and XML document ID columns, are not included in the result of the SELECT * statement. To view the values of these columns, you must specify the column name.
The following SQL statement selects all columns from the department table:
SELECT * FROM DSN8C10.DEPT;The result table looks similar to the following output:
DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION ====== ============================== ====== ======== ======== A00 SPIFFY COMPUTER SERVICES DIV. 000010 A00 -------- B01 PLANNING 000020 A00 -------- C01 INFORMATION CENTER 000030 A00 -------- D01 DEVELOPMENT CENTER ------ A00 -------- D11 MANUFACTURING CENTER 000060 D01 -------- D21 ADMINISTRATION SYSTEMS 000070 D01 -------- E01 SUPPORT SERVICES 000050 A00 -------- E11 OPERATIONS 000090 E01 -------- E21 SOFTWARE SUPPORT 000100 E01 -------- F22 BRANCH OFFICE F2 ------ E01 -------- G22 BRANCH OFFICE G2 ------ E01 -------- H22 BRANCH OFFICE H2 ------ E01 -------- I22 BRANCH OFFICE I2 ------ E01 -------- J22 BRANCH OFFICE J2 ------ E01 --------Because the example does not specify a WHERE clause, the statement retrieves data from all rows.
The dashes for MGRNO and LOCATION in the result table indicate null values.
SELECT * is recommended mostly for use with dynamic SQL and view definitions. You can use SELECT * in static SQL, but doing so is not recommended because of possible host variable compatibility and performance implications. Suppose that you add a column to the table to which SELECT * refers. If you have not defined a receiving host variable for that column, an error might occur, or the data from the added column might not be retrieved.
If you list the column names in a static SELECT statement instead of using an asterisk, you can avoid problems that might occur with SELECT *. You can also see the relationship between the receiving host variables and the columns in the result table.
- Example 2: selecting specific columns with SELECT column-name
- Select the column or columns you want to retrieve by naming each column. With a single SELECT statement, you can select data from one column or as many as 750 columns. All columns appear in the order you specify, not in their order in the table.
For example, the following SQL statement retrieves only the MGRNO and DEPTNO columns from the department table:
SELECT MGRNO, DEPTNO FROM DSN8C10.DEPT;The result table looks similar to the following output:
MGRNO DEPTNO ====== ====== 000010 A00 000020 B01 000030 C01 ------ D01 000050 E01 000060 D11 000070 D21 000090 E11 000100 E21 ------ F22 ------ G22 ------ H22 ------ I22 ------ J22 - Example 3: Selecting data from implicity hidden columns
- To SELECT data from implicitly hidden columns, such as ROWID and XML document ID, look up the column names in SYSIBM.SYSCOLUMNS and specify these names in the SELECT list. For example, suppose that you create and populate the following table:
CREATE TABLE MEMBERS (MEMBERID INTEGER, BIO XML, REPORT XML, RECOMMENDATIONS XML);Db2 generates one additional implicitly hidden XML document ID column. To retrieve data in all columns, including the generated XML document ID column, first look up the name of the generated column in SYSIBM.SYSCOLUMNS. Suppose the name is DB2_GENERATED_DOCID_FOR_XML. Then, specify the following statement:
SELECT DB2_GENERATED_DOCID_FOR_XML, MEMBERID, BIO, REPORT, RECOMMENDATIONS FROM MEMBERS