|
|
|
To further illustrate the use of the CA-Datamacs/II commands, the following series of sample jobs will show initially populating several tables in a database, unloading those records, automatically reloading them, and modifying a record in one of the tables.
The database to be used is described in the CA-DATACOM/DB Application Programming Guide, and is reproduced here.
Purchase Order Header (POH) Table
Table DATACOM-ID is 001
Table STATUS/VERSION is P001
POH key names: POLI VENDR POHTV LATE
POH ID's: 001 002 003 004
POH Fields: PO LI VENDR TPVAL LATE
POH Values: 12345 000 ABC 100.00+ 22222 000 ABC 100.00+ X 22233 000 ABC 500.00+ 33333 000 PDQ 200.00+ 54321 000 XXX 000.00+ X
Note: ID is the master key, no duplicates. ID 004 is INCLUDE-NIL-KEY=NO
Purchase Order Line Item (POL) Table
Table DATACOM-ID = 002
Table STATUS/VERSION = P001
POL Key Names: POL1 PN
POL ID's: 001 005
POL Fields: PO LI PN QTY UCOST
POL Values: 12345 010 XYZ 10+ 000.03 12345 020 ABC 05+ 010.00 22222 010 ABC 10+ 010.00 33333 010 PXT 01+ 150.00
Note: ID 001 is the Master Key, no duplicates.
Part Cost (PNC) Table
Table DATACOM-ID = 003
Table STATUS/VERSION = P001
PNC Key Names: PNDAT
PNC ID's: 006
PNC Fields: PN EFFDT COST
PNC Values: ABC 840101 010.00
PXT 840101 150.00
XYZ 840101 000.02
XYZ 840311 000.03
XYZ 840401 000.04
Note: ID 006 is the Master Key, no duplicates.
Part Master (PNM) Table
Table DATACOM-ID = 004
Table STATUS/VERSION = P002
PNM Key Names: PN
PNM ID's: 005
PNM Fields: PN DESC
PNM Values: ABC Bolt, 10-32, 1 in.
PXT Bolt, 10-24, 1 in.
XYZ Washer, 1/16 x 1/4
Note: ID 005 is the Master Key, no duplicates.
The following example illustrates initially populating all tables in the CA-DATACOM/DB sample database.
*DM TBLSZE IS 10.
IDENTIFICATION DIVISION.
PROGRAM-ID. Datamacs.
AUTHOR. CA-DATACOM/DB INTERFACE.
REMARKS. INITIAL CREATE DESIGN
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
*DM GENERATE ALL RECORDS.
*DM PRINT ALL RECORDS.
*DM DATACOM CREATE DATABASE NAME IS HUMAN-RESOURCE
*DM CHECKPOINT EVERY number-of-recs RECORDS.
*DM START.
*DM PERFORM DEMO-DEM-POH 5 TIMES.
*DM PERFORM DEMO-DEM-POL 4 TIMES.
*DM PERFORM DEMO-DEM-PNC 5 TIMES.
*DM PERFORM DEMO-DEM-PNM 3 TIMES.
*DM GO TO FINISH.
*DM DEMO-DEM-POH.
*DM ADDIT DEMO-DEM-POH.
*DM DEMO-DEM-POL.
*DM ADDIT DEMO-DEM-POL.
*DM DEMO-DEM-PNC.
*DM ADDIT DEMO-DEM-PNC.
*DM DEMO-DEM-PNM.
*DM ADDIT DEMO-DEM-PNM.
DATA DIVISION.
WORKING-STORAGE SECTION.
*
*DM COPY TABLE NAME IS DEMO-DEM-POH
*DM ELEMENT element-list
*DM KEY NAME IS POLI.
M A P 01 DEMO-DEM-POH. 00100010
VER#* VERSION=P001 00100020
04 PO PIC 9(5). 00100060
*DM CONSTANT 12345 22222 22233 33333 54321. 00100061
04 LI PIC 9(3). 00100070
*DM CONSTANT 000. 00100071
04 VENDR PIC X(3). 00100080
*DM CONSTANT 'ABC' (3) 'PDQ' (1) 'XXX' (1). 00100081
04 TPVAL PIC S999V99. 00100090
*DM CONSTANT 100.00 100.00 500.00 200.00 000.00 . 00100091
04 LATE PIC X. 00100100
*DM CONSTANT ' ' 'X' ' ' ' ' 'X'. 00100101
*DM END-COPY.
*
*DM COPY TABLE NAME IS DEMO-DEM-POL
*DM ELEMENT element-list
*DM KEY NAME IS POLI.
M A P 01 DEMO-DEM-POL. 00200010
VER#* VERSION=P001 00200020
04 PO PIC 9(5). 00200060
*DM CONSTANT 12345 12345 22222 33333. 00200061
04 LI PIC 9(3). 00200070
*DM CONSTANT 010 020 010 (2). 00200071
04 PN PIC X(3). 00200080
*DM CONSTANT 'XYZ' 'ABC' (2) 'PXT' (1). 00200081
04 QTY PIC S99. 00200090
*DM CONSTANT 10 05 10 01. 00200091
04 UCOST PIC 999.99. 00200100
*DM CONSTANT 000.03 010.00 010.00 150.00 . 00200101
*DM END-COPY.
*
*DM COPY TABLE NAME IS DEMO-DEM-PNC
*DM ELEMENT element-list
*DM KEY NAME IS PNDAT.
M A P 01 DEMO-DEM-PNC. 00300010
VER#* VERSION=P001 00300020
04 PN PIC X(3). 00300060
*DM CONSTANT 'ABC' 'PXT' 'XYZ' (3). 00300061
04 EFFDT PIC 9(6). 00300070
*DM CONSTANT 840101 (3) 840311 840401. 00300071
04 COST PIC 999V99. 00300080
*DM CONSTANT 010.00 150.00 000.02 000.03 000.04. 00300081
*DM END-COPY.
*
*DM COPY TABLE NAME IS DEMO-DEM-PNM
*DM ELEMENT element-list
*DM KEY NAME IS PN.
M A P 01 DEMO-DEM-PNM. 00400010
VER#* VERSION=P002 00400020
04 PN PIC X(3). 00400060
*DM CONSTANT 'ABC' 'PXT' 'XYZ'. 00400061
04 DESC PIC X(25). 00400070
*DM CONSTANT 'Bolt, 10-32, 1 in.' 00400071
*DM 'Bolt, 10-24, 1 in.' 00400072
*DM 'Washer, 1/16 x 1/4'. 00400073
*DM END-COPY.
*
PROCEDURE DIVISION.
CREATE Programming Notes:
In this example, the field values were already defined. Because the number of records to be added is small, only constants were used for the field generators.
The following example illustrates unloading selected records from the Part Master table and the Purchase Order Line Item table. All records in the Purchase Order Header and Parts Cost tables will be unloaded. Assume that the keys which are used to select the PNM table are in the keys input file, DMIKEYS
1 * M SYNTAX
2 *DM TBLSZE IS 10.
3 IDENTIFICATION DIVISION.
4 PROGRAM-ID. Datamacs.
5 AUTHOR. CA-DATACOM/DB INTERFACE.
6 REMARKS. INITIAL UNLOAD DESIGN
7 ENVIRONMENT DIVISION.
8 INPUT-OUTPUT SECTION.
9 FILE-CONTROL.
10 *DM GENERATE ALL RECORDS.
11 *DM PRINT ALL RECORDS.
12 *DM DATACOM UNLOAD DATABASE NAME IS HUMAN-RESOURCE.
13 *DM START.
14 *DM
15 *DM UNLOAD-DEMO-DEM-POH.
16 *DM SCAN TABLE DEMO-DEM-POH
17 *DM AT-END GO TO UNLOAD-DEMO-DEM-PNM.
18 *DM WRITE DEMO-DEM-POH.
19 *DM GO TO UNLOAD-DEMO-DEM-POH.
20 *DM
21 *DM UNLOAD-DEMO-DEM-PNM.
22 *DM READ EQ DEMO-DEM-PNM
23 *DM KEY NAME IS PN USING DMIKEYS AS INPUT
24 *DM NOT-FOUND GO TO FINISH
25 *DM KEYS-AT-END GO TO UNLOAD-DEMO-DEM-PNC.
26 *DM WRITE DEMO-DEM-PNM.
27 *DM
28 *DM MOVE PN(DEMO-DEM-PNM) TO PN(DEMO-DEM-POL,PN).
29 *DM READ EQ DEMO-DEM-POL
30 *DM KEY NAME IS PN
31 *DM NOT-FOUND GO TO UNLOAD-DEMO-DEM-PNM.
32 *DM WRITE DEMO-DEM-POL.
33 *DM
34 *DM PARA1.
35 *DM READ NEXT-EQ DEMO-DEM-POL
36 *DM NOT-FOUND GO TO UNLOAD-DEMO-DEM-PNM.
37 *DM WRITE DEMO-DEM-POL.
38 *DM GO TO PARA1.
39 *DM
40 *DM
41 *DM UNLOAD-DEMO-DEM-PNC.
42 *DM
43 *DM SCAN TABLE DEMO-DEM-PNC AT-END GO TO FINISH.
44 *DM WRITE DEMO-DEM-PNC.
45 *DM GO TO UNLOAD-DEMO-DEM-PNC.
46 *DM
47 DATA DIVISION.
48 WORKING-STORAGE SECTION.
49 *
50 *DM COPY TABLE NAME IS DEMO-DEM-POH
51 *DM KEY NAME IS POLI
52 *DM SUPPRESS.
53 *DM END-COPY.
54 *
55 *DM COPY TABLE NAME IS DEMO-DEM-POL
56 *DM KEY NAME IS POLI
57 *DM SUPPRESS.
58 *DM END-COPY.
59 *
60 *DM COPY TABLE NAME IS DEMO-DEM-PNC
61 *DM KEY NAME IS PNDAT
62 *DM SUPPRESS.
63 *DM END-COPY.
64 *
65 *DM COPY TABLE NAME IS DEMO-DEM-PNM
66 *DM KEY NAME IS PN
67 *DM SUPPRESS.
68 *DM END-COPY.
69 *
70 PROCEDURE DIVISION.
UNLOAD Programming Notes:
This UNLOAD example uses the basic CA-Datamacs/II database access commands and closely resembles traditional programming logic.
The READ at line 22 uses keys read from an input file with a DD name of DMIKEYS to retrieve the required records. The KEYS-AT-END option will be activated after the last key record is read. In this case, it will go to unload the Parts Cost table.
To support the one-to-many relationship which exists between the PNM and POL tables, the READ commands at lines 29 and 35 will retrieve the many records. The MOVE command (line 28) moves the field PN from the recently retrieved DEMO-DEM-PNM row to the key value area of table DEMO-DEM-POL, which is used in the subsequent read. After the last record is retrieved, we loop back to read the next keys record from the input file.
The SCAN commands at lines 16 and 43 retrieve one record from the table for each execution of the SCAN command. The SCAN command reads the table sequentially by native key until all records have been read. After the last record is read, the AT-END option becomes active and a branch to the paragraph name specified will occur. The GO TO commands at lines 19 and 45 set up the loop to keep reading the table until the last record.
The following example is based on the previous UNLOAD function, except now we will be using the advanced options (EQUI-JOIN) of the database retrieval commands to unload the same database.
1 *DM SYNTAX
2 *DM TBLSZE IS 10.
3 IDENTIFICATION DIVISION.
4 PROGRAM-ID. Datamacs.
5 AUTHOR. CA-DATACOM/DB INTERFACE.
6 REMARKS. INITIAL UNLOAD DESIGN
7 ENVIRONMENT DIVISION.
8 INPUT-OUTPUT SECTION.
9 FILE-CONTROL.
10 *DM GENERATE ALL RECORDS.
11 *DM PRINT ALL RECORDS.
12 *DM DATACOM UNLOAD DATABASE NAME IS HUMAN-RESOURCE.
13 *DM START.
14 *DM
15 *DM UNLOAD-DEMO-DEM-POH.
16 *DM SCAN TABLE DEMO-DEM-POH
17 *DM AT-END GO TO UNLOAD-DEMO-DEM-PNM.
18 *DM WRITE DEMO-DEM-POH.
19 *DM GO TO UNLOAD-DEMO-DEM-POH.
20 *DM
21 *DM UNLOAD-DEMO-DEM-PNM.
22 *DM EQUI-JOIN DEMO-DEM-PNM,PN
23 *DM TO DEMO-DEM-POL,PN
24 *DM USING DMIKEYS AS INPUT
25 *DM NOT-FOUND GO TO FINISH
26 *DM KEYS-AT-END GO TO UNLOAD-DEMO-DEM-PNC.
27 *DM
28 *DM UNLOAD-DEMO-DEM-PNC.
29 *DM
30 *DM SCAN TABLE DEMO-DEM-PNC AT-END GO TO FINISH.
31 *DM WRITE DEMO-DEM-PNC.
32 *DM GO TO UNLOAD-DEMO-DEM-PNC.
33 *DM
34 DATA DIVISION.
35 WORKING-STORAGE SECTION.
36 *
37 *DM COPY TABLE NAME IS DEMO-DEM-POH
38 *DM KEY NAME IS POLI
39 *DM SUPPRESS.
40 *DM END-COPY.
41 *
42 *DM COPY TABLE NAME IS DEMO-DEM-POL
43 *DM KEY NAME IS POLI
44 *DM SUPPRESS.
45 *DM END-COPY.
46 *
47 *DM COPY TABLE NAME IS DEMO-DEM-PNC
48 *DM KEY NAME IS PNDAT
49 *DM SUPPRESS.
50 *DM END-COPY.
51 *
52 *DM COPY TABLE NAME IS DEMO-DEM-PNM
53 *DM KEY NAME IS PN
54 *DM SUPPRESS.
55 *DM END-COPY.
56 *
57 PROCEDURE DIVISION.
UNLOAD Programming Notes:
By using the EQUI-JOIN command, many programming steps can be saved when the same key must be retrieved from 2 or more tables.
Specific keys may be used to selectively unload the table (line 24).
Also, it is not necessary to code a separate WRITE command to output the record.
This example will automatically load the records previously unloaded.
*DM TBLSZE IS 10.
IDENTIFICATION DIVISION.
PROGRAM-ID. Datamacs.
AUTHOR. CA-DATACOM/DB INTERFACE.
REMARKS. LOAD DESIGN
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
*DM GENERATE ALL RECORDS.
*DM PRINT ALL RECORDS.
*DM DATACOM LOAD DATABASE NAME IS HUMAN-RESOURCE
*DM CHECKPOINT EVERY number-of-recs RECORDS.
*
DATA DIVISION.
WORKING-STORAGE SECTION.
*
*DM COPY TABLE NAME IS DEMO-DEM-POH
*DM ELEMENT element-name SUPPRESS.
*DM END-COPY.
*
*DM COPY TABLE NAME IS DEMO-DEM-POL
*DM ELEMENT element-list SUPPRESS.
*DM END-COPY.
*
*DM COPY TABLE NAME IS DEMO-DEM-PNC
*DM ELEMENT element-list SUPPRESS.
*DM END-COPY.
*
*DM COPY TABLE NAME IS DEMO-DEM-PNM
*DM ELEMENT element-list SUPPRESS.
*DM END-COPY.
*
PROCEDURE DIVISION.
The following example illustrates modifying the 12345000 record in the Purchase Order Header table (POH). The LATE code will be changed to X. Assume the key is unique.
*
*DM TBLSZE IS 10.
IDENTIFICATION DIVISION.
PROGRAM-ID. Datamacs.
AUTHOR. CA-DATACOM/DB INTERFACE.
REMARKS. INITIAL MODIFY DESIGN
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
*DM GENERATE ALL RECORDS.
*DM PRINT ALL RECORDS.
*DM DATACOM MODIFY DATABASE NAME IS HUMAN-RESOURCE.
*DM START.
*DM READ AND HOLD EQ DEMO-DEM-POH
*DM KEY NAME IS POLI USING KEY '12345000'
*DM NOT-FOUND GO TO FINISH.
*DM UPDATE DEMO-DEM-POH.
*DM GO TO FINISH.
DATA DIVISION.
WORKING-STORAGE SECTION.
*
*DM COPY TABLE NAME IS DEMO-DEM-POH
*DM ELEMENT element-list
*DM KEY NAME IS POLI.
M A P 01 PURCHASE-ORDER. 00100010
VER#* VERSION=P001 00100020
04 DATACOM-NAME-POH PIC X(4). 00100030
04 DATACOM-DATA-OFFSET-POH PIC 9(7) COMP-3. 00100040
04 DATACOM-DM-FILLER-POH PIC 9(4). 00100050
04 PO PIC 9(5). 00100060
04 LI PIC 9(3). 00100070
04 VENDR PIC X(3). 00100080
04 TPVAL PIC S999V99. 00100090
04 LATE PIC X. 00100100
*DM CONSTANT 'X'. 00100101
*
*DM END-COPY
PROCEDURE DIVISION.
If the LATE code in more than one record needs to be changed, then the keys of those records should be placed in a keys file and the MVS/VSE DD/DTF name should be coded on the READ command. For example:
READ AND HOLD EQ DEMO-DEM-POH KEY NAME IS POLI USING DMIKEYS AS INPUT NOT-FOUND GO TO START.
| Copyright © 2009 CA. All rights reserved. | Tell Technical Publications how we can improve this information |