Tips & Tricks - Server File Environment

Contents

Use Oracle's DIRECTORY to set up File Paths

Traditionally, we have used the parameter UTL_FILE_DIR to define file paths for our server files. This is a database parameter, and the database has to be stopped and restarted when the parameter has changed.

Instead, we may use the CREATE DIRECTORY command to define a server path for our files :

Syntax : CREATE OR REPLACE DIRECTORY <directory name> AS '<file path>'  
Further : GRANT READ (,WRITE) ON DIRECTORY <directory name> TO <user> (i.e.APPOWNER)

Benefits:

  1. We can do this 'on the fly' without having to restart the database.
  2. Use of Directories instead of physical file-paths in the Job Header eliminates the need for editing basic data when we export a job to another database.

Example :

CREATE OR REPLACE DIRECTORY DIRONSERVER AS '\\machine_name\DirOnServer';
GRANT READ,WRITE ON DIRECTORY DIRONSERVER TO IFSAPP;

Used in Data Migration :

   

The Directory DIRONSERVER will point to one path in the Test-database and another in the Production database.

Note:  1. Directory names are stored in UPPERCASE and must be referred in UPPERCASE
          2. We sometimes create databases by copying database-files.
              Remember to update defined DIRECTORIES in the new database with correct server paths.

 

Use EXTERNAL TABLE to load a file into a IC-table

This option is useful if large data files should be loaded into a temporary tables using procedure CREATE_TABLE_FROM_FILE.

As from Oracle9 it is possible to create a table that points to a server-file, a so-called External Table.
The table is not populated by data, but the data on the file is accessed by using SQL*Loader features.
The SQL*Loader reads data from server files faster than UTL_FILE.

Up to App7 CPS3, Data Migration has used Oracle's UTL_FILE utility to read server files, but we have introduced a new Rule EXTTABLE that makes it possible to use Oracle's External Table concept for jobs using procedure CREATE_TABLE_FROM_FILE.

The file job setup is done in exact same manner as before. But when the job is started, it will execute in 3 steps :

Benefits: Populating a IC-table with data can be performed 10 times faster than before.
 

Example:

Below is a setup of a simple file job. After setting up a job, you may preview what the statements for creating the table + inserting data .
In the LU IntfaceServerFiles, procedures are available and returns complete statements that are used during execution.

Create External Table example :

Using the procedure Intface_Server_File_API.Show_Create_Ext_Table_Stmt,

Insert from External to IC-table example :

Using the procedure Intface_Server_File_API.Show_Ins_From_Ext_To_Ic_Stmt,

Error handling with EXTERNAL TABLES

Sometimes we get errors when creating the External Table. As we define all columns as VARCHAR2, the main cause are field length errors.

Data Migration will detect if errors have occurred and will load the LOG-file into the Detail folder in the Execute Job window.
 

Note :

  1. Always check the feedback-message in Last Info.
    There may be a error-message after the Create Table statement.
  2. The LOG-file from the SQL*Loader job will be loaded into the Detail folders File String.
    Search for lines starting with 'KUP' and you will see the Loaders error message.

EXAMPLE 1 :

  1. We can see that some records are being rejected.....
  2. ...but there are some OK records inserted in the IC-table....
  3. ... and there is 1 rejected record

Now, we would like to complete the IC-table with the rejected record. See below how it is done.


 

  1. RMB on header of Execute Job and select 'Migration Job..' . This will direct to the Migration Job detail view. From LOV on File Name under 'Formatting' tab, we see that a BAD-file has been created for this job.
    The BAD-file contains the rejected file records. We select this file and save the migration job.


 

  1. Then RMB on the Migration Job header and select 'Start Job'. This will direct back to the Execute Job view. First clean up the existing job using RMB 'Clean Up' and load the file into the Detail folder with RMB 'Load File...'There is 1 record on the file. ACTIVE exceeds the defined length 5 in File Mapping


 

  1. We remove the trailing 'E' by activating Rule TRUNCVAL. (Values exceeding pos 5 will be truncated)
  2. We also activate rule CRETABCONF with value KEEPALL.
    Data Migration will now keep the previous 7 rows in the IC-table and add this one.
  3. When executing the job now, the one missing row is added to the 7 previous records

Note: Remember to deactivate rule CRETABCONF after this execution