WBM POSTS ARE MY LOST WORKS FOUND IN THE WAY BACK MACHINE

Using the SQLDS relatinal database in the MVS/CMS/COBOL environment: a cookbook for MIS students

A Brief Description of System Environment

1. MVS: The machine you will be using is an IBM 4381/13. Its primary operating system is MVS or Multiple Virtual Storage. Virtual storage is a feature of the IBM/370 architecture. Virtual storage allows each application or job to view and decode memory addresses independent of the computers physical addresses. The translation between the virtual address and the real address is performed by the Dynamic Address Translator which is completely transparent to the user. MVS is principally a batch job processor. Jobs are submitted to MVS using JES2 or Job Entry Subsystem #2. Users issue commands to JES2 using a specialized language called JCL or job control language.
2. VM/SP: VM/SP or Virtual Machine/System Product (VM for short) is a program written by IBM and used by our machine here at UofA to control interactive access of machine resources via on-line terminals. The name derives from the fact that VM builds an environment for each user that seems to be a complete mini computer with its own mini disks, memory, and communication channel via the virtual reader. When many VM users are on the system, the system is logically similar to a PC network where each user has his own machine but is also able to communicate with other users and able to link to a central file server. In the VM mode, it is convenient though not accurate to think of MVS as just another user to whom certain jobs may be sent via the network; the results of the job may be directed to any node in the network which includes the users virtual machine virtual reader and the various network printers. There are four components of VM. These are (1) CP or Control Program, (2) CMS or Conversational Monitor System, (3) RSCS, and (4) IPCS. We need concern ourselves only with CMS with an wareness of CP.
3. Control Program (CP):  CP manages the real (or as Kroenke likes to say, the physical physical) computing system while CMS manages each users virtual machine. CP is in direct control of all resources of the 4381 including the real storage (as opposed to virtual storage presented to batch applications by MVS), processor time, and all I/O devices including DASDs (the battery of 3330 and 3380 disk drives). Its job is to make these resources available to a number of online users at the same time by presenting the virtual machine to each user. While CMS is the operating system for each one of these virtual machines, CP is the environment that allows all of these virtual machines to co-exist in the same real computer.
4. CMS:  This is the most important component of the operating system for interactive users (us). CMS is a component of VM/SP that acts as the operating system of the virtual machine of each online user; and, together with CP, provides access to system resources to the interactive user and serves as a base on which to build interactive applications. From within the user friendly CMS environment, the interactive user may submit MVS jobs, build and manipulate minidisk files, link to virtual minidisks that contain compilers and development platforms such as COBOL, and SQL/DS; communicate through the network with other CMS users as well as with MVS; and even make direct links with OS files and VSAM files, and build an MVS environment. A useful utility for building interactive applications is DMS or the Display Management System.
5. The Display Management System or DMS:  The Display Management System allows your interactive COBOL programs to use predefined screens for interactive input-output. The predefined screens consist of text that is to be displayed as background plus fields that are to contain input and/or output data and are referred to as panels. Panels are created by using the panel editor called PANEL. Panels are loaded or read from the COBOL program by making calls to EUDCOBOL.
6. Creating Panels:  The panel editor PANEL, is not command compatible with the system editor and requires strict adherence to a set of rules. In other words, the program is difficult to use. An alternative is to use the program PANELIT that is included with this package. PANELIT converts the XEDIT text file into PANEL format. To use PANELIT, first create your screen form using XEDIT denoting data fields with underscores. Background display is entered as normal text. The only hard and fast rule is that the data field specifications should have one more underscore than is needed by the data to be displayed. PANELIT (and DMS) uses the first underscore to indicate the beginning of a field. Here is an example of a PANELIT input file created with xedit. The file might be named income stmt (i.e., the CMS filename is income and the CMS filetype is stmt). This panel is to be loaded by a COBOL program that will supply values for each field designated by underscores.

