In Mainframe, it is possible to run a SQL query without writing a COBOL program. You can do it by using, SPUFI, QMF and IBM Studio.
SQL query must begin with EXEC SQL and end with END-EXEC. Second, your SQL query uses COBOL host variables.
Host variables in COBOL:
Host variables appear with a colon (:) prefix in SQL statements. Host variables must be defined properly in COBOL; they are the exact COBOL equivalent of the column attribute.
DELETE statements do not require host variables since they delete all the rows (the set) identified in the WHERE clause. SELECT statements, on the other hand, always require host variables.
INSERT statements usually require host variables, but you can also specify data in a VALUES clause.
UPDATE statements also require host variables, but only for columns whose data you are going to change. For example, the following SQL query uses an UPDATE statement to change the sex of employee
MOVE ‘FEMALE’ TO SEX OF DCLEMPLOYEE
EXEC SQL UPDATE EMPLOYEE
SET SEX = :DCLEMPLOYEE-SEX
WHERE EMP_ID = 1
END-EXEC
Notice the syntax for structures in SQL. SEX OF DCLEMPLOYEE is COBOL syntax. Inside a SQL statement, SEX OF DCLEMPLOYEE becomes DCLEMPLOYEE-SEX. DCLEMPLOYEE is the structure name (probably an 01 level).
DCL followed by the table name is a common naming convention for this structure or I/O area.
How to generate Host Variables in COBOL program:
Database software often includes utilities that generate these structures for different languages (for example, DCLGEN is the DB2 utility).
Static and Dynamic SQL:
EXEC SQL DECLARE statement, because it is completely commented out by the SQL preprocessor, which executes before the COBOL compiler.
In DB2, the SQL preprocessor stores the EXEC SQL DECLARE information in a file called a database request module (DBRM).
This is called static SQL. In mainframe CICS/DB2 applications, almost all SQL is static SQL.
Later, before executing the program, you use a BIND utility to turn the DBRM(s) into a plan. The plan is a deliverable, like the CICS BMS map and COBOL load module, and programs require it in order to execute. A partial plan is called a packet. You can combine packets to build a plan.
Creating a DBRM with a SQL preprocessor and building a plan is required only by DB2. Since access paths to the data are chosen before the program executes, program performance is greatly enhanced. Other databases process the SQL entirely at runtime, although they are likely to be less efficient than DB2.
In dynamic SQL, the SQL statement is unknown until execution time. Dynamic SQL is less efficient but more flexible than static SQL. If your program needs to accept a SQL statement typed by an end user, execute it, and display the results, you must use dynamic SQL.
Related
Srinimf 