SQL Query optimization is a process of writing optimized SQL queries to improve database performance. It is one of the factors that affect application performance. When working with large-scale data, even the most minor change can have a dramatic impact on performance. Top 10 ways to improve performance Indexing: Proper indexing ensures optimal access to […]
SQL Query Optimization: Tips & Tricks Read More »
The DB2 Predicates affect how DB2 selects the access path for the statement. Predicates are found in the WHERE, HAVING, or ON clauses of SQL statements; they describe attributes of data. Most DB2 predicates are based on the columns of a table. They either qualify rows (through an index) or reject rows (returned by a
DB2 Predicates : Performance Tuning of DB2 Queries Read More »
SQL predicates, also referred to as conditional expressions, specify a condition of a row or group that has one of three possible states: TRUE, FALSE NULL (or unknown). SQL Predicates are found on the tail end of clauses, functions, and SQL expressions inside of existing query statements SQL Predicates Categories Basic Predicate – A basic
SQL Predicates: use in COBOL DB2 Queries Read More »
EXISTS and NOT EXISTS are used with a subquery in the WHERE clause to examine if the result the subquery returns is TRUE or FALSE. It will identify the existence of a relationship without regard for quantity. For example, if the subquery returns at least one record in its result set, the EXISTS clause will evaluate to true
EXISTS vs NOT EXISTS use in Subqueries Read More »
The sysibm.sysdummy1 table is a special in-memory table that can be used to fetch the value of DB2 registers Date-Time-Timestamp. To extract a certain portion of data from the current timestamp you can use the below-mentioned functions. Specifying CURRENT_TIMESTAMP is equivalent to specifying CURRENT TIMESTAMP. Date-Time-Timestamp SELECT current date FROM sysibm.sysdummy1 SELECT current time FROM
Date-Time-Timestamp use in SQL Queries Read More »
DB2 Cursor builds a results table to hold all the rows retrieved by executing a SELECT statement. DB2 uses a cursor to make the rows, from the results table, available to the application program. A cursor identifies the current row of the results table. When you use a cursor, the program can retrieve each row
DB2 Cursor use in COBOL Programs Read More »
ROWSET processing is a group of rows for the result set of a query that is returned by a single FETCH statement. You can control the number of rows you want to fetch and return by specifying rowset limit in your program. ROWSET size is specified on the FETCH statement, the maximum rowset size is
ROWSET Processing using Cursor – DB2 Read More »
The DB2 interview questions and answers are intended for those with a working knowledge of DB2 as a programmer. This will help you prepare for technical DB2 interview and online selection tests during campus placement for freshers and job interviews for professionals. DB2 Interview Questions 1. What do the initials DDL and DML stand for
DB2 Interview Questions with Answers Read More »
An INTERSECT operation retrieves the matching set of distinct values (not rows) from two columns. The INTERSECT ALL returns the set of matching individual rows. An INTERSECT and/or EXCEPT operation is done by matching ALL of the columns in the top and bottom result sets. In other words, these are rows, not the column, operations.
INTERSECT and EXCEPT use in SQL Queries Read More »
A UNION operation combines two sets of columns and removes duplicates. The UNION ALL expression does the same but does not remove the duplicates. When DB2 encounters the keyword, it processes each select / sub-select to form an interim result table, then it combines the interim result table and deletes duplicate rows to form a
UNION vs UNION ALL use in SQL Queries Read More »
COALESCE DB2 function is used to handle the NULL values that might be present in the data or a list of values that are specified. If the NOT NULL constraint on the column is not applied, then the default value that gets inserted in those columns when not specified is the NULL value. It returns the
COALESCE DB2 Function in SQL Queries Read More »
SQL CASE statement allows you to perform IF-THEN-ELSE functionality within an SQL statement. CASE SQL expression can help make your data more readable and useful to the user or to the application. It allows selecting one sequence of statements to execute out of many possible sequences. There are two general flavors of the expression. In
CASE statement in SQL Queries with Examples Read More »
GROUP BY clause specifies a result table that consists of a grouping of the rows of intermediate result table that is the result of the previous clause. The GROUP BY clause allows you to find the characteristics of groups of rows rather than individual rows. ORDER BY clause specifies the particular order in which you
GROUP BY and ORDER BY clause in SQL Read More »
DB2 Join: An INNER JOIN finds and returns matching data from tables, while an outer join finds and returns matching data and some dissimilar data from tables. An inner join finds and returns matching data from tables, while an outer join finds and returns matching data and some dissimilar data from tables. For combining two sets
DB2 Join – Inner Joins and Outer Joins Read More »
DBRM: DataBase Request Module has the SQL statements extracted from the host language program by the SQL precompile. The COBOL-DB2 program is preprocessed by DB2 to produce a DBRM that will be used to bind against a particular DB2 subsystem. The Db2 system uses the DBRM to optimize application program interaction. PACKAGE: A package contains control structures that DB2 uses
DBRM, Package, Collection and Plan Read More »
The FETCH FIRST n ROW ONLY clause is used to limit the result set of a query to a specified number of rows. It is particularly useful when you are only interested in retrieving a subset of the total records. Retrieving the entire result table from the query can be inefficient. You can specify this
FETCH FIRST n ROW ONLY and OPTIMIZE FOR n ROWS Read More »
A DB2 null Indicator/value represents missing or unknown information at the column level. When a column is set as null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known. It
DB2 NULL Indicator use in SQL Queries Read More »
DB2 locking and concurrency is a critical part of DB2 as well as batch performance. Batch update jobs processing large amounts of data will need to have restart logic through the use of the COMMIT statement and a program data save area. This will identify the restart point. Commit frequency should be an input parameter
DB2 locking and concurrency – Tips & Trick Read More »
