Sensitive cursor,insensitive cursor and asensitive cursor!!

Scrollable Cursor in DB2 : Sensitive cursor,insensitive cursor and asensitive cursor!!

Having completed the Basics of CURSOR in db2 ,we know there can be scrollable and non scrollable cursor. Further more There are two types of DB2 scrollable cursors .(a).Sensitive (b) Insensitive.
(We will difference between static and dynamic cursors in next post. Here we will see broadly what is sensitive/insensitive cursor) 
To start with we will see first what is an insensitive cursor.Let us see how the  keyword 'INSENSITIVE' is used to declare this type of cursor. 
(We will see difference between STATIC and DYNAMIC cursor in next post)

DECLARE C1 INSENSITIVE SCROLL CURSOR FOR
 SELECT STAFF_ID,NAME  FROM EMP
....
OPEN C1
...
FETCH C1
.. 
CLOSE C1 
We will see how and where the INSENSITIVE feature is shown below.

Once we open the C1 cursor, a temporary table is created in the WORK File database  which holds the data from the base table ,ie, EMP table. (We must know that to use scrollable cursors we must use declared temporary  tables.DB2 places the rows returned from the cursor into these Temporary tables.)
TIMELINE T1: The Cursor is opened and result set is fetched. We will see that the  base table EMP and the result table(the result of cursor open statement) is same

STAGE1: The cursor is fetched and Main table and result table looks same

TIMELINE T2 (T2>T1) :  After some time T2,user deletes one row from the main table ie the EMP table.But since the cursor is INSENSITIVE, it does not sense the modification done to the base table, thereby shows the same rows even though main table has changed.

STAGE2: Row deleted from main table , yet the cursor result table shows the deleted row!!

Lets see now SENSITIVE CURSOR:
DECLARE C1 SENSITIVE STATIC SCROLL CURSOR FOR
 SELECT STAFF_ID,NAME  FROM EMP
....
Proceeding in the same way, let us review the Timelines T1 and T2 of the cursor life cycle.
TIMELINE T1: The Cursor is opened and result set is fetched. We will see that the  base table EMP and the result table(the result of cursor open statement) is same.  This is the same as shown above.
TIMELINE T2 (T2>T1) :  After some time T2,user deletes one row from the main table ie the EMP table.But since the cursor is SENSITIVE, the Temporary table will sense the modification done,ie the delete and will also delete the corresponding row in the Temporary table.

In Timeline T2, the cursor fetched reflects the same change as the base table EMP.

SENSITIVE CURSOR comes with the combination of  SENSITIVE STATIC and SENSITIVE DYNAMIC
Lets look at the FETCH statements for these scrollable type cursors.

All the possible FETCH options are shown 

here

To FETCH 5th Row from beginning of the Result set:
FETCH SENSITIVE ABSOLUTE +5 FROM C1
Fetch the FIRST ROW
FETCH SENSITIVE FIRST FROM C1
Move forward:
FETCH NEXT FROM C1
Move Backward:
FETCH PRIOR FROM C1 
Fetch 5th last row:
FETCH ABSOLUTE -5 FROM C1

RELATIVE n - will FETCH the row that is n rows away from the last row fetched
ABSOLUTE n - will FETCH the row that is n rows away from the first row in the results set

Static and Dynamic Cursor

Static cursors and Dynamic cursors in DB2

Coming to the cursor types, before DB2 v7, we could only move/scroll through  the cursor in forward position.So when the cursor is opened, the pointer would be positioned before the first row in the result set. Once the FETCH has been issued, the pointer would move one row forward.
NO SCROLL is the default and indicates that the cursors are not scrollable.This is the normal cursor which commonly gets used in application program.

DB2 V7 introduced STATIC SCROLLABLE CURSOR  which can be scrolled backwards or
forwards, and to an absolute position or to a relative position.When the CURSOR is opened, the qualifying rows would be placed in a  declared temporary table automatically created by DB2.Scrolling is performed on the temporary table in both the forward direction and backward direction. DB2 deletes the result table when the cursor is closed.

