Friday, February 28, 2014

Editing Numeric Fields in DFSORT/SYNCSORT

EDIT Feature of OUTREC in DFSORT/SYNCSORT gives us great flexibility to mask and represent the data in our very own format.We can play around with PD or ZD data format.We can insert commas,hyphens,slashes,signs with this edit feature. I have tried out some functions randomly.

Suppose this is how the input looks like with the file structure like NAME(14 bytes) ,SALARY(6 bytes),COUNTRY(8bytes)

1. Lets put in some commas(,) in the salary part after first 4 digits.

//            DCB=*.SORTIN,
//            SPACE=(TRK,(100,200),RLSE)
//SYSIN    DD *
      OUTFIL  OUTREC=(1:1,6,11:10,6,ZD,EDIT=(IIII,II))

Similarly the Below SYSIN Card will put the comma after first 2 bytes.
OUTFIL  OUTREC=(1:1,6,11:10,6,ZD,EDIT=(II,IIII))

2 .Now , lets put a dollar sign '$' using EDIT in Outrec
we need to use the below statements.

//SYSIN    DD *
OUTFIL  OUTREC=(1:1,6,11:10,6,ZD,EDIT=($II,IIII))

3. Include the Decimal point using EDIT.
We need to use the below SYSIN card to place a decimal point using EDIT.
OUTFIL  OUTREC=(1:1,6,11:10,6,ZD,EDIT=(IIII.II)) 
HARLEY    1234.56
DAVID       6589.99

4. Include a positive sign ('+') before the digits using EDIT.
HARLEY   +1234.56
DAVID      +6589.99

Note: There are  for 27 predefined Edit masks available;  M0 -M26. They can be straight way used in our code instead of coding 'EDIT= ' parameter. Like we can write the control card like:

OUTFIL  OUTREC=(1:1,6,11:10,6,ZD,M6)   instead of writing
Both giving the same output result:
HARLEY        012-3456
DAVID           065-8999

'I' is used to display digits (1-9) and blanks for leading zeros.
'T' is used to display digits (0-9)
'S' indicates Sign which can be leading or Trailing.

Tuesday, February 11, 2014

What are control blocks in Mainframes?

Control blocks are the memory units which represents/contains the status of what is going on or what has just happened, in short the TASK information when task is executed in the operating system. It provides the information about the job, CICS task and much other information.

Types of Control Blocks:

System Related Control Blocks
Resource Related Control Blocks
Job Related Control Blocks
Task Related Control Blocks

These provide debugging functionality in the event an operating system component or user application fails while the system is running.

Most commonly used system related control blocks are as follows:
1. CVT (Communication Vector Table)
2. SSCVT (Subsystem CVT)
3. ASCB (Address Space Control Block)
4. TCB (Task Control Block)
5. ASXB (Extended Address Space Control Block)

Where we use Control Blocks:
Dump Reading
CICS Abend Analysis
z/OS Data Areas
Diagnosing Dump
Abends  and many more..

TCBs represent tasks executing within an address space, such as user programs with multi tasking.

SRBs  executing from one address space and  perform a task in other some address space.
Each address space then has its own chain of ready SRBs and/or TCBs, pointed to from its ASCB. Whenever an event completes which changes the status of an address space, the relevant z/OS function updates the dispatching queues to reflect it.

ASCB – Address Space control Block
ASCB contains information and pointer addresses required for the management of this address space. Each address space is represented by an ASCB within the z/OS operating system.

ASVT – Address Space Vector Table
ASVT used to keep track of all address spaces in the system image, therefore it contains a list of all possible address space IDs, if assigned with associated ASCB. Remember an address space is created for each job, started task or mount request. CVTASVT which is the location of ASVT from system CVT.

ASXB – Address Space Extended Block
control block also contains information about an address space which has crucial information about number of TCB etc.     

PSA – Prefixed Save Area
Contains PSW ( Program Status Word) , interruption codes, registers  & register save area for lock manager & interrupt handler. It holds the basic information z/OS needs when scheduling work on a Central Processor (CP) , it always start at address ‘0’. One PSA per processor  & it contains processor related information.

FLCCVT - A pointer to the CVT

