Scheduler Usage

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.

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.

Hi,

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

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.

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

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

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

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>

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

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

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

ctmpsm -UPDATETABLE (tablename) DELETE

#!/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"