What is SQL and why you need SQL? | Type of SQL | Introduction to SQL | Structured Query Language (SQL) Example - Quick Guide.

SQL - Structured Query Language. 

COBOL+DB2 application program uses SQL statements. These SQL statements are used to select, insert, update, and delete data from the DB2 tables. These SQL statements must be embedded into the host language programs such as COBOL, JAVA, or REXX.

Welcome to today's session on "Structure Query Language (SQL)". In this session, discover what is SQL statements and why you need them in your COBOL+DB2 application programs. Learn why you need DB2 pre-compilation for COBOL+DB2 programs. So, without wasting any time let's get started with today's SQL tutorial. 

 Agenda. 

• What is Data and why it is important?
• What is SQL and why you need it?
• Features of SQL ( ie. Structure Query Language).
• What is the difference between SQL and Host language? 
• What are the different types of SQL statements?
• SQL statements examples. 

What is Data and why it is important?

Data is a collection of facts, that computers can understand and process. So, when you book your flight or pay utility bills, or share photographs on the social media platform. 

Your action generates tons of data. Different storage mediums such as databases, file systems, etc. store generated data for processing. Large-scale enterprise applications generally use the database to store this valuable information. 

Proper storage and analysis of data not only improve the process but also boost profit. 

Now, the question is how to store and access data from the database. The answer is SQL or structured query language. 

An Overview of SQL.

The term SQL stands for "Structured Query Language". it's popularly known as "SQL" or "SEQUEL" or “ESS-CUE-EL”. SQL is a powerful data manipulation tool that is supported by all the leading RDBMS products such as DB2, MS SQL Server, Oracle, MYSQL, etc.

"SQL is the language that communicates with the database." 

The programmer uses SQL statements to specifies what data to be fetched or updated, but does not specifies how to perform the operation. DBMS parse and analyze the SQL statement to find the optimal path to retrieve data from the tables. 

Features of SQL.

SQL is not just a query language but it's the complete package in itself. The structured query language (SQL) provides all most all features of a high-level programming language such as JAVA, COBOL, REXX, C++, etc. In fact, users can easily manipulate data without writing a huge chunk of code. Following are a few salient features of SQL. 

• SQL is simple and easy to use. 
• SQL is flexible and portable. 
• SQL statements are used to retrieve data instead of lengthy code. 
• SQL is a free-form structure. 

What is the difference between SQL and Host language?

A high-level programming language such as COBOL, JAVA, REXX, etc. is designed to processing one record-at-a-time. However, SQL is designed to process a set of records at-a-time. In set-level processing, all operations applied on the set and the output is a set of records. But, sometimes the result set can be an empty or single row. 

Host languages such as COBOL, JAVA, REXX, etc. can not handle many records in a single request. Thus, in the COBOL-DB2 or JAVA-DB2 application, you need a DB2 cursor and host variables to process many records.

SQL vs Host language.

Let's talk about the different types of SQL statements. 

What are the different types of SQL statements?

There are no specific criteria for categorizing SQL statements. SQL statements are generally categorized into two categories.

    - SQL Functionality.

    - SQL Execution.

The two categorized further sub-divided into many categories. Let's look at each of them one by one. 

Based on the Functionality the SQL statements are of three types: 

1. Data Control Language (DCL): Control the user access to data. SQL statements such as Grant/Revok are used to grant or revoke user access to the data. 
2. Data Manipulation Language (DML): Maniulupate data stored in the database tables. SQL statements such as Select, Insert, Update, and Delete is used to perform various data manipulation operations. 
3. Data Definition Language (DDL): Create data objects such as tables, views, etc. SQL statements such as Create table, Drop table is used to create database objects.

Type of SQL Statements.

Based on the Execution the SQL statements are of two types:

1. Static SQL or Embedded SQL statements: These SQL statements are hardcoded into application programs such as COBOL+DB2.  
2. Dynamic SQL statements: These SQL statements are prepared by the application program during the run-time.    

SQL statements examples.

Following are some SQL statement examples. 

 

 

What is SQL and why you need SQL?

This SQL tutorial is linked to a YouTube video for better understanding. Please do visit and consider subscribing to my channel. 

Conclusion. 

In this SQL tutorial, you'll why is SQL and why you need it. You also learn the basics of SQL, type of SQL statements based on functionality and execution. Followed by SQL examples for better understanding. 

►Subscribe to Topictrick & Don't forget to press THE BELL ICON to never miss any updates. Also, Please visit below mention the link below to stay connected with Topictrick and the Mainframe forum on - 

► Youtube

► Follow us on Twitter  

► Facebook 

► Linkedin

► Reddit

► Mainframe Blog

► Medium Blog

Thank you for your support. 

Mainframe Forum™

Enable GingerCannot connect to Ginger Check your internet connection
or reload the browserDisable in this text fieldRephraseRephrase current sentenceEdit in Ginger×

