COBOL SQL Trace procedure

COBOL SQL TRACING INSTRUCTIONS

PART I – IDENTIFY THE TRACE SETTING

The trace setting that you choose to use will be determined by a single number that is the sum of the values corresponding to each option that you wish to include.   These are the values for each of the options available to you:

1 = SQL Statements

2 = SQL statement variables

4 = SQL connect, disconnect, commit and rollback

8 = Row Fetch (indicates that it occurred, not data)

16 = All other API calls except SSBs

32 = Set Select Buffers (holds values of columns to be selected.

64 = Database API specific calls

128 = COBOL statement timings

256 = Sybase Bind information

512 = Sybase Fetch information

So if you only wanted to trace the SQL Statements, SQL Statement variables and Row Fetch, you would use 11 (1 + 2 + 8 = 11).

If your analyst provides you with a setting to use, use that setting.

If you do not know which setting to use, use 255.

PART II – PROCESS SCHEDULER TRACING

The easiest method for getting a cobol SQL trace of a process is to use the tracing options that are available to us when the job is run via the Process Scheduler.  If the problem is occuring during a remote call (online) job only, and/or you cannot replicate the problem using the equivalent batch process, then you’ll need to go to PART III for App Server tracing instructions. 

Step 1 – Identify the job that you will be tracing

Go to the Run Control Page of the job you wish to trace.  In my case, I am going to trace a process called Student Refunds, and so I will go to that page.  Once there, I will press the Run button one time:

This will take you to the page where you can then note the name of the process you are running:
Step 2 – Set the trace

Once you have this information, go to Peopletools, Process Scheduler, Process Types and find the default parameters for all Cobol SQL jobs using your platform.  In my case, I will use the NT/Win2000 platform on an Oracle database:

Copy these default parameters to your clipboard (make sure you get the entire string in when you do the copy:

Now go to Peopletools, Process Scheduler, Processes, Override Options and find the job that you are tracing, which you did at the very beginning of this step.  You can then change the Parameter list value to Override and paste the default options that you just copied to your clipboard into this parameter list field:

Now locate the pair of forward slashes between INSTANCE and DBFLAG in the parameter list and insert your trace setting in between these two slashes (in my case I am using 255:

Save the page, and tracing is now set for this specific process.  You can now run the process normally from your usual run control page.

Step 3 – Retreive the trace

To retrieve the trace that you just ran, go to the process scheduler page (from the run control page or from Peopletools navigation) and find the job that you just ran.  Once you find your job, drill down on Details:

And then drill down on View Log/trace:

And you can then find the trace:

PART III – TRACING ON THE APP SERVER

Step 1 – Set the trace on the App Server Configuration File

TraceSQLMask is the variable that you will set.  Use the value that you determined in Part I.  If you do not know what setting to use, use 255.

Once the trace has been set on the App Server, ALL remote call cobol jobs will be traced, so use this judiciously in your production environment.  Once you have retreived the trace, be certain to turn it off.

Step 2 – Retrieve the trace

Once the job has been completed, you can turn off the trace at the App Server and retrieve the trace.  The trace will be located in:

<PSHOME>/Appserv/<domain>/Logs/

The name of the trace will start with ‘cobsql_xxxxxx.trc’ where xxxxxx is a date time stamp indicating when the trace was made.