Saturday, April 26, 2014

Where are DB2 plans , DB2 packages stored?

Recollecting from the DB2 architecture, we know when DB2 gets installed  on Z/OS,  the following system objects are created: DB2 Directory,catalog... as shown below.
DB2 SYSTEM Objects. We need all these components to run our DB2 on Z/OS.


 There are  system databases like DSNDB01, DSNDB06, DSNDB07 on which all these system objects resides on.
DB2 Directory resides in DSNDB01 database and this database contains 5 Table spaces.
SCT02 : knows as Skeleton Cursor table
SPT01 : Known as the Skeleton Package  table
SYSLGRNX : Log range. This table space maintains the log whenever a table or index is opened or closed.
SYSUTILX:  Maintains a row when ever any utility is running and Row persists till utility runs. If the utility is terminated, db2 uses the information in the row to restart.
DBD02 : Contains information about the databases that exists within the sub system.

Whenever we issue a bind against our  SQL statements onto a plan, a DB2 structure is created in the SCT02 Table space.
If we do a package bind, a similar structure is created in the SPT01 table space.
When executing the db2 program, these structures are loaded  from  SCT02 or SPT01 into the EDM pool.

So,We are creating and maintaining one form of  DB2 internal structure corresponding to the SQL statements in our Cobol-db2 program inside the DB2 directory(either in SCT02 or SPT01). and this is where the plans/packages are stored.
We cannot view this structure inside this directory..
On a broader sense, we cannot view anything in  DB2 directory. This Directory is only for DB2 internal use.
 However information about the package or the plan are written on to the DB2 CATALOG  tables like SYSIBM.SYSPLAN, SYSIBM.SYSPACKAGE and others. These table we can query and gather the required information as and when needed.

Saturday, April 19, 2014

why do we get DB2 sql code -904 ?

We all know, DB2 SQL of -904 stands for "resource unavailable", but here i will try to explain why we get that error in our work. There can be many reasons , but i will mention one of the reason here.
Before coming to that point, let us see
What is DSNZPARM parameter?
While installing DB2 subsystem,we supply some values via the install ISPF panels, and these parameters are know as DSNZPARM. We may look upon it  as  a 'subsystem parameter load module', which contains DB2 execution-time parameters. There are many parameters which needs to be fed into the system as part of this DSNZPARM.
One of such parameter is   NUMLKUS which stands for "Locks per user". This is the maximum number of page locks  or row locks that can be concurrently held by single DB2 application. When our application runs a db2 query,it attempts to take a lock on a db2 resource (page or row lock).
If this attempt to take a new lock causes the application to cross the NUMLKUS threshold, we get SQL code of  -904. In this case the program can issue a Rollback.
The value for this NUMLKUS can vary from 0 to 100000. What if we specify a value of '0' for this parameter?. Well, that means, no limit per user; and you can run into storage problem.(DB2 uses 250 bytes for each lock)

Wednesday, April 9, 2014

Static call and Dynamic call in cobol with example - compiler options for static and dynamic call

This is a much discussed topic among the COBOL programmers.We can identify the calls by seeing the 'CALL' structure.Lets see what is the difference between static and dynamic call and other information.
Static call implies that the objects modules(of called and calling) are bound together before they use each other.This bonding remains the same until one of the program gets changed and the whole set is compiled/linked again.
Dynamic call implies the calling program does not know about the called program until the call takes place.The bonding between the program happens right at the time the call is made 

Lets see in details how the calls are made and few other details
In case of static call, we refer to the called program name directly in the call statement. 
Ex: CALL ‘PGM1’.

In case of dynamic Call, we call one working-storage variable which is populated with the called program name at run time dynamically. 
01 WS-PGM PIC X(08). <<== Working storage variable
Move ‘PGM1’ to WS-PGM 
CALL WS-PGM                 
Here WS-PGM is the Working Storage item. In Procedure Division we are moving the called program name 'PGM1' to WS-PGM and then calling it.

When to go for Static and Dynamic call ?
Taking the decision to call statically or dynamically is upto the programmer.Generally we consider the size of the program and decide.If the size of the called program is  small (i mean, very few lines of code and non complex functions )then we can go for static call, otherwise dynamic call will be better.Both have its positive and negative points.
So in case of static call:
If we have the main program PGM-A and it calls two sub programs statically(pgm-b &pgm-c), we will have only 1 load module for all the three.Load modules of pgm-b and pgm-c will be linked edited into the load module of PGM-A making the size of the load bigger.
Negative points for a static call
Since the load modules exists together,it takes up more virtual space on disk 
If any changes are made to called program, then all the programs must be compiled/linkd.
Positive point:  Execution is fast

In case of Dynamic call:
As the name suggests, the program is called dynamically.The load module of the called program  will exists separately in the load module library. Thus here if PGM-A calls pgm-b and pgm-c, we will have 3 load modules our load library.
Positive point:
The main module does not take up unnecessary space.
Since these are stand alone programs, we can compile/link respective programs without touching all the programs and main program.
Negative points for a dynamic Call:
Since it executes with program call resolution, the call is a bit slow than static all.

Compiler options which can override the Static and dynamic calls:
If compiled with NODYNAM compiler option
CALL 'PGM-A' will be handled as  a static call
CALL WS-VARA  will be treated as dynamic call

If compiled with DYNAM compiler option:
CALL 'PGM-A' will be also treated as  a dynamic call
CALL WS-VARA will be treated as a dynamic call