DBILITY

SQL Arrays in DB2 for i 7.1 본문

database/db2 for iseries

SQL Arrays in DB2 for i 7.1

DBILITY 2017. 8. 14. 09:49
반응형

http://ibmsystemsmag.com/CMSTemplates/IBMSystemsMag/Print.aspx?path=/ibmi/administrator/db2/SQL-Arrays-in-DB2-for-i-7-1

언젠가 사용할 지 몰라 붙여뒀다.

    Historically, to pass a list of values to or from an SQL procedure, you'd had a few options
  • Use a long list of input and output parameters. Over time, the procedure's parameter list can become unbearably long and difficult to understand.
  • Concatenate all the values together into one ad hoc "string" parameter. This requires more code to convert non-character types to character and back, and to construct and deconstruct the string. Unless the data is extremely simple, this approach is vulnerable to unexpected errors cropping up.
  • Create a temporary table (perhaps via the DECLARE GLOBAL TEMPORARY TABLE statement) to be used by the procedure, or return the data in a result set. Although this can work well, it's the weakest approach from a performance standpoint.

Array support in DB2 for i 7.1 gives you more options in your procedures and data access code.

Arrays can be defined as parameters and variables for SQL procedures and external procedures written in Java.

Arrays can be passed from one procedure to another as IN and OUT parameters.

DB2 for i 7.1 supports new functions, specific to SQL procedures, which let you easily transform arrays to tables and tables to arrays.

By integrating arrays into the relational model, DB2 offers both performance and ease of use for array types.

Finally, release 7.1 will be good news for vendors and developers on other database systems that already support arrays.

The array support in DB2 for i 7.1 is compatible with the support offered in DB2 LUW 9.5.

The IBM Toolbox for Java JDBC drivers have been updated for IBM i 7.1 to support array parameters through the standard JDBC APIs.

The Database Monitor and Visual Explain tools also received enhancements to support application development with arrays.

반응형
Comments