Wednesday, 31 August 2022

COBOL DB2 Multi-Row fetch | DB2 row set positioning | Multi-row fetch example in COBOL.

Are you still using the DB2 cursor to fetch a single row at a time? If yes, then consider upgrading application logic to fetch multiple rows instead of a single row at a time. DB2 cursor multi-row fetch technique retrieves multiple rows at one time. This improves application performance and lowers COBOL+Db2 application running costs. 

In this multi-row fetch tutorial, you'll learn the basics of DB2 rowset positioning (i.e. DB2 multi-row fetch technique). You'll learn how to fetch and process multiple rows (i.e. Multi-row fetch) in your COBOL+DB2 applications by using a single fetch statement. 

Agenda. 

  • Introduction.
  • What is a DB2 Cursor?
  • How to fetch multi-row in the COBOL DB2 program?
  • What is the DB2 rowset positioning?
  • How does DB2 multi-row fetch work?
  • Benefits of Multi-row fetch.
  • Youtube: COBOL+DB2 Multi-Row Fetch Tutorial Video.
  • Summary.

Introduction.

A COBOL-DB2 application program either use the DB2 cursor or singleton select statements to access data from the DB2 database. However, the decision of using cursor or SQL statement depends on business requirements and technical feasibility. 

In the COBOL-DB2 program, the cursor is the preferred option to navigate through the set of rows returned by the SQL statement. You can use DB2 cursors to read or update data into the DB2 database. Most of the program logic still use cursor single-row fetch facility instead of multi-row fetch. This leads to poor application performance and high application running costs and the Mainframe is all about cost!
 

What is a DB2 Cursor?

DB2 cursor was designed to bridge the gap between SQL and the host language (i.e. COBOL, JAVA, C++, etc) processing. These programming languages are capable of processing single records at a time but SQL operates on a set of rows. Without using the DB2 cursor it would be absolutely impossible for the COBOL program to navigate through the set of rows.

Thus, a cursor in DB2 is a mechanism by which you access data row-by-row from the result set. Cursors in DB2 keep track of records in the results set, and with the help of cursors, you can update, delete, or select individual rows from the table.

How to fetch multi-row in the COBOL DB2 Program?

Over the past few years, DB2 has evolved dramatically. In the beginning, the cursor could fetch a single row, but multi-row fetch functionality was added in DB2 z/OS version 8. DB2 multi-row fetch capability enables programmers to fetch multiple rows with a single fetch statement. 

This reduces the number of I/O operations with the database and improves the performance of the COBOL DB2 application. Lastly, DB2 multi-row fetch capability help in reducing application running cost. 

Now let's try understanding how to include the multiple-row fetch technique in your COBOL DB2 programs. There are two ways to include the multi-row fetch technique in COBOL+DB2 applications. 
  • Multi-row with serial or normal Cursor.
  • Cursor with Rowset positioning.  
Let's discuss each DB2 multi-row fetch technique with an example.

What is the DB2 rowset positioning?

Essentially, a rowset is a grouping of rows that are processed together. Cursor rowset positioning enables you to fetch multiple rows (i.e. set or group) instead of a single row by using a single fetch statement.

If you fetch multiple rows at once, your request becomes more efficient, especially for distributed requests. A single fetch call can return up to 32 767 rows. The cursor will be positioned on the last row retrieved after retrieving the data.

How does DB2 multi-row fetch work?

You can use the FETCH statement with both serial and scrollable cursors. A cursor for a multiple-row FETCH is defined, opened, and closed in the same manner as for a single row FETCH. There is only one change, the FETCH statement, which specifies the number of rows to get and the storage that contains them.

You can define the storage location where rows are placed in two different ways:
  • As part of a host structure array.
  • As part of a row storage area with an associated descriptor.
Let's take a look at each technique one by one.

Example 1: Fetch Multi-row with the simple cursor. 

In order to use a multi-row fetch with the simple cursor, you must define the appropriate structures. Therefore, an array of host variables must be defined into which the rows are put. There will be one variable array for each column fetched, where its values will be placed. Make sure the array size matches the rowset size.

In the following example, 

 IDENTIFICATION DIVISION. 
 WORKING-STORAGE SECTION. 