PSAAOLD - A pointer to the Address Space Control Block (ASCB) of the address space currently scheduled on this CP. The ASCB holds basic information about an address space, including Job name and Address Space ID. More information is held in the Address Space Extension Block (ASXB), which is pointed to by the ASCB.

PSATOLD - A pointer to the Task Control Block (TCB) of the task currently scheduled on this CP. The TCB holds information on a specific task.

Friday, February 7, 2014

Data Compression in DB2

Disk storage and space management is one the hot discussed topics now a days.Coming to database management, DB2 V9 uses Venom technology to compress a row data which leads to reduced IO, efficiency and provide fast access to data.
There are two main types of data compression available for tables:
1 Row compression
2.Value compression
How Can we COMPRESS the data
When we use COMPRESS YES in CREATE TABLE/ALTER TABLE statements the data compression is achieved.
DB2 scans the table data ,even rows and looks for the repetitive ,duplicate data.
Then internally it builds a dictionary based algorithm with these data and assigns a short numeric keys to those items.
An Example:
Suppose we have two rows with the below values in one table like below.

Mike   2000 LONDON 70045 UK
JOHN 3000 LONDON 70045 UK

DB2 scan these rows and optimizer decides that  the pattern 'LONDON 70045 UK' is repetitive. So DB2 assigns one numeric qualifier to this pattern,say 02.
Thus the above row will be changed to

MIKE 2000 02 JOHN 3000 02
where 02 = LONDON 70045 UK

This Dictionary ,ie the mapping of the repetitive field will be hidden object in the database.It remains cached for quick access.There can be instances where such type of COMPRESSION does  not result in space saving.DB2 would not compress in those cases.

NOTE: This data compression happens only when the  table dictionary is built which usually happens during the REORG PHASE.
While creating a LOB tablespace, we should not specify the COMPRESS YES OPTION.

There are several types of data compression available for tables:
Row compression
Value compression
Adaptive compression

Example used above shows Adaptive and classic row compression because
they attempt to replace data patterns that spans multiple columns with short symbols.

Value compression is effective when rows with columns with the same value, like CITY, or columns that contain the default value for the data type of the column.

When we need adaptive compression,we use  COMPRESS YES ADAPTIVE
when we need classic row compression,use  COMPRESS YES STATIC.
When we need value compression,use  COMPRESSION clause. If we want to compress data that represents system default column values, also include the COMPRESS SYSTEM DEFAULT clause.

Wednesday, February 5, 2014


We all know DB2 places lock on the data when we query the database.But why does DB2 does that?.
Answer is very simple. To maintain concurrency and consistency.
To make it clear, Concurrency means "Access to the data by two or more applications using SQL query."
By Consistency we mean that "when ever the data is getting changed, the control information of the page must be updated and it should be consistent".  As we go deeper into locking,we must know that DB2 handles this locking mechanism with the help of IRLM.
How does this locking mechanism happens ?
IRLM stands for Internal Resource lock manager.As the name says it all, handles all these locking mechanism. IRLM runs in different subsystem and in its own address space.Every DB2 subsystem has its own instance of IRLM running.This is what happens when we run a query.
1->When a SQL Query needs to be run, DB2 talks with IRLM with cross system MVS calls.
2->IRLM receives this request and checks if it can/cannot provide the requested locks.
3->It then conveys this information back to MVS and MVS informs DB2 regarding the lock availability.

The locks set by DB2 have the following  attributes :
Lets have some more in-depth idea about each of these attributes.
LOCK SIZE: Lock sizes typically specify how much data in the DB2 objects shall be locked.By objects we mean the db2 resources like table space,table and other resource.Broadly DB2 supports locking at 4 levels.Namely A) Table Space B) Table C) Page D) Row. (From most data locked to least data locked)
A diagrammatic representation of Table and Row level locking

Table space: Lock the entire table space.
Table: Only segmented table spaces permit an individual table to be locked.
Partition: DB2 Locks only a partition in a partitioned table space
Page: Lock an individual page in a table space..This is best choice in terms of concurrency control
Row: Lock an individual row in a table.This is the lowest level of granularity.
Allows two users to access different rows on the same page @ same time,But @ the cost of increased overhead.
Note: we must know that ROW and PAGE lock happens at the same level.
LOB DATA: For large object type (BLOB,CLOB) DB2 provides LOB locks.
XML DATA: DB2 also provides locks for xml data.