Income Statement ————————- Company _____________________________ Year _____ Sales _______ Cost of Goods Sold _______ Selling and Admin costs _______ Depreciation Expense _______ NOI (net operating income) _______ Interest Expense _______ Income Tax _______ NIAT (net income after taxes) _______ </plaintext> <p> The simplest way to invoke PANELIT is from the FLIST or FILEL display. This is a list of files in your directory. On the directory list, move the cursor to the file to be converted, in this case income stmt and enter the command panelit. PANELIT will process this file and produce a panel file according to DMS specifications. The output file will be called income panel. The filename is retained and the filetype of panel is assigned. This is required by DMS. All panel files must have a filetype of panel. The output file income panel will look like this: <p> <plaintext> ^Income@Statement ^————————- ^Company _@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ^Year _@@@@ ^Sales _@@@@@@ ^Cost@of@Goods@Sold _@@@@@@ ^Selling@and@Admin@costs _@@@@@@ ^Depreciation@Expense _@@@@@@ ^NOI@(net@operating@income) _@@@@@@ ^Interest@Expense _@@@@@@ ^Income@Tax _@@@@@@ ^NIAT@(net@income@after@taxes) _@@@@@@ </plaintext> <p> After creating the panel file, PANELIT will invoke PANEL and turn you over to it. This is necessary so that you may add field specifications if you want to; and so that PANEL will create the needed PCB file for the new panel. Most of the time, you will not use field specifications so simply enter PF5 to exit PANEL and ENTER to save the files. Your new panel income panel is now ready to display your COBOL variables. PANELIT requires two files. A REXX program called panelit exec and a companion file called panelit needsit. <p>COBOL Code Needed for DMS <p> The minimal cookbook COBOL code necessary to use DMS is as follows. Immediately prior to the procedure division, you must have the command copy eudcobol. This causes prewritten portions of DMS access code from the system file eudsmac maclib to be inserted into your source prior to compilation. The placement of the copy statement prior to the procedure division is shown below. Note that the copy command is in area B, or beyond column 12. <p> <plaintext> IDENTIFICATION DIVISION. ENVIRONMENT DIVISION. CONFIGURATION SECTION. INPUT-OUTPUT SECTION. FILE-CONTROL. DATA DIVISION. FILE SECTION. WORKING-STORAGE SECTION. * setup to use dms copy EUDCOBOL. PROCEDURE DIVISION. </plaintext> <p> Before you use the panel code, however, two of the DMS variables need to be initialized. These are, unload-list and load-list. The necessary initialize code is as follows: <p> <plaintext> InitializeDMS. Move “=” to UNLOAD-LIST. Move “Y” to LOAD-LIST. </plaintext> <p> When using a large number of panels, it may be necessary to release your panels since no more than 15 panels may be defined at any given time. The release panel code is shown below and may be used verbatim. <p> <plaintext> ReleasePanels. Move spaces to PANEL-NAME. Move “R” to Display-Code. Move 0 to NUMBER-DATA-FIELDS. CALL “EUDCOBOL” USING EUDCNTRL. Move “D” to Display-Code. </plaintext> <p> The variables load-list, unload-list,panel-name, number-data-fields,and display-code are declared by the macros from eudsmac maclib. <p> Panel Loader Code <p> Panels may now be read (unloaded) or written to (loaded). The code needed to load a panel requires specification of the number of data fields to be loaded, the name of the panel file to use, and a list of the variables in panel sequence. A call is made to the DMS subroutine eudcobol to perform the actual data display. The following code loads the ten variables listed into the panel called income. It can be used as a cookbook by changing only the the number of data fields from 10 to the number being loaded, the panel name from income to the the name of the panel file being used, and the variable list to the list of variables being displayed. The number in this list must be the same as the number of data fields declared. Note that the call sentence ends with a period only after the last variable has been declared. <p> <plaintext> * load the panel called income with 10 data items Move 10 to number-data-fields. Move INCOME to panel-name. Call eudcobol using eudcntrl CompanyName Year Sales Cgs SellAdmin Depreciation Noi Interest Tax Niat. </plaintext> <p>Panel Unload Code <p> Panels may be used for data entry using CRT terminals in exactly the same way as they are displayed. For example, the code below uses a panel called mainmenu and displays the value of UserChoice that is currently in memory. If the user enters a new value, the old value is updated. If the user simply depresses enter then the displayed value is accepted. This is a common way to show and use default values to minimize keystrokes. <p> <plaintext> MainMenu. Move HELP to UserChoice. * if no entry, then show help screens with commands Move “MAINMENU” to panel-name. Move 1 to number-data-fields. Call “eudcobol” using eudcntrl UserChoice. </plaintext> <p> Structured Query Language / Data System or SQL/DS <p> SQL/DS or Structured Query Language Data System is one of two relational database products from IBM (the other is DB2). It supports the standard SQL query language. The SQL commands may be issued interactively or embedded in application programs written in COBOL (also Pl/I, C, FORTRAN, and Assembler). <p> In a relational database the data are presented to the user in a set of relations or tables. The SQL query language is designed to produce new tables from these primary relations by extracting specific columns and rows from one or more relations. These operations are usually categorized as selections, projections, and joins. <p> Selection, Projection, Join <p> A selection is any SQL operation that extracts specific rows of a table. The SQL keyword WHERE is used to effect the row selection process using a command syntax such as: <p> <plaintext> SELECT * FROM Managers WHERE Years &lt; 15 </plaintext> <p> The asterisk (*) indicates that all columns of the table called managers are to be selected into the new display table. However, the WHERE clause restricts the rows selected to only those where the value of the column years is less than 15; in this case, a list of managers who have been with the company for less than 15 years. <p> The project operation of SQL restricts the display table to specified columns of the source table(s). For example, if only the columns designated by Years, Location, and Division are required, then the asterisk of the above query is replaced by a list specifying the columns to be moved to the display table. <p> <plaintext> SELECT Years, Location, Division FROM Managers </plaintext> <p> This is an example of a pure projection process. All rows are returned but the display table is restricted only to the specified columns. However, most SQL queries are a combination of selection and projection. For example, we can combine the commands above to produce a display table containing only the columns Years, Location, and Division and only those rows having a Years value of less than 15 years would be; <p> <plaintext> SELECT Years, Location, Division FROM Managers WHERE Years &lt; 15 </plaintext> <p>

