7 Batch Loading Data

7.1.1 Defining a Rollback Segment

To prevent exceeding the rollback segment space and causing the entire job to fail, you can choose to run the batch data loader in its own rollback segment. Define the rollback segment as follows:

1. Define and tune a rollback segment and bring it online, following instructions in the Oracle SQL Reference manual.

2. Enable the segment for use in Oracle Clinical by adding the entry BDL_R_ SE to the reference codelist OCL_STATE. For further information on this reference codelist, see the Oracle Clinical Administrator's Guide.

7.1.2 Defining and Using Format Masks

To batch-load data into Oracle Clinical, you must create or select a format mask with which you specify how the data in your files corresponds to fields in Oracle Clinical. Masks achieve this correspondence in two different ways:

• Positional masks define a length for each column in the data file; for example, the STANDARD format mask (described below) assigns the first ten characters to the Investigator, the following ten characters to the site name, and so on.

• Delimited masks have no set column length and separate column values with a user-defined delimiter character such as a comma (,). Flexibility in field length can save space.

Oracle Clinical ships with a predefined format mask of the fixed or positional type. You can define your own format masks as required.

,,2,,VISIT1,0,20110101,,HEMA,HEMA,HEMA,LAB,HCT,0,1,43 
,,2,,VISIT1,0,20110101,,HEMA,HEMA,HEMA,LAB,HGB,0,2,17 

,,2,,VISIT1,0,20110101,,HEMA,HEMA,HEMA,LAB,LTEST,0,1,HCT 
,,2,,VISIT1,0,20110101,,HEMA,HEMA,HEMA,LAB,LVALUE,0,1,43 
,,2,,VISIT1,0,20110101,,HEMA,HEMA,HEMA,LAB,LTEST,0,2,HGB 
,,2,,VISIT1,0,20110101,,HEMA,HEMA,HEMA,LAB,LVALUE,0,2,17 

7.2.1 Load

Moves the data in the data files from the server to the Oracle Clinical database table Temporary Batch Data Items (TBDI).

7.2.2 Prepare

Updates the TBDI table, and creates the Temporary Batch DCI and Temporary Batch DCM tables. During this stage the system looks for completeness and consistency of the records, whether the keys are valid, and checks for locked RDCMs.

Also during this stage, records from the data files are divided into INSERT and UPDATE records. INSERT records are those that do not already exist in Oracle Clinical, and need to be created; UPDATE records are those that have updated information for Received DCIs and Received DCMs that already exist in Oracle Clinical. These records are termed pending inserts and pending updates, respectively.

You can run the Detail Load Impact Report, or the Summary version of this report, after the Prepare stage to get a list of how many RDCMs have been updated or inserted.

7.2.3 Transfer

Moves the data from the temporary database tables (Temporary Batch Data Items, Temporary Batch DCIs, and Temporary Batch DCMs) to the permanent Oracle Clinical tables (RDCMs, RDCIs, Responses, and Actual Events) if necessary.

7.4.1 Preprocessing Data Files

Before you start loading data into Oracle Clinical, some preprocessing may be necessary to bring data into conformity with required format standards.

If you are using a positional or fixed format, including the shipped STANDARD mask, each value occupies a fixed column position, as listed in Table 7-1. If there is no data in a field, that field must contain spaces for the expected number of characters to make the record conform to the fixed format. However, you can leave the fields for subevent number, investigator, site, document number, or DCM question group name blank, in which case the individual field is calculated automatically. (See "Subevent Number Not Supplied" for a discussion on what occurs when the subevent number is left blank.) If the data is not in the correct fixed format, batch data load will not succeed.

If you haven't already, also check the format mask to make sure that the target fields do not include derived questions because this can cause the job to fail or even corrupt data or prevent derivation for other documents. See Defining and Using Format Masks for information about format masks and see Loading Data into Derived Question Fields for details about risks and remediation.

Updating discrepancy entries - rows to update
Fatal error: No rows in Discrepancy Entries table updated.

Derived questions exist for TMS. Calling TMS_OCL_DERV.DERIVATION second time
value of :last_tms_deriv_ts 16-SEP-2016 08:17:42
Error during TMS Thesaurus Validation
Batch Validation aborted due to an error
SQL error during TMS Thesaurus Validation: SQLERROR ORA-00001: unique constraint (RXC.RESPONSE_PK_IDX) violated
ORA-06512:

