Use SQL descriptors to extend DB2 for i database applications

Learn how to use SQL descriptors and their advantages

The SQLDA (SQL descriptor area) is the tried and true mechanism for SQL applications to provide and receive detail and data. The SQLDA was not designed to accommodate many of the recent extensions, attributes, and capabilities found within IBM® DB2® for i. The SQL descriptor support was added to DB2 for i on V5R4. Discover how this exciting extension to the SQL support allows application programs new ways to solve problems under the comforting umbrella of the DB2 standardized solution.

Scott L. Forstie (forstie@us.ibm.com), IBM DB2 for i - SQL development leader, IBM

Scott ForstieScott 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.



Tony Poirier (tpoirier@us.ibm.com), IBM DB2 for i - SQL developer, IBM

Tony PoirierTony Poirier is an advisory software engineer at IBM in Rochester, Minnesota. He has worked 19 years in SQL development for IBM DB2 for i.



05 November 2008

Introduction

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.


Allocating SQL descriptors

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 descriptors, 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.

For example:

EXEC SQL ALLOCATE DESCRIPTOR 'mydescriptor' WITH MAX 20;
EXEC SQL DEALLOCATE DESCRIPTOR 'mydescriptor';

Use of SQL descriptors in SQL statements

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'

Retrieving attributes in the descriptor area

To get information such as type, length, and Coded Character Set Identifier (CCSID) from the SQLDA, host language statements are used. A new SQL statement, GET DESCRIPTOR, is how information is retrieved from the SQL descriptor.

For example:

EXEC SQL GET DESCRIPTOR 'mydescriptor' VALUE 1
:type = TYPE, :length = LENGTH, :ccsid = DB2_CCSID;

This 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 GET DESCRIPTOR:

  • DB2_BASE_COLUMN_NAME
  • DB2_BASE_SCHEMA_NAME
  • DB2_BASE_TABLE_NAME
  • DB2_COLUMN_GENERATION_TYPE
  • DB2_COLUMN_UPDATABILITY
  • KEY_MEMBER

For a complete list of GET DESCRIPTOR attributes, see the "DB2 for i SQL Reference" (see Resources).


Setting attributes within the SQL descriptor

Analogous to retrieving attributes, attributes are set in the SQLDA using host language statements and are set in the descriptor area with the SET 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 descriptor.

EXEC SQL SET DESCRIPTOR 'mydescriptor' VALUE 1
TYPE = :dtype, LENGTH = :dlength, DATA = :datival;

Example programs using SQLDA and SQL descriptors

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

Compare the example program output

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
Side-by-side comparison of the sample program output

Conclusion

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.


Download

DescriptionNameSize
Sample programsprograms.zip4KB

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, IBM i
ArticleID=350327
ArticleTitle=Use SQL descriptors to extend DB2 for i database applications
publish-date=11052008