With the introduction of DB2 v8, the concept of DYNAMIC SCROLLABLE CURSOR has been introduced. Dynamic cursors DO NOT need any temporary table.This is one of the main difference between static and dynamic cursors. They SCROLL on the main table itself, and therefore are sensitive to all the  committed inserts, updates, and deletes. Thus the overhead in building the temporary table(as in static cursor)  and maintaining the synchronization between the base table and result table is not there in case of dynamic cursor.

Declare cursor statements:

DECLARE CURSOR statements for static and dynamic cursors

NO SCROLL is the default and indicates that the cursors are not scrollable. If you specify
SCROLL, the cursors become scrollable.
For  Dynamic CURSORS we can have the below attributes:
1. SENSITIVE DYNAMIC SCROLLABLE
2. ASENSITIVE SCROLLABLE

Cobol Db2 program : Precompile and Compilation Process

How the DB2 Application Program runs on Mainframe

This Post is to understand in detail how your application program runs (and various stages it goes through) on mainframe.

What is DB2 Application program?

The program written in any High-Level language (In this context COBOL language) which access the data stored in DB2 database.

IF we feed the DB2 application program written in COBOL to the Cobol Compiler (IGYCRCTL) it can recognize only Cobol statements in application program and cannot recognize any DB2 statement hence before feeding this program directly to Cobol compiler, we want something which can recognize DB2 statement and separate the DB2 statement from the Cobol program. This can be done by feeding the application program to DB2 Pre-compiler.

Ø  DB2 Pre-compiler :

·         The DB2 pre-compiler will comment out the DB2 statement in program and replace them with the CALL statement.  


This commented DB2 statement will be stored separately in different dataset which is called as DBRM (Data Base Request Module).

So this pre-compiler separates out the main program in two parts