7.4.2 Creating and Viewing Data File Entries

You must log information about the files into Oracle Clinical—that is, create entries for the data files so that Oracle Clinical can access and process them.

• Creating Data File Entries for Out of Study Batch Data Loads

• Creating Data File Entries for Standard Batch Data Loads

7.4.3 Selecting Data Files

To select data files from a directory to include in a data file group:

1. Click the Select Server Files button in the Maintain Batch Load Data File window to reach the Select Server Files window.

2. Enter the directory that contains your data files.

3. Specify a query filter, if necessary. The wildcard % is allowed.

4. Click the Get Directory Information button for information on this directory.

   Note:

   If information is retrieved a second time for the same directory in the same session, and if Refresh? is selected, another query goes to the operating system; otherwise, you would see outdated information.

5. Click in the Select column next to each data file you want to include. You can use the Select All and Deselect All buttons and the navigation buttons at the bottom of the window.

6. Save your input.

7.4.4 Loading Data

To successfully load data from an external source into Oracle Clinical tables you must perform all three batch data load stages, in order: Load, Prepare, and Transfer (see "Batch Data Load Stages"). You can choose to perform a single stage or multiple stages at a time. Performing a single stage allows you to check the error file just for that stage and takes less time.

1. Navigate to Conduct and, depending on the files you are loading, select either Batch Data Load or Out of Study Load. See Standard Batch Data Load and Out of Study Batch Data Load for details.

2. Select one of the following menu options to execute one or more of the Batch Load stages:

   • Load Batch Data

   • Prepare Batch Data

   • Transfer Batch Data

   • Load/Prepare Batch Data

   • Load/Prepare/Transfer Batch Data

   • Load/Prepare/Privileged Transfer. This option allows you to update locked data, and is available only if you have special privileges. If you select this option the system automatically updates locked data without prompts if updates to additional data are loaded.

   The appropriate Parameterized Submission (PSUB) form is displayed with the parameters required for the stage or stages you selected.

3. Enter values for the job's parameters. The parameters displayed depend on the stage or stages you selected. The complete list of parameters includes:

   Table 7-5 Standard and Out of Study Batch Data Load Parameters

   Stage	Parameter Name	Description
   Load	Datafile Group	ID of the data file group you want to load. A list of values is available.
   Load	Max No. of Bad Data Allowed During Load	Maximum number of bad records allowed before the system ends the entire process. If fewer than the maximum number of bad records are found, the batch load continues; the bad records are not loaded, but are reported in the log file.
   Prepare	OS File ID for Prepare	ID of the data file group you want to prepare. A list of values is available.
   Prepare	Max No. of Errors Allowed During Prepare	Maximum number of bad records allowed before the system ends the entire process. If fewer than the maximum number of bad records are found, the batch load continues; the bad records are not loaded, but are reported in the log file.
   Prepare	Prepare to Completion?	Whether to complete the Prepare phase or to accept a partial completion.
   Prepare	Spool Directory for Reloadable Data Files	If Prepare to Completion is Y, the system populates this field with the appropriate value for the user, according to the values of the BDL_ROOT and BDL_USERDIRS in the OCL_STATE local reference codelist, and the user's account name (minus "OPS$" if it contains OPS$.
   Transfer	Data File Group ID for Transfer	ID of the data file group you want to transfer. A list of values is available.
   Transfer	Transfer Mode	The mode in which the transfer should take place. Values include: INSERT - Transfer only pending insert records. UPDATE - Transfer only pending updates records. BOTH - Transfer both pending inserts and pending update records. If you are using Out of Study load and are loading DCIs with multiple DCMs, select BOTH for proper processing. The process sees the records in the first Received DCM in a Received DCI as inserts and the records in subsequent Received DCMs as updates to the existing Received DCI.

   
   

4. Click the Job Details button to display the Submission Details window and set the appropriate fields; see Oracle Clinical Getting Started for details. Then click Back to return to the Submission of Module window.

5. Click Submit Job.

6. Click the Job Status button to monitor progress of the batch job.

   If there is a problem loading the data, the load stops and an error message appears. To review details about why the load was aborted, look at the log file created during the batch load. See "Troubleshooting Batch Validation".

   Note:

   After the Transfer stage you must indicate if you want to purge pending or bad updates or inserts by displaying the Maintain Batch Load Data File form.

   To delete inserted (new) records included in the batch file, select Purge Inserts from the Special menu. Update Status should be UPDATED, and Insert Status should be PENDING or BAD INSERT.

   To delete updates (additions to existing records), select Purge Updates from the Special menu. Insert Status should be INSERTED, and Update Status should be PENDING or BAD UPDATE.

