Who i am??!!!

My name is Darshan J. I completed my Bachelor of Engineering in Computer Science and Engineering in 2012 from Mangalore Institue of Technolgy and Engineering. My professional Information is as below, Please you see..

Sybase DBA
Ahana Systems and Solutions Pvt. Ltd.
October 2012 – April 2013 (7 months)
Worked for IBM project in ITPL Bangalore for ING vysya BANK as a Sybase DBA



Progress DBA,MySQL and MongoDB DBA
Altisource
April 2013 – July2015
• Database Software Installation, Configuration and Up-gradation. 
- Have experience with Progress Open Edge 10.2B and related database software installation and configuration on Solaris, Linux and Windows platform.

• Database Requirements Analysis, Design and Monitoring
- Hands on experience with new database creation and up gradation, clone from existing database and database creation from logical backup file based on application requirements.
- Implement and maintain Progress Database security (creation and maintenance Database users and roles, assign privileges).

• Database Backup/Recovery and Data Movement
- Establish and maintain sound Backup and Recovery policies and procedures for Database server.
- Hands on experience of Online/Offline (Hot and Cold) and logical backup tasks.
- Experience with Database backup, restore and recovery using progress utilities.
- Hands on experience with data movement using Import/Export (progress) utility.

• Report generation and Automation.
- Report generation for regular Health checkups of Database and to keep track of its growth.
- Automation of manual task such as report generation by use of shell scripting, perl and 4GL.
- Writing scripts and scheduling in crontab for monitoring database and to reduce manual DBA work.


Courses undergone at Ahana Systems and Solutions Pvt. Ltd.

• MongoDB DBA   (Certified from 10Gen )
• MS SQL 2008 R2
• Sybase DBA
• Unix Shell and Perl scripting.

Database Lock File - .lk file

Database Lock file is text file which will create when database having one or more connection. This file will be existing in the database directory where .db file reside.

When DB is having single user mode connection it will not allow more than one user to connect at a time. It will throw error if you connect using “mpro”, or if it already user connect via “pro” command.

mpro /path/to/dbase/dbname

            ** The database <file-name> is in use in single-user mode. (263)

When DB is having multiuser open connection it will allow you to connect to DB using “pro” command. You can able to connect DB only using “mpro” command.

pro /path/to/dbase/dbname

            ** The database <file-name> is in use in multi-user mode. (276)

It is very curious now what is the containing element in the small text file which controlling the Database connections. Let me clear you on this

           1.      The mode of DB started (single or multi)

           2.      Pid of the process which started DB.

           3.      Hostname of the process that started DB.

When the process asks DB to start progress will check the .lk file in the .db file location and if .lk file is not exist it will create one and enters the .lk file necessary entry and starts the database.

If .lk file is already exist and if it valid it will throw error either 276 or 263 according to the mode of connection.

Validation is as below

                 a.       It will check the hostname which is present in .lk file and the process which is connected if both matches then it valid. If it does not matches directly it threw error.

                 b.      It then check for pid which is active or not, if it is active then .lk is valid, if not you can remove .lk file and start DB.

                 c.       If .lk file is valid we cannot start the DB as we get appropriate error in the console.

.lk file is crucial file which having the connection control details it should not deleted ascommon file. Deleting the .lk file may lead to database to be corrupted.

In some situation like abnormal shutdown/unclean shutdown which will unable to remove the entry of .lk file we can remove it only if no progress process is not running on the server but the risky factor should be taken. 

Progress Openedge database license file.

License file is a Text file which contains the details of Progress DB connections. This file is placed in the same location where .db file exist and naming convention of this file is dbname.lic (if database name is dbname). if this file having issue in writing then information will be recorded in .lg file itself.

License file is space delimited and having 12 columns in it, these columns represent different meaning as below

1.     Current date

2.     Current time

3.     Number of licensed users specified by the configuration file (if -1 shows, it means that an unlimited number of users are licensed)

4.     Current number of total connections

