How to debug SQL*Loader jobs on an Applications Manager Windows Agent

Products

CA Automic Applications Manager (AM)

Issue/Introduction

Environment

Release: AAMOS499000-8.0-Automic Applications Manager-OS400 Agent
Component:

Resolution

Description

In order to troubleshoot a SQL*Loader job on an Applications Manager (AM) Windows Agent you would want to try and run the job outside of AM to ensure that it was successful and then again through AM's loadp.bat script. If the SQL*Loader job is successful when run outside of AM, but fails when run from AM you would need to contact [email protected] for assistance to further troubleshoot the issue. This article provides the steps taken to run the job both from AM with debugging enabled and outside of AM.

Steps to follow to run a SQL*Loader job with debug enabled from the Windows command line NOT using the AM loadp.bat script.

1) First, enable BODY debug on the Windows Agent where you are running the SQL*Loader job.

- Create an empty file in the %AW_HOME%\debug folder called BODY with no extension.

2) Request the SQL*Loader job from the AM client. With debug enabled the pm and the pr files for the run of the job will remain in the %AW_HOME%\run folder.

3) From the %AW_HOME%\run folder, modify the pm<job's seq_no>.bat file as follows:
a. Modify the 1st line and change "@echo off" to "@echo on"
b. Remove the last 2 lines of the file, the call statement and the exit line.
4) Execute the sosite.bat file and the pm file to set up the environment
%AW_HOME%\site\sosite.bat
%AW_HOME%\run\pm<job's seq_no>.bat

5) Create a loader.par file with the following information:
USERID=<Oracle userid>/<Oracle password>
control=<full path to the .ctl file>
data=<full path to the data file>
log=log.txt
discard=discard.txt
6) Run the loader.par file:
sqlldr parfile=loader.par
7) If the SQL*Loader job was successful the rows would have been inserted and a discard file would have been generated if there were discard rows. You should see something similar to this when you run the command:
D:\AMInstall> sqlldr parfile=loader.par
SQL*LOADER: Release 11.2.0.1.0 - Production on Thu Aug 28 13:09:41 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 14
And, in the log.txt file that is generated you would see something similar to this:
Table LOAD_TEST:
   10 Rows successfully loaded.
   2 Rows not loaded due to data errors.
   2 Rows not loaded because all WHEN clauses were failed.
   0 Rows not loaded because all fields were null.
You can verify that the rows were inserted correctly in the table to confirm that the SQL*Loader job was successful.

8) Remove the BODY file from %AW_HOME%\debug to disable debugging.

Steps to follow to run the SQL*Loader job with debug enabled using AM's loadp.bat script from the Windows command prompt.

1) Follow steps 1 through 4 from the procedure above to enable debugging, run the SQL*Loader job, and modify and run the pm<job's seq_no>.bat script and sosite.bat script to set up the environment.
2) Copy the %AW_HOME%\exec\loadp.bat script to %AW_HOME%\exec\loadp.bak
3) Modify line 284 of the loadp.bat script and set the userid to the Oracle login and password where you are loading the data. You will be changing this line:
print PARFILE "userid=$login\n";
to this:
print PARFILE "userid=<Oracle login>/<password>\@<connect string>\n";
4) Edit the job's output file (%AW_HOME%\out\o<jobid>) from the the job that you ran from Applications manager with debugging enabled and search for the string 'In SOBATS'. There will be a command similar to this in the line right below that calls the LOADP script:
'calling'D:\AMInstall\exec\LOADP control_file=D:\AMInstall\LOADER\test_load.ctl data=D:\AMInstall\test_load.dat abort_bad=Y max_bad=0 direct_load=N compress=N extensions=N suffix_data=dat suffix_complete=end log_f
ile=log.$seq_no discard_file=discard.$seq_no data_file=data.$seq_no
Modify this command to use your LOADP1 script instead of LOADP and the -d option to get more information from the loadp1.bat script, similar to this:
D:\AMInstall\exec\LOADP -d control_file=D:\AMInstall\LOADER\test_load.ctl data=D:\AMInstall\test_load.dat abort_bad=Y max_bad=0 direct_load=N compress=N extensions=N suffix_data=dat suffix_complete=end log_f
ile=log.$seq_no discard_file=discard.$seq_no data_file=data.$seq_no
5) Run the above command from the %AW_HOME%\run folder and the commands being executed will appear on the screen.
6) Once you have completed your test, go back to the %AW_HOME\exec directory and move the loadp.bak script to loadp.bat.
move loadp.bak loadp.bat
7) Remove BODY from the %AW_HOME%\debug folder to disable debugging.