1>    The modified Source code (The main program with Commented Db2 statements and corresponding  replaced  CALL statement.

2>    The DBRM (Contains all the DB2 statement from the source program)

The same (How the DB2-Precompiler separates out the main program in two parts) can be viewed in BATCH JCL.  
      

                                                                                                                                                                                                                               ·         Place the timestamp token  on DBRM and modified source code .
This Timestamp token will then carry forward in bot parts and will be checked at the run time when these two separated parts reunite.So at the run time the matching timestamp token will indicate that we are using the same DBRM and Modified source code which get separated at pre-compile step.

The pre-compilation timestamp token placed by DB2 pre-compiler on the DBRM can be obtained by checking the value from POS 25 through POS 32 in the DBRM header, which is the first record in the DBRM member.



The pre-compilation timestamp token placed by DB2 pre-compiler on the Modified source code can be found in variable SQL-TIMESTAMP-1 SQL-TIMESTAMP-2.



SQL-TIMESTAMP-1 (412701320) equals 18995288 in Hex which is the same as the first 8 digits the DBRM.
SQL-TIMESTAMP-2 (471453846) equals 1C19D096 in Hex which is the same as the second 8 digits the DBRM.

Ø  COBOL Compiler (IGYCRCTL):

The modified source code out of the DB2 pre-compiler will now fed to the COBOL compiler. The compiler will check the code for syntax error and creates the OBJLIB (Object Library)   
              


Ø  Link-Edit (IEWL) :

IEWL is the program which link-edit the OBJLIB created in previous step by Cobol compiler to create a LOAD MODULE (The corresponding machine level code of our main DB2 Application program)



Ø  BIND :  

             The second part created out of DB2 Pre-compiler (DBRMLIB) will now get processed.
The Bind is type of compiler to the SQL statement (DB2 statement).It will read the DB2 Statement from the DBRMLIB and check it for syntax error and create a mechanism to execute the SQL statement.
There are two types of BIND, BIND PLAN and BIND PACKAGE.

·         BIND PLAN:
 This will have the one or more DBRM, Packages or the combination of DBRM and Package as an input. The BIND PLAN command will invoke the DB2 Optimizer which intern will create the PLAN containing optimized access path to execute the SQL statement inside DBRM.



The PLAN created here is an executable and will execute only along with the corresponding Load module created from modified source code through Compile-Link Edit process. None of the PLAN or Load Module can be executed alone.

·         BIND PACKAGE:

This will have only single DBRM as an input. The Package created here is not executable and need to be bind to plan again in order to get executed.
The bind process also updates the Pre-compile Timestamp token placed by the DB2 Pre-compiler on DBRM header in TIMESTAMP column in SYSIBM.SYSDBRM table .PRECOMPTS column will have the regular Timestamp. The columns PRECOMPTIME and PRECOMPDATE will have time (Resolution to 100^th of a SEC.) and Date (YYMMDD).Note the remaining flags in the DBRM header will be updated in the first row of SYSIBM.SYSSTMT (for PLAN bind) orSYSIBM.SYSPACKSTMT (for package bind). DB2A gets the details from these tables.

Ø  RUN PROGRAM:

So at the start we separated our main DB2 application program into Modified source code and DBRM which then went through two different processes to create the LOAD module and PLAN respectively. Now just before running the program we need to reunite these two and run our main DB2 application program.

While running, the Timestamp token placed by the DB2 pre-compiler on both part (Plan (fromSYSIBM.SYSDBRM) and Load module) will be checked.
We will get the SQLCODE of -818 if the Timestamp between two doesn’t match.

When to Use Which or That

When to use which or that has always been known as one of the most grammatically confusing grammar lessons ever taught. The fact that the two words are interchangeable does not make a lesson on either word that much easier. You could sit through four years of English classes and still not understand when to use “which” or “that.” Hopefully the illustrations below will help you understand a little better.

Restrictive and Non-Restrictive Elements

The elements up for illustration are not Earth, wind or fire. Let’s talk sentence elements. Two come to mind – they are, restrictive and non-restrictive elements.

• A restrictive element is a word, phrase or a clause that manages to limit the meaning of the sentence element that it modifies. When a restrictive element is not included then the entire meaning of the sentence will change.

• The non-restrictive element is a word, phrase or a clause that provides excess information about the beginning of a sentence without restricting the meaning of that part of the sentence.

Keep these two definitions in the back of your mind because they will be crucial understanding when to use “which” or “that.”

Using Which vs. That

That Is Restrictive

The word “that” is considered to be a restrictive element of any sentence that it may be used in.

If you take a quick look at the definitions above, you will see that a restrictive element limits the meaning of the sentence element that it modifies. For example if you used the following sentence, “Baby foods that contain soybeans are best.” The restrictive element of the sentence are the words “that contain” because these words restrict the type of baby food that is being discussed. 

In effect without the words “that contain” the whole sentence meaning would be altered. You can see that without the words “that contain” there would be no restrictive element of the baby food. Instead the sentence would imply that all baby food is best.

Let’s examine the notion of the non-restrictive element. Reverting to the definition above, a non-restrictive element is a word, phrase or a clause within a sentence that only serves as additional information – an info item so to speak.  Should a non-restrictive item be left out of the sentence then it will not change the meaning of the sentence whatsoever. 

Which Is Not Restrictive

You can usually recognize a non-restrictive element because it is surrounded by commas or parentheses. Here is an example (working from a similar version of the sample sentence above) of how a non-restrictive element using the word “which” should be used. “Soybean baby foods, which are Sally’s favorite, work well for her diet.”

Can you see the difference between how the word “that” (a restrictive element) and the word “which” (a non-restrictive element) work in a sentence? Take a look again. If you look at the illustration for a non-restrictive element (or the word “which”) you can see how if you removed the phrase “which are Sally’s favorite” the meaning of the sentence does not change. The only thing that the phrase “which are Sally’s favorite” does is provide additional information.

It is important, when reviewing how to use “which” or “that” in a sentence that you take your time and let the lesson sink in. If you read it too fast, you may miss it and get confused. When you are learning about restrictive and non-restrictive elements it is crucial that you pay close attention. The slightest distraction could lead you to use the words “which” and “that” in the wrong context. When it comes to these words it helps to fully understand the role that they play in sentence structure.

For more information on how to effectively use the words “which” and “that” you might want to consult your local English or Grammar department or log on to the Internet and search through the grammar lessons that many teachers have listed. These lessons can be invaluable in discerning the proper usage of the words.

NAIL THE RCs

Well I am no expert but I use certain techniques by which I nail about 3-4 RCs in each mock. I am not that good in Quant so I try to maximise my verbal section through RCs. Techniques that I follow , some of them are:-

1) First, remove the fear that RCs are negative marks accumulators.
2) Read two editorials per day from www.aldaily.com and the newspaper.
3) Try and read novels-good old classics should help.