7. If you have selected one of the steps that does not execute all three stages, repeat steps 2 through 6 for the remaining stages until the batch data load process is completed successfully.

You can use the Batch Job window to query on your job ID to see when it completes, or you can use the Maintain Batch Load Data File window or the Maintain Out of Study Batch Data Load File window to view the status of the batch job; the system automatically updates the Data File Status (DF Status) depending on your menu selection and the success of the current batch load stage.

In the Maintain Batch Load Data File window you can also change the comment text (only before processing starts) for a data file or purge either the pending inserts or pending updates, which are records of the data files not successfully inserted into the Temporary Batch Data Items, Temporary Batch DCIs, or Temporary Batch DCMs tables.

Purging Pending Inserts and Updates in the Maintain Batch Data Load Window In the bottom part of this window, place the cursor on the data file group name with the appropriate data file status and then select either Purge Updates or Purge Inserts from the Special menu.

Purging Pending Inserts and Updates for Out of Study Load Files To purge pending inserts or pending updates of Out of Study Load files:

1. Query for the data file in the Maintain Out of Study Batch Load Data File window.

   In the bottom half of the window the last part of each data file group name contains the study name for which the data was loaded. Note the study name and the data file group names for which you want to purge inserts and/or updates.

2. Navigate to the Maintain Batch Data Load Files window, select the appropriate study and query for the data file group name.

3. In the bottom part of this window, place the cursor on the data file group name with the appropriate data file status and then select either Purge Updates or Purge Inserts from the Special menu.

7.5.1 Troubleshooting the Prepare Phase

The batch data loader also performs error checking during the Prepare phase. To diagnose these errors, follow these steps:

1. Open the .log file to get a general overview of the errors. When data files fail, you can check the log file for the status code of RECEIVED or PREPARED. The data file will not contain these status codes.

2. Open the .out file to see specific details about the errors.

   • First review the error messages, and use Table 7-6 to diagnose them.

   • Then review the Error Reporting Table that follows each reported error to get a more detailed analysis of the problems. Table 7-7 shows an example of the Error Reporting Table.

Table 7-7 shows an example of an Error Reporting Table that is displayed in the .OUT file if there are errors associated with the Load or Prepare phase. The example reports errors for Investigators. An equivalent Error Reporting Table is created for errors associated with patients.

In Table 7-8, the "Yes" in the Wrong Site column indicates that Oracle Clinical was expecting a different site from the one it found in the data file. In this case, it was expecting Site 1 and found Site 2.

To correct this error, either:

• change all occurrences of Site 2 to Site 1 for Investigator 3260 in the data file

• change the site assignment for Investigator 3260 to Site 1 in Oracle Clinical

After the Transfer stage, the log shows the number of records updated and the number of records inserted.

7.5.2 Troubleshooting the Transfer Phase

The log file keeps track of the status of every discrete job during the Transfer process, including the names of the data files being loaded, summary information, and errors. If the transfer is successful, the log shows the status at the beginning and at the end.

7.6.1 Auditing Data Deletion

The Oracle Clinical table STUDY_DATA_DELETE_AUDIT records each time a batch data delete job is run. The table records:

• the parameters used to invoke the deletion

• the actual number of each object that is deleted by the run

This audit table enables you to document the use and impact of the data deletion facility, and may help you diagnose whether a problem has been caused by a hard data delete.

When you execute batch data delete with audit, the records in the deleted CRFs are backed up in a set of audit tables. For each source table that is deleted, a new corresponding audit table is created with a suffix of $BDD (for instance, RECEIVED_DCIS$BDD). Columns in the table include the batch_job_id followed by the same columns as in the source table. For example, RESPONSES$BDD includes batch_job_id followed by same fields as responses.

The job generates audit tables corresponding to the following Oracle Clinical tables:

7.6.2 Reports on Audited Data Deletion

You can run two scripts to generate reports on audited deletions of data and discrepancies. For information, see the section about running reports on deleted data and discrepancies in the "Utilities" chapter of the Oracle Clinical Administrator's Guide.