5.     Maximum number of total connections

6.     Minimum number of total connections

7.     Current number of interactive connections

8.     Maximum number of interactive connections for the last hour

9.     Minimum number of interactive connections for the past hour

10. Current number of batch connections

11. Maximum number of batch connections for the past hour

12.   Minimum number of batch connections for the past hour  

It will resets the value once it writes to .lic file. APW BIW AIW and WDOG process will not be counted and recorded in the file. Webspeed and appserver connection will be recorded in 7,8 and 9th column, There is no option to start DB without writing to lic file. You should not delete lic file when DB is up and running, if lic file is deleted when DB is down it will create the new one at the time of starting the DB

       

       

Before-Image Fies

Before image file

Before image file is a file which store the transaction information (like user, transaction number and previous value) happens in the DB in a Binary form, once the transaction is completed it will stores the binary info on how to modify the records and indices. if the transaction is aborted then DB will reads the data from the bi file and stores in it.

BI files are essential to access any DB. At least one BI and one data file is required.

BI in .st file,

            BI entry in a .st is file must, a simple entry can be made as below

b .

            b here is stands for the before image file and “.” stands for the current directory where DB is going to create.

It will be good practice to have as much bi file is needed, when the long running transaction happening it will keep on writing the transaction details to the bi files which leads to growth in bi file, when bi growth is occur which may affects the performance or once it reaches the maximum size then which may down the DB as well!. so it is must to keep an eye on the BI file size.

           

            It is always a good practice to run one Before-image page Writer (BIW) for Database to reduce the Bottleneck happen from BI.

Syntax: probiw <db-name>

Ultimately you can have only one biw per DB which consumes one “–n”

           

How to check the size of the BI?

You can find the current size of bi

 for each  _dbstatus no-lock.

  disp (_dbstatus-bisize  *  _DbStatus-BiBlkSize / 1048576  ) format   ">>>,>>>,>>>,>>9"  label "BI Size#"

    ( _DBStatus-BISize * _DBStatus-DBBlkSize / 1048576 ) format ">>>,>>>,>>>,>>9"   label "Used BI Size#"  with frame bi.

But remember this query will give you the actual size of bi once it crosses bi cluster 

Monitoring BI Activity

Use operating system utilities to monitor the amount of I/O activity on the disk where the BI file or files reside. Use the PROMON utility to monitor specific BI activity. Use the R&D option BI Log Activity -

promon > R&D > 2. Activity Displays > 5. BI Log

- Busy buffer waits

- Empty buffer waits

- High number of writes per second

- High number of partial writes

How to Truncate the Grown Bi Size

            Shutdown th DB using proshut or promon

            Hit the below command

proutil <db-name> -C truncate bi

4GL vs. SQL

4GL vs. SQL

4GL is programming language to fetch the data from the Progress database, it do support the sql query but itport the sql query i4GL and some complicated sql query progress compiler wiil not understand so it is recomended  will be much faster if you use 4GL and some complicated sql query which progress compiler will not understand so it is recommended to use the 4GL over SQL.

When it come to programming language it do have its own syntax, prototype, data types etc, 4GL also has its own, where it seems bit difficult to the beginners if you know English grammar I assure you will become good 4GL programmerJ.

First thing we learn in SQL is the customer table field extraction as we get example in the all academic text book,

select * from customer;

4GL corresponding for the same will be

for each customer:

display customer.

Simpler than ever. which was the t-shirt quotes for 2 decades. above program will give the all the fields and records of customer table, it will display the record page by page, once it reaches the bottom of screen it’ll pause the display and shows message as “press space bar to continue” once you hit space key it will display next set of record till it hit the end of records, in the time of execution if you do not want to continue you can hit esc and press spacebar.

Below are the few comparison 4GL and SQL