Now the RC bit

1) If you don't like the initial line of the RC don't proceed then, solve it at the very end if you have time or completely leave it. This liking is similar to the feeling you get when you choose your girlfriend over another girl. grin emoticonJokes apart, this is important

2) When you are reading your RC, stop looking at the clock, read slowly but steadily. If verbal is your weaker section then you must attempt 2Rcs to the bare minimum , so yes you have time. If you complete the other questions and have 25 min left for your RCs then you can knock of two of them completely. So don't panic.

3) Don't look here are there. Read it at a stretch and slowly. Put two fingers in your ears and read if possible but get the facts in your head.

4) After reading the entire passage summarise the gist in your head for about 30sec and then proceed to the questions.

5) Read the question-each question at-least two times if not thrice. Try to understand what is being asked and get to the part of the passage from where the question loiters about. Read that part again. don't worry about the time. Two options shall be damn close. If you have clicked the obvious then stare at the screen and contradict yourself . If you can't Convince yourself to change the response click on that.

6) Treat RCs as the father of critical reasoning questions. We aren't afraid of ghosts, we are afraid of the dark. So change the term!!! Call the RCs " Long CRs" It worked for me. grin emoticon

7) If you have no idea regarding an answer even after following all the other steps, leave that question. -1 se behataar zero hain. tongue emoticon

8) For those who are lacking the appetite to solve RCs i suggest you to practise from www.rcprep.com. If you are really poor at solving RCs I suggest you to take 12min per rc and solve two of them correctly. matlab 7*3=21 is better than 6*3-7=11, if you get what I mean.

9) Last but not the least, as the saying goes : " Shop till you drop. " Practise till you literally drop. RCs are like DI , only a bit complex. They aren't as straightforward as DIs and ajke din me sabhi googly hi daalte hain. So we should all get used to it colonthree emoticon

10) Always read the passage first and then answer the questions. But if you see that only 4 or 3 minutes are left and you have nothing else to do. Go look for the other passages and skim for direct factual questions like :" What does the author say when we meant this word? " or " What was the reason behind this" or "meaning of word". Such questions you can solve easily if you find the part of the passage from where it is taken. skim through the passage fast. the idea is to hold on to your nerves. again nahi hua toh thik hain lekin -1 nahi chahiye. if you find out the part and if you are absolutely sure, then mark it. this is the only time when you should read the question first and the passage later

PS:- THIS ARTICLE IS ENTIRLY COMPOSED BY ME AND REFLECTS MY VIEWS ONLY, ON HOW TO TACKLE RCS. THEY MAY NOT BE THE BEST WAY TO DEAL WITH IT BUT IT WORKS FOR ME. JUST THOUGHT MAYBE MY PROCESS WOULD HELP SOME PEOPLE. AGAIN I AM NO EXPERT. tongue emoticon

Pascals Triangle

How to find square of any number in seconds with just mental calculation?

There are many ways with which we can find out square of a number within seconds. Let’s see how we can find out square of a number faster using Vedic Mathematics (Nikhilam method)

