RE: FW: Re: Post1-How to delete records from tables in one schema
Gokul Sagi
This is my answer for the first post to delete records from tables in one schema .
i am creating an empty del file in a particular path and replaces it on the existing tables through
load option .
so that :load does not check for dependencies.
less number of logfiles would be generated when compared to delete each and every record
from a table .
This is the script for that .
echo "enter database name"
read dbname
echo "enter schema name"
read schema_name
db2 connect to $dbname
table=`db2 -x "select TABNAME from syscat.tables where TABSCHEMA ='$schema_name'"`
echo $table
for i in $table
do
echo $i
db2 "load from /home/bhasker/bhasker.del of del replace into $i "
done
If you follow any other solution or any concerns with this script please mail me.
cheers,
Gokul Sagi
------- Original Message -------
From : Satish
Mullapudi[ mailto:satishmu...@gmail.com]
Sent : 8/29/2007 6:25:03 AM
To : mirac...@googlegroups.com
Cc :
Subject : FW: Re: Post1-How to delete records from
tables in one schema
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.
On 8/29/07, Sanjay kumar akasapu
<saka...@miraclesoft.com> wrote:
>
>
> 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
>
> Hi team,
>
> I want to delete all the records from tables in one
schema..can you work
> out
> for the solution.
>
> Thanks
> KishoreMalla
>
>
>
>
>
> >
>
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
- (NONRECOVERABLE
- Specifies that the load transaction is to be marked as non-recoverable and that it will not be possible to recover it by a subsequent roll forward action. The roll forward utility will skip the transaction and will mark the table into which data was being
loaded as "invalid". The utility will also ignore any subsequent transactions against that table. After the roll forward operation is completed, such a table can only be dropped or restored from a backup (full or table space) taken after a commit point following the completion of the non-recoverable
load operation.
With this option, table spaces are not put in backup pending state following the load operation, and a copy of the loaded data does not have to be made during the load operation. )
కిశోర్ మల్ల