IBM DB2 for i has SQL descriptor support on V5R4 and IBM i operating system 6.1. In V5R3 and prior releases, the SQLDA (SQL descriptor area), a DB2-architected control block was used to pass variable information to and from SQL applications that used dynamic SQL.
On V5R4 and 6.1, an alternative method is available to SQL users for passing and receiving variable information to the database: SQL descriptors. SQL descriptors can be used to access information that isn't available in the SQLDA. Additionally, the user doesn't have to manage the space, as with the SQLDA. This article contrasts SQLDA and SQL descriptor support using two simple ILE C programs. Before digging into the sample programs, this article covers some of the basics of how SQL descriptors are used.
The space for the SQLDA and its setup is managed by the user. It needs to
be declared in the host language and have certain variables set before it
can be used in SQL statements. SQL descriptors are managed by the
database. Two new SQL statements are used to allocate and deallocate
ALLOCATE DESCRIPTOR and
DEALLOCATE DESCRIPTOR. Once established, the
descriptors are scoped to the session and remain in existence until the
end of the job or until they are deallocated. The name supplied during
allocation is the user's handle for working with the descriptor.
EXEC SQL ALLOCATE DESCRIPTOR 'mydescriptor' WITH MAX 20; EXEC SQL DEALLOCATE DESCRIPTOR 'mydescriptor';
SQL descriptors can be used in the same manner that the SQLDA is in SQL statements, with slightly different syntax.
For example (SQLDA style):
EXEC SQL DESCRIBE S1 INTO :mysqlda; EXEC SQL FETCH C1 INTO DESCRIPTOR :mysqlda;
Compared to (SQL descriptor style):
EXEC SQL DESCRIBE S1 USING SQL DESCRIPTOR 'mydescriptor'; EXEC SQL FETCH C1 INTO SQL DESCRIPTOR 'mydescriptor'
To get information such as type, length, and Coded Character Set Identifier
(CCSID) from the SQLDA, host language statements are used. A new SQL
GET DESCRIPTOR, is how information
is retrieved from the SQL descriptor.
EXEC SQL GET DESCRIPTOR 'mydescriptor' VALUE 1 :type = TYPE, :length = LENGTH, :ccsid = DB2_CCSID;
GET DESCRIPTOR statement retrieves the data
type, the maximum length of the data, and CCSID of character or graphic
data for the first entry in the SQL descriptor, placing the values in the
specified host variables. The SQLDA is limited to 30 character column
names, but SQL descriptors return the entire column name for columns that
are longer than 30 characters. Many more attributes can be retrieved from
an SQL descriptor than is available from the SQLDA. The following are
examples of some of the attributes that can be retrieved with
For a complete list of
attributes, see the "DB2 for i SQL Reference" (see
Analogous to retrieving attributes, attributes are set in the SQLDA using
host language statements and are set in the descriptor area with the
DESCRIPTOR SQL statement.
Since the database is managing the SQL descriptor, you need to indicate how many items could be needed. Consider the number of columns that will be referenced on SQL statements when deciding how many items to use.
The following example would set the number of items in the descriptor to 10:
EXEC SQL SET DESCRIPTOR 'mydescriptor' COUNT = 10
Once the number of items is set, the items themselves can be used. The next
statement shows how the first item (out of 10) could set the data type,
maximum data length, and the data value to be used. A difference between
the SQLDA and the descriptor is that changing the above host variables in
the host language program following the
SET DESCRIPTOR statement would not impact the
EXEC SQL SET DESCRIPTOR 'mydescriptor' VALUE 1 TYPE = :dtype, LENGTH = :dlength, DATA = :datival;
Before looking at the exciting stuff, let's establish a sample corporate database to be used by the example programs. Execute the following statements in Listing 1 using System i Navigator's Run SQL Scripts interface or a green screen interface. The ViewOverDepartment view will be the target of the sample programs. Note that the view uses longer column names and is built upon three base tables.
Listing 1. Establish a sample corporate database
CALL QSYS.CREATE_SQL_SAMPLE('DESCSTUDY'); SET SCHEMA = DESCSTUDY; SET PATH = DESCSTUDY; DROP VIEW DESCSTUDY.ViewOverDepartment ; CREATE VIEW DESCSTUDY.ViewOverDepartment ( DepartmentNumber , DepartmentName , CorporateDatabaseAccount_EmployeeNumber, CorporateDatabaseAccount_EmployeeLastName, CorporateDatabaseAccount_EmployeePicture, CorporateDatabaseAccount_EmployeeRowIdentifier, CorporateDatabaseAccount_EmployeePictureURL ) AS SELECT ALL A.DEPTNO, A.DEPTNAME, B.EMPNO, B.LASTNAME, C.PICTURE, C.EMP_ROWID, C.DL_PICTURE FROM EMP_PHOTO C INNER JOIN (VDEPT A INNER JOIN VEMPDPT1 B ON A.DEPTNO=B.DEPTNO) ON C.EMPNO=B.EMPNO;
Included with this article are two programs (Listing 2 and Listing 3) that retrieve column information about a query. (They are also available for download.) The first (Listing 2) uses SQL descriptors, while the second (Listing 3) uses a traditional SQLDA implementation. The SQLDA program must allocate the SQLDA using host language statements. This makes it more difficult to port to other host languages. More significantly, the SQLDA program is able to return much less information about the columns in the query. Among the information highlighted in the program that is only available with the SQL descriptor-based program are:
- Column name, when it is longer than 30 characters
- Base table information (schema, table name, column name)
- Updatability of column
A side-by-side comparison of the sample program output shows the clear advantage to be gained by using SQL descriptors. The SQL descriptor-based program output appears on the left-hand side of Figure 1:
Figure 1. Side-by-side comparison of the sample program output
This article has given a glimpse into the capabilities that are available through SQL descriptors. The SQLDA is an inflexible structure that cannot be extended to cover longer names or extended attributes. Consider using the SQL descriptor support when implementing or extending SQL applications.
for i SQL Reference
(IBM, 2008): Explore the definitions of SQL as supported by IBM DB2 for i.
Find reference information for the tasks of system administration,
database administration, application programming, and operation.
(IBM, 2008): Learn more about the System i implementation of the SQL using
the DB2 for i5/OS database and the IBM DB2 Query Manager and SQL
Development Kit for i5/OS licensed program.
developerWorks resource page
for DB2 for i: Find articles
and tutorials, and connect to other resources to expand your skills on DB2
developerWorks Information Management zone:
Learn more about Information Management. Find technical documentation,
how-to articles, education, downloads, product information, and more.
- Stay current with
developerWorks technical events and webcasts.
Browse for books on these and other technical topics.
Get products and technologies
- Build your next
development project with
IBM trial software,
available for download directly from developerWorks.
- Participate in
and get involved in the developerWorks community.
Scott Forstie is a senior software engineer at IBM, and he is the SQL development leader for IBM DB2 for i in Rochester, Minnesota. Before working on DB2, he worked on UNIX® enablement for the AS/400® and S/390® systems.