Transcript Start Verb and calling programs

Embedded SQL
SQL in Cobol
Working Storage
• Include SQLCA
– SQLSTATE (PIC XXXXX)
SQL Equivalents
Read a record?
Delete a record?
Add a record?
Change a record?
Retrieving more than one record
• Declare a cursor
– Contains a select statement that retrieves all of the
records
• Open the cursor
– Loads the data into the cursor
• Fetch
– Fetches a record from the cursor (default is next)
• Close the cursor
– Clears the data in the cursor
Journaling
Capturing Database (file) specific history
Journal
Controlling object used to record changes to database objects
Journal Entry
Record of a change to a database object
Journal Receiver
Holds the Journal Entry.
A Journal can have more than one journal receiver.
Journal
*FILE
*FILE
Journal
Receiver
Journal
Entries
Journal
Entries
Journal
Receiver
Journal
Entries
Journal
Entries
Creating a Collection with a Data
Dictionary
• Automatically creates a journal to log
changes to the database files created in
the collection
Setting up the Journal
Creating a Journal Receiver
CRTJRNRCV
• Fill in the Journal Receiver name, the
library where it will be stored
Creating a Journal
CRTJRN
• Fill in the name of the journal and the
library that it is stored in.
• Attach the journal receivers by listing them
in the Journal Receiver Fields
Start Journal Physical File
STRJRNPF
• Used to attach physical files to the Journal
Receivers.
• Record images parameter defines whether
the before/after image of the file will be
stored or just the after.
Using the Journal
Commitment Control
Commit
• Used to finalize the database change.
• Happens automatically when a program is
ended normally.
Rollback
• Uses the journal entries to take the
database back to the state that it was in
after the last commit.
Displaying a Journal
• DSPJRN
File access review
Indexed organization
Types of Access (so far)
• Sequential
• Random
• Dynamic
18
Sequential Access
• Reads one record at a time
• Reads records in keyed sequence or
arrival sequence
19
Sequentially Read using a
Random Defined Record
Make sure that ACCESS is DYNAMIC!!
READ file-name NEXT RECORD
AT END
Perform perform end-of-file-logic
END-READ.
20
Defining a Sequential Access
File
SELECT Employee-File
ASSIGN to DATABASE-EMPPF
ORGANIZATION is INDEXED
ACCESS MODE is SEQUENTIAL
RECORD KEY is
EXTERNALLY-DESCRIBED-KEY
File status is variable-name.
21
Sequential Read using a
Random Defined File?
22
Sequentially Read using a
Random Defined Record
Make sure that ACCESS is DYNAMIC!!
READ file-name NEXT RECORD
AT END
Perform perform end-of-file-logic
END-READ.
23
Positioning the File Pointer
Used with Sequential or dynamic access
defined files.
Initialize / populate the record key to
position the record pointer
START file-name
KEY Condition
INVALID KEY
Perform Invalid-Logic
NOT INVALID KEY
Perform valid-logic
END-START.
24
Random Access Techniques
25
Random Access
• Records are processed in some other
order than the one in which they were
physically written to the disk or indexed.
• A key field is looked up in an index, the
address is retrieved and the record is
accessed from the physical file using the
address.
26
Defining a Random Access
File
SELECT Employee-File
ASSIGN to DATABASE-EMPPF
ORGANIZATION is INDEXED
ACCESS MODE is RANDOM
RECORD KEY is
EXTERNALLY-DESCRIBED-KEY
(with duplicates)
File status is variable-name.
27
Randomly Retrieve a Record
using a Random Defined File
Update the Key Fields in the File.
READ file-name
INVALID KEY
Perform invalid-logic
NOT INVALID KEY
Perform valid-logic
END-READ.
28
Dynamic Access
• Defined when a file will be used both
randomly and sequentially
29
Defining a Dynamic Access
File
SELECT Employee-File
ASSIGN to DATABASE-EMPPF
ORGANIZATION is INDEXED
ACCESS MODE is DYNAMIC
RECORD KEY is
EXTERNALLY-DESCRIBED-KEY
(with duplicates)
file status is variable-name.
30
WRITE
• Writes records to a file
• File must be opened as I-O or OUTPUT
WRITE record-name (FROM variable)
INVALID KEY perform error-rtn
NOT INVALID KEY perform continue-rtn
END-WRITE.
31
REWRITE
• Updates a record in a file
• File must be opened as I-O instead of input
• Record must first be read before rewrite
REWRITE record-name (FROM variable-name)
INVALID KEY perform error-rtn
NOT INVALID KEY perform continue-rtn
END-REWRITE.
32
DELETE
• Deletes a record from a file
• File must be opened as I-O
• The record must have been read first
DELETE file-name RECORD
INVALID KEY perform error-rtn
NOT INVALID KEY perform continue-rtn
END-DELETE.
33
Normalization Theory
(Relational Database Design)
• First Normal Form:
– Information entities are divided into Files or Tables on
the basis of their Relationships.
• Second Normal Form:
– All Entities must have a logical dependency on the
Primary Key (or part of).
• Third Normal Form:
– All Entities must have a functional dependency on the
Primary Key in it’s entirety.
34
Due to Normalization Practices, we
usually have more than one file in
our databases.
A typical reports usually consists
of one or more Sequential files
and one or more Random Access
files.
35
File Status Codes
• Used by the system to communicate to the
program (programmer/operator) the nature
of a file I/O error
• 2 hexadecimal digits range 00-9F.
36
File Status Codes
File Status Meaning
Code
21
A sequence error exists for a sequentially accessed keyed
file. The keys are not in the correct order.
22
An attempt was made to write a record that would create a
duplicate primary record key in a keyed file.
23
An attempt was made to randomly access a record that
does not exist in the file. The required record was not
found during a READ.
24
A boundary error has occurred. An attempt has been made
to write beyond the pre-established boundaries of a keyed
file.
Page’s 483 - 486
37
Beware of File Status Code
95!
Need to add ‘WITH DUPLICATES’
to your select statement or your
need to take it out!
38
How to avoid File Status Errors
• Use Invalid Key clauses with Reads,
Writes, ReWrites and Deletes
• Use Declaratives
– Error Handling Routine
39
Steps to Coding Declaratives
• File Status Clause in the Select Statement
for the file.
• Declare the variable used in the File
Status Clause in the Working Storage
Section.
• Type the code for the Declaratives – ErrorHandling Section in the Procedure
Division.
– See page 487 of your text.
40
ARRAYS / (TABLES ?)
41
Loading Arrays
in Working storage using Redefines
01 Month-Names.
05 Month-String
PIC X(36)
VALUE ‘JanFebMarAprMayJunJul…
05 Month-Entries REDEFINES
Month-String
OCCURS 12 TIMES PIC X(3).
42
Loading Arrays - Redefines
MOVE ‘January’ TO Month-Entries (1).
MOVE ‘February’ TO Month-Entries (2).
MOVE ‘March’ TO Month-Entries (3).
MOVE ‘April’ TO Month-Entries (4).
MOVE ‘May’ TO Month-Entries (5).
MOVE ‘June’ TO Month-Entries (6).
MOVE ‘July’ TO Month-Entries (7).
43
Loading Arrays - 1
Read from a File
• Create a physical file (Month-File) with the fields:
– Month Number
– Month Name
• Create an array as follows
01 Month-Table.
05 Month-Entries occurs 12 times.
10 Month-Number PIC 9(2).
10 Month-Name
PIC X(15).
44
Loading Arrays - 2
Read from a File
PERFORM 120-Read-Table-Rtn
VARYING Sub FROM 1 by 1
UNTIL Sub > 12.
120-Read-Table-Rtn.
READ Month-File into Month-Entries
AT END
DISPLAY ‘Not enough table records’
END-READ.
45