The real power of the relational model is realized in the join operation. In this case, projections and selections from two or more related tables can be consolidated into one display table. The row-to-row correspondence between tables is maintained by virtue of foreign keys; that is, the key of one table is retained as a column in the other. <p> For example, say that in addition to Managers, we have a table called Engineers such that a manager can have many engineers but an engineer can only belong to one manager, then we might indicate the belonging by including the Manager Number as in the Engineers relation. The SQL command to produce a display table of the names of the managers and engineers would be as follows: <p> <plaintext> SELECT m.Name,e.Name FROM Managers m, Engineers e WHERE e.MgrNumber=m.MgrNumber </plaintext> <p> The two tables Managers and Engineers are thus joined to produce one table that contains columns from both. Rows are matched according to the common value of MgrNumber. <p> Views <p>

Recall that SQL operations simply produce a new table from one or more existing tables. In the SELECT commands we have used so far the new table produced is a temporary display table that evaporates upon completion of the display or print operation. In many instances it is convenient, efficient, or necessary for security purposes to to save these tables for future use. This is done with the create view … as command. For example, to save the display table produced by the join operation above, we could write; <p> <plaintext> CREATE VIEW NamesList AS (SELECT m.Name,e.Name FROM Managers m, Engineers e WHERE e.MgrNumber=m.MgrNumber) </plaintext> <p> This would save the display table as new virtual table called NameList which can then be treated as a table in constructing subsequent queries or in granting access privileges. <p> Interactive Access to SQL/DS <p> The SQL/DS system provides a program called ISQL (the I is for Interactive) to allow direct ad-hoc queries to be made to SQL from CRT terminals. Once in ISQL, the user simply types in the SELECT commands and ISQL responds immediately by displaying the resultant tables on the screen. It is especially useful to programmers who wish to test their SQL codes before embedding them into application programs. <p> However, the procedure for saving, editing, and re-invoking SQL commands is cumbersome and difficult to use. Yet, this sort of debug cycles must be performed several times while pretesting SQL code. <p> There are also problems relative to printing the results from each query. First, each query produces a different print file. It would be advantageous to collect the results of all queries from one session and produce one organized print-file. <p> Further, ISQL forces the user to view the display tables prior to printing. This can be very time consuming when a number of different display tables are to be printed. <p> All of these problems with ISQL can be avoided by using the XQL EXEC file provided with this package. XQL, along with its companion program RUNSQL EXEC, sets up an ISQL environment that alleviates all of the aforementioned difficulties in interactive usage. <p> The XQL access to ISQL offers the following features: <ul> <li> A CMS file can be used as a batch command file. This command file can be executed with PF11 and edited with PF10 while in ISQL. (and with XEDIT while in CMS). <li> The batch file processing allows display tables to be printed without being viewed. <li> All display tables sent to the printer are collected into one print file and sent to the users virtual reader. The user can then view them or re-route them to a selected network printer. (using the LOOK utility described below). <li> PF5 and PF6 are defined to access two frequently used ISQL utilities. PF5 produces a listing of all tables owned by the user. PF6 produces a complete data dictionary; listing all column names in all tables and their attributes. </ul> <p> Using XQL <p> To use XQL from CMS, you must have the programs XQL EXEC and RUNSQL EXEC in one of your CMS minidisks. To begin execution simply type in <plaintext> XQL <enter>. </plaintext> <p> The SETUP SQL command normally used prior to the ISQL procedure is not required since XQL will perform the setup for you. <p> The SQL commands file <p> The second step in using XQL is to enter the name of the CMS file that contains (or will contain) the SQL commands you wish to execute. If you dont have a file yet, then it is the file that will be created when you use PF10 to edit the command file. The default name is SQL COMMANDS. This name may be retained by making a null entry. <p> A typical command file looks like this: <p> <plaintext> /* List names and ages of managers who could retire*/ select name,age- from managers- where age&gt;55 display end /* print out names of managers in plastics division */ select mgrnumber,name,location- from managers- where division=plastics print end stop /* thats all folks */ </plaintext> <p> The features of the command file demonstrated above are: <ul> <li> Comments are entire lines that begin with /* and end with */. XQL is not smart enough to deal with comments that follow a command such as <li>select name,haircolor- /* get the name and hair color */ <li>As in interactive ISQL, each line must end with a hyphen (-) except the last. <li>All query commands must be followed by two lines containing instructions about the disposition of the resultant table and the word end. The disposition is either display to see the result on the screen or print to add the result to the print file without a screen display. <li>Blank or null lines may be used as needed for clarity. <li> The word stop may be used anywhere to end execution of the file. This makes it possible to debug portions of a large file without having to run through the entire list. <p> Normal usage of XQL <p> In the initial phases of SQL code development, it is usual for the programmer to loop through many cycles of PF10 and PF11 editing and re-executing the SQL commands until there are no sql errors and the desired results are obtained. The command files are used not only for SELECT commands but also to create tables, load data into tables, update tables, and generally perform all necessary operations normally required in database management, maintenance, and usage. For example, the following file creates a zipcode file and loads some new data into it. <p> <plaintext> /* first drop the table in case it exists */ drop table zipcode /* now create the zipcode table */ create table zipcode – (zip integer,- city char(20),- state char(2)) /* enter some data into this table */ input zipcode 72701,fayetteville,ar 72702,fayetteville, ar 72703,springdale,ar 73533,duncan,ok 74102,tulsa,ok 77002,houston,tx 74603,ponca city,ok 72902,fort smith,ar 72716,bentonville,ar 74004,bartlesville,ar 76067,dallas,tx end /* index the table on zip */ create index zipindex on zipcode (zip) </plaintext> <p> It is a prudent practice to save these files so that the tables can be re-built. The command files can also be used to automate the production of routine reports. Also simple application-specific user views can be programmed as with the ROUTINE facility of ISQL. XQL is considerably more flexible and easier to use than ROUTINE. <p> Embedding SQL code in COBOL programs <p> In addition to direct and interactive access, SQL provides for direct access to SQL data from application programs via a series of complex BIND instructions to set up variable address pointers. However, these bind instructions are so complex and unforgiving, that IBM has provided a preprocessor that takes normal SQL code as input and generates the necessary code for access to SQL data from Cobol. Although the preprocessor makes it a lot easier to use sql, there are a few rules that the COBOL programmer needs to follow for trouble free compilation and execution. The minimum SQL code needed is presented below in cookbook fashion. For additional information consult the SQL programmers reference manual. <p> Preparing source code for the preprocessor <p> Rule no. 1: The source program containing SQL code must have file type of COBSQL. The preprocessor will write the COBOL file which will contain all your non-sql code plus all your sql code translated into bind instructions. This COBOL file can then be compiled using the COBOL2 compiler. <p> Rule no. 2: The cobsql file must have a WORKING STORAGE section and must have a host variable declaration section. A good practice is to always put the host variable declarations in the working storage section. The host variables will receive values transferred from SQL to COBOL or contain values destined for SQL tables. <p> Rule number 3: All SQL instructions to the preprocessor begin with EXEC and end with END-EXEC. These delimiters signal the preprocessor to translate the SQL instructions within for the COBOL2 compiler. <p> Rule number 4: Do not use hyphens in host variable names. Theoretially, the preprocessor will translate them to underscores since otherwise SQL will interpret hyphens as continuation indicators. But it is easy to avoid hyphens. <p> Rule number 5: Declare all host variables as COBOL data level 77 . Multi-level data declarations may not be used except in the case of variable length strings, when it must be used. Variable length strings are declared with two 49 level declarations within a 01 level group. The first of the 49ers must have a pic of s9(4). It is used to hold the length of the string. The second 49er must be a pic x(n) where n is the maximum length of the string. The OCCURS, SIGN, JUSTIFIED, and BLANK WHEN ZERO clauses are absolutely forbotten. The following example may be used to cookbook the host variable declaration section. <p> <plaintext> WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 77 FirstHostVar comp-1. 77 AnotherOne pic x(20). 77 LastHostVar pic s9(9). 01 VariableLength. 49 Length pic s9(4). 49 TheString pic x(80). EXEC SQL END DECLARE SECTION END-EXEC. EXEC SQL INCLUDE SQLCA END-EXEC. </plaintext> <p> Rule Number 6: The begin declare and end declare statements must contain within them all the host variable declarations. The include sqlca must immediately follow the end declare. It sets up the SQL communication area. <p> Variable conversion table <p> Rule Number 7: The host variable type to be declared depends on the SQL data type to be converted. The following correspondence between sql and host variable data types must be maintained; thats a MUST with a capital M. <p> <plaintext> Type of Data Data Declaration SQL COBOL 31-bit integer integer pic s9(9) comp. 15-bit integer smallint pic s9(4) comp. character string of fixed length n char(n) pic x(n). single precision floating point real comp-1. double precision floating point real comp-2. decimal number p.s that is p digits wide and has s digits to the right of the decimal. (n=p+s) decimal(p,s) pic s9(n) </plaintext> <p> Embedded SQL Queries <p> Rule Number 8. Embedded SQL queries differ from interactive SQL queries in two respects. First, every query must begin with the delimiter EXEC SQL and end with END-EXEC. Second, unlike interactive modes, these queries pass values between SQL variables and COBOL variables (the declared host variables). Therefore, not only the SQL column names but the corresponding COBOL variables must be listed. <p> Passing One Row From SQL to COBOL <p> When it is certain that data from only one row will be passed as a result of the query, then a direct transfer can be made by using the SELECT…INTO syntax as in this example. The end of the COBOL sentence occurs after the end-exec clause and is denoted with the customary period (.). <p> <plaintext> Exec SQL SELECT Division, Location, Years INTO :Division, :Location, :Years FROM Managers WHERE MgrNumber=:MgrNumber end-exec. </plaintext> <p> Rule Number 9. Host variable names must be preceded by a colon (:) when used in SQL code. In this example, the host variable names, Division, Location, Year, and MgrNumber are the same as the SQL column names. Other names could have been used but it is best to follow the same name convention to keep the data correspondence clear. If you do use other names remember that; Rule Number 10. Host variable names are limited to eighteen characters. <p> <p>All variables that receive values from SQL (INTO…) or transfer values to SQL (VALUES…) must be declared in the host variable portion of the working storage section. <p> Both COBOL2 and SQL/DS will accept mixed case variable names such as LineItemNumber. As such, COBOL programmers may now begin to wean themselves from hyphenated names and use case changes to delineate words within variable names. Dropping hyphens makes it is easier to stay within the 18 character limitation and it skirts the hyphen issue of SQL/DS. <p> Passing Multiple Rows from SQL to COBOL <p> The method just described would fail if the query did not return exactly one row. To allow for the possibility of more than one row or no rows being returned in response to an SQL query, the CURSOR method should be used. Many programmers only use the CURSOR method since it is completely general and works under all circumstances. <p> The CURSOR method defines a memory stack area where all data from SQL are stored. After the transfer is made, the data may be removed from the stack one row at a time until it returns an SqlCode=100 which signifies that the stack is empty. If the stack is empty to start with it means that no data were returned by SQL. The cookbook code for the CURSOR shown below is the COBOL procedure called OldManagers which extracts selected rows from SQL into the stack and uses the procedure PopData to transfer the values from the stack into host variables. <p> <plaintext> OldManagers. * define a new data stack called C1 * and stash all the data into it. exec SQL declare C1 cursor for SELECT Name, Location, Years FROM Managers WHERE Age &gt; 65 end-exec. * now lets take a look and see what weve got exec SQL open C1 end-exec. * if anything in it, then pop em out one at a time * until the stack is empty. Perform PopData until SqlCode=100. * tell them we dont need this stack anymore. exec SQL close C1 end-exec. PopData. * try to pop out the next row of SQL data * into host variables exec SQL fetch C1 into :Name, :Location, :Years end-exec. * if sqlcode=100, there was nothing to pop If SqlCode not equal 100 then * we have some data. so move em into * display variables and output. perform DisplayData. </plaintext> <p> The procedure OldManagers opens an SQL stack area, names it C1 and places into it the Name, Location, and Years columns of all rows returned by SQL in response to the query. <p> Rule Number 11. Every time a cursor is declared it must be assigned a unique name. These names cannot be re-used. Like host variable names, these names can be 18 characters long. <p> Rule Number 12. Every Cursor operation must consist of the triad of Declare xx cursor, Open xx, and Close xx in that order. Note once again, that every command to the preprocessor is enclosed in the delimiters exec sql and end-exec. <p> After the data is transferred from SQL tables to the buffer (cursor), the cursor is opened. The rows of data are then removed from the data one at a time. Each time, the SQL variable SqlCode is checked. A value of 100 indicates that there is no more data. An initial value of 100 means that no rows met the selection criteria set. <p> The data are removed from the stack using the SQL fetch verb. The COBOL procedure PopData is used to perform the fetch operation. The values thus retrieved are then moved to regular COBOL display variables appropriately PICed for the reports being generated. <p> Neither the variable SqlCode nor the cursor names need be declared in COBOL picture clauses. The preprocessor and SQLCA will take care of these variables.

