REPLACE (Replace All Data in a Table)
The REPLACE function of DBUTLTY provides the ability to load one table in a multiple table area with new data, replacing all current data for that table.
The REPLACE function of DBUTLTY provides the ability to load one table in a multiple table area with new data, replacing all current data for that table. It executes in the MUF and allows full read and update access to every other table in the same area and database to other applications in the same MUF. For example, you could extract a table that is not a partitioned table, redefine it to be a partitioned table, and use the REPLACE function to populate it, provided all the other rules are met, such as the table to be replaced not being the only table in an area. FILEIN may be used before a REPLACE.
The following topics are discussed on this page:
- The area must be loaded in the URI format.
- The area may not be clustered.
- The input data must be in EXTRACT format.
- The table may not have variable length records, for example inDatacomVSAM Transparency.
- The table may not be relative record.
- The table may not be the only table in the area.
- Sort is required.
- May not name a partition Full Parent or Any Parent.
When to Use REPLACE
REPLACE is a special purpose function and is not a general replacement for the LOAD function. For reasons related to performance and ease of use, instead of placing every table in a database in its own area it is often better to place multiple small tables in either a single area or in a few areas. Although a single table in a multiple-table area cannot be loaded, the REPLACE function provides you with a similar ability. However, be aware that it is probably best to place a table with a large number of rows in its own area and a table with a very large number of rows in its own database. The area can be conveniently backed up and loaded as an area or a database. Occasionally, one table in a multi-table area needs to be replaced with no impact on other tables, such as when a table is being redefined. This specific niche is the reason for this function and, as such, it has numerous restrictions.
If the input file is empty, the table is effectively null loaded.
This utility function executes under control of a MUF but can also contain Single User processing. The table cannot currently be open for the REPLACE function to start. When the REPLACE function starts, it marks the table as not loaded and, when successfully completed, it sets the table as loaded. The function opens the database for update in the MUF and opens it for read in Single User processing.
The date/time loaded as reflected in the Directory (CXX) report and the Dynamic System Table DIR_TABLE is updated to reflect the REPLACE function. However, this assumes that the Directory is Version 11.0 style. Running in a compatibility mode with a r10 style, the date and time reflects the last LOAD function and is not updated for a REPLACE function.
Even though the data replace processing occurs in the MUF, the deletion of old records and insertion of new records are not subject to logging. Therefore, no transaction backout can occur. If the utility fails, you must restart it.
If the area has insufficient space for the data or index, a dynamic extend is attempted. If it fails to find enough space, a return code 07 or 08 occurs. Perform a DBUTLTY EXTEND and restart the REPLACE function.
Note that new Unique Row Identifiers (URIs) are assigned to the added data. The numbers only start over from 1 when an area is loaded (when not using RECID=YES). If the high number should reach four billion, additions stop with a return code. In this case, you must back up the area and reload before more additions (including those with the REPLACE function) can be done.
The console status messages provided during a sorted LOAD or RETIX function also occur for the REPLACE function. Note however that, since much of the work being done by the REPLACE function occurs in the MUF address space, the progress often occurs in 'spurts' and some progress information is not available.
REPLACE does not affect the MASSADD function. REPLACE takes the place of most MASSADDs, however, for those who use MASSADD to load multiple table areas.
Highlights of the Utility Side of the Processing
Reads the input and blocks rows to MUF at the task area size.
Builds index records for sorting.
Accepts sorted keys and sends blocks to MUF for index merge.
Highlights of the MUF Processing
Reads area in physical sequence with SEQBUFS=128.
For each block read, deletes all rows for the table being replaced.
If block has space and new data exists, adds new rows to the block.
Space management updated as required for additions and deletions.
If block changed, is scheduled for pipeline write.
Assigns new URI to each row.
Successful Execution Requirements and Controls
- The database may or may not be open for update in the connected MUF.
- The database maynotbe open for update in another MUF or Single User job.
- The database maynothave any table inunloadingstatus.
- The database ACCESS must be set OPTIMIZE and WRITE/UTLTY.
- The area ACCESS must be set WRITE/UTLTY.
- The table may not be in an open User Requirements Table.
- When MULTUSE=YES, this function will not be allowed against a Data Sharing MUF if more than one is executing.
- The database will be opened by MUF for update.
- The utility ACCESS AREA is not set (table level function not area level).
How to Use REPLACE
The MUF must be active when you execute this command. Execute the REPLACE function using the following command format.
►►─ REPLACE ─ TABLE=t,DBID=n,DDNAME=d,MULTUSE=YES,SORT=n─────────────────────► ►─┬──────────────────────────┬─┬─────────────────────────┬───────────────────► └─ ,LOADPTN= ─┬─ FAIL ◄ ─┬─┘ └─ ,OPTION1= ─┬─ NOF ◄ ─┬─┘ └─ SKIP ───┘ ├─ '(I)' ─┤ └─ '(U)' ─┘ ►─┬─────────────────────┬─┬─────────────┬─┬──────────────────────┬───────────► └─ ,SLACK= ─┬─ 0 ◄ ─┬─┘ └─ ,SORTDD=n─┘ └─ ,SORTWK= ─┬─ 3 ◄ ─┬─┘ └─nnn─┘ └─n───┘ ►─┬────────────────────────┬─┬───────────┬───────────────────────────────────►◄ └─ ,UNIT= ─┬─ SYSDA ◄ ─┬─┘ └─ ,PRTY=n─┘ └─c───────┘
- REPLACEInvokes the function to load one table in a multiple table area with new data, replacing all current data for that table.
- ,DBID=Identifies the database containing the table to be loaded.
- Limits:DATACOM-ID of the database
- Default:(No default)
- ,DDNAME=Specifies the JCL DDname for the input data set.A DDNAME is not acceptableDatacomarea. Names with the following patterns are therefore not acceptable for DDNAME=:
- 3-byte names that end with XX, meaning they are reserved as either current or futureDatacomcontrol areas.
- 6-byte names that end with what could be a database ID from 001 through 999.7-byte names that end with what could be a database ID from 1000 through 9999.The DDNAME= value is verified for acceptability to protect you from unintentionally causing data corruption. The DDNAME check is the default but optional. You can prevent the DDNAME check by using a DBSIDPR parameter (DBUTLTY_EDIT_DATA_SET=) for individual MUF environments. However, we recommend that you allow the DDNAME check.The data corruption risk involves not the DDNAME itself but the content of the data set. For example, suppose that you used the CXX DDNAME as the output of a backup. You then copied the CXX DD statement and changed the DDNAME of the copy to be acceptable, avoiding the DDNAME= error. The backup would, however, then overlay the CXX data set, which is not the intent of a backup.If you specify an unacceptable name for DDNAME=, message DB10059E is generated.We recommend that you allow DDNAME= check protection. You can, however, disable DDNAME= protection. To disable protection, assemble the DBSIDPR module used for thisDatacomenvironment and specify NONE for the DBUTLTY_EDIT_DATA_SET= parameter. The default is DBUTLTY_EDIT_DATA_SET=FULL_1, which allows DDNAME= protection.
- ,MULTUSE=YESSpecifying MULTUSE=YES is required when executing the REPLACE function.
- Default:(No default)
- ,SORT=Sorting index entries is required.The OPTION1= parameter is highly recommended to obtain warning and critical messages if a problem occurs.To estimate the value to enter, multiply the number of keys in the table by the number of records to be sorted and add 30 percent. For example, if the table contains 2 keys and you are sorting 1000 records, the value you specify should be at least 2600. The value you specify is passed to the sort routine and can affect the amount of runtime used by the sort. If you use multiple LOAD or REPLACE statements in a single step, place the largest sort value first.Donotspecify over 99 million unless you verify that the sort package you are using allows a larger number.
- TABLE=Indicates a single table is to be loaded.
- Limits:DATACOM-NAME of a table in the database and area being loaded
- Default:(No default)
- ,LOADPTN=Specifies how the utility handles a rejected row for a table partition. If it is set to SKIP, the row is ignored with no error condition. If it is set to FAIL, the row is dumped, and the utility function terminates.
- Limits:FAIL or SKIP
- ,OPTION1=Specifies the statistics display option to be passed to the sort program (see the SORT= parameter).
- NOFCorresponds to the NOFLAG parameter of the IBM sort. Only critical messages are to be displayed on the console.
- '(I)'Specifies that informational and critical messages are to be displayed on the printer and the console.
- '(U)'Specifies that only critical messages are to be directed to both the printer and the console.NOF, '(I)', and '(U)' correspond to the SyncSort MSG options of CC, SC and CB respectively.This parameter also accepts any three-character (including the surrounding parentheses) FLAG parameter that can be processed by the IBM sort. See your IBM sort manual for details.
- Limits:NOF, '(I)', or '(U)'
- ,PRTY=Identifies for this execution the URT priority of frequent or long-running DB requests in the MUF issued by this DBUTLTY function.
- Limits:1 (lower) through 15 (higher)0 is allowed but means that you should use the default 16 value
- Default:16 is the default value if PRTY= is not specified or is set as 0
- ,SLACK=Specifies the number of bytes to reserve in each data block for future expansion of records in that block, or for addition of records when using a space reclamation option. The number entered applies only to blocks loaded to an area during this execution of the REPLACE function. Blocks not loaded with data use the SLACK value specified in the AREA definition inDatacom Datadictionary.
- Limits:A number that is less than the block size
- SORTDD=Specifies an optional DD statement, provided to the SORT that redirects SYSPRINT-type output from the SYSPRINT DD (used by DBUTLTY) to this optional DD statement.
- Limits:printed output directed to optional DD statement
- Default:printed output directed to SYSPRINT, intermixed with DBUTLTY output
- ,SORTWK=Specifies the number of sort work areas.Do not specify a 2-digit number unless the sort package you are using allows a 2-digit number. Sort packages that do not support a 2-digit number can fail in an unpredictable manner, if you specify a 2-digit number.
- ,UNIT=Specifies the unit name for sort work areas. The parameter is always edited, but is only used when the SORT= parameter is specified as not zero.
- Limits:1- to 8-character unit name
Example JCL (REPLACE)
Following is a JCL example for the REPLACE function.
Use the following as a guide to prepare your JCL. The JCL statements are for example only. Lowercase letters in a statement indicate a value you must supply. Code all statements to your site and installation standards.
//jobname See the previous note andJCL Requirements.// EXEC PGM=DBUTLTY,REGION=2M //STEPLIB See the previous note andJCL Requirements.//CXX DD DSN=cxx.data.set,DISP=SHR Directory data set //IN1 DD DSN=input Input data set //* //*NOTE: The following sort work allocations are not//*required if your sort package can dynamically//*allocate sort work areas. Be sure to provide//*sufficient space for all keys to be sorted.//* //SORTWK01 DD UNIT=disk,SPACE=(alctyp,(prim,scnd)) //SORTWK02 DD UNIT=disk,SPACE=(alctyp,(prim,scnd)) //SORTWK03 DD UNIT=disk,SPACE=(alctyp,(prim,scnd)) //SYSIN DD * Command Input REPLACE DBID=1,TABLE=F01,DDNAME=IN1,SLACK=12, SORT=300,MULTUSE=YES /*
Following is a sample report page. For an example report header, see Sample Report Headers.
Following begins a REPLACE sample report.
CONTROL CARD(S) .........1.........2.........3.........4.........5.........6.........7.........8 REPLACE DBID=1,TABLE=F01,DDNAME=IN1,SLACK=12, SORT=300,MULTUSE=YES FUNCTION=REPLACE DBID=00001 DDNAME=IN1 MULTUSE=YES SLACK=0012 SORT=000000300 TABLE=F01
This page of the report shows the following:
- The command exactly as entered.
- An analysis of keywords encountered and expected. Any errors found are flagged with a note in the left margin.
- Any messages related to syntax processing.
TABLE RECORDS F01 50
This page of the report shows information about the table and records.