COBOL | Using the SQL statement table for troubleshooting
A. COBOL Overview
Although COBOL in PeopleSoft is a massive and challenging topic on its own, we can make life slightly easier by concentrating on one element only: the use of the SQL statement table (PS_SQLSTMT_TBL) table to store embedded SQL required for COBOL programs.
For those unfamiliar with the language, COBOL is an old-school procedural language. This means that all programs are (1) written in a plain text editor external to the PeopleSoft system, and (2) compiled using a dedicated COBOL compiler. Because the native COBOL language has no concept of a database, it uses a feature known as ‘embedded SQL’ to connect to the PeopleSoft database. Most commonly, this takes the form of a static SQL statement such as a select, insert, update or delete. In rarer cases, the COBOL builds the SQL dynamically itself, and then connect to the database to execute the statement.
B. Stored SQL Statements
In the case of the static SQL statements, these are not stored in the COBOL source files but in a database table: PS_SQLSTMT_TBL. This means that if you are troubleshooting a COBOL issue, you may not even have to look at the COBOL source code at all. If you are fortunate to receive a quasi-meaningful message from a problematic COBOL (this could be an online message or an entry in a trace file), you might see a funny string of characters such as the following:
SFPORGPY_U_ITEM_SF
This string is a reference to a stored SQL statement. The SQL queries are stored in PS_SQLSTMT_TBL under Program Name (PGM_NAME), Statement Type (STMT_TYPE: ‘S’ for Select, ‘U’ for Update, ‘I’ for Insert and ‘D’ for Delete), and Statement Name (STMT_NAME). These three parts are generally combined into one and presented as such in error messages. So to check the exact SQL statement used by the COBOL program, separate the three elements and run the following statement:
select * from ps_sqlstmt_tbl where pgm_name = 'SFPORGPY' and stmt_type = 'U' and stmt_name = 'ITEM_SF';
Once you’ve found the relevant statement in SQLSTMT_TBL, you can analyse and possibly even run the SQL as a standalone query. This will hopefully give you a clue as to what the problem is. For instance, if the COBOL errors on an SQL ‘Insert’, you can run the ‘Insert’ separately and discover that it fails to due to a duplicate key or a missing field. It goes without saying that this sort of analysis is best performed in a non-Production database.
Of course, given this is COBOL we’re talking about here, it’s best not to get your hopes up too high when troubleshooting the SQL statement table alone. Sometimes you might discover that the SQL runs perfectly fine as an independent statement, but still errors when run as part of the COBOL. This is where you need to troubleshoot the COBOL source code in more detail, but this is a topic for another time…
C. COBOL DMS File
Finally, while on the subject of the stored SQL statement table, most COBOL programs in PeopleSoft comes with their own matching DMS file. If you have a look in the /src/cbl directory of the App Server ‘home’ directory, you will see a list of file pairings such as these:
The PeopleSoft convention is for the COBOL source file to be saved in upper case letters, while the DMS file is saved in all lower case letters.
The DMS file is a complete list of all stored SQL statements used by the COBOL. These can be loaded into the system via Data Mover. By running the DMS file for a COBOL, you will therefore update the PS_SQLSTMT_TBL with the entire list of SQL statements for the one COBOL. This task is usually carried out after a major upgrade. There’s a good chance that at least one of the SQL statements has been changed by the upgrade, so the stored SQL statement should be updated to match the latest version of the COBOL source files. Therefore, if you are getting errors from a COBOL along the lines of a missing or invalid SQL statement, try running the latest DMS files into the system via Data Mover.
See Also:
Tip 19: Data Mover Scripts