Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
Database Articles
Page 88 of 546
How to bind multiple versions of a DB2 program into a package?
Packages are DB2 database objects which hold the executable forms of SQLs which are used in COBOL-DB2 programs. The packages are stored in a catalog table and contain the strategy/tables/columns/predicate associated with the SQL statements.If there is a DB2 table ORDERS_TEST in test environment and ORDERS_PROD in production environment, then we need two versions of COBOL-DB2 program (which will access these tables) — one for test and the other for production.Although both programs will be carbon copy of each other, the only difference lies in the SQL statements. The test version of the program will use table ORDERS_TEST in SQL ...
Read MoreWhat will be the result if there is a timestamp mismatch in the DBRM and Load module?
When the COBOL-DB2 source code is given as an input in the pre-compilation stage, we get 2 important components — DBRM and modified source code.In modified source code, the SQL statements are replaced by COBOL calls and the DBRM contains all the SQL statements which are present in the COBOL-DB2 program. The pre-compiler inserts the timestamp in both DBRM and modified source code.In case of DBRM bind directly to the plan, the system compares the timestamp of the DBRM and load module when the COBOL-DB2 program is executed. In case there is a mismatch in the timestamp the JCL step ...
Read MoreExplain the concept of integrity in DB2 along with types of integrity
Problem: What is INTEGRITY in a DB2? Explain DOMAIN, ENTITY and REFERENTIAL integrity with the help of an example in ORDERS table.SolutionThe integrity refers to the accuracy, consistency and correctness of data present in the DB2 database. Data integrity is imposed during the database design to make sure that data residing in the database remains complete, accurate and reliable.There are three types of integrity described in below figure.Domain integrityIt ensures that column data in the DB2 table adhere to the permissible set of values. For example, in the ORDERS table, the domain integrity on the ORDER_DATE column ensures that data ...
Read MoreWrite a DB2 query to find out all the duplicate INVOICE_ID in ORDERS DB2 table?
The duplicate INVOICE_ID in ORDERS DB2 table can be found by using a combination of GROUP BY with HAVING clause and COUNT function. The GROUP BY will enable us to group the result invoice wise and COUNT function will enable us to count the number of occurrences in each group. We can use predicate in HAVING clause to filter the results for groups having count greater than one.Below is the SQL query which we can use to find duplicate invoices in ORDERS DB2 table.ExampleSELECT INVOICE_ID FROM ORDERS GROUP BY INVOICE_ID HAVING COUNT(INVOICE_ID) > 1For example, if we have ...
Read MoreExample of SQL query describing COUNT function and GROUP BY
Problem: Write a query on TRANSACTIONS DB2 table to list down the number of orders (ORDER_ID) assigned to a particular transaction (TRANSACTION_ID).SolutionWe can use the below query to find out the number of orders assigned to a particular transaction id on TRANSACTIONS DB2 table.ExampleSELECT TRANSACTION_ID, COUNT(ORDER_ID) FROM TRANSACTIONS GROUP BY TRANSACTION_IDWe will use GROUP BY function on the ORDER_ID to fetch the result order wise. The COUNT function will count the number of orders. For example, we have below DB2 ORDERS table.TRANSACTION_IDORDER_IDIRN22345A23118IRN22345A45901IRN22345A67990IRN56902A23119IRN99781A67921IRN56902A23167 The result of our DB2 query will return the below result.TRANSACTION_IDCOUNT(ORDER_ID)IRN223453IRN569022IRN997811
Read MoreWhat is -551 error code in DB2? How will you resolve it?
When we get -551 in the SQLCODE then there is some privilege level issue. It signifies that the user does not have access to the database/tablespace/view/table that he is trying to access. As per the IBM documentation -551 SQLCODE states that.Example-551 auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation ON OBJECT object-nameThere are DCL (Data control language) statements which are used by DBAs in order to control the access on DB2 objects. We can raise a request with DBA to provide access to the particular object for which user is getting -551 SQLCODE.Following DCL statement will give access ...
Read MoreWhat are QMF and SPUFI? What is the difference between them for accessing DB2 tables?
Both QMF and SPUFI are tools built to access DB2 databases in a mainframe environment. Using these tools, we can SELECT, UPDATE and DELETE the data from the DB2 database. QMF stands for Query Management Facility and SPUFI stands for SQL Processor Using File Input.Below are the differences between QMF and SPUFI.SPUFI is a free tool which comes with mainframe DB2 installation, while QMF is a separate licensed toolQMF is an interactive tool which can be used for reporting and formatting purposes, but in case of SPUFI, we need to write the SQL queries in the mainframe dataset and the ...
Read MoreWhat is the purpose of OPTIMIZE FOR ROWS in DB2 SQLs? How is it useful?
The OPTIMIZE FOR N ROWS is a DB2 clause which we can add in the query to give priority for the retrieval of the first few rows only. This clause will enable the optimizer to choose the access path that minimizes the response time for fetching first few rows.The OPTIMIZE FOR N ROWS clause is not effective on SELECT DISTINCT and COUNT function because DB2 will need the entire qualifying rows in order to fetch the DISTINCT rows or COUNT the number of rows. The OPTIMIZE FOR N ROWS clause gives DB2 a better opportunity to establish the access path.The ...
Read MoreHow can a COBOL-DB2 program call a STORED PROCEDURE? Give an example.
A STORED PROCEDURE generally contains the SQLs which are often used in one or more programs. The main advantage of STORED PROCEDURE is that it reduces the data traffic between the COBOL and DB2 as the STORED PROCEDURES resides in DB2.A COBOL-DB2 program can call a STORED PROCEDURE using a CALL statement and we can have nested STORED PROCEDURE upto 16 levels. For example, if we have STORED PROCEDURE with a name ORDERSTAT, then we can call it in our COBOL-DB2 program using the below command:ExampleEXEC SQL CALL ORDERSTAT (:WS-ORDER-ID, :WS-ORDER-STATUS) END-EXECIn order to create a DB2 procedure, we ...
Read MoreImpact of database downtime on the COBOL-DB2 program
Problem: What will be the result if a COBOL-DB2 program tries to query a DB2 table, but the database in which table is residing is down?SolutionWhen we try to access any table using a COBOL-DB2 program and the DB2 database in which that table is residing is down then the COBOL-DB2 program will abend. In this case, the SQLCODE of SQLCA field will have the value as -904.As per the IBM documentation SQLCODE -904 states.“Unavailable resource. The database or tablespace is not available for use”There are several methods which we can use in order to find the current status of ...
Read More