Db2 Request to SQL: Translate Business Rules into SELECT Statements



Translate the request before writing the SELECT.

A request like "show active accounts with balances over 10,000" is not SQL yet. Before a COBOL program can use it in embedded SQL, the developer must identify the result columns, source tables, join rules, predicates, ordering, and host variables. Most bad SELECT statements start with a vague request that was never translated into database terms.

This refresh keeps the original beginner intent but rewrites it for Db2 and mainframe developers. The goal is not SQL tuning; the goal is turning a business question into a precise, testable SQL statement.

Start With the Business Request

Write the request in one sentence and remove soft wording. Replace words like "current," "recent," "large," or "active" with the actual rule used by the application.

Vague wording	Database rule
current customers	CUST_STATUS = 'A'
recent payments	PAYMENT_DATE >= :WS-FROM-DATE
large balances	BALANCE > 10000
open accounts	CLOSE_DATE IS NULL

Identify the Columns to Return

The SELECT list should contain the columns the program actually needs. If the report needs account number, customer name, and balance, name those columns. Do not use SELECT * in production COBOL cursors unless there is a rare, documented reason.

SELECT A.ACCT_NO,
       C.CUST_NAME,
       A.BALANCE

Choosing only the needed columns reduces data movement and keeps the COBOL host variable list clear.

Find the Source Tables

Next, map each result column to a table. If columns come from more than one table, write the join rule before writing the final query.

Needed value	Column	Table
Account number	ACCT_NO	ACCT_BAL
Balance	BALANCE	ACCT_BAL
Customer name	CUST_NAME	CUSTOMER
Customer status	CUST_STATUS	CUSTOMER

Add Join Rules

A join rule explains how rows from two tables match. Do not rely on column names that merely look similar. Confirm the relationship from the data model, DCLGEN copybooks, or existing tested SQL.

FROM ACCT_BAL A
JOIN CUSTOMER C
  ON C.CUST_NO = A.CUST_NO

If the relationship is optional, decide whether the query needs an inner join or a left join. A wrong join type can silently drop rows.

Convert Conditions into Predicates

Conditions become the WHERE clause. Each condition should have a column, operator, and value or host variable.

WHERE C.CUST_STATUS = 'A'
  AND A.BALANCE > :WS-MIN-BALANCE
  AND A.BRANCH_ID = :WS-BRANCH-ID

Use host variables for values that change at runtime. Match host variable definitions to Db2 column types. The related DB2 Host Variables and Structures article covers that copybook work.

Write the Final SELECT

After the request is translated into columns, tables, joins, and predicates, the final SQL is much easier to review.

SELECT A.ACCT_NO,
       C.CUST_NAME,
       A.BALANCE
  FROM ACCT_BAL A
  JOIN CUSTOMER C
    ON C.CUST_NO = A.CUST_NO
 WHERE C.CUST_STATUS = 'A'
   AND A.BALANCE > :WS-MIN-BALANCE
   AND A.BRANCH_ID = :WS-BRANCH-ID
 ORDER BY A.BALANCE DESC
 FETCH FIRST 100 ROWS ONLY

The SQL now says exactly what data is needed, where it comes from, how rows match, and which rows qualify.

COBOL Cursor Pattern

In COBOL, the translated SQL often becomes a cursor. Keep the cursor declaration close to the host variable definitions during review.

EXEC SQL
    DECLARE C-ACCT CURSOR FOR
    SELECT A.ACCT_NO,
           C.CUST_NAME,
           A.BALANCE
      FROM ACCT_BAL A
      JOIN CUSTOMER C
        ON C.CUST_NO = A.CUST_NO
     WHERE C.CUST_STATUS = 'A'
       AND A.BALANCE > :WS-MIN-BALANCE
       AND A.BRANCH_ID = :WS-BRANCH-ID
END-EXEC.

Check the SQLCODE after every OPEN, FETCH, and CLOSE. A correct SELECT still needs correct cursor handling.

Review Before Tuning

Before tuning a query, confirm it answers the right question. Then check access path and indexes. The companion Db2 SQL Optimization Tips for COBOL Programs guide covers EXPLAIN, RUNSTATS, and predicate checks.

• Are all selected columns required by the program?
• Are joins based on real relationships?
• Are date and status rules stated precisely?
• Are host variables typed correctly?
• Does the query need ordering or row limiting?

FAQ

What is the first step in translating a request into SQL?

Rewrite the business request as exact data rules: result columns, source tables, join relationships, and predicates.

Should I use SELECT * while translating a request?

No for production COBOL SQL. Name only the columns the program needs so host variables, performance, and review remain clear.

When should I tune the SQL?

Tune after the query answers the right business question. Then use EXPLAIN, current statistics, and index checks to review the access path.

A good SELECT starts as a precise requirement. Once the columns, tables, joins, and predicates are clear, the SQL is easier to test, bind, and maintain.