Advantages of DB2 CLI over embedded SQL

<div dir="ltr" style="text-align: left;" trbidi="on"><h1 style="text-align: justify;">Advantages of DB2 CLI over embedded SQL</h1><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgREk6eMWPtvXFPtVPVdshT7Iu_fPEbrZIDyaja-mGL7T6CQ1QC-HvWUFgK0n3O78dqb-d3JEdX37lvPMYBVmaKTX3DlNE3nSRREoZNjSJjLBPfnNeLb_ASPa_nq1XejRBp_TUsnTzpjAc/s1600/DB2-concepts+at+www.topnaukrialert.com.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgREk6eMWPtvXFPtVPVdshT7Iu_fPEbrZIDyaja-mGL7T6CQ1QC-HvWUFgK0n3O78dqb-d3JEdX37lvPMYBVmaKTX3DlNE3nSRREoZNjSJjLBPfnNeLb_ASPa_nq1XejRBp_TUsnTzpjAc/s1600/DB2-concepts+at+www.topnaukrialert.com.jpg" width="320" /></a></div><div><br /></div><div style="text-align: justify;"><span style="font-size: large;"><a href="https://www.blogger.com/null" name="idx301"></a><a href="https://www.blogger.com/null" name="idx302"></a>The DB2 CLI interface has several key advantages over embedded SQL. </span></div><div style="text-align: justify;"><span style="font-size: large;"><br /></span></div><ul type="disc"><li class="MsoNormal" style="mso-list: l1 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">It is ideally suited for a client-server environment, in which the target database is not known when the application is built. It provides a consistent interface for executing SQL statements, regardless of which database server the application is connected to. </span></li><li class="MsoNormal" style="mso-list: l1 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">It increases the portability of applications by removing the dependence on pre-compilers. Applications are distributed not as embedded SQL source code which must be preprocessed for each database product, but as compiled applications or run time libraries. </span></li><li class="MsoNormal" style="mso-list: l1 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">Individual DB2 CLI applications do not need to be bound to each database, only bind files shipped with DB2 CLI need to be bound once for all DB2 CLI applications. This can significantly reduce the amount of management required for the application once it is in general use. </span></li><li class="MsoNormal" style="mso-list: l1 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">DB2 CLI applications can connect to multiple databases, including multiple connections to the same database, all from the same application. Each connection has its own commit scope. This is much simpler using CLI than using embedded SQL where the application must make use of multi-threading to achieve the same result. </span></li><li class="MsoNormal" style="mso-list: l1 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">DB2 CLI eliminates the need for application controlled, often complex data areas, such as the SQLDA and SQLCA, typically associated with embedded SQL applications. Instead, DB2 CLI allocates and controls the necessary data structures, and provides a <span class="italic">handle</span> for the application to reference them. </span></li><li class="MsoNormal" style="mso-list: l1 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">DB2 CLI enables the development of multi-threaded thread-safe applications where each thread can have its own connection and a separate commit scope from the rest. DB2 CLI achieves this by eliminating the data areas described above, and associating all such data structures that are accessible to the application with a specific handle. Unlike embedded SQL, a multi-threaded CLI application does not need to call any of the context management DB2^(R) APIs; this is handled by the DB2 CLI driver automatically. </span></li><li class="MsoNormal" style="mso-list: l1 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">DB2 CLI provides enhanced parameter input and fetching capability, allowing arrays of data to be specified on input, retrieving multiple rows of a result set directly into an array, and executing statements that generate multiple result sets. </span></li><li class="MsoNormal" style="mso-list: l1 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">DB2 CLI provides a consistent interface to query catalog (Tables, Columns, Foreign Keys, Primary Keys, etc.) information contained in the various DBMS catalog tables. The result sets returned are consistent across DBMSs. This shields the application from catalog changes across releases of database servers, as well as catalog differences amongst different database servers; thereby saving applications from writing version specific and server specific catalog queries. </span></li><li class="MsoNormal" style="mso-list: l1 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">Extended data conversion is also provided by DB2 CLI, requiring less application code when converting information between various SQL and C data types. </span></li><li class="MsoNormal" style="mso-list: l1 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">DB2 CLI incorporates both the ODBC and X/Open CLI functions, both of which are accepted industry specifications. DB2 CLI is also aligned with the ISO CLI standard. Knowledge that application developers invest in these specifications can be applied directly to DB2 CLI development, and vice versa. This interface is intuitive to grasp for those programmers who are familiar with function libraries but know little about product specific methods of embedding SQL statements into a host language. </span></li><li class="MsoNormal" style="mso-list: l1 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">DB2 CLI provides the ability to retrieve multiple rows and result sets generated from a stored procedure residing on a DB2 Universal Database (or DB2 Universal Database for z/OS and OS/390 version 5 or later) server. However, note that this capability exists for Version 5 DB2 Universal Database clients using embedded SQL if the stored procedure resides on a server accessible from a DataJoiner^(R) Version 2 server. </span></li><li class="MsoNormal" style="mso-list: l1 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">DB2 CLI offers more extensive support for scrollable cursors. With scrollable cursors, you can scroll through a cursor as follows: </span></li><ul type="circle"><li class="MsoNormal" style="mso-list: l1 level2 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list 1.0in; text-align: justify;"><span style="font-size: large;">Forward by one or more rows </span></li><li class="MsoNormal" style="mso-list: l1 level2 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list 1.0in; text-align: justify;"><span style="font-size: large;">Backward by one or more rows </span></li><li class="MsoNormal" style="mso-list: l1 level2 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list 1.0in; text-align: justify;"><span style="font-size: large;">From the first row by one or more rows </span></li><li class="MsoNormal" style="mso-list: l1 level2 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list 1.0in; text-align: justify;"><span style="font-size: large;">From the last row by one or more rows. </span></li></ul></ul><div class="MsoNormal" style="margin-left: 0.5in; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; text-align: justify;"><span style="font-size: large;">Scrollable cursors can be used in conjunction with array output. You can declare an updatable cursor as scrollable then move forward or backward through the result set by one or more rows. You can also fetch rows by specifying an offset from: </span></div><ul type="disc"> <ul type="circle"><li class="MsoNormal" style="mso-list: l1 level2 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list 1.0in; text-align: justify;"><span style="font-size: large;">The current row </span></li><li class="MsoNormal" style="mso-list: l1 level2 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list 1.0in; text-align: justify;"><span style="font-size: large;">The beginning or end of the result set </span></li><li class="MsoNormal" style="mso-list: l1 level2 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list 1.0in; text-align: justify;"><span style="font-size: large;">A specific row you have previously set with a bookmark. </span></li></ul></ul><div class="MsoNormal" style="text-align: justify;"><span style="font-size: large;"><br /></span></div><div class="MsoNormal" style="text-align: justify;"><span style="font-size: large;"><br /></span></div><div class="MsoNormal" style="text-align: justify;"><span style="font-size: large;"><br /></span></div><div class="MsoNormal" style="text-align: justify;"><span style="font-size: large;"><br /></span></div><h3 style="text-align: justify;"><span style="font-size: large;">When to use DB2 CLI or embedded SQL </span></h3><div style="text-align: justify;"><span style="font-size: large;"><a href="https://www.blogger.com/null" name="idx303"></a><a href="https://www.blogger.com/null" name="idx304"></a>Which interface you choose depends on your application. </span></div><div style="text-align: justify;"><span style="font-size: large;">DB2 CLI is ideally suited for query-based graphical user interface (GUI) applications that require portability. The advantages listed above, may make using DB2 CLI seem like the obvious choice for any application. There is however, one factor that must be considered, the comparison between static and dynamic SQL. It is much easier to use static SQL in embedded applications. </span></div><div style="text-align: justify;"><span style="font-size: large;">Static SQL has several advantages: </span></div><ul type="disc"><li class="MsoNormal" style="mso-list: l0 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">Performance </span></li></ul><div style="margin-left: 0.5in; text-align: justify;"><span style="font-size: large;">Dynamic SQL is prepared at run time, static SQL is prepared at precompile time. As well as requiring more processing, the preparation step may incur additional network-traffic at run time. The additional network traffic can be avoided if the DB2 CLI application makes use of deferred prepare (which is the default behaviour). </span></div><div style="margin-left: 0.5in; text-align: justify;"><span style="font-size: large;">It is important to note that static SQL will not always have better performance than dynamic SQL. Dynamic SQL is prepared at runtime and uses the database statistics available at that time, whereas static SQL makes use of database statistics available at BIND time. Dynamic SQL can make use of changes to the database, such as new indexes, to choose the optimal access plan, resulting in potentially better performance than the same SQL executed as static SQL. In addition, pre compilation of dynamic SQL statements can be avoided if they are cached. </span></div><ul type="disc"><li class="MsoNormal" style="mso-list: l0 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">Encapsulation and Security </span></li></ul><div style="margin-left: 0.5in; text-align: justify;"><span style="font-size: large;">In static SQL, the authorizations to access objects (such as a table, view) are associated with a package and are validated at package binding time. This means that database administrators need only to grant execute on a particular package to a set of users (thus encapsulating their privileges in the package) without having to grant them explicit access to each database object. In dynamic SQL, the authorizations are validated at run time on a per statement basis; therefore, users must be granted explicit access to each database object. This permits these users access to parts of the object that they do not have a need to access. .</span></div><div style="margin-left: 0.5in; text-align: justify;"><span style="font-size: large;"><br /></span></div><div style="margin-left: 0.5in; text-align: justify;"><span style="font-size: large;"><br /></span></div><div style="margin-left: 0.5in; text-align: justify;"><span style="font-size: large;"><br /></span></div><ul type="disc"><li class="MsoNormal" style="mso-list: l0 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">Embedded SQL is supported in languages other than C or C++. </span></li><li class="MsoNormal" style="mso-list: l0 level1 lfo2; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in; text-align: justify;"><span style="font-size: large;">For fixed query selects, embedded SQL is simpler. </span></li></ul><div style="text-align: justify;"><span style="font-size: large;">If an application requires the advantages of both interfaces, it is possible to make use of static SQL within a DB2 CLI application by creating a stored procedure that contains the static SQL. The stored procedure is called from within a DB2 CLI application and is executed on the server. Once the stored procedure is created, any DB2 CLI or ODBC application can call it. </span></div><div style="text-align: justify;"><span style="font-size: large;">It is also possible to write a mixed application that uses both DB2 CLI and embedded SQL, taking advantage of their respective benefits. In this case, DB2 CLI is used to provide the base application, with key modules written using static SQL for performance or security reasons. This complicates the application design, and should only be used if stored procedures do not meet the applications requirements. </span></div><span style="font-size: large;"><br /></span><div style="text-align: justify;"><span style="font-size: large;">Ultimately, the decision on when to use each interface, will be based on individual preferences and previous experience rather than on any one factor. </span></div></div>

Advantages of DB2 CLI over embedded SQL

Advantages of DB2 CLI over embedded SQL The DB2 CLI interface has several key advantages over embedded SQL. It is ideally suited for a ...

Read more »