Post1-How to delete records from tables in one schema

[malla kishore]

Hi team,

 

I want to delete all the records from tables in one schema..can you work out for the solution.

 

Thanks

KishoreMalla

 

[Sanjay kumar akasapu]

Hi Kishore,
I am sending the shell script given below which can be used to delete rows of tables
associated with a schema.We will discuss if there are any queries regarding the script.
Cheers.

echo "enter database name"
read dbname
echo "enter schema name"
read schema_name
db2 connect to $dbname
table=`db2 -x "select name from sysibm.systables where creator='$schema_name'"`
echo $table
for i in $table
do
echo $i
db2 "delete * from table $i"
done

Thanks & Regards
Sanjay kumar Akasapu,
Jr.Database Administrator,
Miracle Software Systems India(P)Ltd,
Mail:saka...@miraclesoft.com,
Mobile:
phone:248-233-31854.

------- Original Message -------
From : malla kishore[mailto:mallak...@gmail.com]
Sent : 8/29/2007 2:49:53 AM
To : mirac...@googlegroups.com
Cc :
Subject : RE: Post1-How to delete records from tables in one schema

[Satish Mullapudi]

Hello Sanjay,

I guess the SELECT query which you specified doesn't look correct. You are asked to get the tables in a schema and not created by a creator. Remember, Creator is different from Schema. For suppose, you can log into the user test_user and create a table saying CREATE TABLE PROJ.Staging(x x xxx  xx x ). Now, test_user is the creator of the table Staging which is in the schema PROJ. I guess you got the difference. In ur case, might be both the creator & schema are the same, so u r not experiencing any differences. So, do change ur query accordingly.

Thanks & Regards,

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

[malla kishore]

Hi Sanjay ,

 

Yes this may be solution if we have small amount of data .. but in real we will have gb's pf data and that too we have lot of dependiencies like constraints and all these stuff .. the answer is right if there are no dependensies if there are relations between tables in schema how you can know that we can delete first this table later this

 

sorry to add to my query is that my tables in the schema will have lot of dependiencies so how to acheive that.

 

Thanks in advance

Kishore

 

Please let me know any queries

 

On 8/29/07, Sanjay kumar akasapu <saka...@miraclesoft.com> wrote:

--
కిశోర్ మల్ల

[Satish Mullapudi]

Yes Kishore,

That's a main prob faced everywhere. To overcome that, there are nmany ways.

1. IF you have the data model , you can analyse from that model, on what are the parent tables & what are its child tables. Then you can delete the data in the child tables first & then go for the parent tables.

2. IF you don't have the data model, it is not a big issue, we get on what are the constraints in the schema provided you know the tablenames

db2 SELECT FK_COLNAMES "FK",TABNAME "FK_TAB",PK_COLNAMES  "PK",REFTABNAME "PK_TAB" FROM SYSCAT.REFERENCES WHERE TABSCHEMA='SCHEMA_NAME'.

U'll get the list of all the tables which are having FKs ^ their associated PKs. So, u caan analyse from the o/p u get, on the order of deletion to follow.

I guess this helps.

[malla kishore]

Hi Satish,

 

No issues no need to chk all those stuff ...

we have another way to do it.. ..

Hope our guys can make another way to do so

 

 

Thanks

Kishore