Intellisense with Db2 SQL COBOL Statements in VSCode and Theia
Have you wished for coding assistance for SQL statements in COBOL similar to other languages like Java as part of your modern IDE? If yes, then be aware that it is part of COBOL LS[1] now. Broadcom’s COBOL Language Support extension is enhanced to support writing SQL code in COBOL programs. In this article I will demonstrate how easy it is to write SQL statements in COBOL programs using VS Code and Theia Editors.
Db2 SQL Support in COBOL Programming Language
SQL, a fourth generation language, is a standardized programming language for defining and manipulating data in a relational database. The ability to easily write sqlcode in your COBOL programs is a great help for developers, especially when editing the code using a modern IDE like VS Code or Eclipse Che and the Broadcom Code4z extension pack. Code4z already has CICS EXEC statements support but now it is extended to bring support for EXEC SQL statements (based on IBM Db2 database dialect[4]). It provides all the language-smartness features like code-snippets, goto-definition, coloring support, code completion, highlighting syntax or semantic errors related to your Db2 SQL statements.
Let’s dig deeper into using SQL with Db2 tables as part of your COBOL application and how the LSP extension can improve the edit experience.
Db2 Application Programming
Rules to be followed while writing Db2 SQL statements in the COBOL program.
- Each Db2 SQL statement in COBOL must run between
- SQL statements must be coded in Area B.
- All the tables that are used in a program must be declared in the Working-Storage Section. This is done by using the INCLUDE statement.
- All SQL statements other than INCLUDE and DECLARE TABLE must appear in the Procedure Division
Db2 SQL Support in VS Code Editor — Traditional UI
In your VS Code extension tab, search for Code4z and install the extension pack. Create a new file with .cbl or .cob extension or open an existing COBOL file, and start using language aware features during SQL statements coding.
Code Snippets
To write COBOL, Db2-SQL or CICS statements faster, Code4z extension provides several code snippets (shell, exec-sql, exec-sql-include, exec-cics, function-cos, etc.) which appear in IntelliSense (Ctrl+Space) mixed with other code suggestions (keywords, functions, etc.). You may also define your own code snippets as shown here.
SQL INCLUDE
This statement inserts application code, including declarations and statements, into a source program when the program is compiled or pre-compiled. We can use it to insert SQLCA, SQLDA (required for execution of the SQL DESCRIBE statement) or other member-name (like copybooks but for SQL usage).
Each host language (COBOL, REXX, etc.) provides a mechanism for handling diagnostic information. SQLCA is a SQL communication area which is used to check whether execution of SQL statements run successfully or failed. Errors and warning conditions can be checked by using SQLCA fields like SQLCODE and SQLSTATE in a host language. SQLCA fields are updated after each SQL statement executes.
In the below example, include statement is used to define a table-definition via a member-name ABC1 (can be either local or remote Db2 copybook). If it is a remote member then it is automatically downloaded once you configure the path for “Default list of datasets to search for copybooks” field in User Settings.
Host Variables
Host variables are COBOL language variables declared in the Working-Storage Section that are referenced within SQL statements. They must be declared for all values that are to be passed between the COBOL program and Db2. They are used for receiving data from a table or inserting data in a table. Host variables cannot be group items, but they may be grouped together in host structure. They cannot be Renamed or Redefined. Using host variables with SQL statements, prefix them with a colon (:)
Data Definition Language (DDL) Statements
DDL deals with descriptions of the database schema and used to create and modify the structure of database objects (TABLE, COLUMN, VIEW, SEQUENCE, INDEX, PROCEDURE, etc.) using one of SQL commands (CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME).
Data Query Language (DQL) Statements
A database query (starts with SELECT) is a request for a database’s data so we can retrieve or manipulate it using different combinations of several commands and scalar functions (AND, OR, NULL, IN, BETWEEN, LIKE, UNION, JOIN, ORDER BY, HAVING, AVG, SUM, COUNT, etc.). E.g.
or an example of more complex query
Data Manipulation Language (DML) Statements
DML is used to manipulate data in databases using commands: INSERT, UPDATE, DELETE. E.g.
Transaction Control Language (TCL) Statements
TCL manages the transactions of changes made by DML statements. COMMIT command permanently saves the changes made to the databases. ROLLBACK command brings back the database to the last committed point, and it is also used with the SAVEPOINT command to jump to the savepoint in an ongoing transaction.
Data Control Language (DCL) Statements
DCL deals with the rights, permissions and other controls of the database system using commands: GRANT (gives privilege) and REVOKE (withdraws privilege). E.g.[4]
Db2 SQL Support in Theia Editor — Cloud Web IDE
Eclipse Che [6] is the world’s first Kubernetes-based, next-generation container development platform, developer workspace server and cloud-native Web IDE. It provides a default Web IDE based on the Eclipse Theia project. Our COBOL extension, che4z, is tailored to be used in the Theia Editor via a Che devfile (YAML file), which is used to create a workspace with all required plugins.
What is Next?
In the previous series of my blogs, I have described the power of the LSP and DAP Architecture, about how these new specifications can be leveraged to deliver a user friendly, consistent experience to developers working on mainframe code regardless of choice of IDE.
To continue this tradition, in the next series of my blogs, I intend to mention about another complementary tool — ANTLR (ANother Tool for Language Recognition) which is used to generate language-parsers using our language-grammars (COBOL, REXX, Db2 SQL, etc.). It will be another deep-technical-dive experience like in article Multithreading in Java vs NodeJS.
For further questions and feedback you may contact us on Slack: che4z.slack.com
To report an issue or suggest an improvement use our GitHub-Issues
References
[1] https://marketplace.visualstudio.com/items?itemName=broadcomMFD.cobol-language-support
[5] https://projects.eclipse.org/projects/technology.lsp4j
[6] https://www.eclipse.org/che/
[7] https://www.tutorialspoint.com/cobol/cobol_database_interface.htm
