Delete a table from command line

Several scripts
Post Reply
User avatar
Shashwat
Nouveau
Nouveau
Posts: 24
Joined: 01 Oct 2009 12:00
Contact:

Delete a table from command line

Post by Shashwat » 08 Nov 2010 11:03

Can anyone provide a script to delete the control-m tables from command line?

Before deploying a table, I need to delete the existing table from CTRL-M database. Currently I'm deleting it maually through Scheduling tab.

User avatar
brownbag
Nouveau
Nouveau
Posts: 161
Joined: 11 Oct 2007 12:00
Location: Melbourne

DELETING A TABLE FROM COMMAND LINE

Post by brownbag » 10 Nov 2010 3:28

I've had a really good look around and I don't think it is possible without using SQL on the database. The closest I got was the cli utility which can delete a job from a table. Even with this utility you need to specify the full job name (no wildcards are accepted) so it isn't even close to an answer.

There is no reason that you cannot use SQL queries to delete the table and all it's jobs. You can pass to it the Table name and CONTROL-M and it should be quite simple.

User avatar
satavpa
Nouveau
Nouveau
Posts: 13
Joined: 19 Nov 2009 12:00

Post by satavpa » 10 Nov 2010 5:54

Hi,

Could u plz elabrate it, how to delete the CTM table with sql query...

User avatar
brownbag
Nouveau
Nouveau
Posts: 161
Joined: 11 Oct 2007 12:00
Location: Melbourne

DELETING A TABLE FROM COMMAND LINE

Post by brownbag » 10 Nov 2010 6:02

I've never done it myself, and I wouldn't want to have to! Here's a few hints:
- If you don't have it, get a copy of the Database Schema for Control-M/EM and Control-M/Server from BMC for your version and then find the tables relating to the job definition.
- You probably only need to remove it from Enterprise Manager, as you seem to be planning to copy it from Development then upload the table to Control-M/Server.
- find all tables in 'Job and Calendar Definitions' that relate to that table. In 6.4.01 the 'table' holding the table name is DEF_VER_TABLES. You'll need to get the TABLE_ID from this table (using the SCHED_TABLE field to match the table name). Then delete any entries matching this TABLE_ID in other tables. And there's plenty of them, as each type of data in a job parameter is held in a different table. For instance there is a table for the conditions, resources, shouts (postproc) and even one for every Do option.
- Test it very very well.

User avatar
rahulsehgal
Nouveau
Nouveau
Posts: 148
Joined: 19 Mar 2009 12:00
Location: Delhi
Contact:

Post by rahulsehgal » 16 Nov 2010 12:09

Hi Shashwat,

Deleting Scheduling table using SQL Query is not suggested instead try the Control-M EM Command Line /O option or simply use Scheduling table Manager to manually delete the Schedulig tables from Control-M EM Database,

but if you still want to delete the scheduling table then you can run "delete command" as per your database on following SQL Table :-

Control-M EM Database :-
DEF_TABLES

Regards,
Rahul Sehgal

User avatar
Walty
Nouveau
Nouveau
Posts: 473
Joined: 20 Jan 2006 12:00

Post by Walty » 16 Nov 2010 10:05

Hi,

Just a little question, where do you want delete the scheduling table:

1) In the Control-M/EM Database
2) In the Control-M/Server Database
3) Both
Best regards
Walty

User avatar
philmalmaison
Nouveau
Nouveau
Posts: 1148
Joined: 08 Jun 2007 12:00
Location: Ile de France

Post by philmalmaison » 19 Nov 2010 4:45

This is ridiculus, how can you manage the on delete cascade for a table !!!!
Use the desirated interface to do it, or you are a dba, and perfectly know physical data model for your controlm version.

Regards,
Philmalmaison

User avatar
Shashwat
Nouveau
Nouveau
Posts: 24
Joined: 01 Oct 2009 12:00
Contact:

Over write option

Post by Shashwat » 13 Dec 2010 11:27

How can I use the overwrite (\O) option? Can anyone tell me the complete command?

To upload the CTM table I'm using the command as below -

defjob -u XXX -p XXX -s EM-DEV -src <Source>

User avatar
mauriziog
Nouveau
Nouveau
Posts: 807
Joined: 08 Jun 2007 12:00
Location: Varese - Italy
Contact:

Post by mauriziog » 03 Mar 2011 12:03

To upload the CTM table I'm using the command as below -
defjob -u XXX -p XXX -s EM-DEV -src <Source>
deftable is not for upload table. From documentation:
"deftable
The deftable utility imports scheduling tables and group scheduling tables into the CONTROL-M/EM database
."

Translating is like "write" action....


the /o option from documentation:
"/o Overwrite. The /o switch directs the utility to overwrite any existing tables."

Have a good day

User avatar
johnwright456
Nouveau
Nouveau
Posts: 3
Joined: 27 May 2011 12:00

Post by johnwright456 » 27 May 2011 2:39

Let's say you need to drop all tables in a mysql database. How do you do that?

You could use a gui, but that's not fun.

You're a shell jockey so you want a commandline:

mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | \
gawk '{print "drop table " $1 ";"}' | mysql -u uname dbname

(that's all one line, but if I do it as a line then it screws up my theme - go figure).

This assumes that you are running in passwordless mode. See "man mysql" for tips on how to pass in passwords in another manner.

What this does is

connect to a specific mysql database and execute the command for showing tables
find lines that match "Tables_in" and not show them
find lines that match the + character and not show them
use gawk to print out the words "drop table" followed by the table name (which is in $1) and then a semicolon
pipe all of that back to the database you got the list from to drop those tables

User avatar
Sinbad
Nouveau
Nouveau
Posts: 1
Joined: 12 Oct 2011 12:00

Post by Sinbad » 12 Oct 2011 3:26

To delete from the CM (datacenter) database
ctmpsm -SCHEDTAB -REMOVE<tablename>

You can get a list of tables with
ctmpsm -SCHEDTAB -LISTTABLE "*" and use awk to get it into a format you can use for -REMOVE

User avatar
mwright
Nouveau
Nouveau
Posts: 5
Joined: 11 Aug 2011 12:00
Location: Irving, Texas

Post by mwright » 02 Nov 2011 6:43

ctmpsm -UPDATETABLE (tablename) DELETE

User avatar
Allison
Nouveau
Nouveau
Posts: 14
Joined: 22 Sep 2012 12:00

Post by Allison » 22 Sep 2012 7:11

#!/bin/bash
MUSER="$1"
MPASS="$2"
MDB="$3"

# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)

if [ $# -ne 3 ]
then
echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}"
echo "Drops all tables from a MySQL"
exit 1
fi

TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )

for t in $TABLES
do
echo "Deleting $t table from $MDB database..."
$MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"

Post Reply