How does AND and OR clause operate ?

Here is the situation, I have a DB2 table with 5 rows as shown below

 

I need to perform a DELETE operation in such a way that I only retain rows which contain PRDN in the column SUBS and PDB01 in column DBNM, i.e row 2 and 3. Well, it might have been a question back those days when you are new to SQL and need to break your head to figure out how to fullfil the requirement.

 I don't need rows any other rows to be present in the table other than row 2 and 3, Let us first at a DELETE DML -

DELETE * FROM TABLE WHERE SUBS='TEST' AND DBNM='TDB01' ;

Result Table -

Well this DML will only DELETE 1 row ( Row number 4 ) as it is an AND operation. AND operation in a DELETE DML will perform the delete(s) on the table when BOTH the conditions in the WHERE clause are satisfied/true.  Row numbers 4 and 5 were not deleted as one of the conditions failed

Let's look at the second DELETE DML -

DELETE * FROM TABLE WHERE SUBS='TEST' OR DBNM='PDB02' ;

Result Table -

Perfect DML, The OR clause on a DELETE DML will perform the delete(s) on the table on the rows which satisfy any one or both the conditions. It will delete rows where SUBS='TEST' ( row 4 and 5 ), it will also delete rows where DBNM='PDB02' ( row 1 ) .  Row number 2 and 3 are retained. 

This example had just 5 rows and the requirement was pretty simple, This post was only meant to make the users feel and understand the difference in the OR and AND clause. In case you have large number of records and need to filter it out, use the LIKE clause as appropriate as shown in below example -

DELETE * FROM TABLE WHERE SUBS='TEST' OR DBNM NOT LIKE 'PDB01' ;

This query will also delete row number 1, 4 and 5.

Note -

Like clause can cause performance overhead on queries and is not recommended.

 

 

How to define a DECIMAL datatype in DB2 for z/OS

Young DBA's and developers always have this confusion while defining the DECIMAL datatype in DB2. Well I had that problem as well and here is how I overcame it a long time ago,

Requirement : I need a column CURRENCY which can accomdate this figure : $ 1,000,000.00 on a DB2 for z/OS table . This post is designed to help you understand the ' how to ' of defining a DECIMAL datatype on a DB2 table. Let's now focus the requirement on defining $ 1,000,000.00 as a decimal datatype

Break your request to 3 easy findings :

1) As we can see 1,000,000 is on the left hand side of the period ( dot ) and it accounts for 7 digits of the number, this is called precision of the decimal data type.
2) We see a period ( a dot ' . ' ) after the 7 digit number, well we will call it a dot.
3) We also see 00 on the right hand side of the period ( dot ) which accounts for 2 more digits of the number, it is called the scale.

7 ( precision ) + 2 ( scale ) = 9 : Straight forward DECIMAL ( 9 , ? ) - What about the ? ( question mark ). Well that's the scale which is 2 in our case, so it makes up as DECIMAL ( 9 , 2 )

To define $ 1,000,000.00 in DB2 you need to define the column datatype as DECIMAL ( 9 , 2 )

Note :  The usual mistake commited in interpreation of 1,000,000.00 will be reading the figure as DECIMAL(7,2) which is incorrect.