Example 1: Find out the square of 12

Step 1
We need to take the nearest power of 10. So here 10 is the nearest power of 10 which we can take as our base. The deviation to the base = 12-10 = 2
(To find out the deviation, just remove the left most digit "1" and you will get it quickly)

​Now the Left Hand Side of the answer (LHS) will be the sum of the number and deviation. Hence the Left Hand Side of the answer (LHS) = 12 + 2 = 14
Step 2
Our base is 10 which has a single zero. This means that our Right Hand Side of the answer (RHS) will have a single digit and that can be obtained by taking 
the square of the deviation.
Hence the Right Hand Side of the answer (RHS) = 2² = 4
Hence the answer  = 144

Example 2: Find out the square of 13
Step 1
We need to take the nearest power of 10. So here 10 is the nearest power of 10 which we can take as our base.  The deviation to the base = 13-10 = 3
(To find out the deviation, just remove the left most digit "1" and you will get it quickly)

​Now the Left Hand Side of the answer (LHS) will be the sum of the number and deviation.Hence the Left Hand Side of the answer (LHS) = 13 + 3= 16
Step 2
Our base is 10 which has a single zero. This means that our Right Hand Side of 
the answer (RHS) will have a single digit and that can be obtained by taking the square of the deviation.
Hence the Right Hand Side of the answer (RHS) = 3² = 9
Hence the answer  = 169

Example 3: Find out the square of 14

Step 1
10 is the nearest power of 10 which we can take as our base.  The deviation to the base = 14-10 = 4
(To find out the deviation, just remove the left most digit "1" and you will get it quickly)

​
​Now the Left Hand Side of the answer (LHS) will be the sum of the number and deviation. Hence the Left Hand Side of the answer (LHS) = 14 + 4 = 18
Step 2
Our base is 10 which has a single zero. This means that our Right Hand Side of the answer (RHS) will have a single digit and that can be obtained by taking the square of the deviation.
Hence the Right Hand Side of the answer (RHS) = 4² = 16. However please note that the Right Hand Side of the answer (RHS) can have only a single digit because our base 10 has a only single zero. Hence, from the obtained number 16, we will take R.H.S as 6 and 1 is taken as a carry which we will add to our LHS. Hence LHS becomes 18+1 = 19
Hence the answer = 196

Example 4: Find out the square of 106

Step 1
Here 100 is the nearest power of 10 which we can take as our base.  The deviation to the base = 106-100 = 6
(To find out the deviation, just remove the left most digit "1" and you will get it quickly)

​Now the Left Hand Side of the answer (LHS) will be the sum of the number and deviation. Hence the Left Hand Side of the answer (LHS) = 106 + 6 = 112
Step 2
Our base is 100 which has two zeros. This means that our Right Hand Side of the answer (RHS) will have two digits and that can be obtained by taking the square of the deviation.
Hence the Right Hand Side of the answer (RHS) = 6² = 36.
Hence the answer = 11236

Example 5: Find out the square of 112

Step 1
Here 100 is the nearest power of 10 which we can take as our base.  The deviation to the base = 112-100 = 12
(To find out the deviation, just remove the left most digit "1" and you will get it quickly)

Now the Left Hand Side of the answer (LHS) will be the sum of the number and deviation. Hence the Left Hand Side of the answer (LHS) = 112 + 12 = 124
Step 2
Our base is 100 which has two zeros. This means that our Right Hand Side of the answer (RHS) will have two digits and that can be obtained by taking the square of the deviation. Hence the Right Hand Side of the answer (RHS) = 12² = 144. However please note that the Right Hand Side of the answer (RHS) can have only two digits because our base 100 has only two zeros. Hence, from the obtained number 144, we will take R.H.S as 44 and 1 is taken as a carry which we will add to our LHS. Hence LHS becomes 124+1 = 125
Hence the answer = 12544

---

For a video tutorial, here is a link: https://youtu.be/X_cB44awmbk