*
  01 WS-TBL-DPT.
     02 HDEPT         OCCURS 10 TIMES. 
        05 HEMPNO            PIC X(6). 
        05 HLASTNAME. 
           49 HLASTNAME-LEN  PIC S9(4) BINARY. 
           49 HLASTNAME-TEXT PIC X(15). 
        05 HWORKDEPT         PIC X(03). 
        05 HJOB              PIC X(08). 
  01 WS-TBL-IND. 
     02 HIND-ARRAY     OCCURS 10 TIMES. 
        05 HINDS             PIC S9(04) BINARY OCCURS 4   
                                 TIMES.
*
  EXEC SQL
     DECLARE CRSAL CURSOR FOR
        SELECT EMPNO, COMM, SALARY, DEPT, JOB
          FROM EMPLY 
         WHERE DEPT = “CS”
  END-EXEC.
  …..
  …..
  EXEC SQL
     FETCH CRSAL FOR 10 ROWS 
      INTO :HDEPT 
           :HIND-ARRAY 
  END-EXEC.


Example 2: Fetch Multi-row with Cursor Row-set positing.

 IDENTIFICATION DIVISION. 
 WORKING-STORAGE SECTION. 
*

 01 WS-TBL-DPT.
    02 HDEPT         OCCURS 10 TIMES. 
       05 HEMPNO            PIC X(6). 
       05 HLASTNAME. 
          49 HLASTNAME-LEN  PIC S9(4) BINARY. 
          49 HLASTNAME-TEXT PIC X(15). 
       05 HWORKDEPT         PIC X(03). 
       05 HJOB              PIC X(08). 
 01 WS-TBL-IND. 
    02 HIND-ARRAY     OCCURS 10 TIMES. 
       05 HINDS             PIC S9(04) BINARY OCCURS 4   
                                TIMES.

 EXEC SQL
     DECLARE CRSAL CURSOR WITH ROWSET POSITIONING FOR
        SELECT EMPNO, COMM, SALARY, DEPT, JOB
          FROM EMPLY 
         WHERE DEPT = “CS”
 END-EXEC.
 …..
 …..
 EXEC SQL
     FETCH CRSAL FOR 10 ROWS 
      INTO :HDEPT 
           :HIND-ARRAY 
 END-EXEC.
 

Benefits of Multi-row fetch.

DB2 z/OS Version 8 introduced the multi-row fetch capability. Since then, it's been very useful, and it would definitely help reduce costs. The following are the benefits of DB2 Multi-row fetch capability.
  • DB2 application programs that access a lot of data can benefit from multi-row FETCH techniques. 
  • Reducing the number of statements issued between your program's address space and DB2. Thus, reducing the CPU consumption. 
Testing has shown that multi-row FETCH reduces CPU time by up to 50%. Multi-row FETCH works best at about 100 rows. Consequently, the percentage improvement will decrease as the number of rows fetched per call decreases.   

Youtube Video: Watch COBOL DB2 Multi-Row Fetch Tutorial.



Youtube: COBOL DB2 Multi-Row Fetch Tutorial. 

Summary.

Finally, this marks an end to the COBOL DB2 Multi-ROW Fetch or DB2 rowset positioning. In this session, we discussed what is multi-row fetch capability and how you can use Multi-row fetch capability in the COBOL DB2 programs. Do check out COBOL LEVEL 88 Condition.


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

► Youtube
► Facebook 
► Reddit

Thank you for your support. 
Mainframe Forum™

Thursday, 15 April 2021

COBOL interview questions | MF COBOL Interview Questions 2021 | [Best COBOL interview questions].

COBOL Interview Questions, Best COBOL Interview Questions

COBOL Interview Questions.


Welcome back to today's session on "COBOL Interview Questions". In this, interview question you'll go through different MF COBOL Interview questions and answers that cover different topics from COBOL. Let's get started with the best COBOL interview questions.    


What is the initialize verb in COBOL and what is the difference between initialize and MOVE spaces in COBOL?

Initialize verb in COBOL is used to initialize the variables with the default value (i.e. SPACE or ZEROES). An INITIALIZE statement is functionally equivalent to one or more MOVE statements.

