DBUTIL is a general purpose utility for issuing DL/I calls dynamically, printing databases and database management. An interactive interface enables DL/I calls or special functions to be entered and executed dynamically. A command line interface is provided for automating repetitive functions. We recommend using the interactive interface until you become more familiar with DBUTIL.
DBUTIL executes as a BMP type program in IMS. Although DBUTIL uses interactive screens, they are not sent or retrieved using MFS and the I/O PCB. DBUTIL uses a PC-specific display which enables database position to be maintained across multiple interactive DL/I calls. If DBUTIL used MFS, database position would be lost whenever it issued a GU to retrieve the screen request.
You can start the DBUTIL utility from the Mainframe Express IDE:
The Start debugging dialog box appears.
| Command or transaction code |
Information |
|---|---|
| DBUTIL | If you specify this value you should already have defined a BMP transaction with DBUTIL as the program-name. See the chapter Developing IMS Applications in your User's Guide. |
| A batch program command | You can specify any batch program
command. For example: bmp,dbutil,psbname. Where psbname is the name of the PSB containing the PCB description of the database(s) you want to use. Since DBUTIL issues DL/I calls like any other application, the PCB must specify the appropriate processing options for the calls or DBUTIL special functions you want to perform. |
| Leave blank | If you leave this field blank an application region screen appears. To start DBUTIL enter DBUTIL (you should have already defined a BMP transaction with DBUTIL as the program-name) or specify a batch program command. |
The DBUTIL screen has two parts:
At the top of the screen is a field for entering the name of a file containing DBUTIL control statements.
The remainder of the screen is used to enter and execute DL/I calls or DBUTIL special functions dynamically. You can input either the name of a control file or a dynamic function but not both.
DBUTIL terminates if you press the Enter key without entering any data.
The table below describes the DBUTIL fields that are used for entering dynamic DL/I call parameters.
| DBUTIL Field |
Description |
|---|---|
| Function | The DL/I call (GHU, REPL, ISRT, etc) or DBUTIL special function |
| PCB ID | The relative PCB number or the database-name. The PCB number is always one larger than it appears in the PCB since DBUTIL runs as a BMP and has an I/O PCB. If you enter a database-name, the first PCB matching the name is used. |
| SSA 1-5 | A maximum of five SSAs can be entered on the screen. If more than five, or a longer SSA is needed, use the DBUTIL2 Animator interface for DBUTIL. |
| I/O area | The I/O area for the DL/I call |
We recommend using DBUTIL along with the IMS DL/I Call Trace facility, especially when first using DBUTIL. The trace output enables easier viewing of packed and binary data. It also confirms that you are making the correct entries on the screen.
The DBUTIL interactive screen can be difficult to use for entering packed fields, binary fields, long SSAs or complex I/O areas. This can be overcome by combining DBUTIL with a program named DBUTIL2 which is supplied as COBOL source code in the mfe\mfims\source folder. When DBUTIL2 is compiled, you can use the Debugger's 'Examine' commands to view and edit the DL/I call parameters entered on the DBUTIL screen or returned from IMS.
To use DBUTIL2, copy the dbutil2.cbl sample program into the folder where your application resides and compile it. Start the DBUTIL program and enter the parameters as if DBUTIL2 was not present.
When DBUTIL formats the database call, it passes DBUTIL2 the DL/I call parameters instead of calling CBLTDLI. DBUTIL2 is displayed in Debugger so that you can use Debugger's powerful features to change the SSAs and I/O area.
DBUTIL2 is distributed in source form to enable user modification. COPY statements can be added to the source to enable specific field definition of the DL/I call I/O area. This can be helpful when working with long segments and trying to determine where one field ends and the next one starts.
The DBUTIL print function provides a quick method to obtain a list of the contents of a database. It works along with the DL/I Call Trace facility. It sets specific call trace settings and issues a series of GN calls until it reaches the end of the database. To view or print the contents, format the trace file with the IMS Trace Print tool.
When you request a database print, DBUTIL temporarily enables tracing of the PCB, I/O area and Call Stats tracing options and disables screen tracing when the trace output filename is bts.lst.
The PRNT function prints the contents of a database. The function can be specified on the interactive screen or in a DBUTIL control file. When entered through control statements, you must issue a PCB function prior to the PRNT function to identify which database to print.
Interactive input of PRNT
To enter the PRNT function on the interactive screen, enter PRNT as the function and enter the PCB number or database-name of the database you want to print. For example:
Function
: PRNT PCB ID : 02
PRNT control statement
*Record columns *---+----1----+----2 PRNT
There are no options for the PRNT function
DBUTIL runs as a BMP type program in IMS. The command line interface for DBUTIL is the same as for other batch programs except that you pass it the name of the file containing DBUTIL control statements. When DBUTIL is started with a control filename, it bypasses the display of its screen and immediately begins processing the control statements. If you do not pass a filename containing control statements, DBUTIL displays its interactive screen.
An example command line for Windows is:
bmp,dbutil,psbname dbutil.crd
Since DBUTIL issues DL/I calls like any other application, the PCB must specify appropriate processing options for the calls or DBUTIL special functions you perform. dbutil.crd is the name of the file containing the control statements. The name can include a drive and/or folder.
The DBUTIL control statement file is intended primarily for automating repetitive tasks. The file contains a list of DBUTIL control statements. A control statement is a DL/I call or a DBUTIL special function.
The format of the file is an ASCII text file (line sequential file). The format of each record is the same. The maximum record length is 32768 characters. There are limitations on the type of ASCII characters which can be entered in this kind of file. In short, these files cannot contain packed or binary fields without special handling.
The record layout of the DBUTIL control statement is:
Columns 1 - 4
Positions one through four contain the DL/I call function or DBUTIL
special function. Any DL/I database call can be used, for example, GHU,
REPL or ISRT. The DBUTIL special functions are listed in the table below:
| Function |
Description |
|---|---|
| PCB= | Specifies the PCB for subsequent DL/I calls. Specify the relative PCB number or DBD-name |
| IO | DL/I call I/O area for ISRT, REPL and DLET calls |
| * | An asterisk in column 1 indicates a comment line |
| PRNT | First step in printing the contents of a database |
| ZERO | Zeroload a database |
| LOAD | Load a database |
| UNLO | Unload a database |
| MLOD | Load an MSDB |
| LDIX | Used for loading logical databases |
| USEQ | Unload sequential dependent segments from a DEDB |
| MSGS | Sets tracing options for batch control statement execution |
Column 5
A non-blank character in column 5 indicates the next line is a
continuation of this line. This line becomes one of a set of records which
is grouped to form one DL/I call. You cannot use this continuation to
continue an 'IO' function record.
Column 6 - 32768
The data associated with the function
*Record Columns
*---+----10---+----20---+----30---+----40
*Set the PCB number
PCB= 03
*
*Issue get unique call with one SSA
GU TESTSEG1
*
*Issue get unique call with three SSAs.
*(Note the continuation)
GU XTESTSEG1*-(SEGKEY01 =10)
XTESTSEG2*-(SEGKEY02 =20)
TESTSEG3*-(SEGKEY03 =2000)
*
*Issue insert call with SSAs
ISRTXTESTSEG1*--(SEQFIELD =AA)
XTESTSEG2*--(SEQFIELD =BBBB)
XTESTSEG3
IO NEWSEGMENTDATA
The IMSDBU utility is the recommended database management utility.
You can also perform the following database management functions using DBUTIL if you are using DBUTIL control cards to manage your databases:
DBUTIL requires special processing and careful planning for loading logical databases.
The ZERO function is not available from the interactive screen. It can be specified in a control statement file which is run from the interactive screen but you cannot enter ZERO in the Function field on the interactive entry portion of the screen.
The ZERO function initializes a database in the same way as when using the IMSDBU Zeroload function. It is available in DBUTIL to enable more flexible database manipulation for DBUTIL control file processing. For example, to Zeroload a database before performing a LOAD function.
The ZERO function does not require a 'PCB=' statement to indicate the database. The database-name is specified with the function. There are no 'directives' which can be used with the DBUTIL Zeroload.
ZERO control statement
*Record columns *---+----1----+----2 ZERO dbdname
The 'dbdname' specifies the name of the DBD to Zeroload.
The LOAD function can be entered in a DBUTIL control file or as an interactive call.
The LOAD function loads a database from segment data contained in an input file. The input file can be created by the UNLO function or from a program on your IMS/ESA system and downloaded to the PC. DBUTIL performs a load by issuing a series of ISRT calls.
The input file can be a variable or fixed length record file format. Each record in the file must contain the segment-name and segment data at the same location in each record. The segments within this file must be in hierarchical order but do not have to be in key sequence.
The fixed length record format is a record sequential file. The variable length record format is a VRECGEN file. Each record in the file must contain a binary length code in the first two bytes. Its value indicates the total length of the record including the two byte length code itself. See the chapter File Conversion Utilities in your Administrator's Guide for information on VRECGEN and the chapter Accessing Mainframe Files in your Administrator's Guide for information on downloading an IMS database from the mainframe.
The sample programs dbutilv.cbl and dbutilf.cbl can be used to unload IMS/ESA databases. These utilities produce database unload files which can be used directly by this LOAD function. The DBUTILV utility includes the record length code so VRECGEN is not required for its output files. See the sample program's in the Project's source folder for information on running these programs.
Databases should be Zeroloaded prior to loading with DBUTIL. When loading to a database which contains segments, duplicate keyed segments can be rejected with an II status code. Database position after an II status code is on the segment which caused the II code. The duplicate segment's children in the input file are inserted under it. Any unique child segment is effectively added to the existing database record. If the child segments are not uniquely keyed, they are all inserted under the segment which failed with an II code.
It should be noted that performance degradation can result if the DL/I call trace facility is active during database loading. If you are not familiar with using DBUTIL for loading databases, it is helpful to use the DL/I call trace facility to verify the load is proceeding correctly. The trace can also be used to obtain a list of the segments loaded. Once you become more familiar with DBUTIL, disabling the trace improves load performance and reduces disk space requirements.
When the LOAD function is entered through control statements, you must issue a PCB= function before the LOAD to indicate the database to load.
The PCB must specify a processing option which enables ISRT calls ('A', 'I' or 'L'). A processing option of 'L' is not required, however the use of a load mode processing option provides significant performance gains. Processing option 'L' should be used when loading databases which contain non-uniquely keyed or non-keyed segments. This forces the 'LAST' rule so these segments are inserted in the database in the sequence they are present in the input file. See the Processing option L topic in the online help for details; click Help Topics on the Mainframe Express Help menu, then on the Index tab select Processing option L.
LOAD control statement
*Record columns *---+----1----+----2 LOAD (V,lrecl),SEGM=n,DATA=n,filename
where:
LOAD is the function
(V,lrecl) indicates the input file format. It has one of three specifications:
Note:
A 'V' or an 'lrecl' value must be specified for databases defined as ACCESS=LOGICAL
SEGM=n indicates the position of the segment-name in the input record. The default is SEGM=1. For variable length input files, do not include the 2 byte length code in this value.
DATA=n indicates the position of the segment data in the input record. The default is DATA=9. For variable length input files, do not include the 2 byte length code in this value.
'Filename' specifies the name of the input file. It can include a drive and/or folder.
Control statement examples
LOAD V,DBDATA.LOD
Loads a database created by the DBUTILV program on IMS/ESA. The file was named dbdata.lod when it was transferred to the PC.
LOAD 300,DBDATA.LOD
Loads a database from a 300 byte fixed length record input file. The DBUTILF program creates fixed length output files from IMS/ESA databases. The file was named dbdata.lod when it was transferred to the PC.
LOAD V,SEGM=7,DATA=36,DBDATA.LOD
Loads a database from a variable length input file was which created by the IBM standard unload utility and processed by VRECGEN on your mainframe. The file was named dbdata.lod when it was transferred to the PC.
Interactive input of LOAD
Enter LOAD as the Function and enter the PCB number or database-name of the database you want to process. Enter the input filename in the field labelled SSA #1. If the filename is too long for SSA #1, it can be continued to the SSA #2 field. For example:
Function
: LOAD PCB ID : 02 SSA #1 : V,DBDATA.LOD
An example using SEGM and DATA keywords:
Function
: LOAD PCB ID : 02 SSA #1 : V,SEGM=7,DATA=36,DBDATA.LOD
The MLOD function can be entered in a DBUTIL control file or as an interactive call.
The MLOD function is used for loading Fast Path MSDB databases. It is required for loading all but terminal related dynamic MSDBs. A special function is required since the DL/I insert calls created by a LOAD function cannot be issued for other types of MSDBs. The special insert calls issued during MLOD processing appear in the DL/I call trace as insert calls.
The MLOD input file can be a variable length or fixed length record file. For a nonterminal-related MSDB without LTERM keys, the input record must contain the segment data beginning in position 1 of the input record. Other data can follow the segment data. For all other MSDB types, the input record must contain the LTERM-name in positions one through eight of the input record (space filled if needed) and the segment data beginning in position nine.
These formats differ from the input file for the LOAD function. For nonterminal-related MSDBs without LTERM keys, you must change the DBUTILV sample program, which is provided as COBOL source code, to have it omit the segment-name when formatting the output record. For all other types of MSDBs, you need to create a program to format the type of input file required by MLOD.
The fixed length format file is a record sequential file. The variable length record input file is a VRECGEN format. Each record must contain, as the first two bytes, a binary length code indicating the total length of the record including the two byte length code itself. If your mainframe unload program does not include the length code in the record you can use the Mainframe Express VRECGEN program to prefix each record with its length.
When the MLOD function is entered through control statements, you must issue a 'PCB=' function before the MLOD to indicate the database to load. The PCB does not require any special PCB processing options.
MLOD control statement
*Record columns *---+----1----+----2 MLOD (V,lrecl),filename
where:
MLOD is the function
(V,lrecl) indicates the input file format. It has one of three specifications:
'Filename' specifies the name of the input file. It can include a drive
and/or folder.
Control statement examples
MLOD V,DBLOAD.LOD
Loads an MSDB using a variable length data file downloaded as dbload.lod.
MLOD 300,DBLOAD.LOD
Loads an MSDB using a 300 byte fixed length record file downloaded as dbload.lod.
Interactive input of MLOD
Enter MLOD as the Function and enter the PCB number or database-name of the database you want to process. Enter the input filename in the field labelled SSA #1. If the filename is too long for SSA #1, it can be continued to the SSA #2 field. For example:
Function
: MLOD PCB ID : 02 SSA #1 : V,DBLOAD.LOD
The use of the LDIX function is only needed when loading recursive logical relationships. It operates in conjunction with the LOAD function. When an insert fails with an IX status code during LOAD processing, DBUTIL writes the segment which failed to a pending file. An IX status code indicates the logical parent does not exist when inserting a logical child.
The name of the pending file is dbutilix.tdo (the 'to do' file). It is created in the project's listing folder.
If the IX status codes are a result of loading a recursive database, the inserts for these segments need to be re-processed. The LDIX function reads the dbutilix.tdo data set, formats a DL/I call and reissues the insert call. The LDIX always processes the dbutilix.tdo data set from the last execution of LOAD which had segments failing with an IX status code. Thus, the LDIX function must immediately follow the LOAD which created the dbutilix.tdo file.
There are no options for the LDIX function except to indicate the PCB number for the affected database. The LDIX function can only operate on a dbutilix.tdo file created by the LOAD function. This file cannot be modified by the user.
LDIX control statement
*Record columns *---+----1----+----2 LDIX
There are no options for the LDIX function.
Interactive input of LDIX
Enter MLOD as the Function and enter the PCB number or database-name of the database you want to process. The PCB ID must be the same as the PCB ID used for the LOAD function which produced the dbutilix.tdo file. For example:
Function
: LDIX PCB ID : 02
The UNLO function unloads database segments to a sequential file. DBUTIL performs an unload by issuing a series of GN calls. The segment-name returned in the PCB and the segment data from the I/O area are written to the output file. The segment-name is placed at the beginning of the record with the segment data immediately following. The output file can be a fixed or variable length record file.
The UNLO function can be entered in a DBUTIL control file or as an interactive call. When entered through control statements, you must issue a 'PCB=' function before the UNLO to indicate the database to load. The PCB must specify a processing option which enables GN calls.
UNLO control statement
*Record columns *---+----1----+----2 UNLO (V,lrecl),filename
where:
UNLO is the function
(V,lrecl) indicates the output file format. It has one of three specifications:
Note:
A 'V' or an 'lrecl' value must be specified for databases defined as ACCESS=LOGICAL
'Filename' specifies the output filename. It can include a drive and/or folder. A special value of 'DUMMY' supresses the creation of the output file. Specifying DUMMY enables you to set specific DL/I call trace options for viewing or printing a database instead of using the DBUTIL PRNT function.
Control statement examples
UNLO V,DBDATA.UNL
Unloads a database into a variable length file named DBDATA.UNL.
UNLO 300,DBDATA.UNL
Unloads a database into a 300 byte fixed length record file named DBDATA.UNL.
Interactive input of UNLO
Enter UNLO as the Function and enter the PCB number or database-name of the database you want to process. Enter the output filename in the field labelled SSA #1. If the filename is too long for SSA #1, it can be continued to the SSA #2 field. For example:
Function
: UNLO PCB ID : 02 SSA #1 : V,DBDATA.UNL
The USEQ function unloads all sequential dependent segments (SDEPs) from a Fast Path DEDB. The output is a sequential file which contains the SDEP segment and the date and time the SDEP was inserted into the database. The output file can be sorted for chronological processing.
The output file is a fixed length record sequential file. DBUTIL derives the LRECL and notify you before proceeding with the unload. The first eight bytes of the output file record contain the date/time stamp. The next eight bytes contain the SDEP segment-name. The remainder of the record contains the SDEP segment data. The date is an unsigned three byte packed decimal field in Julian format. The time is an unsigned 5 byte packed decimal field and contains resolution to the hundredths of seconds. You can process this file with any program or utility which supports fixed length sequential files.
The PCB used must reference a DEDB database which contains an SDEP definition. The PCB does not have to list the SDEP as a SENSEG.
The USEQ function has no DL/I call equivalent. That is, most of the other DBUTIL special functions cause DBUTIL to issue a specific series of DL/I calls to perform the operation. The USEQ function causes specialized processing between DBUTIL and the DEDB database.
USEQ control statement
*Record columns *---+----1----+----2 USEQ filename
'filename' specifies the output file which will contain the unloaded SDEP data. This is required and must be a valid filename. A drive and/or folder can be specified with the filename.
Interactive input of USEQ
Enter USEQ as the Function and enter the PCB number or database-name of the database you want to process. Enter the output filename in the field labelled SSA #1. If the filename is too long for SSA #1, it can be continued to the SSA #2 field. For example:
Function
: USEQ PCB ID : 02 SSA #1 : DEDBSDEP.DTA
This function sets DL/I call tracing options for unattended running of a control statement file. It would defeat the purpose of command line operation if you first had to use an interactive screen to set tracing options.
The MSGS command turns off PSB, SSA and I/O area tracing. It turns on Call Stats tracing and screen tracing but disables the Pause feature. Data set tracing is not changed. These options were chosen so that database utility commands, such as LOAD and UNLOad, would not clutter the display with trace information and require the user to press Enter.
The MSGS command does not change the settings in the System Configuration file.
The MSGS command is entered starting in position 1 of the control statement and has no options. Although it can appear anywhere in the control file, we recommend placing it as the first record.
DBUTIL performs loading of databases by issuing DL/I calls. DBUTIL requires that input data sets for loading contain the segment-name. From this, a DL/I insert call is issued using the segment-name from the input record as the only SSA in the call. Since the input data set must also contain the segments in hierarchical order, the segments are inserted into their correct position in the database because of the DL/I current position rules for insert calls with missing SSAs.
This technique requires special consideration when loading databases containing logical children segments. You must know the logical insert rules for the destination parents (logical parents) or always ensure that the destination parent exists when inserting a logical child. Since the load uses a DL/I call, it follows the same rules as if an application program were doing the insert call.
To ensure that the destination parent exists, using an example of a uni-directional, non-recursive relationship, is to first load the database which contains the logical parent and then load the database which contains the logical child. When loading in this sequence the insert rules for the logical parent do not affect the load. However, there can be some performance degradation if the logical parent specifies Virtual insert rules.
For a bi-directional non-recursive relationship, a similar technique can be used. You can load either side first, as they each contain a logical parent but when you load the first database, do not load the logical child segment. The easiest way to do this is to exclude the logical child segment from the PCB which is being used for the load.
Trying to insert the logical child fails with a status code of 'AM' (application not sensitive to segment) but the load continues with the next segment. Since the AM status code does not affect database position, the remaining segments under this parent are inserted correctly. Then load the other side of the relationship and include the logical child. This causes IMS Option to insert the paired logical child on the other side of the relationship automatically.
Recursive relationships, both uni-directional and bi-directional, have similar but different concerns for loading. Recursive, as used here, means the logical parent resides in the same physical database as the logical child. For recursives, it may not be possible to load the logical parents first as the logical parent segment type is often the same as the physical parent segment type.
If you load the logical parents first and then load the logical children, during the load of the logical children, all of the logical children's physical parents fail the insert with an 'II', duplicate key. You might be able to take advantage of the 'II' position rules. This is explained in the section Loading using 'II' position rules. Otherwise, you can load the recursive relationship as follows.
To provide for loading recursive relationships, the DBUTIL utility has an additional function entitled LDIX - which means Load failed IX status codes. The general technique for loading uni-directional recursive relationships would be to load the database using the physical path, including the logical child segment in the first load. Many of these logical children fail the insert with an 'IX' (no logical parent) status code. Any segments which fail with an IX are written to a 'pending' data set. When the load is complete, run the LDIX procedure which takes the pending data set and re-inserts the logical children using the concatenated key of its physical parent.
It would not generally make sense to perform the LDIX function on a non-recursive relationship as the logical parent resides in a different database. If you immediately tried to use the LDIX procedure after a load of a non-recursive relationship, the same logical children would again fail with an IX status code because the other database still does not contain the logical parent.
The technique for loading bi-directional recursive relationships is the same as for uni-directional except that only one side of the relationship can be loaded. The easiest way to do this is to exclude one of the logical child segments from the PCB which is being used for the load.
Variable Intersection Data (VID) segments which are dependents of a logical child which failed with an IX status code are NOT written to the pending file for the LDIX function. If you have variable intersection data for logical children in a recursive relationship, you should use the 'II' position load technique described in the section Loading using 'II' position rules. Otherwise, the VID segments are not loaded when running the LDIX procedure.
When an inserted segment fails with an II (duplicate key) status code, database position is immediately before the failed segment for subsequent retrieval calls. For insert calls, database position is considered to be on the failed segment. That is, if you issue an unqualified insert call after receiving an II status code for a dependent segment, it is inserted as a child of the duplicate II segment.
This is useful when loading recursive relationships or when loading databases which have uni-directional relationships which point to each other as if they were bi-directional.
For example, consider a database with one bi-directional recursive relationship where one of the logical children has some physical children (VID). Load all of the segments other than the two logical children and any VID. Remove the two logical children segments and the VID segments from the PCB definition in the PSB used to perform the load. When complete, all of the physical parents (and logical parents) and any other segments are loaded in the database.
Using the same input data set but a different PCB, load only the logical child which has the VID, all of its VID segments and its physical parent(s). This is done by removing the other segments from the PCB. When the physical parents are inserted, they fail with an 'II' status code but position is on that segment for the next insert call. The logical child would be inserted under its correct parent. The logical child's VID segments load normally. The paired logical child (the one without the VID) is inserted automatically by IMS Option when the logical child is inserted.
As an example of two uni-directional relationships which point to each other similar to a bi-directional relationship, consider two separate physical databases, each containing two segments. The child segment in each has the root segment of the other database as its logical parent in a uni-directional relationship. You could load this as a three step process:
Like the other load technique, the easiest way to select which segments are loaded is to put only the desired segments in the PCB. If other segments are inserted, they fail with an 'AM' status code which does not affect database position for subsequent inserts.
Copyright © 2001 Micro Focus International Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.