Select * from customer;	for each customer: display customer.
select cust_num, city from customer;	for each customer: display cust_num city.
select * from customer where name=’Darshan’;	for each customer where name = ‘Darshan’: display customer.
select * from customer where name=’Darshan’ order by name;	for each customer where name = ‘Darshan’ by name: display customer.
select * from customer where name not null order by name;	for each customer where name <> ? by name: display customer.
select customer.custnum, name, city, ordernum, orderdate, shipdate from customer, order where state = “Karnataka” and customer.custnum = order.custnum;	for each customer where state = 'Karnataka' by city: display custnum name city.             for each order of customer:             display ordernum orderdate shipdate.             end. end.

Parameter File

What  is Parameter file in Openedge? 

  Parameter file or .pf is a text file which holds any number of start up parameters to run progress, If you using the same parameter always when you start the DB it will be better to use .pf file where you can put all those parameters.

   .pf file can be used to start not only the database, you can use to start webspeed or appserver where you can mention the Database to which it should connect.

How to use .pf file?

   pf file for database you can use by the time of starting it,

Syntax:

 $DLC/bin/proserve <DB-name> -pf /path/to/pf/pfname.pf

This way you can start the Database with .pf file

  

structure file in Openedge

What is Structure File?

Structure file or .st file is a text file in a system which defines the structure of database.

What does this .st file contains?

.st files contains the information about database like,

   What type of File it contains,

   What is the area name,

   What is the cluster,records per blocks, area number,

   Where the physical location of datafile,

   Is it a fixed or Variable extent

   What is the maximum size of datafile,

Lets know all details one by one,

 What type of File it contains?

       This is the first entry in .st file,File can be of Schema and Data, Before image, After image or transaction log.

     

       Schema and Data: this is defined as "d" in .st file and its physical file extension will be .d1-.dn, This file will be holding the schema and application data.

example:
      d “Area-name”,32 /usr1/inv f 1024

     you can see in the above .st file entry "d" is defined at first, which will tells it is containing data file.

      Before image: The before-image file also called the "bi file", contains the primary transaction log of the Progress RDBMS. it should be there to start the Database.  Without the before-image extents, the database cannot be accessed.

example:
     b /path/to/bifiles.b1 f 1024

     "b" is highlighted above which tells it is before image file, it doesn't require the area name unlike "d"

     After Image: The after-imaging lets you to recover the Database or .bi file which are lost or damaged. You can use the AI files with the roll-forward recovery process to restore the database to the condition it was in before you lost the database.

example:
    a path/to/after.a1 f 2048

     "a" is highlighted above which tells it is after image file

   Transaction log: The transaction log contains the transaction number of all committed distributed transactions. When there is an in-doubt transaction, this log is scanned for the transaction number to determine if the transaction committed or not. If the transaction number is found in the log, it means the transaction committed. If the transaction number is not found in the log, it means the transaction aborted. 

To a structure file it is must that one before image and one schema data entry to create Database, 

What is the area name?

    This is the name of the area where the data gonna store physically.

What is the area number,records per blocks,blocks per cluster?

  Area Number is the number of the storage area if you do not include it in .st file. while creating DB using prostrct will assign the Number to the area.

  Records per blocks is the Number of DB records should be in a blocks, the value entered here can be of 1,2,4,8,26,32,64,128,256.

  Blocks per Cluster is the Number of Database block should be in a cluster, if this area is blank or 1 area will be of type 1 and 8,64,512 will be of type 2.

   

Physical location of datafile.

  This is the path mentioned where the Datafile exist physical, it could be either relative path or an absolute path.

Fixed or Variable extent.

  Fixed extent can be identified by token "f" which will be mentioned after the path, if it is variable it doesnt required you to mention the size or "v".

What is the maximum size of datafile?

  if it is a fixed extent you should mention the size after the token "f" in .st file, if it is variable and large files is not enabled it will be of 2 GB. it should be in multiples of 16th of DB block size.

The minimum information is required in a .st file are an before image entry and at least one data extent.

The minimum information required to define any extent is type and the path.

example:

b .

d /path/to/data1.d1

above can be a example of minimum .st file requirements.