Generally, the COBOL INITIALIZE statement is used to initialize all the group data items without any hassle. However, it is unproductive to initialize an entire group unless you require all the items in the group to be initialized.
  • Alphabetic, Alphanumeric fields & alphanumeric edited variables are set to SPACES.
  • Numeric, Numeric edited items set to ZERO.
  • FILLER, OCCURS DEPENDING ON items left intact.

What is the difference between levels 66, 77, and 88 and what are they used for?

Well, Levels 66, 77, and 88 are functionally different and are used for different purposes. 
  • Level 66 is a RENAME Clause and is used for rename items. 
  • Level 77 is an elementary level item. It cannot have group items. 
  • Level 88 is used to specify the condition name. 

What is the difference between index and subscript in COBOL?

The subscript and index in COBOL are used to access items from the COBOL array/table. The subscript in COBOL refers to the array occurrence. However, an index in COBOL is the displacement of an item from the beginning of the array. An index in COBOL can only be changed by using COBOL PERFORM statement, COBOL SEARCH, and SET statement in COBOL.

You must always use an index for a table to use COBOL SEARCH or COBOL SEARCH ALL statements.

What is the difference between SEARCH and SEARCH ALL in COBOL?

The COBOL SEARCH and COBOL SEARCH ALL are used to search data within the table/array. COBOL SEARCH is a serial search. However, COBOL SEARCH ALL is a binary search and it is faster and more efficient as compared to SEARCH. The data in table/array should be sorted either in ASCENDING/DESCENDING before using COBOL SEARCH ALL.

What should be the sorting order for COBOL SEARCH ALL?

The data in the COBOL array/table should be sorted in a specific sequence i.e. either ASCENDING or DESCENDING. The default data sorting order is ASCENDING. If you need to search the data in descending order, you must use DESCENDING KEY Clause while declaring the array.

What is the basic difference between performing a SECTION and a PARAGRAPH in COBOL?

The COBOL PERFORM statement is used to execute a business logic defined in the paragraph or section. Performing a SECTION means executing a group of paragraphs within the section. However, performing a PARAGRAPH means executing a particular paragraph logic.

What is the COBOL EVALUATE statement and why it is recommended over the COBOL IF/ELSE statement?

The COBOL Evaluate statement is like an excel case statement and can be used instead of nested Ifs statements. The main difference between EVALUATE in COBOL and case is that no 'break' is required for EVALUATE i.e. control comes out of the EVALUATE as soon as criteria are satisfied.

You can use the EVALUATE statement instead of a series of nested IF statements to test several conditions and specify a different action for each. It is always recommended to use COBOL Evaluate instead of nested Ifs. 

What is the difference between COBOL CONTINUE and NEXT COBOL SENTENCE Statement?

In general, both the COBOL CONTINUE statement and NEXT SENTENCE in COBOL are used to transfer control to the next executable statement. But, there is a difference in the way control is transferred. The NEXT SENTENCE transfers control to the next executable statement after a period ".", but the CONTINUE statement transfers control after the explicit scope terminator such as end-if, end-evaluate, etc.

Is it possible to REDEFINE an X(50) field with a field of X(100)?

Yes, it is possible. COBOL REDEFINE statement is used to define the same field in two or more ways. It means, that definition and the redefinition of both fields refer same storage.

01 WS-FNAME       PIC X(50).
01 WS-EMP-NAME  REDEFINES WS-FNAME  PIC X(100).

What is the difference between COMP & COMP-3 and how is sign stored in COMP-3 fields?

Both COMP and COMP-3 in COBOL are used to define the storage format of numerical fields. COMP in COBOL represents the binary format while COMP-3 in COBOL represents the packed decimal format. In COMP-3 the sign is stored in the last nibble. 

COBOL Interview Questions and Answers!

 

Conclusion. 

Finally, this marks an end to the COBOL Interview Questions and answers. These interview questions are designed for both experienced and fresher. With the help of these COBOL interview Questions, you can easily clear your interview questions. Do check out COBOL LEVEL 88 Condition.


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

► Youtube
► Facebook 
► Reddit

Thank you for your support. 
Mainframe Forum™

Saturday, 10 April 2021

COBOL Interview Questions | 10 Incredible COBOL Interview Questions and Answers [COBOL Interview]

