How To Troubleshoot COBOL with TraceSQL & RemoteCall From Application Engine

How do I use the TraceSQL Tool to troubleshoot a SQL error from a COBOL batch process?
How would trace remote call COBOL program called from an Application Engine run via the process scheduler be done?
A Brief Guide to using TraceSQL for troubleshooting followed by tracing for RemoteCall called COBOL from Application Engine process

TraceSQL is a troubleshooting tool which may be used for many different type of COBOL process errors. When the processing error involves a SQL error -- e.g. ORA-00932 inconsistent data types, Trace SQL is a natural choice for trouble shooting.

TraceSQL is not normally invoked during processing so the first step is to recreate the problem with TraceSQL invoked. For COBOL processes this is normally set in the file which sets parameters for the Process Scheduler. The parameter name is TraceSQL and the value set as desired according to the following:

TraceSQL is implemented as a bit map with the following values
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 (identifies the attributes 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 want Statements and Row Fetch and COBOL timings, you would specify TraceSQL=137 (1+8+128). For troubleshooting, sometimes it is best not to guess which values you want so 255 will set all values on -- except for the Sybase specific settings.
Once you have the cobsql file -- the COBOL TraceSQL results, the next step is locating the clues provided for the problem at hand. Start from the bottom of the file and locate the failure point. There will be many rows which indicate rollback and disconnects for open cursors. Once you find the point of the error, note the cursor number -- e.g. cur#12.

Working your way on up in the file, locate the SQL Statement that established this cursor. It is that statement that needs to be further analyzed for the cause of the problem. Start by copying that statement into your SQL tool and replacing any variables (:1, :2 etc.) with appropriate values. Since we set bit 2 on, the SQL statement variable values are listed in the file just below the statement -- or possibly just above the failure point, if the program is updating the variables before each use of the statement.
When you run the SQL statement in your SQL tool, you should probably get the same error as identified in the TraceSQL file. Break down the statement into smaller pieces as need to determine the cause of the error.

If the error is not the SQL statement, but caused by the interaction with the COBOL program, then compare the statement found in the cobsql file with the Stored SQL statement delivered in the current .dms file for the program at issue. The program name is the first part of the SQL statement name which is in the cobsql file. If the correct stored statement is in the TraceSQL results, then perhaps the COBOL program is a fault. Be sure you are compiling the correct version of the source file. In summary there are three common sources of error when SQL portions of the PeopleSoft COBOL programs are changed -- Database Table definitions (Did you alter the table?), Stored SQL definition (Did you use datamover to update?), and the COBOL variables (Did you compile the new program?)

This is the basics for problems with SQL. This Tracing can also be helpful for trouble shooting code logic.

If you have an application engine program that calls a COBOL program, how can you trace the COBOL program?

1) In psprcs.cfg using psadmin ...
Set TraceSQL to a nonzero value (e.g 1, 7, 255 etc.)
Set TraceAE to 131 (1+2+128)
Set RCCBL Redirect to 1 (on)

2) Shutdown the process scheduler and restart it.

3) Run the AE program, the log_output directory will contain the trace

Click here to find out how to troubleshoot and trace Remote Call COBOL on application server

No comments:

Post a Comment

Please refrain for marketing messages and unnecessary back links.

Workflow errors on New Hire and Add employment instance

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow...

Trending in this Blog this month