Re: Task for Cleanup of Database- small update

[Satish Mullapudi]

Hi,
It is good that you ppl. are into this task of analyzing the task of cleaning of the db.
I want to mention one word is , if you feel that any of the columns' datatype mentioned is not sufficient/inconsistent to hold the aforesaid info. u are free to increase/modify the column. But mind you.... think twice before making any changes. The same have to be reflected in the DDL used for recreating the tables.
Another thing I want to ask is: The main condition which I have laid out in the set of conditions is:

1. out of that 1L 80k emps have already left the org, only 20 k r working now. So delete all the 80k records

I didn't find anyone reverting back to me asking on how to figure out the employees who have left the org.
For this pls. add a column
( STATUS CHAR(1)) [ Domain = 1->WORKING   0->TERMINATED/RESIGNED ]
Now I want only the emps. who are currently working in the org. to be present in the EMPLOYEE table after db cleanup.

For further queries feel free to contact me,

Thanks & Regards,

Satish Mullapudi,
IBM Certified Database Associate,
IBM Information Management (DB2 UDB),  
Miracle Software Systems Inc.
email: smull...@miraclesoft.com
Mobile: +919923020321

On Nov 14, 2007 12:52 PM, Satish Mullapudi <satishmu...@gmail.com> wrote:

Hi Gokul,

I have got your question. Even if the names doesn't match in Parent & Child Tables, you can have a Primary - Foreign Key relationship. The reason why we go for common names in both (parent-child tables) is , not to get confused between both of them.

For further queries feel free to contact me,

Thanks & Regards,

Satish Mullapudi,
IBM Certified Database Associate,
IBM Information Management (DB2 UDB),  
Miracle Software Systems Inc.
email: smull...@miraclesoft.com
Mobile: +919923020321

On Nov 14, 2005 9:22 AM, Gokul Sagi <gs...@miraclesoft.com> wrote:

Hi Ravi,

 

Here's the status of my task (Data modeling Doc)

 

We have to create 6 tables (employee, Department, Project, sales, Staff, Emp_proj) and a view (emp_dept_view) based upon Department, sales.

 

I had a problem here

 

In the creation of tables there is a foreign key specified for column PROJ_DEPT in EMP_PROJ table and it's related to DEPARTMENT table where such column does not exist.

 

Similarly there is a foreign key specified for column PROJ_MGR in table PROJECT and it's related to employee where there's no column in EMPLOYEE table.

 

Just check it once and mail me. Then I shall create tables and populate the data into them.

 

Thanks & Regards,

Gokul Sagi

 

 

 

 

 

 

-----Original Message-----
From: mirac...@googlegroups.com [mailto:mirac...@googlegroups.com]On Behalf Of Venkata Ravi Kumar Koppala
Sent: Tuesday, November 13, 2007 1:38 PM
To: Satish Mullapudi; mirac...@googlegroups.com
Cc: va...@miraclesoft.com

Subject: RE: Task for Cleanup of Database

 

Gokul,

 

    Go through the Data Modelling Doc given by Satish & Validate by EOD today.

 

Thanks & Regards,
Venkata Ravi Kumar Koppala,
Team Lead,
Database/BI/ETL,
Miracle Software Systems, Inc.
Phone :- 248-233-1854
WWW.MIRACLESOFT.COM
A Premier Business Partner of IBM.
A National Partner of SAP.
A National Partner of Siebel.
An Associate Partner of WebMethods.

-----Original Message-----
From: Satish Mullapudi [mailto:satishmu...@gmail.com]
Sent: Tuesday, November 13, 2007 11:34 AM
To: mirac...@googlegroups.com
Cc: va...@miraclesoft.com; vkop...@miraclesoft.com
Subject: Task for Cleanup of Database

Hi All,
First of all am happy to announce that the scripts which I have developed for the cleanup of a database have successfully been deployed into production.
These scripts are windows batch scripts. I want you ppl to work out & develop the same as Unix shell scripts. As a matter of fact unix is much more user friendly when coming to scripting when compared to windows. I'll jus give the online of the task. I want you ppl. to work on it.

1. Take the backup of the existing db
2. Verify whether the backup operation is successful of not using the appropraite cmds
3. Once it is confirmed that the backup is successful
4. Run Emp_Org cleanup script
   a. From current tables, export rows we want to keep
    b.Drop old tables
    c.ReCreate required tables
    d.Import the exported rows
5.Verify the entire cleanup operation
6.Backup new database
If at any stage the cleanup is unsuccessful, RESTORE the database to its old state (before cleanup)

I am giving a sample datamodel to work on & the conditions . Do create a database as required & insert sample data into it. The data preferably should be huge because that tests the performance of your scripts.

Ravi: It is preferrable that you update me with the status of the task being done there , so that I can guide you ppl, if you are going wrong at any point.
Varma Sir/Ravi: Pls. see to it that all of our DB team works on this bcoz it is really a good task to work on both on DBADMIN & UNIX shell scripting.

If you have any queries feel free to contact me.

Thanks & Regards,

Satish Mullapudi,
IBM Certified Database Associate,
IBM Information Management (DB2 UDB),  
Miracle Software Systems Inc.
email: smull...@miraclesoft.com
Mobile: +919923020321

color=black>

--
Thanks & Regards,

Satish Mullapudi,
IBM Certified Database Associate,
IBM Information Management (DB2 UDB),  
Miracle Software Systems Inc.
email: smull...@miraclesoft.com
Mobile: +919923020321