1 / 18

BCIS 4620 INTRODUCTION TO DATABASE PROCESSING Class 4 NX COBOL/SQL Supplement

BCIS 4620 INTRODUCTION TO DATABASE PROCESSING Class 4 NX COBOL/SQL Supplement. Fall 2008 Dr. Jack D. Becker. NX COBOL with SQL Homework #3 (20 points). Running NX COBOL with SQL SQLCOBOL add to new project folder (.app) Homework 3.A to 3.B (10 points) Basic Install and Test

haru
Download Presentation

BCIS 4620 INTRODUCTION TO DATABASE PROCESSING Class 4 NX COBOL/SQL Supplement

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BCIS 4620INTRODUCTION TO DATABASE PROCESSINGClass 4 NX COBOL/SQL Supplement Fall 2008 Dr. Jack D. Becker

  2. NX COBOL with SQLHomework #3 (20 points) • Running NX COBOL with SQL SQLCOBOL add to new project folder (.app) • Homework 3.A to 3.B (10 points) • Basic Install and Test • Control Break report • FILE.INFILE (copy to SQLCOBOL project folder); • FILE.OUTFILE will be produced • Install copies of DEPT and DEPARTMENT • [Use unique index names] DEPTLOAD.SQL, DEPARTMENT.SQL • Install copies of EMP and EMPLOYEE • EMPLOAD.SQL, EMPLLOAD.SQL • Adjust Location.SQLID to correct locations in SQL/Database • CREATE VIEW VPHONE • VPHONE.SQL • Homework 3.C (10 points) • SELECT05.SQL to SELECT24.SQL demos (Some Tables may not exist) • EXPORT DEPT & EMP to SQL files

  3. Homework #3 A. & B.NX COBOL with SQL • COBOL Source Programs • SQLCOBOL.CBL • FILE.INFILE • SQL/DB2 Data: EMP, DEPT [EMPLOYEE, DEPARTMENT backups] • PRE-Compile [Rebuild] • PGMPREP SQLCOBOL CBL • REFRESH.SQL • COBOL Compile [Animator; Run] • COBOL2.SQLCOBOL • Execution • FILE.OUTFILE [FILE OUTFILE1 vs. FILE OUTFILE2?] • MYSTERY? What happened to EMP &DEPT?

  4. COBOL w/Embedded SQL COBOL/CMS File Section. Select Assign … Working-Storage Section. Declare Section. SQL/DS Data Dictionary Empno Ename Lastname Firstname Deptno ... Procedure Division READ input-file record DECLARE vcur CURSOR SQL FETCH vcur-view-row SQL/DS Data CMS Data

  5. SQL Communications Area (SQLCA)

  6. Primary & Foreign Keys Note: 4 Tables: DEPT DEPARTMENT EMP EMPLOYEE 1 View: VPHONE

  7. Homework 3.C – 10 Points SELECTnn.SQL • SQL Wizard • SELECTnn.SQL (where nn is 05 through 24; 20 SQL examples) • Open/Import and RUN [or Run Batch] • Print SQL View • Print Table View • Note: In words write what are the SQL statements doing in each SQL View • If a program fails; explain the reason!

  8. EXPORT Tables to SQL files Backup Copy

  9. EXPORTed SQL file • NEWDEPT.SQL • Saved .EXP file • NEWDEPT.EXP

  10. FILE.INFILE & FILE.OUTFILE

  11. THE END Pause for a moment…

  12. Homework 3 -- COBOL with Embedded SQL • BONUS5 DOC B1 -- optional FILE OUTFILE1 has 118 records. When D11 is deleted from the DEPT table, all of the records in EMP that are in D11 should be deleted, if the ALTER3 program was correctly executed ALTER3 established referential integrity between the DEPTNO and WORKDEPT with cascading delete option in place. See below: 00044 0ACTION LNAME FNAME ENO PNO DNO 00045 D D11 00046 0 MSG10I - DELETE COMPLETE - RECORDS = 1 00047 __________________________________________________ 00048 0ACTION LNAME FNAME ENO PNO DNO 00049 I D11 00050 0 MSG09I - INSERT COMPLETE - RECORDS = 1 00051 __________________________________________________ 00052 0ACTION LNAME FNAME ENO PNO DNO 00053 F D11 These records should have been deleted….

  13. Homework 3 -- COBOL with Embedded SQL • BONUS5 DOC B1 -- optional continued These records should have been deleted…. 00054 0LASTNAME FIRSTNAME PHONE EMPNO DEPARTMENT NAME 00055 ADAMSON BRUCE 4510 000150 MANUFACTURING SYSTEMS 00056 BROWN DAVID 4501 000200 MANUFACTURING SYSTEMS 00057 JONES WILLIAM 0942 000210 MANUFACTURING SYSTEMS 00058 LUTZ JENNIFER 0672 000220 MANUFACTURING SYSTEMS 00059 PIANKA ELIZABETH 3782 000160 MANUFACTURING SYSTEMS 00060 SCOUTTEN MARILYN 1682 000180 MANUFACTURING SYSTEMS 00061 STERN IRVING 6423 000060 MANUFACTURING SYSTEMS 00062 WALKER JAMES 2986 000190 MANUFACTURING SYSTEMS 00063 YOSHIMURA MASATOSHI 2890 000170 MANUFACTURING SYSTEMS FILE OUTFILE2 is the BEST solution, if ALTER3 was correctly executed.

  14. DATALOAD Command • Homework #2 • CREATE TABLE JAnn.COMMISSION …; • COMM2 DBSINPUT B2 • DEC(5, 2) • DATALOAD TABLE (JAnn. COMMISSION) ...; • COMM2LOAD DBSINPUT B2 • ZONED (2) • Try: RUNCOB EXEC for fun!

  15. TableDesigner a.k.a., SALSA DEMO (Version 1.1)See Chapter 7 Class Notes • Files ==> Albums (.apm) • Objects (SOL); Items; Groups; Domains (F3) • Profile Manager Window; Model Designer Window • Properties (F6); or right click • ID (**; unique); Single Value; Group; SOL • Group Properties (Edit/Modify Object) • Arrange Objects • Generate SQL statements • Generate Database

  16. Figure 4-13College/Dept Example

  17. Summary • S-O Modeling Concepts • 6 Basic SO Types • S-O Diagramming Guidelines Explained • SALSA demonstrated • Normalization Concepts Reviewed • Homework 3 review • Homework 4 tips

More Related