This time around I am going to delve back into the DBINFO intrinsic a little bit. First I want to mention an awesome little tool in the Interex CSL called DBSAME. This little tidbit was written by Chuck Glenn back in 1990, and what it does is make extensive use of the DBINFO intrinsic to compare two databases. It will report on virtually every difference it finds, including item number and item names not matching between the two database. I have personally found this to be invaluable as a tool for rolling in database structural changes.
Chucks program is rather large, so I thought I would use a new one of mine which I call DBTPATHS. Let me give you the scenario that gave me the idea. How often have you tried to delete a Master record in an Image database, either through QUERY or your own program, only to be stymied by non-empty detail chains? Well this happened once to often for me, so I wrote this program to take a key item, and a value, and search all path's for existing entries.
This program is based on another DBINFO program that I used in this column a couple of years ago. The original program was meant to give structural information about a database. All those original features were left, but this new feature was added.
So let's take a look at figure 1, keep in mind that we have already gotten the base and data set and data item names from the user at this point. We first are going to issue a DBINFO mode 301 call to find all the data item paths that are linked to this dataset/item combination. These are returned to a table that we will then loop through after we get a key value from the user.
The call to DBINFO mode 205 returns all the data set information including any DDX information. This is an enhanced version of mode 202 which just returned capacity information basically. The main purpose here is to get the actual data set name for each data item number in the table.
Next we call DBINFO mode 102 to retrieve the data item information, specifically the data item number, for the item number returned in mode 301. This set's us up perfectly for the final call to DBFIND. By checking the chain length in the status array we are able to find out how many entries exist on each path for the key value specified.
Take a look at figure 2 to see a sample dialog. I find this tool to come in handy more and more often as time goes by, and I hope you enjoy making use of it.
I want to wrap up by telling you how hard this column was to write. I had the entire thing written, and then due to the wonder that is Microsoft Windows, I lost the whole thing because their incredibly stable so called O/S locked up before I had a chance to save it. So with no printouts I had to reproduce it again from scratch, which will probably explain why it's not that good now. Go Netscape.
Figure 1
WORKING-STORAGE SECTION.
01 D3.
03 D3-SET
PIC X(16) VALUE SPACES.
03
PIC X(02) VALUE SPACES.
03 D3-ENTRIES
PIC ZZ,ZZZ,ZZ9.
*
01 DATA-ITEM-TABLE.
03 DT-ITEM
PIC S9(4) COMP OCCURS 1024.
*
01 DS-INF.
03 DS-SET
PIC X(16) VALUE SPACES.
03 DS-TYPE
PIC X(02) VALUE SPACES.
03 DS-WORD-LENGTH
PIC S9(04) COMP VALUE 0.
03 DS-BLOCKING-FACTOR
PIC S9(04) COMP VALUE 0.
03
PIC S9(04) COMP VALUE 0.
03
PIC S9(04) COMP VALUE 0.
03 DS-ENTRY
PIC S9(09) COMP VALUE 0.
03 DS-CURR-CAP
PIC S9(09) COMP VALUE 0.
03 DS-HIGH-WATER
PIC S9(09) COMP VALUE 0.
03 DS-MAX-CAP
PIC S9(09) COMP VALUE 0.
03 DS-INIT-CAP
PIC S9(09) COMP VALUE 0.
03 DS-INCR-ENTRY
PIC S9(09) COMP VALUE 0.
03 DS-INCR-PCT
PIC S9(04) COMP VALUE 0.
03 DS-DDX
PIC S9(04) COMP VALUE 0.
*
01 DI-INFO.
03 DI-ITEM
PIC X(16) VALUE SPACES.
03 DI-TYPE
PIC X(02) VALUE SPACES.
03 DI-LEN
PIC S9(04) COMP VALUE 0.
03 DI-OCCURS
PIC S9(04) COMP VALUE 0.
03
PIC S9(04) COMP VALUE 0.
03
PIC S9(04) COMP VALUE 0.
*
01 WS-DB-DATA-ITEM
PIC X(16) VALUE SPACES.
01 WS-DB-ARGUMENT
PIC X(60) VALUE SPACES.
01 DS-NUMBER
PIC S9(04) COMP.
01 DS-ITEM
PIC S9(4) COMP VALUE 0.
01 DB-BASE-NAME.
03
PIC XX
VALUE SPACES.
03 BASE-NAME
PIC X(24) VALUE SPACES.
*
Procedure Division.
A1300-PROMPT.
MOVE 301
TO WS-DB-MODE.
INITIALIZE DATA-ITEM-TABLE.
CALL "DBINFO" USING DB-BASE-NAME,
WS-DB-DATA-SET,
WS-DB-MODE, DB-STATUS-AREA, DATA-ITEM-TABLE.
IF NOT DB-CALL-OK
CALL "DBEXPLAIN"
USING DB-STATUS-AREA
GO TO A1300-ITEM.
MOVE 1
TO S1.
MOVE DT-ITEM(S1)
TO EDIT-NUM.
DISPLAY SPACES.
DISPLAY 'There are ' EDIT-NUM
' paths for this dataset.'.
A1300-VALUE.
DISPLAY 'Enter a key value to
search for: ' NO ADVANCING.
ACCEPT WS-DB-ARGUMENT FREE.
IF WS-DB-ARGUMENT = SPACES
GO TO A1300-ITEM.
DISPLAY H3.
ADD 1 TO S1.
PERFORM UNTIL DT-ITEM(S1) =
0
MOVE 205
TO WS-DB-MODE
CALL 'DBINFO'
USING DB-BASE-NAME, DT-ITEM(S1),
WS-DB-MODE, DB-STATUS-AREA, DS-INF
MOVE DS-SET
TO D3-SET
MOVE 102
TO WS-DB-MODE
CALL "DBINFO"
USING DB-BASE-NAME, DT-ITEM(S1 + 1),
WS-DB-MODE, DB-STATUS-AREA, DI-INFO
MOVE 1
TO WS-DB-MODE
CALL 'DBFIND'
USING DB-BASE-NAME, DS-SET, WS-DB-MODE,
DB-STATUS-AREA, DI-ITEM,
WS-DB-ARGUMENT
MOVE DB-CHAIN-LENGTH
TO D3-ENTRIES
DISPLAY D3
ADD 3 TO S1
END-PERFORM.
DISPLAY SPACES.
MOVE 301
TO WS-DB-MODE.
INITIALIZE DATA-ITEM-TABLE.
CALL "DBINFO" USING DB-BASE-NAME,
WS-DB-DATA-SET,
WS-DB-MODE, DB-STATUS-AREA, DATA-ITEM-TABLE.
IF NOT DB-CALL-OK
CALL "DBEXPLAIN"
USING DB-STATUS-AREA
GO TO A1300-ITEM.
MOVE 1
TO S1.
GO TO A1300-VALUE.
A1300-EXIT. EXIT.
Figure 2
RUN DBTPATHS.PROG.SMGA
Begin run of DBTPATHS at 21:31:01
Copyright 1992 S.M.Gordon & Associates
Enter ?? for help at any prompt (almost)
Enter ? for lists on sets and items
Base name: MYBASE
Password:
Mode:
Dataset: COMPANY-M
Data Item: COMPANY-NO
There are 15 paths for this dataset.
Enter a key value to search for: 1234
Data Set Name Entries
COMP-LINK-D
4
NAME-PATH-D
8
DED-CODE-D
60
DEPT-DIST-D
1
EMPLOYEE-D
10,714
EMP-CHANGE-D
0
FED-TAX-D
1
GL-ACCT-D
71
GL-DIST-D
0
HISTORY-D
11,828
MAN-CHECKS-D
0
QTD-HIST-D
4,405
STATE-TAX-D
38
TAX-DEPOSIT-D
10
TIME-CARD
0