DB2 application development is the construction of application programs with SQL statements embedded in it. The programming languages or host languages as they are known, currently supported are APL2, Assembler H. IBM BASIC, OS/VS COBOL, VS COBOL II, C/370, FORTRAN, LISP and, PL/I. DB2 application programs can be run in the execution environments like MVS batch using Call Attachment Facility (CAF), TSO batch, DL/I batch, CICS, IMS/DC, IMS/BMP, TSO Interactive, etc. This book uses COBOL as the principal host language, because it is most widely used. But of course the ideas are fairly general and translate into other host languages with only comparatively minor differences.
Embedded SQL is a method of combining the computing power of a programming language and the database manipulation capabilities of SQL. Embedded SQL statements are SQL statements written in line with the program source code of the host language. The embedded SQL statements are parsed by an embedded SQL preprocessor and replaced by host-language calls to a code library. The output from the preprocessor is then compiled by the host compiler. This allows programmers to embed SQL statements in programs written in any number of languages such as: C/C++, COBOL and Fortran.
The ANSI SQL standards committee defined the embedded SQL standard in two steps: a formalism called Module Language was defined, then the embedded SQL standard was derived from Module Language. The SQL standard defines embedding of SQL as embedded SQL and the language in which SQL queries are embedded is referred to as the host language. A popular host language is C. The mixed C and embedded SQL is called Pro*C in Oracle and Sybase database management systems. In the PostgreSQL database management system this precompiler is called ECPG. Other embedded SQL precompilers are Pro*Ada, Pro*COBOL, Pro*FORTRAN, Pro*Pascal, and Pro*PL/I. This is presented in the following table:
Embedding SQL statements in the application program make the programming easy and the programmer is freed from the tedious jobs of opening, closing of files, coding the file descriptions etc.
The main differences between the conventional programming using flat file programming and DB2 programming is summarized in the following table:
Differences between Conventional Flat File and DB2 Programming
All embedded SQL statements must be enclosed between delimiters. The delimiters for COBOL are 'EXEC SQL' and 'END-EXEC. These delimiters should be coded for each and every SQL statement and must start in column 12.
The following are some practices/guidelines for embedded SQLs, which will make you DB2 application program, perform better and will improve the maintainability.
The SQLCA contains fields, which are used for communicating information describing the success or failure of the execution of an embedded SQL. The most important among them is the SQLCODE, which contains the return code passed by DB2 to the application program. The return code provides information about the execution of the last SQL statement. A value of zero indicates successful execution, a positive value indicates successful execution but with an exception and a negative value indicates failure during execution. By knowing the value of the SQL code the problem of failure can be pinpointed and this is a very useful tool in debugging.
But it is always better to avoid the use of WHENEVER statement. It is always safe to code specific SQLCODE checks after each SQL statement and process accordingly.
Host Variables, Structures and Null indicators
A host variableis an area of storage allocated by the host language and referenced in the SQL statement. The host variables are defined and named using the host language syntax. In COBOL they must be defined in the DATA DIVISION of the program in the WORKING-STORAGE section or the LINKAGE section. Moreover, when you use the INCLUDE verb to specify the host variable they should be delimited by the EXEC SQL and END-EXEC. When using the host variables in the SQL statements prefix them with a colon(:).
When the same variable is referenced outside the SQL statement in the COBOL program, do not use the colon, because it will result in compilation errors.
Host variables are means of moving data from the program to DB2 and from DB2 to the program. Data can be read from the file, placed into the host variable and used to modify a DB2 table through SQL. For data retrieval host variables are used to house the retrieved data. Host variables are also used to change the value of the predicates in the WHERE clauses.
DCLGEN, a DB2 utility, produces host variables named as the same as the columns. Use of DCLGEN reduces the coding effort. But when using tables with the same column names, edit the DCLGEN output tp make the host variable names unique. This confusion can be avoided if you prefix the column names with an abbreviated table identifier, while table definition.
In addition to host variables SQL statements can use host structures to enable SQL statements to specify a single structure for storing all retrieved columns. A host structure is thus a COBOL group level data area composed of host variables for all columns to be returned by a given select statement.Example A COBOL variable is defined in the working storage section as:
This when used in the SQL statements should be prefixed with a colon as follows:
This populates the host variables for all columns defined under the DCLEMPLOYEE group-level data area.
Before you select or insert a column that can be set to null, it must have an indicator variable, called null indicator, for it. You can also use null indicator variables with UPDATE statement to set columns to null. A third and very important use of null indicators (which we have discussed earlier) is when any column is retrieved using the column functions AVG, SUM, MAX and MIN the only exception being COUNT which does not returns the value 0 if no rows meeting the criteria are found.
The null indicator variable is separate form both the columns to which it pertains and the host variable for that column. To determine the value of any nullable column, a host variable and an indicator column are required. The host variable contains the value of the column when it is not null. The indicator variable contains one of the following values to indicate a column's null status:
You can use null indicator variables with host variables in the following situations:
Use a null indicator variable when referencing a nullable column. Failure to do so will result in a -305 SQLCODE. If you fail to check the null status of the column being retrieved, your program might continue to execute, but with unpredictable results.
There are two types of embedded SQL SELECT statements, singleton SELECTS and cursor SELECTs. SQL statements operate on a set of data and return a set of data. Host language programs, on the other hand, operate on a row at a time. A singleton SELECJ simply a SQL SELECT statement that returns a single row. The singleton SELECT differs from the ordinary SELECT statement in that it contains an INTO clause. The INTO clause is where you code your host variables that accept the data returned by DB2. But if such a SELECT statement returns more than on row, the values of the first row is placed in the host variable and DB2 issues an SQLCODE of -811. So in your application program, if the SELECT will return more than one row then use must use Cursors.
DB2 uses cursors to navigate through a set of rows returned by an embedded SQL SELECT statement. A cursor can be compared to a pointer. The programmer declares a cursor and defines the SQL statement for the cursor. After that you can use the cursor like a sequential file. The cursor is opened, rows are fetched from the cursor, one row at a time and at the end of processing the cursor is closed. The four operations that must be performed for the successful working of the cursor are:
When coding embedded SQLs using cursors the following guidelines will improve the performance and maintainability of the program:
WITH HOLD prevents subsequent COMMITs from destroying the intermediate results table from the SELECT statement, thereby saving positioning within the cursor. The WITH HOLD option is not available for cursors coded in CICS programs.
Batch Programming Guidelines
Line Programming Guidelines
IBM Mainframe Related Interview Questions
|IBM Lotus Notes Interview Questions||IBM-CICS Interview Questions|
|COBOL Interview Questions||Linux Interview Questions|
|IBM-JCL Interview Questions||IBM Mainframe Interview Questions|
|IBM AIX Interview Questions||IBM WAS Administration Interview Questions|
|IBM Lotus Domino Interview Questions||IBM Integration Bus Interview Questions|
|Mainframe DB2 Interview Questions||Unix Production Support Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.