Updating SQL Tables Two types of updating is normally necessary. Either a new row of data is added to an existing table, or an existing data item (row and column) of a table is changed to a new value. Other table operations such as dropping tables, creating new tables, etc. are best done in interactive mode or using XQL batch files discussed earlier. <p> Before a table can be updated, the new values must be moved into COBOL variables that have been declared in the host variable declare section with a compatible variable type. The two types of updates are described using examples. <p> Adding a New Row <p> The SQL insert into …. values clause is used to add new rows to a table. For example, to add a new row of values to the Managers relation, we might use a COBOL procedure like the one shown below. <p> <plaintext> AppendManagers. * first move the values into the host variables. Move InMgrNumber to MgrNumber. Move InMgrName to Name. Move InMgrAge to Age. Move InMgrYears to Years. * now move them from the host variables into SQL table exec SQL INSERT INTO Managers * list of columns that will be updated (MgrNumber,Name,Age,Years) VALUES * COBOL host variables containing new values (:MgrNumber, :Name, :Age, :Years) end-exec. * all done </plaintext> <p> Normal COBOL continuation rules apply. For example, <plaintext> (MgrNumber, Name, Age, Years) </plaintext> is equivalent to <br> <plaintext> (MgrNumber, Name, Age, Years) </plaintext> <p> The Cobol sentence that begins with exec sql does not end until the end-exec phrase. The code above has been excessively commented to explain each movement of data. The essential append verbs are “INSERT INTO” and “VALUES”. <p> Changing Values in an Existing Row <p> The SQL update code is composed of the keywords “UPDATE…. SET….WHERE” as shown below. In the example the Salary of Engineer number 543 is being changed to $44,000 along with an change in position to Senior Engineer. Update identifies the table to be changed, Set lists the columns to be updated and the new values that the columns should take, and Where identifies the row(s) to be affected by the change order. <p> <plaintext> ChangeSalary. * move data from input variables into host variables move 44000 to Salary. move Senior to Position. move 543 to EngrNumber. * move data from host variable into table exec SQL UPDATE Engineers SET Salary = :Salary, Position = :Position WHERE EngrNumber = :EngrNumber end-exec. </plaintext> <p> All rows meeting the WHERE condition are updated. In this case, presumably, only one row will be changed. The set command allows arithmetic operations. For example, all engineers having a position of Senior could be given a 10 percent raise with the set command: <p><plaintext> SET Salary = Salary * :Raise </plaintext><p> where a value of 1.1 has been loaded into the Raise variable. In all the examples above, wherever host variables have been used to add new data or update existing data in tables the actual numbers or literals can be used directly in the SQL code as in: <p><plaintext> SET Salary = 44000, Position=Senior </plaintext><p> However, this is not considered good programming practice.. <p> Invoking the Preprocessor and COBOL Compiler <p> To set up the CMS environment for development of interactive SQL application programs in COBOL, the following setup commands must be issued at the beginning of the CMS session. <p> <plaintext> SETUP SQL SETUP COBOL2 GLOBAL </plaintext> <p> These commands define new logical CMS minidisks in the users environment to allow direct access to SQL/DS and the COBOL2 compiler. One of the programs in the SQL/DS minidisk is the ISQL interactive access system. Another is a program called SQLPREP,the preprocessor that converts COBSQL programs with embedded SQL code into code that can be compiled with the COBOL2 compiler. SQLPREP will remove all the SQL code (with an asterisk (*) in column 7) and replace them with the necessary bind instructions for accessing the data in the SQL/DS database. All of these instructions will be tested against the actual data and not only syntax errors, but data errors such as no such table, or no such column will be flagged. <p> To convert a COBOL program called mycobol cobsql to a compilable Cobol version, the sqlprep program is invoked as follows: <p><plaintext> SQLPREP COB PP(PREP=mycobol,COB2,QUOTE) SYSIN(mycobol) </plaintext><p> The COB and COB2 parameters direct the preprocessor to prepare a program for the COBOL2 compiler. The QUOTE option, normally used, indicates that the single quote () is used in the program to delimit text strings. (COBOL2 expect double quotes otherwise.) <p> SQLPREP will create two files on the CMS minidisk A. These are mycobol COBOL, and mycobol LISTPREP. Depending on program and minidisk size, these files may require more disk space than a normal 1 cylinder CMS minidisk can supply. <p> If errors are encountered, SQLPREP will issue a message to the terminal and insert error messages into the COBOL file. Once an error-free COBOL file is successfully produced it can be compiled with the COBOL2 compiler. In this example, the name of the COBOL file will be mycobol Cobol. The commands necessary to compile the program are: <p><plaintext> GLOBAL MACLIB EUDSMAC COBOL2 mycobol (LIB </plaintext><p> It is being assumed that the program mycobol uses DMS panels. The CMS global command is used to specify that the macro library called eudsmac (i.e., a CMS file called eudsmac maclib) is to be searched for missing macros and copy files during compilation. This is necessary because we need some code from this file to be included prior to compilation in order to access DMS panels. <p> The compiler will read the COBOL file and produce a TEXT file which contains the object code. In this case, the object file will be called mycobol text. If storage space on the minidisk is scarce, the LISTPREP file produced by SQLPREP can be deleted (erase * listprep *) to make room. <p> If there are compilation errors, these errors will be listed on the terminal as well as on the LISTING file. For the sake of source code integrity, it is best to make all corrections to the COBSQL file and re-SQLPREP. That way, the COBSQL file will contain all changes. After successful compilation, the LISTING and COBOL files can be deleted. The TEXT file can now be link-loaded to produce a MODULE or executable file. The link procedure requires these CMS commands in the sequence shown. <br><plaintext> GLOBAL TXTLIB ARIRVSTC ARIPADR VSC2LTXT EUDSTXT LOAD mycobol </plaintext><br> The CMS command GLOBAL is used to assign four TXTLIB files to the library of code to to be link-loaded along with the TEXT file mycobol. In addition to the standard COBOL2 library vsc2ltxt, we are including the SQL code libraries called arirvstc and aripadr, and the DMS text library called eudstxt. These files contain compiled subroutines mycobol needs in order to access SQL tables and DMS panels. <p> A successful link will produce the object code and place it in the file mycobol text. To convert the object code to an executable module (by adding entry point addresses from CMS) the GENMOD command is used as; <br><plaintext> GENMOD mycobol </plaintext><br> This will finally produce our executable program called mycobol module. The program is executed with the name of the program; in our case, the new CMS command mycobol which we created will begin execution. <p> Normally the executable, or MODULE, file is only produced after the development cycle is complete. The CMS command START is used to make test runs during development. <br><plaintext> LOAD mycobol (START or, if already loaded START mycobol </plaintext><br> If CMS files are to be used for input or output, then the appropriate filedef command must be issued prior to execution. The filedef command makes the logical link between cms files and COBOL files. For example the filedef command, <br><plaintext> FILEDEF mydata DISK mycrud data </plaintext><br> links a CMS minidisk file called mycrud data to a Cobol file SELECTed as mydata which may be ASSIGNed to its COBOL name of TRANSACTION-FILE. The corresponding COBOL assignments are: <br><plaintext> SELECT mydata ASSIGN TO transaction-file. </plaintext><p> The LOOK Utility <p> The XQL utility described often sends very large print files to the virtual reader. These cannot normally be RECEIVEd into CMS files due to disk size limitations. More problematic is that the readers PEEK facility allows the user to view only the first 200 lines of the reader file. What the adroit SQL programmer would like is the ability to view these print files in their entirety and, if appropriate, re-route them to a network printer. <p> The LOOK EXEC allows exactly these options. To use LOOK, first produce a normal PEEKable reader list with the CMS command RL. Then place the cursor next to the file you wish to look at and enter the command LOOK. The file will be displayed, as in peek, but without any line limitations. When done looking at the file, exit normally with PF3. At that point, LOOK will give you the option to reroute the file to a printer (PRINT command) or to simply get rid of it (PURGE) command. A null command will return the user to the RL reader list screen. <p> If a print command is issued, then a valid network printer must be identified with its name such as remote5. <p> The programs PANELIT EXEC, LOOK EXEC, and XQL EXEC are written by <a href=”/web/20010210040648/http://munshi.sonoma.edu/”>Jamal Munshi</a> and made freely available to anyone upon request.<p> <a href=”/web/20010210040648/http://munshi.sonoma.edu/working/working.html”>back to working papers index</a><p> <!– FILE ARCHIVED ON 04:06:48 Feb 10, 2001 AND RETRIEVED FROM THE INTERNET ARCHIVE ON 05:39:09 May 24, 2020. JAVASCRIPT APPENDED BY WAYBACK MACHINE, COPYRIGHT INTERNET ARCHIVE. ALL OTHER CONTENT MAY ALSO BE PROTECTED BY COPYRIGHT (17 U.S.C. SECTION 108(a)(3)). –> <!– playback timings (ms): RedisCDXSource: 0.56 CDXLines.iter: 13.207 (3) esindex: 0.011 PetaboxLoader3.resolve: 55.371 load_resource: 108.986 exclusion.robots: 0.257 captures_list: 81.03 PetaboxLoader3.datanode: 84.11 (4) LoadShardBlock: 63.006 (3) exclusion.robots.policy: 0.247 –>