AS400 and SQL Tricks

GROUP BY clause in DB2 for i SQL The GROUP BY clause allows us to group identical rows based upon one or more column or expressions and displays a …

HAVING clause in DB2 for i SQL The HAVING clause is used to specify the search condition for the groups selected by the GROUP BY clause. Means we…

ORDER BY clause in DB2 for i SQL The ORDER BY clause is used to sort the result set in ascending or descending order according to one or more colu…

Handling NULL values in DB2 for i SQL NULL value means that there is no value present for the column in a row in table. It is an unknown value. We…

Some Special Registers in SQL DB2 for i SQL We can use certain Special registers in our SQL queries. For example, CURRENT DATE, CURRENT TIME etc. t…

DISTINCT clause in DB2 for i SQL The SELECT DISTINCT statement is used to return only distinct rows i.e. different rows. It will avoid the duplica…

BETWEEN AND predicate in Search condition in DB2 for i SQL We can use various predicates in our WHERE clause condition such as BETWEEN, IN, EXIST…

IN predicate in Search condition in DB2 for i SQL It is used to get the rows that falls in the listed values in IN expression. For Example, refer…

EXISTS predicate in Search condition in DB2 for i SQL It is used to test the existence of certain rows. For Example, refer to the Customer table …

LIKE predicate in Search condition in DB2 for i SQL It is used to search for a specified pattern in a column and is used in WHERE clause. There…

Basic Select statement in DB2 for i SQL We can use SELECT statement to retrieve data from the table/tables or view/views. We can use SELECT to ret…

Creating Indexes in DB2 for i SQL Indexes can be created using CREATE INDEX statement and can be used to sort and select data. Indexes also helps …

How to create and use View in DB2 for i SQL A view is similar to Non-keyed LF and is used to access data in one or more table or views. We can crea…

How to retrieve catalog information about a column QSYS2 schema contains a view called SYSCOLUMNS that contains a row for each column of a table a…

How to retrieve catalog information about a table There are several tables and views exists in QSYS2 scheme that fulfil several purposes of the end…

Create and use Sequence object in DB2 for i SQL Sequence is very similar to identity column as they both generate the unique values. However, seq…

How to create and alter Identity columns in DB2 for i SQL If a table has an Identity column then whenever any new row inserted into that table syst…

Creating DDL audit columns in DB2 for i SQL When a table row is added or changed that has an auditing column defined in the table, then the value …

Creating a table with remote server data in DB2 for i SQL We can create a table on the local server that points to the one or more table on the rem…