DB2 SQL OLAP : RANK and DENSE_RANK
In any exam how are participants are ranked ? Participant(s) with highest marks got the 1st rank. Participant(s) with 2nd highest marks got the 2nd rank and so on.
DB2 SQL OLAP : ROW_NUMBER
ROW_NUMBER is the most basic OLAP function. It is very easy, powerful, and very handy to use function. ROW_NUMBER assigns a temporary number to each row of result set returned by SQL statement.
DB2 SQL OLAP : OnLine Analytical Processing BASIC
OLAP functions : OnLine Analytical Processing functions Advantages- OLAP functions are specially helpful to create complex reports using a single SQL statement. OLAP functions provide multiple and very advance data grouping and ordering options which are not possible by simple “GROUP BY” and/or “ORDER BY” clauses.
Numeric Datatypes in RPG.
RPG has multiple numeric datatype support. Other then TWO most famous ZONED and PACKED numbers there are BINARY , INTEGER and FLOAT.
DB2 DYNAMIC SQL : PREPARE and EXECUTE
If you need to run the same dynamic SQL statement more than once per job use the PREPARE – EXECUTE statements using EXECUTE IMMEDIATE incurs the unnecessary cost of re-preparing the SQL statement each time PREPARE is like a mini-compile of its own. If required info to PREPARE a statement is not available on COMPILE TIME than the [...]
WrtSFL : Write complete Subfile code in less than 20 seconds.
Description This utility can write Subfile code for any given SQL select statement with little or no user interaction. It takes less than 20 seconds to write complete Subfile code. Check video at top/bottom of this page for more details.
DB2 DYNAMIC SQL : EXECUTE IMMEDIATE
EXECUTE IMMEDIATE is simple option to execute a Dynamic SQL. Here is an example TableName = 'CUSTOMER'; SQLString = 'DELETE FROM '+ %TRIM(TableName) ; EXECUTE IMMEDIATE : SQLString
FREE FORMAT RPG : SAMPLE CODE AND COMMENTS
Here is a sample code to add 2 number in Free Format RPG 0001.00 D Total S 10 0 INZ 0002.00 C *ENTRY PLIST 0003.00 C PARM Number1 5 0 0004.00 C PARM Number2 5 0 0005.00 /FREE 0006.00 Total = Number1 + Number2 ; // Get total for both input values 0007.00 DSPLY Total '*EXT' ; // Display value of Total 0008.00 *InLR = *ON; // End of program [...]
FREE FORMAT RPG : BASIC
Free Format RPG was a major step towards the modernization of RPG language. This change in RPG syntax gave RPG programmers flexibility to write code which is easy to type, read and maintain. Technically Fix Format RPG and Free Format RPG are equally powerful and each one of them has its own pros and minor cons with respect to each other.
DB2 DYNAMIC SQL : BASIC
There are two ways to write SQL one is Static SQL which get job done in most case and second one is Dynamic SQL for rest of complex stuff.
Embedded SQL : Easy String Manipulation Using SQL Functions
SQL is always much better than RPG for handling string data. Embedding SQL in RPG gives RPG the great power not only to handle database operations but also to perform string manipulation. For string manipulation, RPG provides some very basic functions, such as %TRIM, %SUBST, %SCAN, and %REPLACE. CISSP pdf The geology of the surrounding [...]
DB2 SQL FIND nth HIGHEST NUMBER.
We can use very simple correlated query to fine nth highest or lowest record in a table. Here is the query to find 2nd highest salary from EMPLOYEE table Select * from EMPLOYEE E1 where 2 = (Select count(*) from EMPLOYEE E2 where E1.salary <= E2.salary)
DB2 SQL DELETE DUPLICATE DATA FROM TABLE.
There are multiple ways to Delete duplicate data from A Table using SQL Query.Here we are going to talk about using Correlated subqueries to Delete duplicate data.
DB2 SQL CURSOR : COMMITMENT CONTROL
When you use COMMIT or ROLLBACK statements with CURSOR, the behavior of the CURSOR depends on whether or not it is declared using the WITH HOLD clause If the CURSOR is declared using the WITHOUT HOLD clause, all of its resources (cursor, locks, and large-object datatype, or LOB, locators) are released upon either COMMIT or [...]