Lets see a a brief on each one of these locks below:

Table space and Table locks: In case of simple table space, table locks may lock data in other tables,because rows can be intermingled on different pages. In case of segmented table space,only the pages from single table will be locked.
When accessing a Partioned Table space,DB2 will lock only the partition being used in query, instead of the entire table space.
In a few instances, DB2 may not be able to take partition-level locks:
When the plan or package is bound with ACQUIRE(ALLOCATE)
When the table space is defined with LOCKSIZE TABLESPACE
When the LOCK TABLE statement is used without the PART option.

PAGE locks:DB2 internally continues read/write operation in terms of equal size units known as pages.
The size can vary from 4k,8k,16l,32k.  The default page size is 4K.
So a page lock in a Simple Table space may locks rows from other tables since one or more tables can be intermingled on a page. In SEGMENTED TABLE SPACE,a page lock will lock only rows of single table.

ROW locks: Will be needed when multiple applications need page for simultaneous processing.
Note: Row lock & page lock operates occurs at the same level.

LOB locks: LOB or large object locks are bit different from regular locks.
LOB locks are taken when INSERT or UPDATE is used. No locks are taken for
SELECT statemnt or DELETE statement.LOB lock is taken only when it is needed.So even if we use ACQUIRE option in BIND card,it would not have any effect on the LOB table space.

So, how do we specify all these Lock sizes ?
When creating or altering the tablespace, we specify a lock size (ROW, PAGE, TABLE or TABLESPACE) or LOCKSIZE  ANY, indicating that DB2 should choose the lock size whenever the table is accessed.

When a sql statement is first executed, DB2 first takes a type of lock,called INTENT LOCK on the  table or Tablespace.Later This intent lock later is changed to 'S', or 'U' or 'X' lock against the row or page.

Intent share: The transaction intends to read but not update data pages and, therefore, takes S locks on them; it tolerates concurrent transactions taking S, IS, SIX, IX, or U locks.

Intent exclusive: The transaction intends to read or change data and therefore take an X lock against the data pages; it tolerates concurrent transactions taking IX or IS locks.

Shared with intent exclusive: The transaction intends to read or change data; it tolerates other transactions taking an IS lock on the tablespace, which allows them to read data by taking S page locks. They cannot change the data.
Thus, primary purpose of intent lock is to act as a Traffic signal and cause a program to wait for a required S, U, or X lock at the table and tablespace level until the necessary locks have been released from the rows and pages and there is a compatible intent lock.

Further More locks can be taken in share (S), exclusive (X), or update (U) mode.
(S)hare lock is taken is response to a SELECT statement in the query.It allows 2 or more programs to read simultaneously
(X)lusive  lock indicates the user needs exclusive use of table or TS/partition for update activity and other access is not allowed.
(U)pdate lock is taken when there is a 'possibility to update'.If the SQL query uses 'FOR UPDATE OF' clause, a U lock is taken.Other applications can get S locks to read the data until the Update(U) lock is promoted to an Exclusive(X) lock at the instant the update occurs.

                                                            A pictorial view of locking
Pictorial view of 4 lock modes. It shows how concurrent programs locks are affected wile one lock is in place.

The lock duration is the length of time a lock can be held by a requester.
The ACQUIRE and RELEASE parameters impact table space locking.

ACQUIRE(ALLOCATE) versus ACQUIRE(USE): The ALLOCATE option specifies that locks will be acquired when the plan is allocated, which normally occurs when the first SQL statement is issued. The USE option indicates that locks will be acquired only as they are required, SQL statement by SQL statement.

RELEASE(DEALLOCATE) versus RELEASE(COMMIT): When you specify DEALLOCATE for a plan, locks are not released until the plan is terminated. When you specify COMMIT, table space locks are released when a COMMIT is issued.

What is a DB2 LATCH? What is the Difference between a lock and latch?
As we have seen, IRLM maintains the concurrency, A page consistency is maintained  by a latch.
Think of latch as a indicator or signal. If the signal is ON, then the page is latched, ie, the page control information is currently being updated. A latch is not handled by IRLM. Latches are purely internal to DB2 and are not visible at transactional level.