1

I'm new to Unix and bash scripts so please bare with me if this is a silly question.

I've just started working for a company that uses DB2 hosted on a Unix platform and there are a lot of unwanted schemas in their databases (created by IBM Design studio). I want to clean up these schemas and I'm assuming a bash script is the best way.

What I'd like to do is query the database to get the schema names and store them in some kind of collection; then iterate through the collection executing a drop command for each name.

I'm OK on the DB2 commands (I think) but I'm not clear on how to get the results back in a script.

This will retrieve the offending schema names:

db2 "SELECT schemaname FROM syscat.schemata WHERE schemaname like 'IWSCHEMA%'"

This will remove the schema where the name has been assigned to the variable $SCHEMA_TO_DROP: 

db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('$SCHEMA_TO_DROP', NULL, 'ST', 'ErrorTable')"

All I need to do now is link the 2 together but not sure where to start?

TeamWild
  • 111
  • 1
  • 3

3 Answers3

1
# POSIX shell: command substitution, bash.info 3.5.4
# Sets SCHEMA_TO_DROP to the output of balh blah..
SCHEMA_TO_DROP="$(db2 "SELECT schemaname FROM syscat.schemata WHERE schemaname like 'IWSCHEMA%'")"
db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('$SCHEMA_TO_DROP', NULL, 'ST', 'ErrorTable')"

Update:

Like what visudo have mentioned, the SCHEMA_TO_DROP captured is actually a list. You can use the for var in balh list loop to iterate through the list.

Using $? may be unnecessary. Personally I prefer logic operations like || and &&:

IFS='\n' # make sure that for don't split anything other then newlines
for foo in $SCHEMA_TO_DROP; do
    db2 something something || oh_crap_it_failed
done
Mingye Wang
  • 1,181
  • 9
  • 23
1

You can assign the select output to a variable, and then iterate over each schema_name using a for loop.

/usr/bin/ksh

# Assign the output of the SELECT command to a local shell variable
SCHEMA_DROP_LIST=$(db2 "SELECT schemaname FROM syscat.schemata WHERE schemaname like 'IWSCHEMA%'")

# Loop over each schema_name
for SCHEMA_TO_DROP in $SCHEMA_DROP_LIST
do
     # Drop the schema, 1 at a time
     db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('$SCHEMA_TO_DROP', NULL, 'ST', 'ErrorTable')"

done

You could also use the $? variable immediately after the db2 drop command to capture the return code and then take action based on that (echo the error code, exit etc).

visudo
  • 4,631
  • 1
  • 11
  • 5
0

Have you tried using a subquery? It could probably look something like:

db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA((SELECT schemaname FROM syscat.schemata WHERE schemaname like 'IWSCHEMA%'), NULL, 'ST', 'ErrorTable')"

But would need to handle multiple values in the first parameter.

Centimane
  • 4,420
  • 2
  • 21
  • 45