COBOL Interview Questions, COBOL Interview Questions and Answers

COBOL Interview Questions.


Welcome back to the "COBOL Interview Question" session. In this session, you'll go through COBOL interview questions and answers from different COBOL topics. These top 10 COBOL interview questions also include scenario-based questions. Let's get started with Introduction to COBOL. 


Introduction to COBOL. 

The term COBOL stands for COmmon Business Oriented Language. COBOL is one of the oldest and mature programming languages. COBOL is a robust, simple, and powerful programming language. COBOL has a proven track record of 60 years and COBOL still dominates enterprise computing. 

Now, let's get started with the top 10 COBOL Interview Questions and Answers.  

What is the difference between COBOL COPY and COBOL CALL Statements?

  • The COPY statement is used to bring into a program a source member consisting of a series of prewritten COBOL statements.
  • The CALL statement transfers control from one program to another. The main program containing the CALL statement that references or calls a program is referred to as the calling program.

What is the purpose of the PROGRAM-ID and in which division it is defined?

  • PROGRAM-ID parameter is defined in the IDENTIFICATION DIVISION.
  • It is used to specify the name of the COBOL program. It can consist of 1 to 30 characters.

How many divisions does a COBOL Program contain? Can you name each division?

A COBOL program has four divisions:
  • Identification Division.
  • Environment Division.
  • Data Division.
  • Procedure Division.

What is the difference between a level 77 item and a level 01 item with no subordinate levels?

Well, The two items are functionally the same. Both are elementary level items if the 01 level does not have any subordinate items (i.e. Group fields). If 01 is defined as a group field then it would be different from level 77. Another difference is that an 01 level is aligned on a double word boundary, whereas a 77 level is unaligned. 
  • 01 EMP-NAME       PIC X(35).
  • 77 EMP-ADD        PIC X(40).

When using Declarative, can you perform another paragraph with the declarative area?

Yes, you can have a common error paragraph within the declarative that is executed. However, you must not Perform any paragraph outside the declarative.

Which statement is coded to update the record in a sequential file? What has specified with the statement: the record description or the file name?

The COBOL REWRITE Statement is used to update the record. The record description is specified in the REWRITE statement, instead of the file name.

REWRITE in COBOL Example. 

REWRITE EMP-REC FROM WS-EMP-REC
END-REWRITE.

Is the At End clause valid when issuing a Random Read?

No. You cannot use the "At End Clause" because you are reading records directly via key. You'll never have an end-of-file condition in such a scenario. However, you should code Invalid Key Clause to handle "record not found" - File status 23.

COBOL REWRITE Example. 

READ EMP-REC INTO WS-EMP-REC
     INVALID KEY
             DISPLAY “EMP REC NOT FND"
             DISPLAY "FLE ST CDE:“ WS-STAT-CDE
END-READ.


If a file is defined as optional and it doesn’t exist, which file status is returned if the file is opened Input? What about Output?

The input file status code would be 05. An OPEN statement was successfully executed, but the referenced optional file was unavailable at the time the OPEN statement was executed. The output file status code would 00.

COBOL OPEN Statement Example: 

READ EMP-REC INTO WS-EMP-REC 
     INVALID KEY 
         DISPLAY 'EMP REC NOT FND'
END-READ.

In which mode must you open a file to update the record in the file?

If you want to update data in the file, then it should be opened in I-O mode (i.e. Input-Output Mode). Make sure you read the record from the file before updating the record in the file. 
 
COBOL OPEN Statement Example: 

 OPEN I-O EMPFILE.

How do you read records from the file and what must be done before you can read records from a file?

The record from the file is read by using the COBOL READ statement. You should open the input file in the desired mode by using the COBOL OPEN Statement before reading records from the file. 

COBOL OPEN Statement Example: 

 OPEN INPUT EMPFILE.

COBOL Interview Question - You tube.



Conclusion. 

Finally, this marks an end to the COBOL Interview Questions and answers. These interview questions are designed for both experienced and fresher. With the help of these COBOL interview Questions, you can easily clear your interview questions. Do check out COBOL LEVEL 88 Condition.


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

► Youtube
► Facebook 
► Reddit

Thank you for your support. 
Mainframe Forum™